ABC inventory analysis

Quickest Way to Perform ABC Inventory Analysis- Based on Value, Volume & Frequency of Sales

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.

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!

ABC Analysis- Image 1

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.

ABC Analysis- Image 2

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.

 ABC Analysis- Image 3

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.

ABC Analysis - Image 4

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.

ABC Analysis - Image 5

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.

ABC Analysis- Image 6

 Summary

 

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!!)

 Recommended Reading: 

The 80/20 Principle: The Secret to Success by Achieving More with Less by Richard Koch (Author)


 

9 thoughts on “Quickest Way to Perform ABC Inventory Analysis- Based on Value, Volume & Frequency of Sales

  1. 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!

    1. 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!!!!

  2. 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.

    1. 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!

Leave a Reply

Your email address will not be published. Required fields are marked *