IT zone

Reporting Actuals vs Budget with pivot tables. Tutorials by David Carter

Handling budgets with pivot tables is easy. The hard bit comes when you have to compare the budgets to the actuals. The trick is to get your actuals and your budgets into the same format, that of a Trial Balance with the month's values (debit or credit) in a single Amount column.

Problem #1, different data formats
The problem is that budgets and actuals usually come in different data formats. Actuals usually derive from hundreds or thousands of transaction records in the accounts package. You summarise these to calculate the monthly account balances. Budgets, on the other hand, simply store each of the these monthly balances as a total.

So while actuals are stored as transactional data, budgets are stored as balance data. It becomes awkward when you try to mix these two data types in the same Excel worksheet.

Problem #2, rows and columns layout
A second problem is that budgets will usually come to you from the budgetholder in spreadsheet form – a list of accounts down the left, and a row of months at the top.

Unfortunately, pivot tables are not really designed to process data in this "rows and columns" layout (you have to use the Multiple Data Ranges option, but it's not flexible enough). Pivot Tables are designed to handle data in List format, i.e. column headings in Row 1, and the records underneath.

Solution: format both sets of data as a trial balance
When working with a customer a couple of months ago who had over 1,000 cost centres, it occurred to me that we could use the Paste Special–Transpose command to convert their budgets into List format. This solved the rows and columns format problem.

Secondly, when we brought the actuals into the budget worksheet in order to compare the two, we brought them over in the form of a trial balance. For each cost centre there was be a single column containing the monthly account totals.

This meant that both budgets and actuals now took the form of a set of monthly balances. With actual and budget data now in the same format, it was easy to produce a P&L comparing the two.

Tutorials 1, 2 and 3
I’ve outlined the process in the first three tutorials 1 to 3. Apologies in advance if the method seems a little convoluted, but it does work because it gets both the budget data and the actual data into the same format. Once you’ve achieved that, reporting becomes easy.

The first three tutorials form a series. In the first, consolidate multiple budgets, you are an accountant who has received budgets from three regional heads. You have to consolidate them to produce a consolidated company budget. In addition, you must organise them so that it will be easy to produce reports comparing them to actuals as the year progresses.

In the second tutorial match actuals to budgets you record the first month’s actuals. You also encounter a common problem, which is that the categories used for budgeting by the regional heads don’t tie up exactly with the nominal codes in your own chart of accounts. Therefore you have to match (in IT jargon, “map”) your nominal codes to the budget categories.

In the third tutorial report actuals vs budget you produce the first month’s P&L’s of actual vs budget, and variance. You also add a second month and produce figures for cumulative year to date. Then you use the pivot tables to rotate the figures and display them in pretty well any way you want

If you are short of time, you can cheat by going straight into Tutorial #3. At the beginning you can download the budget workbook as it stood at the end of Tutorial #2 and start from there.

Tutorials 4, 5 and 6
The three tutorials in the middle section are earlier efforts. They still have their uses, but are limited compared to 1, 2 and 3.

In the first Budgeting with Pivot Tables, you are accountant who has to set up monthly budgets for several departments within a company, then use a pivot table to calculate the overall company total budget. You learn how to drill down on the monthly figures to check doubtful balances, then how to revise the individual departmental budgets and recalculate the company totals.

In practice, the limitation of this approach is that you have to key in the budgets yourself. This means extra work compared with Tutorial #1, where you receive the budgets already keyed in by the departmental heads.

In part 2 you add the actuals to the budget via Budgeting with Pivot Tables, part 2. You add actuals to the budgets each month as they occur, then use pivot tables to produce monthly profitability reports comparing Actual versus Budget, and Variance.

The sixth tutorial takes you through the “Multiple Data Ranges” option in Step 1 of the pivot table wizard. Microsoft includes this feature in order to handle data such as budgets where the source data is in spreadsheet form with a list of accounts down the left, and a row of months at the top. In the tutorial Consolidations with pivot tables - putting together a budget you use the Multiple Data Ranges option to combine budgets from several departments into one company summary.

Unfortunately, the Multiple Data Ranges feature is limited and clumsy, and nowhere near as flexible as standard pivot tables. Personally, I don’t use it any more, These days, if I have to use data that is in row and column format, I first transpose it into a list, as explained in tutorial #1, and then analyse it with a standard pivot table.

Tutorial 7 - Financial modelling
The last tutorial is quite different. It deals with Modelling and "What If?" Analysis with Pivot Tables.

You are a magazine publisher who is forecasting future income streams dependent upon variables such as the number of new subscribers anticipated per month, number of pages per issue, estimated advertising rate per page, etc etc. It shows how, by using a pivot table for the underlying structure, you can build a model that is reliable, that is properly documented and whose structure can easily be changed at a later date.

This is a bit of a curiosity. When we first issued it, some readers found problems with double counting in Excel. So I'm not sure how good pivot tables are for financial modelling, but it's worth a look because this is a very unusual but very simple way of using a pivot table.

For a discussion of how pivot tables can be applied in other accounting and reporting situations, see Want to learn about Excel pivot tables? Start here

To summarise, these are the seven tutorials: