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.
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.
Data & method
SKUs ranked by 12-month revenue. Cumulative share up to 80% = A, 80–95% = B, the rest = C.
Coefficient of variation of weekly demand: ≤ 0.5 = X (stable), ≤ 1.0 = Y (variable), > 1.0 = Z (sporadic).
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;Interactive dashboard
ABC/XYZ matrix
Pareto curve: cumulative revenue
Share of SKUs, revenue and stock
Excess stock by segment
Slow movers (days of supply > 60)
Key findings
C items hold 16% of stock value but bring only 5% of revenue. Fixed MOQs push slow sellers to months of cover.
The CZ segment (39 SKUs, sporadic demand) carries 116 days of supply – more than three times its target.
Even stable AX items hold 17 days instead of 10: the flat one-week safety stock is too high for predictable fast movers.
In total 37% of the average stock value sits above target cover and could be released over 2–3 order cycles.
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.
Data
Download the raw data and reproduce the analysis in Excel, SQL or Power BI: