What is ABC Analysis? Lean how to make one

Have you ever been to the market to buy something and when you got there, you saw that the product was gone? So know that, probably, this product is not very relevant for this market and also that the ABC Analysis is very related to this!

Well, an ABC Curve is widely used with a pattern of priorities in companies.

Interested? Follow this article to learn more!

What is ABC Analysis?

The ABC Analysis is a classification method that allows the ordering of information according to the degree of importance. This facilitates analysis, information processing and decision making.

It establishes an order of priorities, that is, it separates the items with the objective of prioritizing those that add more value to the institution.

The curve is divided into 3 regions (A, B and C), with A being the one with the highest value or quantity, corresponding, in most cases, to 80%, and C - the lowest - corresponding to 5%.

This helps in decision making to define the strategy of which items in stock can have priorities.

Where is it used?

The ABC Analysis is widely used in the administrative environment for the purpose of inventory control and management. But you can also see its use in: sales, pricing and purchasing, production scheduling, among others.

What is the Pareto Principle?

The Pareto Principle was created by Joseph Moses Juran, who named it after Vilfredo Pareto, as he basically realized that 80% of the land in Italy was owned by 20% of the population, and that this repeated itself for other things.

This Pareto analysis gave rise to what would in the future be known as the Pareto Principle, also called the 80/20 Rule: this rule says that 80% of the effects come from 20% of the causes.

However, knowing just 20% is not always enough. Sometimes it is necessary to increase this division to get more details of the information. That is why the ABC curve was created, which separates the Pareto chart into three parts, of which:

• A - 20% of the items represent 80% of the value
• B- 30% of the items represent 15% of the value
• C - 50% of the items represent 5% of the value.

Remember that these values ​​are not fixed and may vary depending on the situation analyzed.

In addition, the ABC Curve can be used to compare, for example, the ABC Sales Curve with the ABC Inventory Curve and thus define efficient inventory control.

Having an efficient stock is all about eliminating waste and continuous improvement, and having these concepts applied in the routines of companies has become an essential factor.

Are you tired of missing out on incredible career opportunities because you don't know about the main Excel tools?

So you are in the right place! In the Excel for Beginners course, you will learn to work with data in an agile way.

In addition, the course is available for FREE, just click on the banner button below and embark on this journey of knowledge!

How does the ABC Analysis help in a company's inventory control?

The ABC curve helps in efficient inventory control. Your analysis is important because if Class A products are in short supply, it means that the company is losing money.

In this way, it is important for the company that class A products, those that have higher revenues, when classified in this way by Curva ABC, are never in short supply, as they will always be those that are priority for sale.

In addition, with the analysis of the ABC Curve also indicating which are the Class C products, for example, as they are low demand and low price products, they can be boosted through promotions so that they do not remain stuck in stock, avoiding wasted space and costs.

But now let's practice! How to make an ABC curve?

How to make an ABC Analysis in Excel?

Now let's understand the step by step to create an ABC Analysis in Excel:

1. To create the ABC curve we need to build a matrix. The following is a way to assemble the matrix in Excel to create the ABC curve:

2. Now we need to calculate the total value of each product. To do this, multiply the quantity by the unit value of each one:

3. And now calculate the total amount value of the products:

4. After calculating the cumulative total, arrange the products in descending order relative to the total value. To do this, select your table and go to the Data tab, click on the Organize tab in which a new window will open:

5. Under "Sort by" select "Total Value" and under "Order" select "Largest to Smallest". Then click "OK". You will notice that all products will be sorted from highest total value to lowest:

6. Next, it is time to calculate the percentages for each product and the accumulated percentage to generate the ABC Analysis graph:

7. Next, you must divide the items into classes "A", "B" and "C". In this example 75% for A, 95% for B and 100% for C was adopted. Create a class column and put an SE function to sort the products as class "A", "B" or "C".

Okay, now you can see which class each product fits into. If you prefer, you can even add conditional formatting on the class cells, which change the cell color according to the class.

Once that's done, your spreadsheet should look like this:

8. After defining the zones "A", "B" and "C", create a column ABC that will be used to visualize the ABC region in the graph. Create column ABC adjacent to column CLASS and in the first row add the formula to count how many items from A, B and C.

9. Now select column ABC and create a Bar Chart.

Click on the generated chart and two new tabs will appear click on DESIGN>Change Chart Type>Bars>100% Stacked Bars. Select the graphic on the right and click OK.

Now double click on the graph the window to format data series will appear, then go to Spacing Width and change the Value to 0 and press ENTER.

Again, go to the DESIGN tab and click Select Data, Add. Click Series Values ​​and select the Accumulated % column and click OK and OK.

Check if the graph has changed. If so, click again on the graphic and then on the Design tab. Go to Change Type of Chart, this way a new window will open. In it, click on Combination and look for the last series created, referring to the accumulated %, check the box of Secondary Axis and then on Type of Chart. Select Line Chart and press OK.

Okay, now your chart is created. To make it look better, change the colors of each zone so that they are the same colors as the class column.

Now you have your ABC analysis

Through this curve and the data analyzed in this example, it is clear that products 01, 03, 12 and 08 are responsible for most of this company's revenue, representing 74.82%, and there must always be stock of them.

Products 11, 14, 05, 09, 10, 15 and 13 represent 19.82% of sales and the other products represent only 5.36%.

In this case, it is concluded that the items must be prioritized according to their respective classes, with Class A items being the highest priority and that they should not be out of stock, whereas Class C items may be lacking, as the value of these represent in the billing is low, if compared to class A and B.

If you made it this far, I have a question for you...

Today one of the most demanded skills in selection processes is the use of Excel. And to give you a good idea of the first steps and first functions, we created the Excel eBook for Beginners.

By downloading our eBook, you will have a step-by-step and practical guide to be able to create your first spreadsheets, and use your most basic and essential functions.

Download it now!