ABC XYZ Analysis In Inventory Management: Example In Excel | AbcSupplyChain (2024)

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 XYZ Analysis In Inventory Management: Example In Excel | AbcSupplyChain (2)

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 highSafety stock on A codes,to protect top sellers
  2. Setting low Safety Stockon 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
ABC XYZ Analysis In Inventory Management: Example In Excel | AbcSupplyChain (3)

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 😉)

ABC XYZ Analysis In Inventory Management: Example In Excel | AbcSupplyChain (4)

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 In Inventory Management: Example In Excel | AbcSupplyChain (5)

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 In Inventory Management: Example In Excel | AbcSupplyChain (6)

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

ABC XYZ Analysis In Inventory Management: Example In Excel | AbcSupplyChain (7)

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:

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.

ABC XYZ Analysis In Inventory Management: Example In Excel | AbcSupplyChain (9)


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 In Inventory Management: Example In Excel | AbcSupplyChain (10)


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
ABC XYZ Analysis In Inventory Management: Example In Excel | AbcSupplyChain (11)

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.
ABC XYZ Analysis In Inventory Management: Example In Excel | AbcSupplyChain (12)

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 In Inventory Management: Example In Excel | AbcSupplyChain (13)

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%).

ABC XYZ Analysis In Inventory Management: Example In Excel | AbcSupplyChain (14)

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.

ABC XYZ Analysis In Inventory Management: Example In Excel | AbcSupplyChain (15)

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)

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”.

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
)

ABC XYZ Analysis In Inventory Management: Example In Excel | AbcSupplyChain (2024)
Top Articles
African American Good Morning Greetings - Motivational Messages
Faith Lessons From the Prayers of the Black Church
Evil Dead Movies In Order & Timeline
Hotels Near 6491 Peachtree Industrial Blvd
Knoxville Tennessee White Pages
Time in Baltimore, Maryland, United States now
Robot or human?
Miss Carramello
Select The Best Reagents For The Reaction Below.
Volstate Portal
Lichtsignale | Spur H0 | Sortiment | Viessmann Modelltechnik GmbH
Mlifeinsider Okta
Acbl Homeport
Toonily The Carry
Full Range 10 Bar Selection Box
Raid Guides - Hardstuck
6001 Canadian Ct Orlando Fl
Dump Trucks in Netherlands for sale - used and new - TrucksNL
Foodland Weekly Ad Waxahachie Tx
Me Cojo A Mama Borracha
Andhrajyothy Sunday Magazine
Edicts Of The Prime Designate
Kashchey Vodka
Drift Boss 911
Little Rock Skipthegames
LCS Saturday: Both Phillies and Astros one game from World Series
Jobs Hiring Near Me Part Time For 15 Year Olds
Rust Belt Revival Auctions
Craigslist Illinois Springfield
Netwerk van %naam%, analyse van %nb_relaties% relaties
Select Truck Greensboro
Catchvideo Chrome Extension
Effingham Daily News Police Report
Mchoul Funeral Home Of Fishkill Inc. Services
Page 2383 – Christianity Today
Revelry Room Seattle
Christmas Days Away
FREE Houses! All You Have to Do Is Move Them. - CIRCA Old Houses
3473372961
Gridwords Factoring 1 Answers Pdf
Gina's Pizza Port Charlotte Fl
Dreammarriage.com Login
Aliciabibs
Craigslist Florida Trucks
Owa Hilton Email
Winta Zesu Net Worth
Academic Notice and Subject to Dismissal
The Nikki Catsouras death - HERE the incredible photos | Horror Galore
Conan Exiles Colored Crystal
6463896344
Missed Connections Dayton Ohio
Equinox Great Neck Class Schedule
Latest Posts
Article information

Author: Tyson Zemlak

Last Updated:

Views: 5948

Rating: 4.2 / 5 (63 voted)

Reviews: 94% of readers found this page helpful

Author information

Name: Tyson Zemlak

Birthday: 1992-03-17

Address: Apt. 662 96191 Quigley Dam, Kubview, MA 42013

Phone: +441678032891

Job: Community-Services Orchestrator

Hobby: Coffee roasting, Calligraphy, Metalworking, Fashion, Vehicle restoration, Shopping, Photography

Introduction: My name is Tyson Zemlak, I am a excited, light, sparkling, super, open, fair, magnificent person who loves writing and wants to share my knowledge and understanding with you.