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.

**Did you like the spreadsheet? Has the analysis of a spreadsheet with ABC curve in excel already helped you to control your stock and make correct investments? Leave your comments regarding the experience with this spreadsheet.**

See also the Inventory Control Worksheet from light!

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

ah right, thanks again !!!

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

I did not know what to do with 1: $ B $ 2: $ B $ 11; B16) /CONT.VALORES ($ B $ 2: $ B $ 11), I saw this in a video in yotube rs, it worked, but the total costs column did so, did not give 70-20-10, gave approximate values, see figure

https://uploads.disquscdn.com/images/8b10249e540ac564b13a49023f07fe8b079a5d76c37dfde65a564a7229dd1f71.jpg

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 ...

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?

https://uploads.disquscdn.com/images/fa01de66f01c002ae9ac3e0c5f4a1a1ce780c16cffce7331d40266a2457907c5.jpg

Thank you very much in advance!

Fair, good tip Thiago!

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

Hi Madson, normally the SUBTOTAL function works for these cases

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.

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

Thank you

Just make a CONT.SE linked to the ABC curve group that it is linked to

good afternoon,

how do I get the values in the last column of the second table,% total products?

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

How do you calculate the last table to do the ABC separation ???????

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/

The spreadsheets are really cool, and the way to do it… too bad I'm not so good with excel…