In models with a time axis, there are many instances where one wishes for the value of an input parameter to change gradually over time. For example:
In this section, we provide examples of the implementation of the calculations of these types of “tapering” processes. The section covers both linear tapering and percentage-based (geometric or exponential) tapering.
The examples are based on the case that often arise in practice, namely that the value of the parameter to be used (such as the periodic growth rate):
As a first example, the following image shows a completed set of calculations, in which the growth rate of Sales Revenues is 10% until year 3, 5% from year 7, and tapers gradually in between (which is emphasised using the shaded cells in row 7):
Thus, the overall process has four input parameters i.e. the growth figures 10%, 5% and the time points 3 and 7.
Noting that – for any single time period in the model – there are exactly three possible cases for the “time bracket” in which it is i.e:
To create such a formula, there are two main logical components:
This can therefore be implemented by using IFS function to check which of the time-bracket conditions is met, and then applying the relevant growth figure in each case.
For example, for cell J7 in the first image above, the following image shows the first two sets of arguments for the IFS function, which returns the growth rates for the two “edge” cases i.e where the time period under considearion is period 3 or before, and where it is from period 7 onwards:
Between these two times (i.e. for the tapering period from period 3 to period 7), the AND function can be used as the thir logical test for the function:
The final pont to add, under the argument “Value_if_true3” (which is not shown in the above image) is the formula to be used for the tapering. There are two main forms, which are discussed and presented in the following examples:
A second example of linear tapering, is shown in the following image. In this case, it is used to taper a cost item, which is initially $100 for three periods, and then tapers down to $50 by period, with the periodic change within each period of the tapering being $12.50 (as calculated and shown in cell G6).
Within the tapering period, the formula uses for the tapering simply reflects the effect of the cumulated number of periods since the start of the tapering:
Note that in linear tapering – as shown by the percentage figures underneath the following image – the percentage change in the item is not constant: For a declining profile, the constant level of absolute change ($12.50 of decline per period) becomes an increasing percentage of that period’s starting value:
Similarly, for an increasing profile (such as if the values for Cost were to increase from $50 to $100 from period 3 to period 7), the changes or growth rates would form a decreasing series, rather than an increasing one.
To create a situation where the change in each period of the tapering is proportional to the starting value (i.e. a constant rate of growth or change in the tapered value), a different formula is required.
Using the same costs example above, the required periodic change (r) can be calculated as the amount required so that – when cumulated over 4 years – the figure of $100 would change to $50. That is, the value of r must satisfy the equation 100*(1+r)^4 =50. That is r=(100/50)^(1/4)-1, or in terms of the cell references in the model, the formula in cell G6 is:
This is then applied within the IFS function, to reflect the effect of the cumulative effect of the appropriate period:
The result is then that the changes in each period of the tapering are of the same percentage amount, as shown by the figures underneath the following image:
This is known as proportional, geometric or exponential tapering.
Proportional tapering is intuitively and logically often more appealing than linear tapering. However, its main disadvantage is the values used must not be zero or of opposite signs.
It is possible to combine the effects of two tapers. For example:
Thus, the total nominal cost (i.e. the actual $ value in that period) would need to reflect these two competing trends (one which reduces real terms cost, whilst the other increases cost due to inflation). The following image shows an example, in which the inflation factor is dominant in the early and ending periods of the model, whilst the productivity improvements are dominant in the middle part:
This is based on the following assumptions and model calculations: