Demand Forecast & Backtest Explorer
Use historical monthly demand to compare simple forecasting baselines before publishing a forward plan. The workbook keeps history, horizon, backtest depth, and interval coverage in Excel while Python for Excel handles full-horizon rolling evaluation, model selection, empirical error bands, and visualization.
Result preview
The supplied history is evaluated with seasonal-naive, three-month-average, and trend-plus-seasonality models. Trend + seasonality wins the baseline full-horizon rolling-origin comparison with 8.77% WAPE. The first forward forecast is 1,406.90 units with a 95% empirical error band of 1,136.55 to 1,677.26 units.
What this template does
- Preserves monthly demand history as editable worksheet inputs.
- Compares three understandable forecasting baselines rather than selecting a model by fit alone.
- Uses complete rolling origins that score every step in the selected forecast horizon.
- Reports WAPE, MAE, signed bias, origins used, and backtest point counts.
- Publishes the selected-model forecast and horizon-matched empirical lower/upper error bands back to Excel.
Why Python
Forecast evaluation is naturally iterative: each backtest origin must recompute the model using only information that would have been available at that time. NumPy and pandas make that time-aware loop compact while keeping the workbook focused on durable data, assumptions, and decision outputs.
Try it live
Change recent demand, forecast horizon, backtest origins, or interval coverage. The model comparison, selected model, chart horizon, and forecast recalculate from the same worksheet state.
Operating workflow
Author: a demand-planning analyst maintains the history contract, candidate models, rolling-backtest logic, validation rules, and published forecast tables.
Workbook user: a planner updates demand history and durable forecast controls, then reviews the selected baseline, backtest metrics, and forward forecast without maintaining the Python implementation.
For repeated planning cycles, save the completed notebook to open in App mode and validate the same workbook with a second user. App mode simplifies the presentation but does not restrict access to the saved source.
Download the Excel template
Inputs and assumptions
Demand History!A5:B35 contains 30 monthly observations. Dashboard!A5:B8 contains the forecast horizon, requested number of rolling origins, and empirical interval coverage. Demand must be non-negative and the history is assumed to be monthly and regularly spaced. Longer horizons can reduce the number of complete rolling origins available from the supplied history; the summary reports the count actually used.
Notebook implementation
The notebook separates input normalization, candidate-model definitions, rolling backtests, forecast/error-band construction, visualization, and publication into reactive cells. It publishes summary, comparison, and forecast worksheet tables with bf.publish(...). The notebook model selector changes only the diagnostic chart; durable forecast assumptions remain in Excel.
How the calculation/model works
Seasonal naive repeats the value from 12 months earlier. The three-month baseline carries the latest three-month mean forward. Trend + seasonality uses least squares on a time trend plus annual sine/cosine terms and clips negative demand forecasts to zero. Each eligible rolling origin predicts the complete worksheet-selected horizon, so model scoring and interval diagnostics use like-for-like forecast steps. The selected model minimizes WAPE, with MAE as the tie-breaker. For each future step, the error-band radius is the selected model’s same-horizon absolute backtest error at the worksheet-selected empirical quantile; no pooled-error or random-walk √h scaling assumption is used.
Validation / expected results
The baseline asserts Trend + seasonality, 8.77% WAPE, 110.71 MAE, +2.15% bias, 10 complete origins / 60 backtest points, and a first forecast of 1,406.90 with a 1,136.55–1,677.26 empirical band. Runtime scenarios also change the latest demand, exercise the one- and twelve-month horizon boundaries, reduce backtest origins to four, and narrow interval coverage to 80%.
Limitations
These are intentionally transparent baselines, not a full automatic forecasting suite. The interval is an empirical same-horizon absolute-error band, not a calibrated probabilistic forecast; with only a few complete origins, high quantiles can be noisy. Long horizons may use fewer origins than requested because every scored origin must have actuals for the entire horizon. Structural breaks, promotions, holidays, stockouts, missing periods, and explanatory variables are not modeled.
When to use this approach
Use it when a planning team needs a defensible baseline, wants to compare candidate forecasts with time-aware validation, or needs a benchmark before adopting a more complex forecasting method.