ABC analysis as the name shows that it is a technique in inventory management to categorize the overall catalogue of products into 3 classes "A","B" and "C". Categorization "A" relates to the class of most valuable products, "B" being less valuable, and "C" beaing the least valuable.
The criteria of how much a product is valuable is generally determined on the basis of Pareto principle (or 80/20 rule). This rule says that 80% of the effects (in our case 80% of total sales) comes from 20% of the causes (in our case 20% of the products (SKUs)). It is a very simple and effective method that allows inventory managers to take decisions about which SKUs are important to produce first and which SKUs are least important. It has to be noted that in a normal business scenario all orders should have equal importance but when a business reaches a level where it cannot produce according to market demand then managers have to decide which SKUs are generating more sales to the business. The first focus of the managers is to produce the products which generates more sales and then go for less important products with respect to sales.
Implementation in MS Excel
The calculation for the segmentation of ABC products is pretty straight forward. It generally requires quantity and volumes of each SKU sold over a specific period of time. In inventory management, managers usually consider 1 year data but it not a benchmark. In our example, we'll generate some random data for quantity and volumes based on different SKUs. The data looks like the following
We take the above data and make pivots in Excel based on 3 factors. The first factor is the total number of lines. We'll use the =COUNTIF function to count the number of lines of a particular SKU in the data set. This will show that how many times a particular SKU has been ordered over the period of time. Then, for the second and third pivot tables we'll use the =SUMIF function to calculate total quantity for each SKU ordered and total sales it generates over the period of time. See the below implementation
Next we,ll calculate the respective percentage of each SKU with respect to all SKU in all 3 pivots and then calculate the cumulative sum in the next column like below. We'll then see if each SKU (presented in a descending order i.e. sorted by highest to lowest) is greater then our threshold value of 80% or not by making a Decision column. If the SKU value is greater than 80% it lies in the top 80% SKU with respect to either frequency, quantity or volumes, we'll mark the Decision column as 1 otherwise 0.
We'll then merge all the 3 tables by applying =VLOOKUP function on SKU and sum each of the 3 attributes. If the the sum for a specific SKU is equal to 3 then it will be categorized as "A" SKU. Similarly, if the sum is 2 them "B" and if the sum is 3 then "C". We also segmented SKUs as category "Q" whose sum of all 3 factors equals 0 or in other words, those SKUs are not important either in terms of frequency, quantity or value.
Python Implementation
The above process for ABC analysis in Excel is a very easy to implement and understandable to beginners as well. We also have Python implementation below for the same process explained above. We start by loading the data as a dataframe.
After that we simply make 3 pivots by using Pandas and then in the end merge all the 3 tables. Once the merging is done, we'll add the results from each column in the and does the segmentation like we did above by using segmentation method.
The criteria of how much a product is valuable is generally determined on the basis of Pareto principle (or 80/20 rule). This rule says that 80% of the effects (in our case 80% of total sales) comes from 20% of the causes (in our case 20% of the products (SKUs)). It is a very simple and effective method that allows inventory managers to take decisions about which SKUs are important to produce first and which SKUs are least important. It has to be noted that in a normal business scenario all orders should have equal importance but when a business reaches a level where it cannot produce according to market demand then managers have to decide which SKUs are generating more sales to the business. The first focus of the managers is to produce the products which generates more sales and then go for less important products with respect to sales.
Implementation in MS Excel
The calculation for the segmentation of ABC products is pretty straight forward. It generally requires quantity and volumes of each SKU sold over a specific period of time. In inventory management, managers usually consider 1 year data but it not a benchmark. In our example, we'll generate some random data for quantity and volumes based on different SKUs. The data looks like the following
We take the above data and make pivots in Excel based on 3 factors. The first factor is the total number of lines. We'll use the =COUNTIF function to count the number of lines of a particular SKU in the data set. This will show that how many times a particular SKU has been ordered over the period of time. Then, for the second and third pivot tables we'll use the =SUMIF function to calculate total quantity for each SKU ordered and total sales it generates over the period of time. See the below implementation
Next we,ll calculate the respective percentage of each SKU with respect to all SKU in all 3 pivots and then calculate the cumulative sum in the next column like below. We'll then see if each SKU (presented in a descending order i.e. sorted by highest to lowest) is greater then our threshold value of 80% or not by making a Decision column. If the SKU value is greater than 80% it lies in the top 80% SKU with respect to either frequency, quantity or volumes, we'll mark the Decision column as 1 otherwise 0.
We'll then merge all the 3 tables by applying =VLOOKUP function on SKU and sum each of the 3 attributes. If the the sum for a specific SKU is equal to 3 then it will be categorized as "A" SKU. Similarly, if the sum is 2 them "B" and if the sum is 3 then "C". We also segmented SKUs as category "Q" whose sum of all 3 factors equals 0 or in other words, those SKUs are not important either in terms of frequency, quantity or value.
Python Implementation
The above process for ABC analysis in Excel is a very easy to implement and understandable to beginners as well. We also have Python implementation below for the same process explained above. We start by loading the data as a dataframe.
After that we simply make 3 pivots by using Pandas and then in the end merge all the 3 tables. Once the merging is done, we'll add the results from each column in the and does the segmentation like we did above by using segmentation method.
21 comments
Very useful blog, thanks for sharing
ReplyI have bookmarked this article page as i received good information from this.
Best Inventory Management Software in Hyderabad | Best ERP Software in Hyderabad
Cloud Based ERP Software in Hyderabad | Cloud ERP Software
elazığ
Replybilecik
kilis
sakarya
yozgat
N7ZQQ
ankara parça eşya taşıma
Replytakipçi satın al
antalya rent a car
antalya rent a car
ankara parça eşya taşıma
W3PAQ
ACBED
ReplyAğrı Evden Eve Nakliyat
Muğla Lojistik
Urfa Lojistik
Çorum Parça Eşya Taşıma
Karaman Evden Eve Nakliyat
65C44
ReplyKilis Evden Eve Nakliyat
Bayburt Evden Eve Nakliyat
Kocaeli Evden Eve Nakliyat
Binance Güvenilir mi
Tekirdağ Çatı Ustası
F534D
ReplyÜnye Kurtarıcı
Sinop Şehir İçi Nakliyat
Kripto Para Nedir
Tokat Şehir İçi Nakliyat
Ünye Parke Ustası
Erzurum Evden Eve Nakliyat
Rize Evden Eve Nakliyat
Tekirdağ Boya Ustası
Kütahya Parça Eşya Taşıma
8A6CD
ReplyMuş Lojistik
Kripto Para Nedir
Malatya Evden Eve Nakliyat
Giresun Evden Eve Nakliyat
Trabzon Şehir İçi Nakliyat
Trabzon Evden Eve Nakliyat
Çerkezköy Çekici
Ardahan Şehir İçi Nakliyat
Ünye Oto Elektrik
476D8
ReplyÜnye Kurtarıcı
Trabzon Şehirler Arası Nakliyat
Muş Parça Eşya Taşıma
Ünye Evden Eve Nakliyat
Silivri Duşa Kabin Tamiri
Coinex Güvenilir mi
Afyon Evden Eve Nakliyat
Mersin Evden Eve Nakliyat
Artvin Lojistik
1CA92
ReplySilivri Fayans Ustası
Hakkari Evden Eve Nakliyat
Batman Lojistik
Tekirdağ Şehir İçi Nakliyat
Apenft Coin Hangi Borsada
Çorum Lojistik
Aksaray Evden Eve Nakliyat
Elazığ Şehirler Arası Nakliyat
Hatay Şehirler Arası Nakliyat
36781
Reply%20 binance indirim kodu
703F0
ReplyBitcoin Oynama
Bitcoin Kazanma
Bonk Coin Hangi Borsada
Bitcoin Nasıl Alınır
Twitter Takipçi Hilesi
Gate io Borsası Güvenilir mi
Threads Takipçi Satın Al
Bitcoin Para Kazanma
Telegram Görüntüleme Satın Al
F0CBF
ReplyAptos Coin Hangi Borsada
Loop Network Coin Hangi Borsada
Mexc Borsası Güvenilir mi
Soundcloud Dinlenme Hilesi
Kripto Para Çıkarma Siteleri
Gate io Borsası Güvenilir mi
Görüntülü Sohbet
Ergo Coin Hangi Borsada
Baby Doge Coin Hangi Borsada
GBNHCGBHNJGHMK
Replyشركة تسليك مجاري بالخبر
yuhikyuikuo
Replyشركة مكافحة النمل الابيض بالاحساء
شركة تسليك مجاري بالدمام 06CjmYzqVO
Replyشركة تسليك مجاري بابها SfeOlIPOpQ
Replyشركة مكافحة النمل الابيض بالاحساء WgNouMscGG
Replyتسليك مجاري بالهفوف aMMfhIFAav
Replyشركة عزل مواسير المياه بالجبيل tIOkm6VhG1
Replyشركة عزل خزانات بالرس wm884IIY6X
Replyشركة تنظيف مجالس بجازان ivO5sdqCR7
ReplyPost a Comment