# Forecast Accuracy Worksheet: MAPE, WMAPE and Bias by Item

> Source: https://netray.co/tools/forecast-accuracy-mape-wmape-worksheet
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Fill in actual and forecast per SKU and get item-level percentage error, plus portfolio MAPE, WMAPE, bias and forecast accuracy. Exports to Excel.

## Category

supply-chain

## Industry

Discrete Manufacturing

## Audience

- Demand planner
- Supply chain manager
- S&OP lead

## Tool

**Kind:** worksheet

**Mode:** editable

**Columns:**

#### Item / SKU

**Key:** item

**Type:** text

#### Actual demand

**Key:** actual

**Type:** number

#### Forecast

**Key:** forecast

**Type:** number

#### Absolute error

**Key:** abserr

**Type:** number

**Expr:** abs(actual - forecast)

**Format:** number

#### APE %

**Key:** ape

**Type:** number

**Expr:** abs(actual - forecast) / actual * 100

**Format:** percent

**Help:** Absolute percentage error. Shows 0 when actual is 0, so review zero-demand items separately.

**Rows:**

**Item:** BRK-1001 Bracket

**Actual:** 1240

**Forecast:** 1100

**Item:** HSG-2040 Housing

**Actual:** 860

**Forecast:** 940

**Item:** SHF-3310 Shaft

**Actual:** 410

**Forecast:** 520

**Item:** GSK-4500 Gasket

**Actual:** 5200

**Forecast:** 4900

**Item:** PCB-7721 Control board

**Actual:** 180

**Forecast:** 150

**Item:** CBL-9010 Harness

**Actual:** 640

**Forecast:** 690

**Totals:**

#### Total actual

**Key:** actual

**Agg:** sum

**Format:** number

#### Total forecast

**Key:** forecast

**Agg:** sum

**Format:** number

#### Total absolute error

**Key:** abserr

**Agg:** sum

**Format:** number

#### MAPE (simple average of APE)

**Key:** ape

**Agg:** avg

**Format:** percent

**Summary:**

#### MAPE

**Key:** mape

**Expr:** avg_ape

**Format:** percent

**Description:** Unweighted average of item percentage errors.

#### WMAPE

**Key:** wmape

**Expr:** sum_abserr / sum_actual * 100

**Format:** percent

**Primary:** true

**Description:** Total absolute error divided by total actual. Volume weighted, so large items count more.

#### Forecast accuracy (100 - WMAPE)

**Key:** accuracy

**Expr:** max(0, 100 - sum_abserr / sum_actual * 100)

**Format:** percent

**Primary:** true

#### Portfolio bias (forecast over actual)

**Key:** bias

**Expr:** (sum_forecast - sum_actual) / sum_actual * 100

**Format:** percent

**Description:** Positive means you over-forecast, negative means under-forecast.

**Notes:** Use the same lag and bucket for every row (for example, the forecast frozen one month before the period). Mixing lags makes the accuracy number meaningless. Items with zero actual show 0 APE here, so exclude or review them by hand.

## Content

**Intro:** Forecast accuracy is the first number every S&OP review asks for, and it is easy to get wrong in a spreadsheet. This worksheet calculates item-level percentage error, then rolls up MAPE, WMAPE, bias and accuracy so you can see which definition your business should use.

**Sections:**

#### How to use the worksheet

**Body:** Replace the example rows with your own items. Enter actual demand and the forecast for the same period and the same lag. Add or delete rows as needed. The table computes absolute error and absolute percentage error per row, and the summary block rolls up MAPE, WMAPE, accuracy and bias. Export to Excel to share it with sales or finance, or paste a month at a time to keep a history. Start with your top 20 to 50 items by volume or revenue, because the long tail is mostly noise and can swamp a simple average.

**Bullets:**

- Use demand (orders or shipments) as actual, not production.
- Keep the forecast lag consistent across all rows.
- Run it by family and by SKU, since the two answers differ.

#### MAPE versus WMAPE and which to report

**Body:** MAPE is the average of |actual - forecast| / actual across items. Every item counts equally, so a slow mover with 10 units of demand and a 5 unit miss (50 percent) can drag the average above what the business actually feels. WMAPE divides total absolute error by total actual, so volume weights the result and it is closer to units of inventory or revenue at risk. Many planners report WMAPE at portfolio level and MAPE at item level. Accuracy is commonly defined as 100 minus WMAPE, floored at zero, though some companies use 1 minus MAPE.

#### Reading bias alongside accuracy

**Body:** Accuracy says how far off you were, not which way. Bias is total forecast minus total actual divided by total actual. A portfolio with 80 percent accuracy and zero bias has random noise that safety stock can cover. The same accuracy with persistent positive bias means inventory is building, and persistent negative bias means expedites and shortages. Check bias monthly, then use a tracking signal on individual items to catch drift early.

#### Common mistakes

**Body:** Most errors come from the inputs rather than the math. Measure against the forecast that drove the supply plan, not a later version that was adjusted after the fact. Do not average percentage errors across different units of measure without weighting. Do not drop items with large misses, because that inflates accuracy. And do not compare accuracy between companies without confirming the lag, the bucket (week or month) and the level of aggregation.

**Bullets:**

- Weekly accuracy is almost always lower than monthly for the same items.
- Aggregating to family always improves the number, so state the level.
- Zero-demand periods need a rule, such as excluding them or using WMAPE only.

**Cta:** If you pull actuals and forecasts from your ERP by hand every month, we can build a read-only extract that fills this automatically from your own data, on-prem if needed.

**Faq:**

#### What is a good forecast accuracy for manufacturing?

**Answer:** It depends on item volume, lead time and variability. Aggregated families with stable demand often reach 80 to 90 percent at monthly buckets, while individual SKUs and weekly buckets run much lower. Compare against your own history and against a naive forecast rather than a universal target.

#### Should I use MAPE or WMAPE?

**Answer:** Use WMAPE for portfolio reporting because it weights by volume and handles small items better. Use MAPE or APE per item when you want to find which individual items are driving the miss. Report which one you used, since they can differ by 10 points or more.

#### How do I handle items with zero actual demand?

**Answer:** Percentage error is undefined when actual is zero. Common choices are to exclude those periods from MAPE, rely on WMAPE which still includes the absolute error, or use a scaled error such as MASE. This worksheet shows 0 APE for those rows.

#### Is forecast accuracy the same as 1 minus MAPE?

**Answer:** Often, but not always. Some teams define accuracy as 1 minus MAPE, others as 1 minus WMAPE, and some cap each item at 100 percent error. Pick one definition, document it, and keep it fixed so trends stay comparable over time.

## Search terms

forecast accuracy mape calculator, mape wmape excel, forecast accuracy worksheet, how to calculate forecast accuracy, wmape formula, forecast bias percent, demand planning kpi template, forecast error by sku

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