Quickly do ABC analysis in Excel

The key term in the field of management and quality is ABC analysis. In order not to manually conduct such an analysis and not spend a lot of time on it, you can use the tools built into Excel to automate the process. How to do this will be discussed in this article.

What is ABC Analysis in Excel

This is a method of classifying the resources of an enterprise in terms of importance, impact on production processes. ABC-analysis is carried out in order to identify ways to increase sales, primary points from which to build.

Using ABC Analysis

In general terms, this analysis technique can be called one of the components of the Pareto diagram, which clearly allows you to evaluate the effectiveness of the processes taking place at the enterprise. The first letters in the name divide the technique into three categories:

• A – elements, the components of which have 80% of the total specific gravity.
• B – category, the share of which varies from 5-15%.
• C is the remaining elements that make up less than 5% of the specific gravity.

Pay attention! This article will discuss the principles of classical ABC analysis. In practice, the elements can be divided into 4 or 5 groups.

Method 1: analysis using sorting

This method is the easiest way to perform ABC analysis in MS Excel. The general principle of the calculations can be characterized as follows:

1. Select the initial tabular array LMB and in the “Main” section click on the “Sort and filter” button.

2. In the expanded list, click on the line “Custom sorting”.

3. In the displayed sorting menu, in the “Order” field, you must set the value “Descending”, and also check the box “My data contains headers”. After completing these steps, you will need to click on OK at the bottom of the window.

4. Check that all values ​​in the table array are sorted from largest to smallest.

5. Now, an additional column “Specific Gravity” must be added to the original table, in which further calculations will be made according to the ABC analysis. To do this, click on the name of the last RMB column and insert another column by selecting the appropriate option.
6. To calculate the share, it is necessary to divide the amount of revenue from the sale of a particular product by the total revenue from all products. In the first cell of the added column, write the formula “=B2/$B$28” and press “Enter” to confirm. The values ​​in the expression will differ depending on the task. In this algorithm, a specific example is analyzed, shown in the image below.

7. Stretch the formula over the remaining cells of the plate to automatically fill them.

Important! The specific gravity should be displayed as a percentage. Therefore, the general formula must be multiplied by another 100%.

8. Now you need to add a column to the table in which the accumulated share will be calculated. Those. for each subsequent value in the array, the previous number will be added. More details in the screenshot below.

9. Stretch the formula to autocomplete when the first two parameters are calculated in the table array.
10. After performing the above manipulations, the user will only have to group the received data into groups A, B, C in accordance with the analysis being carried out. To do this, you need to add another column called “Group” to the table.
11. Everything that is more than 80% is indicated by the letter A. If the resulting value is in the range from 80% to 95%, then it should be indicated by the letter B. Opposite the remaining elements, the letter C is placed.

12. If necessary, the calculated groups can be highlighted in a different color to highlight them.

Method 2: Using a Complex Formula

You can perform ABC analysis in Microsoft Office Excel without using the sort function, i.e. the user does not have to change the arrangement of rows in the original table array. The second method of accomplishing the task is to use a complex formula. As an example, a table from the first method will be given. The algorithm of actions is divided into the following stages:

1. Create a source table for which you want to make a calculation.
2. Add an additional column called “Group” to the created array according to the above scheme.

3. Use the left mouse button to select the first cell in the created column, and then click on the “Insert function” button located in the program to the left of the line for entering formulas.

4. In the “Function Wizard” window that opens, in the “Category” field, select the “Links and arrays” option from the drop-down list.
5. In the “Select a function” section, double-click on the word “Select”, and then click on OK at the bottom of the window.

6. A menu of arguments for the specified “Select” function will now be displayed. Here, in the lines “Value1”, “Value2” and “Value3”, you must manually enter the elements for the analysis being carried out, i.e. enter the characters A, B, C in each of the fields, respectively.

7. Now let’s move on to filling in the very first line “Index number” in the function arguments window. First you will need to click on the icon icon to the right of the specified line. Then, at the top of the Excel main menu, expand the “Select” function by clicking LMB on the arrow on the right. In the context menu, you will need to select the option “More functions …”.

8. After completing the previous steps, the “Function Wizard” window will open again. This time, in the list of values, select the function “MATCH”, double-click on it with the left mouse button to highlight it, and then click on OK at the bottom of the menu to confirm the action.

9. The argument menu for the MATCH function opens. This is the “Index Number” argument in the “Select” statement. Here in the line “Viewed array” you need to write the expression “{0:0,8:0,95}”, and near the field “Looking for value” click on the icon and again in the same way go to the menu of the function wizard.

10. This time, in the “Category” field, you will need to set the value to “Math”, select the “SUMIS” operator and click on OK at the bottom of the window.

11. Next, the argument window will open for the “SUMIF” function. Here the user needs to put the mouse cursor in the “Range” field, hold down the LMB and select all the values ​​in the “Revenue” column of the original table array.
12. After the range of cells is specified, it remains to press the “F4” button from the keyboard to set the formula to absoluteness by hanging dollar signs on it.
13. In the second line “Criterion” you need to write the combination “”>”&”, and then also select the range of cells in the “Revenue” column of the original plate.

14. Now, in the formula bar at the top of the window, you need to LMB click on the “MATCH” operator to return to the arguments of this function.
15. To the registered information in the Search value field, you must manually add the “+” sign from the keyboard and add absoluteness to the expression. Moreover, you do not need to enclose the character in quotation marks.
16. After completing the previous steps, the entire formula in the “Looking for value” line must be enclosed in brackets, after them put the division icon from the keyboard (/) and click on the icon to the right of the argument field again.

17. In the function selection window in the “Math” category, find the “SUM” operator, select it by pressing the LMB, and then click on OK.

18. Now we fill in the arguments for the newly selected “SUM” operator. Next to the line “Number1” you need to click on the icon and select the column “Revenue” in the source table, indicating its coordinates. You also need to add dollar signs to the expression by pressing the “F4” key from the PC keyboard, and then click on OK.

19. Check result. If the above manipulations are done correctly, then the symbol “A” will appear in the first cell of the added column, characterizing the analysis.

Additional Information! To fill in the remaining columns, the resulting formula will need to be stretched over the entire table array.

Method 3: Using the ABC Analysis Tool add-in

This add-in will only work with Microsoft Office Excel starting with the 2013 version. In this case, the tool must be additionally downloaded from the official website of the developers and installed on your PC by entering the product key. In general, the process of conducting ABC analysis in this tool is as follows:

1. Launch the program from the computer.
2. In the “Name” field, enter the addresses of the table cells for which the analysis is carried out.
3. Choose to automatically run the analysis for a fast process.
4. Check result. The main menu of the software will display a graph divided into subgroups A, B, C.

ABC Analysis – Use Cases

Such an analysis shows the effectiveness of a particular system, shows its strengths and weaknesses. Based on the information received, you can draw up a strategic plan to eliminate deficiencies, highlight important aspects that should be worked on. In the following, examples of the application of ABC analysis in various fields will be given.

In procurement

In this area, a certain number of goods is analyzed, after which, using the ABC method, the required volume of purchases of each type of product is determined.

In sales

Here, ABC analysis allows you to highlight the best-selling products, as well as understand which products are not in demand. It can be excluded from the general assortment of goods in the future.

In digital marketing

There is a ranking, distribution of traffic types according to their contributions to the sale. At the same time, when conducting an ABC analysis, most of the marketing budget goes to group A.

In SEO

In this area, such a procedure allows you to evaluate the frequency and competition of the object under study, look for the right ways to promote it, competently conduct an advertising campaign, distributing products on the Internet, namely: in social networks, on trading floors.

Pay attention! ABC analysis is more appropriate to perform in the field of procurement and sales.

Advantages and disadvantages of ABC analysis

This type of research has several advantages:

• Ease of implementation. ABC analysis can be used in almost any manufacturing process to improve production efficiency. The technique does not require the introduction of large financial costs.
• Efficiency and high work efficiency. ABC-analysis gives accurate results on the current state of affairs, allows project managers to quickly make a decision to improve its efficiency and productivity.
• Minimum execution costs, time optimization.

Not without flaws. There are several of them in this technique:

• Oriented to work with simple elements, unable to group complex objects.
• Comparison of elements is carried out only by their quantitative indicators.
• Serious impact on the efficiency of external economic factors.

Conclusion

Thus, ABC-analysis is the most important characteristic of any production process, it shows the importance of introducing one or another element. The main methods for its implementation in Microsoft Office Excel have been discussed above. It is recommended that you carefully read the information provided in order to understand this topic well.