2.2 Growth-Based Forecasting
Financial models contain a forecast which is inherently associated with a time period. In some models, the time period is a single period, or a single point in time. For example, in the Starter Course, the volume of ocean water was considered at a single point in time (i.e. instantaneously). Similarly, the models for Savings and Business Economics models generally represented a single time period (such as a year). However, the majority of models in practice contain multiple time periods for the forecast (as well as some historical data).
Hard-Coded or Calculated?
Let us assume that a business had Sales Revenue of $100m in the year immediately passed (called the Base Year in the image). If we wished to build a model which includes the forecasted Sales Revenue for the next two years, one could of course simply type one’s estimate (shown in red) of the figures for those future years into an Excel worksheet, for example:
In fact, whilst there can be cases where “hard-coding” the forecasted figures could make sense, in general it is better for such figures to be calculated. There are several possible disadvantages to hard-coding the figures in this way:
- First, it is not easy to reuse the logic. For example, if an otherwise identical (competitor business) was twice a large (with a Base Year figure of $200m), the model could not be applied to this business: All figures would need to be input again from scratch (i.e. $200m, $220m, and $240m) in this example.
- Second, the opportunities to conduct sensitivity analysis are limited, or may be non-existent, or not realistic. For example, one could not (correctly) measure the effect on Year 2 Sales Revenue of a change in the base year Sales Revenue, since the model is treating these as unlinked (and therefore independent to each other).
- Third, one may input unrealistic figures, since they are not calculated with a specific methodology or logic. Typically, it is very easy to misjudge the effect of growth over several time periods (due to compounding). For example, the figures used in the above image (i.e. $100m, $110m, then $120m) in fact implies that the growth rate is lower in Year 2 than it is in Year 1 (9.1% compared to 10% p.a., see below). This could be unintended and potentially logically inconsistent.
Growth Measurement using the “Classical” or “Effective” Method
The periodic growth rate that is implied by assumed or known values of an item in each of two periods can be calculated using the “classical” growth formula. This is is based on calculating the “effective” (i.e. the observed) change in the values, as a proportion of the initial value:
Using this formula in the above example shows (cell D4) the value of 10% p.a. growth in Year 1 (relative to the Base Year), and a growth rate of 9.1% p.a. between Year 1 and Year 2:
Growth Forecasting using the “Classical” or “Effective” Method
In general, rather than assuming the absolute figures directly to build a model, it is better to calculate them by assuming the growth rate(s). This typically creates a more robust and transparent (and reusable) logic: In the absence of known differences between future years (such as Year 1 and Year 2), it would seem more reasonable to assume that the growth rate in each period is the same. That is changes, improvements, or innovations could create growth that is proportional to the situation existing at that time (such as the level of Sales Revenues in any year).
If one assumes a growth rate during period, then the value of the item for that period can be calculated from the value in the prior period by applying the “classical” of effective growth rate to the prior period’s value i.e.:
The following image shows this in practice: The cells with red font (D4 and E4) contain the assumptions for the growth rate (which is here assumed to be constant in the two periods), whilst cells D3 and E3 contain the forecast figures for Sales Revenue. That is, the core method to forecast items which are expected to grow in time is to make an assumption for the growth rate(s), and then to calculate forward from prior values:
Note also the label in cell B4 has been changed from “Implied Growth Rate…” to “Assumed Growth Rate”, in order to reflect this forecast method.
Direction of Calculation Flow: “Mixed” or “Pure”
Note that in the above image (which is also repeated below for convenience of reference when reading), the direction of the dependency arrows is a combination of:
- Left-to-right flow. For example, the formula in cell E3 involves using cell D3 (i.e. a cell on the same row, but to its left).
- Bottom-to-top flow. The formulas in cell D3 and E3 use the values of the growth rate assumption, that is held in the row below.
Whilst this type of calculation structure is very frequent in models, it does not respect the idealistic principle that flow should be “left-to-right” and “top-to-bottom” (i.e. not “bottom-to-top”). The concept of “model as you read” is another way of stating this idealistic (or “pure”) principle, which aims to make a model easier to understand.
Note that it would be possible to attain an ideal flow by separating items which are historical from those which are forecast. For example, each of the historical and forecast items could be placed on a separate line. The forecast line would be formed by bringing forward any historical data and then combining this with growth assumptions to produce the forecast.
The bringing forward of the historical data into the forecast is shown by the dependency arrow in column C:
… after which the growth formulas can be created in cell D5 by referring to C5 and D4 (thus having a left-to-right and top-to-bottom flow), and then similarly for cell E5:
However, this type of structure is not observed very often in many practical models. The main reason for this is likely to be that it makes the model larger (50% larger in this case, with 3 rows instead of 2), whilst the more “standard” structure with only 2 rows is sufficiently common and – due to its small size – relatively clear and transparent, even if it fails the idealistic best practice test.
Recommended Exercises
Please experiment building these models and variations from scratch, to ensure that you have mastered these issues!