How to Make ABC Curve in Excel

18
33945
Learn how to make ABC curve in Excel
Excel Spreadsheets

The spreadsheet in Excel with the ABC curve call allows the user to do the stock control of your company. Through the data of this worksheet, the manager can make a comparison between the items and determine which are relevant and those that are disposable to increase 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 worked out a step by step how to make an ABC curve sheet in Excel. Follow:

Creating the 4 First Columns

Learn how to make ABC curve in ExcelStep 1: Initially we will create four columns and enter the values. Are they:

  • First Column: A1-A11: This is the code for each product in your inventory.
  • Second Column: B1-B11: Quantity 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%

Learn how to make ABC curve in ExcelStep 5: Create the% column in the E1 cell. The values ​​of the E2 cells to E11 represent the percentage that each value of Column D (cells D1 to D11) is 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

Learn how to make ABC curve in ExcelStep 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: After that, select the F2 cells up to F11 and click Home / Number /%.

Inserting the graph with the ABC curve

Learn how to make ABC curve in ExcelStep 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.

Learn how to make ABC curve in ExcelStep 13: Select the 1 Series caption and then click DELETE.

Learn how to make ABC curve in ExcelStep 14: The percentage axis is in the values ​​between 0 and 120%. To leave between the values ​​of 0 and 100% select the axis and click on Format axis.

Step 15: Under Axis Options, select FIX to Minimum and Maximum. Respect them in 0,0 and 1,0 values. Click CLOSE.

Learn how to make ABC curve in Excel

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

Learn how to make ABC curve in Excel

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

Learn how to make ABC curve in Excel

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 keep a controlled inventory and perform a proper management for perfect investments.

Did you like the spreadsheet? Does analyzing a spreadsheet with the ABC curve in excel have already helped you control your inventory and make the right investments? Leave your comments about the experience with this worksheet.

Get to know the Inventory Control Worksheet from light!

Excel Spreadsheets

18 COMMENTS

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

  2. No problem being approximate values ​​do not. When defining the cuts you will have to make a decision if you want to pick up 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, simply take the values ​​of the accumulated percentages (column F), see that in the third product (amount of type A) we have approximately 70%. To find this value you can do a PROCV (I would create an auxiliary initial column with the quantity numbering of products (1, 2, 3, 4, etc) and would 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 that total.

    3 - The colors of the lines is a bit more complicated. I do not remember very well how it was done, but I believe he has inserted three sets of data on the chart, each series looking for the values ​​of the cumulative percentages for each group and then modifying the color of each of the lines - particularly I do not think that leave the graphic more explanatory ...

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

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

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

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

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

  9. Hi Mineirinho, how are things good?

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

LEAVE AN ANSWER

Please, write your comment
Please enter your name