The ABC XYZ analysis is fundamental to optimize your inventory. The ABC method is a very good start but this is not enough in managing your inventory and I will explain with examples why.
(ABC XYZ Analyse video / Please activate the automatics subtitle in English)
The ABC method is not enough
If you have never heard of the ABC classification, I advise you first to consult our article or video on the matter. I explain to you in concrete terms what an ABC analysis is and how to set it up with examples to download on Excel. Quickly, the advantage of this ABC analysis is to focus your time and energy on the products that represent your biggest sales but also the most inventory for your company.
The ABC analysis is a very good start but we will often find 2 classic mistakes in companies:
- Firstly: code A being a strong sale, you must, therefore, put a large inventory coverage to be sure not to miss any sales.
- The second: it’s the opposite, my code A is a strong sale and since it’s a strong sale, we consider that sales are very stable. As a result, we put in very low inventory coverage.
These two statements are incorrect because it all depends on the uncertainty of sales.
Example: ABC XYZ Analyse
You have ten items, 2 products code A. The first Article 1 produces an extremely stable monthly turnover while the second produces an equivalent total turnover but with a very different monthly turnover. From this, of course, you have a low risk on the first item and can assign a low inventory coverage. On the other hand, on Article 2, you have very high volatility and therefore you will need to put more inventory to cover this risk (see below).
This is the whole point of the ABC XYZ analysis, on the one hand you will classify by volume with the ABC analysis and on the other hand you will classify by uncertainty with an XYZ analysis and therefore this method is very useful in inventory management to identify products at risk, whether it is risks of shortage or overstocks to define the right inventory coverage and the right safety stock. Finally, it is widely used in the industry to optimize production volumes based on volumes and uncertainty.
What is the purpose of the ABC XYZ analyze?
What is uncertainty?
It is the fact of not predicting demand, and not predicting product sales. It is often characterized by instability in sales. Several types of products: very stable and very predictable products (X). Then you have less stable but still quite predictable products (Y). Finally, you have the products and which are completely unstable and totally unpredictable products (Z).
The summary of this ABC XYZ analysis is a matrix of nine categories. On one side you have the ABC products and on the other side the XYZ products. Each product is then identified as:
AX: large volumes, stable
AY: large volumes a little less stable and therefore more volatile
AZ: large volumes very volatile
BX: medium volumes, stable
BY: medium volumes, volatile
BZ: medium volumes, very volatile
CX: low volumes, stable
CY: low volumes, volatile
CZ: low volumes, very volatile
How to classify ABC XYZ in Excel ?
First, you need to mix your historical data with your forecasts, for example, 6 months of historical data and 6 months of forecasts. If you only have histories, that’s fine too. I also advise you to think in terms of value rather than quantity.
You have your ABC analysis and to have your XYZ analysis, you will need a coefficient of variation. There are different ways to calculate this coefficient of variation. I’ll explain one of them to you, it’s very simple. You will take the standard deviation of the series and therefore of the last months.
The standard deviation is the average deviation from the mean. The more unstable your series is, the higher your standard deviation will be. You will divide the whole by the average of the same series and therefore at the end, the more stable your product is the closer the percentage will be to zero.
Thanks to these percentages, you will then be able to use Excel IS to determine your XYZs based on the percentages. This automatic formula will allow you to determine your AX, AY, AZ products…
How to use this classification in inventory management?
If your products are AX or BX, they are very stable, you do not need a lot of inventory, you need a low inventory coverage because the risk of shortage is low.
When your products are AY or BY, you can take an average inventory coverage because the risk is controlled. If your products are AZ or BZ, i.e. very uncertain with large volumes, you will need a larger inventory coverage (if the inventory cost is not too high) because the risk of shortage is very high. Finally, you have low sales. By definition, you don’t need a lot of inventory.
The risk is low on CXs but generally, these products are very rare because when you have low sales, sales are generally very uncertain. Then you have the CY products, so low inventory with a controlled risk that still requires a small inventory coverage. And finally the last ones that represent the majority of the products generally are the CZ products. You have a high risk of overstocking, especially do not put a lot of inventory on these products or do not put any at all. These CZ products represent the majority overstock and I advise you to have some of these products only available on order.
Conclusion ABC XYZ Analyse
To summarize in terms of inventory coverage, here is a fictional example and what I would do. You can see a big coverage on high sales with a big uncertainty to be sure not to miss sales. You can optimize and reduce your inventory coverage on products with low uncertainty which will allow me to gain considerably in cash flow.
Finally, on low sales, even if I have a big uncertainty, I do not advise you to have a lot of inventory because they are low sales and therefore they will generate more inventory than lost sales.
I hope with this article that you have understood the importance of using an XYZ classification in your inventory management. For those who want to go further, you can access other examples with nearly 2000 articles over 52 weeks. I first show you how to redo an ABC Analysis in a completely automatic way.
I then go back to the coefficient of variation but also how to properly determine the variables to determine XYZ, how to have an automatic report that shows you the number of products when a value changes, how to automatically update your inventory coverage according to your item classification with formulas and finally, I talk to you about an alternative method to calculate the uncertainty.
Download Excel ABC XYZ Analyse & go further
If you want to learn how to implement your first ABC XYZ classification step by step with business example and Excel Template, discover our new ABC XYZ online course here