2.5 Ratio-Based Forecasts
Some items in a model may be believed to vary in direct proportion to other items. That is, the dependent item is to be derived as a multiple of another (independent or precedent) item. For example, quite commonly some items are assumed to be linked to the level of sales revenue, such as:
- The value of invoices owned by customers (at any point in time) is derived as a proportion of the annual sales revenue.
- The cost of materials bought from suppliers varies in proportion to the sales revenue.
- The value of capital equipment needed to produce the goods varies in proportion to sales revenue (or future sales revenue).
These methods are often known as a “ratio-based” (or “multiples”) method. That is, the independent item would be forecast first, with the dependent item calculated from it (by multiplying the value of the independent item by the ratio or multiple). The value of the ratio (or proportion or multiple) that is used in the forecast would typically be derived from historical information (for example, by dividing the historical value of the dependent item by that of the independent item).
Example: Cost of Materials as Multiple of Sales Revenue
As an example, one may know that in the most recent year the cost of materials was $30m:
If it believed that the Cost of Materials will vary in proportion to Sales Revenue, then the proportion based on historical data could first be calculated, for example with historical Sales Revenue of $100m (in the same period), the proportion is 30%:
Then, if this ratio is assumed to be constant and the same as the most recent historical figure, then – after first forecasting Sales Revenue (e.g. using a growth-based method) – one can forecast the Cost of Materials ($m p.a.) as a multiple: That is, the forecast in row 7 (for Cost of Materials) is based on apply the assumed multiple to the forecast (of Sales Revenue) in row 4:
Once again, there is a “logic reversal” as the transition from historical to forecast information (as described in the last section): That is, in the historical part of the model, the ratio is calculated from the absolute value of Cost of Materials, whereas in the forecast part, the ratio is assumed, and is used to calculate the absolute value of the Cost of Materials (by multiplication with the Sales Revenue):
A Note on Sensitivities: As for the discussion in the last section, this model could be adapted so that that the (currently separate) items in cells E8 and F8 are linked to a single assumption (such as using the formula =E8 in cell F8), or – in a model with more time periods – a mixed approach can be used.
Historical Data: Further Comments
Note that to calculate a historical growth rate, one needs data from two time periods. However, to calculate a ratio, often only data from one period is necessary. Thus, one could be faced with data such as:
On the other hand, if Sales Revenue data is available for Year Prior 2 (as shown above), it is quite likely that one will also have an additional year of Cost of Materials data, for example:
Therefore, whereas there is only one data point to inform the assumption on future growth, there is more information for the ratios. As mentioned earlier, the treatment of how historical values should inform the forecast assumptions is beyond the current scope of the discussion. For the moment, we simply point out that it is quite common to have (one) more data point for ratios than for growth rates.
Recommended Exercises
Please experiment building these models and variations from scratch, to ensure that you have mastered these issues!