ABC Analysis : Step-by-Step Tutorial in Excel with 500 products
Вставка
- Опубліковано 25 лип 2024
- 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
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.
Great to hear!
Perfect and to the point. thank you for sharing the knowledge.
Big applause from South Korea ! Such a perfect tutorial video for people who study for SCM.
Thanks for being my first South Korean comment 🙌 I will do my best to keep it up 🚀
Excellent..Simple and clear inventory management...thank you!! Looking forward for new videos.
Thanks J DO🙏 I am recording a new tutorial right now. Which topic do you want to see on this channel ?
Edouard
Briliant video & kudos for creating it. Simple demonstration, yet carries much values for day-to-day operations.
Glad you liked it!
It is all time a pleasure to watch your video because it is very instructive.
Thank you for your good job.
My pleasure Nabil, more tutorial coming soon 🚀
Thanks, easy to follow. Please, continue :)
Thank you very much for your sharing! This is very helpful to me, I expect your coming supply chain videos !
Fantastic, I am happy that you enjoy my videos, stay tuned as there is more to come :)
Ohh man, this video is excellent! It is the best tutorial about ABC analysis, thank you so much!!!!
Thank you very much , the video is absolutely great and informative . Looking forward for more such videos
Thank you very much for your feedback Sahil!
Thank you, very comprehensive explanation on the video also combine with the free excel template. Merci Edouard
This is a really great video, you have really simplified ABC analysis, thank you.
Thank you! Yes indeed, "keep it simple" is my mantra ;)
@@abcsc wow
It's also now my mantra, “keep it simple.”
Very useful and specific video. Thank you so much!
Glad you enjoyed it! I am recording a new video about ABC XYZ analysis, you will love it ! coming very soon 🚀
Great Video. Thank you very much. Looking forward for more videos on inventory management
Thank you! About Inventory Management, I just released a new video: ua-cam.com/video/p40SxvWFQ_A/v-deo.html
Merci édouard tu m’aides beaucoup 👌👌
Keep sharing videos.
I have spent hours to do abc analysis manually and after seeing you video, It took me 5 min
Glad it helped Ahmed! Yes, I plan to share more ;)
Excellent guide! Simple & easy to understand. I will comeback for more videos 👍
Looking forward for XYZ analysis 🙏
I am glad you liked it! Much more to come 😉
great video thanks
Thank you so much.
Thank you Edouard Thieuleux for your kindness. much appreciated.
You are very welcome!
Thank you sir! Very helpful in terms of big picture and technical work as well. Abdullah from Arabia.
It was an excellent explanation of the systemt Thanks!!
Glad it was helpful!
Thanks for information and knowledge.
You're welcome!
Thanks so much for this video :)
You are so welcome!
Great !!!!
Amazing....
Thank you so much!!!
You're welcome!
Excellent, you are a Master
Thank you so much, glad you like the video :)
Thank you soo much😇
Stay blessed✨
May God Bless you.
Thank you for watching Hira !
Really it was very helpful.
Glad to hear that!
Thank you!
You're welcome!
Subscribed! I wish I could have a template of your Excel. I love that dashboard tab!
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?
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 : ua-cam.com/video/hy-u_ss7Wu8/v-deo.html
Have a great day
superb
great
Brilliant! Thanks so much. If u could give some tutorials for how to do forecasting, it will be greatly appreciated
You're welcome! Yes it is coming, stay tuned ;)
@@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
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%.
Hi, thank you for your feedback. Well l am not sure I can help without seeing your sheet, double check your formulas ;)
You're welcome!
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??
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).
Hi, if in a Import itens company, working with Long LeadTimes (120days) how frequently would you make the ABC analysis?
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 ?
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
Can I use mark-up price*quantity for each product instead of revenue?
Hi Edouard, is it possible to use your stock(how many products leave the warehouse) instead of revenue? Thanks for the video.
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 ?
@@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.
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?
Awesome Very Informative Sir🙏
Kindly provide example for Hospital Pharmacy too
Thank you very much for your feedback! The analysis is the same for pharmaceutical products ;)
@@abcsc but sir in hospital industry we take A class item upto 70% value on the basis of consumption
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 ?
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?
Hi Michael! Well if you want to get the sales excel files, yes i guess ask to the finance department :)
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.
Then watch my videos and articles 😜
Can we use 10-20-70 rule for ABC analysis instead of 20-80.??
Hey! You mention your inventory management course in the video. Where can I find it?
Hi Christian ! It is still under development in english. But you can join the waiting list here : abcsupplychain.com/inventory-management-course/
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.
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.
so we will not also have to calculate susceptibility to theft in the abc analysis?
how do you use ABC Analysis،
Can i use it for stock classification for inventory counting?🙏 thanx alot
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
@@abcsc
Thank you sir, your advice 100% fullfilled my quastion. 😇
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
will you do a follow up video with XYZ?
I will ! ;)
thanks for sharing this knowledge
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?
Is it possible to make a macro for the analysis or it won't work properly?
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
@@abcsc okay, thanks!
where can I view the excel template shown in 20:03? Can't seem to find it anywhere.
This is from my Inventory Management course: abcsupplychain.com/inventory-management-course/
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?
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
How do I calculate perfect order rate??
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/
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.
Hi Irman! Well just raise the limit above 40% so your item is a A code ;) 40% is not a general rule
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?
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❤
I wonder y the % of revenue need to accumulate?
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 ?
I talk more about setting service level targets in my ABC XYZ video: ua-cam.com/video/-GoYI746kEY/v-deo.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: ua-cam.com/video/-GoYI746kEY/v-deo.html
Can we have the Excell dear?
All the links are in the description of the video.
abcsupplychain.com/download-excel-abc-analysis/
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% ?
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
Why can't we download the practice file?
Yes you can, the link is in the description
Why the revenue not the profit ?
You can also do it with the profit (products' margin) 😉
Hello, I am having a problem drilling down the formula. Each row contains an "A" with no B or C
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
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...
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??