When building forecasts that run over several time periods, one important issues relates to how any sensitivity analysis should be conducted. For example, consider a 5-year model of Salary, in which the Salary is growing at a rate which is is assumed for each individual year:
An alternative approach to building the model as above would be to use a single growth rate (that is set in Year 1), whilst the rates for the other years are calculated from it. In the following, cells E5 to H5 are no longer direct inputs – but are direct cell references to D4 (so the colour coding of the font of these cells has been changed to emphasise this):
This approach represents the simplest form of a general concept of “parameter reduction”, meaning that the number of input assumptions (or number of independent variables) in a model is reduced by calculating some (initially indpendent) items from other, whether explicitly as above or implicitly as descirbed in later examples. Its use here is the simplest case, since the calculation is a direct cell reference only (i.e. which can be considered as multipication by 1, or the simplest form of scaling.
Then, by varying (only) cell D4, one can create a DataTable for the Salary in year 5:
… showing that the Salary in year 5 would vary as:
The same method could be done for some or all of the other assumptions in the model, so that the original model…:
… would be altered to:
… and which would allow a sensitivity analysis to be conducted by varying one or two inputs. For example, the following shows the Savings made in year 5 only as the growth rates for Salary and for Food and Entertainment are varied:
For example, the top left of the above table shows the Savings in Year 5 for the case that is shown explicitly in the following image (in which cell D4 is set to 0%, so that E4, F4, G4 and H4 are all automatically adjusted to 0% as well, and similarly for D10, and E10, F10, G10, H10):
The following related points are worth noting:
It is also worth emphasising that the models which have separate growth rates in each period (for example, as shown at the top of the section) are not wrong, but are just inconvenient from a sensitivity analysis point of view.
In many practical cases, one may in fact which to use figure for earlier periods which are different to those in later periods. For example, one may have specific knowledge about short-term budgets and plans that should be reflected in the model, whereas the longer-term part of the forecast may be more generic. Thus, it is possible to use “mixed” approaches i.e. where the short-term forecast uses separate assumptions for each period, whilst the monger-term part of the forecast uses a single assumption.
For example, in the following image, the growth rates are set for each item separately and for the first three years, whilst the growth rate in year 4 and 5 has been implemented using a formula link to year 3 (for example cell H4 contains the formula =F4, and so on):
As another example, one could have a short-term growth rate that applies for some first periods, and another growth rate for the medium-term. In the following, cell E4 contains simply =D4 (and similarly for other relevant cells in column E), whilst G4 and H4 each contain =F4 (and similarly for the other relevant cells in columns G and H):
These mixed approaches still allow for sensitivity analysis to be conducted more efficiently than if separate assumption values are used in each time period, but such analysis is “conditional”. That is, for example, one could ask the question “Given our assumption for 2% Salary Growth in the first two years, what rate of salary increase would be needed to earn $40000 in year 5?”.
Please experiment with building a simple mixed-form model, such as that shown in rows 2-4 for the Salary in the above image.
The implementation of scenarios using “scenario decks” is another example of parameter reduction. In this case, each scenario is defined by multpled items, such as the time-profile of growth rates. However, when runing the analysis, it is only the scenario identity that is varied. For example, the following shows three scenarios (Base, Low and High), each with a different. time-profile of the growth rates (defined in row 2-6). The model’s calculations (rows 8-10) are based on the the values for the selected scenario (i.e the values in row 6, which in this case correspond to the Low scenario):
When running all scenarios together, it is the scenario name that is varied. This generally requires using functions (such as Lookup and Reference functions), and so is covered in later courses.