Logistics restructuring worth millions in annual savings greenlit
Fill-rate impact modelled at SKU-store granularity across all affected stores
Decision unblocked in weeks after months of prior deferral
Targeted mitigations identified for highest-risk categories and stores
A scenario modelling tool simulating on-shelf availability at SKU-store granularity unblocked a stalled logistics restructuring decision worth millions in annual savings for an international retailer. The tool decomposed profitability impact by category across sales, margin, waste, and substitution effects, giving the commercial team bounded risk ranges and targeted mitigations that converted months of deferral into a confident go-ahead within weeks.
The Problem
The retailer's logistics partner had proposed new delivery schedules for the UK business. Consolidating and rescheduling deliveries would reduce direct delivery costs by millions annually. The proposal sat on the table for months because nobody could answer the question that mattered most: what would happen to on-shelf availability in the affected stores?
The Solution
We built a scenario modelling tool that simulated on-shelf availability under alternative delivery patterns at SKU-store granularity. The tool took each store's actual sales velocity, stock-on-hand profile, and replenishment parameters, then projected what would happen to fill rates if delivery frequency changed according to the proposed schedules.
The simulation used a proxy fill-rate approach rather than modelling the full replenishment chain. This was a deliberate choice: modelling every warehouse allocation decision and backroom-to-shelf process would have introduced complexity that dwarfed the signal. The proxy approach used observed stock profiles and sales patterns to estimate the probability of a gap, which was sufficient for decision-making. Each simulation run produced a fill-rate delta per SKU-store under the new schedule.
The modelling did not stop at availability. We decomposed the profitability impact by category, tracing the chain from availability change through to sales loss, margin erosion, waste effects, substitution behaviour, and service level outcomes. Three core scenarios (expected, downside, and stress test) were constructed with sensitivity ranges across demand volatility, store adaptation speed, substitution rates, and waste coefficients. Category-level views enabled targeted decision-making: rather than accepting or rejecting the schedule change wholesale, the business could consider category-specific mitigations such as holding additional buffer stock for highest-risk lines or phasing the rollout. The entire pipeline was implemented in Databricks with parameterised notebooks, so the model could be re-run rapidly as proposals evolved.
Results and Impact
| Metric | Value |
|---|---|
| Decision enabled | Green light for logistics restructuring worth millions in annual savings |
| Risk quantification | Fill-rate impact modelled at SKU-store level across all affected stores |
| Scenarios delivered | 3 core scenarios (expected, downside, stress test) with sensitivity ranges |
| High-risk lines identified | Specific SKU-store combinations flagged for mitigation before rollout |
| Time from model delivery to decision | Weeks, after months of prior deferral |
| Mitigation actions | Targeted buffer stock increases and phased rollout for highest-risk categories |
The business approved the schedule change with quantified guardrails attached: monitoring triggers on fill rates for flagged categories, pre-positioned buffer stock for the highest-risk lines, and a phased rollout sequence starting with the lowest-risk regions. The logistics savings flowed to the bottom line with availability risk actively managed rather than ignored or feared.
Key Takeaways
-
Ranges and scenarios beat single-number forecasts for high-stakes decisions. Executives did not need a precise prediction. They needed the spread of plausible outcomes and the conditions that would push results toward the worst case. A range with explicit assumptions gave them something to act on; a point estimate would have been debated endlessly.
-
Granularity surfaced the risk that averages hid. The national average fill-rate impact was small and reassuring, but buried within it were a handful of stores and product lines where the impact was severe. Those pockets of risk would have generated customer complaints and reputational damage if missed.
-
Unblocking a stalled decision can be worth more than the analysis itself. The logistics savings had been identified long before this work started. Every month of deferral was a month of savings foregone. The model's value was in providing the risk assurance that released an existing opportunity.