What I want – no – NEED in PowerPivot for Excel

I am an information worker. A big part of my job is to provide our staff and team members with useful information so that they can perform better in their jobs roles. In addition to this, as the Controller of our company, my job is also to guide the accounting department in processing, reconciling, adjusting and balancing our financial records, as well as ultimately reporting our overall results monthly. For this, we use Excel.

I don’t think that’s much of a surprise to anyone who follows this blog, but I think it’s important to be clear on that before we start. Every skill I use to do my job has been developed in order to get our goals accomplished quicker, more accurately and more cost effectively than has been done in the past. But ultimately the fact remains stable. I am an information worker, trying to turn data into information for my team.

I am also the Director of IT for our company, responsible for making sure our IT infrastructure is solid, effective and secure. So while I have the ability to direct IT funds, I also have the responsibility to make sure that they are spent effectively to have the greatest impact at the most practical cost. I believe that this gives me a fairly balanced and pragmatic view of things, as I have to walk that line of needs vs wants vs resources.

Excel is my engine

I love Excel. When working on our information needs, I live in Excel. And I will tell you that PowerPivot is, hands down, the best thing to happen to Excel since VBA was implemented. The product opens whole new worlds of data mining capabilities for the business user, putting the power to generate real information in their hands, rather than the IT departments. While I understand the need for security and IT control, I am also very aware that the business runs on the back of the people using Excel. It is them who generate the reports that allow companies to react to issues and opportunities. It is them who act as the guard dogs and seeing eye dogs for the company assets, alerting the parties that something has gone wrong, or giving hope as to what could go right.

This post is going to walk through one of our month end reconciliation processes in a little more detail, explaining what we do, and how we do it. It’s a real business process to illustrate what PowerPivot has done for us, as well as the major shortcomings that exist in the lack of a VBA object model for PowerPivot. Some things we do will make some people scratch their heads, but we have reasons why we do them the way we do. And I’m perfectly fine with explaining them, for right or for wrong, if you’d like to comment.

The process we use right now

The process I want to cover is the same one that I blogged about in Quantum Shifts With PowerPivot. It’s the process we use to count and reconcile our monthly inventory count of alcohol at our golf course. I’ve broken this down to show the steps, who is responsible, and what process was pre- and is post- PowerPivot implementation. One thing to keep in mind is that there is a large gap in Excel skill levels between Admin and the other departments here. While we live in Excel, they have other programs that they are far more proficient in.

Here is the process for the count and reconciliation.

Step & purpose

Performed By

Prior Excel Versions

With PowerPivot

File Preparation      
  • Open the Excel File

Admin

Manual

Manual

  • Click the RollForward Button

Admin

   
  • Deletes prior month file in shared drive
 

Automated

Automated

  • Copies closing balance to opening balance
 

Automated

Automated

  • Clears data input cells
 

Automated

Automated

  • Increments working paper date
 

Automated

Automated

  • Saves file under current month’s name (in shared drive)
 

Automated

Automated

  • Close the Excel File

Admin

Manual

Manual

       
Recording the counts      
  • Scan all barcoded products (using handheld scanner)

Dept

Manual

Manual

  • Count non-barcoded products by hand

Dept

Manual

Manual

  • Enter manual counts into Excel File in shared directory

Dept

Manual

Manual

  • Copy file from shared drive to accounting drive

Admin

Manual

Manual

  • Upload handheld scanner counts into Access database

Admin

Manual

Manual

       
Reconciliation      
  • Print an inventory valuation report from the Access db

Admin

Manual

N/A 1

  • Print general ledger listing for revenue accounts (6 accounts)

Admin

Manual

N/A 1

  • Print general ledger listing for expense accounts (6 accounts)

Admin

Manual

N/A 1

  • Open Excel File

Admin

Manual

Manual

  • Update linked PowerPivot Tables

Admin

N/A

Manual

  • Update PowerPivot data connections

Admin

N/A

Manual

  • Refresh all OLAP formulas

Admin

N/A

Manual

  • Manually enter inventory closing balances into the Excel file

Admin

Manual

N/A 2

  • Manually enter the current month transactions into the file

Admin

Manual

N/A 2

  • Manually enter the prior month closing balances into the file

Admin

Manual

N/A 2

  • Manually enter each day’s sales for the dates between the count date and end of the month

Admin

Manual

N/A 2

  • Manually enter adjustments for inventory goods in transit

Admin

Manual

Manual

  • Review the file for data entry error issues

Admin

Manual

Manual

  • Overwrite file in shared drive

Admin

N/A

Automated

  • Email Department supervisor (saying it is available)

Admin

N/A

Automated

  • Print entire workbook

Admin

Automated

N/A 3

  • Close Excel File
     
  • Scan entire working paper package to PDF (entire workbook plus all reports listed above = ~30 pages)

Admin

Manual

N/A 3

  • Email package to Department supervisor from Outlook

Admin

Manual

N/A

       
Variance investigation 4      
  • If variances are deemed out of line:
     
  • They product counts are investigated

Department

Manual

Manual

  • Adjustments to non-barcoded items entered in Excel

Department

Manual

Manual

  • Adjustments to barcoded items entered in Access db

Admin

Manual

Manual

  • ENTIRE RECONCILIATION PROCESS IS REPEATED

Admin

   
  • If variances deemed in line:
     
  • Department signs off as acceptable

Department

Manual

Manual

       
File Closing      
  • Re-copy file from shared drive to accounting drive

Admin

N/A

Automated

  • Entire file is reprinted

Admin

Automated

Automated

  • Adjusting Journal Entries printed

Admin

Automated

Automated

  • Journal Entries posted in Jonas Property Management System

Admin

Manual

Manual 5

 

Now, I footnoted some of those items, as they are important…

  1. The printing of the inventory valuation reports and general ledger listings are a thing of the past with PowerPivot. These reports served two purposes for us; they allowed us to manually enter the data in the cells, and also the acted as file support to show where the numbers came from if there was any question. But we don’t need them any more because…
  2. We don’t need to manually enter the data any more. The manual data entry cells have been replaced with OLAP formulas that pull the inventory values and G/L values out of the PowerPivot cubes. This is not only a time saver, but also an accuracy point. We did encounter errors in the past with fat-fingered entries and amounts put in the wrong lines. No more!
  3. Because we pulled the G/L and inventory data into PowerPivot, we were able to craft Pivot Tables to generate the inventory listing, as well as the General Ledger listings. Since they are now embedded in the file, we don’t need to print anything. This saves at least 30 pages of printed data EVERY time we do our inventory reconciliation. Depending on the month, the reconciliation phase might go through 2-3 iterations as well, so this could quickly add up.
  4. The real beauty of this is that, in the variance investigation phase, we have been able to provide our user a LOT more information. Instead of the current month G/L and inventory, they now have access to flip the pivot tables using friendly slicers to see last month’s info if they’d like. We also added a Pivot Table to show last month’s count vs this month’s count and the change. It would have been way too painful to get this before. As well, an entire page of key metric graphs (30 in all) that show product line results, metrics and variances for the past 12 rolling months.
  5. This part kind of sucks, but Jonas won’t let me write to their databases. I understand why, but I’d sure love to automate this process too!

We estimate the net time gained in this one inventory file is about 6 man-hours per month. May not seem like a lot, but that is almost two weeks of employee time per year. When you add the fact that our staff see more information to help them better manage the products in a fraction of the efforts, it really makes you appreciate the new abilities of this powerful tool. The best part to me is that it moves the job away from processing data to interpreting results. And THAT is what we need people for.

You another really cool thing about this? Once the PowerPivot data is in the file, I can send it out wherever I need. It’s like an Excel Add-in in that way: a perfect deployment. Everything I want the user to see packaged up in one nice neat package.

So what’s yer bitch then?

PowerPivot has already solved all my issues, right? Sorry, but no. It’s made my file sing, but it can still do better.

I am a HUGE consumer of VBA automation. HUGE. I don’t believe that my users should need to perform inane predictable steps manually, nor do I want to let them. The reason for this is that if I can automate it, I can guarantee that it will be done more quickly and more accurately. It’s nothing against my users at all, but humans are fallible. They make mistakes. So why let them? Especially where it adds no business value to take that risk?

The process of updating this specific file involves updating the Linked Tables, then going into the PowerPivot window and updating all the data connections as well. This should not need to be done manually. Starting from the Home tab, these actions take 6 clicks to perform. It’s a waste of time. PowerPivot NEEDS a VBA API to generate automatic refresh to take this process out of the users hands and let us make it more efficient to trigger.

And right now, the standard answer to that point is that PowerPivot has scheduled refresh… in Sharepoint. I will admit that my Sharepoint knowledge is very limited, so I may end up talking out of my backside, but based on what I’ve been able to pick up about the product so far I would point out the following issues with using it as a solution in our environment:

  1. I don’t have Sharepoint.
  2. It’s very expensive to get Sharepoint. (Based on a prelim estimate, I’m looking at approximately $15,000 in software and tech time to support my company’s 30 users, never mind training.)
  3. My file is not a report. It (and many of my other files) are working files that need VBA to function.

Now, you could argue that my reconciliation process could be done in Sharepoint. I would struggle with why I should make a staff member with a rich client UI suffer the latency of inputting items in a browser, but you could make that argument. I’ll admit flat out that the Variance Investigation is a prime candidate for Sharepoint implementation. No question at all. You could also maybe argue that the file wouldn’t need to bounce around the network as much, but I can clean that up via VBA (and do.) But can you honestly tell me that you would spend $15,000 for those features for 30 users? I’m sorry, but that’s a real tough pill for me to swallow. When I can’t get approvals for $5000 projects in my IT infrastructure, I just can’t see it happening.

It is also worth mentioning here that THIS file has a logical break point after the roll forward point where we save the file and close it. Most of my working papers don’t. The automation process in those deals with the copying of historical data, clearing of input cells and saving, then… SCREECH! The brakes are slammed on and we need to manually refresh the PowerPivot database to continue on in the process. Wouldn’t that smart a bit with you?

And one more thing too… I need ON DEMAND refresh. Not scheduled. When I’m working on a working paper at month end, and my staff tell me they posted a Journal Entry, I refresh. On demand is key.

Now, back to Excel. The reality is that I work in Excel, and I need my entire toolset there. We know that the API for programmability CAN be built, it’s just a matter of how much effort and resources it takes. The point that I need Microsoft to understand is that the business analysts of the world use Excel, know Excel, and shouldn’t need to learn a new product. They also shouldn’t need to farm their file out to IT to get the capability built. If that’s required, it plain and simply WON’T happen.

We need the refresh ability in Excel. Sharepoint too, but in Excel. The rich client that we also pay big licensing costs for. The one that runs locally under the fingers of accountants, data analysts and small business owners. That one. J

But it’s not all about the refresh

Not at all. The automated refresh is certainly the most important missing piece of PowerPivot for Excel right now, but there are other things I need the ability to change with VBA. Some of those things would be:

  • Adding/modifying or removing a connection to a data source. (Most likely modifying the path by code, but I can see uses for the others too.)
  • Changing the SQL query associated with the data connection so that I can pass dynamically sourced parameters to WHERE clauses. This could be dates or department numbers for example. I’d be all over this, as I’m trying to pull in the smallest data set to do the job, keeping my file sizes down.
  • Adding new measures on the fly with VBA. Please, please give me this ability. I currently have to create a PivotTable to do this and then do it manually… why?

And those are just some that I can think of, but I can assure you that other users will have other needs. Basically, if it moves in the UI, we want to move it by magic… I mean code.

And then there’s security

There’s another feature that is currently missing from the PowerPivot story to some degree, and that is security. I know that they are looking at this, but honestly, my request is fairly simple, I think. I’m not really looking for full SQL server domain level security. Not at all… well okay… not till at least version 3. ;)

My current security paradigm is to make the smallest data subset I can in PowerPivot. This keeps the file size down and performance up, but it’s also the way I restrict the user from getting their hands on info they shouldn’t. “Ah, yes Ken, but what if they modify the query?” you ask?

Well, my first solution is that only the accounting staff have direct access to read from our SQL databases. We develop our files in our accounting directory, then when ready we move them into a directory that my end user can access, and their rights prohibit them from updating the data connections. Simple as that. They can look at the SQL query, but can’t get to the database so it really doesn’t help. But this is pretty poor security.

Personally, all I really need to solve that is to be able to Lock the PowerPivot project for viewing, similar to what we can do with VBA Projects. Now, it would be nice if it was actually secure, unlike VBA or worksheet protection, but something to simply stop my casual users from going in there would be a nice addition at this point, I believe.

I’m sure that, long term, the full security paradigm will come (hopefully after VBA investment though.)

In conclusion

I hope that no one interprets this as unhappiness with the product. It’s far from that. PowerPivot is opening doors for us that we only dreamed of in years past. I had a meeting with my team today about a process we believe we can now automate which would save us about 16-20 man hours per month; a process where automation was impossible before PowerPivot. That’s HUGE! But programmatic control of PowerPivot would be a key part of that as well.

We’ve been given a taste of a product with an incredibly bright future. And that taste was just so good, I want the whole thing, and I want it now. Actually, not now, but RIGHT NOW. I want to roll this stuff into a fully cooked solution that works the way we do, and that involves automation and VBA. I want to make use of the software we have, not be sold on more that has umpteen features we don’t need just to get one feature we do need. I want to use this to maximize my time savings so that I can learn the skills in the rest of the BI story that Microsoft is developing for us. J

Excel Blackjack Game

I was playing a version of 21 with my daughter this weekend, and mentioned Blackjack to her. After a few questions, I thought it might be entertaining to build a version of Blackjack for her to play… so I did. And after that, I thought, what the heck… might as well put it up on the site.

I’m not really happy with the cards (I’d rather have pictures) but I’ve got other things to do, so just called it a day with it at the current stage. If you’re interested in wasting a little time, and you have Excel 2007 or higher, you can find it here: www.excelguru.ca/node/107

Comments welcome, as always. J

Worksheet_Change event and linked controls

I was working on a little application which has a spin button on the worksheet. That spin button is linked to a cell and works just fine for my purposes. After a while though, it became apparent that I needed to use some VBA to prohibit the user from changing the value in the cell in certain circumstances. No problem, I’ll just monitor the worksheet_change event via VBA and make sure that the user can’t change the cell value if certain conditions are met!

Not so fast though… this solution works just fine when the user types a value in the cell, but when you increment the cell value using a worksheet control, the VBA doesn’t fire!

If you’re interested in testing this for yourself:

  • Create a new workbook
  • Create a forms spin button on the worksheet (Developer TabàInsertàSpin button)
  • Link it to cell A1
  • Right click the Sheet1 tab and choose “View Code”
  • Paste the following in the window:

    Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = “$A$1″ Then

    MsgBox “Cell value changed!”

    End If

    End Sub

  • Go back to the worksheet and use the spin button

The value in cell A1 will change, but nothing else. But yet if you type a value in cell A1, you’ll get a message.

I’ve tested this now using the spin button & scroll bar controls, both forms and activeX, in Excel 2007 and 2010. Same results… or rather lack thereof…

I’m a little surprised that this is the case. I understand why a recalculation of worksheet formulas wouldn’t trigger the worksheet_change event… the actual formula didn’t change, after all, but the results did. To me though, this is different. I’m changing a physical value in the worksheet, and I would expect that this change would trip the event.

I guess I’ll have to find another way to deal with this. Not a big deal, but I’m curious… would you expect a change in the worksheet triggered by a control to fire the worksheet_change event?

Text functions teaching workbook

Over the past couple of weeks I’ve been working on a supplemental workbook that will be available to readers of a magazine article that I’ve written. The article is based on my Five Very Useful Functions For Working With Text web page. Since the space for the article is limited though, I had to cut a bunch out of it.

Since the text functions I’ve referred to are a bit more robust than what I had time to cover, I decided to add a “teaching workbook” to my web site, and link to this in the article for further information. The (Excel 2007/2010) workbook is available at the above link (near the bottom), and I’m curious as to what people think of it.

The intention of the workbook is to provide complete instructions for a beginner/intermediate user to teach them how Excel’s text functions can be used. There is a sheet per function which explores the various ways the functions can be used, as well as a test at the end that pulls the functions together to create more complex solutions.

If anyone has any feedback that they’d like to share, please feel free to post it here.

Thanks!