How does the template measure forecast accuracy?

Accuracy = 1 - sum of absolute errors / sum of actual demand, which is one minus WAPE. If three months sold 90, 130 and 110 units against forecasts of 100, 110 and 120, the absolute errors are 10, 20 and 10, so WAPE is 40 / 330 = 12.1 percent and accuracy is 87.9 percent.

Why WAPE and not MAPE?

MAPE averages percentage errors row by row, so a SKU that sold 2 units against a forecast of 4 counts as a 100 percent error with the same weight as your biggest runner, and a month with zero demand cannot be scored at all. WAPE divides total error by total demand, so it weighs SKUs by volume and stays defined. Each row's absolute percentage error is still shown in the log.

What does bias tell me?

Bias = sum of (forecast - actual) / sum of actual. A bias of +25 percent means you forecast a quarter more than sold on average, which builds inventory; a negative bias means shortages and expediting. Accuracy can look acceptable while bias is large, so read both.

What is forecast value added?

It compares your forecast with a naive baseline, here the average of the previous three closed months. If the baseline's absolute errors total 600 units and yours total 450 on 3,000 units of demand, your forecast adds (600 - 450) / 3,000 = 5 accuracy points. A negative value means the forecasting effort made things worse than the simple rule.

How is the hit rate related to the Forecast Accuracy calculator?

A SKU-month is a hit when the absolute error is no more than your tolerance band times actual demand; with 20 percent, 80 to 120 units is a hit when 100 sold. The hit rate is hits divided by closed SKU-months, the same as the calculator's accuracy rate: 210 hits in 250 periods is 84 percent, one point short of an 85 percent target.

Does the template make the forecast for me?

It suggests a baseline for next month, the moving average of the last closed months, and shows it next to the forecast you logged. It does not fit seasonal or trend models; the baseline is a yardstick to beat, and the Method sheet says so.

Planning and Supply Chain

Demand Forecast Excel Template

Log each SKU's monthly forecast and actual demand in one table, and see forecast accuracy, bias, hit rate and whether your forecast beats a simple moving average, with a suggested baseline for next month.

Overview

This demand forecast template is a working Excel tool for demand planners and S&OP teams who need to know how good their forecast is, where it goes wrong and whether the effort pays off. It ships with fictional data for a valve maker forecasting eight SKUs over twelve closed months and three open ones, including seasonal irrigation valves the planner anticipates well, a SKU that sales over-forecast by about a quarter all year and a new SKU launched in April, so the dashboard tells a story on day one.

Each closed month is scored against its actual demand: error, absolute error, percentage error and whether it landed inside your tolerance band. Accuracy is one minus the weighted absolute percentage error (WAPE), formed from sums so a big SKU weighs more than a small one and a zero-demand month never divides by zero. Bias is the signed error over actual demand, so a plus sign means you are over-forecasting and building stock.

Every row also gets a naive baseline: the average actual of the previous few closed months. Forecast value added compares your error with the baseline's error on the same SKU-months; if it is negative, the forecasting process is doing worse than a simple rule. The hit rate within tolerance is the same count the Forecast Accuracy calculator on this site uses.

What this template includes

Setup sheet for the tolerance band, accuracy and hit-rate targets, the moving-average baseline window and the SKU list that feeds the dropdown
Forecast Log with one row per SKU per month: forecast, actual, error, absolute and percentage error, within-tolerance Yes or No, and the moving-average baseline with its error
Open months that carry the forecast only, so the same log is the live forecast and its own scorecard
Dashboard with SKU and month filters, KPI tiles for accuracy (1 - WAPE), hit rate, bias and forecast value added
By-SKU table with accuracy, bias, hit rate, value added, status against your target, the baseline for next month and the forecast already logged
Charts of forecast vs actual by month, accuracy by SKU against target and bias by SKU, plus a monthly table
Method sheet with every formula and its source, and a Verify sheet that re-checks them against an independent reference and the Forecast Accuracy calculator
CSV with live formulas of the Forecast Log for any spreadsheet app; the dashboard, charts, dropdowns and self-check are in the .xlsx

Suggested use case

Use it in the monthly demand review of an S&OP cycle, to show which SKUs drive forecast error, to catch a persistent over- or under-forecast before it builds inventory or shortages, and to prove whether manual overrides add value over a simple baseline.

How to use it

On Setup, set the tolerance band, your accuracy and hit-rate targets and the number of months in the baseline, then list your SKUs.
On the Forecast Log, add one row per SKU per month with the forecast you released to supply, dated the 1st of the month.
When a month closes, type its actual demand; the row is scored and the next months' baselines update.
Fix anything the Check column reports, then read the Dashboard: accuracy, bias and value added overall and by SKU, and the baseline for next month next to your forecast.

Frequently Asked Questions

How does the template measure forecast accuracy?: Accuracy = 1 - sum of absolute errors / sum of actual demand, which is one minus WAPE. If three months sold 90, 130 and 110 units against forecasts of 100, 110 and 120, the absolute errors are 10, 20 and 10, so WAPE is 40 / 330 = 12.1 percent and accuracy is 87.9 percent.
Why WAPE and not MAPE?: MAPE averages percentage errors row by row, so a SKU that sold 2 units against a forecast of 4 counts as a 100 percent error with the same weight as your biggest runner, and a month with zero demand cannot be scored at all. WAPE divides total error by total demand, so it weighs SKUs by volume and stays defined. Each row's absolute percentage error is still shown in the log.
What does bias tell me?: Bias = sum of (forecast - actual) / sum of actual. A bias of +25 percent means you forecast a quarter more than sold on average, which builds inventory; a negative bias means shortages and expediting. Accuracy can look acceptable while bias is large, so read both.
What is forecast value added?: It compares your forecast with a naive baseline, here the average of the previous three closed months. If the baseline's absolute errors total 600 units and yours total 450 on 3,000 units of demand, your forecast adds (600 - 450) / 3,000 = 5 accuracy points. A negative value means the forecasting effort made things worse than the simple rule.
How is the hit rate related to the Forecast Accuracy calculator?: A SKU-month is a hit when the absolute error is no more than your tolerance band times actual demand; with 20 percent, 80 to 120 units is a hit when 100 sold. The hit rate is hits divided by closed SKU-months, the same as the calculator's accuracy rate: 210 hits in 250 periods is 84 percent, one point short of an 85 percent target.
Does the template make the forecast for me?: It suggests a baseline for next month, the moving average of the last closed months, and shows it next to the forecast you logged. It does not fit seasonal or trend models; the baseline is a yardstick to beat, and the Method sheet says so.