Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

Budgeting with Pivot Tables #1: Consolidate multiple budgets

by
22nd Feb 2007
Save content
Have you found this content useful? Use the button above to save it to your profile.

Data held in row and column format like budgets is difficult to analyse in a pivot table. But if you tweak the layout of your budget and then use the Paste Special–Transpose command to convert it into a list, you can achieve excellent results. David Carter shows how.

This tutorial will help anyone who has to analyse costs and revenues over multiple profit centres, and then produce P&Ls for each of them showing actual against budget. The more profit centres you’ve got, the better it gets. I recently had a customer who runs a thousand pubs across the country and this method worked a treat.

In this first session we will take budgets for three regions and consolidate them in a pivot table. In a later session we will add actuals and calculate variances.

Your task
You work for a company that sells audit and consultancy services. You have three regional offices in London, Birmingham and Edinburgh. Each region has supplied you with a forecast of anticipated costs and income for the next four months. Your job is to consolidate these budgets into a company total, and to report Profit and Loss versus budget as the financial year progresses.

Download the Budget Tutorial workbook now.

1. Contents of the workbook
The workbook contains four worksheets. Three contain the budgets you have received from London, Birmingham, and Edinburgh. You will take these budgets, tweak the layout, transpose the data, then collect all three together into a single Combined worksheet. From this Combined worksheet you will produce your consolidated budget and (later) comparison with actuals.

First, look at the London worksheet. This contains the original budget you received from the London office. It is in the standard rows and columns format of a spreadsheet.

Now look at Birmingham. The format is similar to London, except that you have modified the original budget by filling in the top three rows with data.

In addition, there is a separate set of data in rows 19 to 23. This has been created by transposing the budget in A1:E15. So the items in column A1 to A15 now appear in row 19. [If you are not familiar with Transpose, Excel’s Edit–Paste Special–Transpose command converts data in a column into a row, and vice versa. We’ll work through an example in a moment.]

If you look at Edinburgh, this is the same as Birmingham. The top three rows have been filled in, and the budget then transposed into rows 19 to 23.

The Combined worksheet contains the transposed rows from Birmingham and Edinburgh, but not from London. With the budgets stored like this in a LIST, it will be easy to analyse the data with a pivot table.

Go back to the London worksheet.

2. Tweaking and transposing the London budget
You are now going to modify the format of London to match the Birmingham and Edinburgh layouts.

In cell A1, type Region. In B1, C1, D1 and E1, type London. Remove the bold font.

In A2, type Act_Budg. In B2, C2, D2, and E2, type Budget.

In A3, type Month

We’ll now transpose the data. Highlight the data in cells A1 to E15. From main menu, choose Edit–Copy (or press Ctrl+C). Click on cell A19

From main menu, pick Edit-Paste Special–Transpose (bottom middle), then press OK.

Rows 19 to 23 fill up with data. The column headings in row 19 are the same as the row headings in A1 to A15.

Now highlight rows 20 to 23 (i.e. without the column headings). Then copy and paste them into the Combined worksheet, rows 10 to 13.

The Combined worksheet now contains budgets for all three regions in their transposed format. We can use a standard pivot table to consolidate it and compare with actuals.

3. Create a pivot table in the Combined worksheet
Click onto the Combined worksheet. From main menu, select Data–Pivot Table.

The Pivot Table wizard, Step 1 appears. Click Next and Next again, then press the Layout button to bring up the Layout box.

Drag Income – Audit(first column, fourth one down) and drop it into the DATA area.

Now drag and drop Income – Consultancy below Income and drag Audit to the DATA area. Drag and drop the next 10 boxes and put them all underneath each other in the DATA area.

The DATA area should contain three Income Accounts, three Wages Accounts, and six Overhead accounts.

Drag and drop Region into the COLUMN area. Drag and drop Month into the PAGE area, then OK, and Finish.

Down the left the pivot table displays the three Income accounts, the three Wages accounts and the six overhead accounts. Across the top it reads Birmingham, Edinburgh, London.

On my screen the font size is Arial 8 point. Make it bigger, say Arial 11 point.

4. Use Formulas to calculate Total Sales, Total Cost of Sales
To produce a decent P&L we need to calculate some totals, for example Total Sales, Total Cost of Sales and so on.

We’ll use the Formulas option for this.. Right click on the data for pivot table menu.

From the list, select Formulas. [In Excel 2003 the Formulas option is buried in the pivot table toolbar. Therefore right click for pivot table menu and select Show Pivot Table Toolbar. In the toolbar, click on the down arrow next to Pivot Table. Select Formulas from the list.]

In Formulas, select: Calculated Field. The “Insert Calculated Field” box appears.

In the Name box, replace Field1 with Total Sales. In the Formula box click to the left of the 0. Delete the 0, then highlight Income – Audit in the list of Fields and click on Insert Field.

Income – Audit appears in the Formula box. To the right add a + sign.

Complete the formula: Income–Audit + Income–Consultancy + Income–Other. Click on Add to save the formula.

Now create these other formulas: (after you’ve clicked Add, Excel will leave the formulas in the boxes. Don’t worry. Just type over them).

  • Total Cost of Sales = Wages–Audit + Wages–Consultancy + Wages–Other
  • Gross Profit = Total Sales + Total Cost of Sales (remember, Costs are already minus)
  • GP %age = Gross Profit / Total Sales
  • Total Overheads = Premises + Marketing + Office Admin + Motor & Travel + Finance Costs + Depreciation
  • Net Profit = Gross Profit + Total Overheads (remember, Costs are already minus)

After creating all the formulas, click on OK to close the box and return to the pivot table. Nothing has changed.

5. Insert the formulas into the pivot table
Right click anywhere on the pivot table for menu. Choose Pivot Table Wizard–Layout.

The new formulas have been added to the list on the right. Insert them in the Data area as follows:

  • Total Sales under Income – Other
  • Total Cost of Sales under Wages – Other
  • Gross Profit under Total Cost of Sales
  • GP %age under Gross Profit
  • Total Overheads under Depreciation
  • Net Profit under Total Overheads

Then click OK and Finish. The new Total rows are added to the pivot table.

Everything needs tidying up, but before we leave formulas, call up the Formulas menu once more.

Click onto the bottom option – List Formulas. This generates a worksheet with the formulas.

6. Tidy up the pivot table
To tidy up the numbers, click on the top of columns B, C, D and E to highlight them, then from the top menu:

Format–Cells–Number–decimal places = 0. Use 1000 separator? – Yes. Negative numbers in red with a minus sign (at bottom).

Now format the GP %age row as a Percentage – no decimal places. (Format-Cells-Percenbtage)

The percentages should all be 67%.

[If they aren’t, check your formulas! If a formula is wrong, it’s probably because you’ve instinctively put a minus rather than a plus into it. You don’t HAVE to express Income as positive numbers and Costs as negative. But if you do, it means the Variances will be come out correctly in the next session when we are entering the actuals.]

We also need to tidy up column A by removing the “Sum of”’s. Click on the A to highlight the column

From main menu: Edit – Replace

In the Find What box, type Sum of

At bottom left, click on Replace All. OK Close.

Every Sum of has been replaced with a blank. [thanks to reader Stephen Smith for this tip]

Finally, we need to differentiate the Total lines from the rest. Highlight row 8, Total Sales, then make it bold by clicking onto the B icon.

Highlight row 12, Total Cost of Sales. Press F4 (repeat) key to make it bold as well.

Make Gross Profit, GP %age, Total Overheads and Net Profit bold as well.

Now click onto Total Sales in A8. Press F2 (edit) key.

Put an indent on Total Sales as follows: Left arrow to the big T, then space bar 3 times.

Apply F2 and indents to the other Total lines to make it look nice.

7. Rotate the numbers
With a pivot table you can display the numbers any way you want. Here are three examples to give you some ideas:

a) In B1 it says (All). The current figures are the company totals over all four months.

Click on the down arrow in B1. Select MAY. This shows the totals for May only.

Click on the down arrow again. Select (All) again to show yearly totals.

b) Now drag the grey Month field button from A1, and drop it onto the right hand part of A4 (Data).

Month is now in B4, and you see a breakdown by MAY, JUN, JUL, JUL..

c) Or try switching Region and Month, to get Data in A4, Region in B4 and Month in C3
This breaks down each budget category by region and month.

Finally, go back to where you were, with Data in A4, Month in A1 and Region in B3.

This ends the current session. Next time we’ll add the Actuals and calculate Variances. In the meantime, Save this workbook and close down.

END OF SESSION

Related material in ExcelZone
For more than four years, David Carter and AccountingWEB members have built up a massive stockpile of material on Excel and pivot tables. To delve more deeply into the subject, see:

Replies (1)

Please login or register to join the discussion.

avatar
By David Carter
07th Mar 2007 10:36

Good thinking!
Stephen, many thanks for these tips. I have now revised the tutorial to include them.

Thanks (0)