Building a driver-based rolling forecast

This note sets out a method for building and running a driver-based rolling forecast in an organisation of any size. It applies the forecasting and planning domains of the CBA standards for FP&A and for financial modelling. It is written for the finance professional who owns the forecast, and for the operational managers who contribute to it.

A working template accompanies the note: a quarterly rolling forecast model with a driver sheet, calculations, a variance view and an accuracy log.

A forecast is the organisation's best current estimate of what will happen. Before building one, agree the decisions it will support, because they determine its horizon, its level of detail and how often it is updated. Typical uses are:

• managing cash and funding headroom;
• deciding whether to accelerate, delay or stop discretionary spending;
• giving early warning of results that will differ from the budget;
• informing hiring, inventory and capacity decisions.

A forecast built to manage cash needs monthly detail on working capital and a clear view of the lowest cash point. A forecast built to steer spending needs cost detail by owner. Trying to serve every purpose at the most detailed level produces a forecast that is too heavy to update and too slow to be useful.

A rolling forecast always looks the same distance ahead. When a period closes, it drops off the front and a new period is added at the end. Common choices are a horizon of four to six quarters, updated quarterly, or twelve to eighteen months, updated monthly.

Choose the horizon from the lead time of the decisions the forecast supports. If committing to new capacity takes nine months, a forecast that ends in six months cannot inform it. Choose the frequency from how quickly the business changes and how much effort an update takes. A monthly update that the organisation cannot sustain will decay into a copy of the previous month.

A driver is an operational quantity that causes a financial result: units sold, average price, conversion rate, headcount, average cost per employee, days customers take to pay. Forecasting drivers rather than financial totals has three benefits. Each change in the forecast traces to a named cause. The people who manage the driver can own its forecast. And the model shows the effect of a decision before it is taken.

Good drivers share four characteristics:

1. They explain most of the result. A small number of drivers usually accounts for most of the movement in revenue and cost. Find them from history before adding more.
2. They can be measured. A driver whose actual value is not reported cannot be tested against its forecast.
3. Someone owns them. Each driver should have an owner outside finance who is accountable for its forecast.
4. They are stable in their relationship to the result. If the link between the driver and the financial outcome changes unpredictably, the driver adds noise rather than insight.

Resist the temptation to model everything. A forecast with a dozen well-chosen drivers, updated reliably, is more useful than one with two hundred that nobody has time to review.

Separate the model into three parts:

• Inputs: every driver and assumption, by period, with its owner and the date it was last updated. Nothing else in the model should contain a typed number.
• Calculations: the logic that turns drivers into revenue, costs, working capital and cash. Each row should use one consistent formula across all periods.
• Outputs: the summaries, charts and variance views that management reads.

Build in integrity checks — the balance sheet balances, cash reconciles, totals agree to their components — and show their status prominently. A forecast whose checks are failing should not be circulated.

Where working capital is material, forecast it from drivers too: receivables from sales and days outstanding, inventory from cost of sales and days held, payables from purchases and days taken to pay. Show the monthly profile of cash, not only the period-end balance.

The budget is a commitment, the forecast is a best estimate and the target is an ambition. Merging them corrupts all three. In particular, if the forecast is expected to equal the budget, managers stop reporting bad news until it arrives in the actual results.

In practice:

• label every figure in every report as budget, forecast or target;
• report variance to budget and change in forecast as separate measures;
• do not start the forecast from the budget and adjust it; build it from the drivers;
• where possible, let finance own the forecast, with operational input, so that it is not also a performance commitment.

A dependable update follows the same sequence each time:

1. Close the period and load actual results and actual driver values.
2. Roll the horizon: remove the closed period and add a new one at the end.
3. Ask each driver owner to review their forecast against the latest evidence, and to record the reason for any change.
4. Recalculate, review the integrity checks and investigate any unexpected movement.
5. Review the forecast with the owners of the largest drivers before it is published.
6. Publish, with a short commentary on what changed and why.

Set a fixed timetable and keep to it. A forecast that arrives late loses the decisions it was meant to inform.

Keep a record of every forecast of every key line, and compare each with what eventually happened. Two measures matter:

• Accuracy: the size of the error, typically as a percentage of the actual result. It shows whether the forecast is precise enough for the decisions it supports.
• Bias: whether errors are consistently in one direction. A forecast that is always optimistic, or always matches the budget until the last moment, is biased, and the bias usually has an organisational cause.

Review accuracy and bias with the driver owners at least twice a year. The aim is not to punish error but to find drivers that are consistently mis-forecast and improve how they are estimated.

The forecast pack should be short. For most audiences it needs:

• the forecast for the key measures against budget and prior forecast;
• a bridge explaining the main movements by driver;
• the forecast cash position, including its lowest point and when that falls;
• the principal risks and opportunities not included in the forecast, with an estimate of their size.

Write the commentary in terms of decisions: what the forecast implies management should consider doing.

• Too much detail. The forecast takes so long to update that it is always out of date.
• Forecasting from the budget. The forecast inherits the budget's assumptions and cannot warn of their failure.
• Hidden hardcodes. Typed numbers in calculation cells that do not update when drivers change.
• No accuracy record. Without one, nobody can tell whether the forecast is improving.
• Finance forecasting alone. Driver forecasts made without the people who manage the drivers lack the information and the ownership that make them credible.