Inventory decisions with the assumptions in view.
A practical workbook for turning demand, service-level, lead-time, and inventory inputs into explainable safety stock, reorder point, target stock, and replenishment decisions.
Workbook logic / example row
Action priority
Order now
The logic keeps the target, shortfall, MOQ, and order multiple visible.
01 / Inputs
Load the assumptions your team can explain.
Every yellow field has a unit and a likely source-system owner. The workbook does not hide the choices behind a black box.
| Field | Unit / format | Typical source |
|---|---|---|
| SKU | Identifier | ERP item master |
| Description | Text | ERP item master |
| Supplier | Text | ERP item master |
| Annual demand | Units/year | ERP demand history or plan |
| Daily demand standard deviation | Units/day | Demand history or planning model |
| Lead time | Days | ERP supplier/item master |
| Review period | Days | Planning policy |
| Service level | Decimal, e.g. 0.95 | Planning policy |
| Unit cost | Currency/unit | ERP item master |
| On hand | Units | WMS inventory snapshot |
| Allocated | Units | WMS allocation status |
| On order | Units | ERP open purchase orders |
| Backorders | Units | ERP order management |
| Minimum order quantity | Units/order | ERP supplier/item master |
| Order multiple | Units/order | ERP supplier/item master |
02 / Outputs
Make the recommendation auditable.
The formulas translate the loaded assumptions into a shared review surface. They are a planning starting point, not a claim of forecast accuracy.
| Output | Formula assumption | Example |
|---|---|---|
| Safety stock | Z(service level) × daily demand standard deviation × √lead time | 73.9 units |
| Reorder point | Lead-time demand + safety stock | 1,473.9 units |
| Target stock | Lead-time demand + review-period demand + safety stock | 2,173.9 units |
| Projected availability | Available inventory + on order − backorders | 650.0 units |
| Recommended order quantity | Shortfall rounded up to order multiple, not below MOQ | 1,550 units |
| Days of supply | Projected available inventory ÷ average daily demand | 6.5 days |
| Projected inventory value | Projected available inventory × unit cost | $8,125.00 |
| Action priority | Order now, plan replenishment, or monitor based on thresholds | Order now |
03 / ERP + WMS handoff
Useful before the first planning review.
The template is deliberately offline and transparent. Use these notes to make the extract and the review rhythm more reliable.
Prepare one clean extract
Bring item, demand, supplier, open-order, and inventory extracts together on the same SKU key before loading Inputs.
Make ownership explicit
ERP commonly owns item, supplier, cost, lead time, and open-order fields; WMS commonly owns on-hand and allocation snapshots.
Normalize units and timing
Convert all quantities to one planning unit, record the snapshot timestamp, and confirm calendar versus working-day lead times.
Review exceptions together
Use PO due dates, locations, status, blocked stock, and backorders to explain exceptions before releasing a decision.
04 / Preview
A small example of the two working sheets.
The real workbook includes all 15 editable fields; this compact view keeps the preview readable.
| SKU | Annual demand | Daily demand σ | Lead time | Review period | Service level | Unit cost | On hand | Allocated | On order | Backorders | MOQ | Order multiple |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| KST-100 | 36,500 | 12 | 14 | 7 | 95% | $12.50 | 420 | 50 | 300 | 20 | 100 | 50 |
Outputs use the same labels and formulas as the downloadable Optimization sheet.
| SKU | Avg daily demand | Lead-time demand | Safety stock | Reorder point | Target stock | Available inventory | Projected available | Recommended order | Days of supply | Projected value | Priority |
|---|---|---|---|---|---|---|---|---|---|---|---|
| KST-100 | 100.0 | 1,400.0 | 73.9 | 1,473.9 | 2,173.9 | 370.0 | 650.0 | 1,550 | 6.5 | $8,125.00 | Order now |
Next planning cycle
Put the assumptions in one place.
Download the `.xlsx`, replace the illustrative row, and bring the first recommendation into your next inventory review.