2.4 Sensitivity Analysis in Time-Based Forecasts
Introduction to Parameter Reduction
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:
- Varying the growth rate in one year only (such as using a 5% rate in year 2, whilst retaining 3% for years 1,3,4 and 5).
- Manually changing all five values of the growth rates (i.e. in each cell).
- “What would my Salary be in Year 5, if I were to receive a 4% annual increase, instead of 3%?”, or
- “To achieve a Salary in Year 5 of $40000, what growth rate should I target?”.
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):
Side Note: Introduction to Parameter Reduction
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:
Generalising the Approach to Other Items
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:
- Although the use of a single figure (for each line item) makes sensitivity analysis easy to conduct, such analysis is only of interest if the case (e.g. constant salary growth) could arise i.e. that it is a realistic scenario.
- Variations of the above method are approaches such as using individual (separate) growth rates for year 1 and for year 2 (for each item), and then a single growth rate thereafter (for each item). That is, for Salary, there would be (independent) growth assumptions made in cells D4, E4 and F4, with the value from cell F4 also used in G4 and H4 (created using formula links). Then, F4 may be interpreted as the growth rate in the “medium term”, and the sensitivity analysis conducted with respect to that figure.
- This also highlights the importance (mentioned in the last Chapter) of using “sensitivity analysis thinking” as a model design tool.
Mixed Approaches
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?”.
Recommended Exercises
Please experiment with building a simple mixed-form model, such as that shown in rows 2-4 for the Salary in the above image.
Time-Based Scenario Decks: An Introduction
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.