Intermittent Demand Forecast for Spare Parts
Model a spare part whose weekly history contains more zeros than demand events. The template keeps those zeros explicit, compares Croston-style forecasts with a recent-mean baseline, and turns the selected weekly rate into a lead-time forecast, empirically calibrated safety stock, and reorder point.
Result preview
The supplied 36-week history is 72.22% zero-demand periods with 10 non-zero events. Auto selection chooses classic Croston because it has the lowest event-only MAE, producing a 1.482-unit weekly forecast. At a 95% target service level and 4-week lead time, rolling lead-time forecast errors imply 4.98 units of safety stock and a 10.91-unit reorder point.
What this template does
- Preserves sparse zero-demand periods instead of filtering them out.
- Implements classic Croston and SBA-corrected Croston forecasting.
- Compares sparse-demand methods with a recent-mean baseline.
- Auto-selects by event-only MAE first, absolute signed bias second, and all-period MAE only as a tie-breaker.
- Lets the planner override Auto with Croston, SBA Croston, or Recent mean from the worksheet.
- Calibrates safety stock from the selected model’s rolling lead-time forecast-error distribution.
- Flags a possible obsolescence review when the trailing zero run exceeds twice Croston’s smoothed inter-demand interval.
Why Python
Croston methods maintain separate smoothed estimates for demand size and the interval between non-zero events. Rolling model evaluation and lead-time error calibration add stateful logic that is cumbersome in ordinary spreadsheet formulas but concise in Python. Excel still owns the demand history and inventory-policy controls.
Try it live
Paste additional weekly observations, change smoothing alpha, lead time, target service level, forecast horizon, or worksheet model selection to see the sparse-demand forecast and reorder point update.
Operating workflow
Author: an inventory analyst maintains the sparse-demand methods, event-aware holdout evaluation, lead-time error policy, and validation rules.
Workbook user: a planner updates item history and visible assumptions, leaves model selection on Auto or chooses an explicit method, then reviews the weekly forecast, demand-status warning, and reorder implication.
For a repeated spare-parts review cycle, the author can save the notebook to open in App mode and test the workbook with the planner who will operate it. App mode is a focused presentation, not a permissions boundary.
Download the Excel template
Inputs and assumptions
Spare Part History!A5:B1000 is the notebook input window. The supplied workbook contains 36 weekly observations and preformats two years of entry rows; blank rows are ignored, so planners can append history without changing notebook code. Populated dates must be unique, consecutive weekly observations and demand must be non-negative. At least 20 periods and three non-zero demand events are required.
Dashboard!G5:H10 contains an 8-week forecast horizon, Croston alpha 0.20, 4-week lead time, 95% target service level, and Auto model selection. Horizon and lead time are whole weeks. The service level must be at least 50% and below 100%.
Notebook implementation
The notebook reads history and controls through bf.inputs(...), validates and cleans blank history rows, defines forecasting methods in a separate reactive cell, runs the backtest and model selection downstream, calculates the inventory policy in another cell, and publishes summary, comparison, and forecast tables with bf.publish(...). The chart always reflects the worksheet-selected model; there is no separate taskpane-only model control.
How the calculation/model works
Croston exponentially smooths non-zero demand size and inter-demand interval separately, then divides the two estimates to obtain a weekly demand rate. SBA applies the common (1 − α/2) correction to reduce Croston’s positive bias. A recent 12-period rolling one-step backtest reports all-period MAE, non-zero-event MAE, and signed unit bias. Auto selection ranks event MAE first, absolute bias second, and all-period MAE third, preventing zero-heavy all-period error from being the primary decision rule. The worksheet model selection can override Auto.
For the selected method, each eligible historical origin produces a lead-time forecast and an observed lead-time demand total. Forecast error is actual lead-time demand − forecast lead-time demand. Safety stock is the chosen empirical service percentile of those rolling lead-time errors, floored at zero; reorder point is selected weekly demand × lead time plus that safety stock. This avoids applying a Gaussian formula to raw zero-inflated weekly demand, although the empirical policy still depends on the amount and representativeness of available history.
Croston itself does not decay during a long run of zeros. The template therefore compares the trailing zero run with twice Croston’s smoothed inter-demand interval and surfaces Review obsolescence when that threshold is exceeded.
Validation / expected results
The baseline checks Croston selection, a 1.482 weekly forecast, 72.22% zero share, 10 demand events, 4.98 safety stock, 10.91 reorder point, and event MAE of 4.065. Offline scenarios append a new history row beyond the original 36 weeks, exercise 1- and 16-week horizons, test alpha values of 0.05 and 0.90, verify a worksheet override to SBA Croston, and trigger the obsolescence warning with an extended trailing zero run.
Limitations
This is a single-item teaching model. The empirical service percentile uses overlapping rolling lead-time errors from one item’s history; sparse or structurally changing history may provide too little evidence for a stable inventory policy. The obsolescence indicator is a warning, not a TSB-style probability model, and it does not automatically attenuate the Croston forecast. The template does not model repairables, censored stockout demand, variable lead times, order quantities, item pooling, holding costs, or explicit stockout penalties.
When to use this approach
Use Croston-style methods when demand events are sparse and a conventional moving average is dominated by zeros. Use the worksheet override when operational context favors a method other than the automatic event-aware ranking. For large spare-parts portfolios, evaluate methods by item class and calibrate inventory policy to service and cost objectives with more extensive history and portfolio-level validation.