Forecast Errors Can Cancel: Check the Misses Behind a Sales Total

A sales forecast can land exactly on the team total even when every rep's forecast misses. Overestimates and underestimates can cancel. Keep the total result, but inspect the errors underneath it before deciding that the forecasting process is reliable.

Two calculations answer different questions: the signed error tells you which way a forecast missed; the absolute error tells you the size of the miss without letting opposite directions cancel. Mean absolute error, or MAE, averages those sizes across the rows you've chosen to evaluate.

This guide works through four fictional rep forecasts and a reusable spreadsheet review. The amounts are invented, in thousands of one reporting currency. They are not Trellus results, customer data or a forecast-accuracy benchmark.

Make the forecast and the actual amount comparable

Start with a forecast that was saved before the result was known. Preserve its timestamp and the period it covered. A number edited after month-end may help explain a deal, but it cannot stand in for the prediction the team originally made.

Then write down what the amount means. The example below forecasts the value of new contracts recorded as closed won during one completed month, using the fictional team's agreed booking rules. It does not forecast cash received, recognized revenue or the value of all open opportunities. Mixing those measures would make the subtraction misleading.

Use the same currency, period boundaries, team membership and inclusion rules on both sides. If ownership changed during the month, establish how the frozen forecast and final contracts are assigned to the rep rows. Otherwise, a transfer can appear to be a forecasting miss even when the underlying expectation didn't change.

Choose the level of the review as well. Here, one row means one rep's forecast for one month, saved at a common cutoff. A deal-level review or a review of twelve monthly team totals would contain different observations. Their MAEs need their own labels; they aren't interchangeable with this one.

Four misses hiding behind an exact team total

Suppose a fictional team froze all four forecasts at the same cutoff before the month began. After the month ended, operations established the final amounts using the agreed rules. Every actual amount is known; none is a blank or an unresolved record.

Use this sign convention throughout: signed error = actual amount − forecast amount. A negative error means the forecast was higher than the actual. A positive error means the forecast was lower. The absolute error removes that sign.

The team forecast was 400, and the actual total was 400. Its aggregate error really is zero. That is useful if the decision depended only on that total.

The row-level errors tell another part of the story. A's overestimate of 20 cancels B's underestimate of 20. C and D cancel by 10 each. All four rows missed, and their absolute errors add to 60. That 60 is a sum of miss sizes across rep rows, not revenue lost or an amount to subtract from the team's actual bookings.

If the team used these rep forecasts to plan territory support or review individual opportunities, the distribution of the misses matters. Preserve both views: the team total matched, while the underlying forecasts differed from their results.

Download the fictional four-row example CSV or use the blank forecast review worksheet.

Calculate MAE without losing the unit

For these four rows:

Signed errors: −20, +20, −10, +10
Mean signed error:
(−20 + 20 − 10 + 10) / 4 = 0

Absolute errors: 20, 20, 10, 10
Mean absolute error:
(20 + 20 + 10 + 10) / 4 = 15

The MAE is 15 thousand per rep-month forecast. It describes the average absolute difference on these four rows. It is not 15%, a maximum miss, a prediction interval or a guarantee about next month.

The actual-minus-forecast sign and the MAE calculation follow the definitions in Hyndman and Athanasopoulos's Forecasting: Principles and Practice. MAE keeps the units of the amounts being evaluated. The sales example and worksheet here are fictional applications of those definitions.

In a spreadsheet with forecast in column B and actual in C, put =C2-B2 in D2 and =ABS(D2) in E2. Fill those formulas through row 5. Then calculate =AVERAGE(D2:D5) for the mean signed error and =AVERAGE(E2:E5) for MAE.

Check the input cells first. If an actual amount is unresolved, mark the row unresolved and keep it outside the score until it can be established. Don't let a spreadsheet turn a blank into a zero. Report the number of included rows and any exclusions beside the result.

A zero mean doesn't settle whether forecasts are biased

In this example, the mean signed error is zero because the positive and negative errors balance. That single review does not establish that the process is unbiased or that each rep tends to forecast accurately.

To investigate a repeated direction, retain comparable forecasts over multiple completed periods. Look at whether a rep or segment repeatedly forecasts above the actual, repeatedly forecasts below it, or changes direction. Keep the forecast horizon consistent: a month-start prediction and a prediction made on the final afternoon had different information available.

Investigate the dated assumptions with the rep. For a recurring overestimate, inspect the expected close dates and the opportunities included in the forecast. Then check what changed after the snapshot. The error alone won't tell you which explanation applies.

Choose the next review from the errors

Take A's negative 20 as a starting point. Trace the frozen forecast to its included opportunities, then find the records behind the final amount. Was an expected contract delayed beyond the period? Did the amount change? Was the same opportunity assigned to a different owner? Keep corrections to the reporting data separate from changes in the commercial outcome.

The pipeline reconciliation guide can help explain movements in open pipeline, but that is a separate calculation from forecast error. A smaller ending pipeline is not automatically missed bookings, and an opportunity that left the open pipeline may have been won.

If a few large opportunities explain much of the miss, inspect pipeline concentration alongside the forecast. It can help identify which assumptions deserve attention. It doesn't turn a potential deal amount into a probability or a promised result.

When comparing forecasting methods, use the same resolved rows, currency, horizon and saved cutoff for each. A lower MAE on a different set of reps or periods could reflect the changed sample. A lower MAE on the same historical rows is evidence about that comparison; it still needs further evaluation before being treated as a dependable future improvement.

Keep this worksheet with the forecast review

Amount basis and inclusion rules:
Period boundaries and currency:
Team definition:
Forecast timestamp and horizon:
One row represents:
Actual amount source; date checked:
Included rows:
Unresolved/excluded rows; reasons:
Signed error = actual minus forecast
Team forecast total; team actual total:
Aggregate error; mean signed error:
Sum of absolute errors; MAE and unit:
Rows to investigate and why:
Changes in records or assumptions:
Owner, next check and review date:

Keep probability forecasts separate from amount forecasts. If the question is whether recorded win probabilities matched win outcomes by a deadline, use the binary Brier score walkthrough. It answers a different question from the currency differences calculated here.

For a broader choice of methods, start with the sales forecasting techniques guide. For this review, finish with the total result, the size and direction of the underlying misses, and the specific assumptions someone will examine next.