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.

Â

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