Excel tips: how to use flexible budget models

Question

Is there a way to convert a single scenario budget model so that it can handle multiple scenarios?

Answer

Depending on the budget’s structure, you can use formulas, combined with a table layout, to handle multiple scenarios within a single budget model. The example I'm using here is a simple one, but the technique can be modified to handle scenarios involving multiple years.

Your first step is to identify the budget inputs that need to be modified as part of the scenario. Ideally the majority of the budget inputs should be centralised on a single sheet.

This technique is flexible. If you change your mind and need to include another input in the scenarios it can be easily amended. Extra scenarios can also be accommodated.

Figure 1 shows the current input cells (yellow) for a simple budget model. It’s common practice to standardise the cell fill colour of input cells throughout a budget or financial model.

These inputs are used by other formulas to produce the final budget report. We need to replace the manual inputs (yellow cells) with formulas that will be based on a single scenario selection cell. Changing the scenario cell will change all the inputs in one step.

Figure 2 shows the revised layout. Four scenarios are listed to the right of the input cells. I have inserted new rows for the scenario number and the scenario name above the input cells.

To change the budget you now only need to amend cell B3 to select the relevant scenario number. The formulas in column B are all simple and can be copied down. The formula for cell B4, which displays the scenario name, is: =OFFSET(C4,0,$B$3)

This formula is the simplest structure of the OFFSET function, which lets you specify a starting cell, in our case C4, then specify how many rows and columns to offset (move) from the starting cell to select another cell.

The zero in the formula instructs Excel to remain on the current row, so we’re offsetting zero rows from cell C4.

The reference to $B$3 refers to the number of columns to offset from C4. In Figure 2 we are offsetting three columns from cell C4, which will select the entry in cell F4. The $ signs ensure the reference won’t change as the formula is copied down the column.

The advantage with using the OFFSET function is that if you want to add extra scenarios you just enter more values to input cells in the right-hand columns, as shown in Figure 3. Entering 5 in cell B3 would amend the above formula to refer to column H.

Note: In the example in Figure 2, I have used yellow input cells for all the various scenario options. You could instead include formulas here. For example, you could input the base entries in column D and then use formulas in columns F and G to create the Best and Worst case values. This means you can adjust other scenarios by changing the base case.

The formula in cell B4 can be copied down to the other cells below in column B, using the following method.

When copying the formula down the column there will be issues because the former input cells in column B have their own specific number formats. If you just use copy and paste then you will lose the existing formats and have to reformat them.

Fortunately Excel has a paste option that only pastes formulas and leaves formats unchanged. There are a few ways to access this option. After copying you can:

Right click and use the Formula icon (fx), see Figure 4.

Use the drop-down menu on the Paste icon on the Home ribbon and choose the Formula icon, see Figure 5.

Press in sequence Alt h v f. Do not hold the keys down, but press them one after the other.

The OFFSET function is a special type of Excel function because it is a volatile function. This means it recalculates every time Excel calculates.

Most functions only recalculate when their dependant cells or ranges change. This means if you use thousands of OFFSET functions in a file you will notice an impact to the file calculation time.

But with the speed of today’s current computers this volatility issue is less of a problem than it was 10 or 15 years ago.

Try it out with this sample spreadsheet.

Neale Blackwood CPA runs A4 Accounting, providing Excel training, webinars and consulting services to various organisations. Questions can be sent to [email protected].