How to Choose Forecast Drivers
So you’re ready to implement driver-based forecasting but don’t know how to choose forecast drivers? Selecting drivers is easier than you may think, and Excel will do the heavy lifting. Let’s refresh on what a forecast driver and then walk through how to select the best ones for your business.
What are Forecast Drivers?
Driver-based forecasting uses the formula rate x volume = output. By varying the rates and volumes (drivers), you can project Revenues, Expenses, and even KPIs.
In turn, forecast drivers are the rate and volume inputs that allow you to generate an output. Here are some examples of rate and volume drivers and the corresponding outputs.
Selecting Forecast Drivers
The best way to determine forecast drivers is to calculate the correlation between variables. This is a concept that we visited as part of forecasting using linear regression.
Linear regression explains the relationship between two variables by creating the best fit line. The tighter the relationship between the variables (correlation), the better the line fits. For example, you could run a linear regression on the number of visitors to a store and the sales revenue each day. You would expect these two items to have a high correlation. You could do the same for the number of website hits and the corresponding sales revenue each day.
For Finance, we can use the best fit line for forecasting. Taking the example above, if we know historical website hits and sales revenue, we can generate the best fit line and forecast our revenue given a certain number of website hits.
Flipping this around, we can use correlation to test how well a driver will predict the output. And the great news is, we can use our actual P&L data to run the correlation.
Example: Selecting Drivers for Sales Revenue
The Formula
The formula in Excel to run a correlation is =CORREL(array 1, array 2). This is a straightforward formula with a complex calculation behind it. Using the same number of cells, select the range for the two variables you want to test as array 1 and array 2, respectively.
Step 1: Layout the data set
In this example, we want to select a forecast driver for sales revenue. Let’s test three drivers using six months of actual P&L data. We will test wage expense, average revenue per unit, and units sold. First, we will lay out the data set.
Step 2: Correlation Formulas
Next, we will add a column for the correlation formulas. Sales revenue will be array 1, and the corresponding driver will be array 2.
Step 3: Analyze the Results
Finally, we will evaluate the completed analysis.
A correlation can have a value from -1 (a perfectly inverse relationship) to 1 (a perfect relationship). On the other hand, 0 means there is no relationship between the variables.
Wage expense correlates 0.66. However, this is where “correlation does not equal causation.” Wage expense is not driving sales revenue. Higher sales revenue is increasing the demand for wage expenses. Sales revenue might be a driver for wage expense but not the other way around.
Average revenue correlates 0. It is constant across the periods (a fixed price product, rare, but useful for doing Excel examples). Units sold, on the other hand, are a perfect correlation with sales revenue. Absent a change in average revenue (which would reduce the correlation), units sold are the best predictor of sales revenue.
Have any questions on how to choose forecast drivers? Are there other topics you would like us to cover? Leave a comment below and let us know! And make sure to subscribe to our Newsletter to receive exclusive financial news right to your inbox.
