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

Budgeting with Pivot Tables #2: Map actuals to budget

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

When reporting actuals against budget, you often find that your budget categories don’t match the nominal codes in the accounts package. In this second tutorial, David Carter uses an Excel lookup table to make his actuals tie up to the budget.

In the previous tutorial we assembled budgets for three regions – Edinburgh, London, Birmingham – and combined them into a single budget database for the whole company.

The new financial year has begun. We now want to add the first month’s actuals, and report on performance against budget.

To do this, we will simply run the Trial Balance report for the month, transpose it, then add the monthly amounts to the budget database.

Note 1: since the budget relates to the P&L only, I’ve removed the Balance Sheet accounts from this Trial Balance.

Note2: later on you will transfer data into the file Budget_Tut_Master.xls which you worked on in the previous exercise. Don’t open it now, but make sure you know where it is.

Download the Trial Balance now.

1. Format of the Trial Balance
The first account in the TB is 52000-100, Income - Audit – London.

This particular chart of accounts uses multi-segment nominal codes. The first 5 digits represent the nominal account, the next 3 digits represent the cost centre.

The cost centres for London, Edinburgh, Birmingham, are 100, 200, 300.

2. But the budget is not at nominal account code level
First, though, we have a practical problem. There were 12 categories in the budget. But in this TB there are 27 nominal accounts (81 account codes in all, spread over 3 regions).

As often happens, the budgets weren’t made at the detailed nominal code level but one level higher up.

First, then, we have to match the 27 nominal codes in the TB to the 12 categories used in the budget. This is easily done via a lookup table.

Download the Lookup Table now.

3. Format of the Lookup table
The lookup table looks very similar to the TB. In fact, in order to make it, I simply downloaded the TB into Excel, deleted the column of balances, then typed in columns C and D by hand - Category and Region.

The Categories are the same 12 categories as used in the Budget. The first was Income – Audit; the last was Depreciation.

The 12 categories need to be sorted into the order in which they appear in the P&L. Therefore I’ve stuck a 2 digit number in front and made the first category into 10-Income – Audit, and the last into 21-Depreciation.

[Question: Why not make the numbers 1 to 12? Answer: because Excel would sort them into the order 1, 10, 11, 2, 3, 4 etc.]

4. Use lookup tables to revise the layout of your P&L
Lookup tables are immensely powerful. You can use them to re-organise your P&L, or to print it out in more than one layout (e.g. one for local management, one for parent company), or to consolidate several different P&L’s into one.

For example, scroll down until you get to the 74xxx series of Account numbers (row 50).

The 74xxx series of accounts is for Motor and Travel–type accounts. The first of these is 74070-100, Travel-London. The last is 74210-300, Motor Insurance-Edinburgh (row 64).

However, in the budgets Motor Insurance was not included in the Motor and Travel category, but in Office Admin.. To reflect this, in C62:C64 I have put Motor Insurance not into category 19-Motor and Travel, but into 18-Office Admin.

Similarly, the person who originally set up this chart of accounts put Marketing expenses near the end in the 75xxx series, rows 65-73. But your company prefers to report Marketing expenses higher up in the P & L beneath Premises. So I’ve prefixed the Marketing category with a 17. This will put it beneath 16-Premises, and above 18-Office Admin and 19-Motor and Travel.

5. Performing the lookup
OK. You should have two files open – Trial Balance.xls and Lookup table.xls

From main menu: Window - Arrange - Horizontal

The screen splits into two. Both files are open, one above the other.

We are going to add the Categories to the Trial Balance. We’ll do this by importing them from the lookup table, as follows.

In Trial Balance.xls, insert a blank column to the right of GL AcctName. In C1 type in the heading: Category.

Click onto C2 (the first empty cell of the new Category column).

From main menu, select: Insert – Function, (or simply click the fx icon.)

[Excel 2003]. The Insert Function box appears. In the “Select a function” window, look in the list for the VLOOKUP function. If it isn’t there, click on the Or Select a Category box (it probably says Most recently Used.). Click onto the list, select Lookup and Reference. VLOOKUP is at the bottom of the alphabetical list.

[Excel 2000.] The Paste Function box appears. Check to see that VLOOKUP is in the right hand list.
(If it is not, click Lookup and Reference in the left hand list, go to the bottom, select VLOOKUP)

The Vlookup box appears, starting with Lookup_Value.

In TrialBalance.xls, highlight column A by clicking on the A at the top.

Moving lines appear around the GLAcct column and A:A appears in the Lookup_Value field.

Click the mouse onto the next box, Table-Array

In Lookup Table.xls, click on the A at the head of the column, then drag to include all the columns

Moving lines appear around columns A to D. [Lookup Table.xls]Sheet1!$A:$D appears in the box

Click the mouse onto the next box, Col_ Index_Num

Type 3 (i.e. the third – Category - column in the lookup table)

Click the mouse onto the next box, Range_Lookup

Type false

At the bottom left hand of the box you should see Formula Result = 10-Income – Audit, meaning that you have filled in the values correctly. Click on OK

You should see 10-Income Audit appear in cell C2.

Click onto cell C2.

Now drag the mouse to the RH bottom of C2 until it turns into a black cross

Double click to Fill Down to the bottom of the sheet.

The column now fills down with Categories.

6. Convert the VLOOKUP formulae to values
However, these categories are all formulae. They need to be replaced with the actual values.

Highlight the new column C.

From main menu: Edit – Copy. Then again: Edit - Paste Special - Values - OK

The formulae are now replaced with values.

7. Use VLOOKUP to pull in the Regions
We need to pull in the Region as well as the Category.

In Trial Balance.xls create a blank column to the right of Category. Head it Region.

Now use VLOOKUP again to pull in the Region against each GL Acct number. (Don’t forget to overwrite the formulae with values.)

Close down the Lookup table.xls. Maximise Trial Balance.xls.

The columns of Trial Balance.xls should read: GLAcct, GL AcctName, Category, Region, MAY.

8. Create a pivot table in the Combined worksheet
We’ve added the budget categories and regions, so now we’ll convert the TB into a P&L.

Click anywhere on the data. From main menu: Data – Pivot Table

The Pivot Table wizard, Step 1 appears. Select: Next – Next – Layout for the Layout box.

Drag Category and drop it into the ROW area.

Drop Region into the COLUMN area.

Drop MAY into the DATA area, then OK Finish.

The pivot table summarises the nominal accounts by category. Marketing is now below Premises.

Across the top it reads Birmingham, Edinburgh, London.

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

9. Check the detail
Has Motor Insurance been included in the Office Admin category?

To find out, right click on the grey “Category” field button in A4. A menu appears.

Select: Group and Show Detail – Show Detail.

The Show Detail box appears. From the list of fields, double click on GL AcctName.

The Categories all expand to show the underlying GL accounts. Scroll down to Office Admin in row 55. Motor Insurance is now in this group (after Cleaning.)

Now click the Undo icon in the top row (curved blue arrow pointing left; next to the paintbrush).

We are back with the pivot table just summarising the 12 categories.

10. Remove the Grand totals
We are now going to transpose the data, then copy and paste it into the Budgets database we produced last time.

But before doing so, we first need to remove the Grand Totals for the rows and columns. To do this:

Right click on the data for menu - Table Options – untick the two boxes at top left, Grand Totals for Rows and Grand Totals for Columns. OK.

The totals disappear.

11. Transpose the Pivot Table
Open the Budget_Tut_Master.xls workbook you produced in the last exercise. Click onto the Combined worksheet. We will paste the Actuals underneath the 13 rows of budgets in this worksheet.

Now click back into Trial Balance.xls

The pivot table is in the usual rows and columns format. We’ll transpose it.

Right click on the data. From the menu: Select – Entire Table. The table is highlighted.

From main menu: Edit – Copy. Click onto cell B20 [not A20, but B20].

From main menu: Edit – Paste Special. Tick two boxes - Values and Transpose. OK.

Rows 20 to 23 fill up with data (but column A remains blank).

Move cells C20-23 (London, Birmingham etc) to cells A20-23.

In cells B20-23, type: Actuals

In cells C20-23, type: May

12. Copy the Actuals into the Budget database
Highlight rows 20 to 23 by clicking on the row numbers.

Right click for menu. Copy

Go into Budget_Tut_Master.xls. Make sure you are in the Combined worksheet.

Paste the rows into row 17 (right click for menu – Insert Copied Cells).

Check that the column headings you brought over tally with the column headings in row 1.

When you are satisfied that the data is in the correct columns, delete the blank rows and the line of column headings. The 3 Actual rows should now be in rows 14, 15 and 16.

You have added the first set of Actuals correctly. In the next session we’ll add them to the pivot table and calculate the variances.

END OF SESSION

Replies (0)

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.