ABC analysis of the assortment. A ready-made example of ABC analysis with a template in Excel. Here are a number of notes that the analyst should keep in mind:
This is a tool that allows you to study the product assortment, determine the rating of products according to the specified criteria and identify that part of the assortment that
which provides maximum effect.
The assortment is usually analyzed according to two parameters: sales volume (quantity sold) and profit received (realized trade margin). ABC analysis is based on the Pareto rule, according to which 20% of assortment items provide 80% of the profit.
Practice shows that 10% of assortment items (group A) account for 80% of turnover; 15% of assortment items (group B) provide 15% of turnover; 75% of assortment items (group C) account for 5% of turnover.
Taking this into account, the entire assortment of a trading enterprise can be divided into groups according to the degree of importance.
- Group A - very important products that should always be present in the assortment. If sales volume was used as a parameter in the analysis, then this group includes the sales leaders by quantity. If trade margin was used as a parameter in the analysis, then this group includes the most profitable products.
- Group B – goods of medium importance.
- Group C - the least important products, these are candidates for exclusion from the assortment and new products.
The first stage of ABC analysis is to identify goals. If the goal is to reduce the assortment, then sales volume and profit are selected as the main parameters. If the goal is to identify and reduce the costs of maintaining inventories, then the turnover ratio, the volume of illiquid stock and the occupied warehouse capacity are selected as the main parameters. If you want to study profitability, then the turnover ratio and the level of profitability are selected as the main parameter. ABC analysis data helps optimize the product range.
With all the many advantages of this type of analysis, there is one significant disadvantage: this method does not allow assessing seasonal fluctuations in demand for goods.
XYZ analysis
XYZ analysis is a tool that allows you to divide products according to the degree of stability of sales and the level of fluctuations in consumption.
The method of this analysis is to calculate the coefficient of variation or consumption fluctuation for each product item. This coefficient shows the deviation of flow rate from the average value and is expressed as a percentage.
The parameter can be: sales volume (quantity), sales amount, amount of realized trade margin. The result of XYZ analysis is the grouping of goods into three categories, based on the stability of their behavior:
- Category X, which includes products with sales fluctuating from 5% to 15%. These are goods characterized by stable consumption levels and a high degree of forecasting.
- Category Y, which includes products with sales fluctuating from 15% to 50%. These are products characterized by seasonal fluctuations and average forecasting capabilities.
- Category Z, which includes products with sales fluctuations of 50% or more. These are goods with irregular consumption and unpredictable fluctuations, therefore, it is impossible to predict their demand.
Combined ABC/XYZ analysis
The combination of ABC and XYZ analyzes reveals the undisputed leaders (group AX) and outsiders (CZ). Both methods complement each other well. If ABC analysis allows you to evaluate the contribution of each product to the sales structure, then XYZ analysis allows you to evaluate sales jumps and its instability. It is recommended to do a combined analysis, where ABC analysis uses two parameters - sales volume and profit.
In total, when conducting such a multivariate combined analysis, 27 product groups are obtained. The results of such an analysis can be used to optimize the assortment, assess the profitability of product groups, evaluate logistics, and evaluate the clients of a wholesale company.
Advantages of combined ABC and XYZ analyzes
Using a combined ABC and XYZ analyzes has a number of significant advantages, which include the following:
- increasing the efficiency of the commodity resource management system;
- increasing the share of highly profitable goods without violating the principles of assortment policy;
- identification of key goods and reasons affecting the number of goods stored in the warehouse;
- redistribution of personnel efforts depending on qualifications and experience.
Formation of ABC- and XYZ-analysis indicators
Before combining the indicators of ABC and XYZ analyses, it is necessary to conduct an ABC analysis of goods by the amount of income received or by the number of products sold for a certain accounting period, for example, a year.
Then an XYZ analysis of these goods is carried out for the same period, for example, by the number of monthly sales for the year. After this, the results are combined.
When combined, nine product groups are determined:
| AX |
BX |
CX |
| AY |
BY |
C.Y. |
| AZ |
BZ |
CZ |
Identification of nine product groups using combined ABC and XYZ analysis
1) Products groups A and B provide the main turnover of the company, so it is necessary to ensure their constant availability.
As a rule, excess safety stock is created for goods of group A, and sufficient safety stock is created for goods of group B.
Using XYZ analysis allows you to more accurately configure the inventory management system and thereby reduce the total inventory.
2) Products groups AX and BX distinguished by high turnover and stability. It is necessary to ensure constant availability of goods, but for this there is no need to create excess safety stock. The consumption of goods in this group is stable and well predicted.
3) Products groups AY and BY with high turnover, they have insufficient stability of consumption, and, as a result, in order to ensure constant availability, it is necessary to increase the safety stock.
4) Products groups AZ and BZ with high turnover, they are characterized by low predictability of consumption. An attempt to ensure guaranteed availability for all goods in a given group only through excess safety inventory will lead to the fact that the company's average inventory will increase significantly. Therefore, the ordering system for products in this group should be revised:
- transfer part of the goods to an ordering system with a constant amount (volume) of the order;
- ensure more frequent deliveries of some goods;
- select suppliers located close to the warehouse, thereby reducing the amount of safety inventory;
- increase the frequency of monitoring;
- entrust work with this group of products to the most experienced manager of the company, etc.
5) Products Group C make up up to 80% of the company's product range. The use of XYZ analysis can greatly reduce the time that a manager spends on managing and monitoring the products of this group
6) By product CX group You can use an ordering system with constant frequency and reduce safety inventory.
7) By product CY groups you can switch to a system with a constant amount (volume) of the order, but at the same time create a safety stock based on the financial capabilities of the company.
8) B CZ product group all new goods, goods of spontaneous demand, supplied to order, etc. are included. Some of these goods can be painlessly removed from the assortment, and the other part needs to be regularly monitored, since it is from the goods of this group that problems arise.
“Greetings, reader of the blog “Diary of a Marketer.” Glad I looked at this page. I hope you find answers to your questions and join the club of regular readers of this blog.”
This article will talk about one of the key tasks that marketers solve, if not constantly, then certainly quite often. Today we’ll talk about conducting an ABC analysis of the assortment. Reader, how often does this happen in your company? Write in the comments.
What is ABC assortment analysis?
ABC assortment analysis is one of the key analyzes that distributes the assortment of any enterprise engaged in trade, ranking it in terms of importance for sales. ABC assortment analysis is based on the Pareto principle, in which 20% of the assortment generates 80% of the organization’s income, and the remaining 80% provides only a fifth of sales.
The classic or standard version of ABC analysis of a company’s assortment is broken down as follows:
- A – leading positions – about 80% of income;
- B – positions relative to the average profitability – 15% of income;
- C – the least profitable positions, accounting for only 5% of sales.
A brief definition of ABC assortment analysis can be given as follows: it is the ranking of key indicators according to certain parameters. Often, the assortment is taken as a basis, but ABC analysis can also be carried out by suppliers, customers, costs, etc.
Why do ABC analysis of the assortment?
The answer, I think, does not need detailed justification. Naturally, ABC analysis is needed in order to figure it out and understand which assortment is in greatest demand among customers and which product should always be kept in stock. And also a clear definition of which products require the least attention from the procurement department.
Very often on the Internet I come across statements that the conclusions obtained during ABC analysis of the assortment are quite primitive, but for more than 70 years this method has been constantly used by marketers and analysts around the world.
History of the origins of ABC analysis
The roots of this method can be found in the 80s of the 19th century. Back in 1883, during a discussion between the greatest economists of the time, F. Walker and G. George, the idea was born to take into account farm taxes based on their share in the total collections. This was the initial premise of ABC analysis.
Only a couple of decades later, Pareto described and proved his 20/80 theory (80% of the result comes from only 20% of the effort), however, this theory could have been buried in the annals of economics and great thought, but Joseph M. Juran began to propagate, and very actively , this principle is in the management system, as well as logistics.
And only in the 50s, ABC assortment analysis acquired a logical basis and was formed as an independent tool for analyzing the assortment in the company. It took world economists and marketers about seventy years to turn the idea expressed into a concrete model for analyzing various data, which is still used today.
An example of an ABC analysis of an assortment?
Before starting an ABC analysis of the assortment, we need to clearly understand the goals and the result that we want to obtain during this analysis. What do we want to see in the end? And based on this, the selection of the data that will be used during the analysis is made.
So let's get started. To get started, we need sales data for the month, quarter or year. In the standard version of ABC analysis of the assortment, it is customary to use one key indicator. In my practice, I strive to always choose two parameters in a spike, for example, revenue and profit or profit and the quantity of goods sold. It all depends on the job responsibilities of the marketer, which were set initially.
Also, from time to time, I slightly change the classic type of analysis and use a different breakdown of categories, namely A-50%, B-35%, C-15%. I usually use this principle if I need to significantly reduce the assortment.
Further. The data on which the ABC analysis is based is ranked from maximum to minimum, and the share of each position in the total is calculated. We fix the shares of positions taking into account the cumulative total. Then the categories of ABC analysis are determined.
Since, as I have already said, in my practice I often use two key parameters, and sometimes even 3, the above actions are carried out several times. Excel in this case is almost irreplaceable; with its help, all this can be accomplished in a short period of time and without much thinking. The resulting categories are combined into one cell (fortunately, Yoksel has such an option). As a result, an almost ideal picture of the world emerges, and to be precise, an ideal ABC analysis of the assortment.
Frequency of ABC analysis of assortment?
I highly recommend conducting an ABC analysis of the entire product range once a quarter. However, in retail trade, from time to time there are tasks of sorting out a product group, so I carry it out on a truncated assortment much more often. And with some preparatory work on creating a template in Excel, I carry out ABC analysis as many times as I need.
Such manipulations allow you to keep your finger on the pulse, tracking changes and quickly taking the necessary measures. If anyone suddenly needs this template, I will be happy to share it (available for download in marketer templates).
I would like to warn you right away that if ABC assortment analysis is used to reduce the assortment, and based on its results, some positions are displayed, then you should not forget three points:
- If, immediately after reducing positions, the ABC analysis is repeated, there will again be positions that can be excluded from the assortment;
- Even if at the moment we get rid of all essentially illiquid positions, after a while the positions that were left in the assortment may not be sold. It is rare to find such examples when category “C” goes into “A”, but the other way around occurs quite often.
- You cannot “cut” the assortment only on the basis of the numbers obtained during this analysis. There is also logic, related types of analysis, for example the same xyz analysis, production necessity, the presence of analogues (even those not sold) and the ability to provide a choice to the client.
This is where I will end this article. I really hope that I was able to clearly convey the essence of one of the key tools - assortment analysis. In the future, it is planned to publish several articles devoted to the methodology of this or that analysis. So, in order not to miss anything interesting, subscribe to blog updates.
To concentrate the main resources of a business on key customers, ABC XYZ analysis is carried out. This is a study of the customer base in two directions: by volume (ABC) and frequency of purchases (XYZ).
What do the letters mean?ABC XYZ:
Group A – clients with the largest volumes of purchases
Group B – counterparties with average purchases
Group C – clients with a small volume of purchases
ABC XYZ analysis allows you to understand:
- Who pays you more and more often than others;
- How many target buyers are there in your ;
- Which customers should you focus your efforts on?
The main objective of ABC XYZ research is to show the company’s priority buyers in order to focus the main efforts on attracting and servicing counterparties who are in the AX category.
ABC XYZ analysis: product line research
ABC XYZ analysis of the current customer base is just one approach. It should be used in conjunction with an analysis of the assortment (inventory) according to the same criteria.
Product line research allows you to create a rating of the “significance” of goods/services. Traditionally, the significance of a product is greater, the higher its performance according to 2 criteria: profit and volume of shipments.
Segmentation of products into these groups can be carried out according to other criteria, depending on the objectives of the study.
2. Reduction of assortment. This is a classic study of purchasing volume and profitability. As a result, goods/services found in categories C or X will be abolished. With the exception of “new products” that have not yet shown themselves.
3. Reducing warehouse maintenance costs. Products are ranked according to turnover rates and occupied warehouse space.
ABC XYZ analysis: buyer migration
But just doing ABC XYZ analysis is not enough. Next, it is important to control the migration of customers from category to category. Those. you need to know how many customers you have in categories A, B, C, and how they move between them. And also how they move along the XYZ axis.
How to track migration in the B2B segment
To stimulate migration to category “A”, you need to measure the share in the client and conduct a survey through telephone calls on 3 important questions:
- How much do they now buy the same product as from you in other companies;
- How much do they buy other types of goods from other companies, and not from you;
- What else do they buy from others that they would like to buy from you.
These 3 questions immediately give you a huge opportunity to expand your share of the client. Record all answers and take them into account when conducting an ABC XYZ analysis. Calculate your share of these customers and analyze how you can increase it.
How to track migration in the B2C segment
If you have a B2C audience, then you have consumption standards for a particular product depending on the average salary.
You can collect a database and see how many customers order based on this data. Based on this, calculate your share in the client and plan how much you can add. And in the future encourage them to buy more.
In the B2C segment, it is also necessary to conduct an ABC XYZ analysis and rank those who previously bought better than now. Find out the reason for the decrease in purchases.
ABC XYZ analysis: holding promotions
ABC XYZanalysis: measuring results
For the B2B segment, when conducting an ABC XYZ analysis, you must fill out a file that contains information about sales plans for each counterparty. Individual plans should be made based on previously made calls/meetings/sent proposals. Then you will have a clear picture.
ABC XYZanalysis: client portfolio
The quality of the client portfolio is the percentage of clients of a certain category to the total mass. It is important to track shares in various categories: by store, by group, by department, by manager.
Let's give an example of changes in portfolio dynamics.
You can ask your clients just a couple of questions:
- How would you rate our work on a scale of 1 to 10?
- What should you do to get 10 points next time?
Based on the answers to the first question, 3 groups of clients are distinguished: promoters (scored 9-10 points), neutrals (7-8 points) and critics (1-6 points).
It is possible that the answers to the second question of respondents from the category of neutral clients will help you formulate a plan for improving processes within the company, service and maintenance. This, in turn, will help the process of customer migration to more profitable categories according to the ABC analysis system.
We looked at the main points of ABC XYZ analysis, what results it gives, and how to work with this data. Conduct ABC XYZ analysis in your company and focus all your resources on attracting and serving AX customers.
ABC analysis- a method that allows you to classify a company's resources (the customer base is a resource) according to their degree of importance. This analysis is one of the methods of rationalization and can be applied to any enterprise and in any field of its activity.
The main goal of ABC analysis of the customer base is to identify in customer segments the groups of customers that provide the greatest contribution (80%) to the company's results in order to focus on best meeting the needs of this particular group.
By filling out the feedback form at the end of the article, you can receive a convenient Excel template for conducting ABC analysis.
definition
What is ABC analysis
At the core ABC analysis lies the Pareto principle: 20/80. When applied to ABC analysis of a customer base, the rule is as follows: 20% of a company’s total customers bring it 80% of its revenue. Another 15% of revenue comes from the next 30% of customers. And 50% of customers (half of the customer base) bring in only 5% of revenue. Depending on the industry and type of business, these numbers may fluctuate a little (there are also extreme cases when a business has only 1-2 clients), but the general statistics are exactly the same.
Evaluating clients based on the level of income generated is the simplest way to evaluate a company's clients, and companies often set up such an assessment in the accounting system. But, assessing only on the basis of the income brought by the client is insufficient. The company should be interested not just in revenue, but in how much profit it receives for each hryvnia of sales. Therefore, it is important to understand not only the revenue from a sale to a specific client, but also the marginal profit that the client brought to the company. The marginal profit must be determined taking into account all the costs associated with selling to this particular client. Quite often it happens that a client seems to buy a lot, but demands a maximum discount and additional conditions (delivery, or after-sales service, or bonuses). However, this customer's sales cycle is longer than for other customers, and the sales staff spends more time on him than on others. Therefore, it is advisable to evaluate the marginal income generated by the client per unit of time spent on each transaction. As a result, having counted all the costs associated with sales by such a client, it may turn out that these sales do not bring profit to the company.
In addition, the company should be interested in the question: what sales revenue does it receive for each hryvnia of capital used - a question of the efficiency of use of the company's capital. And this directly depends on the payment discipline of clients. Those. leads to the need to evaluate customer accounts receivable.
That is why we propose to use an extended version of the analysis - a simultaneous assessment of customer value based on revenue, marginal income, and several other indicators, selected depending on the specifics of the business and the company's strategy. For example, sales volumes, receivables maturity, frequency of purchases, dates of last purchase or revenue per transaction, etc.
Undoubtedly, a necessary condition for conducting a correct analysis is the correct keeping of sales records by customer segments, customer names, income received and taking into account all types of costs that the company incurs to attract, retain a customer, and after-sales service, if any. For such sales accounting, a well-developed methodology for management accounting in the company and its implementation in the IT system is required.
application area
Application area
In what cases is it used? ABC analysis
ABC analysis should be carried out at least once a year when developing and reviewing the company's strategy for the existing market in order to develop a marketing strategy and a sales strategy for existing customers. The analysis should also be carried out for medium-term (annual) sales planning: past sales to existing customers can serve as a basis for forecasting future sales. However, for many markets and types of businesses it is desirable to conduct ABC analysis more often - once a quarter - this will help quickly focus marketing and sales on the company's best clients.
How ABC analysis can be used in combination with other strategy tools
ABC analysis A powerful tool for analyzing the performance of a company's customer base, it represents only one component of a broad customer analysis process. Therefore, this tool should be used in conjunction with other strategic tools: strategic analysis of the client base, analysis of customer profiles, analysis of customer needs and behavior.
ABC analysis can be used not only for analyzing the customer base and identifying the best customers. It can be used to analyze product categories and nomenclature within a category to determine categories and products that are in greatest demand and most profitable, assess the effectiveness of sales managers, sales channels, partners, analyze inventories, raw materials and purchased materials, cost analysis, etc.
By combining customer analysis and product analysis, we obtain a cross-analysis that allows you to focus both on priority customers and on the items that priority customers choose.
step by step guide
How to put it into practice ABC analysis
Process ABC analysis consists of the following steps (see Fig. 1):
Fig.1 Process ABC analysis
Step 1 Selection of sales indicators for analysis and preparation of accounting data;
Step 2 Analysis of the effectiveness of customer segments;
Step 3 Analysis of segments for each sales indicator separately;
Step 4 Consolidation of assessment results for individual sales indicators into a single table;
Step 5 Analysis of the totality of all sales indicators;
Step 6 Drawing conclusions based on the results of the analysis and making management decisions.
The result of the ABC analysis process will give you a clearer understanding of who the company's most valuable customers are, generating the most revenue and profit. You will gain the basis for making informed management decisions to allocate your marketing budget to your most valuable customers; to focus the efforts of sales personnel (and other company personnel) on the most valuable customers; to further strategically develop a unique value proposition for its most valuable customers and strengthen the company's competitive position.
Step one: Selecting sales indicators for analysis and preparing accounting data
To carry out the analysis, you first need to select sales indicators by which the customer base will be assessed. These are, at their core, strategic sales metrics that a company has chosen to measure sales when developing a strategy. Typically this is revenue and contribution margin, plus maybe accounts receivable collection or sales volumes.
In addition, to carry out the analysis you will need preliminary data preparation. This is due to the fact that, as a rule, most companies have no order in their accounting. The same client can appear in accounting under different names. One of the reasons is that the client may have several legal entities to conduct business activities and execute transactions for different entities. The second reason is simple errors in introducing company names by sales personnel under different names due to the lack of management accounting standards in the company. It is enough to enter the client once as “Company”, the second time as LLC “Company”, the third time as LLC “Company”, and three clients will appear in the system instead of one.
Therefore, we upload data from the accounting system into an Excel table and analyze the names, compile a unique list of clients and summarize the sales of the same client. The results of this step can serve as the basis for bringing order to the accounting of sales to customers.
Step Two: Analyze the Performance of Customer Segments
Typically, a company works with several customer segments. Therefore, the first step is to analyze the contribution of each segment to the company's results. And then conduct further analysis of the customer base within each segment separately.
Fig. 2 Contribution of segments to the overall result of the company Due to the fact that, as a rule, there are not many customer segments in a company (2-4), assessing their effectiveness is not very difficult and clear. By calculating the share of segment revenue in the company’s total revenue and the share of segment revenue in marginal sales profit, we can immediately draw certain conclusions. In the example shown in Figure 2, Segment 2 brings the company 32% contribution margin with revenue of less than 24%. And this is the highest priority segment. You need to pay attention to segment 1, since despite the largest share in sales revenue, it brings the company only about 22% of marginal income. Even closer attention needs to be paid to segment 4 - why this segment has such low marginal income.
Step Three: Analysis of segments for each sales indicator separately
Set the range of categories: ABC
Fig.3 Ranges of ABC categories For each sales indicator, its own Excel spreadsheet is prepared. Let's consider the analysis using the example of the sales indicator “Revenue”. The analysis sequence is as follows:
We create a table;
We sort by sales indicator in descending order (in this case, by revenue);
We calculate the total amount of the sales indicator;
We calculate the “Share” column - the share of each client in the total sales indicator using the formula: (Revenue per client) / (Total revenue) * 100%;
We calculate the “Cumulative Total” column - the cumulative total of the clients’ share in revenue, starting from the highest value. For example, Client 14’s share of revenue is 29.89%, Client 10’s share of revenue is 26.55%, Client 13’s share of revenue is 6.09%. The cumulative total of the first three clients will be: Client 14 (29.89%) + Client 10 (26.55%) + Client 13 (6.09%) = 62.53%;
We assign categories ABC. We find a client whose share is cumulatively closer to 80%. This is the lower limit of category A. We find a client whose cumulative share is closer to 95%. This is the lower limit of category B. All clients below this limit belong to category C. We do conditional formatting by category: category A - green, category B - yellow, category C - red.
Fig.4 Results of Step 3. Step four: Consolidation of assessment results for individual sales indicators into a single table
The results of the analysis of the previous step for each individual sales indicator are summarized in a general table. The table is sorted by priority indicator, for example, by revenue.
Fig. 5 Summary assessment of customers based on a set of sales indicators Step five: Analysis of the totality of all sales indicators
For clarity, we transfer the results to a table with assigned categories. The visual result obtained makes it possible to quickly make assessments and make management decisions. In the example given, we see that Clients 14 and 8 are the most valuable for the company in terms of both revenue and marginal profit. Client 10 brings a lot of revenue and profit to the company, but the size of each transaction with this client is small, i.e. he buys often, but in small quantities. Accordingly, in the time aspect, each transaction with it requires more resources from the company. If the company's accounting system took into account the marginal profit taking into account the expenditure of staff time on one transaction, most likely, in terms of marginal profit, this client would not fall into category “A”. Imperfections in a company's accounting system can lead to incorrect conclusions
Fig. 6 Summary assessment of clients by aggregate of ABC categories There are a number of clients (Client 13, 2, 4) who fall into category “A” in terms of revenue, and category “B” in terms of marginal profit. This means that the company may be providing excessive discounts or additional free services to these customers. Client 15, with revenue in category “B”, falls into category “A” in terms of profit. This means that if efforts are made to increase sales to that customer, the company can generate additional profits proportionately greater than the increase in sales.
From the presented matrix it is also clear that half of the company’s clients fall into category C, i.e. in total brings the company less than 5% of revenue and profit.
A logical question arises: “What management decisions need to be made?”
These customers essentially drag the company down, diverting human and time resources that could be used more efficiently. In fact, those who rank at the bottom of your list are candidates for “fire,” as strange as that may sound.
Step six: Preparing conclusions based on the results of the analysis and making management decisions
No analysis makes sense if management decisions are not made based on its results. Sometimes there is an opinion that it is necessary to “bring up” those lagging behind in order to get more revenue and profit. But this is a wrong opinion. The company should focus on its most valuable, best customers who have proven that they already bring value to the company and can bring it in the future. The best clients are the ones that have generated the most revenue and profit in the last 12 months (or 24 months), and give you the highest hourly income/profit (the income they generate divided by the time you spend on them).
Decisions to make:
How can you better meet the needs of your most valuable customers to increase the revenue and profit they generate?
How to allocate your marketing budget to your most valuable customers?
How to distribute sales staff efforts?
It is imperative to separate customer segments and conduct analysis within the segments. Mixing customers of different segments in one analysis table will lead to incorrect assessment and incorrect management conclusions and decisions.
If a company sells its products in different geographic markets and operates in different industries, it is recommended to conduct an ABC analysis for each segment, each sales channel, each industry, for each market.
To obtain reliable estimates in ABC analysis, it is necessary to have reliable initial information in the accounting system. This requires a well-thought-out management accounting policy with detailed cost accounting and detailed sales analytics.
How to avoid mistakes associated with using ABC analysis
The most serious errors occur due to incorrect accounting data that incorrectly reflects the nuances of the company's business activities. Therefore, a necessary condition should be the development and implementation of a Management Accounting Policy with the greatest possible detail of costs associated with generating income and detailed sales analytics.
It is imperative to separate customer segments and conduct analysis within each segment.
It is not enough to analyze only revenue or only marginal profit. It is necessary to apply a cumulative assessment according to the strategic parameters of the company's sales.
restrictions
What are the advantages and disadvantages of ABC analysis
In order to get maximum benefit from ABC analysis it should be used throughout the organization on a regular basis to ensure that any sales trends can be identified and responded to in a timely manner.
Like all other methods ABC analysis has its advantages and disadvantages.
Advantages
Provides a simple, easy-to-use, and visual template for analyzing a company's customer base;
ABC analysis provides an economic assessment of each customer to determine their value to your business;
Flaws
Takes into account exclusively the company’s previous statistics (dynamics may change in the future);
Depends on the quality of accounting information: a well-thought-out management accounting methodology and the availability of detailed sales analytics;
Companies often simplify the analysis and conduct it based on only one sales indicator, which can lead to incorrect conclusions;
The process must be carried out regularly to be effective. However, companies often do not do this, believing that it requires significant time (and therefore money);
To make strategic decisions, it is recommended to use ABC analysis in combination with the method of strategic assessment of the client base, which complements the quantitative indicators of ABC analysis with qualitative expert assessments of the strategic importance of clients for the future development of the company.
For getting MS EXCEL ABC Analysis Template You must complete and submit the following form.
The purpose of analyzing the results of an enterprise’s activities is to identify problems, as well as to find ways and directions to combat them. The company's product range consists of many positions, each of which includes several varieties of the same product, differing in functionality, color and other characteristics. However, the production and sale of not all product items becomes profitable and ultimately brings the planned rate of profit. In order to prioritize between products and make a decision to exclude certain products from the range, it is necessary to conduct a comprehensive sales analysis. One of the methods of such analysis is ABC analysis.
What is ABC analysis
ABC analysis is a division of the company's product range into three groups, depending on the rate of profit that each of them brings.ABC analysis allows you to divide product items into three categories. During the analysis, more groups can be identified. The main functions of ABC analysis are presented in Figure 1.
Figure 1. Functions of ABC analysis In the process of ABC analysis, groups are designated by Latin letters:
- A – high priority, i.e. product groups that generate the largest percentage of income.
- B – medium priority, i.e. product groups that generate a percentage of income that is an order of magnitude lower than high priority groups, but make up a significant part of the profit.
- C – low priority, i.e. product groups that bring in the smallest percentage of income.
Speaking about determining the quantitative boundaries of a group, two characteristics can be distinguished: the share of revenue and the percentage of items. The most common quantitative boundaries for each group are shown in Table 1.
Quantitative boundaries of product groups
| Group name | Revenue share (%) | Percentage of titles (%) |
| A-group | 80 | 20 |
| B-group | 15 | 30 |
| C-group | 5 | 50 |
The ABC analysis process can be divided into several stages:
- Selecting an object of analysis.
At this stage, you should decide on the object. Since ABC analysis can adapt to any characteristic that has a quantitative assessment, it is very important to choose what exactly will be analyzed. For example, consumers, suppliers, product groups, product items, services, etc. - Selecting a parameter for analysis.
At this stage, you should decide on the parameter in relation to which the analysis will be carried out. Such a parameter can be the share of revenue, part of profit, market share, number of sales units, sales volume, etc. - Ranking of objects of analysis.
At this stage, the objects of analysis are sorted in descending order. - Distribution of analysis objects into groups.
At this stage, the share of the selected parameter for each group is calculated, and based on this, the groups are saturated with objects of analysis.
ABC analysis has the following advantages:
- Ease of use.
- Visibility of the analyzed indicators.
- Accuracy of calculated criteria and parameters.
- Quickly identify key problems and ways to solve them.
- Possibility of automation of each stage of the method.
- Does not require expensive equipment or additional methods for implementing the method.
- The speed of carrying out each stage of the method.
- Some subtleties when constructing complex structured diagrams.
- Some mistakes can lead to incorrect conclusions.
ABC analysis example
As an example, let's conduct an ABC analysis of sales at company N.Company N is engaged in the production of spare parts, mainly working to order. The assortment includes about 5,000 product items. One nomenclature group “Diamond wheels” was chosen as the object of analysis, containing 29 product units. As the primary data for ABC analysis, a balance sheet was generated for account 43 “Finished products” for 2011 using the 1C: accounting program. This report shows balances at the beginning and end of the period and turnover for the selected period of time in the context of analytics for product units included in the “Diamond Wheels” group. The balance sheet for account 43 for 2011 is presented in Table 1.
Turnover balance sheet
under account 43 “Finished products”
| Nomenclature units | Balance at the beginning of the period | Period transactions | balance at the end of period | |||
| Debit | Credit | Debit | Credit | Debit | Credit | |
| Alm. circle AS 3510-01, 100x10x5 ASN 40/28 Qty |
1 070,10 | 1 542,82 | 2 612,92 | |||
| Alm. circle AS 3510-02, 100x10x5 ASN (40/28+28/20) Qty |
633,12 | 15 428,20 20,000 |
15 291,35 20,000 |
769,97 | ||
| Alm. circle AS 3513-02, 100x9.5x5 ASN (40/28+28/20) Qty |
1 227,82 | 1 227,82 | ||||
| Alm. circle AS 3515-03, 150x10x5 ASN 60/40 Qty |
10 062,08 | 10 062,08 | ||||
| Alm. circle AC 3515-05, 150x10x5 AC6 80/63 Qty |
1 115,77 | 70 438,76 | 60 054,21 | 11 500,32 | ||
| Alm. circle AC 3515-06, 150x10x5 AC6 100/80 Qty |
8 866,24 | 2 216,56 | 6 649,68 | |||
| Alm. circle AC 3515-07, 150x10x5 AC20 125/100 Qty |
12 998,52 | 42 648,80 | 55 647,32 | |||
| Alm. circle AS 3515-14, 150x10x5 ASN 20/14 Qty |
1 663,14 | 1 663,14 | ||||
| Alm. circle AS 3516-03, 150x6x5 ASN 60/40 Qty |
3 958,96 | 3 958,96 | ||||
| Alm. circle AS 3520-01, 200x10x5 ASN 40/28 Qty |
2 550,30 | 2 550,30 | ||||
| Alm. circle AS 3520-03, 200x10x5 ASN 60/40 Qty |
21 444,20 20,000 |
749 273,47 | 732 788,28 | 37 929,39 29,000 |
||
| Alm. circle AC 3520-04, 200x10x5 AC 6 63/50 Qty |
388 764,38 | 349 527,08 | 39 237,30 30,000 |
|||
| Alm. circle AC 3520-05, 200x10x5 AC6 80/63 Qty |
19 072,39 19,000 |
1 224 304,49 | 1 201 523,76 | 41 853,12 32,000 |
||
| Alm. circle AC 3520-06, 200x10x5 AC6 100/80 Qty |
7 456,68 | 703 885,79 | 711 342,47 | |||
| Alm. circle AC 3520-07, 200x10x5 AC20 125/100 Qty |
213 231,94 | 213 231,94 | ||||
| Alm. circle AC 3520-08, 200x10x5, AC20 160/125 Qty |
67 098,72 39,000 |
1 432 125,75 | 1 487 172,33 | 12 052,14 | ||
| Alm. circle AS 3521-03, 200x6x5 ASN 60/40 Qty |
5 600,52 | 5 600,52 | ||||
| Alm. circle AC 3521-07, 200x6x5 AC20 125/100 Qty |
6 160,04 | 6 160,04 | ||||
| Alm. circle AS 3525-03, 250x10x5 ASN 60/40 Qty |
35 326,20 | 35 326,20 | ||||
| Alm. circle AS 3580-00, 80x10x5 ASN 28/20 Qty |
6 248,90 10,000 |
6 248,90 10,000 |
||||
| Alm. circle AS 3580-03, 80x10x5 ASN 60/40 OS Qty |
10 880,99 18,000 |
10 880,99 18,000 |
||||
| Alm. circle AC 3580-05, 80x10x5 AC6 80/63 Qty |
2 999,95 | 31 820,10 | 22 949,96 | 11 870,09 15,000 |
||
| Alm. circle AC 3580-06, 80x10x5 AC6 100/80 Qty |
35 474,60 | 26 571,00 | 8 903,60 10,000 |
|||
| Alm. circle AS 3581-10, 85x6x10 ASN 60/40 Qty |
193 596,99 | 193 596,99 | ||||
| Alm. circle AC 3581-12, 85x6x10 AC 6 63/50 Qty |
227 464,95 | 227 464,95 | ||||
| Alm. circle OS 100x6x5 AC6 80/63 Qty |
3 203,75 | 3 203,75 | ||||
| Alm. circle OS 100x6x5 ASN (40/28+28/20) Qty |
1 483,76 | 1 483,76 | ||||
| Alm. circle OS 150x10x5 ASN 60/40 Qty |
5 994,96 | 5 994,96 | ||||
| Alm. circle OS 80x6x5 ASN 28/20 Qty |
4 928,70 | 4 928,70 | ||||
| Total (amount) | 186 843,57 | 5 385 203,28 | 5 357 193,36 | 214 853,49 | ||
| Total (quantity) | 181,000 | 3818,000 | 3791,000 | 208,000 | ||
The debit reflects the receipt, and the credit reflects the disposal of inventories. For the purposes of this analysis, we will assume that the cost of all goods shipped has been paid.
Without going into details of the release and sale of each item and analyzing only the balance indicators at the beginning and end of the period by debit, you can notice that the balance of unsold goods in warehouses in monetary terms increased by 1.15 times compared to the previous year. This fact indicates that there are some problems with the sales of products, the identification of which requires a more detailed study of the assortment.
An initial examination of the balance sheet shows that there are some goods that have not been sold since last year. These items were not produced in the current period, however, they occupied some space in the warehouse. Also, their cost was not covered, which negatively affects the overall profit.
Let us calculate the share of such goods in the total volume of products of the analyzed product group. For the calculation, let's draw up table 2.
Goods stagnant in warehouse
| Name of nomenclature units |
Monetary value (rub.) |
Quantitative expression (PC.) |
| 1227,82 | 2 | |
| 3958,96 | 4 | |
| 6160,04 | 4 | |
| 6248,90 | 10 | |
| 10880,99 | 18 | |
| 3203,75 | 9 | |
| 1483,76 | 4 | |
| Alm. circle OS 150x10x5 ASN 60/4 | 5994,96 | 6 |
| 4928,7 | 14 | |
| Total | 44087,88 | 71 |
Based on the data obtained in Table 2, it is possible to calculate the share of non-profit-making goods in quantitative and monetary terms:
For calculations, you can use balances at the beginning and end of 2011. Since the object of analysis is sales for 2011, the share of goods stagnant in warehouses will be calculated relative to the balance at the end of the period.
The share of non-profit-making goods in quantitative terms is 0.34 (71/208);
The share of non-profit-making goods in quantitative terms is 0.21 (44087.88/214853.49);
Having compared the obtained indicators, we can say that the share of these goods in the total cost of all goods of the enterprise is significantly less than their share in the total number of product items. This indicates that these goods take up space in the warehouse, but their share in possible revenue is not large enough.
For a more in-depth analysis of the product range, we will select the share of product cost in the total cost as a parameter.
To conduct an ABC analysis regarding the share of the cost of goods in the total cost, turnover on credit 43 accounts was used, i.e., the cost of shipped goods was examined. Based on these data, nomenclature items were sorted from the highest sales in monetary terms to the lowest.
As a result of this ranking, the goods were divided into groups A, B and C. The ABC analysis report is presented in Table 3.
Range ranking
(in monetary terms, rub.)
| Nomenclature units | Period transactions | |
| Debit | Credit | |
| Group A | ||
| Alm. circle AS 3520-03, 200x10x5 ASN 60/40 | 749 273,47 | 1 487 172,33 |
| Alm. circle AC 3520-04, 200x10x5 AC 6 63/50 | 388 764,38 | 1 201 523,76 |
| Alm. circle AC 3520-05, 200x10x5 AC6 80/63 | 1 224 304,49 | 732 788,28 |
| Alm. circle AC 3520-06, 200x10x5 AC6 100/80 | 703 885,79 | 711 342,47 |
| Alm. circle AC 3520-07, 200x10x5 AC20 125/100 | 213 231,94 | 349 527,08 |
| Total | 4 482 353,92 | |
| Group B | ||
| Alm. circle AC 3520-08, 200x10x5, AC20 160/125 | 1 432 125,75 | 227 464,95 |
| Alm. circle AS 3521-03, 200x6x5 ASN 60/40 | 5 600,52 | 213 231,94 |
| Alm. circle AC 3521-07, 200x6x5 AC20 125/100 | 193 596,99 | |
| Alm. circle AS 3525-03, 250x10x5 ASN 60/40 | 35 326,20 | 60 054,21 |
| Alm. circle AS 3580-00, 80x10x5 ASN 28/20 | 55 647,32 | |
| Alm. circle AS 3580-03, 80x10x5 ASN 60/40 OS | 35 326,20 | |
| Alm. circle AC 3580-05, 80x10x5 AC6 80/63 | 31 820,10 | 26 571,00 |
| Alm. circle AC 3580-06, 80x10x5 AC6 100/80 | 35 474,60 | 22 949,96 |
| Alm. circle AS 3581-10, 85x6x10 ASN 60/40 | 193 596,99 | 15 291,35 |
| Alm. circle AC 3581-12, 85x6x10 AC 6 63/50 | 227 464,95 | 10 062,08 |
| Total | 860 196 | |
| Group C | ||
| Alm. circle OS 100x6x5 AC6 80/63 | 5 600,52 | |
| Alm. circle OS 100x6x5 ASN (40/28+28/20) | 2 550,30 | |
| Alm. circle OS 150x10x5 ASN 60/40 | 2 216,56 | |
| Alm. circle AS 3510-01, 100x10x5 ASN 40/28 | 1 542,82 | 2 612,92 |
| Alm. circle OS 80x6x5 ASN 28/20 | 1 663,14 | |
| Alm. circle AS 3510-02, 100x10x5 ASN (40/28+28/20) | 15 428,20 | |
| Alm. circle AS 3513-02, 100x9.5x5 ASN (40/28+28/20) | ||
| Alm. circle AS 3515-03, 150x10x5 ASN 60/40 | 10 062,08 | |
| Alm. circle AC 3515-05, 150x10x5 AC6 80/63 | 70 438,76 | |
| Alm. circle AC 3515-06, 150x10x5 AC6 100/80 | ||
| Alm. circle AC 3515-07, 150x10x5 AC20 125/100 | 42 648,80 | |
| Alm. circle AS 3515-14, 150x10x5 ASN 20/14 | 1 663,14 | |
| Alm. circle AS 3516-03, 150x6x5 ASN 60/40 | ||
| Alm. circle AS 3520-01, 200x10x5 ASN 40/28 | 2 550,30 | |
| Total | 14643,44 | |
| Total for all groups | 5 385 203,28 | 5 357 193,36 |
Analyzing the data obtained, we can draw the following conclusions:
- Group A contains 5 items, which is about 17% of the total number of product items in the product range under study. However, the share of revenue (at cost) for this group is 84% of total sales.
- Group B includes 10 items, which is 35% of the total number of items in the nomenclature. The share of revenue (at cost) for this group accounts for 16% of total sales.
- Group C consists of 14 items that provide the smallest percentage of sales. Moreover, this group includes 9 product items that have been sitting in the warehouse since last year and were discontinued in the analyzed period.
- Searching for new customers for products in group B in order to increase sales volumes;
- The production of goods in group A, not to order, but with the aim of forming a certain reserve in the warehouse in order to satisfy the needs of customers for these goods in the shortest possible time.
- Production of goods included in group C exclusively to order in order to avoid unjustified accumulation of product balances in warehouses.
- Sales of goods that are left in the warehouse at reduced prices in order to free up warehouse space and increase the total sales volume.
Considering the above, we can conclude that ABC sales analysis helps identify problems associated with assortment items, and also provides an information base for improving product offerings. But you shouldn’t try to increase your performance in many different areas at once. Efficiency should be increased gradually, highlighting priority development prospects and focusing marketing ideas and methods of their implementation on them.
Construction company "SDS"
External sources of financing for the enterprise
Presentation: The peasant question in Russia and its solution by the government in the 19th century Educational - instilling feelings of patriotism, uv