Case study 01 · Inventory segmentation

Where is the money tied up? ABC/XYZ segmentation of 250 SKUs

A one-year review of sales and stock in a four-warehouse FMCG network – to set differentiated stock policies and free up working capital without hurting availability.

Tools: SQL · Excel · DashboardData: 250 SKUs · 4 DCs · 52 weeksMethod: Pareto · coefficient of variation · days of supply
ⓘ 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.
30%
of SKUs (A items) generate 80% of revenue
€236k
average stock value (at cost)
€88k
stock above target cover – 37% of total
116
days of supply in the CZ segment (vs. 35-day target)
01

Business question

All SKUs were managed with the same replenishment rule: a flat one-week safety stock and fixed order quantities (MOQ). Planners felt that warehouses were full of slow movers while fast movers still ran short.

Goal: segment the assortment by value (ABC) and demand stability (XYZ), compare actual stock cover with a target per segment and quantify how much capital could be released.

02

Data & method

ABC · value

SKUs ranked by 12-month revenue. Cumulative share up to 80% = A, 80–95% = B, the rest = C.

XYZ · stability

Coefficient of variation of weekly demand: ≤ 0.5 = X (stable), ≤ 1.0 = Y (variable), > 1.0 = Z (sporadic).

Cover vs. target

Average stock ÷ average weekly demand = days of supply. Stock above the segment target, valued at cost, = excess.

SQL: ABC classification

WITH rev AS (
  SELECT sku, SUM(units * price) AS revenue_52w
  FROM sales_weekly
  WHERE week BETWEEN 53 AND 104
  GROUP BY sku
), ranked AS (
  SELECT sku, revenue_52w,
         SUM(revenue_52w) OVER (ORDER BY revenue_52w DESC)
           / SUM(revenue_52w) OVER () AS cum_share
  FROM rev
)
SELECT sku, revenue_52w, cum_share,
       CASE WHEN cum_share <= 0.80 THEN 'A'
            WHEN cum_share <= 0.95 THEN 'B'
            ELSE 'C' END AS abc_class
FROM ranked;
03

Interactive dashboard

Inventory segmentation · last 52 weeks
Metric

ABC/XYZ matrix

Pareto curve: cumulative revenue

x: share of SKUs, sorted by revenue · y: cumulative share of revenue

Share of SKUs, revenue and stock

C items tie up a disproportionate share of stock

Excess stock by segment

Stock above target cover, at cost

Slow movers (days of supply > 60)

Click a column header to sort
04

Key findings

16% vs. 5%

C items hold 16% of stock value but bring only 5% of revenue. Fixed MOQs push slow sellers to months of cover.

116 days

The CZ segment (39 SKUs, sporadic demand) carries 116 days of supply – more than three times its target.

€33k

Even stable AX items hold 17 days instead of 10: the flat one-week safety stock is too high for predictable fast movers.

€88k

In total 37% of the average stock value sits above target cover and could be released over 2–3 order cycles.

05

Recommendations

Differentiate safety stock by segment

Lower cover for AX/BX (stable, high volume), keep higher service targets for AY/AZ, and accept lower service for C items.

Renegotiate MOQs for C items

Ask suppliers for smaller order units or mixed pallets for CY/CZ items; where not possible, switch to make-to-order or central stock only.

Review CZ items for delisting

Share the slow-mover list with Sales: candidates for delisting, promotions or second-tier channels before stock becomes a write-off.

Refresh the segmentation quarterly

Run the SQL above every quarter and track excess stock as a KPI next to service level.

06

Data

Download the raw data and reproduce the analysis in Excel, SQL or Power BI: