A step-by-step guide to measuring sales forecast accuracy and bias in Excel: keeping a weekly snapshot table, pulling the forecast each rep made at a fixed horizon, signed error and absolute error against actuals, bias per rep across four quarters with AVERAGEIFS, mean absolute error, the team figure against the per-rep spread, and the horizon curve. Includes the exact formulas and the point at which the spreadsheet stops being enough.
Forecast accuracy is how close the forecast was; bias is which way it missed. Both come from one snapshot table and one actuals table. This guide gives the Excel formulas.
Sheet Snapshots, appended weekly:
| Column | Content |
|---|---|
| A | Snapshot date |
| B | Rep |
| C | Period, such as 2026-Q2 |
| D | Forecast for that period |
Sheet Actuals:
| Column | Content |
|---|---|
| A | Rep |
| B | Period |
| C | Actual closed revenue |
| D | Period end date |
On Actuals, with the horizon in weeks in Calc!B1, the target date is period end less the horizon. The latest snapshot on or before it, E2:
=MAXIFS(Snapshots!A:A,Snapshots!B:B,A2,Snapshots!C:C,B2,Snapshots!A:A,"<="&(D2-7*Calc!$B$1))
F2:
=IF(E2=0,NA(),SUMIFS(Snapshots!D:D,Snapshots!B:B,A2,Snapshots!C:C,B2,Snapshots!A:A,E2))
Signed, G2:
=IFERROR((F2-C2)/C2,"")
Positive is over-forecast; negative is under-forecast. Absolute, H2:
=IF(G2="","",ABS(G2))
On Calc, reps in A5 down. List the four periods to include in D1:G1, or add a column on Actuals flagging the last four quarters as TRUE in I. Bias, B5:
=AVERAGEIFS(Actuals!G:G,Actuals!A:A,A5,Actuals!I:I,TRUE)
Mean absolute error, C5:
=AVERAGEIFS(Actuals!H:H,Actuals!A:A,A5,Actuals!I:I,TRUE)
Accuracy is =1-C5. Read the pair:
| Bias | Absolute error | Reading |
|---|---|---|
| Near zero | Small | Accurate |
| Near zero | Large | Imprecise; the deals are uncertain |
| Strongly negative | About the same size | Sandbagging |
| Strongly positive | About the same size | Optimism; stage discipline |
When bias and absolute error are nearly equal in size, the rep misses the same way every time, and that is correctable.
Team signed error for a period, with the period in J1:
=(SUMIFS(Actuals!F:F,Actuals!B:B,J1)-SUMIFS(Actuals!C:C,Actuals!B:B,J1))/SUMIFS(Actuals!C:C,Actuals!B:B,J1)
Per-rep spread for the same period:
=AVERAGEIFS(Actuals!H:H,Actuals!B:B,J1)
A team at plus 2 percent with a spread of 14 percent is two reps cancelling each other. The second number is the honest one.
Change Calc!B1 to 12, 8, 4 and 1 and record the team's average absolute error at each, or duplicate columns E to H four times with fixed horizons. Plot the four points. This quarter's curve against last year's curve is the benchmark that matters.
Every actual should find a snapshot:
=COUNTIF(Actuals!E:E,0)
Zero, for the periods since snapshots began. And snapshots should not have gaps:
=MAX(Snapshots!A:A)-MIN(Snapshots!A:A)
divided by seven, plus one, should equal the number of distinct snapshot dates.
No snapshots. The only forecast available is the final one, and accuracy is always excellent.
Actual of zero. Division fails for a rep with no closed revenue. IFERROR hides it; list those rows separately, because a forecast against an actual of zero is the largest miss there is.
Forecast categories mixed. Commit one week, best case the next. Snapshot the same category every time, or snapshot both in separate columns.
Territory changes. A rep who inherited accounts mid-quarter is measured against a forecast they did not make. Note reassignments beside the row.
The discipline of the weekly append is the hard part, and a spreadsheet depends on one person remembering. Once the snapshot is automated, by rep, by team and by segment with deal-level movement between weeks, it is no longer a spreadsheet job; see the four signs a spreadsheet is no longer enough. For what the figures should be, see what is a good forecast accuracy; for the arithmetic by hand, forecast bias on five reps and four quarters. Covirage keeps the snapshots from each week's export and produces the bias table every quarter.
Start today. Most CRMs overwrite the forecast, so history cannot be recovered. Export the forecast by rep every Monday and append it to the snapshot sheet with the date. In one quarter you have a horizon curve; in four you have a bias table. Nothing else in this guide works without it.
Mean absolute percentage error is the average of absolute errors, which is the accuracy half of this guide. Bias is the average of signed errors, which MAPE discards by design. A rep at minus 12 percent every quarter and a rep alternating plus and minus 12 have the same MAPE and need different conversations.
Several, but compare like with like. Twelve, eight, four and one weeks before period end is typical for quarterly forecasts. Bias per rep is most useful at the horizon where decisions get made, usually eight weeks out, when there is still time to act on the number.