Rob Collie, chief technology officer at cloud BI company Pivotstream, explains how to quickly compare budget versus actuals in a single pivot using Excel add on program PowerPivot.
In my previous article, I showed how with PowerPivot, rather than tediously using VLOOKUP to combine separate tables into a single table, you can just link them together using relationships that only take a few seconds to create.
That saves a lot of time, shortcutting you past the tedious preparatory steps and straight into the analysis and reporting phase. In this article, I’ll show how that same approach can also deliver results that you cannot achieve with VLOOKUP.
Budget versus actuals
A prime example: when you have data sets of different sizes, no amount of VLOOKUP will do the trick. This is something we face all the time in our cloud BI business at Pivotstream, particularly to provide our clients with analysis of budget vs. actuals.
Let’s say that, over the course of a few years, your business conducts about 60000 transactions:
Your business, like all good businesses, performs a budgeting process every year (or perhaps every quarter).
Budgets are always built at a coarser granularity than transactions. Here’s an example of a detailed budget that is built at the year-month-sales territory-product category level of granularity, and consists of less than 2000 rows:
Single integrated reports: the hard way
Naturally, you would like to be able to report on how well your business is performing versus budget. You’d ideally like to have a concise, integrated report like this:
That is not easy to build; at least, not without PowerPivot. VLOOKUP can’t merge the transaction and budget tables because they are not the same size.
If you are the tenacious sort, you can get to a unified report like this by creating two pivots, one for budget and one for actual, hiding both of them, and then building a single report that uses formulas to ‘fetch’ data from each pivot behind the scenes. However, it is not a light task, even if you have the formula skills to do it at all.
Single integrated reports: the easy way
With PowerPivot, you don’t need to do any of that. I built the report above in a few minutes.
See the Fiscal Year slicer (filter) above the report? When I click a different year, the entire report updates based on that single click:
Pretty slick. Imagine doing that with the ‘two pivots plus formulas’ approach and having a single click change everything.
You could of course make your formulas even more complex than they already were and use data validation to provide a FiscalYear dropdown, or maybe write some macros. Again, it’s even more work with a greater degree of skill required, whereas in PowerPivot, a single click adds the FiscalYear slicer.
Also, note that this business didn’t offer as many product lines in 2003 as it did in 2004, and the report automatically shrank to reflect that. For instance, I don’t see ‘Bike Racks’ and a blank row in my report for 2003, even though Bike Racks do show up in 2004.
The tedious ‘two pivots, lots of formulas’ approach simply cannot do that, and this was no extra work with PowerPivot.
Method
In short, you use separate tables. Let’s consider the question of time – we have a sales table that operates at the level of individual dates, and a budget table that operates at the year-month level.
The solution is to create a ‘periods’ table that is just year-month combinations:
Then use relationships to link this table to the sales and budget tables. Conceptually, you end up with something like this:
The budget table was already at the year-month level, and included a year-month ID column which we can use to create a relationship to the periods table:
We need to create a similar column in our sales table. This is simple to do with a calculated column:
Then, you use columns from the periods table on your pivot. In the report I showed above, the FiscalYear column that I used for the slicer came from periods:
The only thing left that may not be obvious is the ‘sales versus budget’ column of the report. That’s another example of a measure, and the formula is very simple:
([Actual sales]-[budgeted sales])/[budgeted sales]
New layouts with a few clicks
If you want a different report layout, you just drag fields to different zones in the field list. It takes just a few seconds to produce something completely different:
With the ‘two pivots plus formulas’ approach, you would basically start from scratch to create each new layout.
This is another example of what I mean by ‘portable formulas.’
If you have a question about PowerPivot, please feel free to drop Rob a note at rob@pivotstream.com, read his blog at powerpivotpro.com or visit the Pivotstream website.
Links
Spreadsheet skills: modelling actual versus budget
Professional development
CIMA on demand