Excel Tip: Budgeting With The Pivot Table
This is one in a series of Excel Pivot Table Tutorials developed by AccountingWEB software consultant David Carter. The tutorials were originally developed for a UK audience, but the techniques work equally well for US needs.
Be sure to check out Carter's other pivot table tutorials, including:
- Budgeting with the Excel Pivot Tables
Excel expert David Carter recently conducted an online workshop with AccountingWEB's sister site in the United Kingdom on using Pivot tables in Excel. The tutorial he provided has turned out to be THE most read feature of the year in AccountingWEB, and we thought we'd bring it to this side of the Atlantic so CPAs across North America can benefit from this tutorial as well. Ladies and gentlemen, may I present David Carter.
Budgeting with pivot tables - Tutorial by David Carter
For the management accountant, Excel pivot tables represent the most important development in IT since the original invention of the spreadsheet.
If you are regularly required to analyse data, or to prepare and present management reports, they are an indispensable tool. Particularly valuable is the facility to export raw transactions out of a copmany's accounting system (Sage, Pegasus, Sun etc), import them into Excel, then use pivot tables to analyse the transactions and present the results in a customised report.
Microsoft first introduced pivot tables in version 5 of Excel, released in 1994. The basic design has changed little since then, but the number of records that can be analysed has consistently increased. Excel 5 could handle a maximum of 16,000. Excel 97 went up to 65,000 records but can handle more if you first load the data into an Access database. In practice, the only limit now to the number of records a pivot table can handle is the physical memory of your PC, so pivot tables are suitable for use in all but the largest applications.
But if pivot tables are so important, why are they so little known? It takes only an hour or two to learn how to use a pivot table to generate a report. However, the difficulty comes in knowing what data you need to produce that report in the first place. And then you must know how to find and retrieve that data from your particular accounting package. Pivot tables are, in fact, but a small part of a whole new science, that of converting transaction records into management informatoin. To my knowledge, there are no published guidelines or rules for this new science and most practitioners out in the field are simply working things out as they go along.
BUDGETING: STEP-BY-STEP TUTORIAL
Download the Excel file to your own computer.
You can also download these instructions as a Word file, or follow them on screen.
In this tutorial you are an accountant setting up next year’s P and L budget for your company. For simplicity’s sake the number of nominal accounts has been reduced to a minimum and the budget is only for 3 periods. The company is made up of 4 departments, each of which contains only 2 or 3 staff. Each member of staff is treated as a separate cost centre.
The departments and their staff are:
– Administration (Arthur and Anita).
– Design (Diana, David and Doug )
– Marketing (Mick and Maureen)
– Production (Pat, Paula and Peter)
The tutorial should take you about 30 – 45 minutes. If possible, it is a good idea for two people to work through it together, one reading the instructions, the other operating the keyboard.
The tutorial assumes that you are on a PC with Excel currently loaded on the screen. The instructions are given in step by step detail, but it will be helpful if you are tolerably familiar with Excel.
You will make extensive use of both left and right mouse buttons. When I say “right click” I mean click the right mouse button. When I say “click” on its own, I mean “click the left mouse button”.
You can run this tutorial on any version of Excel from Excel Five (released in 1994) onwards, that is, on Excel Five, on Excel 7(for Windows 95), on Excel 97 or on Excel 2000
To avoid confusion, the tutorial does not cover any areas where the versions of Excel markedly differ (principally that of formatting the pivot table). Where I know of differences I have included a note in the text, but readers should be aware that the tutorial was developed and tested on Excel 97.
CREATING THE BUDGET DATABASE
The Excel document before you should have the headings: NLGROUP, NLCODE, NLDESC, CC, DEPT, YR, PD, MTH, BUDGET, COMMENTS
Go to the bottom of the spreadsheet. There should be 101 rows. Don’t worry if there’s one or two missing.
Calculate the total for column I, the BUDGET column. It should add up to £187,633 or thereabouts.
Make a note of the the total on a piece of paper.
Now delete the total from the spreadsheet.
Finally, find the worksheet number at bottom left. It is probably Sheet1. Right click on Sheet1.
A menu appears. Select Rename. Rename the worksheet MASTER.
Click back onto the data in the worksheet.
GENERATING THE PIVOT TABLE
We’ll build up the pivot table step by step. From the main menu at the top of the screen, select :
Data - Pivot Table Report
The Pivot Table Wizard, Step 1 of 4 screen appears. Click on Next.
Step 2 of 4: Click on Next
Step 3 of 4: The COLUMN –ROW -DATA box appears. To the right are all the column headings of your spreadsheet [note: if you are using Excel 2000, you must first click on Layout]
Drag and drop these headings as follows:
NLCODE into the ROW area.
BUDGET into the DATA area.
[When dropped into the DATA area, BUDGET should now say “Sum of BUDGET”. If it says “Count of BUDGET”, double click on Count of BUDGET. A box appears. Change “Count” to “Sum”]
Now click on Finish. A list of Nominal Codes appears with a total beside it. The total at the bottom of the sheet is the total you noted down.
Excel has taken the 101 budget records in the MASTER worksheet and generated a pivot table that summarises them by nominal code
Look at the bottom left of your screen. To create the pivot table, Excel has generated a new worksheet to the left of MASTER.
Click back onto the MASTER worksheet to confirm that your original Budget Database is still there.
Now right click back to the new sheet containing the pivot table. Rename this worksheet PIVOT.
Nominal codes on their own don’t tell us much. Let’s add the Nominal Description as well:
Click anywhere within the pivot table., then right click the mouse.
A menu headed “Format Cells” appears..
From the menu, select Wizard. The COLUMN- ROW-DATA box re-appears.
Drop NLDESC below NLCODE in the ROW area. Then Finish.
The nominal description is displayed on the pivot table, but the screen is not very clear as the NLCODE is totalling. These totals need to be removed:.
Double right click on A2, the grey NLCODE field button. The menu appears
[Note to Excel 2000 users. Cell references within pivot tables are always two rows lower in Excel 2000 compared with other Excel versions . Cell A2 therefore signifies cell A4, and so on].
Select: Field. In the Subtotals area on the left, change from Automatic to None. OK
That looks better. Now left click on cell A2. The column is highlighted.
Centre the account codes by clicking the Centre icon.
Next we’ll break the Total down by period: Right click anywhere within the pivot table (you must always click within the pivot table. Clicking outside it brings up the wrong menu).
Select Wizard. The COLUMN-ROW-DATA box re-appears.
Drop PD into the COLUMN area. Then Finish. The pivot table breaks down by Period.
Now right click – Wizard once more. This time drop NLGROUP above NLCODE, so that the ROW area contains NLGROUP, NLCODE and NLDESC.
Finish. The pivot table is divided into “71-Salaries, “ 72-Marketing” etc, each with a total.
[Excel 97 users only] Try shading in these “Salaries”, “Marketing” total lines as follows:
Click on cell A8, “71-Salaries”. All the sub-total lines are highlighted
Click on the yellow “Fill Color” icon in the bottom row of icons, second from right.
Now click anywhere to remove the highlighting.
The “Total” lines are shaded.
DRILLING DOWN ON A BALANCE
One area where screen-based reports like pivot tables score over paper-based reports, is in the area of “drill-down”. If you wish to query a balance, it is a simple matter to double click on it and drill down to see the underlying entries.
For example, the figure of £12,000 for Internet costs in period 2 (Cell E12) looks rather large. To query it, simply:
Double click on cell E12. The originating transaction appears and explains: “Set up new web site”
Your drill down has generated a new worksheet. Click back onto PIVOT.
Try another example: In D10 the figure of 10,960 for PR & Promotions looks very high. Double click on it.
The balance is made up of the standard monthly budget of £1,500, plus £9,460 for the Olympia exhibition.
Click back onto the PIVOT worksheet.
In our example we are using only 100 records. But where the pivot table is summarising several thousand transactions from an accounts package, this ability to drill down on any balance is very useful. For example, if you use pivot tables to report monthly costs to departmental managers, to query a figure they simply have to double click on it to see a list of the transactions that make up the total.
BREAKING DOWN BY DEPARTMENT
So far we’ve presented the budget in a standard P & L format . Now we’ll break it down by departmental totals:
Right click – Wizard. Remove PD from COLUMN by dropping it anywhere outside the box.
Drop DEPT into the COLUMN area. Finish.
Now you see the totals for ADMIN, DESIGN, MARKETING and PRODUCTION.
But we may wish to analyse by both Department and Period. Excel allows you to add another dimension of analysis via “Page” fields. Right click – Wizard:
Take DEPT and drop it onto the PAGE area. Put PD back into the Column area. Finish.
The original format returns, broken down by period. In cell A1 it says DEPT. In B1 it says (All)
DEPT is a Page field. To see how it works, click on the down arrow in the right of cell B1.
ADMIN, DESIGN, MARKTG, and PRODN appear. Highlight MARKTG and click.
The pivot table now shows a mini P & L just for the Marketing department.
Click on the B1 down arrow to see the budgets for ADMIN, DESIGN and PRODN.
[Note that the (All) option signifies the company total.]
Click back onto MARKTG once more.
We will now break down the budget farther into individual cost centres (CC). Right click – Wizard
Drop CC underneath NLDESC. Then double click on NLDESC. Change “Subtotals” from Automatic to None. OK. Finish
The pivot further subdivides to show the costs of the individual members of the Marketing department, Mick and Maureen. Note that a CC of (blank) means that the budget was allocated only to the Marketing department itself, not to any individual within Marketing..
HIDE DETAIL and SHOW DETAIL
The larger your spreadsheet becomes, the more levels the pivot table is liable to acquire.
As the pivot table grows in complexity, the Hide Detail and Show Detail commands allow you to expand and collapse it between different levels. To see this, first click on cell B1 and select (All).
This displays the budget for the whole company down to the (lowest) Cost Centre level.
To collapse the pivot table up to the highest level, simply:
Double right click on A4, NLGROUP. From the menu select: Group and Outline - Hide Detail
The pivot table is summarised at top level – 71-Salaries, 72-Marketing, 73-Establishment etc..
To see more detail for the Salaries group:
Double left click on cell A5, 71-Salaries. Excel expands the Salaries detail down to CC level.
To revert to the higher level, now double left click in the gap below 71 – Salaries (eg A6 to A20)
Now show the entire pivot table in full detail once more:
Double right click on A4, NLGROUP. Group and Outline - Show Detail
The full company detail is displayed once again.
HIDING A COLUMN
Sometimes, also, you might want to see cumulative totals for selected periods. For example, suppose that you now want to see cumulative totals for Periods 1 and 2 only:
[Excel 5, Excel 7 or Excel 97] Right double click on PD in cell E3.. Select Field:
At the bottom of the box it says Hide Items. Click on 3 to highlight it. OK.
The period 3 column disappears and the cumulative totals for each NLCODE are recalculated.
[Excel 2000] Left click on the grey PD field button in E5. Periods 1, 2 and 3 are listed, all ticked.
Remove the tick on Period 3. OK
The period 3 column disappears and the cumulative totals for each NLCODE are recalculated.
REVISING THE BUDGET
The greatest advantage of using pivot tables for budgetting is the control they give you when making any revisions. It is very easy to change a budget figure, and you can instantly see the results of the change. Nor are there any formulae whose logic must be rechecked each time you revise the format of the budget.
Suppose, for example that Marketing want to amend their budget. Firstly, unhide period 3 as follows:
Double right click on PD in cell E3. Select: Field. Click on 3 to remove the highlight. OK
Period 3 returns. Now click on the down arrow in B1 and select MARKTG.
You should see the budget for Marketing, broken down to the cost centre (Maureen and Mick) level.
Suppose that the figure of £50 in F12, Direct Mail, period 2 was an error and should have been £500.
Make a note of the NLCODE – 72030.
Now click onto the MASTER worksheet containing the original 101 budget records.
Left click on anywhere in the data, then in the main menu select: Data - Filter - Autofilter
Click onto the down arrow beside NLCODE in cell B1. A list of NLCODES appears.
Highlight 72030 and click.
Excel finds 3 records (see message at bottom left). [If the screen is blank you may need to scroll up to Row 1 to see them.]
Change the Period 2 (May) budget figure from 50 to 500.
Now click back onto PIVOT. Check that the figure for Direct Mail, Period 2 is still 50.
To update the change, right click on the pivot table - Refresh Data
The 50 changes to 500 and all the related column and row totals are updated.
Suppose that in period 3 Doug is going to move out of Design department and into Marketing. Also, his salary will increase from £1,917 to £2,500.
Click onto the MASTER worksheet. The filter is still on. The down arrow in cell B1 is blue, indicating that the filter is being applied to this field.
Click on the blue down arrow, then select (All) from the top of the list. All the records reappear
Click on the down arrow in D1. From the list of CC’s, select DOUG
Three records for Doug come up. In the third row (June) change DESIGN to MARKTG, and change 1917 to 2500.
Now click onto the PIVOT worksheet. Right click onto the pivot table, then: Refresh Data.
Doug is added to the Marketing budget for period 3. The totals are all recalculated.
While it may take you some time for you to set up your first budget via pivot tables, thereafter you can use it as a template to quickly produce others.
For example, six months later management ask you to produce a revised forecast for the year. One of the changes is that Doug’s salary increase in June is £3000, not £2500. Do this as follows:
Click onto the MASTER worksheet. Autofilter is still on.
Switch Autofilter off via: Data - Filter - Autofilter Click on Autofilter to switch it off.
You are going to insert a new column. Click anywhere in column J, COMMENT. Then:
Main menu - Insert - Columns. An empty column J is created
Now Copy (not Cut) the BUDGET values into the empty Column J, as follows:
Click on column heading I to highlight the column, then Edit - Copy
Click on column heading J, then Edit - Paste
Click on cell J1 and change BUDGET to REVBUDG.
You now have the original and revised budgets side by side. Suppose Doug’s new salary is 3000
Left click anywhere on the data, then: Data - Filter - Autofilter
Click onto the D1 down arrow. Select DOUG from the list.
Change the REVBUDGET for Doug in June in Column J from 2500 to 3000.
Click onto the PIVOT worksheet. Right click on the pivot table, then Refresh Data
Nothing happens, because the pivot table is displaying the sum of BUDGET (see cell A3)
Right click on the pivot table again, then Wizard
The COLUMNS - ROW -DATA box appears. REVBUDG is now among the column headings
Drop REVBUDG below BUDG in the DATA area. Finish.
Your Pivot Table now displays both the original and the revised budget.
Excel offers numerous ways of comparing different budgets, but they go beyond the scope of this basic tutorial. For today’s purposes, simply now: right click - Wizard - remove REVBUDG. Finish
The pivot table now displays the original budget only.
PRINTING THE DEPARTMENTAL BUDGETS
Finally, we will print out the company and departmental budgets as follows:
First, click on the B1 down arrow and select All to show the total company budget.
Right click on the pivot table - select the Show Pages option at the bottom
The Show Pages box appears.
Keep your eyes on the bottom left of the screen, because this is going to be quick. Now click OK
Four new worksheets are generated for ADMIN, DESIGN, MARKTG, PRODN.
The tab on leftmost bottom worksheet ADMIN should be highlighted. Hold down your Shift key.
Click onto PIVOT. All five worksheets are now highlighted.
Columns A and C need to be widened. Widen them.
Select File - Print to print the budgets for the company and the 4 departments
David Carter, known at AccountingWEB as "king of the pivot table", is a software consultant specialising in accounting applications. Based in Hemel Hempstead UK, he has installed systems for a variety of clients and helped several of them to apply pivot tables to their financial reporting tasks. He can be contacted by email at firstname.lastname@example.org or by phone on +44 1442 216778.