Use of ABC analysis allows you to apply various management methods to business indicators depending on their contribution to the overall outcome.
Fincontrollex® ABC Analysis Tool add-in allows you to perform professional ABC analysis in Microsoft Excel with just a few clicks.
ABC analysis in Microsoft Excel
At first glance the ABC analysis in Microsoft Excel is quite easy to perform. You should sort out the values, calculate the accumulative portions and, adhering to the Pareto principle, determine the class for each item to be included in.
As the Pareto principle uses the 20:80 ratio (“20% of items yield 80% of the outcome, and the remaining 80% of the items, only 20% of the outcome”), this ratio is very often used as the limit value for ABC classes.
- Class ‘A’: 0 to 80% of the outcome
- Class ‘B’: 80% to 95% of the outcome
- Class ‘C’: 95% to 100% of the outcome
However, when used literally, the 20:80 ratio (discovered by Pareto) of dividing into classes almost always causes incorrect results of ABC analysis. So what is the cause?
Why the 20:80 ratio should not be used
The problem is that the Pareto principle represents just the result of a statistical observation, which cannot be considered as the law of nature with specifically set numerical parameters.
The essence of this principle is that most of the outcomes in the surrounding world are achieved due to a very small amount of effort, and the 20:80 ratio used to explain this principle is just for illustration. In reality, distribution of contribution made by the greater and lesser portions of items is whatever possible, and it will not necessarily equal 20:80.
This means that using the 20:80 ratio as a method to group classes will not show the real extent of item impacts and will result in misrepresented outcomes of ABC analysis. But if the 20:80 ratio does not show the real picture, which method should be used to group ABC classes? In practice, one of two methods is applied for class identification: the empirical method and calculative method.
In order to identify classes with the empirical method, the values should be sorted from the greatest to the least, then the accumulative portions of items calculated by amount and quantity. Based on these data the Pareto Diagram can be plotted.
After that, using the appearance of the curve on the diagram, you should visually determine the boundaries for item classes by the extent of their contribution to the overall outcome. For example, the diagram on the picture shows that the boundary of ‘A’ class will match to approximately 92% of revenue. By means of these boundaries you may now identify the class for each item.
Thereafter, the spread of values should be studied in each class. If the minimum and maximum values in the class differ by one or more order of magnitude, then the boundaries of the class should be shifted in such a way to decrease this spread. This will allow for more precise identification of the class boundaries.
There are a few calculative methods for identifying class boundaries. However, the most flexible is the tangent method proposed by Lukinskiy V.S. in “The Models and Methods of Logistics Theory”. The essence of the method is to identify the class boundaries by bend points of the Pareto curve. This method allows for precise identification of the class boundaries based on alteration of growth rate for the amount and number of items.
In terms of implementation in Excel, this method is more complicated than the empirical one. It requires more calculations to be carried out. Moreover, this method cannot be fully computed in Excel without using macros. The tangent method is well described in the network and for more details you can easily find it.
Fincontrollex® ABC Analysis Tool
Given the difficulty in identifying classes, the professionally performed ABC-analysis in Excel now does not seem to be so simple and easy, and it requires more knowledge and effort to be put in by the performer. Fincontrollex® ABC Analysis Tool add-in allows you to compute this process completely!
With the add-in you can easily and professionally perform ABC analysis by altering the manual empirical method with the computed tangent method.
For example, with the computed tangent method you can identify class boundaries and then change the number of classes manually.
Should your data require division into more than three ABC classes, you can easily add the required number of classes using the context menu with no need to change the Excel Book.
The app interface contains all necessary information for professional work: with the Pareto diagram you can see the contribution of each class, and control the class items with the table of class features.
In manual mode you can adjust the class boundaries right in the table of class features in the “Total Value, %” column, and all changes will be immediately shown on the Pareto Diagram.
After configuring the ABC classes, click the “Perform” button, and the add-in will perform ABC analysis and generate a report in a new workbook. The report will consist of two sheets: summary data, which includes all data from the app interface (including the Pareto Diagram) and detailed data for each item.
In order to make sure that Fincontrollex® ABC Analysis Tool is effective, download a free-of-charge a trial version right now. Before installation we recommend you look through the license agreement, installation manual and reference manual.
Trial version limitations
The trial version has a limited expiration of 30 days.
- — Validity period — 30 days
- — The watermark on a Pareto chart Fincontrollex® ABC Analysis Tool
- — Windows 7 - 10
- — Microsoft Excel 2010 (or later) for Windows
- — .Net Framework 4.6.2 (or later).
The rest of the system requirements can be found on the Microsoft website.
Attention! The add-in does not work on Mac computers and in earlier versions of Microsoft Excel for Windows.