Forecasting in Excel With Python and Backtesting

How to compare demand-forecast models with full-horizon rolling-origin backtests in Excel instead of choosing a model from in-sample fit alone.

A forecasting workbook should not choose a model because it fits historical data well. It should test how candidate methods would have performed using only information that was available at each point in time.

That is the role of rolling-origin backtesting, also called time-series cross-validation. Forecasting: Principles and Practice describes the same core rule: each training set contains only observations that occurred before the forecast origin.

Boardflare’s Demand Forecast & Backtest Explorer keeps demand history and forecast controls in Excel, then uses Python to compare several transparent baseline models across repeated historical forecast origins. The workbook publishes the selected model, validation metrics, empirical error bands, and forward forecast back to Excel.

The broader lesson is simple:

Treat forecast selection as an out-of-sample evaluation problem, not a curve-fitting contest.

Why ordinary fit can be misleading

Suppose you have 30 months of demand. You can fit several models to all 30 observations and calculate how closely each model reproduces the same data.

That tells you something about fit. It does not tell you how the model would have performed when month 24 was the latest information available and months 25–27 were still unknown.

A rolling-origin backtest recreates that historical decision point:

Origin 1: train through month 20 → forecast months 21–23 → compare with actuals
Origin 2: train through month 21 → forecast months 22–24 → compare with actuals
Origin 3: train through month 22 → forecast months 23–25 → compare with actuals
...

The model is repeatedly refit using only the data available at each origin.

That prevents future observations from leaking into the historical evaluation.

Score the full forecast horizon

If the workbook is being used to make a six-month forecast, evaluation should reflect six-month forecasts rather than only the first step from each origin.

The current template therefore uses full-horizon rolling origins. At each eligible historical origin it forecasts every step in the worksheet-selected horizon and scores all of those predictions against the actual observations that followed.

For a six-month horizon and 10 complete origins, the comparison therefore contains 60 evaluated forecast points.

This matters because forecast difficulty often changes with horizon. A model that performs well one month ahead may be less useful six or twelve months ahead.

Longer horizons also reduce the number of complete origins available from a fixed history because every scored origin must have actual observations for the entire horizon. The workbook reports both the number of origins used and the total backtest point count so that tradeoff remains visible.

Start with understandable baselines

The published template compares three deliberately transparent methods:

• seasonal naive;
• three-month average;
• trend plus annual sine/cosine seasonality.

These are not intended as a universal forecasting suite. They are useful because each model has an understandable failure mode and gives more complex methods something to beat.

A strong forecasting process should include simple benchmarks even when you ultimately deploy a more sophisticated model. If a complex model cannot consistently improve on seasonal naive or a recent average under the same backtest, its additional complexity may not be justified.

Evaluate metrics that match the decision

The template reports:

• WAPE;
• MAE;
• signed bias;
• complete origins used;
• number of evaluated backtest points.

Each metric answers a different question.

MAE measures average absolute error in the original units.

WAPE scales total absolute error by total actual demand, which can be easier to compare across contexts with different volume.

Signed bias helps reveal systematic over- or under-forecasting that an absolute metric can hide.

No single metric is sufficient for every use case. For intermittent demand, service-level planning, or asymmetric business costs, you may need different measures. The point is to decide evaluation criteria before declaring a winner.

Keep durable forecast controls in Excel

Forecast horizon, requested backtest depth, history, and interval coverage are business-facing state, so the template keeps them in the workbook.

Python binds them once:

inputs = bf.inputs(
    history=bf.ref("Demand History!A5:B35", headers=True),
    controls=bf.ref("Dashboard!A5:B8", headers=True),
)
inputs

This allows a planner to update history or the intended horizon without editing Python code.

The notebook owns the repeated model-fitting, rolling evaluation, selection, and error-band construction—logic that is much easier to express and test in Python than with a large collection of helper ranges.

Keep every backtest origin time-aware

The most important implementation rule is that each historical origin must use only data that would have been available then.

Common leakage mistakes include:

• normalizing the full dataset before splitting;
• calculating seasonal factors from future periods;
• choosing model parameters using the entire history;
• using centered rolling windows;
• imputing missing values with information from later dates.

When reviewing code, ask: Could this line know anything about observations after the current origin?

If yes, the historical score may be optimistic.

Make model selection deterministic

The template selects the model with the lowest WAPE and uses MAE as a tie-breaker.

That rule is intentionally simple. The important feature is that the selection criterion is explicit and reproducible.

In a production process, you might add constraints such as:

• minimum improvement over a benchmark;
• maximum allowed bias;
• stability across recent origins;
• separate selection by forecast horizon;
• business overrides for known structural breaks.

If overrides exist, record them. A model-selection process is hard to audit when the final model changes because someone “liked the chart better.”

Build uncertainty bands from comparable forecast errors

A point forecast is not a complete description of uncertainty.

The current template builds its lower and upper bands from the selected model’s same-horizon absolute backtest errors. For each future step, it takes the worksheet-selected empirical quantile of the historical absolute errors observed at that same forecast horizon and applies that radius around the point forecast.

For example, the six-month-ahead band is based on historical six-month-ahead errors rather than pooling it with one-month-ahead errors or applying a generic square-root-of-horizon scaling rule.

That makes the interval logic easy to inspect, but it is still a teaching approach rather than a fully calibrated probabilistic forecast. With few complete origins, high empirical quantiles can be noisy. Structural breaks, changing volatility, intermittent demand, and asymmetric error distributions can also make historical absolute errors a weak guide to future uncertainty.

Treat interval methodology as a separate modeling choice rather than a cosmetic band around the line chart.

Validate with known worksheet scenarios

The canonical workbook records a baseline in which trend plus seasonality wins the full-horizon comparison with 8.77% WAPE, 110.71 MAE, +2.15% signed bias, 10 complete origins, and 60 evaluated backtest points.

Its first forward forecast is 1,406.90 units with a 95% empirical error band of 1,136.55 to 1,677.26.

A changed-input scenario sets the latest demand observation to 1,800. Trend plus seasonality still wins, but the comparison and forecast change: WAPE becomes 9.24%, MAE 117.21, signed bias +1.63%, and the first forecast becomes 1,479.48 with a band of 1,209.13 to 1,749.84.

The template also tests horizon boundaries. With a one-month horizon, 10 origins produce 10 backtest points. With a twelve-month horizon, only five complete origins are available, but those five origins still contribute 60 horizon-matched forecast points.

These scenarios prove that:

• history is bound from Excel;
• the rolling backtest reruns;
• model evaluation changes with worksheet state;
• horizon changes alter the historical evaluation window;
• the forward forecast is regenerated;
• empirical bands respond to both model errors and interval coverage.

A reproducible forecasting application should keep fixed fixtures for both baseline and changed-input scenarios rather than validating only a screenshot of one result.

Distinguish model selection from forecast monitoring

Backtesting answers: Which method appears to perform best before deployment?

Monitoring answers: How is the deployed forecast performing as new actuals arrive?

Those are related but different workflows.

Once a forecast is used operationally, preserve the historical forecast vintages so you can compare what was predicted at each horizon with what actually happened. The Forecast Accuracy & Bias Monitor is designed for that post-deployment evaluation problem.

Do not overwrite the prior forecast every month and then expect to reconstruct historical accuracy later.

Know when the simple models are insufficient

The template intentionally excludes several real-world features:

• promotions;
• holidays;
• price effects;
• stockouts and censored demand;
• explanatory variables;
• structural breaks;
• intermittent-demand-specific methods.

Those can justify richer models, but the same backtesting discipline should remain. Add complexity only after defining how it will be evaluated against a reasonable baseline.

The operating model

The forecast author owns candidate methods, backtesting logic, validation, and output design.

The planner updates history and approved forecast controls, then reviews model comparison, bias, uncertainty bands, and the forward plan without maintaining the Python implementation.

That split makes forecasting a strong workbook-application use case: Excel remains the place where planning data and outputs live, while Python handles time-aware repeated evaluation that is cumbersome to maintain in formulas.

Use the Demand Forecast & Backtest Explorer for a reproducible implementation and the Forecast Accuracy & Bias Monitor for the separate monitoring stage after forecasts are issued.