Excel guide · 4 min read
How to calculate forecast accuracy in Excel
=SUM(F2:F11)/SUM(D2:D11), where F holds =ABS(C2-D2). That's weighted MAPE (WMAPE). Forecast accuracy is =MAX(0,1-WMAPE), and bias, =SUM(E2:E11)/SUM(D2:D11) with E = forecast - actual, tells you whether you forecast too high or too low.Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|---|
| 1 | SKU | Month | Forecast | Actual | Error | Abs error | APE |
| 2 | BLT-10 | Sep | 500 | 460 | 40 | 40 | 0.086956521739 |
| 3 | BLT-10 | Oct | 520 | 548 | -28 | 28 | 0.051094890511 |
| 4 | BLT-10 | Nov | 540 | 512 | 28 | 28 | 0.0546875 |
| 5 | CLP-22 | Sep | 80 | 61 | 19 | 19 | 0.31147540984 |
| 6 | CLP-22 | Oct | 90 | 70 | 20 | 20 | 0.28571428571 |
| 7 | CLP-22 | Nov | 85 | 0 | 85 | 85 | |
| 8 | HNG-05 | Sep | 1200 | 1310 | -110 | 110 | 0.083969465649 |
| 9 | HNG-05 | Oct | 1250 | 1185 | 65 | 65 | 0.054852320675 |
| 10 | HNG-05 | Nov | 1300 | 1342 | -42 | 42 | 0.03129657228 |
| 11 | SPR-40 | Nov | 30 | 12 | 18 | 18 | 1.5 |
Download this sheet as an Excel practice file (.xlsx)
Set up error and absolute error columns
Line up one row per item and period: SKU, month, forecast, actual. Then add three helper columns. Error in E2 is =C2-D2, forecast minus actual, so a positive number means you forecast too high. Absolute error in F2 is =ABS(E2), the size of the miss whatever its direction. APE (absolute percentage error) in G2 is =IF(D2=0,"",F2/D2).
Why two error columns? Errors in opposite directions cancel out. BLT-10 was 40 over in September and 28 under in October; added together that looks like a 12-unit miss. The absolute errors add to 68, which is what really happened. Use E for bias and F for accuracy.
Pick one sign convention and write it on the sheet. Some teams use actual minus forecast instead; the maths is the same, but a positive bias then means under-forecasting.
Result: 95
95: errors in both directions mostly cancel. The absolute errors add up to 455, so the net number hides most of the miss.
MAPE, and why it breaks on zero sales
MAPE (mean absolute percentage error) averages the percentage miss of each row. The quick version, =AVERAGE(ABS(C2:C11-D2:D11)/D2:D11), fails as soon as one actual is 0: CLP-22 sold nothing in November, so that row divides by zero and the whole formula returns #DIV/0!.
The IF in column G leaves those rows blank, and AVERAGE skips text, so =AVERAGE(G2:G11) works. But note what it does: the month where you forecast 85 and sold 0, the worst miss in the table, simply disappears from MAPE.
Result: #DIV/0!
#DIV/0!: one actual of 0 breaks the whole average.
Why MAPE can mislead: small items get the same vote
Even without the zero, plain MAPE gives every row the same weight. SPR-40 is a 12-unit item: forecasting 30 is a 150% error. That one row pulls MAPE from 12.0% (=AVERAGE(G2:G10)) up to 27.3%, while HNG-05, which sells over 1,000 units a month and is 70% of the volume, barely moves it.
If you report MAPE, say which rows were left out, and look at the median too: =MEDIAN(G2:G11) gives 8.4%, much closer to the typical miss.
Result: 0.27333855182 shows as 27.3% with Percent format
0.273: plain MAPE is 27.3%, mostly because of one tiny SKU.
Weighted MAPE and forecast accuracy
WMAPE adds up the absolute errors and divides by total actual: =SUM(F2:F11)/SUM(D2:D11). It can't divide by zero unless nothing sold at all, the November zero counts, and big items count in proportion to their volume. It's what most supply chain teams mean by forecast error.
Forecast accuracy is then 1 minus that error. Floor it at 0 with MAX, because a WMAPE above 100% would otherwise give a negative accuracy, which means nothing: =MAX(0,1-SUM(F2:F11)/SUM(D2:D11)) gives 91.7%.
Per SKU, swap SUM for SUMIFS: =SUMIFS(F2:F11,A2:A11,"HNG-05")/SUMIFS(D2:D11,A2:A11,"HNG-05") gives 5.7% for HNG-05, while CLP-22 comes out at 94.7% (accuracy 5.3%) because of that 85-unit miss against 131 units sold.
Result: 0.082727272727 shows as 8.3% with Percent format
0.0827: WMAPE is 8.3% (455 units of error on 5,500 sold), so forecast accuracy is 91.7%.
Forecast bias: too high or too low?
Accuracy tells you how big the misses are; bias tells you which way they lean. Bias is the sum of signed errors divided by total actual: =SUM(E2:E11)/SUM(D2:D11). A positive result means you forecast too high on balance (extra stock); a negative one means too low (stockouts).
Here total bias is a small +1.7%, but the SKUs tell different stories. Every CLP-22 month was over-forecast, so its bias equals its error, 94.7%. HNG-05 runs slightly low: =SUMIFS(E2:E11,A2:A11,"HNG-05")/SUMIFS(D2:D11,A2:A11,"HNG-05") gives -2.3%. A forecast that's always off in the same direction is the easiest one to fix.
Result: 0.017272727273 shows as 1.7% with Percent format
0.0173: the forecast runs 1.7% high overall.
Common mistakes
Averaging signed errors and calling it accuracy
Use absolute errors (ABS) for accuracy; keep signed errors only for bias.
MAPE returning #DIV/0!
Skip zero-actual rows with IF and say so, or switch to WMAPE, which divides by total actual.
A negative forecast accuracy
Wrap it in MAX(0, 1 - WMAPE); errors larger than total actual mean 0% accuracy.
Comparing accuracy at different levels
SKU-by-month accuracy is always lower than total-company accuracy, because errors cancel when you add items up. Compare like with like.
FAQ
What is the formula for forecast accuracy in Excel?
A common one is 1 - SUM(ABS(forecast - actual)) / SUM(actual), floored at 0: =MAX(0,1-SUM(F2:F11)/SUM(D2:D11)) with absolute errors in F. That's 1 minus weighted MAPE.
What is the difference between MAPE and WMAPE?
MAPE averages each row's percentage error, so every row counts equally and zero actuals break it. WMAPE divides total absolute error by total actual, so rows count in proportion to their volume and zeros are handled.
How do I calculate forecast bias in Excel?
Sum the signed errors (forecast minus actual) and divide by total actual: =SUM(E2:E11)/SUM(D2:D11). Positive means over-forecasting, negative means under-forecasting.
Should I divide by actual or by forecast?
Dividing by actual is the usual definition, and the one the formulas here use. Some teams divide by forecast; that's fine if everyone uses the same definition, but don't compare numbers calculated both ways.
Functions used
ABSAbsolute valueSUMAdds numbersSUMIFSAdds with several conditionsAVERAGEMean of numbersMEDIANMiddle valueMAXLargest numberIFPicks a result from a test
Related guides
- How to do an exponential smoothing forecast in Excel
- How to build a rolling forecast in Excel
- How to calculate percent difference in Excel
- How to make negative numbers positive in Excel
- How to calculate a tracking signal in Excel
- How to calculate year-over-year growth in Excel
- How to calculate return on assets (ROA) in Excel
Updated