Trainings
Excel Tutorials
Blog
About us

ABC XYZ Analysis in Inventory Management: example in Excel

Table of Contents

The ABC XYZ analysis is a great tool to manage products and optimize inventory.

The ABC classification is a good start, but this is not enough to manage stock levels correctly. I will tell you why in this article and how to add more categories to the analysis.

To fully understand this method, you can download the ABC XYZ Analysis Excel template here:

ABC XYZ analysis in Excel: Step-by-step video tutorial 👇

The ABC classification is not enough

If you have never heard of the ABC classification, I advise you first to consult our article on the matter. I explain in concrete terms what ABC analysis is with an example to download in Excel.

Briefly, the main advantage of the 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.

abc analysis abc classification pareto law

When it comes to inventory management, the ABC Analysis is a good tool to:

  • Identify the products we should review and spend most of our time on (demand planning reviews/meetings, emails…)
  • Set Service Rate targets, that determine safety stock levels (see my article about Safety Stock here).

About the latter, there are 2 classic mistakes made by companies:

  1. Setting high Safety stock on A codes, to protect top sellers
  2. Setting low Safety Stock on A codes, because sales volume is high, so they seem more predictable

We have the opposite issues for C items:

  1. Setting high safety stock on C codes because these items are unpredictable
  2. Setting low safety stock on C codes because we don’t want to hedge low selling items

Both statements are incorrect because it all depends on the uncertainty of demand.  

XYZ analysis: demand uncertainty

To better estimate service rates and safety stock targets, we need to add another dimension: XYZ categories.

Products have different levels of demand uncertainty. For example, there are stable products such as toilet paper, and others with much more volatility, such as umbrellas – which are sold only during rainy periods (Z items). You will probably have a better forecast quality on toilet paper (X items) than on the umbrella (Z items). Other products are in-between (let’s say… french baguette, more or less stable during the year with peaks during holidays 😉)

Demand uncertainty example
XYZ Analysis with different demand patterns

ABC XYZ analysis in Inventory Management

The main idea of the ABC XYZ analysis is to combine ABC and XYZ categories across two dimensions: we end up with a matrix of 9 categories.

Then, we can classify items around 4 extremes:

AX: High sales volumes, stable
AZ:
High sales volumes, very volatile
CX:
Low sales volumes, stable
CZ:
Low sales volumes, very volatile

ABC XYZ Analysis matrix volumes and uncertainty
ABC XYZ Analysis Matrix

Inventory Management Policy

To use effectively the ABC XYZ matrix, we need to define an Inventory Management Policy: setting service level and safety stock targets. Roughly speaking, if you want a better service level, you need higher safety stock.

For example, we can choose to hold more inventory for the A category, as A items are the major drivers of your business, and we want to maximize the service level for those products. For AX items, we can afford to hold less safety stock than AZ items, as we have better visibility over the demand.

The same logic applies to B items.

Regarding C codes, you can decide to hold low inventory for both CX and CZ categories, for two reasons:

  • CX items have a low impact on the business, so we can afford to set a lower service level. Also, they are stable, so we require even less safety stock.
  • CZ items are very volatile. We can think we need therefore a bit more safety stock, but I know by experience that most of the time It is not worth it: because they are both low-selling items and very unpredictable, CZ items are often a source of high stock levels and unnecessary headaches. I noticed that CZ items represent a big part of the total sleeping inventory of most companies. It might be wise to set lower service levels for this category.
ABC XYZ Analysis Matrix: inventory policy

Here is an example of Service Level targets for the Inventory Management Policy we just defined:

ABC XYZ Analysis Matrix: service level targets

Remember that this is just an example. There is no golden rule, it all depends on your own supply chain challenges. For example, if you want to focus more on the customer service rather than the stock levels, you might want to have a bit higher service rate targets for Z items. The key is always to find the right balance between the inventory and the service level.

ABC XYZ Analysis in Excel

You can download the ABC XYZ Analysis Excel template here:

This image has an empty alt attribute; its file name is excel_gestion_de_stock_icone_3.png

In this example, we have a list of items with the last 12 months’ sales, already categorized with ABC classification.

We can see that item 1 sales are much more stable than item 2. We want to quantify this difference.


To have a good estimation of the items’ demand uncertainty, we can use the coefficient of variation (CV). It is simply the standard deviation divided by the average. We can apply the formula over the past 12 months to have a monthly coefficient of variation. The formula can be easily obtained in Excel with STDEVP() and AVERAGE() functions, as shown in the image below (cells D to K are hidden):

ABC XYZ analysis Example in Excel


The more unstable your series is, the higher your standard deviation will be. For item 1, we get a 5% coefficient of variation vs 58% for item 2 which is consistent with the graph we saw above.

Then, we need to define coefficients of variation thresholds to classify items in XYZ categories. Here, we assumed:

  • CV < 10%: X code
  • 10% < CV < 25%: Y code
  • CV > 25%: Z code


So, Item 1 is an X code and item 2 a Z code.

Of course, those threshold values are arbitrary, and you should adjust according to your data (if you have almost no X items for example). I advise you to set thresholds based on your business specificities: Identify clear patterns and group your items accordingly.

ABC XYZ Analysis: Other Purposes

ABC XYZ Analysis is not only about Inventory Management, this is also a good management tool that can be useful in various situations.

An excellent Supply Chain Education Tool

I have been a Supply Chain Manager and S&OP Manager for many years, and I have used extensively ABC XYZ analysis for educational purposes. I used it in particular to animate S&OP, customer, and supplier meetings:

  • Marketing/Sales: to educate them on why it is not possible to have a very high service rate for all the products.
  • Finance: to educate them on why it is not possible to lower inventory and holding costs for all the products.
  • Production: to tell them why the demand uncertainty and the diversity of service rate targets prevent production schedules to be steady and uniform all year.
  • Suppliers/Customers: Educate them on your own challenges and inventory strategy to find trade-offs. For example, I used to explain to customers that we had not in stock some highly unpredictable products because it was part of our policy. And I used to convince them that once I had those items back in stock, I could make fast delivery for them.

A good Crisis Management Tool

ABC XYZ Analysis is also very useful for crisis management.

By caricaturing, you might face situations when your CFO comes to your desk telling you to “cancel all orders because there is no more cash”. Or telling you the opposite, urging you to “double all orders” because you sell surgical masks and plan to make +100% in times of pandemic 😉.

Having your products well categorized is key to taking action quickly in those situations. In the first situation, you could convince your anxious CFO to save at least your most strategic products:

ABC XYZ Analysis limitations

The ABC XYZ Analysis comes with several limitations.

Demand variations and predictability

In this article, we used the coefficient of variation to assess the predictability of demand: the higher variations are, the harder it is to forecast demand.

In practice, this is often not a good indicator of demand predictability: some demand patterns are much easier to forecast than others.

In the example above, item 1 has a clear seasonal peak in July, and item 2 demand profile is much more sporadic. A forecast model taking into account seasonality parameters could perform much better with item 1 than item 2. Still, the coefficients of variation are very close (54% and 52%).

To address this issue:

  • Use your forecast instead of past sales
  • Use your forecast accuracy KPI instead of the coefficient of variation

Is ABC XYZ Analysis good enough?

With ABC XYZ classification, we assume Inventory Management is a 2 dimensions problem (items’ profitability and demand uncertainty). But in Supply Chain, we always deal with multifactor issues. We could add other categories for supply uncertainty, obsolescence risks…

The problem is that any new dimension increases the number of categories exponentially and so the level of complexity. If the level of complexity is too high in comparison to your resources, the level of performance can drop quickly. That’s why I recommend mastering the foundations before seeking more complexity.

More is not always better

ABC XYZ Analysis: Action Plan

  • Start your first ABC XYZ Analysis TODAY. You can download the file used in this article below.
  • Classify your products ABC first, then XYZ
  • Define Service level targets per category
  • Track your performance
  • Optimize
  • Share and Educate

Other Inventory Management content

I recommend you to check out our video tutorials:

  1. Safety Stock formula to define the right levels of Safety Stock (Excel Tutorial)
  2. EOQ Formula to optimize the quantity to order (Excel tutorial)
  3. 11 solutions to optimize your inventory & shortage

Become an Inventory Management Expert

If you want to go to the next level, join my next Inventory Management Workshop (free): “How to avoid shortages and overstocks in times of great uncertainty”.

Workshop with Edouard Thieuleux, Supply Chain Expert

During this webinar, I will share with you:

👉 My method for dealing with sales and supplier uncertainty: 13 parameters to master
👉 How to reduce your inventory and increase your service rate simply and automatically
👉 Excel files and case studies presented live (Zara, H&M, Amazon…)

Become an inventory management expert
Join my free 7-day mini training: “How to Reduce Stockouts and Overstock.”
Share
Boost your inventory management skills

Join my free 7-day mini training: 
How to Reduce Stockouts and Overstock.”

  • The method for dealing with Demand Uncertainty.
  • How to integrate the Reliability of your Suppliers (3 examples)
  • The 13 Parameters I use to optimize my inventory (7 of which are essential)
  • How to Automate your inventory management on Excel or ERP (and stop working like a fireman)
  • Case studies of large groups: Zara, H&M, Amazon…
is also on Youtube
+30K Subscribers

(across 2 channels)

online supply chain training platform
Ready to boost your skill ?
Discover our courses
See all courses
Excel training label
Excel Expert
View the course
SCM Analytics training label
SCM Analytics
View the course
Inventory management training label
Inventory Management
View the course
Forecasting training label
Forecasting Expert
View the course
Scroll to Top

FIND OUT HOW MUCH YOU ARE WORTH.

You will receive an email with an estimate of your salary along with some advice on how to boost it.

Your information is confidential and will never be given to third parties. You can unsubscribe in 1 click from any of my emails.