ReorderOpt: inventory and demand planning in native Excel
ReorderOpt turns a product list and a daily sales export into reorder decisions, cash-at-risk figures and plain-language guidance for each SKU. Dimitris designed and built it from 20+ years of supply chain practice, to make the logic a planning team applies explicit and traceable, cell by cell.
- Designed and built by
- Dimitris Dakos
- Runs in
- Excel, native formulas, no macros
- Scale
- Up to about 2,000 SKUs per template
- Status
- In final testing
How it works
The user pastes two things. Everything after the data check recalculates automatically.
Product list
One row per SKU: cost, price, supplier, lead time, MOQ, stock on hand.
Sales history
A daily sales export: SKU, date, units.
- Gate
Data check
Sort order, duplicates, negatives, blanks and capacity are checked first.
Classify and forecast
Each SKU is classified and routed to the forecasting method that fits its demand.
Decide
Reorder now or not, how many units, the cash needed and the next action; plus cash tied up, overstock and dead stock.
If the data check fails, no result downstream is trusted until the inputs are fixed.
The method
Every SKU is classified, routed to the forecasting method that fits its demand, and sized to a target service level. This is the logic a planning team applies, made explicit in formulas.
Classification, through several lenses
| Lens | Based on | Sets |
|---|---|---|
| Value ABC | Annual usage value at cost | Service level: A 97%, B 92%, C 85% |
| Margin ABC | Margin | Flags value-versus-margin conflicts for the owner |
| Velocity ABC | Units sold | Forecast granularity: daily, weekly or monthly |
| XYZ | Variability of daily demand | X ≤ 20%, Y 20–50%, Z > 50%; frequent zero-sales weeks mean intermittent |
Forecasting, routed by demand pattern
| Demand pattern | Method |
|---|---|
| Intermittent (Z) | Croston with Syntetos–Boylan bias correction (SBA) |
| A or B, steady or variable | Simple exponential smoothing; Holt when a trend is present |
| C, steady (X) | Moving average |
| C, variable (Y) | Simple exponential smoothing |
| Seasonal | Holt-Winters with 2+ years of history, otherwise seasonal-naive or level × uplift |
| One-shot seasonal buy | Newsvendor |
Safety stock and reorder point
SS = z · √L·σd2 + d̄2·σL2
ROP = d̄·L + SS
Safety stock covers both demand variability and supplier lead-time variability, so the reorder point uses the average lead time and the risk is not counted twice. Lumpy demand gets an empirical reorder point instead of the normal-distribution one; one-shot seasonal buys use the newsvendor critical ratio.
The same run flags cash tied up, overstock, dead stock, failed launches, GMROI below 1 and an estimate of revenue lost to stockouts.
Design choices
Native formulas only
No macros, so the file opens without security prompts and every result traces back to its inputs.
Scales by design
Sales sorted by SKU and running totals give each SKU's demand and variance in constant time, instead of SUMIFS over every row.
Data-quality gate
A Data Check sheet verifies sort order, duplicates, negatives, blanks and remaining capacity before any number is trusted.
Edge cases tested
Never-sold items, zero lead times and empty rows were seeded on purpose at 1,000 SKUs; every division and comparison is guarded.
Before the logic was built, the formula reference and the operating rules were cross-checked in four rounds, from 23 open issues to zero.