Seven of David Carter's self-teach tutorials on Excel pivot tables are about budgets. The first ever AccountingWEB tutorial back in 1999 was entitled Budgeting with Pivot Tables, while the most recent are the series of three tutorials issued in February 2007. In this aricle he sets them all in context.
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 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 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 Tutorial #1: Consolidate multiple budgets in a pivot table
