We take a look at a common type of financial model — the business driver or cost driver model. This is where you forecast costs or expenses based on the level of some relevant business driver.
For example, you may want to forecast advertising expense based on projected growth in revenue. Alternatively, you might forecast your phone expenses based on the projected number of employees. Some common cost drivers include:
rate of inflation
number of employees
number of units sold
salaries and wages
So what is the best way to set up your model to achieve this? The method we prefer is the lookup table approach. The key components of this approach are:
Set out all the cost drivers you’ll use in a section at the top of your model. (Rows 5-8 below.)
A column that will contain the cost driver for each cost line. (Column E below.)
A column containing the base levels of the cost drivers and the associated base levels of each cost line. (Column G below.)
Write a formula to calculate the cost forecast based on the driver specified. This is the same formula for every cost line and follows the design in blocks approach discussed earlier. (Cells I11:K16 below.)
Here’s a simple Excel spreadsheet with these components:
Basically what this formula is saying is “find the cost driver level for this period, divide this by the cost driver level for the base period, and multiply this by the base cost level”. The use of INDEX and MATCH is my preferred approach for looking up specific values in another table.
You can download this model and play around with it yourself. Do you have any ideas for improving on this technique?