Measuring Forecast Accuracy

Building a forecast is only half the job. The other half is knowing how wrong it is. Every forecasting method makes errors — the question is how large those errors are, whether they are systematic, and which method produces the smallest errors on your data. This lesson gives you the tools to answer all three questions objectively.

Why Measuring Accuracy Matters

Without accuracy measurement, forecast selection is guesswork. You might choose the moving average because it looks smoother on a chart, or the ETS forecast because it sounds more sophisticated — neither of which is a valid reason. Accuracy metrics force the question: which method was closest to reality on historical data, and therefore which one should you trust most going forward.

The standard approach is backtesting — hiding the last N months of known data from the model, generating forecasts for those months as if you didn't know the answers, then comparing the forecasts to the actual values you held back.

Setting Up a Backtest

Use the last 12 months of your history (January 2024 – December 2024) as the test period. Train each model on months 1–24 (2022–2023 only), generate forecasts for months 25–36, then compare against the actual 2024 values you already have.

In your Forecast_Input sheet, add a helper column called Is_Training:

=IF([@Month_Num]<=24,"Train","Test")

Your backtest forecast formulas reference only the training rows:

=FORECAST.LINEAR([@Month_Num],
    IF(Forecast_Input_Table[Is_Training]="Train", Forecast_Input_Table[Total_Revenue], ""),
    IF(Forecast_Input_Table[Is_Training]="Train", Forecast_Input_Table[Month_Num], ""))

Enter this as an array formula (Ctrl+Shift+Enter) for the 12 test-period rows.

The Four Accuracy Metrics

MAE — Mean Absolute Error

The average size of your forecast errors, in the same units as your data (dollars). Simple and intuitive — if MAE is $45,000, your forecast is off by $45,000 per month on average.

=AVERAGE(ABS(Actual_Range - Forecast_Range))

Enter as array formula:
=AVERAGE(ABS(Forecast_Input_Table[Total_Revenue] - Backtest_Forecast_Range))

MAPE — Mean Absolute Percentage Error

The average error expressed as a percentage of actual values. This is the most widely used metric in business reporting because it is scale-independent — you can compare it across products, regions, and time periods regardless of revenue magnitude.

=AVERAGE(ABS((Actual - Forecast) / Actual)) × 100

Array formula:
=AVERAGE(ABS((C25:C36 - F25:F36) / C25:C36)) * 100

Interpretation benchmarks for monthly revenue forecasting:

MAPE	Forecast Quality
Below 5%	Excellent
5% – 10%	Good — acceptable for most business planning
10% – 20%	Fair — usable but investigate the largest errors
Above 20%	Poor — reconsider the method or the data

RMSE — Root Mean Square Error

Similar to MAE but penalizes large errors more heavily because it squares each error before averaging. Use RMSE when a single large miss is more damaging than several small ones — common in inventory and cash flow forecasting.

=SQRT(AVERAGE((Actual_Range - Forecast_Range)^2))

Array formula:
=SQRT(AVERAGE((C25:C36 - F25:F36)^2))

Bias — Mean Error

Unlike the previous three metrics, bias keeps the sign of each error. A positive bias means your model consistently over-forecasts. A negative bias means it consistently under-forecasts. A model with low MAE but high bias is dangerous — it is reliably wrong in the same direction.

=AVERAGE(Forecast_Range - Actual_Range)

Array formula:
=AVERAGE(F25:F36 - C25:C36)

Building the Accuracy Comparison Table

Create a dedicated section in your KPI_Calculations sheet comparing all four methods across all four metrics:

Metric	Baseline	MA3	Linear	ETS
MAE ($)
MAPE (%)
RMSE ($)
Bias ($)

Use conditional formatting on each row — green for the best (lowest absolute value) result per metric, red for the worst. This makes the winning method immediately obvious.

The method with the lowest MAPE on the test period is the one you carry forward into Sections 3 and 4 as your primary forecasting baseline.

Interpreting Results Beyond the Numbers

The accuracy table tells you which method wins, but the error chart tells you why. Plot the forecast errors (Actual minus Forecast) for each month of the test period as a bar chart:

• Errors randomly scattered around zero — good. The model has no systematic bias.
• Errors consistently positive or negative — the model has directional bias. Adjust the forecast up or down by the average bias amount.
• Errors that grow larger over time — the model is diverging. It may be missing an accelerating trend or a structural shift in the data.
• Errors that spike at specific months — seasonal effects the model failed to capture. Check whether those months correspond to known business events.

Task: Run the Backtest

Goal: Generate held-out forecasts for 2024 using only 2022–2023 training data, then measure accuracy against known actuals.

1. In your Forecast_Input_Table, add a helper column Is_Training with =IF([@Month_Num]<=24,"Train","Test").
2. In a new area, create a backtest table with columns: Month_Num (25–36), Period_Label, Actual_Revenue (linked from Total_Revenue), and one forecast column per method.
3. For the Linear backtest, enter the FORECAST.LINEAR array formula referencing only training rows (Month_Num 1–24) and projecting months 25–36.
4. For the MA3 backtest, calculate the 3-month moving average using only the last three training months (months 22, 23, 24) as the starting window, then self-feed forward.
5. For the ETS backtest, re-run the Forecast Sheet using only rows 1–24 as input and set the forecast end to cover 12 months — extract those values into your backtest table.

Task 2: Build the Accuracy Comparison Table

Goal: Calculate all four accuracy metrics for every method and identify the best performer on your dataset.

1. In your KPI_Calculations sheet, create the accuracy comparison table shown above with all four methods as columns and all four metrics as rows.
2. For each method, calculate MAE, MAPE, RMSE, and Bias using array formulas referencing the backtest actual and forecast columns from Task 2.5A.
3. Apply conditional formatting to each metric row — green fill for the lowest value, red fill for the highest.
4. Below the table, add a single cell labeled Recommended_Method and enter the name of the method with the lowest MAPE as a text value.
5. Create a bar chart of monthly forecast errors (Actual minus Forecast) for the winning method across the 12 test months. Format positive errors in blue and negative errors in red.
6. Note in a comment cell whether the errors show any systematic pattern — bias, growth divergence, or seasonal spikes — and what that implies for how you should adjust the forecast before using it in Section 3.