Background

I've been pretty up front with the productivity gains that I saw when I embraced VBA years ago. In fact, my About Me page on my website spells it out pretty clearly:

In the fall of 2002, the resort's management company decided to cut labour costs in the administration department. Overall, 2.5 full time equivalent positions were cut out of a 7.5 person office (yes 33% of the labour used) within one month, with the department expected to produce the same amount and quality of work. During a desperate shuffle of tasks to other departments, and dropping of tasks that were low priority, I realized that automation was going to be the only answer.

From there, I launched myself into VBA (Excel's programming language) full force. Automating as many tasks as possible, the administration office has been able to perform virtually the same task load as before the cuts, and the annual labour savings can be estimated somewhere between $20,000 and $40,000 per year. Suffice it to say, I love VBA, and am still amazed by the things it can do.

Revolution #1 - The Power of VBA

My embracing VBA caused some revolutionary changes in our processes. Many of the macros I wrote I now consider rather basic, but they made a huge difference to our productivity. We do a full working paper file set every month end, reconciling about 100 accounts monthly. Our first macros were controlled by two buttons in each file and basically allowed us to automate the following:

• RollForward Button
  • Copy month end balances to opening balances
  • Clear out the data entry fields
  • Update the date to a new month
  • Save the file under a new (correct) file name
  • Created a folder for the new file if one didn't exist
• PrintAll Button
  • Printed a copy of the journal entries (but only if required)
  • Printed a letter sized copy of our leadsheet (if required) to attach to the JE for support
  • Printed a legal sized copy of the leadsheet
  • Printed any supporting schedules (inventory listings, etc..)

Now some of the files were a bit more complicated, but even that simple level of automation allowed us to do a huge amount more work. The macros were consistent, eliminating much user error, and quick, allowing us to focus on doing work, rather than manually rolling things forward.

A specific case of needed improvement

For the last few years we've been working with these macros, trying to figure out how to amp some things up and become more efficient. Over the past couple of weeks I've spent some time working on a file that has been ticking along since the early automation days – one of our liquor inventories.

In the past, this inventory's process was as follows:

• Preparation
  • Open the Excel file
  • Click the RollForward button
• Inventory
  • Scan the inventory counts using a handheld scanner
  • Departments also record the inventory date and some counts in the Excel file directly
  • Upload our handheld scanner counts into an Access database (that I built)
  • Print an inventory valuation report from the Access database
  • Manually enter the inventory closing balances into the Excel file
• Reconciliations
  • Print the general ledger listings for the appropriate revenue and expense listings (6 product lines)
  • Manually enter the current month transactions into the file
  • Manually enter the prior month closing balances into the file
  • Manually enter each day's sales for the dates between the count date and end of the month
• Review
  • The whole package is scanned and sent to the department for review

I'll be completely honest, the file is weak and has long needed some better information in it to help with the review portion. The problem has always been getting good information into the file efficiently in a usable format. With the volumes of other work that are going on, it's simply been too much work to do.

Enter PowerPivot... and the next Revolution

In case you haven't worked it out yet, PowerPivot is allowing me to start taking this file to the next level. I've been waiting for this for a long time, and some of my dreams are finally coming to fruition with this new technology. So what did I do?

I've added a PowerPivot database to my file. This database pulls information from 4 different sources; 2 Access databases, a SQL Server database, and an Excel list.

In fact, there are actually three separate tables sourced from my BI Database: Chart of Accounts, Budget_Ops and Budget_Internal.

So what, right? Well here's why it's all important. Remember how I had to print my general ledger listing, walk to the printer to get it, then enter it manually enter the number into the spreadsheet? Not any more. Now I just hit Refresh. Why? Because the PowerPivot database is an OLAP database, which means we can use Excel's CUBE functions to pull data directly from the database via a formula. In fact, this formula returns the value of my beer sales for the current month:

=CUBEVALUE("PowerPivot Data",

CUBEMEMBER("PowerPivot Data","[Actuals_GL].[GeneralLedgerAccountNumber].&["&VLOOKUP(A9,tblAccounts,3,FALSE)&"]"),

CUBEMEMBER("PowerPivot Data","[Actuals_GL].[Month].&["&TEXT(Date,"yyyy-mm")&"]"),

CUBEMEMBER("PowerPivot Data","[Measures].[Sum of Amount]"))

This fixes a major problem that I've had in the past. No more fat-fingered entry of numbers, no more forgotten entries. And even better? I can use a similar formula to pull out the value of the Beer inventory for the month. Mark the importance of that… it's from a different database.

I've also now loaded up this file, not only with the closing value of the inventory for one of my report tables, but a full inventory listing in a Pivot Table. No longer do we have to run the inventory listing from Access and email it down with the Excel file. It's embedded.

I've also put in a G/L Listing in a Pivot Table as well. This was never there before either. It actually makes use of a relationship that I defined in PowerPivot between the Chart of Accounts (from my Access BI database) and the Jonas Actual Transactions table (from my SQL database), as well as a relationship with a table of dates from an Excel worksheet. The relationship allows me to display the data with the account names, and drive the drilldowns via date slicers.

But the crowning achievement to me is that I now have access to my budget information in addition to the rest. This means that I can now provide a full dashboard of micro graphs that tells us what is happening in the department. The process of sourcing this data in the past was just far too painful to consider.

So from a rather stripped file that reported numbers in an ugly accounting format, we're turning this into an intelligent application capable of identifying why there is an issue in the inventory. (G/L Listing, Inventory Listing, Micro-graph dashboard, and all delivered quickly without having to enter information manually.) My team member who works on the inventory with the department figures that this will save him about 1.5 hours per month in processing and investigation time. While it may not seem like a lot, this is HUGE to us. It's 1.5 hours when we need them the most.

Is it all roses?

Lord no, not at all. I have an active thread open in the MSDN forums, trying to work out how to use a date/time DAX formula in a PowerPivot measure. The point of that is that I want to be able to use "OpeningBalance" as a measure in my formula:

=CUBEVALUE("PowerPivot Data",

CUBEMEMBER("PowerPivot Data","[Actuals_GL].[GeneralLedgerAccountNumber].&["&VLOOKUP(A9,tblAccounts,3,FALSE)&"]"),

CUBEMEMBER("PowerPivot Data","[Actuals_GL].[Month].&["&TEXT(Date,"yyyy-mm")&"]"),

CUBEMEMBER("PowerPivot Data","[Measures].[Opening Balance]"))

This is not easy stuff to me at this point. (I'm kind of hoping that I'll look back on this and laugh in about 6 months.) I'm still very much learning how to work with PowerPivot here, but this has huge potential.

Could I have done this without PowerPivot?

I should be up front here. Some of what I'm doing now could have been done outside PowerPivot. If I had solid SQL skills, I could certainly have created custom SQL queries and leveraged them against each database... at least, I think I could. I do still write some of my SQL to try and get the data source as narrow as possible, but I'm not sure that I'd be able to write the SQL to link the account names with the account numbers from my Jonas (SQL) database. If I could, then I suppose I could have hooked up a PivotTable for each query and used GETPIVOTDATA formulas to pull the data out.

Overall

Despite the fact that I may have been able to pull this off without PowerPivot, this approach is WAY easier. Plain and simple: I don't need to be a SQL expert. That's the whole point to this product… it puts the ability to mine data into the hands of business pros, rather than IT experts.