Forecasting from historical revenue

Forecasting from historical revenue means predicting future revenue from the pattern in what you have already invoiced: the trend, the seasonality and the stable base of recurring customers. It works well for companies with a lot of recurring revenue and a stable customer base, and poorly for companies where a few large deals or projects decide the year. In practice it is the reliable floor of your forecast, on top of which you place the pipeline for new revenue.

Why is historical revenue the most honest source?

A pipeline in the CRM is a collection of expectations. Historical revenue is a collection of facts. Every invoice line in Xero, NetSuite, SAP, Exact or AFAS is an amount a customer genuinely owed, on a real date. There is no account manager's optimism in it, and no deal that has been about to close "next week" for three months.

That makes historical revenue the best basis for everything that repeats: maintenance contracts, licences, subscriptions, regular customers who order every month, framework agreements with predictable volumes. For many B2B companies with EUR 2 million or more in revenue, that is a large part of revenue, and yet the forecast is often built from the CRM, where recurring revenue is recorded poorly or not at all. Why that is a problem is explained in forecasting from CRM data.

The broader explanation of what revenue forecasting is shows that a good forecast consists of layers. Historical revenue is the bottom layer, and usually the thickest.

What data do you need?

You need at least two, preferably three, years of invoice lines, with for each line:

• Customer, with a fixed customer number (not just a name that has been spelled differently over the years).
• Product group or revenue type, so you can separate recurring revenue from one-off revenue.
• Invoice date and, if you have it, service date. An annual contract invoiced in advance in January is revenue over twelve months. If you forecast on invoice date, you see a January peak that does not really exist.
• Credit notes, linked to the original invoice. Without that link you count revenue that was later reversed.
• Price and quantity separately, so you can see whether growth came from more volume or from a higher price.

This seems obvious, but in most accounting systems this is exactly where the mess is. Revenue types have been reclassified over time, customers have been created twice and credit notes stand on their own. An hour of cleaning before you start calculating delivers more than the best model.

Which forecasting methods use historical revenue?

Last year plus a percentage

The most widely used method: the same month last year, times a growth percentage. It is quick and everyone understands it. The drawback is that every fluke from last year moves along with it. If you had one large one-off order last March, you forecast it again this year.

Moving average

You take the average of the last three, six or twelve months and project it forward. That dampens flukes, but ignores seasonality. For a company without a clear seasonal pattern, it is a perfectly good starting point.

Trend plus seasonal index

Here you split revenue into two parts: the underlying trend (is revenue growing or shrinking) and the seasonal pattern (which months are structurally higher or lower). A seasonal index is simply how much a month deviates on average from an average month, calculated over several years.

Worked example: suppose your revenue over the last twelve months was EUR 6.0 million, an average of EUR 500,000 a month. Over the past three years, July was on average at 80 percent of an average month (index 0.8) and November at 120 percent (index 1.2). Underlying growth was 6 percent a year. Then your forecast for July next year is EUR 500,000 x 1.06 x 0.8 = EUR 424,000, and for November EUR 500,000 x 1.06 x 1.2 = EUR 636,000. With "last year plus 6 percent", an outlier in last year's July would have given you a very different number.

Splitting existing and new revenue

The method that pays off most in B2B is not the most mathematically sophisticated, but the best split. You forecast three streams separately:

1. Recurring revenue from existing customers. Contracts, subscriptions, regular buyers. You forecast this per customer or per cohort, using their historical retention and growth.
2. One-off revenue from existing customers. Projects, extra work, individual orders. A historical average per customer group works reasonably well here.
3. Revenue from new customers. History can only give a rough estimate of this. This is where the pipeline should do the work.

The split also shows you what drives the forecast. If the total stays flat but the recurring base is shrinking and new customers fill the gap, that is a very different business from the reverse.

Statistical models

Methods such as exponential smoothing or ARIMA essentially do the same as trend plus seasonality, but estimate the weights themselves and can cope with noise. They are built into Excel (the FORECAST.ETS function), Power BI and most data tools. They are useful as soon as you forecast many series at once, for example per product group or per branch. Machine learning builds on this by also weighing signals other than revenue itself.

Where does forecasting from history go wrong?

Historical revenue is honest, but not neutral. Five things distort it structurally.

One-off outliers. A single EUR 400,000 project in a EUR 5 million year is eight percent of your revenue. If you include it in the trend, you forecast growth that is not there. Take large one-off items out and forecast them separately, or not at all.

Price indexation in the history. If you indexed by 7 and 4 percent over the past two years, that is in your growth figure. Growth in euros is then not growth in volume. If you forecast the same growth next year while indexation turns out lower, you are too high. The reverse also applies: if indexations were forgotten last year, your history is too low and you carry that leak into your forecast. That leak is the subject of revenue leakage from missed price indexation.

Lost large customers. A customer who left in October is still in your twelve-month average for another ten months. A model on totals only notices when it is too late. A model per customer sees it immediately.

Changed billing moments. If you move from annual invoicing in advance to monthly invoicing, or the other way round, your entire seasonal pattern shifts. The model thinks January has collapsed, when only the invoicing has changed.

Leakage you carry forward. This is the least visible. If some of your won deals were never invoiced, or extra work is structurally left unbilled, your history is too low. A forecast built on that history neatly predicts the same leak. The forecast is then accurate, but the number is too low. So first check whether your recurring revenue itself is correct, see how do you find errors in recurring revenue.

How do you combine history with the pipeline?

A forecast from history and a forecast from pipeline are not competitors. They answer different questions. History tells you what happens if you do what you have always done. Pipeline tells you what could come on top.

The approach that holds up best in practice:

1. Forecast the recurring base from history, per customer or cohort, with historical retention.
2. Add the weighted pipeline for new customers, but only deals with a realistic close date within the period.
3. Compare the total with a simple trend line at total level. If they differ by more than a few percent, find out why before you send a number to the board.

That third step is a good check on the pipeline. If the pipeline promises 25 percent growth and history has never shown more than 8 percent, someone needs to explain what is different this year. That conversation belongs in the forecast, not afterwards in the explanation of why it missed.

If you work with several variants, you are already doing scenario forecasting: a base scenario from history, a scenario with the pipeline, and a scenario in which your largest customer leaves.

How certain is a historical forecast?

A forecast without a range is a wish. From history you can set that range fairly honestly: forecast the past twelve months using only the data you had at the time, and see how far off you were. That deviation is your realistic margin. If your monthly forecast was historically 6 percent off on average, a 2 percent margin is nonsense, however elegant the model. More on that margin in forecast confidence explained.

Quarterly and annual forecasts are almost always more accurate than monthly ones, because shifts within the quarter cancel each other out. So steer at quarter level and use the monthly forecast for cash planning, not for judging sales.

A step-by-step plan for tomorrow

1. Export three years of invoice lines with customer number, revenue type, date and amount, and link credit notes to their invoices.
2. Mark revenue as recurring or one-off. If in doubt, check whether the customer bought in the same revenue type in at least two of the three years.
3. Take out large one-off items and put them on a separate list.
4. Calculate a seasonal index per month over the three years.
5. Forecast the recurring base per customer or customer group, with historical retention.
6. Add the weighted pipeline for new customers.
7. Back-test your method on the past year and note how far off you were. That is your margin.
8. Put the forecast next to reality every quarter and change one thing at a time.

Frequently asked questions

How many years of history do you need?

For a trend without seasonality, twelve months is enough. For a reliable seasonal index, you need at least two, preferably three, full years. More than five years rarely adds anything, because your business has changed too much in that time.

Does this also work for project businesses?

Partly. The recurring base (maintenance, service, framework contracts) can be forecast well from history. Project revenue itself cannot: it depends on what is in the order book and how quickly the work is carried out. For that you forecast from work in progress.

Should you forecast on invoice date or service date?

For cash planning, on invoice date, because that is when the money comes in. For steering on revenue and margin, on service date, because that is when the work is delivered. Do not mix them in one number.

Is Excel enough?

For a single company with a manageable number of revenue types: yes. Excel has built-in functions for trend and seasonality. It gets difficult as soon as you want to forecast per customer, combine several sources or update the forecast automatically every week.

Does historical revenue also predict churn?

Only on average. The historical retention rate tells you how much revenue you lose on average, not which customer will leave. For that you need signals per customer, such as falling volumes or more support requests.