What is XYZ Analysis, and Its Importance in Category Management

The ABC and XYZ analyses are tools used to classify products, services, nomenclature analysis, and sales. They help to determine the product’s value in the assortment the company presents and form the volumes of new supplies based on the obtained data. With their help, you can always see popular products, spending on purchases, and profits made. It means that the business owner’s finances will be spent rationally, and profits will be higher.

Experts recommend using ABC and XYZ in combination to get the maximum efficiency of these tools. So, today, let’s take a closer look at XYZ analysis.

Definition, purpose, and usefulness of the tool

XYZ analysis is a tool that allows you to determine the frequency and regularity of product sales.

First, let’s start with some theory, and then let’s move on to practice 🙂

Different products have different levels of demand. There are stable products (bread, salt, toilet paper). The number of sales of such goods hardly changes from period to period. In other words, they are the most predictable. In XYZ-analysis, they belong to the class “X”.

Goods with more variability (gloves, umbrellas, sunglasses) are in class Z. Since we don’t know exactly how many cold, rainy, or sunny days the future will bring, reliable demand prediction is impossible.

You will have a better prediction for bread and toilet paper (X) than for gloves and an umbrella (Z).

All other products belong to the category with medium-stable sales (class “Y”), which means that demand for them is relatively stable throughout the year. But during holidays, specific events, and fashion trends, surges in sales occur. It means that it is possible to foresee the variability of demand to a certain extent.

Algorithm of analysis

Step 1. Select objects for analysis.

Brands, categories of goods, suppliers, etc., can be the objects of analysis. But most often, the goods are selected. We have prepared an example of the XYZ analysis in Google Tables. In this case, the object of analysis is a brand

Step 2. Choose a classification attribute.

The classification attributes are usually cost price, frequency of transactions, and gross income. Regarding category management, it is turnover or the number of sales.

Our example uses the number of sales as a classification criterion.

Step 3: Choose the number of periods to analyze.

We use six months in our example. Enter the data on the number of sales for each month.

Step 4. Calculate the coefficient of variation.

The coefficient of variation is a relative value that serves to characterize the fluctuations (variability of the sign) and is calculated by the formula:

where Vσ is the coefficient of variation,

σ standard deviation, which we find by the formula:

x̄ — arithmetic average:

Excel has no function to calculate Vσ but there are formulas to calculate the standard deviation and the arithmetic mean of a number series. We use them.

We calculate the coefficient of variation for the first brand. Put it in the cell →calculate the “Standard deviation” of the range for all six months of STDEV.S (B4: E4) and divide by the average value of the same range AVERAGE (B4: E4). That is, the formula is as follows:

=STDEV.S(B4:E4)/AVERAGE(B4:E4).

Pulling the formula down, Excel counts Vσ for each brand in the list.

Step 5. Rank the objects of analysis in order of increasing coefficient of variation.

Sort brands by increasing the coefficient of variation and create an ordered list.

Step 6. Segment the obtained results into groups.

Group X — small variation (from 0 to 10%): X products show a stable turnover over a long time.

Group Y — medium variation (from 11 to 25%): although the demand for Y products is not constant, it is possible to predict the variability of demand to a certain extent.

Group Z — considerable variation (in the interval over 26%): the demand for goods Z can fluctuate a lot or occur from time to time.

So, we have segmented brands based on their predictability, that is, the probability that demand will be different than predicted 🙂 Now let’s look at how to apply this in practice.

Applying the results of XYZ analysis

In category management, XYZ analysis is used to plan purchases to avoid shortage or excess of goods.

The results obtained above should be considered when planning order schedules. For example, goods X should be ordered most frequently, goods Y less regularly, considering expected changes, and goods Z even less often because demand for them is irregular and unpredictable.