ABC XYZ Analysis for Inventory Management: Example in Excel (Full Tutorial)

[@abcsc]

Many people asked me to provide the file used in the video. Here it is: abcsupplychain.com/download-abc-xyz-analysis/ Enjoy!

[@haijundeng9483]

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.

[@aakashniteshkumarpatadiya5398]

I am 3 weeks into my first ever job as supply chain analyst and this is GOLD.

[@abcsc]

I am super glad it was helpful!

[@juliopena9136]

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.

[@sound-engineer]

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.

[@GAND3RSON]

All your videos are excellent

[@jajatikesharidev]

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.

[@cocochang8608]

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!

[@abcsc]

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/

[@jamesrudary4435]

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

[@xiaojienan7891]

Amazing analysis! Thankyou

[@abcsc]

My pleasure!

[@kabirhussain1725]

🎉🎉🎉 You are great and thanks for your efforts ❤

[@abcsc]

Much appreciated 😊

[@anastasi5946]

Excellent overview, thank you!

[@pekandras3880]

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.

[@abcsc]

Thanks for your feedback! 😀Curious to know how you manage your "AZ" items: do you have set higher safety stock levels? Cheers

[@pekandras3880]

@@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 :)

[@ljmarinc]

I. agree, low SS for AX SKUs since they are more predictable, an higher SS for AZ SKUs. Great videos @abcsc!

[@splattrick2432]

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!

[@vasudevnarayan6568]

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

[@abcsc]

You're welcome Vasudev, glad it was helpful!

[@vasudevnarayan6568]

@@abcsc can you recommend or suggest the sequence in which we do ABC, XYZ AND FSN analysis

[@fenixchow1]

very good video, thank you very much!

[@danielcordova8767]

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?

[@alamRizvi-sj3bj]

really helpful videos. thank you for educating

[@tiagra4103]

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?

[@abcsc]

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)

[@Ale-jj6jk]

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?

[@abcsc]

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

[@malindusandanuwan9555]

this is very help full thank you

[@pawineeouiwattanachai7014]

Life saver✨

[@dg7150]

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.

[@waseemchakkala7949]

Good Job ! Edouard.

[@abcsc]

Thank you!

[@frankandersendk]

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?

[@mohamedhossam5830]

Thank You Very much👏

[@paf432]

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?

[@zulkhaireesulaiman8575]

when there be another free workshop in July! hope there is, your classes are great.

[@abcsc]

You can join the waiting list here: abcsupplychain.com/inventory-management-workshop/

[@tugceyildiz5806]

Hi, thank you for this video. But ı need different and complicated XYZ formula.

[@186santhosh]

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

[@rafwenger]

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?

[@abcsc]

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

[@opioelijah4665]

Thanks. Great Video

[@abcsc]

My Pleasure!

[@IamMukalen]

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

[@abcsc]

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)

[@muizbarok6022]

what a great lessons, may i get file at 8:20 sir for full view procuring base on EOQ 🙏thanks a lot

[@danielhoraciocordovazambra8773]

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.

[@mohammadsweileh8601]

Great job. Where i can find the execl sheet?

[@abcsc]

Hi Mohammad, Thanks! I don't share the file for this tutorial, but try to make it on your own following the few steps 😊

[@kbazyl]

How often do you recommend updating ABC XYZ?

[@juliatsoy9446]

Huge thanks to you Eduardo🫶🏻🫶🏻

[@rajal1994]

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.

[@raginghotdogs]

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

[@fsz9048]

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)

[@abcsc]

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

[@yimingma5977]

Hi Edouard, thank you for the amazing video! Is it possible to download the "full database" file you mentioned at 08:14?

[@williamlarsen456]

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!

[@abcsc]

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)

[@florian4200]

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.

[@bichnhatpham2]

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 ?

[@AlyDaae]

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!

[@Pikminbloomshorts]

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.

[@abcsc]

You're welcome! If you reorder once per week, it is good to have weekly data ;)

[@abcsc]

Hi Joe! Yes that's true, that's why you need to know at least the ABC category of your product: kzbin.info/www/bejne/jWKveYWOdrmgjNk

[@purdiyantipratiwi5767]

Hi Edouard, where we can download the excel example? Thanks for your sharing. It's helpful

[@purdiyantipratiwi5767]

I went to your blog but for this particular excel template, the download link does not work. Thanks

[@abcsc]

Hi, you can send us an email at [e-mail]

[@KiyoshiNagata-rd6hv]

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

[@abcsc]

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

[@craigandrews7544]

Category AX you’ve listed as having high safety stock but given high volume low variation the opposite should be true

[@abcsc]

Hi Craig, this case was an example when you get a crisis ;)

[@JasonWalker-l6g]

Ondricka Pine

[@crissanee9392]

very helpful videos... thank you.