ABC XYZ Analysis for Inventory Management: Example in Excel (Full Tutorial)
Вставка
- Опубліковано 25 лип 2024
- Download the ABC XYZ Analysis in Excel: abcsupplychain.com/download-a...
Join my next LIVE Inventory Management WORKSHOP (free): abcsupplychain.com/inventory-...
The ABC Analysis is a good start, but it's not good enough to classify your products and optimize your inventory.
Here are the 2 classic mistakes I have made for many years ⛔️ :
1) High Safety stock on A codes, to protect our top sellers
2) Low Safety Stock on A codes because sales volume is high, so they seem more predictable
We have the opposite mistakes for C codes:
1) High safety stock because these items are unpredictable
2) Low safety stock because we don't want to cover low selling items
Whatever the approach, the result is the same:
Too much inventory and shortages at the same time 😭
That is why I want to introduce you to the ABC XYZ Analysis.
In this new step-by-step tutorial video, I tell you:
-Why the ABC analysis is not enough
-How to define the demand uncertainty XYZ
-My step-by-step tutorial in Excel with examples
-How to combine ABC XYZ Analysis with Safety Stock
-What the limitations of ABC XYZ Analysis are
-The action plan to start optimizing your inventory TODAY 🚀
In the comments, let me know what you think of the ABC XYZ analysis. Is this method good enough? ;-)
I am always grateful to receive your comments and feedback to improve my pedagogy 😁
Edouard
▬▬▬▬▬▬▬▬▬▬▬ CHAPTERS ▬▬▬▬▬▬▬▬▬▬▬▬
ABC XYZ analysis in Excel:
00:00 Intro: ABC Classification is not enough
00:38 ABC Analysis: quick reminder
01:24 2 common mistakes
02:29 Demand Uncertainty
03:04 ABC XYZ Analysis Matrix
04:56 ABC XYZ Analysis: example in Excel
08:20 ABC XYZ Classification limits
9:05 Conclusion: Is ABC XYZ analysis good enough?
10:05 A great Supply Chain education tool
11:12 A great Crisis Management tool
12:13 Your Action Plan
13:18 Free Inventory Management Workshop
MY BLOG: abcsupplychain.com
#supplychain #inventorymanagement #abcxyz
Many people asked me to provide the file used in the video. Here it is: abcsupplychain.com/download-abc-xyz-analysis/
Enjoy!
Hi Edouard, thanks for your sharing. Here I have a question about the "low safety stock for the AZ", though it is very unstable, but for fullfilling the customer requirement(OTIF), we have to prepare more for safety stock. I think there should be some balance between the service level and the safety stock. Thanks in advance.
I am 3 weeks into my first ever job as supply chain analyst and this is GOLD.
I am super glad it was helpful!
🎉🎉🎉 You are great and thanks for your efforts ❤
Hi Edouard. Thanks for sharing those impressive IM tutorials. Very useful not only for those who are starting on the supply chain but also for those not too familiar with the topic.
Wow. I just discovered this very underrated channel. I can't wait to watch your free cources. Thank you so much. You got my subs.
Excellent overview, thank you!
really helpful videos. thank you for educating
All your videos are excellent
very good video, thank you very much!
this is very help full thank you
very helpful videos... thank you.
Thank You Very much👏
Life saver✨
Amazing analysis! Thankyou
My pleasure!
2 videos into this absolute masterclass of a channel and I am blown away! Thank you so much for explaining complicated concepts so clearly! I have a question, when you were talking about standard deviation in this video as a way to measure how fluctuative a particular product is, the column next to it is called "coefficient variation" and there is a percentage number, how is that calculated? I did a rough test and could it be Std. Dev/Average Sales? Anyway I will be watching the rest of the videos in this channel now, thank you so so much for the amazing material you've put up Mr Edouard!
Thank you so much for your videos!! They are very inspiring! My question is, when performing ABC xyz analysis, should we be using monthly dollars booked (incoming sales), or monthly sales (outgoing sales)?
Hi Edouard, the tutorials are quite helpful. In this video you have mentioned about the 2nd method but its not covered in the video and I have not been able to find it in other videos of your. It would be quite helpful if you can share the 2nd method as well.
Thanks a lot Edouard for sharing the knowledge in a very clear way. It is really a great starting point for me to step into supply chain domain :).
You're welcome Vasudev, glad it was helpful!
@@abcsc can you recommend or suggest the sequence in which we do ABC, XYZ AND FSN analysis
Thanks. Great Video
My Pleasure!
Thanks a lot, Edouard! Great video and it's very easy to understand and follow through it. I just keep watching one after another!!! Very addictive! I realized this tool is no longer available when I tried to download it. Can you please make it available again? Really appreciate it!
Hi and thank you very much for your feedback, i really appreciate ! I didn't provide an Excel File for this video, just try to redo the example :) if you want to go further don't hesitate to come to my free workshop abcsupplychain.com/inventory-management-workshop/
Good Job ! Edouard.
Thank you!
Great video. Exist some difference between the use of standard deviation of a sample and standard deviation of a population in the formula? Which is better?
Hi Eduard. Thanks for some truly great videos.
Do you have any rule-of-thumb regarding the percentage of items which should be classified as X, Y and Z?
Does your course come with the full access to your excel database/dashboard? I'm thinking of signing up for you course. This is great information. I appreciate your sharing and educating.
what a great lessons, may i get file at 8:20 sir for full view procuring base on EOQ 🙏thanks a lot
Great explanation, well done. I myself opt for a lower SS on "AX" materials as those are expensive and due to predictability the business can give good enough service level without needing to tie up lot of cash. Sure companies can have different lead times and agreements which need alternative stock keeping strategy.
Thanks for your feedback! 😀Curious to know how you manage your "AZ" items: do you have set higher safety stock levels? Cheers
@@abcsc Typically I use the terminologies you defined in your educational tool slide. Convincing sales to have a customer outline agreement and implement high stocks only if customers are willing to pay for the price that we taking by stocking up. Does it always work? you bet no :)
I. agree, low SS for AX SKUs since they are more predictable, an higher SS for AZ SKUs. Great videos @abcsc!
HI Eduourd, thanks for this video. Its helpful to do the analysis. I had a question. While doing the ABC analysis, we did it on the basis of value but while doing the XYZ analysis, do we need to do it on the basis of volumes or value and if yes on value, how can we calculate safety stock on XYZ based on value? Thanks.
when there be another free workshop in July! hope there is, your classes are great.
You can join the waiting list here: abcsupplychain.com/inventory-management-workshop/
Hi Edouard, thank you for the amazing video! Is it possible to download the "full database" file you mentioned at 08:14?
Hi Edouard, thanks for your series of videos which I find very helpful. I have a question on your table @11:12. My understanding of safety stock is that its purpose is to cater for unexpected situations (like volatile demand), so I'm surprised that you are suggesting high safety stock for AX and low safety stock of AZ. I was expecting it to be the other way around. If the demand is predictable like it is for X materials, shouldn't you be able to reduce your safety stock?
Hi, thank you for this video. But ı need different and complicated XYZ formula.
Thank you very much.But in 11.13 secs - can you explain why we need low safety stock for AZ (we do have high uncertainty and volumes are high).I guess it should be HIGH SAFETY STOCK
Great job, many thanks Edouard. I have a question: when you have to analyze 4000 items, with different units of measurement (pcs, Kg, etc), how do you do the ABC classification on the quantity consumed?
Hi Ale, thanks for your feedback! There is a simple solution to get rid of your problem: classify your products by sales value (for example last 3 months' sales value). I always recommend to do it by value (or even margin) to see the most profitable products. Nails are not your most profitable products even if you sell huge quantities of it ;)
Great video!
I have some doubts. If I work at supply management I should make the global ABC XYZ analysis by costs or quantity to use(units, kg, etc) of the insums and materials used for production? On the other hand I should do the analysis according the type of insume for example ABC XYZ analysis for colorants wich uses 0,01 kg per ton according the colour. Other ABC XYZ analysis per fragances wich uses 20 kg per ton and so on.
I am looking forward for you reponse. Thanks a lot.
Hi Edouard. Thanks for this very enlightening video!
I've seen examples where the square of the Coefficient of Variation (CV²) is used.
What benefit/disadvantages does this have in comparison to the method you demonstrated?
Hi Raf, you're welcome!
The square is used to "penalize more" the largest variations.
The best is to use your forecast KPI if you have one, because it reflects your real ability to predict a product
I enjoyed this video a lot. Great job! Can you share a video about product distribution? for example from Distribution Centers to Stores? best practices and analytics. Thanks!
Great video. I am graduated in management engineering, and i found your video very useful!
I perfomed the ABC XYZ analysis on my
company's item !
I have some issues related to the coefficiente of variations : our demand Is affected by High seasonality
How could we deal with It?
Hi and thank you for your feedback! This is a very good question, I talk about it in the article: abcsupplychain.com/abc-xyz-analysis/
(Go to ABC XYZ analysis limitations)
How often do you recommend updating ABC XYZ?
hey I have a question: if we already calculate the ABC base on average sales performance, isn't that already take the % fluctuation into account. It seems XYZ is redundant if I put it that way ?
Thank you for uploading. I've been waiting for a long time now. Your singular ABC helped me categorising the amount of sales (in quantity) for 400 sterile hospital disposables. Since february have I been working to optimize two decentral strorages at the OR. I've made a double ABC by bonding the amount of order quantities to amount of sales (in quantity). That way I selected a couple disposables to be replaced? What do you think of this method? Should I continue with this or should I try it your way? I'd like to hear tour opinion. Thanks in advance
Hi and thank you for your feedback! Yes ABC is a good start, try to classify by value rather than quantity if you can, to see what items have the most impact on your business. You can try then to add the XYZ uncertainty by analyzing past demand data as shown in the video (or forecast if you have)
Hi Edouard, thanks for sharing another great tutorial video. One question: Why A-X category needs to hold a high safety stock at 12:13?(I think when demand is stable, the ss can be set relatively low)
Hi! In that case I was talking about a crisis management, where you just want to focus on the most predictable and "get rid" of the rest ;) this was just an example
Thanks for yet another great tutorial. Will using data per day or week give a better result? Or is it just overkill? I have the consumption data per day and have to merge it to week or month to calculate with those periods.
You're welcome! If you reorder once per week, it is good to have weekly data ;)
Hi Joe! Yes that's true, that's why you need to know at least the ABC category of your product: ua-cam.com/video/W1LHRXATmVw/v-deo.html
Great job. Where i can find the execl sheet?
Hi Mohammad, Thanks! I don't share the file for this tutorial, but try to make it on your own following the few steps 😊
At 11:15 you have a chart that seems to conflict with the chart and explanation given at 3:00. At 3:00 you explain that an AZ may have more safety stock due to the high variability, then at 11:15 it shows the opposite. Can you please clarify or tell me what I am misinterpreting
question y must have a category of Seasonality
ABC -> Consumes
XYZ -> Variation of Consumes
What other element do you consider relevant apart from consumption and variability of consumption?
Example i think something like Seasonility (but is nearly similar to Variation of Consumes)
DEF -> Seasonality
XYZ is used to assess the uncertainty of your products. There is no such thing as adding a category for seasonality. If you want to be more precise, you can use forecast accuracy instead of the coefficient of variation in the XYZ classification. Thus, the uncertainty is assessed with the level of accuracy of your forecast
Hi Edouard, where we can download the excel example? Thanks for your sharing. It's helpful
I went to your blog but for this particular excel template, the download link does not work. Thanks
Hi, you can send us an email at contact@abcsupplychain.com
Huge thanks to you Eduardo🫶🏻🫶🏻
Hi Edouard, thanks for the video, very helpfull! I'm currently working on a business project with a large data set that I'm doing an ABC-XYZ analysis on.
I have one complex question that I hope you can help me with, concerning the calculation of average and ST.D. for the XYZ part.
First the question: How do I input 0 values in the blank cells in between the sales data values, and not in the blanks before and after the actual sales values (only during the PLC)?
And then the explanation (may be needed to understand the question):
I have data on 2000 SKU's (columns) over a period of 36 months(rows). The SKU's have different PLC's as they are introduced during the full data period, and therefore I have a lot of 0 values in different columns and rows in the dataset, and some SKU's have 0 sales during the PLC as well. In order for Excel to work automatically for me, I have replaced all 0's with blanks so they don't affect the average and ST.D score, but I need excel to keep the 0 values that were during the product life cycle, otherwise the average will be too high and ST.d will be to low to reflect the real picture of the SKU's.
So how do I input 0 values in the blank cells in between others values, and not in the blanks before and after the actual sales values?
Worst case I will have to go through all 2000 lines manually ;)
Thanks!
Hi Larsen. It is a classic problem :)
First, you need a column with "New Product" "Active" and "Discontinued" to identify the stages of your products. Then:
-You can clean manually if it is not too much time-consuming.
-You can make it automatic by establishing rules between sales value and Product Status value. For example, if the current status is New product, it means you are not currently selling it but you might be next month, so you only take the Std deviation of positive sales values.
-If you have a forecast with good forecast accuracy metrics, use the forecast accuracy instead of the Standard deviation (best solution)
Regarding typing manually, I hope you don't do that manually :)).
Just simply select all your data from items all the way until the Grand Total, then go up right in the corner of excell where you can see "Find&select", choose "Replace", type 0 at "Replace with" and then "Replace all". You welcome.
Category AX you’ve listed as having high safety stock but given high volume low variation the opposite should be true
Hi Craig, this case was an example when you get a crisis ;)