Download the free template
One sheet, no macros. Paste up to 36 months of demand into the yellow column and set α. The sheet calculates the Croston and SBA forecast month by month, plus ADI, CV² and the demand pattern.
Download the Croston & SBA template (.xlsx)
Works in Excel 2010 and later and in LibreOffice. Every number was checked against an independent implementation before publishing.
How Croston's method works
Ordinary exponential smoothing treats a zero month like any other value, so on an intermittent SKU it drops after every zero and jumps after every order. Croston (1972) separated the two things that actually vary:
- z — the typical size of an order when one arrives
- p — the typical interval, in periods, between orders
Both are smoothed with the same constant α, and only in periods with demand. In a zero month nothing is updated; the interval counter just grows by one.
When demand dt > 0: z = z + α (dt − z) p = p + α (q − p)
Forecast rate per period = z ÷ p
Here q is the number of periods since the previous order, counting the current one. The forecast is a rate: average demand per period. It does not say which month the next order will land in; for a quarter's volume, multiply the rate by three.
Starting values
At the first month with demand, set z to that demand and p to the number of periods up to and including it. Before that there is nothing to forecast from, which is why the template leaves those rows blank.
SBA: the one-line bias correction
Syntetos and Boylan (2001) showed that z ÷ p systematically overestimates the true demand rate. Their approximation removes most of that bias by scaling the result:
SBA rate = (1 − α ÷ 2) × z ÷ p
With α = 0.1 the correction is 5%. It sounds small; across a portfolio of slow movers held on a safety-stock policy it is not. In practice SBA is the better default whenever Croston is a candidate.
The Excel formulas
Demand in column B from row 5, α in cell B2. Row 5 starts the counters; from row 6 down:
| Column | Meaning | Formula in row 6 |
|---|---|---|
| C | Periods since last demand (q) | =IF(N(B5)>0,1,C5+1) |
| D | Size estimate (z) | =IF(N(B6)>0,IF(D5="",B6,D5+$B$2*(B6-D5)),D5) |
| E | Interval estimate (p) | =IF(N(B6)>0,IF(E5="",C6,E5+$B$2*(C6-E5)),E5) |
| F | Croston rate | =IF(D6="","",D6/E6) |
| G | SBA rate | =IF(F6="","",(1-$B$2/2)*F6) |
Row 5 uses C5 = 1, D5 = IF(N(B5)>0,B5,"") and E5 = IF(N(B5)>0,C5,""). Fill rows 6 onward down as far as your history goes.
Worked example, α = 0.1
The first twelve months of the template's sample SKU:
| Month | Demand | q | z | p | Croston rate | SBA rate |
|---|---|---|---|---|---|---|
| 1 | 0 | 1 | — | — | — | — |
| 2 | 0 | 2 | — | — | — | — |
| 3 | 14 | 3 | 14.00 | 3.00 | 4.67 | 4.43 |
| 4 | 0 | 1 | 14.00 | 3.00 | 4.67 | 4.43 |
| 5 | 0 | 2 | 14.00 | 3.00 | 4.67 | 4.43 |
| 6 | 9 | 3 | 13.50 | 3.00 | 4.50 | 4.27 |
| 7 | 0 | 1 | 13.50 | 3.00 | 4.50 | 4.27 |
| 8 | 0 | 2 | 13.50 | 3.00 | 4.50 | 4.27 |
| 9 | 0 | 3 | 13.50 | 3.00 | 4.50 | 4.27 |
| 10 | 21 | 4 | 14.25 | 3.10 | 4.60 | 4.37 |
| 11 | 0 | 1 | 14.25 | 3.10 | 4.60 | 4.37 |
| 12 | 6 | 2 | 13.43 | 2.99 | 4.49 | 4.27 |
Month 3 is the first order: z = 14, p = 3, so the rate is 14 ÷ 3 = 4.67 a month. Nothing moves through months 4 and 5. In month 6 an order of 9 arrives after a gap of 3: z = 14 + 0.1 × (9 − 14) = 13.5 and p = 3 + 0.1 × (3 − 3) = 3.0, giving 4.50. In month 10 an order of 21 arrives after a gap of 4: z becomes 14.25, p becomes 3.1 and the rate rises to 4.60.
Choosing α
The literature on intermittent demand generally recommends low values, roughly 0.05 to 0.2, because each update rests on a single order. A high α lets one unusual order reset the forecast. Rather than pick by feel, fit α on the SKU's own history: try a small grid of values, forecast each month using only the months before it, and keep the α with the lowest error.
Where Croston falls short
- Obsolescence. If orders stop, Croston keeps forecasting the old rate indefinitely, because nothing updates in zero months. TSB fixes this.
- Bias. Classic Croston over-forecasts; use SBA unless a backtest says otherwise.
- No timing. The rate says nothing about which month the next order lands in. For stock decisions, pair it with the lead time and a service-level policy.
- Smooth demand. On SKUs that sell every month, Croston reduces to exponential smoothing with extra steps. Classify first.
Research references
- Syntetos, Boylan & Croston (2005) — on the categorization of demand patterns; the 1.32 and 0.49 cut-offs.
- Croston (1972) — forecasting and stock control for intermittent demands.
- Syntetos & Boylan (2001) — the bias in Croston's method and the SBA correction.
- Kostenko & Hyndman (2006) — a note on the categorization of demand patterns.