Forecast Bias and Tracking Signal Calculator Worksheet
A forecast can be accurate on average and still be biased in one direction for months. The tracking signal catches that by dividing the running sum of forecast errors by the mean absolute deviation. This worksheet computes it from your own period data.
| Period | Actual | Forecast | Error (actual - forecast) | Absolute error | |
|---|---|---|---|---|---|
-5 | 5 | ||||
4 | 4 | ||||
-15 | 15 | ||||
-12 | 12 | ||||
-8 | 8 | ||||
-18 | 18 | ||||
-9 | 9 | ||||
-13 | 13 |
Sign convention here is actual minus forecast, so a negative tracking signal means persistent over-forecasting. Some companies use forecast minus actual, which flips the sign; the limits stay symmetrical. Use a rolling window of 6 to 12 periods for fast moving items.
Weekly bias report from your ERP data
Optional: we can look at how your forecast history is stored and outline an automated tracking signal report.
Optional. The tool and every export work without this. No spam.
How to use the worksheet
Enter one item or one family at a time, one row per period, in time order. Actual and forecast must use the same bucket and lag. The worksheet calculates each period error and absolute error, then sums them. RSFE is the sum of signed errors. MAD is the sum of absolute errors divided by the number of periods. Tracking signal is RSFE divided by MAD. Keep at least 6 periods, because a short history makes the signal swing wildly. Export to Excel to keep a running log per item.
The formulas and what the limits mean
Tracking signal = RSFE / MAD. If errors are random, positive and negative misses cancel and the signal hovers around zero. If the forecast is consistently high or low, the signed errors pile up and the signal grows in magnitude. Common practice treats +4 and -4 as control limits, and some planners use +3 and -3 for critical items. Crossing the limit does not mean the model is wrong in every period, it means the cumulative drift is larger than random variation explains.
- Within the limits: noise, keep monitoring.
- Outside the limits: review the model, the override history and any demand shifts.
- Persistent same-sign signal: look for a systematic sales or marketing override.
Worked example
With the example rows, eight weeks of data give a sum of errors of -76 and a sum of absolute error of 84. MAD is 10.5, so the tracking signal is about -7.2, far outside the limit. Bias is about -9 percent of actual, meaning the forecast ran roughly 9 percent high. A planner would check whether a promotion or a lost customer was never removed from the baseline, and reduce the forecast or the sales override before the excess inventory builds.
Common mistakes
Do not reset the running sum silently after a miss, because that hides drift. Do not compute the signal on aggregated data and assume each item is fine, since offsetting biases cancel at family level. Do not use the signal as the only trigger for slow movers, where one large order dominates MAD. Also confirm the sign convention with your team so the same number means the same thing in every report.
Frequently Asked Questions
What is a tracking signal in forecasting?
It is the running sum of forecast errors divided by the mean absolute deviation. It measures whether errors are one-sided. A value near zero suggests random error, while a large positive or negative value suggests persistent bias in the forecast.
What tracking signal limits should I use?
Plus or minus 4 is the most common textbook limit, and plus or minus 3 is used for tighter control on key items. Choose limits based on how costly a missed bias is for the item, and check them against history to avoid constant false alarms.
How do I calculate forecast bias as a percent?
Sum the signed errors (actual minus forecast, or the reverse if you prefer) and divide by the sum of actual demand, then multiply by 100. State the sign convention clearly so readers know whether a positive value means over or under forecasting.
Does a good MAPE mean there is no bias?
No. MAPE uses absolute errors, so over and under forecasts both add to it. A forecast can have a modest MAPE and still lean one way every period. Always read bias or the tracking signal next to accuracy.
If your ERP exposes forecast and actual history, we can build a read-only job that scores the tracking signal for every item each week and flags the outliers to the planner.
Related Tools
Forecast Accuracy MAPE and WMAPE Worksheet
Fill in actual and forecast per SKU and get item-level percentage error, plus portfolio MAPE, WMAPE, bias and forecast accuracy. Exports to Excel.
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.
Supply Chain & InventoryExponential Smoothing Forecast Calculator (Simple and Holt)
Enter the prior level, trend, latest actual and smoothing constants to get the simple and Holt trend forecasts, with a chart showing how alpha changes the result.
Discrete ManufacturingDemand Planning Maturity Assessment
Score your demand planning maturity across forecasting method, accuracy tracking, S&OP process, and data granularity in about five minutes.
More Supply Chain & Inventory tools
See all- Oracle Fusion Inventory Turns and Carrying Cost Calculator
- Distribution ERP Fill Rate Calculator
- Distributor Rebate Tracking Worksheet
- Branch Transfer Planning Worksheet
- Distributor Slow-Moving Inventory Review Worksheet
- Distributor Vendor Performance Scorecard Worksheet
- Distributor GMROI Calculator
- Distributor Backorder Cost and Fill Rate Calculator
- EOQ with Vendor Minimum Order Calculator