Evaluating time series forecasting models on business data

Evaluating time series forecasting models means testing forecasts against actual results from periods the method had not yet seen. Compare the same business rows, forecast horizons and dates, then examine both the size and direction of the errors. A forecast that matches historical data closely still needs to prove that it helps with future decisions.

Start with the decision and its horizon

Define what the forecast will support before opening the workbook. Monthly revenue planning, weekly staffing and quarterly purchasing need different horizons. A forecast made immediately before a month starts has more information than one made at the start of the quarter, so scoring them together gives the later forecast an unfair advantage.

Keep these columns in the forecast export: method, business segment, forecast creation date, target period, horizon and forecast value. Keep actuals in a separate export with the same segment, target period and measurement definition. Revenue forecasts need recognized revenue actuals; order forecasts need orders, not a convenient revenue substitute.

For the underlying patterns and method choices, see time series in forecasting. This evaluation starts after each candidate has produced its forecast values.

Hold out later periods and freeze each version

Use early periods to prepare the method and later periods to evaluate it. Save the forecast before the actual is available, including the assumptions and adjustments that were approved at that date. Do not overwrite the saved forecast with a revised version after seeing the result.

Forecasting: Principles and Practice explains that accuracy must be assessed on new observations, rather than inferred from the fit to historical observations. For a business workbook, that means preserving the original forecast and joining it to the actual only when the period closes.

One holdout can favor a method by chance. Repeat the comparison at successive forecast dates, always using only earlier information. This is evaluation with a rolling forecasting origin. Score each horizon separately: a method may be useful for the next month and weak farther out.

Work the same rows through every method

A US distributor compares monthly revenue forecasts. Every forecast below was locked before its target month, at a one-month horizon. Methods A and B are candidate workbook methods; the baseline uses the preceding month's actual revenue. December revenue was $95,000, which supplies January's baseline.

Put the following table in A1:E6: headers in row 1, monthly inputs in rows 2 through 5 and totals in row 6. All amounts are USD, with no rounding in the input rows.

Use one error sign throughout:

Forecast error = Actual − Forecast. Positive error means underforecasting; negative error means overforecasting.

Set F, G and H to the errors for A, B and the baseline. In row 2, then fill down:

F2 = B2-C2
G2 = B2-D2
H2 = B2-E2

The first reconciliation is total actual less total forecast equals total error. For A, $420,000 minus $416,000 equals $4,000. Run the same check for every method before ranking it.

Calculate error size and bias separately

MAE is average absolute error. RMSE is the square root of average squared error, so larger misses carry more weight. WAPE divides total absolute error by total actual revenue. Bias here means average signed error; opposing errors can cancel, so bias is not a substitute for accuracy.

In I2 enter =ABS(F2) and in J2 enter =F2^2, then fill down. The summary formulas for A are:

MAE:  =AVERAGE(I2:I5)
RMSE: =SQRT(AVERAGE(J2:J5))
WAPE: =SUM(I2:I5)/SUM(B2:B5)
Bias: =AVERAGE(F2:F5)

Repeat the helper columns using G and H for the other forecasts. Dollars below are rounded to the nearest dollar; percentages use two decimals. Calculations use unrounded values.

Check coverage as well as arithmetic: Microsoft documents that AVERAGE ignores empty cells but includes zero values. Resolve missing forecast or actual rows before calculating the measures, so each method is evaluated on the same observations.

B has smaller errors under every size measure, but it overforecasts every month. That persistent direction matters if excess purchasing is expensive. The Excel guide to forecast accuracy and bias covers the measures in more detail; keep this selection exercise focused on comparable methods.

Make the selection at the level of the decision

This small holdout supports further testing of B, rather than a promise of permanent superiority. Extend the evaluation across more forecast dates, then split the results by the products, locations or customer groups that drive the decision. Keep the same rows for every method; exclude an unavailable row from all candidates and disclose it.

Recompute WAPE from summed absolute errors and summed actuals within each segment. Do not average segment percentages. Record whether the selected method also improves the largest misses and whether its directional bias remains acceptable. For staffing decisions, connect the result to the demand assumptions used in capacity planning.

Where evaluation goes wrong

• Information from the future. Revised actuals, later price lists or updated assumptions can enter an old forecast unnoticed. Preserve the information date.
• Mixed horizons. Comparing a short-horizon forecast with a longer-horizon forecast confuses information advantage with method quality.
• Missing rows treated as zero. A missing actual is an incomplete comparison, not evidence of zero demand. Reconcile record coverage first.
• Percentage errors near zero. Tiny actual values can dominate percentage-based measures. Inspect dollar or unit errors alongside ratios and disclose zero denominators.
• An aggregate hiding an exception. Gains in a large stable unit can conceal failures in a smaller critical operation. Review both the overall total and the decisions made by segment.

Covirage is not affiliated with Microsoft.

Compare your forecast results in Covirage

Bring a workbook or Google Sheet containing saved forecasts, their horizons and the matching actuals. Covirage maps the columns; its tools compute and check the comparison figures, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own forecast export.

See forecast analysis for the supporting business workflow.