Avoiding cell hell on your spreadsheets

'What-if': you could do it better. By Sue Nugus, director, Academic Conferences International.

What-if analysis is the ability to make changes to the data or logic in a business plan and see the effect immediately. There are several approaches to this, from simply changing an opening value to changing the way in which an item is calculated.

For what-if analysis to work the business plan must be constructed appropriately, with the correct use of formulae and cell references. Figure 1 is an example of a simple profit and loss plan and figure 2 shows a formulae view of the plan.

 

Figure 1: Techno Toys summary profit and loss report

 

Figure 2: Formulae view of Techno Toys profit and loss account

Common what-if requirements with such a business plan might be to experiment with different values for the opening sales volume or opening price – or it might be to see the effect of different growth factors. All that is required for this is to overwrite the cells to be changed with new values. This will cause everything in the plan to be recalculated.

There are, however, a few potential problems with this type of what-if analysis, including:

• remembering which cells have been changed and what the original value/s were
• losing sight of what changes are having the greatest impact on the plan
• overwriting cells that contain formulae, leading to incorrect calculation of the plan
• having several versions of the file saved showing different scenarios.

Asking the questions

One way of minimising these problems is to manage the what-if analysis through the use of data tables. These allow you to ask a series of what-if questions and will display the results in a separate table on the spreadsheet – without changing anything in the main plan.

For example, let us suppose that there is concern at Techno Toys that the forecast opening sales volume might not be achievable. Financial management would like to see what effect changing this opening value would have on the rest of the plan.

To set up the table you first need to decide which values to use as what-if data for the opening volume. In this example we opted for values between 6,000 and 7,000 rising in steps of 200. You also need to decide on the output variables on which you want to see the effect of the changes – we chose to look at the total gross profit and total net profit. Figure 3 shows the table structure, which has been placed to the right of the main plan.

 

Figure 3: Outline of the data table

The output variables are entered into the table as cell references to the location of that variable in the main plan. Therefore in figure 3 a reference to cell F10 (=F10) has been made in cell J3 and a reference to cell F14 (=F14) has been made in cell K3.
 
The table is now ready to be completed by using the data table command. After selecting the table area (in this case the range I3:K9) we then select data table from the menu bar and the following dialogue box is displayed.

 

Figure 4: Data table dialogue box

The ‘row input cell’ and ‘column input cell’ boxes require us to specify the cell where we want the different opening sales volume values to be placed. Data tables allow for two sets of input data, but in this example we have only one – sales volume – and this has been entered as a column in the table. Therefore we need to click on the column input cell box and then click on cell B4 in the main plan. A reference to B4 is placed in the table dialogue box. To complete the command click OK and the table is immediately completed as shown in figure 5.

Seeing the effects

From this table you can see how different opening sales volumes would affect the total gross profit and the total net profit. Note that this takes into consideration all the other variables and logic built into the main plan.
 

Figure 5: Completed data table

To test that the data table is correct, select one of the opening values from the table and enter it into cell B4 in the main model. The total gross profit and net profit should match the corresponding line in the table. This is illustrated in figure 6.

 

Figure 6: Testing the data table.

The resulting data table is also dynamic so if the opening sales volume values in column I are changed the table is automatically recalculated. Similarly if you wanted to see the effects of the change in opening sales volume on the total direct costs rather than the total gross profit, changing the reference in cell J3 to =F10 will again cause the table to be recalculated. The execution of the data table command places a formula into the cells and this will be recalculated each time a change is made to the spreadsheet.

This kind of data table is sometimes referred to as a one-way table because only one input cell has been used – in this case the opening sales volume. With a one-way table the effect of changing the input value can be seen on any number of output variables. We chose to look at the total gross and net profits but we could also have looked at the second quarter direct costs and/or the third quarter overhead costs. There is also no limit to the number of input values placed in the table and they do not have to increase or decrease in regular steps as they do in this example. Furthermore the input values can be the result of formulae as opposed to absolute values.

Two-way tables

Sometimes it is preferable to look at the combined effect of changing two items in a plan. In the Techno Toys plan it could be useful to change the opening price as well as the opening volume values. This is achieved by creating a two-way data table.

The outline for the table is shown in figure 7.

  
Figure 7: Outline of a two-way data table

In this example we have kept the values for the opening volume the same and we have included a range of different opening price values in the range J12:N12. With a two-way table we are restricted to seeing the effects of the changes on a single output variable so we have decided to use the total net profit. Therefore a reference to cell F14 has been made in cell I12 (=F14).
 
To calculate the table, the range I12:N18 is selected. The data table command can then be executed. On this occasion both the row and the column cells need to be referenced – the row input cell being a reference to the opening price in cell B5 and the column input cell being a reference to the opening sales volume in cell B4. Figure 8 shows the completed data table dialogue box and figure 9 shows the calculated table.

 

Figure 8: Completed data table dialogue box for a two-way data table

 

Figure 9: Results of the two-way data table

Calculation options

There is no limit to the number of data tables that can be placed on a spreadsheet, although the table must be on the same worksheet as the input cell. Although a dynamic data table is useful, you may not always want the data table to recalculate when an adjustment is made to the main plan. By selecting Tools Options Calculation, it is possible to set the calculation to Automatic Except Tables. The F9 recalculate key will then have to be pressed in order for the tables to be updated.

Each table on the spreadsheet is seen by Excel as an ‘array’. This means that the contents of the table cannot be overwritten, changed or deleted. For example the bottom row of a table cannot be deleted if it is no longer required. If you did want to delete the bottom row you would have to reselect the table with the range you require and then execute the data table command again.

Useful across the company

Data tables provide an effective way of performing different what-if questions without having to change any of the data in the main model. Once created, tables can be referenced by other parts of a business plan, graphs can be drawn from the table data and tables can be printed like any other part of the spreadsheet.

Sue Nugus lectures on the CIMA Mastercourse ‘Spreadsheet skills for forecasting, planning and budgeting’. To find the next available course, visit CIMA Mastercourses and search for ‘spreadsheet skills’.

December 2005