What is XYZ Analysis, and Its Importance in Category Management

XYZ analysis is one of the key methods of assortment analysis in category management, as it helps you make the right decisions about inventory management and, most importantly, avoid a shortage or excess of inventory.

This is important since inventory makes up a large part of the company’s assets, and inventory management affects such critical KPIs as turnover, revenue, turnover costs, profit, and profitability. Low inventory (shortages) can lead to losses due to unsatisfied demand and, therefore, to loss of profits and potential buyers. Whereas the accumulation of excess stock (surplus goods) binds the enterprise’s working capital, reducing its potential and slowing down turnover, which affects the overall costs and financial results of the business.

In this article, you will learn what XYZ analysis is, how it helps you forecast product demand and manage inventory, and, most importantly, how to do it.

Contents

What Is XYZ Analysis

How XYZ Analysis Relates to ABC Analysis

XYZ Analysis in Excel with an Example of How to Calculate It

How To Conduct an XYZ Analysis

How to Apply the Results of XYZ Analysis

FAQ

What Is XYZ Analysis

XYZ analysis is a mathematical and statistical method that allows you to analyze fluctuations in the sale of goods or services over a certain period and assess sales stability. In simple terms, XYZ analysis is a framework for classifying products based on variability in demand for them.

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.

How XYZ Analysis Relates to ABC Analysis

ABC analysis in category management is a method of classifying products by priority based on selected criteria, where A is high priority, B is medium priority, and C is low priority.

Thus, XYZ analysis and ABC analysis are two different methods of evaluating data: XYZ analysis is used to study the dynamics of data over a period of time, such as the variability of consumer demand for certain products; ABC analysis is focused on classifying objects according to their importance.

The most effective from the planning point of view is a combination of ABC and XYZ analysis. Using ABC analysis, you can evaluate individual products according to their priority (high, medium, low), and using XYZ analysis, you can predict sales of these products. That is, instead of three categories, ABC/XYZ analysis gives you nine categories for classification, which allows you to make more rational decisions. You can read about how products are classified based on ABC/XYZ analysis in our article on using these tools for inventory planning.

XYZ Analysis in Excel with an Example Calculation

Before moving on to XYZ analysis, we recommend that you familiarize yourself with the example we prepared in Excel and uploaded to Google Sheets.

The following description of how to conduct XYZ analysis in Excel is based on a case where the object of analysis is a brand category. Use our ready-made template and download an example of XYZ analysis for a product range.

How To Conduct an XYZ 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:

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.

How to Apply 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.

FAQ

What is the main purpose of XYZ analysis in inventory management?

The main purpose of XYZ analysis is to categorize products based on the variability in demand for them, helping companies prioritize effectively.

What are the benefits of implementing XYZ analysis in category management?

Benefits include optimizing your purchasing strategy and responding to changes in demand.

In what areas is XYZ analysis used?

XYZ analysis is mainly used in retail and e-commerce. But XYZ analysis is a universal tool that can be used in various industries where effective inventory management is critical.

How often should a company review and update its XYZ classifications?

The division of goods into X, Y, and Z classes should be reviewed regularly, ideally annually, to take into account changes in market conditions and demand patterns. This ensures that your inventory management strategies remain aligned with current market realities.