# Statistical Forecast Model Comparison Worksheet

> Source: https://netray.co/tools/statistical-forecast-model-comparison-worksheet
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Line up actuals against two forecast methods, see absolute percentage error per period and average MAPE for each, then export to Excel.

## Category

supply-chain

## Audience

- Demand planner
- Supply chain analyst
- S&OP lead

## Tool

**Kind:** worksheet

**Mode:** editable

**Columns:**

#### Period

**Key:** period

**Type:** text

#### Actual

**Key:** actual

**Type:** number

#### Model A forecast

**Key:** fc_a

**Type:** number

#### Model B forecast

**Key:** fc_b

**Type:** number

#### Model A APE %

**Key:** ape_a

**Type:** number

**Expr:** if(gt(actual,0),abs(actual-fc_a)/actual*100,0)

**Format:** percent

#### Model B APE %

**Key:** ape_b

**Type:** number

**Expr:** if(gt(actual,0),abs(actual-fc_b)/actual*100,0)

**Format:** percent

#### Model A error

**Key:** err_a

**Type:** number

**Expr:** fc_a-actual

**Format:** number

#### Model B error

**Key:** err_b

**Type:** number

**Expr:** fc_b-actual

**Format:** number

**Rows:**

**Period:** 2026-01

**Actual:** 410

**Fc a:** 395

**Fc b:** 430

**Period:** 2026-02

**Actual:** 385

**Fc a:** 400

**Fc b:** 405

**Period:** 2026-03

**Actual:** 470

**Fc a:** 420

**Fc b:** 455

**Period:** 2026-04

**Actual:** 455

**Fc a:** 440

**Fc b:** 450

**Period:** 2026-05

**Actual:** 520

**Fc a:** 470

**Fc b:** 505

**Period:** 2026-06

**Actual:** 490

**Fc a:** 495

**Fc b:** 500

**Totals:**

#### Model A MAPE

**Key:** ape_a

**Agg:** avg

**Format:** percent

#### Model B MAPE

**Key:** ape_b

**Agg:** avg

**Format:** percent

#### Model A net error (bias)

**Key:** err_a

**Agg:** sum

**Format:** number

#### Model B net error (bias)

**Key:** err_b

**Agg:** sum

**Format:** number

**Summary:**

#### MAPE gap (A minus B)

**Key:** gap

**Expr:** avg_ape_a-avg_ape_b

**Format:** decimal

**Description:** Negative means Model A is more accurate.

**Notes:** Compare models on data the models did not see while fitting (a holdout). Judge on the bias column too: a model with slightly worse MAPE but near zero bias is often better for supply planning.

## Content

**Intro:** Choosing between a moving average, exponential smoothing or a seasonal method should rest on measured error, not preference. This worksheet lays two models against the same actuals and gives the error for each period and on average.

**Sections:**

#### How to use the worksheet

**Body:** Enter actuals for a holdout window, ideally 6 to 12 periods that were not used to fit the models. Paste in the forecast each method produced for the same periods. The worksheet calculates absolute percentage error and signed error per row, and the average MAPE and net error for each model. Export to Excel to keep the backtest evidence with the item or family.

#### Reading MAPE and bias together

**Body:** MAPE is the average of absolute percentage errors. It tells you how far off the forecast is typically, but not in which direction. The net error column shows bias: a consistently positive sum means over forecasting. A model with a slightly higher MAPE and no bias can be a better planning input than one with lower MAPE that always runs high.

**Bullets:**

- MAPE = average of |actual - forecast| / actual
- Positive net error = over forecast
- Prefer low MAPE and near zero bias
- Use WAPE when volumes vary widely

#### Avoiding a false winner

**Body:** With only a few periods, one outlier can pick the winner. Test across several items of the same type before changing a model for a whole family. Percentage error is unstable when actuals are close to zero, so for intermittent items compare absolute error instead or use a volume weighted measure.

#### Common mistakes

**Body:** The most common error is scoring a model on the same data it was fitted to, which always flatters it. Another is changing the model after every bad month. Set a review rule, for example re-test quarterly, and switch only when the gain holds over a full season.

**Cta:** Netray can run these backtests across thousands of items from your ERP history and recommend a method per item, with the evidence for a planner to approve.

**Faq:**

#### How do I compare two forecasting methods?

**Answer:** Run both on the same historical window, hold back the most recent periods as a test set, then compare error measures such as MAPE and bias on that test set. Pick the method that wins consistently, not once.

#### What is a good MAPE?

**Answer:** It depends on the item. Stable high volume items may reach single digit MAPE, while lumpy low volume items can exceed 50%. Compare against a naive forecast and your own history rather than a universal target.

#### Why does MAPE blow up on small numbers?

**Answer:** Dividing by a very small actual makes any miss look enormous, and zero actuals cannot be scored. For intermittent demand use absolute error or WAPE instead.

#### Should I always choose the lowest MAPE model?

**Answer:** Not always. Check bias, stability and whether the model suits the demand pattern. A slightly less accurate but unbiased and explainable model may serve supply planning better.

## Search terms

forecast model comparison excel, compare forecasting methods worksheet, forecast method selection template, MAPE comparison two models, best fit forecast model worksheet, demand forecast backtest template, holdout forecast test excel

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json