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

 

[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 Richard Koch (Author)


 

Muddassir