In supply chain most of us has been performing ABC inventory analysis for many years and in many different ways. This method has served us well and gives us several advantages. However, most supply chain analyst perform ABC analysis on value as you can see in this Wikipedia article and YouTube video.
Get Excess and Obsolete Inventory Policy
Over the years I have developed an algorithm to perform ABC inventory analysis which is based on Value (£ or $ ), Volume (Qty/Units) and Frequency (number of lines) of sales. I have performed this analysis in many different business and plants I’ve worked with and it has given great results. With the help of my programmer friend, I have developed this algorithm into FREE ABC Analysis Tool . There is no complicated logic; it is simply a 80-20 rule (Pareto) of Value, Volume and Frequency of sales.
Now you have two choices, simply go to the ABC Inventory Analysis tool here or continue reading the blog to understand how the logic works for ABC Inventory Analysis tool. You should get same results by performing manually which will take hours & days depends on number of SKUs & data or less than couple of minutes, with the ABC Inventory Analysis tool if instruction followed correctly! Make sure you use “Item Number, Quantity and Value” in first raw and don’t use any currency symbol for values, just numbers, as shown below. Download the Sample file to see example and follow format!
Steps to performance ABC Inventory Analysis Manually.
Step 1: Download last 12 month sales data.
Download the sales data for last 12 month showing, Item Number, Sales Value, Qty Sold, Cost Value, Margin etc as shown in below example.
Step 2: List sum of sold items in last 12 month as highest to lowest by Value:
List the sum of sold items per SKU in last 12 month as highest to lowest by sales Value and define each Item Number as total percent of sales. Tick the items which fall under 80% of total sales as “x”. For example the below list of 19 Items numbers shown 39.90% of sales from the total list of 2190 I have analyzed.
Step 3: List sum of sold items in last 12 month as highest to lowest by Quantity
List the sum of sold items per SKU in last 12 month as highest to lowest by Quantity and define each Item Number as total percent of sales. Tick the items which fall under 80% of total sales as “x”. For example the below list of 19 Items numbers shown 57.64% of sales from the total list of 2190 I have analyzed.
Step 4: List sum of sold items in last 12 month as highest to lowest by Frequency
List the sum of sold items per SKU in last 12 month as highest to lowest by Frequency and define each Item Number as total percent of sales. Tick the items which fall under 80% of total sales as “x”. For example the below list of 19 Items numbers shown 22.80% of sales from the total list of 2190 I have analyzed.
Step 5: Consolidate the results in above steps in Master Sheet
Once you list down each Value, Volume and Frequency tabs from Highest to Lowest and identify the Top 80% Items , consolidate in one sheet as below. The items you have marked as “x” and where in Top 80% mark them number “1” for tally. Then…
A – Items fall under Top 80% of value, volume (qty) and frequency of sales, i.e. in all 3 dimensions. These are most valuable items.
B – Items fall under 2 of the 3, Top 80% value, volume and/or frequency, i.e. in any 2 dimensions
C – Items fall under 1 of the 3, Top 80% of value, volume or frequency. i.e. in any 1 dimensions.
Q – Do not fall Top 80% of either value, volume or frequency.
Summary
[Tweet “The disciplined management of ABC inventory analysis process is critical to ensure optimum customer service, inventory levels, and operational efficiency.”] It further helps, to ensure proper communication and coordination of activities to suppliers and customers.
ABC inventory analysis also assists in achieving alignment in top-down objectives and strategies with bottom-up operational plans. It provides a framework to manage product life cycle and most importantly drive key metric improvement, which include Inventory, customer on time delivery, suppliers’ performance to lead time, margins, past due, and logistics cost and so on.
Now you have a choice to make: to do it manually in spreadsheets or use this super-fast tool to achieve the same result in couple of minutes by downloading sales qty in 3 columns: Item Number, Quantity & Value (No currency sign remember!!)
I have also made a vlog on the same topic. This vlog explains how to do ABC Analysis in Inventory Management.
Recommended Reading:
The 80/20 Principle: The Secret to Success by Achieving More with Less
by
Andy
It is great tool, i like.
Syed Suffyan Hasan
Please further explain point number 4, i am facing difficulties to understand point 4 and furthermore and please further explain items which fall under 80% o total sales.
Thank you!
muddassirism
Step 4: List sum of sold items in last 12 month as highest to lowest by Frequency – is same is Step 2 & Step 3. This step is to pivot the Number of lines you have shipped/sold for a specific SKU. For example, you have item XYZ-1 and you ship once a month to one customer every month. So the pivot will say 12 shipments for 12 months data!
And if this 12 shipments are in 80% of your total shipment then it means, it fall under 80% of your total sales lines shipped!
Save your self a time, use a tool looking at sample example. That is why I created!!!!
Anas Iftikhar
Great brother for the efforts. Thumbs up !
muddassirism
You are welcome Anas Iftikhar.
Asid
Let say, if someone is not maintaining frequency data. Only qty sold and sales value. Those skus which goes for both type would be A, the ones which goes for either type would be B and remaining ones would be C. Correct me if I am wrong plz.
muddassirism
Asid- That is logically make sense if you are not maintaining the frequency data. But what I don’t why you are saying you are not maintaining frequency data. If you have qty sold and sales value for whatever SKUs you are selling then, what you have to is just pivot the sale qty or sale value for each SKU for count and you can have Frequency for whatever time period you have collected data!
The ABC-XYZ Inventory Management Model – Align Planning Parameter with Business Goals
[…] common inventory management model to classify items for planning and inventory management is the ABC model. Essentially, the ABC model uses the Pareto rule that states that small percentage of the total […]
How to Develop Traffic Light Inventory Management and Control Report
[…] ABC classification for those MTS items. My experience tells me that A Class items should cover your 80% of Value, Volume and Frequency and if you have them in stock then you can almost guarantee 80% of your On Time Delivery […]
18 Targeted Inventory Reduction Strategies for Supply Chain Professionals
[…] Pareto ABC analysis will help determine which inventory is used most often. In very simple terms A is the inventory […]
Stuart Rosenberg
This is a good tool to assist in the calculations of ABC Analysis. However, as a long time supply chain professional I have been using this three prong approach for a number of years. Value, Quantity and Frequency of Sales for all items in the inventory is the crux of properly using ABC analysis/Pareto’s Law. In fact this three prong approach has been adapted by several major corporations for conducting their cycle counts, which include tolerance levels for all inventory items based upon the three criteria.
CY
It is a good tool and article, easy to use and follow the logic.
For those SKUs are fallen into “Q”, it that means which will not be count until annual full stock take?
Muddassir Ahmed
Hi CY- I would agree with that.
CY
one more question :-
The ABC classification should do by every month as of 12 months sales rolling data???
Muddassir Ahmed
I would suggest people to do ABC analysis every 6 month.
Navez khan
Hi Mudassir
Is this toll still working ? when i try to upload the data it says “Upload is successful but analysis failed”. Any reason
Muddassir Ahmed
Yes, you should use the sample Excel file and copy paste the data. Don’t change the header!