Application of ABC analysis in Microsoft Excel. A ready-made example of ABC analysis with a template in Excel

The purpose of the analysis of the results of the enterprise is to identify problems, as well as to find ways and directions to deal with them. The product range of the company consists of many positions, each of which includes several varieties of the same product, differing in functional, color and other characteristics. However, the production and sale of far from all nomenclature units becomes profitable and, as a result, brings the planned rate of return. In order to prioritize between products and decide on the exclusion of a particular product from the assortment, it is necessary to conduct a comprehensive analysis of sales. One of the methods of such analysis is ABC-analysis.

What is ABC analysis

ABC-analysis is the division of the assortment positions of the enterprise into three groups, depending on the rate of return that each of them brings.
ABC-analysis allows dividing nomenclature units into three categories. During the analysis, it is possible to identify more groups. The main functions of ABC analysis are shown in Figure 1.
Figure 1. Functions of ABC-analysis In the process of ABC-analysis, groups are denoted by Latin letters:

1. A - high priority, i.e. product groups that bring the largest percentage of income.
2. B - medium priority, i.e. product groups that bring a percentage of income an order of magnitude lower than high priority groups, but make up a significant part of the profit.
3. C - low priority, i.e. product groups that bring the smallest percentage of income.

Thus, by dividing the entire assortment into several groups, it is possible to identify top-selling products, as well as to identify the reasons why item items from low priority groups cannot move to a group one level higher.
Speaking about the definition of the quantitative boundaries of the group, two characteristics can be distinguished: the share of revenue and the percentage of items. The most common quantitative limits for each group are shown in Table 1.
Quantitative borders of commodity groups

Group name	Share of revenue (%)	Percentage of titles (%)
A-group	80	20
B-group	15	30
C-group	5	50

The figures illustrated in the table are not strictly defined boundaries for each group. At each enterprise, these indicators can vary within different limits.
The ABC analysis process can be divided into several stages:

1. The choice of the object of analysis.
   On the this stage the object must be identified. Since ABC analysis can adjust to any quantifiable characteristic, it is very important to choose what exactly will be analyzed. For example, consumers, suppliers, product groups, stock items, services, etc.
2. Selecting a parameter for analysis.
   At this stage, you should decide on the parameter with respect to which the analysis will be performed. Such a parameter can be a share of revenue, a share of profit, a market share, the number of sales units, sales volume, etc.
3. Ranking of objects of analysis.
   At this stage, the objects of analysis are sorted in descending order.
4. Distribution of objects of analysis into groups.
   At this stage, the proportion of the selected parameter for each group is calculated, and based on this, the groups are saturated with objects of analysis.

The economic content of ABC analysis is that highest value the result of the enterprise's activity is exerted by groups containing the least number of nomenclature positions. This provides the imbalance principle inherent in ABC analysis.
ABC analysis has the following advantages:

1. Ease of use.
2. Visibility of the analyzed indicators.
3. Accuracy of calculated criteria and parameters.
4. Quick identification of major problems and ways to solve them.
5. Possibility of automating each stage of the method.
6. Does not require expensive equipment and additional to the implementation of the method.
7. The speed of each stage of the method.

The disadvantages of ABC analysis include:

1. Some subtleties in the construction of complex structured diagrams.
2. Some mistakes can lead to wrong conclusions.

ABC-analysis can be used not only to assess current activities and search for opportunities to improve them, but also to analyze the effectiveness of the implementation of a set of measures established in the process of ranking goods into groups.

ABC Analysis Example

As an example, we will conduct an ABC analysis of sales at firm N.
Firm N is engaged in the manufacture of spare parts, mainly working under the order. The assortment includes about 5000 nomenclature positions. One nomenclature group "Diamond wheels" containing 29 commodity units was chosen as the object of analysis. As primary data for the ABC analysis, a balance sheet was formed on account 43 “Finished products” for 2011 using the 1C: accounting program. This report shows the balances at the beginning and end of the period and turnover for the selected period of time in the context of analytics for item units included in the Diamond Wheels group. The balance sheet on account 43 for 2011 is presented in table 1.
Turnover balance sheet
on account 43 "Finished products"

Nomenclature units	Balance at the beginning of the period		Turnover for the period		balance at the end of period
	Debit	Credit	Debit	Credit	Debit	Credit
Alm. circle AC 3510-01, 100x10x5 ASN 40/28 Qty	1 070,10		1 542,82	2 612,92
Alm. circle AC 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 AC 3513-02, 100x9.5x5 ASN (40/28+28/20) Qty	1 227,82				1 227,82
Alm. circle AC 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 AC 3515-14, 150x10x5 ASN 20/14 Qty			1 663,14	1 663,14
Alm. circle AC 3516-03, 150x6x5 ASN 60/40 Qty	3 958,96				3 958,96
Alm. circle AC 3520-01, 200x10x5 ASN 40/28 Qty			2 550,30	2 550,30
Alm. circle AC 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 AC 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 AC 3525-03, 250x10x5 ASN 60/40 Qty			35 326,20	35 326,20
Alm. circle AC 3580-00, 80x10x5 ASN 28/20 Qty	6 248,90 10,000				6 248,90 10,000
Alm. circle AC 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 AC 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 (sum)	186 843,57		5 385 203,28	5 357 193,36	214 853,49
Total (number)	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 shipped goods has been paid.
Without going into details of the production and sale of each nomenclature unit and analyzing only the debit balance at the beginning and end of the period, it can be seen 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 sale of products, the identification of which requires a more detailed study of the range.
An initial inspection of the balance sheet shows that there are some items that have not been sold since last year. These items were not produced in the current period, however, they took up 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 nomenclature group. For the calculation, we will compile table 2.
Goods stuck in stock

Name of the nomenclature units	Monetary expression (rub.)	quantitative expression (PCS.)
	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-profitable goods in quantitative terms is 0.34 (71/208);
The share of non-profitable goods in quantitative terms is 0.21 (44087.88/214853.49);
Comparing the obtained indicators, we can say that the share of these goods in the total cost of all goods of the enterprise is much less than their share in the total number of nomenclature units. This indicates that these goods take up space in the warehouse, but their specific gravity in possible revenue is not large enough.
For a more in-depth analysis of the product range, we will choose the share of the cost of products in the total cost as a parameter.
To conduct an ABC analysis regarding the share of the cost of goods in the total cost, the turnovers on the loan of account 43 were used, i.e., the cost of shipped goods was studied. Based on these data, item positions were sorted from the most maximum realizations in terms of money 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.
Product range ranking
(in monetary terms, rub.)

Nomenclature units	Turnover for the period
	Debit	Credit
Group A
Alm. circle AC 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 AC 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 AC 3525-03, 250x10x5 ASN 60/40	35 326,20	60 054,21
Alm. circle AC 3580-00, 80x10x5 ASN 28/20		55 647,32
Alm. circle AC 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 AC 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 AC 3510-01, 100x10x5 ASN 40/28	1 542,82	2 612,92
Alm. circle OS 80x6x5 ASN 28/20		1 663,14
Alm. circle AC 3510-02, 100x10x5 ASN (40/28+28/20)	15 428,20
Alm. circle AC 3513-02, 100x9.5x5 ASN (40/28+28/20)
Alm. circle AC 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 AC 3515-14, 150x10x5 ASN 20/14	1 663,14
Alm. circle AC 3516-03, 150x6x5 ASN 60/40
Alm. circle AC 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 obtained data, we can draw the following conclusions:

1. Group A contains 5 items, which is about 17% of total nomenclature positions of the studied product range. However, the share of revenue (at cost) for this group is 84% ​​of total sales.
2. Group B includes 10 items, which is 35% of the total number of items of the nomenclature. The share of revenue (at cost) for this group accounts for 16% of total sales.
3. Group C consists of 14 items that provide the smallest percentage of sales. Moreover, this group includes 9 nomenclature items that have been in stock since last year and discontinued in the analyzed period.

Based on these findings, we can formulate the following proposals for optimizing the studied nomenclature group:

1. Search for new customers for products in group B in order to increase sales;
2. The production of goods in group A is not on order, but in order to form a certain reserve in the warehouse in order to satisfy the needs of buyers for these goods as soon as possible.
3. The production of goods in group C is exclusively on order in order to avoid unjustified accumulation of product residues in warehouses.
4. Sale of stocked goods at reduced prices to free up storage space and increase overall sales.

In order to find the optimal solution for optimizing the entire assortment, each product group should be examined in a similar way.
Given the above, we can conclude that ABC-analysis of sales helps to identify problems associated with assortment positions, and also provides an information base for improving product offerings. But do not try to increase the 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.

ABC analysis

ABC analysis- a method that allows you to classify the company's resources according to their degree of importance. This analysis is one of the methods of rationalization and can be applied in the field of activity of any enterprise. It is based on the Pareto principle - 20% of all goods account for 80% of the turnover. In relation to ABC analysis, the Pareto rule can sound like this: reliable control of 20% of positions allows you to control the system by 80%, whether it be stocks of raw materials and components, or the product line of an enterprise, etc. Often ABC analysis confused with ABC method, deciphering ABC as Activity Based Costing , which is fundamentally wrong.

ABC analysis - analysis of inventory by dividing into three categories:

• A - the most valuable, 20% - commodity stocks; 80% - sales
• B - intermediate, 30% - commodity stocks; 15% - sales
• C - the least valuable, 50% - inventory items; 5% - sales

Depending on the goals of the analysis, an arbitrary number of groups can be distinguished. Most often, 3, less often 4-5 groups are distinguished.

In fact, ABC-analysis is a range ranking according to different parameters. It is possible to rank in this way both suppliers, and stocks, and buyers, and long periods of sales - everything that has a sufficient amount of statistical data. The result of ABC analysis is the grouping of objects according to the degree of influence on the overall result.

ABC analysis is based on the principle of imbalance, during which a graph of the dependence of the cumulative effect on the number of elements is built. Such a graph is called a Pareto curve, a Lorenz curve, or an ABC curve. Based on the results of the analysis, assortment positions are ranked and grouped depending on the size of their contribution to the cumulative effect. In logistics, ABC analysis is usually used to track the shipment volumes of certain articles and the frequency of access to a particular item in the assortment, as well as to rank customers by the number or volume of orders they have placed.

The procedure for conducting ABC analysis

1. We define the purpose of the analysis (and why do you actually need this analysis?).
2. We determine the actions based on the results of the analysis (what will we do with the results obtained?).
3. We select the object of analysis (what will we analyze?) and the analysis parameter (on what basis will we analyze?). Typically, the objects of ABC analysis are suppliers, product groups, product categories, commodity items. Each of these objects has different description and measurement parameters: sales volume (in monetary or quantitative measurement), income (in monetary terms), inventory, turnover, etc.
4. We compile a rating list of objects in descending order of the parameter value.
5. We calculate the share of the parameter from the total sum of the parameters with a cumulative total. The cumulative share is calculated by adding the parameter to the sum of the previous parameters.
6. Select groups A, B and C: assign group values ​​to selected objects.

There are about ten methods for selecting groups, the most applicable of them are: the empirical method, the sum method and the tangent method. In the empirical method, the division occurs in the classical proportion of 80/15/5. In the sum method, the share of objects and their cumulative share in the result are added - thus the value of the sum is in the range from 0 to 200%. Groups are distinguished as follows: group A - 100%, B - 45%, C - the rest. The advantage of the method is its great flexibility. The most flexible method is the tangent method, in which a tangent is drawn to the ABC curve, first separating group A and then C.

The probabilities of the emergence of demand for material resources A, B and C are subject to different laws. It has been established that in most industrial and trading firms, approximately 75% of the value of the sales volume is only about 10% of the items of the nomenclature (group A), 20% of the cost - 25% of the items (group B), 5% of the cost - 65% of the items (group C) . There are many ways to select groups in ABC analysis.

The ABC method is widely used in planning and forming an assortment at various levels of flexible logistics systems, in production systems, supply and marketing systems.

Experts advise caution when shifting the boundaries of the ABC groups (80/15/5), the fact is that the division of 80% 15% and 5% is usually used in practice. If you move the boundaries, an external listener (or expert) may draw incorrect conclusions based on the indicators you have given, for example, for the “C” group. Since his expectations about the group "C" = 5% will not coincide with the rules for selecting groups that you have changed.

see also

Literature

• Sterligova A. N., “Inventory management of a wide range. Where to start? ”, LogInfo magazine dated 12.2003
• Fisher Andrey, "Methods for selecting groups in ABC-analysis", journal "Logistics and Management", No. 1-2008

Links

Wikimedia Foundation. 2010 .

See what "ABC analysis" is in other dictionaries:

An operational management tool used to identify key customers, products, product groups in order to analyze, monitor and develop effective business solutions. It is based on the Pareto principle 20% of all goods account for 80% of sales... Glossary of business terms

- ... Wikipedia

Classification of receivables according to the degree of importance and methods of control, in which all debtors are divided into three categories depending on the value of receivables: A the most valuable, B intermediate, C the least ... ... Financial vocabulary

ABC is a multi-valued abbreviation, has a similar spelling to different languages. It can have the following meanings: Contents 1 Cyrillic 2 Latin 2.1 Television ... Wikipedia

Three factor linkage analysis- * analysis on the linkage of three factors * three point test cross method of genetic analysis, which determines the presence or absence of linkage between three genes and their relative position in case of linkage (see). To this end, they receive... Genetics. encyclopedic Dictionary

analysis (metallurgy)- analysis The procedure of mental or real dismemberment of an object (process, phenomenon), properties of an object (objects) or a relationship between objects into parts (features, properties, relationships). The inverse procedure of a. is synthesis. Analytical methods… … Technical Translator's Handbook

As an independent system, it is algebra in the broadest sense of the word, which considers all quantities as unknown numbers, using letters instead of arithmetic signs for numbers. Including in mathematical A. the doctrine of equalities, which is ... ... Encyclopedic Dictionary F.A. Brockhaus and I.A. Efron

- ... Wikipedia

ABC analysis is a method to classify a firm's resources according to their importance. It is based on the Pareto principle: 20% of all goods account for 80% of sales. Towards ABC analysis the Pareto rule might sound like this: reliable control 20% ... ... Wikipedia

FMR (abbreviation for the English Fastest Medium Rare fast, medium, slow) analysis of the product range by the frequency of calls / take. Sometimes the terms are also used: FSN (English Fast moving, Slow moving, Non Moving) and FNS ... ... Wikipedia

Books

• Management Accounting, Atkinson Anthony A., Kaplan Robert S., Bunker Rajeev D., Jung Mark S. …

ABC-analysis is a method that allows you to classify the company's resources according to their degree of importance. It is based on the Pareto principle, according to this law, 20% of the number of elements of any array of goods determines 80% of its total value. In relation to ABC analysis, the Pareto rule can sound like this: reliable control of 20% of positions allows you to control the system by 80%, whether it be stocks of raw materials and components, or the product line of an enterprise, etc.

ABC analysis - analysis of inventory by dividing into three categories:

A - the most valuable (75%);

B - intermediate (20%);

C - the least valuable (5%).

In fact, ABC-analysis is a range ranking according to different parameters. It is possible to rank in this way both suppliers, and stocks, and buyers, and long periods of sales - everything that has a sufficient amount of statistical data. The result of ABC analysis is the grouping of objects according to the degree of influence on the overall result.

ABC analysis is based on the principle of imbalance, during which a graph of the dependence of the cumulative effect on the number of elements is built. Such a graph is called a Pareto curve, a Lorenz curve, or an ABC curve. Based on the results of the analysis, assortment positions are ranked and grouped depending on the size of their contribution to the cumulative effect. In logistics, ABC analysis is usually used to track the shipment volumes of certain articles and the frequency of access to a particular item in the assortment, as well as to rank customers by the number or volume of orders they have placed.

Algorithm for conducting ABC - assortment analysis

First step: Define the object of assortment analysis.

A product category, a group, a subgroup, an item item, a customer (for wholesale trade), a debtor, a supplier, etc. can serve as an assortment analysis object.

When analyzing the assortment for the purpose of assortment management, it makes sense to choose a nomenclature unit (range item) as the object of analysis, which will allow for a detailed analysis of sales; and to analyze the structure of the assortment - a product category.

Second step: Determine the parameter by which the analysis of the assortment will be carried out.

It can be sales volume (in value or volume terms), gross income, average inventory (in value or volume terms), number of orders (for example, when analyzing customers), order volume, etc.

When analyzing the assortment for the purpose of analyzing the assortment, two parameters (features) are most often chosen: turnover in physical terms and income. In this case, the analysis is carried out within the category for each category. When conducting a through analysis, the turnover must be taken in value terms. It makes no sense to compare bottles with kilograms, and pieces with liters.

Conducting an analysis of the assortment on two grounds, in particular, on turnover and income, will make it possible to understand which goods are the most / least popular among consumers and what is the profitability for a trading enterprise.

When choosing several features (two or more), they are carried out separately for each feature, and then the results are combined. For example, when analyzing the assortment, groups (ABC) are first determined by turnover, and then by profitability. Then a combination is carried out and each position is characterized by two letters (for example, BC - the name of the assortment is in group B in terms of turnover and in group C in terms of profitability).

When analyzing the structure of the assortment, the profitability of the category is chosen as a sign of the analysis of the assortment. When analyzing customers, the volume of orders and the number of orders are chosen as features.

Third step: Determination of the total value of the selected indicator.

It is necessary to sum up the indicator for each position. When analyzing sales (for the purpose of assortment management), we determine the total value of turnover in physical terms for each category (group) and the total value of gross income for each category (group).

When analyzing the structure of the assortment, we summarize the values ​​of turnover (in value terms) and gross income of all categories.

Fourth step: Determine the share of each position in the overall result.

When analyzing sales, we determine the share of turnover and income of each position in the total value of these parameters of each category (product group).

When analyzing the assortment (determining the influence of a category on the overall result), we determine the share of turnover (in value terms) and gross income of each category.

Fifth step: Sort the assortment analysis objects in descending order of the share of each position.

Positions are ranked (analysis of the assortment, categories, customers, suppliers, inventory) in descending order of the share of the selected attribute.

So, for example, when analyzing sales (for the purpose of analyzing the assortment), we rank within each category by the share of turnover (in physical terms) in such a way that the higher the share contributed by a particular position to the total turnover, the higher this position will be located in the list within a category. At the next iteration, when we build groups by profitability, the position of the position in the list will be the higher, the higher the share of gross income.

Sixth step: Calculation of the share with the accumulation of the result of the assortment analysis.

We calculate the share with the accumulation of the result of the assortment analysis. When analyzing the assortment for this, the first position is assigned the share value determined in the fourth step, the second position is assigned the sum of the share of this position determined in the fourth step and the accumulated result of the previous position, the third position is assigned the sum of the share of this position determined in the fourth step and the accumulated result of the second positions, etc. That is, in general case accumulated result is equal to the sum share of the position and the accumulated result of the previous position.

For the first position, there is no previous one, which is why its accumulated share is equal to the share itself.

For the last position, the accumulated share should be equal to 100%, since as a result we have added up all the shares.

Let us clarify that if we consider the analysis of the assortment within each category, then we determine the accumulated result within each category, and not the end-to-end analysis of the assortment.

The seventh step: determining the number of groups and the boundaries of the groups of the assortment analysis matrix.

This step is the most complex algorithmic, but at the same time one of the most important. It defines, firstly, the number of partition groups, and secondly, the boundaries of these groups. So, for example, on which group we assign this or that position (the name of the assortment), it will depend, firstly, on its further fate(whether it will be left in the assortment or excluded), and secondly, the way its stocks are managed.

The number of partition groups can theoretically vary from 2 to n, where n is the number of positions under study. In practice, division into 3, less often 4 groups is often used.

Group boundaries are often set at the next level (in the case of choosing a division into three groups).

Eighth step: assignment of the position to the selected group.

Based on the accumulated share and the selected boundaries, we attribute each position to one or another group of the assortment analysis matrix.

To concentrate the main business resources on key customers, an 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 meanABCXYZ:

Group A - customers with the largest volumes of purchases
Group B - counterparties with average purchases
Group C - customers with a small volume of purchases

ABC XYZ analysis allows you to understand:

1. Who pays you more and more often than others;
2. How many target buyers are in your business;
3. Which customers should you focus on?

The main objective of the ABC XYZ study is to show the company's priority buyers in order to focus the main efforts on attracting and servicing counterparties that are in the AX category.

ABC XYZ Analysis: Product Line Study

ABC XYZ analysis of the current customer base is just one of the approaches. It should be used in conjunction with the analysis of the assortment (inventory) according to the same criteria.

The study of the product line allows you to rank the "importance" of goods / services. Traditionally, the significance of a product is the greater, the higher its performance in 2 criteria: profit and shipment volume.

Segmentation of products by these groups can be carried out according to other criteria, depending on the objectives of the study.

2. Reducing the range. This is a classic study on 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 the cost of maintaining the warehouse. Goods are ranked by turnover ratios and occupied warehouse space.

ABC XYZ Analysis: Buyer Migration

But simply doing an ABC XYZ analysis is not enough. Further, it is important to control the migration of customers from category to category. Those. you need to know how many buyers you have in category A, B, C, 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 through phone calls make a survey on 3 important questions:

1. How much they are now buying the same product that you have in other companies;
2. How much they buy a different type of product from other companies, and not from you;
3. What else do they buy from others that they would like to buy from you.

These 3 questions immediately give you a huge field in which to expand your share of the client. Record all responses and take them into account when conducting ABC XYZ analysis. Calculate your share of these buyers and analyze how you can increase it.

How to track migration in the B2C segment

If you have a B2C audience, then you have norms for the consumption of a particular product depending on the average salary.

You can collect a database, see how many buyers order in relation to this data. Based on this, calculate your share in the client and plan how much you can add. And in the future to encourage them to buy more.

In the B2C segment, it is also necessary to conduct an ABC XYZ analysis and rank those who used to buy better than now. Find out the reason for the decrease in purchases.

ABC XYZ Analysis: Holding Stocks

ABC XYZanalysis: measuring results

For the B2B segment, when conducting an ABC XYZ analysis, it is imperative to fill out a file that contains information about sales plans for each counterparty. Individual plans you need to put on the basis of previously made calls / meetings / sent offers. Then you will have a clear picture.

Therefore, here it is necessary to lead permanent job and for the current base, and for the new base, and for those who fell into group A. Then you will see a completely different dynamic in your business.

There can be from 50 to 200 B2B buyers per manager. And even more in the B2C segment.

We have covered the main points of ABC XYZ analysis, what results it gives, and how to work with this data. Do an ABC XYZ analysis in your company and focus all your resources on attracting and serving AX customers.

Today, the essence of work on assortment optimization is increasingly reduced to identifying and developing the most significant product groups in terms of profitability. It doesn't have to be complex marketing theories. As a basis, you can take, for example, data on profit in the context of product groups and connect the ABC analysis methodology. Let's take a closer look at how to analyze the range of goods, using only the main financial indicators.

In this article you will learn:

The assortment of many companies was formed spontaneously. New suppliers and brands were added, a new nomenclature. A large assortment served a large turnover. But at the same time, all this required large financial resources for the purchase, production and storage. Today, this is an unaffordable luxury for companies, many of which feel a shortage of working capital.

The main signs of an unsuccessfully selected assortment line:

• an increase in stock balances both in kind and in value terms, and at the same time, the lack of goods demanded by customers;
• an increase in the number of positions, accompanied by a decrease in profit;
• lack of purchasing resources.

Moreover expert methods when the assortment is formed only on the basis of the opinion of the heads of sales departments, in the case of large quantity product names do not work. A rigorous mathematical approach is needed. In fact, the essence of assortment optimization is often closer and more understandable to the financial director than, for example, to the head of the marketing department, and comes down to highlighting profitable product groups and, accordingly, developing them, and carefully folding the rest.

Personal experience
Mikhail Podlazov,
Before optimizing the assortment, it is necessary to determine what the company plans to achieve. As a rule, enterprises pursue three key goals: increasing revenue and profitability of sales, optimizing stocks of finished products, expanding sales markets and conquering new ones.
At Nidan Juices, four key metrics are assessed and analyzed before making portfolio adjustments:

• arithmetic mean sales volume;
• product line size;
• warehousing, transport network and production opportunities;
• profitability.

An example of an ABC analysis of product groups

What will help: maximize profits by managing inventory, and not keeping money in stock.

What will help: identify and eliminate excess stocks, implement a system for monitoring and optimizing stocks.

So, to conduct an ABC analysis, it will be necessary to determine the profit generated by each specific group of goods, its share in total amount profits and rank the groups according to this indicator, as well as calculate the share in profits as a cumulative total (see Table 1).

Table 1. Example of ABC analysis for product groups

Serial number in assortment	Product rating in terms of "share in profit"	Group name	Profit for the first half of 2009, rub.	share in profit, %	Share in cumulative profit, %	Category by profit
10	1	Beer	3 324 754	20,7	20,7	BUT
14	2	Perfumes and hygiene	2 157 010	13,4	34,1	BUT
1	3	Alcohol	2 040 270	12,7	46,8	BUT
12	4	Snacks	1 504 268	9,4	56,1	AT
3	5	Soft drinks	1 482 471	9,2	65,3	AT
5	6	Confectionery	1 469 275	9,1	74,5	AT
8	7	Meat products	1 205 017	7,5	82,0	AT
11	8	Cigarettes	1 093 273	6,8	88,7	FROM
4	9	Frozen food	724 245	4,5	93,3	FROM
2	10	Grocery	332 012	2,1	95,3	FROM
13	11	Juices	270 044	1,7	97,0	FROM
9	12	Household products	201 096	1,3	98,2	FROM
7	13	Milk products	191 609	1,2	99,4	FROM
6	15	Coffee Tea	80 046	0,5	99,9	FROM
15	16	Bread	10 832	0,1	100,0	FROM
TOTAL			16 086 221	100

It should be said right away that ABC analysis using the example of a company's assortment involves the selection of groups in slightly different proportions than the classical Pareto rule suggests. When conducting an ABC analysis, it will be quite justified to use the following criteria:

• category A - groups with the largest contribution to profit (share in profit), which together give up to 50 percent of the company's total profit;
• category B - commodity groups that together give about 35 percent of profit, and their accumulated share in profit is from 50 to 85 percent;
• category C - groups with the lowest share in profits, bringing the remaining 15 percent (cumulative share from 85 to 100 percent).

By the way, at the stage of analyzing the share in the profit of each product group, it would not be superfluous to compare the data obtained with similar indicators, for example, for the same period last year. A decrease in the share in profits may well indicate a post-crisis reduction in demand for certain goods, and it is advisable to take this trend into account in further work on optimizing the assortment.

Personal experience
Mikhail Podlazov, Deputy General Director for Economics and Finance "Nidan Soki"
For us, the profitability of a commodity item is one of the most significant indicators that make it possible to judge the need for the production of a particular item. There were precedents when the company refused some commodity items due to their low profitability in the practice of Nidan Juices. Thus, two commodity items in the product line, which includes 8 commodity items, demonstrated low profitability for six months. The reason is the large losses during the reconfiguration of equipment and the high cost of packaging. The financial service calculated that to reach the minimum required level of profit, it is necessary to increase sales of each commodity item by 80 percent.
In a competitive market, this was impossible, and the company had to abandon the production of these two commodity items. Of course, this also took into account such indicators as sales volume, the size of the product line, the possibilities of warehousing, transport network and production. After the decision to reduce commodity items is made, the available raw materials and materials are used only for the production of unprofitable SKUs. Then the equipment is reconfigured, production schedules change. The final stage is the adjustment of price lists and the removal of product residues from store shelves.

After conducting an aggregated ABC analysis (example above), it is necessary to expand and increase the depth of groups that are strong in terms of profitability and reduce financially disadvantaged categories.

Optimization of the most profitable product group

Product groups allocated to category A bring the company half of all profits, and their optimization should give the greatest effect. But in order to work with this group, you will have to analyze its composition.

Let's make a reservation right away that a product group can be homogeneous in composition, such as, for example, "beer" or "juices". In them, goods will differ only in brand, taste or package size. Or the group contains subgroups. For example, the group “perfumes and hygiene” contains subgroups “soap”, “wipes”, “shampoos”, “deodorants”, etc. In order to streamline all this, the concept of the width and depth of the assortment is most often used. The width is determined by non-interchangeable subgroups of goods. For example, for the product group “shoes”, the width of the assortment will be subgroups: “winter”, “summer”, “beach”. The depth is respectively determined by interchangeable goods. Usually they are located within a subgroup. For example, goods in different packaging volumes or similar in their characteristics, produced under different brands.

So, the optimization of group A assumes that the width and depth of the group should be maximum. In practice, most often it is justified to keep no more than 6–7 positions in one subgroup in terms of depth, but there may be exceptions in relation to group A. This is the key idea of ​​optimizing the range of goods that bring the greatest profit, and the algorithm for reducing unprofitable items for the company will be as follows.

Step 1. Checking the number of items in subgroups. Within the category A group, an ABC analysis is carried out, an example of which was described above. The bottom line is to determine the subgroups of category C and check the number of items on them.

According to the universal rule, the number of items in each subgroup assigned to this category should not exceed the product of the number of commodity items in group A, multiplied by the share in the profit. For example, if the entire group A, in particular “perfumes and hygiene”, consists of 300 items and includes such subgroups as “shampoo”, “nail polish removers”, “soap”, “toothpaste”, etc. Moreover, the subgroup “nail polish remover” was categorized as C (0.9 percent share of profit). Accordingly, there should not be more than three types of nail polish remover (0.9: 100 X 300 = 2.7). If this is not the case, their number will have to be reduced. To make a choice, an ABC analysis is carried out already for specific commodity items - the next step.

By the way, if the number of items in the category A group under study is less than 50 items, then the first step can be neglected and immediately proceed to the analysis by commodity items.

Step 2. Name analysis. The logic of actions at this stage is the same - ABC analysis by share in profit, with the only difference that the object is the analysis of specific product names (see Table 2). Category C falls under the reduction. Although there are some exceptions that need to be considered, namely:

• if the company's position is not critical, you should not delete products that have been launched recently from the assortment. It is clear that the profit on them is lower, if only because they are sold for less time than all the others;
• category C may include accessories and related products that stimulate sales of category A.

table 2. Example for trade names of the subgroup "shampoo"

№	Subgroup	Profit for the first half year 2009, rub.	share in profit, %	share in cumulative profit, %	Category by profit
1	Shampoo "Nivea" dmuzh 250 ml	58 636	16,86	16,86	A
2	Shampoo "Nivea" dry 250 ml	49 985	14,38	31,24	A
3	Shampoo "Nivea" dokr hair	41 090	11,82	43,06	A
4	Shampoo "Nivea" with extension pod 250 ml	27 551	7,92	50,98	A
5	Shampoo "Nivea" jir wal 250 ml	26 211	7,54	58,52	A
6	Shampoo "Nivea" dmuzh 250 ml	19 582	5,63	64,15	B
7	Shampoo "Nivea" from dandruff. with extber 250 ml	18 451	5,31	69,46	B
8	Shampoo "Nivea" volume 250 ml	17 351	4,99	74,45	B
9	Shampoo "Belito" kefir 500 ml	17 107	4,92	79,37	B
10	Shampoo "Belito" beer yeast	15 165	4,36	83,73	B
11	Shampoo "Belita Exclusive" egg 585 g	13 459	3,87	87,60	B
12	Shampoo "Daf" osvmilvol 200ml	8646	2,49	90,08	C
13	Shampoo "Belito Exclusive" henna 585 g	7729	2,22	92,31	C
14	Shampoo "Timotei" Cherry/Cotton 400 ml	7217	2,08	94,38	C
15	Shampoo "Palmoliv" for hair	5250	1,51	95,89	C
16	Shampoo "Timothy" henna 400 ml	4811	1,38	97,28	C
17	Shampoo "Palmoliv" for light hair	3937	1,13	98,41	C
18	Shampoo "Nivea" for hair	2849	0,82	99,23	C
19	Shampoo "Daf" dokr ox 200 ml	1312	0,38	99,61	C
20	Shampoo "Antoshka" club 320 ml	1239	0,36	99,96	C
21	Shampoo "Head & Shoulders" basic care 200 ml	132	0,04	100,00	C
TOTAL		347 712	100

It would also be justified to reduce the range of items that fell into category C, getting rid of "unstable" goods. It's about about those items whose sales fluctuate greatly from month to month. It is quite risky to bet on it, since in the event of an unfavorable development of events, it threatens to overstock warehouses, increase the volume illiquid assets in a word, significant losses for the company.

To assess the stability of sales using XYZ-analysis. For each product of the analyzed group, the coefficient of variation is calculated (shows the degree of data deviation from the average value) according to the following formula*:

where х i is the volume of sales of goods for the i-th period;

x is the average value of the sales volume for the analyzed product;

n is the number of periods.

As initial data, sales data of the group's goods for several periods are used. The volume of sales can be considered in rubles or in physical terms. Although the latter is preferable. The fact is that if the company has recently revised its pricing policy, the results will be incorrect.

Two important remarks. First, the number of periods must be at least three. Secondly, for products with pronounced seasonality the period must be longer than the seasonal cycle. Another option is to use periods within seasonal ups (or downs, respectively).

• X - variation does not exceed 10 percent. Stable sales, therefore, the main concentration of efforts and resources. Such a product does not promise big losses company, even if it is purchased (produced) in a larger volume than required:
• Y - variations within 11-25 percent. A less stable category than X, but nevertheless a fairly reliable product;
• Z - the spread exceeds 25 percent. It is better to remove such a product from the assortment or work with its supply (production) under the order.

* The formula for calculating the coefficient of variation can be otherwise represented as the ratio of the standard deviation to the average value of the indicator. In Excel, the standard deviation is easy to calculate using the "STDEV" formula.

Table 3 Sales stability analysis

Name of product	Sales volume, rub.			Standard deviation	Average value, rub.	Variation, %	Group
	IV quarter. 2008	I sq. 2009	II quarter. 2009
3	116 285	114 926	116 195	760	115 802	1	X
5	47 818	50 697	48 299	1542	48 938	3	X
1	305 922	276 658	335 817	29 580	306 132	10	X
6	34 500	27 865	32 289	3379	31 551	11	Y
8	37 929	36 685	30 750	3837	35 121	11	Y
2	255 420	245 089	327 870	45 108	276 126	16	Y
4	79 036	48 999	102 571	26 851	76 869	35	Z
7	12 346	33 786	32 502	12 025	26 212	46	Z

What to do with products that bring in 35 percent of profits

So, with the groups classified as category A, sorted out. A slightly different approach is applied to the category B group - in Table. 1 is “snacks”, “ soft drinks», « confectionery". The sequence of actions may be as follows.

1. ABC​-analysis of names. It is quite justified to conduct an analysis immediately for commodity items for the entire group, without dividing into subgroups. We definitely leave the positions of category A. From the remaining products of categories B and C, we will have to complete the assortment.

2. Select related products. If you focus only on profitable positions and there are no related products (services) in the assortment, then instead of increasing sales, there will be a decrease in revenue.

You can identify them in two ways. First, expertly, you can force such a sample of employees of the marketing department. Secondly, related products can be found through cross-analysis. It is done by analyzing checks in retail or by analyzing invoices in wholesale companies. The bottom line is to collect data on which kits are most often purchased. And in the end, leave in the assortment those items from category B, which are most often purchased with category A.

3. Keep stable products. Based on the XYZ analysis discussed above, categories X and Y are returned to the assortment. Regardless of sales volume and profit margin. If some products consistently (plus/minus 10–25%) bring the company a small but profitable profit from month to month, it will most likely be inappropriate to refuse them.

4. Increase the depth of category A groups. Let's say that in the group "juices" cherry, orange and apple juice one brand. This is an occasion to add other flavors of the same trademark from category B.

5. Determine the commodity items that are significant for the buyer. There are several categories of goods that should be in stock:

• "traffic generators" - a category that provides a flow of buyers. These are goods from high frequency purchases. However, they do not necessarily bring significant profits. But, coming for them, customers simultaneously acquire other names;
• "cash generators" - a category that provides a large volume of sales, that is, having the maximum turnover in the group's assortment. In conditions of liquidity shortage, it is important not to exclude it in pursuit of profit;
• "Defenders" - a category of goods for which the buyer concludes general level prices in the organization. For example: "milk is cheap, butter is cheap - which means you can look at price tags with less predilection." As a rule, it is built on a price basis from goods - traffic generators.

The marketing department will also insist that branded products be on sale. Whether this is justified in category B depends on the strategy of the business and its resources.

Personal experience
Dmitry Ivanov, Chief Financial Officer Wimm-Bill-Dann
Wimm-Bill-Dann has over a thousand product names. Of course, the idea to reduce and optimize the range would seem obvious. This will automatically lead to a reduction in the required storage space, eliminate the need to work with a huge number of items of purchased raw materials, equipment changeovers, illiquid stocks, etc. But we must not forget that the products support each other. You cannot significantly reduce the number of items and not lose in revenue. When a customer comes to the store, he should see, for example, ten varieties of juice. If there are fewer of them, you take up less shelf space, you are less visible, you are bought less and, therefore, your market share is less.
But I would not categorically state that it is impossible to reduce the assortment. This can be done, but with extreme caution. If you reduce the number of juices from ten to nine, that's fine. This is exactly what we do from time to time, trying to save our “penny”. But reducing the assortment to six types of juices is a big mistake. Such optimization will have an extremely negative impact on the company's revenue. In other words, it is relatively painless to cut product line 10 but not 20 percent.
In Russia, more than 25 percent of the market falls on Orange juice, approximately 25 percent is an apple, the same is in a tomato, and peach and other flavors occupy the remainder. However, we cannot limit ourselves to these names. Our consumers want to see variety.

6. Save novelties. Regardless of the situation in the company, its financial position will be more stable if new products appear in the assortment from time to time. The fact that some product is profitable now does not mean that tomorrow customers will lose interest in it. In order not to miss the moment of “degeneration” of “profit generators”, you need to regularly analyze the results of sales for the main products (at least once every six months or a year), track changes in the share in profits.

All other products from category B can be thrown away and disposed of.

What products to exclude from the assortment

Category C groups identified during preliminary analysis assortment, it is better to completely exclude and not spend the financial resources of the company on them. Of course, making exceptions for items that have recently appeared in the assortment, they are important related products for category A, traffic generators.

Expert opinion
Alexey Fedoseev, CEO group of companies "Intalev"
A number of our clients are deliberately reducing the number of brands and suppliers they work with. And all this is done in order to optimize costs as much as possible. Many finally began to calculate the cost of commodity items, which almost no one had done before. It turned out that a number of brands are very expensive. One of our clients reduced the number of suppliers of non-fungible goods from 150 to 10. In the next quarter, he lost 20 percent of revenue, while profit fell by 3 percent. This was done last year, even before the crisis. But due to the fact that the company began to work more efficiently with the remaining customers, the profit in the next quarter increased by 40 percent.
The company began to receive large discounts for volume purchases and large rebates. Of course, the measure was risky. Throughout the quarter, the company's managers were, to put it mildly, in a state of stress - whether it would work or not, constantly monitoring the market's reaction. But in the end, the reduction in assortment justified itself, although one or two commodity items were returned after the quarter.