1.5 Updating with New Actuals Data as Time Progresses
In an earlier course, we discussed the reversal in logical flow as one transitions from historical to forecast figures. For example, in the model used in the last section, the historical growth figures were calculated from the actual Sales Revenues, whereas the Sales Revenue were forecast using assumptions about the future growth rate.
As a result, the common approaches to growth-based forecasting calculations do not fully respect the “top-to-bottom” principle. This is shown in the image below:
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 achieved by using separate lines for historical data and for the forecast (even if the conceptual logic reversal is still present):
(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).
Using ISBLANK to Detect Updated Data
The above structure creates a separation of the historical and the forecast data. However, it is static in the sense that it would not allow for data to be included as it becomes available in the future. For example, the following image shows that as soon as the true Year 1 figure (such as $120) is available and entered into the model, then there would be two Sales Revenue figures for that year, and inconsistencies in the growth rate information:
Of course, the model could be adapted by altering all the required elements to extend the part of the model associated with historical data. However, as shown in the following image, this involves adapting all the formulas in the first period of the forecast (i.e. all the formulas in all the rows of column H).
Practically speaking, this represents a level of work that is nearly equivalent to building the entire model from scratch. In a large model (with hundreds of rows, for example), this process could be very time-consuming and error-prone.
If such updates are required to be done regularly, it can therefore make sense to create a model that allows this to be done in the most effective way.
This can be done by creating a model which stores any historic data in a separate range and – for any particular period – builds the forecast calculations using:
- The forecasting assumptions, for periods when historical data is not present.
- Historical data, if it is present. That is, the forecast essential recalculates the historical data in this case, so that a single forecast line can represent both the historical values and the “genuine” forecast.
That is, the row structure would therefore be:
… and the model’s calculations would be set up so that (see the following image)
- A single forecast for Sales Revenue is created (row 6 in the image).
- This forecast uses either the growth rate implied by historical data (when such data is present), but uses the forecast assumption (when such data is not present):
The ISBLANK function can be used to detect whether historical data is present, so that the formula in cell C5 is:
Thus, the first historical figure (cell F4) is directly carried into the integrated forecast (cell F6), whereas the other elements of this forecast (cells G6 to L6) are calculated:
When new data is added, the growth figures and integrated forecast are updated automatically. For example, the following image shows the effect of the addition of the actual Sales Revenue data for Year 1 and Year 2 (in cells H4 and I4), which leads to these figures being used to determine the relevant growth rates (cells H5 and I5) that are then used the reforecast the same figures (cell H6 and I6) in the integrated forecast line, whilst the subsequent forecast is calculated using the original assumptions:
