# How to Make ABC Curve in Excel

18
36827

The spreadsheet in Excel with the so-called ABC curve allows the user to make the inventory control your company. Through the data in this spreadsheet, the manager can make a comparison between the items and determine which ones are relevant and which are disposable for increasing the company's revenue.

Do not you know the ABC curve? You do not perform a comparison to control your stock? Know that using this tool is very important to reduce the costs of your business.

The origin of the ABC curve is a method based on theories of the Italian economist Vilfredo Pareto. It is about dividing the items of your stock into 3 groups according to their importance: A, B or C. In group A are the most important items. In group B are the intermediate level items. In group C are the items with low relevance.

## Stock management with the ABC curve in Excel

We elaborated a step by step how to make an ABC curve worksheet in Excel. Follow:

### Creating the 4 First Columns

Step 1: Initially we will create four columns and enter the values. Are they:

• First Column: A1-A11: It is the code of each product in your stock.
• Second Column: B1-B11: Quantity of product in stock.
• Third column: C1-C11: Unit value of each product.
• Fourth Column: D1-D11: Total value of the products.

Step 2: To complete the data in the fourth column, multiply the value of column B with the value of column C for the respective product. For example: To get the value of the D2 column, use the formula = B2 * C2. Therefore, the quantity of products multiplied by the unit value will result in the amount spent in your company for that stock item.

Step 3: After that, put the values ​​in the D2 column in descending order.

Step 4: In sequence, in the D12 cell use the formula = SUM (D2: D11). The value will represent the amount of the amount spent in your company's current inventory.

### Inserting the%

Step 5: Create the% column in cell E1. The values ​​of cells E2 through E11 represent the percentage that each value of Column D (cells D1 to D11) in relation to the value of cell D12.

Use the respective formulas in each cell below:

• Cell E2: = D2 / D12
• Cell E3: = D3 / D12
• Cell E4: = D4 / D12
• Cell E5: = D5 / D12
• Cell E6: = D6 / D12
• Cell E7: = D7 / D12
• Cell E8: = D8 / D12
• Cell E9: = D9 / D12
• Cell E10: = D10 / D12
• Cell E11: = D11 / D12

Step 6: After that, select the E2 cells up to E11 and click Home / Number /%.

Step 7: In sequence, in the E12 cell use the formula = SUM (E2: E11). If the result is 100%, the worksheet is correct until now. Otherwise, check the step-by-step again to notice the error and correct the error.

### Inserting% Accumulated column

Step 8: Create the% Accumulated column in the F1 cell. The values ​​of the F2 cells to F11 represent the sum of the percentages of the% column.

Use the respective formulas in each cell below:

• F2 cell: = E2
• F3 cell: = F2 + E3
• F4 cell: = F3 + E4
• F5 cell: = F4 + E5
• F6 cell: = F5 + E6
• F7 cell: = F6 + E7
• F8 cell: = F7 + E8
• F9 cell: = F8 + E9
• F10 cell: = F9 + E10
• F11 cell: = F10 + E11

Step 9: Once this is done, select cells F2 through F11 and click Home / Number /%.

### Inserting the graph with the ABC curve

Step 10: Select the values ​​of the A2 cells up to A11.

Step 11: Holding down the CTRL key also select the values ​​of the F2 cells to F11.

Step 12: Select the chart: Insert / Graph / Rows / Line 2d / Rows.

Step 13: Select the 1 Series caption and then click DELETE.

Step 14: The percentage axis is between 0 and 120%. To leave between the values ​​of 0 and 100% select the axis and click on Format axis.

Step 15: In axis options, select FIXED for minimum and maximum. Respectively leave them at 0,0 and 1,0. Click CLOSE.

Through these steps the worksheet will be ready. You can change colors and leave it any way you like.

## Analyzing the spreadsheet data with ABC curve

Taking into account 3 groups A, B and C, we chose the reference 70-20-10.

That way group A represents 70% of total costs, group B 20%, and group 10%.

Taking into account the data of this worksheet, the groups are represented:

• GROUP A: 3 products (9, 4 and 10 codes);
• GROUP B: 2 products (5 and 8 codes);
• GROUP C: 5 products (1, 3, 6, 7 and 2 codes).

Therefore:

• 30% of products represent 70% of total costs;
• 20% of products represent 20% of total costs;
• 50% of products represent 10% of total costs.

This data is of fundamental importance for you to maintain a controlled inventory and perform an adequate management for perfect investments.

Administrator and turismologist, passionate about the possibilities that Excel gives to managers, entrepreneurs and students. He is the managing partner of the spreadsheet department at LUZ - Spreadsheets (luz.vc) and is responsible for the development of spreadsheets with business and personal focus commercialized in its website. He also teaches online Excel courses (cursos.luz.vc) and likes to help thousands of planners through the LUZ Forum and Blog.

1. You're welcome, Claudio! Hugs and any other questions I am at your disposal.

2. No problem, they are approximate values. When defining the cuts you will have to make a decision, whether you want to pick up to the last value before the cut values ​​(70 - 20 - 10) or the next value

3. Is Cesar talking, is everything okay? Let's do it by steps:

1 - To make the third column, just take the values ​​of the accumulated percentages (column F), see that in the third product (quantity of type A) we have approximately 70%. To find this value you can do a VLOOKUP (I would create an initial auxiliary column with the numbering of the quantity of products (1, 2, 3, 4, etc.) and use the PRODUCTS column of the auxiliary table to find the corresponding percentage.

2 - The% of total products is even simpler, just make a CONT of the total quantity of products and make a simple division of the quantity of products in a certain group (A, B or C) by this total.

3 - The colors of the lines are a little more complicated. I don't really remember how it was done, but I believe that he inserted three series of data in the graph, each series looking for the accumulated percentage values ​​for each group and then changed the color of each of the lines - particularly I don't think that make the graph more explanatory ...

4. Hi, I have a problem with 70-20, but I can not get the 10-2 reference table. columns, the first one I used the function Cont.se and it worked, already the other columns neither I have idea, and also how to paint the lines of the graph in the same way as in the article?

Thank you very much in advance!

5. it was necessary to place the region below the filled curve to be 10 = v

6. I'm making an ABC curve using an autofilter table, I wonder if you know any formula that calculates the percentage accumulated when the filter is applied.

7. Could you explain how this second table does and how do you determine these colored ABC lines?
Thank you

8. good afternoon,
how do I get the values ​​in the last column of the second table,% total products?

9. Hi Bruno, to do this calculation it is necessary to put the values ​​in descending order in a column and make the accumulated of each successively in another.

So you get what it represents in% and how much the accumulated represents. From there, just think of the percentages and establish the criteria of which groups A, B and C are

10. Hi Mineirinho, how are things good?

If you want to improve your Excel skills, we have two very good online courses that can help you a lot! One is a beginner / intermediate and the other advanced. You can find out more about them (and even see some free classes) at this link - http://cursos.luz.vc/