Case study 03 · Availability

Why is the shelf empty? Out-of-stock root-cause analysis across four warehouses

One year of daily stock, orders and deliveries – to measure the real service level, put a price on lost sales and find out what actually causes stock-outs.

Tools: SQL · Excel · DashboardData: 250 SKUs × 4 DCs · 52 weeksKPIs: Fill rate · lost sales · root causes
ⓘ Dataset is simulated to mirror a typical FMCG distribution network (250 SKUs, 4 warehouses, 14 suppliers). Methods, analysis and recommendations reflect how I work as a planner.
96.4%
fill rate (demand served from stock) vs. 98% target
€188k
lost revenue – 2.9% of annual sales
1,316
stock-out events (SKU × warehouse × week)
88%
of lost revenue comes from A items
01

Business question

Sales reported frequent out-of-stocks, while the warehouse reported high stock levels. Every department had its own explanation: wrong forecasts, late trucks, too little safety stock.

Goal: measure service level per warehouse and category, quantify lost sales and assign each stock-out to a root cause – so that actions target the real problem.

02

Data & method

Service level

Fill rate = units delivered from stock ÷ units demanded, per week, warehouse and category.

Lost sales

Unserved demand × selling price for every SKU-warehouse-week with a stock-out.

Root cause rules

Open purchase order overdue → late delivery; delivered < ordered → short delivery; demand > 140% of forecast → demand spike; otherwise → safety stock too low.

03

Interactive dashboard

Service level & stock-outs · last 52 weeks
Show

Weekly fill rate

All warehouses and SKUs · dashed = 98% target

Root causes of stock-outs

By warehouse

Fill rate by category

Target 98%

Top 10 critical SKUs

By lost revenue
04

Key findings

72%

of lost revenue is caused by late supplier deliveries – not by forecasting. Forecast-related spikes explain only 20%.

88%

of lost revenue falls on A items: the flat one-week safety stock is too thin exactly where it matters most.

93.9%

Beverages has the lowest fill rate – seasonal peaks plus long, unreliable lead times.

DC South

has the lowest fill rate (95.6%). Differences between warehouses are small – the problem is upstream, not in one site.

05

Recommendations

Tackle supplier reliability first

Start a supplier review with the four least reliable suppliers (see case study 04) – this addresses the biggest share of lost sales.

Raise safety stock for A items from risky suppliers

Include lead-time variability in the safety-stock formula instead of a flat one week of cover.

Weekly exception list for planners

Flag overdue purchase orders for A items every Monday so planners can expedite or reallocate stock between warehouses.

Agree promo volumes with Sales

Get promotion volumes at least one lead time in advance to remove the remaining demand-spike stock-outs.

06

Data

Every stock-out event with warehouse, supplier, lost units and root cause: