Driver-Based Revenue Forecasting, Step by Step

A driver-based forecast predicts revenue by modeling the handful of operating numbers that produce it, instead of extending last year's growth line. Its main advantage shows up when the forecast is wrong, which it will be: you can see which assumption missed and by how much, and fix that one number instead of guessing at a new trend.

This is general information, not financial advice. The worked example is a made-up online coffee subscription that ships one bag a month, and every figure in it is illustrative.

Step 1: choose the drivers

Start by writing revenue as an equation made of things you can measure. For the coffee subscription:

• Revenue in a month = active subscribers x price per box.
• Active subscribers this month = last month's subscribers x (1 - monthly churn) + new subscribers.
• New subscribers = site visitors x the rate at which visitors subscribe.

That gives four drivers: traffic, conversion, price and churn. Each earns its place by passing three tests:

1. You can measure it every month from your own records. Traffic comes from web analytics, conversion from checkout data, price from billing (after discounts), churn from subscription cancellations.
2. It plausibly causes revenue, rather than just moving alongside it.
3. Someone can act on it, or at least watch it closely enough to react.

Resist adding drivers for their own sake. Every driver is an assumption you will have to defend and update. Start with four to six, and add one only when a forecast miss shows you need it. If a number cannot be measured monthly, it belongs in a note, not in the model.

Step 2: build the model

Put every assumption on one Inputs tab, and let the Model tab contain only formulas that point at those cells. Nothing typed into a formula, ever. When the assumptions change, you change one cell and the whole forecast updates.

Inputs tab
B1  Starting subscribers       1000
B2  Monthly traffic growth     2%
B3  Visit-to-subscriber rate   1.5%
B4  Price per box              22
B5  Monthly churn              8%
B6  Month 1 visitors           20000

Model tab (column B = month 0, column C = month 1, and so on)
B5  Active subscribers, month 0   =Inputs!B1
C3  Visitors, month 1             =Inputs!B6
D3  Visitors, month 2 onward      =C3 * (1 + Inputs!$B$2)
C4  New subscribers               =C3 * Inputs!$B$3
C5  Active subscribers            =B5 * (1 - Inputs!$B$5) + C4
C6  Revenue                       =C5 * Inputs!$B$4

Copy D3 to the right, and copy C4:C6 to the right, for as many months as you need.

State your timing conventions in writing. This model bills new subscribers in the month they join and applies churn to the subscribers active at the start of the month. Other conventions are fine, as long as the model and your actuals follow the same one.

With the example inputs, the first months and month 12 come out like this (rounded for display; the model keeps the decimals):

One property of this structure is worth knowing before you trust any subscription forecast. If new subscribers arrive at a steady rate and churn holds constant, active subscribers level off at new subscribers divided by churn. With flat traffic at 20,000 visitors, the example adds 300 subscribers a month, so the base would settle near 300 / 0.08 = 3,750. Churn sets the ceiling.

Step 3: build scenarios

A single forecast invites false precision. Build three scenarios, and change drivers together so that each scenario tells a coherent story, rather than nudging each number up or down by the same amount.

Write one sentence under each scenario explaining what would have to be true for it to happen. "A competitor launches at a lower price, our ads get more expensive, and more subscribers cancel after their first box" is a downside you can watch for. "Everything is 20% worse" is not.

In Excel, Scenario Manager (under Data, then What-If Analysis) saves a set of input values as a named scenario, allows up to 32 changing values per scenario, and can produce a summary report that compares scenarios side by side. A simpler approach that works in any spreadsheet: keep three columns of inputs and a single selector cell that the model reads from.

Step 4: test sensitivity

Scenarios show plausible futures. Sensitivity shows which assumption matters most. Move one driver at a time by the same relative amount, 10% down and 10% up, and record month 12 revenue:

Traffic and conversion tie exactly because new subscribers are visitors multiplied by conversion, so a 10% move in either one moves the product by the same amount. Swing is computed before rounding, which is why it can differ by $1 from the two rounded figures beside it. Sorted by swing, this is the list of what to measure most carefully and what to argue about least. Churn looks modest over twelve months because its effect compounds past the forecast horizon. Using the ceiling from Step 2, cutting churn by 10% (from 8% to 7.2%) raises the long-run subscriber ceiling from 3,750 to about 4,167, roughly 11% higher. Extend the horizon before you conclude that churn does not matter.

To see two drivers interact, use a two-variable grid. Excel's data table (Data, then What-If Analysis, then Data Table) builds one automatically and handles at most two variables. Here is month 12 revenue across conversion and churn:

Read it diagonally. At 1.2% conversion and 6% churn, the business earns more in month 12 ($62,763) than at 1.5% conversion and 10% churn ($60,433). A grid like this helps decide whether the next quarter's effort should go into getting subscribers or keeping them.

Step 5: update monthly against actuals

A forecast earns trust by being checked. At each month end, put actual drivers next to forecast drivers, not just actual revenue next to forecast revenue.

Revenue landed almost exactly on forecast, and yet every driver except price missed. Traffic came in short, conversion ran well ahead, and churn was worse. A revenue-only check would have called this month a success and learned nothing. The driver view says the traffic assumption needs a look, the conversion gain needs explaining before anyone counts on it, and churn needs attention now.

Track accuracy over time as well. Forecasting: Principles and Practice by Rob J Hyndman and George Athanasopoulos defines the forecast error as the actual value minus the forecast, and the mean absolute percentage error (MAPE) as the average of the absolute percentage errors. It also flags the limits: percentage errors are undefined when the actual value is zero and penalize negative errors more heavily than positive ones. Alongside MAPE, keep the plain average error with its sign. If it stays negative month after month, the forecast is consistently optimistic, and that is a bias to fix rather than bad luck.

Then roll the forecast forward so it always covers the next twelve months. Change an assumption only when the actuals give you a reason, and log each change with the date, the old value, the new value and the evidence. After a few quarters, that log becomes one of the most useful parts of the model: it shows which of your instincts about the business hold up and which ones you should stop trusting.