Supply Chain & InventoryFree worksheetPDF, Excel, Word export

SKU ABC XYZ Segmentation Worksheet

ABC ranks items by how much value they carry. XYZ ranks them by how predictable their demand is. Together they tell a demand planner where to spend effort: stable high value items need a good model, unstable low value items need a simple rule.

Download:

Annual value	Monthly mean	Coefficient of variation
$444,000	2,000	0.155
$403,200	800	0.525
$57,600	400	0.65
$102,000	100	1.4
$3,900	50	1.1

Total annual value

$1,010,700

Average CV

0.766

SKU count

Average value per SKU

$202,140

A common starting split is A for the top 70 to 80% of value, B for the next 15 to 20% and C for the rest. For XYZ, many teams use CV below 0.5 as X, 0.5 to 1.0 as Y and above 1.0 as Z. Adjust these cut-offs to your business.

Write classes back to the ERP

Ask for an optional review of syncing ABC XYZ classes into ERP item planning fields.

Optional. The tool and every export work without this. No spam.

How to use the worksheet

Enter annual units, unit value and the standard deviation of monthly demand for each SKU. The worksheet calculates annual value, monthly mean and the coefficient of variation. Sort by annual value in Excel after export, assign A, B and C by cumulative share, and assign X, Y and Z by CV thresholds. Choose the class from the dropdowns to keep your decision with the data.

The method

Coefficient of variation equals the standard deviation divided by the mean. A low value means stable demand and good forecastability. ABC uses cumulative share of annual value, commonly around 70 to 80% for A. The nine cells that result, AX through CZ, drive different policies: automatic statistical forecasting for AX, collaborative review for AZ, simple min-max rules for CX.

CV = standard deviation / mean
A = top share of value
X = low variability
Z = erratic or lumpy demand

Using the segments

Spend planner time on A items with Y or Z variability, where a better input produces the biggest effect. Automate X items. For CZ items, avoid heavy forecasting effort and use make-to-order or higher safety margins where service permits. Review the segmentation at least twice a year because items migrate as they mature.

Common mistakes

Calculating CV on a series that includes a known one-time spike or a stock out inflates variability. Clean obvious outliers or flag them. Another mistake is using revenue instead of margin or criticality for ABC when criticality matters, for example for long lead time components.

Frequently Asked Questions

What is ABC XYZ analysis?

It is a two dimensional classification. ABC ranks SKUs by value contribution, XYZ ranks them by demand variability using the coefficient of variation. The combination guides forecasting method and inventory policy per segment.

What CV thresholds define X, Y and Z?

There is no universal rule. Many teams use below 0.5 for X, 0.5 to 1.0 for Y and above 1.0 for Z. Set cut-offs from your own distribution so each class has a meaningful size.

Which period should I use for variability?

Use monthly buckets across at least 12 to 24 months if the item is old enough. Weekly data suits fast moving items. Keep the same bucket for all SKUs in a comparison.

How often should I redo the segmentation?

At least twice a year, and after major portfolio or customer changes. Items launched recently should be classed provisionally until they have enough history.

Netray can calculate ABC XYZ classes from your ERP history and write them back to item planning fields for approval.

Talk to an Expert Explore More Free Tools

Related Tools

Supply Chain & Inventory

More Supply Chain & Inventory tools

See all

Back to All Tools