2.3 Logic Reversals: Transitioning from Historical Data to Forecasts
In practice, when building models, one will use historical data to provide guidance on the value of the assumptions that should be used in the forecast. There are various ways that historical values could be used to information the forecast. For example, the forecast assumptions could:
- Be set to be exactly equal to the most recent figure historical figure.
- Use the average of historical figures (if several prior periods are available).
- Reflect trends in historical figures (for example, the growth rate over recent years may have been declining, so to use an average or a current figure could result in an overestimate).
- Use any historical information as a guide only, with forecast values reflecting not only the historical information (in some way), but also take into account other information that one is aware of (such as if a company were to shortly be introducing a major new product for the first time in several years).
Example of A Logic Reversal
Let us assume that the assumptions for the forecasted growth rate are to be determined after first analysing the historical performance. For example, in the following image, there are two years of historical data (with values $92m and $100m), so that the historic growth rate in the prior year was around 8.7% (i.e. (100-92)/92). In the model in the image below, this figure is used only as a guide – the base case as shown is for the forecast growth rate to be 10% per year:
The logic reversal refers to that fact that – in the historical part of the model – the growth rate is calculated from Sales Revenue, whereas – -in the forecast part – Sales Revenue is calculated from the assumed growth rates.
(The historical part uses the “left-to-right and top-to-bottom” flow, whereas the forecast uses the mixed flow (“left-to-right and bottom-to-top”).
Note that this reversal is a result of the underlying logic of the model, not its layout or the way it is implemented in Excel. In fact, the “top-to-bottom” principle of an Excel implementation can be created by using separate lines for historical data and for the forecast (as discussed in the last section):
However, the logic reversal is still present in this model (since historical growth is calculated from Sales Revenue, and Sales Revenue is forecast using growth assumptions).
Note: Once again, this model is twice the size (i.e. 4 rows instead of 2) of the model that uses the “standard” or “mixed” flow approach, and so this approach (i.e. having separate lines for the historical and forecast values of the same item) is not often used in practice.