Spare Parts & Slow Movers: Intermittent Demand, ADI & CV², Croston Forecasts, Service-Level Stock, Criticality and Dead Stock

What you'll build

What you will be able to do

Each lab ends with a concrete result that you build yourself and the checker confirms, cell by cell.

• Describe 18 months of issues of a slow-moving spare part with three numbers – how often demand occurs, the average interval between demands and the typical size when it does occur – instead of one misleading monthly average.
• Measure how often six parts are demanded (ADI) and how much the demand sizes vary (CV²), sort each part into one of four demand patterns and rebuild the average demand per month from size and interval.
• Forecast a lumpy part with a 3-month moving average, measure its absolute errors and discover that a forecast of zero every month scores a better MAE – the trap that makes intermittent demand special.
• Continue a Croston forecast by one month: count the months since the last demand, update the smoothed demand size and the smoothed interval only because demand occurred, and turn them into the Croston and the bias-corrected SBA forecast.
• Find out why MAPE breaks on intermittent demand, calculate MAE, bias and a scaled MAE for the SBA forecast in single formulas, and compare them with a moving average and a forecast of zero.
• Read a fictional table of demand during the lead time, find the smallest stock that reaches a 95 % service level with XLOOKUP, check the chance it really gives and set the max level in whole packs.
• Classify every part by annual value (A/B/C), count the parts in each cell of a criticality × value matrix with COUNTIFS and look up the stocking policy that belongs to its cell.
• Count the months since each part last moved, mark active, review and write-off candidates while protecting critical parts, total the stock value per status and chart the stock value by part.