Budgeting With Pivot Tables - Part II
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
David Carter's first Budgeting tutorial showed you how to set up company and departmental budgets within an Excel pivot table.
This second "Budgeting with Pivot Tables – Part Two" tutorial takes this to its conclusion, showing you how to record actuals as well and to report Actual against Budget on a Monthly and Year to Date basis, as well as display Variances between the two.
The tutorial is built around two files. The Excel data file holds departmental budgets and is the same one as that supplied with the first Budget tutorial. In addition, there is a Word file which tells you how to analyse the Excel data file via pivot tables.
Please start by printing out your instructions for downloading the files from AccountingWEB. They come in a single-page Word file. When they appear on your screen, select File – Print to print them off. Double click now for these STARTUP instructions.
Using the instructions in Startup, now return to AccountingWEB and download the two files which you will be using in the tutorial. They are the WORD file containing the Excel instructions, and the EXCEL file containing the sample data.
In our experience a small proportion of users will encounter difficulty downloading the files. If you have any problems please email Rob Benson at AccountingWEB and we will email you copies of the data files by return.
INTRODUCTION - BUDGETING WITH PIVOT TABLES, PART TWO
This tutorial follows on from the first Budgeting tutorial, released on 15th December 1999.
Taking the same budget data used in the first tutorial, it shows you how to add monthly actuals, calculate variances, and finally to produce reports comparing Month and Year to Date Actual versus Budget.
Before starting you should already have worked through the first Budgeting tutorial (“Budgeting with Excel Pivot Tables”), which can be found in the Expert Guides section of AccountingWEB.
The tutorial should take you about 30 minutes to work through. If possible, it is a good idea for two people to do it together, one reading the instructions, the other operating the keyboard.
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 5, on Excel 7 (for Windows 95), on Excel 97 or on Excel 2000.
The tutorial was developed and tested on Excel 97. Notes in square brackets  indicate where other versions of Excel differ. If you are not sure which version you have, find out now by starting up Excel. At the right of the main menu click on Help, then About Microsoft Excel. The version is at the top of the screen.
Note however that the variances will be calculated using the “Formulas” feature first introduced in Excel 97. So users of Excel 5 or Excel 95 will have to omit several sections.
Ideally, the instructions in this tutorial should be sufficiently clear for you to be able to work your way through to the end. If you find any of the text unclear, please read it a second time. If after a second reading it is still unclear, please email me a note of it at email@example.com. If any section is regularly causing difficulty, it is no problem to revise it.
CORRECTING YOUR MISTAKES
If you find that the instructions seem OK, but at some point what’s on your screen doesn’t seem to tally with what the tutorial says, try clicking several times on Excel’s Undo icon (the left curving arrow in the icon bar at the top). This will cancel your previous steps. But if the error does not immediately come to light, it may be better not to try to correct any errors but to go back and start over again.
WHAT YOU WILL LEARN
The objective of the tutorial is twofold. In the first place it’s to give you an overall concept of how pivot tables can be used for a particular application – budgeting, sales analysis, financial reporting etc.
First time around you cannot expect to memorise each section, and probably by the end you will have forgotten what you did at the beginning. So if you want to teach yourself how these pivot tables work in detail, be prepared to go work your way through the tutorial two or three times. Second or third time around you will find that a lot of things fall into place that didn’t really register earlier because then you were just concentrating on following the instructions.
1. CREATE “ACTUALS” WORKBOOK
Start up Excel. Open the file: Budgdem
The Excel document on your screen should have the headings: NLGROUP, NLCODE, NLDESC, CC, DEPT, YR, PD, BUDGET, COMMENT
We’ll save the file under a different name and in the latest file type. Select: Save As
Save as Type: Excel Workbook File name: Actuals
Click on the “2” brick to the left of Row 2. The whole row is highlighted
Freeze the column headings by selecting from the main menu: Window - Freeze Panes
2. MOVING AROUND THE FILE
Click onto cell A1. Now go to the bottom of the file as follows:
Find the second “Ctrl” key at the bottom right of the keyboard under the Enter key. Hold it down with the thumb of your right hand.
Now tap Down arrow with the middle finger of your right hand.
You go to the bottom row of the spreadsheet.
The number of the bottom row is 101. Therefore there are 100 records in this data file (Row 1 contains the column headings).
Return to the top of the file, cell A2. (hold down Ctrl key; press Home key)
3. GENERATING THE PIVOT TABLE
First, we’ll build up the pivot table as in the first Budget tutorial. Click anywhere on the data.
From the main menu at the top of the screen, select :
Data - Pivot Table Report [Excel 2000 – Pivot Table and Pivot Chart Report]
The Pivot Table Wizard, Step 1 of 4 screen appears. Click on Next. [Excel 2000 – Step 1 of 3]
Step 2 of 4 The Range box should say: $A$1:$I$101.
[Earlier versions of Excel sometimes didn’t pick up the range automatically. If the Range box is blank or says “Database” type in $A$1:$I$101].
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:
NLGROUP into the ROW area.
NLCODE into the ROW area below
NLDESC into the ROW area below
CC into the ROW area below
PD into the COLUMN area
DEPT into the PAGE area
BUDGET into the DATA area.
[When dropped into the DATA area, BUDGET should 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. [if you are using Excel 2000, OK, then Finish]
The pivot table is generated. Excel has taken the 101 budget records in the MASTER worksheet and generated a pivot table that summarises them by nominal group, code and description.
Look at the bottom left of your screen. The name of the new worksheet is Sheet4.
Right click on Sheet4 at the bottom of the screen. A menu appears. Rename the worksheet PIVOT.
Now right click back onto the Sheet1 worksheet which contains the source data. Rename it MASTER.
Left click back onto the PIVOT worksheet.
4. TIDYING UP THE PIVOT TABLE
The NLCODE and NLDESC columns are both totalling. Remove the totals as follows:
Double left click on the grey NLCODE field button in B4. The “Pivot Table Field” box appears.
Under Subtotals change from Automatic to None. OK The Totals disappear .
Now double left click on NLDESC in C4. Change Subtotals from Automatic to None. OK.
Widen columns A and C.
[Excel 97] Now left click on NLCODE in cell B4. The column is highlighted.
[Excel 2000]. Move the mouse pointer above NLCODE in cell B4 until you get a black down arrow, then left click. The column is highlighted.
Centre the nominal account codes by clicking the Centre icon.
Right click on any number within columns E to H.
A menu appears. It has “Format Cells” either at the top or in the middle.
From the menu select: Field. [Excel 2000 = Field Settings, Excel 5 or 7 = Pivot Table Field]
The “Pivot Table Field” box appears. From the list of options on the right, select: Number
The “Format Cells” box appears. From the “Category” list, select: Number Then:
Decimal Places = 0 - Use 1000 separator? YES - click on the -1234 in red at the bottom of the list - OK - OK.
Commas now separate the thousands.
5. SHADE SUBTOTALS AND COLUMN HEADINGS (Excel 97 and Excel 2000 only)
Click on “71-SALARIES total” in cell A21 . All the sub-total lines are highlighted
Move the mouse pointer to the grey “21” brick opposite cell A21, “71-SALARIES Total”. Move it gradually to the right until it assumes the shape of a thick horizontal black arrow. Then left click the mouse. All the sub-total lines are highlighted
To add shading, click on the yellow “Fill Color” icon in the bottom row of icons, second from right (the one that looks like it’s pouring a can of paint).
Now click anywhere to remove the highlighting. The “Total” lines are all shaded yellow.
In addition, we’ll shade and centre the monthly column headings, as follows:
Click on the PD field button in cell E3. The monthly column headings are highlighted.
[Excel 2000 – move the pointer over the grey PD field until it turns into a thick black down arrow]
Apply the yellow Fill Color icon again. Or if you prefer another colour, click on the down arrow to the right of the icon, and select a colour from the palette. Personally, I like light blue or light green in the bottom row.
Click on the Centre icon. Then click the B Bold icon. Click anywhere to remove the highlight.
Whenever you apply colour to an area, Excel automatically removes the gridlines. However, we want to retain the gridlines between Apr, May and Jun. Therefore:
Highlight the monthly column headings again by clicking on the PD field button.
Find the “Borders” icon to the left of the Fill Color icon (looks like four quarters).
Click on the down arrow to the right of the Borders icon.
Choose the “All Borders” button, in the bottom row, second from left.
Click anywhere to remove highlighting. The gridlines have returned.
At present, the pivot table shows detail at individual Cost Centre level. We will hide this:
Double right click on the NLDESC field button in C4. From the menu select:
Group and Outline – Hide Detail. The budget is summarised at Nominal Code level.
5. ENTERING THE ACTUALS
The Budget is set up. Suppose that two months have now passed. You wish to record the two months’ actuals and run a report comparing Actual versus Budget and any variance.
We will do this now. Click back onto the MASTER budget database.
To record the Actuals create a new “ACTUAL” column as follows:
Highlight the COMMENT column by clicking on the “I”. Select: Insert – Columns.
A blank column I appears. In cell I1 type ACTUAL
The simplest way to enter Actuals is to key them in by hand from the Trial Balance report each month.
However, in this exercise for the sake of speed we are going to calculate the ACTUAL column as being the BUDGET figure plussed up by 15%.
We will be entering Actuals for periods 1 and 2 only. Therefore:
Click onto cell G10 in the PD column. Click the AZ icon. The worksheet is sorted by period.
In cell I2 type =H2*1.15 and press Enter. The result of 1581.25 is displayed.
Click back onto 1581.25 in cell I2.
Note that the Formula Bar shows the formula =H2*1.15
Place the mouse pointer on the RH bottom corner of I2 until it becomes a black cross.
Holding down the left mouse button, drag the formula down to Row 68 (where period 2 ends).
Actuals are calculated for Periods 1 and 2.
Cells I2 to I68 should still be highlighted. We will save the formulas in them as values:
Select: Edit – Copy. Moving lines appear around column I.
Select: Edit – Paste Special – Values. OK. Press Escape key to remove the moving lines.
The Formula Bar now displays 1581.25 not =H2*1.15.
Values have replaced formulas. Click anywhere to remove the highlighting,
6. ADDING THE ACTUALS TO THE PIVOT TABLE
We will now revise the pivot table to include the actuals. Click back onto the PIVOT worksheet.
Right click on the pivot table. Select: Refresh Data
Right click again and select: Wizard The ROW – COLUMN – DATA area appears
Drag and drop ACTUAL into the DATA area above Sum of BUDGET.
[When dropped into the DATA area, ACTUAL should say “Sum of ACTUAL”. If it says “Count of ACTUAL”, double click on Count of ACTUAL. A box appears. Change “Count” to “Sum”]
Finish The ACTUAL figures now appear in the Pivot Table above the BUDGET figures.
We’ll tidy up the ACTUALs. The first ACTUAL value is 31059.2 in F5. Right click on it.
From the menu select: Field. The “Pivot Table Field” box appears.
Select: Number - Number - Decimal Places = 0 - Use 1000 separator? YES - -1234 in red at bottom - OK - OK.
The ACTUAL numbers are now displayed in the same format as BUDGET.
7. COPY THE PIVOT TABLE
At this point we will make a copy of the PIVOT worksheet. To do this:
Right click on PIVOT at the bottom. From the menu select: Move or Copy
The Move or Copy box appears. Tick the Create a Copy box at bottom left. OK
A new sheet, PIVOT(2) is created. Right click and Rename it PERIOD2.
8. CALCULATE VARIANCES FROM BUDGET (Excel 97 or Excel 2000 only)
We’ll now use the Formulas option to calculate any Variances from Budget.
Right click anywhere on the PERIOD2 pivot table. The menu appears.
Select: Formulas – Calculated Field The “Insert Calculated Field” box appears.
In the “Name” box, replace Field1 with VAR’CE.
In the “Formula” box, click to the left of the 0. Delete the 0
In the list of Fields, find BUDGET and highlight it. Then click on Insert Field.
= BUDGET appears in the Formula box
To the right of BUDGET, type a minus sign.
Go back to the list of fields. Find ACTUAL and highlight it. Click on Insert Field
The Formula box should now display the formula =BUDGET-ACTUAL
Click on Add, then OK
“Sum of VAR’CE” now appears beneath “Sum of BUDGET” and “Sum of ACTUAL”
9. DISPLAYING ACTUAL, BUDGET AND VARIANCE SIDE BY SIDE
ACTUAL, BUDGET, VAR’CE need to be side by side rather than above each other. To do this:
Left click on the grey Data field button in cell E4 and hold down the mouse key.
Keeping the LH mouse key depressed, drag gently upwards until a horizontal “brick” appears.
Drag the brick onto cell G3 to the right of the PD field. Then release.
Sum of ACTUAL, Sum of BUDGET and Sum of VAR’CE all now appear in Row 4.
Depress the LH mouse on the Data field button in cell E4. Drag it down a few millimetres until you see a horizontal “brick” and below it, an icon with a slab of blue to the left.]
[Excel 2000, continued. Keeping your eye on the slab of blue, drag the brick up and to the right towards cell G3. When the slab of blue changes to horizontal, release the mouse key.
The Sum of ACTUAL, Sum of BUDGET and Sum of VAR’CE all now appear in Row 4.
To get the columns in the correct order we need PD to be to the left of Data. Sometimes this happens naturally, at other times PD ends up to the right. I confess I cannot predict which way it will work out each time.
So, if you have Data in E3 and PD in F3, interchange them now as follows:
Left click on the PD field button in F3. Keep the mouse button depressed..
Now drag PD over to the left of the Data field button until a horizontal brick appears. Release.
The pivot table is recalculated. PD is now to the left of Data. Column E is Sum of ACTUAL, Column F is Sum of BUDGET and Column G is Sum of VAR’CE. This is what we want.
10. CHECKING WHAT HAPPENED VIA THE UNDO AND REDO ICONS
You may not be quite sure what happened.
Click on the Undo icon at the top of the screen (the icon that shows a bent arrow pointing left).
You go back one step, showing the pivot table with PD to the right of Data
Now click on the Redo icon (the icon that shows a bent arrow pointing right).
You go forward one step, showing the pivot table with PD to the left of Data.
Click Undo and Redo a few times. Finally, go back to the correct state, where PD is to the LEFT of Data and Sum of ACTUAL, BUDGET and VAR’CE are adjacent to each other.
The Undo and Redo icons are so useful. If ever you make a mistake when working through one of these tutorials, simply click the Undo icon to reverse out the error and start again. Also, you can use Undo to go back a step or two to remind yourself of what you did a moment ago, then click Redo to return to the present.
11. TIDYING UP THE COLUMNS
We’ll tidy up the pivot table a bit, making the column headings narrower, and putting brackets rather than minuses around negative variances.
Right click on cell E8. Select: Field The “Pivot Table Field” box appears.
At the top of the box, Sum of ACTUAL is highlighted in the Name field.
Click the mouse pointer to the right of the “f” of 'Sum of'.
Delete everything to the left of the mouse pointer so that the box reads ACTUAL (note that there must be at least one blank space before ACTUAL, otherwise Excel will refuse to accept the change and report that the “Pivot Table Field name already exists”.)
Click OK. Column E now has the heading ACTUAL rather than Sum of ACTUAL.
Make the ACTUAL column E narrower.
Now right click on F8. Select: Field Change Sum of BUDGET to BUDGET. OK
We’ll do the same with Sum of VAR’CE, and also put brackets instead of minuses.
Right click anywhere on column G. Select Field to bring up the PivotTable Field box.
As previously, change Sum of VAR’CE to (space) VAR’CE. Do NOT click on OK.
Instead, click Number The “Format Cells” box appears.
From the list at the left, select Custom. The “Type” box at right should show: #,##0_ ;[Red]-#,##0
Find the semi-colon in the Type box. To its left is the format of positive numbers. To its right is the format of negative numbers. So we are interested in the right half [Red]-#,##0
[Red]-#,##0 means that the column is set to display negative numbers in red, with minuses in front. We’ll keep the red, but replace the minuses with brackets.
To do this, click into the right of the ”Type” box.
To the right of the semi colon you need to change from [Red]-#,##0 to [Red](#,##0)
Make the changes carefully, moving via Left arrow key, then Right arrow. Then click OK - OK.
- NOTE: The final Type box should read: #,##0_ ;[Red](#,##0)
You should now see brackets in the VAR’CE column.
12. DISPLAYING IN “PERIOD” AND “YTD” FORMAT
Scroll across to the “Total” columns N, O and P.
The variances in column P are incorrect because “Total Budget” includes Period 3.
We are reporting on Period 2. To make the variance correct we need to “Hide” the Period 3 budget.
[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 03_Jun to highlight it. OK.
The June columns disappear. The ”Total BUDGET” in column L is now correct.
[Excel 2000] On the grey PD field button in E3 there is a down arrow. Click on it.
Periods 1, 2 and 3 are listed, all ticked. Remove the tick on Period 3. OK
The Period 3 columns disappear. The “Total BUDGET” in column L is now correct.
Left click again on the PD field button in E3. Rows 01 Apr and 02 May are highlighted.
[Excel 2000 – thick down arrow]
Click the ZA icon. 02_May now appears to the left of 01_Apr.
Now we’ll hide the April columns. Click on column headings H, I and J to highlight.
Select: Format – Column – Hide. The April olumns disappear.
Click back onto cell A1 (holding down right Ctrl key, press the Home key)
13. DISPLAY VARIANCES BY DEPARTMENT
The pivot table shows Period 2 and Year To Date figures for the company as a whole.
To see departmental performance, click on the down arrow in cell B1 next to the DEPT Page field
A list of departments appears. Select MARKTG. The figures for Marketing dept appear.
To see how Marketing performed at the detailed Cost Centre level:
Double right click on NLDESC in C5. From the menu select: Group and Outline - Show Detail
The pivot table now displays variances down to individual Cost Centre level.
Click on the down arrow in B1 again. The list of departments appears.
This time select ADMIN. The figures for ADMIN are displayed
Click on the down arrow in B1. Select (All) to see the entire company once more.
Now you wish to suppress Cost Centres. Double right click on NLDESC in C5.
From the menu select: Group and Outline - Hide Detail
The Cost Centre details disappear and totals are displayed at Nominal Account level again.
14. COPYING THE PIVOT TABLE WITHOUT UNDERLYING DATA
The Totals in columns K, L and M read Total ACTUAL, Total BUDGET etc. It would be better if they read YTD. However, Excel does not allow you to click on a “Total” heading and change it.
For personal use, this doesn’t matter too much. But if the pivot table is going to be published and circulated to management, it does.
Before publishing a pivot table you can give it a final polish by copying the pivot table without the underlying data. You can then reformat it any way you like. To do this:
Right click for menu, then choose: Select – Entire Table. The whole pivot table is highlighted.
From the main menu: Edit - Copy. Moving lines surround the pivot table.
Now open a new workbook via: File - New A blank worksheet appears.
The cursor should be in cell A1. Select: Edit - Paste Special - Values - OK
The pivot table is copied into the new workbook, but all formatting has been lost.
Left click back onto cell A1. The highlighting disappears.
Now select: Edit - Paste Special - Formats - OK. The formatting has re-appeared.
Go back to the “Actuals” pivot table via: Main menu - Window.
Click anywhere to remove highlighting. Escape to remove Copy lines. File - Save your work.
Close down Actuals via File – Close
On the screen you see the new Workbook, probably named Book2 (see at top of screen)
15. FORMATTING THE PIVOT TABLE FOR PUBLICATION
Your new copy of the pivot table can be changed just like any spreadsheet. Therefore:
Where necessary, widen any columns.
Highlight the Period 1 columns H, I and J. From the main menu: Edit - Delete.
Into cells F4 and G4 type (or copy): 02_May
Into cells H4, I4 and J4 type: YTD
Into cells H5, I5 and J5 type: ACTUAL, BUDGET and VAR’CE
Click on the “3” brick at the left to highlight Row 3
Select Edit – Delete. The row is deleted.
Row 3 is still highlighted. Click on the B icon to make the YTD headings bold.
Click on the “4” brick next to NLGROUP in A4. Row 4 is highlighted.
Click on the B icon to make the column headings bold.
Highlight cells H3 to J4. Then click on the Centre icon.
In addition we’ll insert a blank line below each Subtotal
Click on the “11” brick to highlight the row 72-MARKETING.
Select: Insert – Rows. A blank line appears below 71-SALARIES, but it is yellow.
To remove the yellow, click on the down arrow to the right of the “Fill Color” icon.
Click on the No Fill box at the top of the colour palette. The yellow disappears.
Insert blank lines below the yellow “Total” lines for 72-MARKETING, 73-ESTABLISHMENT, 74-ADMIN and 75-FINANCIAL
In the bottom row delete “Grand Total” in cell A35.
In cell C35 type: TOTAL OVERHEADS
Highlight cells C35 to J35. Click on the B for Bold icon.
Click down arrow to the right of the Borders icon. Choose “Thick Box Border” at bottom right..
The bottom line of numbers is boxed and emboldened.
To remove the gridlines to the left of TOTAL OVERHEAD, highlight cells A36 and B36. Then:
Click down arrow on the Borders icon. Select “No Border” at top left. .
Click anywhere. The gridlines have disappeared.
16. PRINTING THE FINAL REPORT
Finally, we’ll print out the report.
First, click the Print Preview icon to see the report on screen [or from menu, File – Print Preview]
It needs to be printed in Landscape. Therefore select:
Setup - Page - Landscape
AND on the same box: Scaling - Adjust to 85% normal size - OK Then, select:
Setup - Margins - at bottom left, tick on the Horizontally box - OK Then, select:
Setup - Sheet - check the Gridlines box - OK. Then select:
Setup - Header/Footer then click on: Custom Header. Type in the following:
Left Section: Type: Printed: then click on the 8/7 icon. &[Date] appears
Right section: Type: Page: then click the # icon - type: of - click on the ++ icon.
Center section: Type in capitals OVERHEADS PERIOD 2 – ACTUAL vs BUDGET
Highlight this text with the mouse .
Click on the A icon. The “Font” box appears
Select Bold and 10 OK
Click OK at top right - OK The Print Preview screen reappears.
Select Print to print the report.
OK. It’s time to take break. Save your work via: File – Save As: Period 2. Relax
17. WHERE DO I GO FROM HERE?
At time of writing (1st May 2000), several tutorials on Excel pivot tables are available on Accountingweb.co.uk. They are located in the Expert Guides section from the main menu.
If your interest lies in Accounting, try the “Audit and Financial Reporting Reporting with pivot tables” tutorial. This takes 2,000 lines of demonstration data from the Sage Line 50 accounts package and shows how to audit it and check for errors, then produce financial reports.
Alternatively, try the “Analysing Sales Data with Pivot Tables” tutorial. This takes 4,000 sales invoice records from an accounting package and shows how to analyse sales by product group, sales by customer, sales by customer by month, etc etc.
--------- END OF TUTORIAL ---------
DAVID CARTER, May 1st 2000.
Based in Hemel Hempstead, Herts, David Carter is an independent consultant who installs accounting and order processing systems into small and medium size businesses. He also writes reviews of accounting and ERP packages for the computer press. Since 1996 he has been using Excel pivot tables in areas such as financial and management accounting, budgetting, job costing, sales analysis, order book analysis, and production control. He would be pleased to have any feedback from you on this tutorial and may be contacted on firstname.lastname@example.org