Cellwise Start free

Excel guide · 4 min read

How to calculate forecast accuracy in Excel

Quick answerPut forecast and actual side by side, then divide the total absolute error by total actual: =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.

RowABCDEFG
1SKUMonthForecastActualErrorAbs errorAPE
2BLT-10Sep50046040400.086956521739
3BLT-10Oct520548-28280.051094890511
4BLT-10Nov54051228280.0546875
5CLP-22Sep806119190.31147540984
6CLP-22Oct907020200.28571428571
7CLP-22Nov8508585
8HNG-05Sep12001310-1101100.083969465649
9HNG-05Oct1250118565650.054852320675
10HNG-05Nov13001342-42420.03129657228
11SPR-40Nov301218181.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.

=SUM(E2:E11)

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.

=AVERAGE(ABS(C2:C11-D2:D11)/D2:D11)

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.

=AVERAGE(G2:G11)

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.

=SUM(F2:F11)/SUM(D2:D11)

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.

=SUM(E2:E11)/SUM(D2:D11)

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

Related guides

Updated