Modelling and What if? analysis with pivot tables

Modelling and What if? analysis with pivot tables

Didn't find your answer?

All accountants use spreadsheets for financial modelling and 'What if?' analysis. However, the unstructured format of the traditional spreadsheet means that large models especially can turn out to be a house of cards, with no-one trusting the results or understanding how they are arrived at.

For the results to be trustworthy, the source data has to be properly structured in the first place. Pivot tables provide this structure and result in a reliable, well-documented model which can be easily amended as circumstances change.

Financial modelling is also one of the easiest applications for pivot tables. To get started, all you need to do is to type some data into a dozen or so cells of your spreadsheet, run the pivot table wizard, and you are interactively building your model on the screen. Follow David Carter's tutorial to see how easy it really is!

* * *

NOTE: This tutorial comes with three files:
1/ Startup instructions - instructions on how to downolad pivot table files. Print these details and return to AccountingWEB
2/ Word tutorial - download this guide and print it off, or follow it below
3/ Excel file - containing the "What If" data. Download and save it on your hard disk.

The STARTUP file contains detailed instructions on how to run through the tutorial; but if you have any problems, email [email protected] with details of the files you're having problems with.

INTRODUCTION - MODELLING AND WHAT IF? ANALYSIS WITH PIVOT TABLES

This tutorial shows you how to set up a forecasting model with Excel pivot tables and how to use it to make 'What if?' calculations.

All accountants use spreadsheets for financial modelling. However, traditional methods leave the data unstructured. This makes for difficulties later on when the model needs to be changed or when someone else has to interpret the formulae. If you build your models with pivot tables, your source data will be properly structured and your formulae properly documented, so avoiding these problems.

In this exercise you are the publisher of a magazine. You are using the model to forecast future income. Income derives from various sources, and depends on variables such as the number of subscribers to the magazine, the average subscription fee, the number of pages in each month's issue, the average fee rate per page of advertising, and so on.

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'.

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.

The tutorial makes extensive use of the 'Formulas' feature first introduced in Excel 97. Therefore you must be using either Excel 97 or Excel 2000 on your machine.

UNCLEAR INSTRUCTIONS
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 protected]. 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 ' in this case financial modelling.

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. STARTUP

Start up Excel. Open the file: Whatif

The Whatif workbook contains two worksheets ' STANDARD and SOURCE.

Click onto the STANDARD worksheet. Print off a copy now.

2. THE 'STANDARD' WORKSHEET

The STANDARD worksheet contains some projections of future income that have been produced in the traditional way. Your job in this tutorial will be to produce these same projections via pivot tables.

First, however, let's interpret the STANDARD worksheet.

You are the publisher of several monthly magazines, Magazine1, Magazine2, Magazine3 etc. In this case you are projecting future income streams for Magazine1.

Income derives from 3 sources ' monthly subscriptions from readers, advertising revenue, and a small commission you earn from your advertisers whenever a reader buys one of their products which has been advertised in your magazine.

At the time your budget starts, the beginning of April, you have 20,000 subscribers. The number of subscribers is forecast to increase by around 2,000 per month.

Subscription Income
Each month your members contribute a monthly subscription of £5. This is due to go up to £6 in month 4, July.

Advertising Income
On average you receive £4,000 in advertising for each page of the magazine. The precise number of pages varies per month. Advertising revenue is estimated to increase to £4,400 per page in August.

Commission Income
Whenever your readers buy any products advertised in the magazine and quote the magazine reference number, your advertiser gives you a rebate of 2.5% of the sales value.

Please study the STANDARD worksheet, and in particular the formulae in column B, in order to understand how each type of income is calculated.

When you understand how the incomes are calculated, click onto the SOURCE worksheet.

3. THE 'SOURCE' WORKSHEET

The SOURCE worksheet will hold the source data from which you generate the pivot tables. Some of the data has already been entered. There are 4 columns:

TITLE contains the name of the magazine.

MONTH contains the month

NEWSUBS contains the number of new subscribers you plan to sign up each month.

SUBSCRIBERS contains the total number of subscribers anticipated at the end of each month.

The values in columns A to C have all been typed in. However, SUBSCRIBERS in column D is a calculated value. Click on cell D2.

You see the formula =C2+20000

When the budget starts at 1st April you have 20,000 existing subscribers. A further 2,000 new subscribers are forecast by the end of April, making 22,000 at the end of April..

Click on D3, the May figure. The formula is = d2+c3

This is a straight running balance formula, calculating that: subscribers at the end of this month = closing subscribers last month + new subscribers this month.

This same running balance formula continues throughout the rest of column D.

4. CREATE THE PIVOT TABLE

We'll begin the pivot table. Click onto cell C3 or anywhere within the data.

From the main menu at the top of the screen, select :

Data - Pivot Table Report [Excel 2000 = Pivot Table and Chart Report; Excel 2007 & 2010: look for the PivotTable icon on the left hand side of the Insert menu ribbon]

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:$D$7.

Click on Next.

Step 3 of 4: The COLUMN-ROW-DATA box appears. To the right are the four column headings of the SOURCE worksheet [note: if you are using Excel 2000, you must first click on Layout]

Drag and drop the column headings as follows:

MONTH into the COLUMN area

TITLE into the PAGE area

SUBSCRIBERS into the DATA area

NEWSUBS into the DATA area beneath SUBSCRIBERS

[When dropped into the DATA area, SUBSCRIBERS should say 'Sum of SUBSCRIBERS'. If it says 'Count of SUBSCRIBERS', double click on Count of SUBSCRIBERS. A box appears. Change 'Count' to 'Sum'. If NEWSUBS says 'Count', correct it in the same way]

Now click on Finish. [if you are using Excel 2000, OK, then Finish]

The pivot table is generated. Under each monthly heading you see the total Subscribers and total New Subscribers for the month.

Click back onto the SOURCE worksheet to confirm that the original source data is still there.

Now right click back onto the new sheet with the pivot table. Rename it PIVOT.

Take a look at your STANDARD printout. Check that the number of Subscribers on PIVOT is the same as the number on the printout.

5. SHADE COLUMN HEADINGS

[Excel 97]
Click on the grey 'MONTH' field button in cell B3. All the sub-total lines are highlighted
or
[Excel 2000]
Move the mouse pointer above the grey field button 'MONTH' in B3. Move it gradually downwards until it assumes the shape of a thick vertical down 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 centre the months by clicking the Centre icon. Also click the B for Bold icon

For some reason shading a cell in Excel removes the gridlines. To keep the gridlines, first locate the 'Borders' icon to the left of the yellow 'Fill Color' icon (it looks like four quarters).

Click the down arrow in the right part of the Borders icon.

A box of 12 possible borders appears. Select 'All Borders' in the bottom line, second from left.

Click anywhere to remove the highlighting.

6. REMOVE 'GRAND TOTALS'

The 'Grand Total' values in column H are false and need to be removed. To do this:

Right click on the pivot table for menu.

Select: Options. [Excel 2000=Table Options] The'PivotTable Options' box appears.

Find the 'Grand totals for Rows' option near the top left. Remove the tick. OK

The Pivot Table is recalculated and the 'Grand Total' column disappears.

7. ENTER MONTHLY SUBSCRIPTION FEE INTO THE 'SOURCE' WORKSHEET

Subscription income is calculated as Number of Subscribers x Subscription per Month.

The SOURCE worksheet already contains the number of Subscribers. Now we have to add the Subscription fee per month.

Click onto the SOURCE worksheet. In cell E1 type the column heading: SUB£_MTH.

In cells E2 to E7, type 5,5,5,6,6,6 (the subscription goes up to £6 per month in July)

Click onto the PIVOT worksheet.

Right click on the pivot table to bring up the menu. Select: Wizard

The Step 3 box appears. From the list of options at the bottom, select: Back

You see the Range box, Step 2. The range is $A$1:$D$7

[Or in Excel 2007 & 2010, stay in the PIVOT worksheet and choose Insert-PivotTable Tools-Options and click the Change Data Course option].

Above you can see cells A1 to D7 surrounded by circling lines.

The new column E is outside the lines. It needs to be included by changing $D$7 to $E$7.

To do this: In the Range box, click onto the left of the 'D' in $D$7.

Delete the D by pressing the 'Delete' key to the right of the big Enter key

Type in E so that it reads $e$7, then click on Next. Step 3 re-appears.

Click onto Back again. Check that the lines are now moving around all the data. Next

The ROW-COLUMN-DATA area appears [Excel 2000. Click on Layout.]

Note that SUB£_MTH has appeared at the bottom of the list on the right. Drag and drop it underneath Sum of NEWSUBS in the DATA area. Finish.

The monthly subscription fee appears at the bottom of the pivot table.

8. USE FORMULAS TO CALCULATE SUBSCRIPTION REVENUE

Now to calculate Subcription Income as Number of Subscribers x Monthly Subscription Fee.

Right click on pivot table for menu. Select: Formulas - Calculated Field

The 'Insert Calculated Field' box appears.

In the 'Formula' box click to the left of the 0. Delete the 0 Then:

In the list of Fields, find SUBSCRIBERS and highlight it. Then click on Insert Field.

SUBSCRIBERS appears in the Formula box

To the right of SUBSCRIBERS, add an asterisk ('*') , meaning 'multiply by'.

Go back into the list of fields. Find SUB£_MTH and highlight it. Insert Field

The Formula box should now display the formula =SUBSCRIBERS*'SUB£_MTH'

Now click onto the 'Name' box just above.

Replace Field1 with Total Subscription Income

Click on Add, then OK at the bottom.

A new row appears at the bottom of the pivot table, entitled 'Sum of Total Subscription Income'

Right click anywhere within the new row. The menu appears. Select: Field [if Excel 2000, Field Settings] - Number - Currency - Decimal places = 0 - £ sign OK OK

The Total Subscription Income line takes on commas and pound signs.

This completes the calculation of the first source of income.

9. ENTER PAGES AND AD. RATES INTO THE 'SOURCE' WORKSHEET

Now we'll work out the income from the second source - Advertising. This is calculated as an estimated rate of £4000 for each page that the magazine carries. In April the magazine is forecast to carry 22 pages. So April ad revenue should be 22 x £4000 = £88,000.

Note that ad revenue per page is forecast to increase in July from £4,000 to £4,400.

Click back onto the SOURCE worksheet.

In F1 type the column heading PAGES

In F2 to F7 type: 22, 22, 24, 25, 20, 24

In G1 type the column heading AD£_PAGE

In G2 to G7 type: 4000, 4000, 4000, 4400, 4400, 4400.

Click onto the PIVOT worksheet.

Right click on the pivot table to bring up the menu. Select: Wizard

Step 3 appears. From the list of options at the bottom, select: Back

You see the Range box, Step 2. The range is $A$1:$E$7

Above you can see cells A1 to E7 surrounded by circling lines.

The new columns F and G need to be included in the range.

In the Range box, click to the left of the 'E' in $E$7.

Delete the E by pressing the 'Delete' key to the right of the big Enter key

Type in G so that the range now reads $A$1:$G7 (upper or lower case)

Click on Next. Click on Back again to check that the lines are OK.

Click on Next once more. The ROW-COLUMN-DATA area appears.

PAGES and AD£_PAGE have been added to the list on the right.

Drag and drop PAGES and AD£_PAGE underneath Sum of Total Subscription Revenue in the DATA area. Finish.

'Sum of PAGES' and 'Sum of AD£_PAGE' appear at the bottom of the pivot table.

10. CALCULATE ADVERTISING INCOME

Advertising Income is calculated as Number of Pages x Advertising Rate per Page. Therefore:

Right click on pivot table for menu. Select: Formulas - Calculated Field

The 'Insert Calculated Field' box appears.

In the 'Formula' box click to the left of the 0. Delete the 0 Then:

In the list of Fields, find PAGES and highlight it. Then click on Insert Field.

PAGES appears in the Formula box

To the right of PAGES add an asterisk ('*') , meaning 'multiply by'.

Go back into the list of fields. Find AD£_PAGE and highlight it. Insert Field

The Formula box should now display the formula =PAGES*'AD£_PAGE'

Now click onto the 'Name' box.

Replace Field1 with Total Advertising Income

Click on Add, then OK at the bottom

'Sum of Total Advertising Income' appears at the bottom of the pivot table.

11. REVIEW OF EVENTS

You can see the sequence involved in building up the model. First, you add a new variable to the SOURCE worksheet. Second you go into PIVOT and display the variable. Third, within the pivot table you define the calculation formula based on the variable.

Note that you don't have to display the variables in SOURCE on the pivot table. It's optional.

Note also, by the way, that each row doesn't have to begin with 'Sum of''. For example:

Right click anywhere within the bottom row. The menu appears. Select: Field [Settings]

The Pivot table field box appears. The 'Name' box reads: Sum of Total Advertising Income.

Click to the right of the 'f' in 'Sum of'.

Use the backspace key to delete Sum of. OK

Remember, always, that you must leave a blank space before Total ' otherwise Excel will display the message: 'Pivot Table field name already exists'.

Now change the numbers formatting in this line also: Field - Number - Currency - Decimal places = 0 - Symbol = £ - OK - OK

12. ADD COMMISSION VARIABLES TO SOURCE WORKSHEET

The third source of income was from commission. When readers buy products advertised in the magazine, the advertiser pays you a 2.5% commission on the purchase value.

The commission calculation is a bit more elaborate. First you estimate what proportion of readers will make a purchase, and from that calculate the number of buyers.

Then you estimate the average value of each purchase that a buyer will make.

From these two values you calculate the projected total value of 'Gross Reader Purchases'.

Then you multiply Gross Reader Purchases by the commission rate of 2.5% to forecast your future Commission Income.

Click onto the SOURCE worksheet.

In cell H1 type the column heading %BUYERS.

In cells H2 to H7 type the percentage of readers expected to make a purchase. These are:

0.15, 0.16, 0.14, 0.17, 0.17, 0.18

In cell I1 type the column heading BUY£_VAL

In cells I2 to I7 type: 20, 20, 20, 20 , 20, 20.

In cell J1 type the column heading %COMMN.

In cells J2 to J7 type the standard 2.5% commission as: 0.025

13. CALCULATE GROSS READER PURCHASES

Click back onto the PIVOT worksheet.

Right click on the pivot table to bring up the menu. Select: Wizard

Step 3 appears. From the list of options at the bottom, select: Back

In the Range box change $G$7 to $J$7 (upper or lower case)

Click on Next. Then click on Back. Check the data is surrounded Next again.

The ROW-COLUMN-DATA area appears.

Drag and drop %BUYERS underneath Total Advertising Income in the DATA area.

Drag and drop BUY£_VAL underneath Sum of %BUYERS. Finish.

The bottom of the pivot table now shows Sum of %BUYERS and Sum of BUY£_VAL.

Right click within the %BUYERS row. Field ' Number ' Percentage ' no decimals.

Right click on the pivot table for menu. Select: Formulas - Calculated Field

In the list of Fields, find SUBSCRIBERS and highlight it. Then click on Insert Field.

SUBSCRIBERS appears in the Formula box

Add an asterisk.

Insert %BUYERS into the Formula box.

Add an asterisk.

Insert BUY£_VAL into the Formula box.

The Formula box should now display: =SUBSCRIBERS*'%BUYERS'*'BUY£_VAL'.

In the Name box replace Field1 with: Gross Reader Purchases

Click on Add, then OK

At the bottom of the pivot table 'Sum of Gross Reader Purchases' appears.

14. CALCULATE THE COMMISSION INCOME

Now to calculate the Commission income.

Right click onto the pivot table. Select: Wizard. The ROW-COLUM-DATA box appears

Drop %COMMN into the DATA area under Sum of Gross Reader Purchases.

Finish. The pivot table displays the monthly Commission rate of 0.025.

Right click for menu. Select: Formulas ' Calculated Field

Into the Formulas box insert: Gross Reader Purchases (it's at the bottom of the list).

Add an asterisk.

Insert %COMMN into the Formula box.

The Formula box should now display: ='Gross Reader Purchases'*'%COMMN'.

In the Name box type: Total Commission Income

Click on Add, then OK

No new row appears at the bottom of the pivot table!!!

In fact, there's nothing wrong. Up to now, whenever you created a new formula, Excel obligingly put it onto the pivot table automatically. But after 11 or so rows it stops and you have to put it in yourself. To do this:

Right click on the pivot table. Select: Wizard. The ROW-COLUM-DATA box appears

Total Commission Income is at the bottom right in the list of Field names

Drag and drop Total Commission Income underneath Sum of %COMMN in the DATA area.

Finish. 'Sum of Total Commission Income' appears at the bottom of the pivot table.

Right click on the Total Commission Income line. Field ' Number ' Currency - no decimals - £ sign.

15. CALCULATE TOTAL INCOME

All three sources of income have now been calculated. You have built your model.

To round things off, in cell A18 type: = A8 'Sum of Total Subscription Income' appears.

In cell A19, type = A11. In cell A20, type = A16

Click onto cell A18

Holding down the Shift Key, press Down arrow key to highlight cells A18 to A20.

Keeping the Shift key held down, Right arrow to extend the highlighting to column G.

From the main menu: Edit - Fill - Right. The monthly values appear.

From the menu select: Format - Cells - Number - Currency - No decimals - £ sign - OK

Click on cell B21 at the bottom of the B column.

Highlight cells B21 to G21.

Click on the Σ Autosum icon. The three Income rows are totalled.

Click on the B icon to embolden the totals.

Find the Borders icon to the left of the yellow Fill Color icon. Click on the down arrow on the icon.

Select the 'Thick Borders' option at the bottom right.

Click anywhere to remove the highlighting.

Hopefully, the totals should match those in the STANDARD printout.

16. LIST YOUR FORMULAS

Having set up your model, now display a list of the formulae as follows.

Right click on the pivot table. Select: Formulas ' List Formulas.

A new worksheet is created listing the formulas used within the pivot table.

Rename the new worksheet FORMULAS.

17. PERFORMING WHAT IF? CALCULATIONS

To see the effect of any changes in the variables, you make the changes in the SOURCE worksheet, then go back into PIVOT and use the REFRESH DATA command to see the result. For example:

Suppose new members were not to increase as quickly as forecast. Click onto SOURCE.

Change column C 'NEWSUBS' to: 1800, 1900, 2000, 2000, 1500, 2500

Click back onto PIVOT. Right click for menu.

Keeping your eyes on Rows 6 and 21, select: Refresh Data. All the figures change. NOTE:If you see hashes in any column, extend the width of the column.

18. CHANGING THE MODEL

(First take a note of the Gross Reader Purchases in April. It should be £65,400)

It is also very easy to change the model.

For example, some new subscribers will arrive at the beginning of the month, some at the end. So, when estimating Commission Income, you decide to average out the new subscribers each month by amending the formula for Gross Reader Purchases from:

=SUBSCRIBERS*%BUYERS*BUY£_VAL

to: =(SUBSCRIBERS-(NEWSUBS/2))*%BUYERS*BUY£_VAL

To do this, right click from menu. Select: Formulas ' Calculated Field

Click on the Down arrow to the right of the Name field (which contains Field1)

The various formulas are listed.

Select Gross Reader Purchases.

'Gross Reader Purchases' appears in the Name box and the 'Add' button changes to 'Modify'

Amend the formula to: =(SUBSCRIBERS-(NEWSUBS/2))*%BUYERS*BUY£_VAL

Click on OK. Gross Reader Purchases in April are now £62,700.

Click on the Formulas worksheet. Note that the formula for Gross Reader Purchases has not changed

Right click on Formulas at the bottom of the sheet. Delete this sheet.

Right click on the pivot table for menu. Select: Formulas ' List formulas

A fresh list of formulas is generated. The Gross Reader Purchases formula is now correct.

OK. It's time to take a break. File - Close - Save your work. Relax.

19. WHERE DO I GO FROM HERE?

a) BUILD YOUR OWN MODEL

Building models with pivot tables is very easy. Take a model you have already produced in the traditional way and see if you can reproduce it in a pivot table. Keep this first model as simple as possible until you are confident in the technique. Then try one more ambitious.

b) ADDITIONAL SELF-TEACH TUTORIALS
Many more Excel pivot tables are available on Accountingweb.co.uk.

If your main interest is in Accounting, you could work through Budgeting with Pivot Tables - Part Two. This tutorial takes the budget figures you have used here, and shows you how to add actuals, to report Mth and Year To Date Actuals versus Budget, and to calculate variances.

After this try Audit and Financial Reporting with Pivot Tables. This tutorial takes 2,000 accounting transactions from the Sage Line 50 accounting package and shows how to check them for errors before calculating the Profit and Loss account and Balance Sheet.

Alternatively, if you are interested in sales analysis or data warehousing, the Analyse Sales Data with Pivot Tables tutorial shows you how to import 4,000 sales invoice records from an accounts package such as Great Plains or Navision, and to analyse them in many different ways so as to show Sales by Product Group, Sales by Customer by Month, Gross Profit by Sales Executive, and so on.

b) USING PIVOT TABLES ON YOUR OWN ACCOUNTS DATA

Obviously, one major reason for learning pivot tables must be to use them for analysing your own company's data. If the data resides in a database such as Microsoft Access, pivot tables are included in Access 2000. If the data resides in a third party package such as Great Plains, Navision, Sage etc then you have to work out what data you must extract from it and how to extract it.

The Sage Line 50 and Pivot Tables tutorial is concerned with downloading raw data from the widely-used Sage Line 50 accounting package, preparing it for use in a pivot table, and doing some preliminary analysis. This tutorial discusses the key problem confronting anyone who wants to use pivot tables: that the data you wish to analyse is probably sitting in some third-party accounts package designed for transaction processing. Before you can even begin to analyse it, a lot of work may be required to extract that data and get it into a 'pivot-table ready' format. (To some extent, though, this tutorial has been superseded by the 'Audit and Financial Reporting' tutorial).

END OF TUTORIAL

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 small and mid-range accounts packages for the computer press. Since 1996 he has been using Excel pivot tables in areas such as financial and management accounting, budgeting, 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 [email protected]

Replies (1)

Please login or register to join the discussion.

avatar
By jwwatson
26th Jul 2000 15:01

Entering a Pivot Table formula
I have found that, when entering a Pivot Table formula, it is unnecessary to click to the left of the 0 on the Formula line and then delete it. Even though it isn't highlighted, the =0 will automatically change to (eg) =FIELD1 after you have selected a Field name from the list and pressed the Insert Field button. This might save you about 5 seconds every time you use it!

Thanks (0)