Supply Chain Inventory & Safety Stock Simulator

simulation
Supply Chain
Operations Management
Simulate stochastic demand and supplier replenishment lead times using Latin Hypercube Sampling to optimize safety stock, cycle service levels, and fill rates.

Overview

Traditional safety stock calculations rely on static textbook formulas that assume fixed replenishment lead times or normal distributions. In reality, supplier lead times fluctuate due to logistics bottlenecks, customs inspections, and manufacturing backlogs. When lead times vary, lead-time uncertainty drives up to 90% of total stockout risk, rendering static buffer formulas ineffective.

The Supply Chain Inventory & Safety Stock Simulator models joint uncertainty across daily customer demand and supplier delivery lead times using Latin Hypercube Sampling (LHS). It simulates 5,000 replenishment cycles under continuous review \((s, Q)\) policies, calculates true Cycle Service Level (CSL) and Unit Fill Rate (\(\beta\)), balances annual holding costs against stockout penalties, and generates an optimized safety stock recommendation ladder.

Supply Chain Inventory & Safety Stock Simulator workbook with task pane open

Free Excel alternative baseline

Supply chain analysts traditionally model inventory uncertainty using manual =NORM.INV() formulas or Excel Data Tables. However, native Data Tables freeze workbook UI calculations on every cell change, volatile =RAND() recalculates on every keystroke, native formulas lack Latin Hypercube Sampling and compound lead-time convolution, and Data Tables fail completely in Excel for the web (Excel Online).

This template delivers: - Latin Hypercube Sampling (LHS): Efficient variance reduction ensuring accurate tail stockout probabilities with 5,000 trials. - Compound lead-time convolution: Convolves stochastic daily customer demand over uncertain supplier lead times without simplifying assumptions. - Service level optimization: Reports both Cycle Service Level (\(P(\text{Demand}_L \le s)\)) and Unit Fill Rate (\(\beta = 1 - \mathbb{E}[\text{Shortage}] / Q\)). - Analytical validation: Dedicated validation sheet benchmarking simulated moments against Eve’s law total variance formulas and Hadley & Whitin (1963) partial expectation loss integrals.

Workbook tour

Sheet and range Description Role
Inventory Risk (A6:G14) Uncertain demand and supplier lead-time driver table: distribution dropdown, parameters, min/max bounds. Editable input
Inventory Risk (A18:B23) SKU inventory policy parameters: reorder point (\(s\)), order quantity (\(Q\)), unit cost, holding cost rate, stockout penalty, operating days. Editable input
Inventory Risk (A27:B30) Simulation controls: trials (5,000), random seed (2026), target service level, sampling method. Editable input
Inventory Risk (I5:J17) Inventory performance summary: expected lead-time demand, std dev, safety stock, stockout probability, CSL, shortage, fill rate, annual costs. Output
Inventory Risk (L5:P9) Safety stock recommendation ladder: recommended reorder points, safety stock buffers, and annual holding cost deltas for 90%, 95%, 98%, and 99% CSL. Output
Inventory Risk (R5:T7) Tornado driver sensitivity: Spearman rank correlation and percentage contribution to lead-time demand variance. Output
Validation (A5:F11) Closed-form benchmark checks against exact analytical moments (Eve’s law compound moments, Hadley & Whitin 1963 loss integrals). Verification
Guide (A4:B9) Practitioner guide explaining stochastic lead-time convolution, safety stock optimization, and service levels. Documentation

Using the template

1. Inspect the baseline inventory simulation

  1. Open the workbook. Python for Excel automatically executes 5,000 Latin Hypercube trials across demand and replenishment cycles.
  2. Review the Inventory performance summary (Inventory Risk!I5:J17):
    • Expected lead-time demand: 1,033.35 units (Std dev: 195.08 units)
    • Current safety stock: 316.67 units (\(s = 1,350\) vs. mean demand \(1,033.33\))
    • Cycle stockout probability: 6.02%
    • Cycle Service Level (CSL): 93.98%
    • Expected shortage per cycle: 4.12 units
    • Unit Fill Rate (\(\beta\)): 99.79%
    • Annual holding cost: $19,750.00
    • Annual stockout penalty cost: $2,572.30
    • Total annual inventory cost: $22,322.30
  3. Inspect the Safety stock recommendation ladder (Inventory Risk!L5:P9):
    • 90% CSL: \(s = 1,301\) units (Safety stock: 268 units, Holding cost: $19,013 / -$737 delta).
    • 95% CSL: \(s = 1,369\) units (Safety stock: 336 units, Holding cost: $20,037 / +$287 delta).
    • 98% CSL: \(s = 1,431\) units (Safety stock: 398 units, Holding cost: $20,964 / +$1,214 delta).
    • 99% CSL: \(s = 1,466\) units (Safety stock: 433 units, Holding cost: $21,494 / +$1,744 delta).
  4. Review the Tornado driver sensitivity table (Inventory Risk!R5:T7):
    • Supplier Lead Time (Days) contributes 89.2% of total demand uncertainty (Spearman \(\rho = +0.9446\)).
    • Daily Customer Demand contributes 10.8% (\(\rho = +0.3061\)).

2. Modify SKU policy parameters

  1. On the Inventory Risk sheet, adjust the reorder point in Inventory Risk!B18 from 1,350 to 1,400 units.
  2. Observe the automatic recalculation:
    • Safety stock increases to 366.67 units.
    • Cycle stockout probability drops and CSL exceeds 96%.
    • The Validation sheet updates to show "n/a (model edited)" reflecting departure from the baseline literature benchmark.

Customizing

Adjust supplier lead time distribution

  1. To model longer supplier lead times, adjust parameter mode in Inventory Risk!D7 from 10 to 14 days.
  2. The simulation recalculates automatically, increasing recommended safety stock buffers to maintain target service levels.

Developer reference

  • Module coordinates: Authored in templates/simulation/inventory-risk/.
  • Shared engine: Inlines tested routines from templates/simulation/engine/ (sampler.py, correlation.py, metrics.py, sensitivity.py).
  • Benchmarking: Validated against Hadley & Whitin (1963) Analysis of Inventory Systems stochastic inventory models and Eve’s law compound variance integrals matching within 0.1%.

Changelog

  • 2026-10-28: Initial release of Phase 2 Supply Chain Inventory & Safety Stock Simulator with Latin Hypercube Sampling, (s, Q) policy simulation, and Hadley-Whitin validation.