Despite the wide use of the ABC analysis in various areas, Microsoft Excel is not able to perform it automatically. Therefore, you will have to work a bit to achieve this task. After reading this article, you will learn how to do an ABC analysis in Excel in just a few clicks.
ABC analysis in Excel
Unfortunately, you cannot find a “Perform ABC analysis” button in Excel. As already mentioned above, Excel just does not support this feature. Therefore, you will have to take the following steps:
- Sort the data in descending order.
- Calculate the cumulative total of each item in the aggregate amount.
- Determine the ABC group boundaries.
- Assign each data item a group name.
- Make a Pareto chart.
If you do not know Excel very well, you will have to look for step-by-step instructions and understand everything for yourself. How much time do you think this will take you? If time is not an issue, then this is the method for you.
But even if you know Excel well and think that performing these steps will not be difficult, you are mistaken. As you know, the devil is in the details, and the bottleneck in ABC analysis is determining the ABC class boundaries.
Why it is difficult to determine the boundaries of the ABC classes
A lot of experts use the 20/80 ratio to determine the boundaries of the classes discovered by Pareto (“20% of the indicators give 80% of the results and the remaining 80% of the indicators give only 20% of the results”). Literally following this principle, the class boundaries are often determined by the following values:
- Class A — from 0% to 80% of the result
- Class B — from 80% to 95% of the result
- Class C — from 95% to 100% of the result
However, this is a gross mistake, which can have severe consequences, for example, as in this story. The thing is that the Pareto principle is just a result of a statistical survey, which cannot be regarded as a law of nature with specifically set numerical parameters. There are no unified values for determining the ABC classes. These values, like the very number of classes, always depend on the data set being analyzed.
The empirical method is most often used in order to determine the number and the boundaries of the classes. The essence of this method consists of a manual selection of the number of classes and their boundaries based on the spread of the minimum and maximum values and the appearance of the curve in the Pareto chart. This is a rather labor-intensive process, which is iterative and always takes up a lot of time. As you can see, even excellent Excel knowledge will not allow you to perform the ABC analysis quickly and accurately. So what method should be used?
The fastest way to perform the ABC analysis in Excel
Fortunately, there is a better way. You should use the Fincontrollex® ABC Analysis Tool add-in for Microsoft Excel if you want to save considerable time and avoid grave mistakes. This add-in is a real savior for users who have to perform ABC analysis in Excel. What are its advantages?
First, the execution speed. You can do an ABC analysis in just a few clicks if you use the add-in. The add-in will perform the ABC analysis and create a professional report with the Pareto chart and it does not matter how much data you have to analyze.
Second, accuracy. Fincontrollex® ABC Analysis Tool is the only add-in for Excel that enables automatic calculation of the ABC class boundaries depending on the data set being analyzed. This means that you will never make a mistake when determining the class boundaries.
Third, flexibility. The add-in enables you to switch between automatic and manual modes for determining the ABC classes, manually change the boundaries of the ABC classes, split and unite the ABC classes, see the spread of the minimum and maximum values, and the number of items and their sum. With all of these options, all changes are immediately displayed in the Pareto chart. This means that you can immediately see the result of the analysis even before the report is generated.
All these advantages make Fincontrollex® ABC Analysis Tool an excellent tool for performing ABC analysis in Excel. In order to verify this, let's see how it works.
It's easy to do an ABC analysis in Excel!
Before proceeding to the example, you need to download and install a free trial version of the Fincontrollex® ABC Analysis Tool. You can read the installation instructions here. It is worth noting that to activate the trial version you will need to register with your email address, and you will receive a message with the activation key.
After installation and activation of the add-in, open the workbook with the input data for the ABC analysis, then go to the fincontrollex.com tab on the Excel ribbon and click the “Analysis” button in the ABC Analysis Tool group. In the Fincontrollex® ABC Analysis Tool window that appears, specify a range with data names, as shown below. Then specify a range with the data values and click the “Perform” button. That's all. The ABC analysis is ready!
Using the Fincontrollex® ABC Analysis Tool enables you to significantly simplify the ABC analysis in Excel. You do not have to create templates, sort data, enter formulas, create a Pareto chart and select the group boundaries - the add-in will do all this work for you. This is the fastest way to perform ABC analysis in Excel.
If you want to master working with Fincontrollex® ABC Analysis Tool (for example, by learning how to set up the group boundaries manually, and unite and split them), then visit the product page.
Free publication of this article is only allowed subject to the integrity of the contents and with a link to the original source. Use of images outside this article is not allowed and is copyright infringement.