Forecast Accuracy Worksheet: MAPE, WMAPE and Bias by Item
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.
| Item / SKU | Actual demand | Forecast | Absolute error | APE % | |
|---|---|---|---|---|---|
140 | 11.3% | ||||
80 | 9.3% | ||||
110 | 26.8% | ||||
300 | 5.8% | ||||
30 | 16.7% | ||||
50 | 7.8% |
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.
Automate forecast accuracy from your ERP
Optional: we can review how your actuals and forecasts are stored and sketch a read-only monthly accuracy report.
Optional. The tool and every export work without this. No spam.
How to use the worksheet
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.
- 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
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
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
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.
- 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.
Frequently Asked Questions
What is a good forecast accuracy for manufacturing?
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?
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?
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?
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.
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.
Related Tools
Forecast Bias and Tracking Signal Worksheet
Enter actual and forecast for each period of one item and get running error, MAD, tracking signal and bias percent so you know when a forecast has drifted.
Supply Chain & InventoryForecast Value Add (FVA) Worksheet
Compare naive, statistical and final consensus forecasts against actuals by item to measure whether each step in your forecasting process adds or destroys accuracy.
Discrete ManufacturingForecast Accuracy (MAPE) Calculator
Turn your forecast and actual history into MAPE, accuracy, and bias figures, then price what that error is costing you across the year.
Supply Chain & InventoryDemand Plan Template by Product Family
An editable demand plan worksheet: statistical baseline, sales and marketing adjustments, consensus, growth versus last year and confidence by product family.
More Supply Chain & Inventory tools
See all- Kanban Card Size Calculator
- Supplier Corrective Action Request (SCAR) Template
- Forecast Exception Review Template
- RFQ Template for Machined Parts
- Supplier Switching Cost Breakeven Calculator
- Vendor Invoice Submission Requirements Letter Template
- Reshoring Cost Comparison Calculator
- Oracle Fusion Inventory Turns and Carrying Cost Calculator
- Distribution ERP Fill Rate Calculator