The business problem
Procurement and planning teams often work from separate reports. Buyers see supplier data, planners see stock data, and nobody sees how a late supplier turns into a stockout two weeks later. The goal is one view that connects supplier performance to inventory health.
Starting from questions, not charts
| Stakeholder | Question they need answered |
|---|---|
| Head of Procurement | Which suppliers are consistently late or short-shipping, and how much spend is at risk? |
| Supply planner | Which SKUs are heading for a stockout, and is a supplier the cause? |
| Finance | How much working capital is tied up in excess or slow-moving stock? |
KPI dictionary
| KPI | Definition | Target (example) |
|---|---|---|
| On-time in-full (OTIF) | Supplier deliveries received on or before the due date with full quantity ÷ total deliveries | ≥ 95% |
| Supplier lead-time variance | Std. deviation of actual vs. agreed lead time (days) | ≤ 2 days |
| Days of cover | On-hand stock ÷ average daily forecast demand | By SKU class |
| Stockout rate | SKU-days with zero stock ÷ total SKU-days | ≤ 2% |
| Excess stock value | Value of stock above the maximum days-of-cover band | Trending down |
Data model
I used a star schema so the measures stay simple and the report stays fast:
- Fact tables: Purchase order lines (ordered, received, due and receipt dates), daily inventory snapshots
- Dimensions: Supplier, Product (SKU, category, ABC class), Location, Date
Dashboard layout
Wireframe, overview page
OTIF %KPI card with trend vs. last month
Stockout rateKPI card
Days of coverKPI card
Excess stock £KPI card
Supplier scorecardTable: OTIF, lead-time variance, spend. Conditional formatting flags the worst performers.
At-risk SKUsProjected days of cover vs. supplier lead time; click a SKU to see its supplier
TrendOTIF vs. stockout rate over 12 months, showing the link between supplier delays and availability
Slicers: date range, category, location, supplier. Drill-through from any supplier to its open POs.
What the data showed
I generated a year of synthetic data (8 suppliers, 40 SKUs, 3 distribution centres) and ran every KPI in SQL:
- OTIF was 82.9% against a 95% target; only 2 of 8 suppliers met it.
- One supplier, at 61.4% OTIF, caused 64% of all lost sales. Its SKUs were out of stock 5.2% of the time.
- SKUs from on-target suppliers were out of stock less than 0.1% of the time.
- Stock for B and C class items was running below the agreed cover bands: a replenishment settings problem, not a supplier one.
What this shows as a BA
- Starting from decisions and questions, not from available data
- Writing precise KPI definitions so everyone calculates them the same way
- Designing a data model and layout that answers each stakeholder's question within a click
Reflection
A good KPI dictionary prevents most dashboard arguments. When finance and procurement disagree about "on time", the fix is an agreed definition, not a new chart.