ABC Analysis : Example in Excel with 500 products

In this post, I introduce you to the ABC Analysis and the Pareto principle that you can use to optimize your inventory management and logistics.

(ABC Analysis video example / Please activate the automatics subtitle in English) 

Many people have the feeling that you have to be an excel or data specialist to be able to analyze your supply chain and thus improve and reduce your inventory management. There are indeed very advanced methods that require particular expertise, often costly, but which allow positive results to be obtained.

But there are also many very simple and affordable ways for evaluating and better managing stocks, but these remain little or unknown despite their proven effectiveness.

Pareto Principle

“80% of the results are produced by 20% of the causes

Vilfredo Pareto

Vilfredo Pareto, 19th-century Italian economist.

  • 20% of the population owns 80% of the wealth –
  • You spend 80% of your time with 20% of your friends.
  • 20% of your customers represent 80% of your turnover.
  • 20% of your suppliers represent 80% of your purchases.
  • 20% of your products represent 80% of your turnover.
  • 20% of your products represent 80% of your stock.

What we must remember from the Pareto rule is that 20% of our actions bring 80% of our results. In our case, 20% of our products represent 80% of our sales and turnover. One of the classic mistakes is to focus not on this 20% but on the remaining 80%, which only represents 20% of our business. This is what it looks like in the form of a graph:

Pareto Curve – ABC Analysis realized from store data

It is clear at first sight that 5% of the products sold represent 40% of the turnover and 20% of the products sold therefore represent our famous 80% of turnover.

The same applies to the value of your stocks:

ABC-Sales-Pareto-system

Pareto curve – ABC method for your sales

ABC Analysis of Excel: an example in 5 steps

1) Get your history and forecasts

First, get your sales history and forecasts and classify them by month and references. If you don’t have any forecasts, try to make them or focus only on your history. I highly recommend you to use volume in value rather than in quantity. Why? If you sell millions of plastic bags for your business, for example, they represent a significant quantity but have little or no value.

So take your historical and forecast data by reference and future data rather than past data.

For example, I usually select 3 months of past history and 9 months of forecast data. Why? Why? Simply in case you have new products to introduce, this will allow you to take them into account and focus on them more easily. If you don’t have any forecasts again, you can add up the last 6 or 12 months.

2) Sort the products

Secondly, we will try to sort our products in a decreasing way in terms of turnover over the selected 12 months.

If you don’t know how to create one, watch our video on Youtube here.

Select your tables to the bottom of your Excel so you don’t have to modify your pivot table when you introduce new articles. Then select in the following order: The item codes, the item description, and a 12-month column. Display your table in tabular form and delete all totals to obtain a clear and easy-to-read result. Then click on the additional sorting option and select your descending sorting on your 12-month sum. Simply identify them as product 1, 2… and so on.

3) Enter the cumulative turnover percentages

Add now the item percentage and the turnover percentage, i.e. those they represent in your sales and turnover in %.

For articles, enter the formula “NB” (number) which provides the total number of items. Then divide the articles by the total number of articles

Similarly, for the turnover: the turnover generated by a product is divided by the global turnover

Drag and drop by adding to each the previous percentage to obtain the cumulative percentage of articles in relation to the turnover.

4) Set up the ABC Analysis in Excel

Set up the ABC codes: In the last column, insert the formula “IF” (cumulative turnover percentage < 40%, then it is an A code, If cumulative turnover percentage > 80%, then it is a C code, If neither of the two, it is a B code). The details of this formula are explained in the video and vary according to your data.

For more visibility, you can include conditional formatting colors for every different code, then you can search for your references according to their code using VLookup functions on Excel.

To get the graph, select your table, and insert your curve in the insertion tab.

5) Create your Pareto Curve

In the end, we obtain a curve and an ABC distribution very close to the distribution of the Pareto curve.

Please note that your Excel must be updated at least once a week, especially if you work in procurement. A common mistake is not spending enough time on this ABC classification, which represents a considerable saving of time and money.

Download the Excel ABC Analysis

You can download the Excel ABC Method presented above with 500 articles on this link

Go Further: ABC XYZ Analyse

One of the defects of the ABC method (each tool has its qualities and defects) is that it does not take into account the volatility of sales or consumption. It is then necessary to turn to an ABC XYZ classification.

The ABC XYZ classification thus provides a framework for developing and refining inventory management strategies by classifying products according to their sales volume (ABC) but also according to their uncertainties (XYZ). Your procurement strategy will then be different according to this new classification to optimize your stock and customer service rate.

ABC-XYZ-Matrice

You can read our article on the ABC XYZ classification here

Scroll to Top