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).
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:
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:
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.
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:
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.
Please experiment building these models and variations from scratch, to ensure that you have mastered these issues!