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

> Source: https://netray.co/tools/excel-forecast-formula-generator-wmape-bias-tracking-signal
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Enter your actual and forecast ranges and get paste-ready Excel formulas for WMAPE, bias, MAE, RMSE and tracking signal.

## Category

supply-chain

## Audience

- Demand planner
- Supply chain analyst
- Finance business partner

## Tool

**Kind:** template

**Output:** code

**Language:** text

**Fields:**

#### Actual range

**Key:** actual

**Type:** text

**Default:** C2:C13

**Help:** Cells holding actual demand.

#### Forecast range

**Key:** forecast

**Type:** text

**Default:** D2:D13

**Help:** Cells holding the forecast at your chosen lag.

#### Absolute error range (helper column)

**Key:** abs_err

**Type:** text

**Default:** E2:E13

#### Signed error range (helper column)

**Key:** err

**Type:** text

**Default:** F2:F13

#### First data row number

**Key:** first_row

**Type:** number

**Default:** 2

#### Smoothing constant for exponential smoothing

**Key:** alpha

**Type:** text

**Default:** 0.3

**Blocks:**

#### Helper columns (fill down from the first data row)

**Body:** Absolute error, column E:   =ABS(C{{first_row}}-D{{first_row}})
Signed error, column F:     =D{{first_row}}-C{{first_row}}

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

#### Accuracy formulas

**Body:** MAE:      =AVERAGE({{abs_err}})
WMAPE:    =SUM({{abs_err}})/SUM({{actual}})
Accuracy: =MAX(0,1-SUM({{abs_err}})/SUM({{actual}}))
RMSE:     =SQRT(SUMPRODUCT(({{actual}}-{{forecast}})^2)/COUNT({{actual}}))
MAPE (ignores zero actuals): =AVERAGE(IF({{actual}}>0,ABS({{actual}}-{{forecast}})/{{actual}}))

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

#### Bias formulas

**Body:** Bias (units):  =SUM({{err}})
Percent bias:  =SUM({{err}})/SUM({{actual}})
Mean error:    =AVERAGE({{err}})

#### Tracking signal

**Body:** Running sum of errors in G, first row:  =F{{first_row}}
Running sum of errors in G, next rows:  =G{{first_row}}+F{{first_row}}
Tracking signal in H (cumulative error divided by MAD so far):
=G{{first_row}}/AVERAGE($E{{first_row}}:E{{first_row}})

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

#### Simple exponential smoothing forecast

**Body:** Next period forecast = alpha x last actual + (1 - alpha) x last forecast
=({{alpha}}*C{{first_row}})+((1-{{alpha}})*D{{first_row}})

**Notes:** 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.

## Content

**Intro:** 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.

**Sections:**

#### How to use the generator

**Body:** 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

**Body:** 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.

**Bullets:**

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

#### Worked example

**Body:** 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

**Body:** 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.

**Cta:** 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.

**Faq:**

#### What is the Excel formula for WMAPE?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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.

## Search terms

excel wmape formula, forecast bias excel formula, tracking signal excel, forecast accuracy excel formulas, sumproduct mape excel, forecast error excel template formulas, mean absolute error excel, demand planning excel formulas

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json