Case study 04 · Supplier management

Who can we rely on? Supplier performance scorecard for 14 suppliers

One year of purchase order lines scored on OTIF, lead-time reliability and fill rate – and translated into safety-stock decisions.

Tools: SQL · Excel · DashboardData: 15,252 order lines · 14 suppliersKPIs: OTIF · lead-time variability · fill rate
ⓘ 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.
78.8%
OTIF – order lines delivered on time and in full
85.9%
on-time delivery
4
high-risk suppliers (score < 55 of 100)
69%
of supplier-caused lost sales come from these four
01

Business question

Case study 03 showed that most lost sales start with late deliveries. Procurement needed facts for supplier reviews, and planning needed to know which suppliers require more safety stock – and how much.

02

Data & method

KPIs per supplier

OTIF, on-time rate, fill rate (delivered ÷ ordered), average lead time and its standard deviation in weeks.

Weighted score

0–100 points: 50% OTIF, 25% lead-time reliability, 25% fill rate. Below 55 = high risk, 55–75 = medium, above 75 = low.

Safety stock

SS = z × √(LT × σ²demand + D² × σ²LT) with z = 1.65 (95% service) – including lead-time variability.

03

Interactive dashboard

Supplier scorecard · last 52 weeks
Risk

OTIF vs. lead-time variability

Top-left is unreliable: low OTIF, high variability. Dot size = order lines

Supplier ranking

Click a column header to sort

Recommended safety stock – A/B items from high-risk suppliers

Current: 1 week of demand · New: formula incl. lead-time variability · Top 10 by added value
04

Key findings

S06, S04, S12, S09

are high-risk suppliers: OTIF between 55% and 75% and lead times that vary by more than a week.

€95k

lost revenue in one year is linked to late or short deliveries from these four suppliers – 69% of all supplier-caused losses.

S11: 86

The most reliable supplier reaches 93% OTIF – proof that the network can perform; the gap is supplier-specific.

€36k

of extra safety stock on 42 A/B items would protect against roughly €95k of lost sales per year.

05

Recommendations

Quarterly reviews with high-risk suppliers

Share the scorecard, agree on OTIF targets and root causes (capacity, transport, order lead times) and follow up every quarter.

Variability-based safety stock

Use the formula above for A/B items from risky suppliers instead of a flat week; reduce it again once OTIF improves.

Second source for critical A items

For the top lost-sales SKUs from high-risk suppliers, qualify a second source together with Procurement.

Monthly scorecard in the planning routine

Automate the scorecard from purchase order data (SQL + dashboard) so it takes minutes, not a day, to update.

06

Data

All purchase order lines with promised and actual lead time, ordered and delivered quantity: