ABC analysis for inventory management in MS Excel and Python from scratch

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.