Learn how the ABC curve helps in decision-making through prioritization
The ABC Curve is an ally in efficient decision-making. Understand where it is used and how to generate it using Excel.
Looking for development in your professional career?
What do you think of becoming a reference professional in the market through one of the most used methodologies in the world?
Our Black Belt training in Lean Six Sigma is the next step towards your growth and recognition!
With our Black Belt certification, you will learn how to manage projects, increase results, lead teams, and the main Lean Six Sigma tools for solving highly complex corporate problems in search of strategic competitiveness.
Don't waste time! Develop the knowledge and skills that will make you a reference professional in the market!
How does the ABC curve help control a company's inventory?
The ABC curve helps in efficient inventory control. Its analysis is important because if class A products are missing, it means that the company is losing money.
Thus, it is important for the company that class A products that have higher revenue when classified in this way by Curva ABC are never lacking, because they will always be the priority for sale.
In addition, with the analysis of the ABC curve also indicating which Class C products are, for example, because they are products of low demand and low price, they can be driven through promotions so that they do not stand still in stock, avoiding waste of space and costs.
But now let's go to practice! How to make an ABC curve?
How to make an ABC curve in Excel?
Now let's understand the step by step to create an ABC curve in Excel:
1. For the creation of the ABC Curve, we need to assemble a matrix. The following is a way to mount 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 accumulated value of the products:
4. After calculating the accumulated total, organize the products in descending order 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 "From Largest to Lowest". Then click on "OK". You will notice that all products will be organized with the highest total value for the lowest:
6. Next, it is time to calculate the percentages for each product and the accumulated percentage to generate the ABC curve graph:
7. Next, the items should be divided into classes "A", "B" and "C". In this example, 75% was adopted for A, 95% for B and 100% for C. Create a class column and put an SE function to classify the products as class "A", "B" or "C".
Okay, now you can see which class each product fits into. If you prefer, you can still add conditional formatting on the class cells, which change the cell color according to the class.
After that, your spreadsheet should be as follows:
8. After defining zones "A", "B" and "C", create an ABC column that will be used to view the ABC region on the chart. Create the ABC column adjacent to the CLASS column and, in the first row, add the formula to count how many items of A, B and C.
9. Now, select the ABC column and create a Bar Chart.
Click on the generated chart and two new tabs will appear, click DESIGN>Change Chart Type>Bars>100% Stacked Bars. Select the chart on the right and click OK.
Now double-click on the chart and will appear in the formatting data series window, 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 on Series Values and select the column of % Accumulated and click OK and OK.
Check if the graph has changed. If yes, click again on the chart and then on the Design tab. Go to Change Chart Type, this way a new window will open. In it, click Combo and look for the last created series, referring to % accumulated check the Secondary Axis box and then under Chart Type. Select Line Chart and give OK.
Okay, now your chart is created. To look better, change the colors of each zone so that they are the same colors as the class column.
To do this, just double-click on one of the zones and, in the window that will open, click on the paint bucket then fill it out. Click on the other bucket of paint and select the color for the chosen area. Do the same procedure for others.
In this graph, the zones are represented by the colors:
- Yellow - Zone A
- Green - Zone B
- Orange - Zone C
Let's change to:
- Green - Zone A
- Blue - Zone B
- Red - Zone C
10. If you did the previous procedure correctly, your chart must have looked similar to the image below. Now, put the Graphic Title and the Axis Title, as shown in the figure.
Finally, your ABC curve is ready!
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 the revenue of this company representing 74.82%, and they should always be in stock.
Products 11, 14, 05, 09, 10, 15 and 13 represent 19.82% of revenue and the other products represent only 5.36%.
In this case, it is concluded that the items should be prioritized according to their respective classes, with Class A items being the most priority and that Class C items should not be missing, since Class C items may be missing, because the value they represent in revenue is low, compared to class A and B.
Ready to apply this tool?
Leverage results with ebook the 7 Quality Tools!
With this eBook, you'll learn how to use tools that will help you improve quality in any company:
• Flowchart
• Control Letters
• Ishikawa diagram
• Check Sheet
• Histogram
• Scatter Graph
• Pareto's chart
In it, you will also find definitions of the concept of Quality in the view of today's gurus. Plus a surprise that we prepared for you. A kit with DMAIC tools, to help you even more at the time of application. Do not miss this opportunity and download this material right now.
