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:
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.