# SKU ABC XYZ Segmentation Worksheet

> Source: https://netray.co/tools/sku-abc-xyz-segmentation-worksheet
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Rank SKUs by annual value and demand variability. Calculates coefficient of variation per SKU and the share of total value, ready for Excel.

## Category

supply-chain

## Audience

- Demand planner
- Inventory planner
- Supply chain analyst

## Tool

**Kind:** worksheet

**Mode:** editable

**Currency:** $

**Columns:**

#### SKU

**Key:** sku

**Type:** text

#### Annual units

**Key:** annual_units

**Type:** number

#### Unit value

**Key:** unit_value

**Type:** number

**Format:** currency

#### Annual value

**Key:** annual_value

**Type:** number

**Expr:** annual_units*unit_value

**Format:** currency

#### Monthly mean

**Key:** mean_m

**Type:** number

**Expr:** annual_units/12

**Format:** decimal

#### Monthly std dev

**Key:** std_m

**Type:** number

**Help:** Standard deviation of monthly demand.

#### Coefficient of variation

**Key:** cv

**Type:** number

**Expr:** if(gt(annual_units,0),std_m/(annual_units/12),0)

**Format:** decimal

#### ABC class

**Key:** abc

**Type:** select

**Options:**

- A
- B
- C

#### XYZ class

**Key:** xyz

**Type:** select

**Options:**

- X
- Y
- Z

**Rows:**

**Sku:** HX-100

**Annual units:** 24000

**Unit value:** 18.5

**Std m:** 310

**Abc:** A

**Xyz:** X

**Sku:** HX-210

**Annual units:** 9600

**Unit value:** 42

**Std m:** 420

**Abc:** A

**Xyz:** Y

**Sku:** LM-330

**Annual units:** 4800

**Unit value:** 12

**Std m:** 260

**Abc:** B

**Xyz:** Y

**Sku:** LM-340

**Annual units:** 1200

**Unit value:** 85

**Std m:** 140

**Abc:** B

**Xyz:** Z

**Sku:** ZK-020

**Annual units:** 600

**Unit value:** 6.5

**Std m:** 55

**Abc:** C

**Xyz:** Z

**Totals:**

#### Total annual value

**Key:** annual_value

**Agg:** sum

**Format:** currency

#### Average CV

**Key:** cv

**Agg:** avg

**Format:** decimal

#### SKU count

**Key:** sku

**Agg:** count

**Format:** integer

**Summary:**

#### Average value per SKU

**Key:** avg_value

**Expr:** if(gt(count,0),sum_annual_value/count,0)

**Format:** currency

**Notes:** 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.

## Content

**Intro:** 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.

**Sections:**

#### How to use the worksheet

**Body:** 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

**Body:** 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.

**Bullets:**

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

#### Using the segments

**Body:** 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

**Body:** 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.

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

**Faq:**

#### What is ABC XYZ analysis?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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?

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

## Search terms

ABC XYZ analysis excel, ABC XYZ segmentation template, coefficient of variation demand variability, SKU segmentation demand planning, XYZ analysis demand variability, ABC analysis worksheet, forecastability segmentation

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json