Predict revenue trends based on historical data and market indicators
✓ Free to download · ✓ Excel 2016+ and Google Sheets · ✓ Custom version in 24h
A forecast is a claim about the future that should be checkable afterwards. This template stores each forecast alongside what actually happened, so the error is measured rather than argued about.
The actual columns, with sample rows
| A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|
| 1 | Month | Baseline | Seasonal | Forecast | Actual | Error % |
| 2 | 2026-05 | 412,000 | 1.04 | 428,480 | 441,200 | +3.0% |
| 3 | 2026-06 | 418,000 | 0.97 | 405,460 | 398,100 | -1.8% |
| 4 | 2026-07 | 424,000 | 0.89 | 377,360 | 372,900 | -1.2% |
| 5 | 2026-08 | 430,000 | 0.91 | 391,300 | — | — |
Why each one is written the way it is
=FORECAST.ETS([@Month], History[Actual], History[Month], 12)
Exponential smoothing with a 12-period season length. It handles trend and seasonality together, which a trailing average does not.
=AVERAGEIFS(History[Actual],History[MonthNum],[@MonthNum])/AVERAGE(History[Actual])
The seasonal index for each calendar month, derived from your own history rather than assumed.
=([@Actual]-[@Forecast])/[@Forecast]
Signed forecast error. Keeping the sign matters — consistent optimism is a different problem from random noise, and only the sign distinguishes them.
=AVERAGE(ABS(Forecast[Error %]))
Mean absolute percentage error across the period. Below 10% is good for monthly revenue; above 25% means the model is not capturing something you know about.
A worked example with real numbers
A distributor's forecasts were within 5% most months, which everyone considered good. Storing the signed error revealed that eleven of twelve months were over-forecast — the pipeline conversion assumption was too generous, and the error only looked small because it was consistent. Adjusting the assumption cut the mean absolute error from 4.8% to 2.1% and, more usefully, removed the quarter-end scramble that the persistent shortfall had been causing.
Tab by tab
| Tab | Contents |
|---|---|
| History | Actuals by month, the input to every model. |
| Seasonal | Derived seasonal indices with the periods they came from. |
| Forecast | Forecast, actual and error, retained for every past period. |
| Accuracy | MAPE and bias over rolling windows. |
| README | How much history the model needs and when to re-fit it. |
What is included, and what to look at next
Specific to this workbook, not generic
Two full seasonal cycles minimum — 24 months for monthly data. With less, the model cannot separate trend from season and will project last year's noise forward.
Because otherwise you cannot tell whether the model is any good. Overwriting the forecast with the actual leaves you with a history of what happened and no measure of what you predicted.
Sheets has FORECAST and TREND but no ETS equivalent, so the template falls back to a seasonal index applied to a linear baseline. It is less sophisticated and, on most business series, within a point or two.
"This forecasting model has transformed our sales planning process. The accuracy of predictions has helped us make better strategic decisions."