Supply Chain & InventoryFree generatorPDF, Excel, Word export

Excel Forecast Accuracy Formula Generator: WMAPE, Bias, MAE and Tracking Signal

Most demand planners still calculate accuracy in Excel. This generator writes the formulas for you from your own ranges, so WMAPE, bias, RMSE and tracking signal are computed the same way every cycle and are easy to audit.

Fill in

Actual range

Cells holding actual demand.

Forecast range

Cells holding the forecast at your chosen lag.

Absolute error range (helper column)

Signed error range (helper column)

First data row number

Smoothing constant for exponential smoothing

Generated code

Download:

Helper columns (fill down from the first data row)

Absolute error, column E:   =ABS(C2-D2)
Signed error, column F:     =D2-C2

Signed error here is forecast minus actual, so a positive value means over-forecast.

Accuracy formulas

MAE:      =AVERAGE(E2:E13)
WMAPE:    =SUM(E2:E13)/SUM(C2:C13)
Accuracy: =MAX(0,1-SUM(E2:E13)/SUM(C2:C13))
RMSE:     =SQRT(SUMPRODUCT((C2:C13-D2:D13)^2)/COUNT(C2:C13))
MAPE (ignores zero actuals): =AVERAGE(IF(C2:C13>0,ABS(C2:C13-D2:D13)/C2:C13))

MAPE above is an array formula. In older Excel confirm with Ctrl+Shift+Enter.

Bias formulas

Bias (units):  =SUM(F2:F13)
Percent bias:  =SUM(F2:F13)/SUM(C2:C13)
Mean error:    =AVERAGE(F2:F13)

Tracking signal

Running sum of errors in G, first row:  =F2
Running sum of errors in G, next rows:  =G2+F2
Tracking signal in H (cumulative error divided by MAD so far):
=G2/AVERAGE($E2:E2)

Flag when ABS(H) is above 4, a common rule of thumb.

Simple exponential smoothing forecast

Next period forecast = alpha x last actual + (1 - alpha) x last forecast
=(0.3*C2)+((1-0.3)*D2)

Check that actual and forecast ranges have the same length and cover the same periods. Treat blank cells carefully, since AVERAGE ignores them but SUM does not distinguish zero from blank. Test one row by hand before you roll the formulas out.

Accuracy reporting from live ERP data

Want these metrics produced from your ERP without a manual sheet? Ask for an optional expert review.

Optional. The tool and every export work without this. No spam.

How to use the generator

Lay out your sheet with actual demand in one column and the forecast at your chosen lag beside it. Enter the ranges and the helper column ranges, then copy each formula block into the sheet. The helper columns for absolute and signed error make every other formula short and checkable. Fill the helper formulas down for the full range and keep all columns the same length.

The method behind each formula

WMAPE is total absolute error divided by total actual demand, which weights by volume. Bias here is forecast minus actual, so a positive total is over-forecasting. RMSE squares the errors, averages them and takes the root, penalising large misses. Tracking signal divides the running sum of errors by the mean absolute deviation, so persistent one-sided error pushes it beyond about plus or minus 4.

WMAPE for portfolio accuracy
Bias for direction of error
RMSE for safety stock inputs
Tracking signal for alerts

Worked example

Actuals 100, 120 and 80 against forecasts 110, 100 and 90 give absolute errors 10, 20 and 10. WMAPE is 40 divided by 300, so 13.3%. Signed errors are 10, minus 20 and 10, so bias is zero. The tracking signal after three periods is 0 divided by 13.3, which is zero, so no alert, although each month missed. Read accuracy and bias together.

Common mistakes

Using the latest forecast instead of the lagged one flatters accuracy. Averaging SKU-level percentages instead of using the WMAPE ratio gives a different number. Mixing sign conventions between reports confuses sales. Hard-coding ranges means new months are missed, so convert the data to an Excel table or extend ranges each cycle.

Frequently Asked Questions

What is the Excel formula for WMAPE?

Use =SUM(abs_error_range)/SUM(actual_range), where the absolute error column is =ABS(actual-forecast). Format the result as a percentage. Accuracy is often shown as 1 minus WMAPE, floored at zero.

How do I calculate forecast bias in Excel?

Sum forecast minus actual over the period for units, or divide that sum by total actual for percent bias. A positive result means over-forecasting. State the sign convention in the report so readers do not misread it.

How is tracking signal calculated?

Tracking signal is the running sum of forecast errors divided by the mean absolute deviation to date. Values beyond about plus or minus 4 suggest the forecast is biased and the model or inputs should be reviewed.

Why does my MAPE formula return an error?

Plain MAPE divides by actual demand, so a zero actual causes a divide by zero error. Exclude zero actuals as in the array formula here, or use WMAPE, which divides by the total instead.

If these sheets are rebuilt by hand every month, Netray can compute them directly from your ERP and show the query, so the numbers match finance.

Talk to an Expert Explore More Free Tools

Related Tools

Supply Chain & Inventory

More Supply Chain & Inventory tools

See all

Back to All Tools