Forecast accuracy (MAPE) — measure how wrong your buying forecast is, in a spreadsheet
Measure MAPE forecast accuracy and bias in a spreadsheet — a worked 3-month example, a cost-of-error table, and when the forecast isn't the problem.
InventoryX is in development and not available today. ClearStock is the live Shopify app.
You can't improve a forecast you've never scored. Forecast accuracy is measured with MAPE — mean absolute percentage error — which tells you how wrong your numbers run on average, and bias, which tells you which direction you're consistently wrong. A merchant who buys on gut usually suspects the gut is roughly right; scoring one quarter of purchases against actuals almost always changes that belief, usually in one specific direction (most people under-order their fast movers). Here's how to score your own forecast in a spreadsheet with a three-month worked example, and the trap where the "accuracy problem" is actually a measurement problem.
The two numbers: MAPE and bias
For each SKU and period:
- Error = forecast − actual. Positive means you over-forecast (bought/expected too much); negative means under.
- Absolute % error = |forecast − actual| ÷ actual × 100. This kills the sign and puts every SKU on one scale.
- MAPE = the average of those absolute % errors across SKUs and periods. One number: "my forecasts are off by 34% on average."
- Bias = the average of the signed errors. If MAPE is 34% and bias is −28%, you're not randomly wrong — you're systematically under-forecasting by about 28%, and that's a fixable habit, not noise.
The separation matters: MAPE with zero bias means your errors scatter both ways (hard to fix, partially hedged by safety stock). MAPE with strong negative bias means one adjustment — order more — fixes a large share of it immediately.
Worked example: scoring three months of buying
Take a bike-parts store, three SKUs, one forecast per month (what the owner expected to sell), against Shopify's actual units sold:
| SKU | Month | Forecast | Actual | Error | Abs % error |
|---|---|---|---|---|---|
| Brake pads (hero) | Aug | 120 | 160 | −40 | 25.0% |
| Brake pads (hero) | Sep | 130 | 175 | −45 | 25.7% |
| Brake pads (hero) | Oct | 140 | 190 | −50 | 26.3% |
| Chain lube | Aug | 60 | 58 | +2 | 3.4% |
| Chain lube | Sep | 60 | 61 | −1 | 1.6% |
| Chain lube | Oct | 60 | 57 | +3 | 5.3% |
| Bike computer (slow) | Aug | 15 | 6 | +9 | 150.0% |
| Bike computer (slow) | Sep | 15 | 9 | +6 | 66.7% |
| Bike computer (slow) | Oct | 12 | 5 | +7 | 140.0% |
- Brake pads MAPE ≈ 25.7%, bias −25.7% — identical to the MAPE, which is the point: pure habit, zero scatter. Consistently under-forecast by a quarter, every month.
- Chain lube MAPE ≈ 3.4%, bias ≈ +2.4% — a slight over-forecast, not worth acting on. Forecast is fine; leave it alone.
- Bike computer MAPE ≈ 119%, bias +119% → wildly over-forecast. But note the scale problem: on a SKU selling 5–9 units, one unit of error is 10–20 points of MAPE. Slow movers make MAPE look catastrophic; score them on absolute units instead (over-buying by 7 units/month) and read them as a stocking decision, not a forecasting one.
Read the table like an operator: one SKU needs a systematic upward adjustment, one needs nothing, one needs to stop being forecast and start being min-max. That split — not the store-wide MAPE — is the output.
Cost-of-error sensitivity: why direction changes the price
The two error directions cost you different things, and not symmetrically:
| You under-forecast (bias −) | You over-forecast (bias +) | |
|---|---|---|
| Immediate effect | Sales you can't make — dark days on a proven seller | Cash tied up, shelf space on slow stock |
| Per-unit cost | Lost margin on units you know would have sold | Carrying cost on units you must still sell |
| Can it self-correct? | No — the sale is gone when stock lands | Yes — it sells eventually, at a price |
| Partially mitigated by | Higher reorder points, earlier order-by dates | Lower buy quantities, more frequent orders |
| Worst case | Repeat dark periods on the hero SKU → the leak compounding | Dead stock requiring markdowns |
For most catalogs the under-forecast side is more expensive per unit of error — a lost hero-SKU sale never comes back, while an over-bought unit usually sells at some discount. That asymmetry is the entire argument for tilting your corrections toward the under-forecast side: if your bias is negative, fix it aggressively; if positive, fix it gently. The sizing of those buffers is the safety stock question, and the trigger that converts a corrected forecast into an order is the reorder point formula.
The trap: when "bad accuracy" is actually censored demand
One reading rule before you score your own numbers: if the SKU went dark during the period, its actual is not demand — correct the actuals or exclude dark-period observations before scoring, per the sales velocity, corrected for days out of stock method.
Building the scoring sheet
- One row per SKU-month: forecast units (what you planned for), actual units (from Shopify's sales export), and whether the SKU had any dark days that month.
- Columns for error, absolute % error; monthly and quarterly MAPE and bias per SKU, plus a store-wide view.
- Ten minutes a month, back-filled for one quarter to start. After one quarter you'll have something almost no gut-buyer has ever had: a measured sense of which direction and which SKUs your error lives in.
Then act on the bias, not the MAPE: a consistent −30% bias on a category is a standing adjustment you can apply today (order 1.4× the forecast and re-score next quarter), while a high scatter error is what your safety buffer exists to absorb.
Common questions
What is MAPE in forecasting? MAPE — mean absolute percentage error — is the average of |forecast − actual| ÷ actual across your SKUs and periods, expressed as a percentage. A MAPE of 30% means forecasts miss actuals by 30% on average. It's the standard accuracy score because it's scale-free, letting you compare a 5-unit SKU against a 500-unit one.
What's the difference between MAPE and forecast bias? MAPE measures how wrong you are regardless of direction; bias measures which direction dominates. A MAPE of 25% with zero bias is scattered error; a MAPE of 25% with −22% bias means you systematically under-forecast — which is far easier to fix, because one standing adjustment corrects most of it.
What MAPE is acceptable for a small store? There's no honest universal threshold — accuracy depends on demand stability, SKU age, and scale, and slow movers make MAPE look far worse than fast ones. Score your own baseline first, then improve against it. Watch the bias as much as the MAPE: direction is what you can actually correct.
Why does my forecast look terrible on slow-selling products? Scale. On a SKU selling 5 units a month, a 2-unit forecast error is 40% MAPE even though it's a trivial buying difference. Score slow movers on absolute units instead, and treat them as min-max stocking decisions rather than forecasting problems.
Should I exclude out-of-stock days from forecast accuracy? Yes — dark-day sales aren't demand; correct or exclude them before scoring, using the method in our sales-velocity correction article.
Next step
Back-fill one quarter: pick your ten fastest movers, enter forecast-vs-actual for the last three months, and compute MAPE and bias per SKU. Then look only at the bias column — a consistent sign on your heroes is worth more than any forecast tool, because it's an adjustment you can apply to next week's buy this afternoon.