ABC analysis is one of the basic tools in the category manager’s arsenal because it helps classify products according to their significance for the business.
If you’re just starting your journey in the commerce field, this article will be your guide to ABC analysis. Read on to learn what problems it solves, why it’s indispensable in category management, and most importantly, how to run it using our Excel example.
Contents
What Is ABC Analysis?
Why Use ABC Analysis?
ABC Analysis in Excel with an Example Calculation
ABC Analysis: A Step-by-Step Tutorial
Challenges in Using ABC Analysis
FAQ
What Is ABC Analysis?
ABC analysis in category management is a method of classifying products by priority based on selected criteria, where A represents high priority, B is medium priority, and C means low priority.
ABC analysis is based on the Pareto principle (the 80/20 rule), which states that 20% of efforts produce 80% of results. In category management, this means that 20% of all products generate 80% of sales.
Why Use ABC Analysis?
The purpose of ABC analysis is to structure the assortment and determine the priority products or categories within it.
By dividing the assortment into segments based on their A/B/C status, you can focus and understand how to achieve a particular KPI, and which tasks need to be started right now. For example, which products or categories can increase turnover and profit or how to optimize inventory.
ABC Analysis in Excel with an Example Calculation
Before we move on to the ABC analysis algorithm, we recommend that you familiarize yourself with the example we prepared in Excel and uploaded to Google Sheets.
The following description of how to perform ABC analysis in Excel is based on a case where the object of analysis is a product, and the criterion is the number of sales. Use the ready-made template and download an example of ABC analysis of the product range.
ABC Analysis: A Step-by-Step Tutorial
Step 1. Set a goal.
Specify the purpose behind conducting the analysis. There are different options:
- optimize the assortment and increase profitability;
- update the supply plan;
- stimulate remnant inventory sales;
- segment products to create special offers for different groups.
Step 2. Select objects for analysis.
These can be products within a category, as well as categories, segments, or brands.
To ensure more accurate results, avoid incorporating new products into the ABC analysis, as there isn’t enough accumulated information about them yet. Also keep in mind that promotional items distort statistics, as they may sell better thanks to special prices. So you need to focus on a wider range of data over time.
Step 3. Define criteria.
The indicators that are usually used for the assortment ABC analysis are:
- number of sales, in units;
- profit, in USD;
- turnover, in USD;
- stock balances, in units;
- advertising expenses, in USD;
- other expenses, in USD.
Choose one of these indicators, the most suitable to the goal of your analysis, as well as the data sample for the relevant period.
Step 4. Use Excel or Google Sheets to enter data.
Enter the values of the calculated parameter (column B).
Step 5. Sort the results in this column from the highest value to the lowest.
Step 6. Calculate the total result.
Step 7. Calculate the share for the first product using the formula:
The share of one product = number of sales of one product / total sales of all products.
In our example, this is column C. The formula will be as follows:
C2 = B2/B$33
Extend the formula to all cells. The total should be 100%.
Step 8. Calculate the cumulative share for each product by adding its share to the share of the previous product.
For the first product on the list, the cumulative share will be the same as in the previous column.
In our example:
D2=C2
And then, cumulatively:
D3=C3+D2
D4=C4+D3
D5=C5+D4 (…)
For the last product on the list, the cumulative share will be 100%.
Step 9. Segment the obtained results into groups.
Group A — focus positions that need to be given more time for deeper analysis based on the set goal. The value of the cumulative share is in the range of 0 to 80%.
Group B — positions with the potential to move into both Group A and Group C. The value of the cumulative share is in the range of 80 to 95%.
Group C — positions that should be eliminated. This may include new items if you are conducting sales ABC analysis because statistics on them have not yet been accumulated. The value of the cumulative share is in the range of 95 to 100%.
How to Apply the Results of the ABC Analysis
After completing the calculations, you can use your final data, for example, to review the inventory situation. To do this, add a column called “Quantity in stock, in units.” Then you can see that, for example, class A items are not in stock based on the quantity sold, while class C items are in excess, and adjust your inventory policy accordingly.
Example of Using Tags A, B, and C in Pricer24
In Pricer24, you can manually assign A, B, and C tags to products and counterparties. For example, our vendor clients use A, B, and C tags to better focus on analyzing the presence of their products on partner shelves. If you have 20 partner stores and three of them account for 80% of sales (meaning these are class A partners), then it is obvious that a stockout in these three stores will affect the brand’s sales overall.
Challenges in Using ABC Analysis
Despite all the advantages of ABC analysis, it is not a magic wand because every business can have specific patterns of customer demand. ABC analysis is based on hindsight: that is, on historical data. But the market can change under the influence of certain factors. Therefore, to build a more accurate forecasting model, you should combine ABC analysis with XYZ analysis, which shows the variability of product demand. Learn more in the ABC/XYZ analysis guide.
In addition, ABC analysis has a number of limitations:
- The standard ABC method does not take into account factors such as the introduction of new products. For example, a new product may have a low sales volume because it does not yet have a purchase history.
- ABC analysis does not take into account seasonal demand. This means that goods can be classified as “A” even if they are only in high demand at certain times of the year.
- ABC analysis determines a product’s importance based on revenue or turnover, but some products may not fit this paradigm. For example, a certain product may rarely sell but may attract many customers (who will buy other products) because of its novelty.
Conclusion
ABC analysis can be very effective as a simple way to focus and prioritize. Especially when you have limited resources: money, time, people, warehouse space, etc.
However, the ABC classification is too simplified because the evaluation criteria are one-dimensional. By prioritizing based on only one indicator, you lose sight of others.
In addition, ABC analysis relies only on historical data, and its results don’t consider market variability. To overcome this inaccuracy, you can implement ABC/XYZ analysis.
FAQ
What is ABC analysis in inventory management?
ABC analysis is a method used in inventory management to classify items into three categories (A, B, and C) based on their importance or value to the business.
What criteria are commonly used for ABC classification in category management?
Usually, products are classified based on criteria such as sales volume, profit, or turnover.
Why is ABC analysis important for inventory planning?
ABC analysis is important because it helps to determine how to group products according to their degree of impact on the overall sales and to determine priorities.
In what other areas of category management can ABC analysis be applied, apart from inventory management?
ABC analysis can be applied to segment customers and suppliers and to analyze the results of marketing campaigns and promotions.
What are some common challenges that category managers may encounter when implementing ABC analysis?
Challenges may include the need for regular updates as market conditions change and potentially ignoring other important factors such as demand fluctuations.
