Backtesting financial models for business forecasts

Backtesting financial models compares an archived business forecast with the actual results that followed it. The useful question is whether the forecast supported decisions using information available at the time, with revenue and costs measured on consistent terms.

Start with the decision and forecast horizon

Choose the forecast being tested: monthly revenue for hiring decisions, delivery costs for capacity planning, or collections for cash planning. Record its issue date, target period, business scope and currency. Keep forecasts made a month ahead separate from forecasts made a quarter ahead.

The forecasting text Forecasting: Principles and Practice explains that genuine forecast accuracy must be assessed on observations excluded from the information used to fit a forecasting approach. Comparing an updated workbook with months already embedded in it does not establish that accuracy.

This page concerns FP&A validation. For the initial choice of workbook, see different financial models and their uses. A delivery budget has a different review unit, covered in financial models in project management.

Assemble the exports without changing history

Bring the saved forecast outputs and the corresponding closed-period actuals. Use one row per issue date, target month, account and business unit. Include the forecast amount, actual amount, currency and the accounting basis of each measure.

Match the rows using the full key. A revenue line for one region cannot be matched to company revenue merely because the month agrees. Check that the join retains every expected forecast line, and flag missing actuals for investigation instead of filling them with zero.

Retain a copy of the original forecast. A later sales order, corrected staffing assumption or subsequent price agreement belongs in a later forecast vintage. The rolling forecasting origin method repeatedly advances the forecast issue date while keeping later observations outside the information available at that point. Archived monthly submissions let finance apply that principle without rebuilding historical assumptions from memory.

Calculate error and bias from the same rows

Consider a US equipment distributor reviewing monthly revenue forecasts. Each forecast was issued at the previous month-end, and all amounts are USD. These illustrative rows are complete, with no missing revenue lines.

Put those columns in A through F, with the months in rows 2 through 5. In Google Sheets, use the formulas below and fill the row formulas down. Labels identify destination cells or summary measures; enter only the expression beginning with =:

E2 = C2-D2
F2 = ABS(E2)
Mean absolute error = SUM(F2:F5)/COUNT(F2:F5)
Weighted absolute percentage error = SUM(F2:F5)/SUM(D2:D5)
Directional bias percentage = SUM(E2:E5)/SUM(D2:D5)

Google documents ABS as returning the absolute value, which prevents positive and negative misses from canceling in the error measure. Covirage is not affiliated with Google.

Mean absolute error is $30,000 / 4 = $7,500 per month. Weighted absolute percentage error, or WAPE, is $30,000 / $440,000 = 6.82%. Directional bias is $20,000 / $440,000 = 4.55%, indicating net overforecasting under the stated sign convention. Percentages are rounded to two decimal places; dollar figures are exact.

These measures answer different questions. Absolute error shows the size of the misses. Bias shows their net direction. A small net miss can coexist with large errors in individual months, so report both.

Compare against a simple alternative and reconcile costs

A more elaborate forecasting process should earn its review effort. Compare it against a simple benchmark that was also available at each issue date, such as a documented prior-period rule.

Benchmark WAPE is $35,000 / $440,000 = 7.95%. The submitted revenue forecasts performed better on this measure over these periods. That finding does not establish superiority through a full seasonal cycle or for a different forecast horizon.

Review costs separately before interpreting the earnings effect:

Forecast revenue less these costs is $185,000; actual revenue less costs is $170,000. The $15,000 overforecast reconciles to the $20,000 revenue overforecast minus the $5,000 cost overforecast. Tie each actual total to the corresponding ledger accounts before investigating forecasting assumptions.

Where backtesting goes wrong

• Replacing archived submissions. Later revisions make the historical forecast look better informed than it was.
• Mixing horizons. Comparing a near-close update with a quarterly submission rewards the availability of later information.
• Netting the misses. Directional errors cancel; absolute errors measure how far each forecast missed.
• Ignoring small or negative actuals. Percentage measures become unstable near zero. Use dollar errors and separate business categories when signed actuals make a combined denominator misleading.
• Choosing the winner after seeing results. Agree on the decision, benchmark and review periods before changing assumptions, then validate the revised approach on subsequent periods.

FP&A owns the comparison; operating leaders own explanations for the underlying drivers. The next review should record which assumption changed and why. Keep the broader decision process in FP&A analysis connected to the evidence from these rows.

Compare your archived forecasts in Covirage

Bring a file containing forecast vintages, their issue dates and matched revenue and cost actuals. Covirage maps the columns, its tools compute and check the comparisons, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account in about a minute, then set up your business and bring a sample of your own data.

See forecast analysis for the supporting business use.