ABC Analysis : Step-by-Step Tutorial in Excel with 500 products
Download my ABC Analysis Excel file here: abcsupplychain.com/download-e...
Join my next Supply Chain KPIs Workshop (free): abcsupplychain.com/webinar-sc...
►Introduction ABC Analysis:
00:00 Introduction
00:21 What is ABC Analysis ? Pareto's law and 20/80 rule
02:16 How to use ABC Analysis ?
04:21 ABC Analysis example to optimize your Inventory Management
►Excel Step-by-Step tutorial:
6:39 Items Status and Product Portfolio
7:21 Quantity or Value Classification ?
8:23 Sales history or Sales Forecast ?
9:00 Which period to use ?
10:30 Pivot Table creation
12:16 Product Ranking
13:28 Cumulative Percentage of Items
13:58 Cumulative Percentage of Revenues
15:00 ABC Classification per item
17:45 Graph creation
19:03 Update the classification with new values
20:36 Why ABC Analysis is not enough
▬▬▬▬▬▬▬▬▬▬▬▬▬▬ LINKS ▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬
TOOLS & EXCEL (free): abcsupplychain.com/en/tools-e...
MY BLOG: abcsupplychain.com
#supplychain #logistics #abcanalysis #inventoryabc #inventorymanagement
Пікірлер: 132
If you know enough excel to do everything that he just did here, you can likely land an entry level job as an analyst in inventory/supply mangement. This is an underrated tutorial
Can't thank you enough ! sheer amount of hard work that has gone into making these videos and the wealth of knowledge you are sharing, Thank you!
This is the best tutorial that i've seen on this topic. Great tips, a deep view of the analysis and everything around it with a touch of humour. Perfect. Thank you so much!
Fantastic materials to revisit supply chain principles, thanks for your hard work!
Fantastic tutorial on the ABC analysis and Pareto diagram. Helped me save a day of studying the textbook.
@abcsc
Жыл бұрын
Great to hear!
Perfect and to the point. thank you for sharing the knowledge.
Briliant video & kudos for creating it. Simple demonstration, yet carries much values for day-to-day operations.
@abcsc
Жыл бұрын
Glad you liked it!
Excellent..Simple and clear inventory management...thank you!! Looking forward for new videos.
@abcsc
2 жыл бұрын
Thanks J DO🙏 I am recording a new tutorial right now. Which topic do you want to see on this channel ? Edouard
It is all time a pleasure to watch your video because it is very instructive. Thank you for your good job.
@abcsc
2 жыл бұрын
My pleasure Nabil, more tutorial coming soon 🚀
Big applause from South Korea ! Such a perfect tutorial video for people who study for SCM.
@abcsc
2 жыл бұрын
Thanks for being my first South Korean comment 🙌 I will do my best to keep it up 🚀
Thank you very much for your sharing! This is very helpful to me, I expect your coming supply chain videos !
@abcsc
2 жыл бұрын
Fantastic, I am happy that you enjoy my videos, stay tuned as there is more to come :)
Keep sharing videos. I have spent hours to do abc analysis manually and after seeing you video, It took me 5 min
@abcsc
2 жыл бұрын
Glad it helped Ahmed! Yes, I plan to share more ;)
This is a really great video, you have really simplified ABC analysis, thank you.
@abcsc
2 жыл бұрын
Thank you! Yes indeed, "keep it simple" is my mantra ;)
@danielamoakohene882
2 жыл бұрын
@@abcsc wow It's also now my mantra, “keep it simple.”
Thank you very much , the video is absolutely great and informative . Looking forward for more such videos
@abcsc
2 жыл бұрын
Thank you very much for your feedback Sahil!
Ohh man, this video is excellent! It is the best tutorial about ABC analysis, thank you so much!!!!
Excellent guide! Simple & easy to understand. I will comeback for more videos 👍
@InspiredCT5258
2 жыл бұрын
Looking forward for XYZ analysis 🙏
@abcsc
2 жыл бұрын
I am glad you liked it! Much more to come 😉
Thank you, very comprehensive explanation on the video also combine with the free excel template. Merci Edouard
Thanks, easy to follow. Please, continue :)
Great Video. Thank you very much. Looking forward for more videos on inventory management
@abcsc
2 жыл бұрын
Thank you! About Inventory Management, I just released a new video: kzread.info/dash/bejne/omhktdqvh6m3j6Q.html
Very useful and specific video. Thank you so much!
@abcsc
2 жыл бұрын
Glad you enjoyed it! I am recording a new video about ABC XYZ analysis, you will love it ! coming very soon 🚀
Merci édouard tu m’aides beaucoup 👌👌
Thank you Edouard Thieuleux for your kindness. much appreciated.
@abcsc
Жыл бұрын
You are very welcome!
Thank you sir! Very helpful in terms of big picture and technical work as well. Abdullah from Arabia.
Thank you soo much😇 Stay blessed✨ May God Bless you.
@edouardabcsupplychain7781
2 жыл бұрын
Thank you for watching Hira !
Thank you so much.
great video thanks
Subscribed! I wish I could have a template of your Excel. I love that dashboard tab!
Thanks for information and knowledge.
@abcsc
2 жыл бұрын
You're welcome!
It was an excellent explanation of the systemt Thanks!!
@abcsc
Жыл бұрын
Glad it was helpful!
Really excellent videos! Thank you very much, it's been very helpfull! Regarding to the forecast, is there a way to forecast for several items at once in excel? Or is it a manually task, where one has to pick up a forecast for each item time series and then aggregate it in a table like in the Data sheet?
@abcsc
2 жыл бұрын
Hello João, I am glad it was helpful for you ! Yes, you can forecast for several items at once. I would say that it doesn't matter if you have several forecasts that are family or items specific, or just one aggregated forecast, as long as you can get one table with all your items and forecasts that you will use for your classification. If you want to learn more about forecasting in Excel, you can check my video : kzread.info/dash/bejne/mq1h18Gso5q9pZs.html Have a great day
Excellent, you are a Master
@abcsc
2 жыл бұрын
Thank you so much, glad you like the video :)
Thank you so much!!!
@abcsc
2 жыл бұрын
You're welcome!
Thanks so much for this video :)
@abcsc
Жыл бұрын
You are so welcome!
Amazing....
Brilliant! Thanks so much. If u could give some tutorials for how to do forecasting, it will be greatly appreciated
@abcsc
Жыл бұрын
You're welcome! Yes it is coming, stay tuned ;)
@xiaojienan7891
Жыл бұрын
@@abcsc one question! Regarding unit price in order to work out ABC by value, in general it will be mixed sales + forecast . 1, if forecast $, we can use cheapest price as worse scenario; 2, if actual sales $ (invoiced price), which unit price should we use as one item usually has different price due to multi different discount for different customers? Thanks
Thank you!
@abcsc
2 жыл бұрын
You're welcome!
Great !!!!
Really it was very helpful.
@abcsc
2 жыл бұрын
Glad to hear that!
superb
Really helpful, Thank you. I have a question regarding the cumulative percentage volume calculation. If I follow exactly like you did, the percentage values goes above 100%.
@abcsc
Жыл бұрын
Hi, thank you for your feedback. Well l am not sure I can help without seeing your sheet, double check your formulas ;)
@abcsc
Жыл бұрын
You're welcome!
great
Can't thank you enouph for that valuable information. I have a question about ABC Model. Can this model can be executed in the level of components and materials?
Thank you very much, Very Excellent Explanation. Actually, I'm dealing with retails with more than 15K items and more than 80 category, how can I differentiate the Percentages of ABC for each category or is it fine to have it all with the same percentage, is there any rule to specify the percentage??
@abcsc
2 жыл бұрын
Hi Sereen, the same applies here: just make your ABC Analysis with 15K products (here I did it with 500 products). You could also make an ABC Analysis with 50 Category to see which ones are the most important (so you must sum the items' sales for each category).
Awesome Very Informative Sir🙏 Kindly provide example for Hospital Pharmacy too
@abcsc
2 жыл бұрын
Thank you very much for your feedback! The analysis is the same for pharmaceutical products ;)
@lavkeshdubey8147
2 жыл бұрын
@@abcsc but sir in hospital industry we take A class item upto 70% value on the basis of consumption
Hi first of all thank you for your video and explanation normally this is the same as the 20 80 rule if I'm not wrong but you do use 40% a A class, could you tell why ?
@abcsc
2 жыл бұрын
Hi Bilal. Yes indeed you can also do it with a 40/60 as shown in the video :) As you wish. Its all about grouping your products: in my example, the first 0.5%-1% of the products represent 10% of the revenue! So it was usefull to seperate A and B class at 40% and not 80%, to better identify the first class products that are critical
Hi, if in a Import itens company, working with Long LeadTimes (120days) how frequently would you make the ABC analysis?
Wow. I'm a Supply Chain Management student. I would want to know all the tool for inventory and the Supply Chain Management as a whole.
@abcsc
2 жыл бұрын
Then watch my videos and articles 😜
Can I use mark-up price*quantity for each product instead of revenue?
Hei, can you help with tips how to make ABC analyse without costs? I want to know only how often some produkt has been picked ? which one is most frequent ?
Can we use 10-20-70 rule for ABC analysis instead of 20-80.??
Hi Edouard, is it possible to use your stock(how many products leave the warehouse) instead of revenue? Thanks for the video.
@abcsc
2 жыл бұрын
Hi Sten. Yes absolutely, usually for the warehouse ABC analysis, we use the volumes that leave (or pick) the warehouse in quantity to optimize flows and productivity. I could include this example, but the video was already too long for an introduction ;-) Do you want to see more details about ABC Analysis ?
@stenborgers1120
2 жыл бұрын
@@abcsc Thanks for the response. Im currently using this method to rearrange a company's warehouse. Im interested in more details if you have them.
Hi, how do you get the table Revenue Index 40, 80 and >80? Where can you find out in the corporation? Finance department or Sales Department?
@abcsc
Жыл бұрын
Hi Michael! Well if you want to get the sales excel files, yes i guess ask to the finance department :)
Starting at 13:30 - to get %item by dividing the rank / Nb of items doesn't make sense to me (=A4/E2). Divide total count of each item/Nb of (All) Items is what I know to get %item. Rank (an ordinal type, not numerical for division) was based on12 month Sales Volume. Am I missing something? Thanks for sharing.
@abcsc
2 жыл бұрын
Good Question ! As I pointed out in the video, we want the CUMULATIVE percentage item. Not the percentage of one item over the rest :) We get the cumulative percentage by starting with the first ranked items (A codes) and ending by the last ones. Thus, we can easily identify the 20/80 rule on the graph.
I am a new subscriber.. I am working in a spareparts warehouse,am wondering how can I use this in this kind of warehouse. Just a newbie. Hoping for a kind reply.thank u
so we will not also have to calculate susceptibility to theft in the abc analysis?
Hey! You mention your inventory management course in the video. Where can I find it?
@abcsc
2 жыл бұрын
Hi Christian ! It is still under development in english. But you can join the waiting list here : abcsupplychain.com/inventory-management-course/
how do you use ABC Analysis، Can i use it for stock classification for inventory counting?🙏 thanx alot
@abcsc
2 жыл бұрын
My pleasure Abd. I would definitely use ABC Analysis for inventory counting. I have been using it for many years with my warehouses and factories. You have 2 options : 1) ABC Classification based on the volumes of sales/movements/shipping 2) ABC Classification based on the volume of stock I would recommend the first option, and make sure you have more frequent inventory counting for A and B codes. WHY ? To guarantee the availability and inventory accuracy for your most strategic products. One of the main reason of poor service level is the incorrect information of available inventory. Your ERP/WMS thinks you have stock available, but in reality you don't and there is no automatic replenishment that can causes long term out of stock. Good luck with your inventory counting, hope you get more than 99% accuracy ;-) Edouard
@moosth84
2 жыл бұрын
@@abcsc Thank you sir, your advice 100% fullfilled my quastion. 😇
will you do a follow up video with XYZ?
@abcsc
2 жыл бұрын
I will ! ;)
Iam struggling with this because I have more than one item of the same item ( e.g. I have 27 of item 1. And 24 of item 2!! This misleads the ABC grouping. Should I separate these by creating the same row 27 times?
Would you be able to explain why at the ABC table the revenue is classified as 40% for A, 80% for B and 80+% for C? Is that normal or does it depend on the company and products?
@abcsc
5 ай бұрын
Hi, you can choose the percentages you want. It's all about grouping your products: in my example, the first 0.5%-1% of the products represent 10% of the revenue! So it was usefull to seperate A and B items at 40% and not 80%, to better identify the first class products that are critical
Is it possible to make a macro for the analysis or it won't work properly?
@abcsc
2 жыл бұрын
Hi! Of course it is possible, but this level of complexity is not worth it in my opinion. You can do it with simple formulas as shown in the video
@artemfedchyshyn7177
2 жыл бұрын
@@abcsc okay, thanks!
thankyou but i cant undrestand why u give A code for less than 40% isnt it reverse?bc A code has the most part of revenue and should have more than 80% in formula? the second question is why u cumulate the percentages?itemes are diffrenet products not the same arent they? i appreciate if u answer clearly thanks for it❤
what if I have one item that dominates more than 40% of the sales? By this definition that item will be in B category whilst in reality that's an A level product because of the contribution for the sales.
@abcsc
Жыл бұрын
Hi Irman! Well just raise the limit above 40% so your item is a A code ;) 40% is not a general rule
thanks for sharing this knowledge
i have a newbie question, im sorry but i dont understand, why did you take 40% revenue i thought A is around 70-80% contribution based on the value?
where can I view the excel template shown in 20:03? Can't seem to find it anywhere.
@abcsc
2 жыл бұрын
This is from my Inventory Management course: abcsupplychain.com/inventory-management-course/
How do I calculate perfect order rate??
@abcsc
2 жыл бұрын
Hi Michael! The perfect order is the order delivered at the right time, quantity and quality. If you want to know more about KPIs, I'll have soon a MasterClass about the best KPIs and dashboards for your Supply Chain, you can register here: abcsupplychain.com/webinar-scm-metrics/
it's funny when i remember how much i did spend time to do gather the data for my ABC analysis for my end-of-study project while you have sum it up all in a 21 video xD .. why 40% and not 20% ?
@abcsc
2 жыл бұрын
Thanks Nosy, I tried to keep it as simple as possible with example ;-) Why 40% ? it's the common distribution we find for most of the company. 5% of your product/action/services represent 40% of your sales/results ... It can vary a bit. In my examples it's 7%, but it also depends if you include inactive or discontinued product in your ABC Classification. Take care, Edouard
But doesn't situations appear where the company wants the same service level, lets say 98% for all categories a,b and c? How do you handle that situation ?
@abcsc
4 ай бұрын
I talk more about setting service level targets in my ABC XYZ video: kzread.info/dash/bejne/X3uju6twZJnRdbw.html Targeting high service levels, no matter the category, assumes you focus exclusively on service levels and not on inventory costs. This kind of supply chain strategy typically prioritizes customer satisfaction, responsiveness, and flexibility above cost minimization. E.g.: High-end Luxury Goods where the cost of not meeting customer demand can be very high in terms of lost sales and diminished brand loyalty. Or Spare Parts and Maintenance where the cost of machinery downtime can be significantly higher than the cost of carrying extra inventory. But for most companies, you need to find the right balance between service levels and inventory levels. Again, Check my ABC XYZ video where I talk about it: kzread.info/dash/bejne/X3uju6twZJnRdbw.html
Hello, I am having a problem drilling down the formula. Each row contains an "A" with no B or C
@abcsc
4 ай бұрын
Hi Bill, Thanks for you comment. This topic is covered in our free Inventory management course. You can join with this link : abcsupplychain.com/inventory-management-course/#minitraining
I wonder y the % of revenue need to accumulate?
Can we have the Excell dear?
@abcsc
2 жыл бұрын
All the links are in the description of the video. abcsupplychain.com/download-excel-abc-analysis/
Why can't we download the practice file?
@abcsc
2 жыл бұрын
Yes you can, the link is in the description
Why the revenue not the profit ?
@abcsc
2 жыл бұрын
You can also do it with the profit (products' margin) 😉
My university just copied this exercise one-to-one, didn't even change the numbers, and claimed it's a new database for VW. This is feel like straight up plagiarism
You explain way too fast...
@abcsc
2 жыл бұрын
I'll try to go slower for my next videos :) don't hesitate to read my article to go at your own rythm: abcsupplychain.com/abc-analysis/
Thanks i have some questions can i get your email??