How to Do an XYZ Analysis: Measuring How Steady Demand Is and Reading the ABC XYZ Matrix

Two parts, same yearly figure

The impeller and the electric motor in the example below both go out 1,200 times a year. An ABC analysis puts the motor in A and the impeller in B, because the motor costs more. It says nothing about how the 1,200 arrive. The impeller's months run from 85 to 120 pieces. The motor's run from 55 to 150.

An XYZ analysis measures that spread, and the spread decides how much safety stock a part needs and whether a reorder point formula fits it at all. Z holds the parts whose months jump around. X holds the steady ones, whose monthly consumption stays close to its average, and Y sits between.

Put the XYZ class next to the ABC class and you get a grid of nine cells, the ABC XYZ matrix. Each cell asks for a different kind of rule, which is the point of doing both. Price plays no part in the XYZ calculation itself.

The invented example: twelve months for fifteen parts

The fifteen parts, their ABC classes and their yearly units come from the pump maker's spare parts store in how to do an ABC analysis. What this article adds is a monthly breakdown, and in every row the twelve months add up to the yearly figure from the ABC list. Like the ABC article, this is an invented example. The company, the parts and every figure are made up.

How to do an XYZ analysis, step by step

The steps below use the impeller. Every other row works the same way.

Step 1: monthly consumption

Take twelve months of consumption per part, then flag any month in which the bin stood empty for most of the time. Such a month shows too little, and the one after it often too much. Consumption here means what the workshop or customers actually drew.

Step 2: mean

1,200 ÷ 12 = 100 pieces a month.

Step 3: standard deviation

Subtract the mean from each month. For the impeller that gives −5, 10, −12, 5, 20, −8, −15, 8, 15, −10, −3 and −5. Square each one and add them up.

Sum of squares = 1,406

1,406 ÷ 12 = 117.17

√117.17 = 10.82 pieces

This example uses the population standard deviation, dividing by 12, because the twelve months are the whole period the classes are meant to describe.

Step 4: coefficient of variation

Coefficient of variation = standard deviation ÷ mean

10.82 ÷ 100 = 10.8%

Dividing by the mean is what lets you compare a part that moves 40 pieces a year with one that moves 9,000.

Step 5: class

The limits are a choice made for this example and not a standard: X up to and including 25%, Y up to and including 50%, Z above that. Write yours down and keep them from one year to the next. At 10.8% the impeller is X, and with its ABC class it lands in BX.

All fifteen parts

Seven parts are X, four are Y and four are Z. The table rounds means and standard deviations to one decimal, but each coefficient comes from the unrounded figures.

No housing went out in seven of the twelve months, then 16 went out in October. That pushes its standard deviation above its mean and its coefficient past 100%, which is the correct result.

The ABC XYZ matrix and what each cell means for ordering

The formulas from how to calculate reorder point and how to calculate economic order quantity assume demand that is steady, or at least spread around its mean like a bell curve. The XYZ column tells you whether that holds for a part. The ABC row tells you how much time the part deserves.

One conversion comes first. Divide a monthly standard deviation by √30.42, the square root of the average days in a month, and you get a daily one, provided days vary independently. The safety stock formula in the reorder point article, z × σ × √lead time, needs the daily figure because it counts the lead time in days.

AX: let the formulas run

The reorder point article assumed a daily spread of 6.0 pieces for the mechanical seal, and 33.1 ÷ √30.42 comes out at exactly that. At a 95% service level with 12 days of lead time, that article arrived at 34.3 pieces of safety stock. The system holds 80.

Before you cut the gasket set's safety stock, find out why someone typed 250 into the system. The formula knows nothing about late deliveries. With a 21-day lead time it asks for 1.65 × 48.3 × √(21 ÷ 30.42) = 66.2, so 67 pieces, which leaves 183 sets or €1,098 of stock above the calculation.

By the EOQ formula the seal comes to 14.7 orders a year. For AX parts like the seal and the gasket set, a calculated reorder point and the EOQ pay off and can then run on their own.

AY: the motor is short

The motor's EOQ of 84 covers less than one average month. With months between 55 and 150, the swings land on the safety stock, and the system holds less of it than the formula asks for. The motor carries 30 pieces on a 35-day lead time. With its spread the formula gives 1.65 × 31.49 × √(35 ÷ 30.42) = 55.7, so 56 pieces, and the reorder point rises from 146 to 171. Check an AY reorder point more often than an AX one, every quarter for example.

AZ: the control board needs a conversation

Ask sales and service where the board's big months come from. If they are orders known weeks ahead, the board belongs on order-driven purchasing rather than a reorder point. The formula still produces a number, 1.65 × 35.0 × √(140 ÷ 30.42) = 123.9 boards, 26 under the 150 in the system. It says little about a part with no boards out in April, 110 in May and three months at 90 or more.

The B row

The impeller is BX. 1.65 × 10.82 × √(30 ÷ 30.42) = 17.7, so 18 pieces against 40 in the system. The 22 extra impellers are €836 of stock and the reorder point would drop from 139 to 117. The ball bearing is X by a narrow margin, see mistake 5. The pump shaft is BY and gets the same treatment as the motor at lower priority. The pressure switch is BZ on paper, but mistake 2 shows why that label misleads.

The C row

CX parts such as the screw set get one fixed rule and large orders, reviewed once a year. CY parts such as the motor capacitor and rating plate get a generous safety stock in days. For CZ the first question is whether to stock the part at all, and the lead time answers it. The pressure gauge had four months with nothing drawn. If it can be ordered when a job comes in, it needs no fixed stock. The reorder point article counts 180 old-series housings on the shelf against 40 used a year.

Doing the XYZ analysis in a spreadsheet

The formulas below are written for row 2. Copy each one down. They assume one part per row, rows 2 to 16, with the twelve months in columns B to M.

1. Column N, mean: =AVERAGE(B2:M2)
2. Column O, standard deviation: =STDEV.P(B2:M2). STDEV.P divides by 12, which matches the choice above.
3. Column P, coefficient of variation: =O2/N2, formatted as a percentage.
4. Column Q, XYZ class: =IF(P2<=0.25,"X",IF(P2<=0.5,"Y","Z"))
5. Column R, ABC class from the sheet built in the ABC article: =XLOOKUP(A2,ABC!$A$2:$A$16,ABC!$G$2:$G$16)
6. Column S, matrix cell: =R2&Q2, which gives AY, CZ and so on.

Type a 0 for a month with no consumption. Don't leave the cell empty. AVERAGE and STDEV.P skip blank cells, and for the housing the sheet then averages five months instead of twelve and returns a mean of 8 rather than 3.3.

Five counting mistakes in an XYZ analysis

All five use figures from the fifteen-part list.

1. Using quarters instead of months

Count in quarters and 13 of the 15 parts come out X. Only the pressure switch and the housing stay Z. The control board shows how that happens. Its quarters read 135, 150, 140 and 175, and its coefficient falls from 70.0% to 10.3%.

Keep monthly buckets, and keep them every year. A long lead time is handled in the safety stock formula, as with the board's √(140 ÷ 30.42) above. It is no reason to classify by a longer time period. With almost everything in X, the classes would separate nothing.

2. Reading a season as noise

The pressure switch shows 64.4% and lands in Z. Yet 214 of its 300 pieces go out between April and August, 71.3% of the year. Within those five months the mean is 42.8 and the coefficient 16.7%. In the other seven months it is 12.3 and 36.1%.

Its consumption follows the calendar. A seasonal part needs two reorder points and a date to switch between them, as mistake 2 of how to calculate reorder point shows. Look at the twelve months of every Z part before you call it unplannable.

3. Comparing standard deviations directly

By standard deviation alone, the ball bearing at 48.9 and the gasket set at 48.3 swing hardest, and the housing at 5.1 and pump shaft at 4.1 look calm. The gasket set moves 750 pieces a month, so 48.3 is 6.4% of it. For the housing, 5.1 pieces is one and a half times the mean.

A standard deviation is measured in pieces of that part. Only divided by the mean does it compare across parts.

4. Putting the monthly spread into the daily formula

Spread and lead time have to share a unit. Either divide the monthly figure by √30.42 or express the lead time in months.

Plug the seal's monthly 33.1 straight into z × σ × √lead time with 12 days and you get 1.65 × 33.1 × √12 = 189.2 pieces of safety stock instead of 34.3. The reorder point goes from 197.3 + 34.3 = 231.6, rounded up to 232, to 197.3 + 189.2 = 386.5, rounded up to 387.

5. Switching between STDEV.S and STDEV.P

Which of the two you use matters less than using the same one every year. Switch, and parts change class while their consumption stays where it was.

The ball bearing shows how close that can be. STDEV.S divides by 11 rather than 12, which lifts every coefficient by a factor of √(12 ÷ 11) = 1.044. The bearing's 24.5% becomes 25.5%, and it moves from X to Y. With STDEV.S the list has six X parts instead of seven.

The next step

Fix your two limits and write them down before you export anything. The export covers your A and B parts, twelve months of consumption each, month by month. Work out mean, standard deviation and coefficient as above. Before you compare system and calculated safety stock for the AX and AY parts, go through the AZ parts and find out where their big months come from.

If you want to know where in your process safety stocks get set, and who checks them, that is the scope of our process analysis. Fixed scope, fixed duration, fixed price.