To analyze the product assortment, ABC analysis 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 provides the maximum effect.

The assortment is usually analyzed according to two parameters: sales volume (quantity sold) and profit received (realized trade margin).

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

A - the most valuable, 20% - inventory; 80% - sales

B - intermediate, 30% - inventory; 15% - sales

C - least valuable, 50% - inventory; 5% - sales

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. Depending on the purposes of the analysis, an arbitrary number of groups can be identified.

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.

In logistics, ABC analysis is usually used to track shipment volumes of certain items and the frequency of requests for a particular product range, as well as to rank customers by the number or volume of orders they place.

Let's consider a specific example of ABC analysis:

The purpose of the analysis is to determine the highest priority product units.

The object of analysis is the sale of goods in natural units.

Initial data for the confectionery products group are in Table 2.

Table 2. Initial data for the group of confectionery products.

Let's divide objects into three groups:

Let's calculate the share of revenue for each product in the total revenue (column 4);

Let's calculate the parameter from column 4 on an accrual basis (column 5). We select groups 50%/30%/20% (column 6);

Let's assign group values ​​to the selected objects.

Table 4. Grouping of objects

Group A – goods, the sum of shares with a cumulative total of which is the first 50% of the total sum of parameters. These objects require careful planning, constant (perhaps even daily) and careful recording and control. These goods make up 50% of the turnover, and accordingly, the higher the cost of the goods, the more expensive errors in their analysis are. Periodic inventory counts with tight tolerances are required. A complete inventory of these groups should take place at least once a quarter. For group A, constant monitoring of demand, volumes of ordered batches and the size of safety stock is necessary. Careful monitoring and reduction of product turnover times is necessary.

Group B – products following group A, the sum of shares with a cumulative total of which ranges from 50% to 80% of the total sum of parameters. These objects are less important for the company and require regular control and well-established accounting (possibly monthly). They are subject to the same measures as for category A, but they are carried out less frequently and with larger acceptable tolerances.

Group C – other goods, the sum of shares with a cumulative total of which ranges from 80% to 100% of the total sum of parameters. These products are characterized by simplified planning, accounting and control methods. However, despite their apparent low value, they account for 20% of turnover (or profit) and require periodic monitoring.

Conclusion

To summarize, let us note the main features of the ABC analysis method.

ABC analysis is one of the methods of rationalization and can be applied in the field of activity of any enterprise and cover various management objects, such as product range, inventory, suppliers, customers, etc.

ABC analysis is based on the Pareto principle: “20% of effort produces 80% of the result, and the remaining 80% of effort produces only 20% of the result.”

The purpose of the ABC analysis method is to identify problems that need to be addressed first by prioritizing them. The ABC analysis method allows you to classify management objects according to the degree of their significance in the final result of the organization’s activities, and based on the results obtained, make appropriate management decisions.

The main advantages of ABC analysis are its ease of use, transparency, and versatility. Disadvantages include its one-dimensionality, distortion of results when using multidimensional ABC analysis, division of objects into groups without taking into account their qualitative characteristics.

The algorithm for performing ABC analysis is presented in detail in the second part of the work. One of the main stages that significantly influences the result of the analysis is the selection of groups, which can also be carried out using several methods: empirical, sum method, differential method, polygon method, tangent method, loop method. The tangent method and the sum method are of greatest interest for practical use, each of which has its own advantages.

All of the above methods distinguish three main groups: group A, group B and group C.

Group A– the most important resources, the locomotives of the campaign, bring maximum profit or sales. The campaign will suffer large losses if the effectiveness of this group of resources sharply decreases. Group A resources must be strictly controlled, clearly forecasted, frequently monitored, be as competitive as possible and not lose their strengths. Maximum investments and the best resources should be allocated to this group of resources. The successes of group A should be analyzed and transferred to other categories as much as possible.

Group B– a group of resources that provide good stable sales/profits for the campaign. These resources are also important for the campaign, but can be moderated at a calmer and more moderate pace. These resources are usually “cash cows” and are relatively stable in the short term. Investments in this type of campaign resources are not significant and are only necessary to maintain the existing level.

Group C– the least important group in the campaign. This group may include: resources that need to be disposed of, that need to be changed, and improved. When analyzing this group, you need to be very careful and first of all understand the reason for their low contribution to the results of the organization's activities.

After all objects are divided into groups according to all selected factors, the results of the analysis are interpreted and, on the basis of this, actions are taken aimed at solving the problem posed at the first stage. The economic meaning of research within the framework of ABC analysis comes down to the fact that the maximum effect is achieved when solving problems belonging to group A.

List of used literature

1. Maslova, T.D. Marketing: textbook for universities / Etc. Maslova, S.G. Bozhuk, L.N. Kovalik. – 3rd ed., revised. and additional – St. Petersburg: Peter, 2008.

2. Berezin I.S. Marketing analysis. Market. Firm. Product. Promotion.
– 3rd ed., rev. and additional - M.: Vershina, 2008.

3. Alesinskaya T.V. Basics of logistics. Functional areas of logistics management Taganrog: Publishing House TTI SFU, 2009.

4. Marketing: general course. Ed. Kolyuzhnova N.Ya., Yakobson A.Ya.
– M.: Omega-L, 2006.

5. Marketing by notes. Practical course using Russian examples. Ed. Danchenok L.A. – M.: Market DS Corporation, 2004 .

6. Electronic resource: “Financial Management Center” // http://center-yf.ru/data/Marketologu/ABC-analiz.php.

Is working with non-target clients. Your managers may be technically savvy, have perfect scripts and work experience, but there will be no results if they are not knocking on the doors of your customers.

ABC analysis: target audience test

How to identify problems?

To find out if you have this problem, run through our checklist:

• Managers hold many meetings, but there is no result;
• Build a business based on the experience gained in another type of business;
• There is no buyer qualification process;
• There is no definition of the target client portrait,
• No ABC analysis.

Even if you agree with one of these statements, construct an ABC analysis.

Why is it needed?ABC analysis?

ABC analysis is a study of the customer base in two directions: volume and frequency of purchases. ABC analysis allows you to understand:

1. Who pays you more and more often?
2. What is your buyer persona?
3. Are your target buyers entering your funnel?
4. Where to direct the main efforts of managers
5. How to increase shipments to new customers

ABC analysis: algorithm for its implementation

The study will show the distribution of buyers by revenue on the ABC axis, and on the XYZ axis we will see them broken down by the frequency of transactions. You will see immediately.

As a result of the ABC study, group A should include 20% of counterparties with the largest purchase volumes, B - 60% with medium purchases, and C - 20% with small purchases. The same needs to be done for XYZ, placing in group X those who contact you most often, in Y - irregularly, in Z - those who make single purchases.

ABC analysis: building a portrait of the target client

What do the ABC analysis results tell you?

After conducting an ABC analysis, we look at the intersection of ABC and XYZ and identify the most interesting buyers for the company.

1) Those who got into groups A and B, provide the bulk of revenue. There should be as many of them as possible in the company.

2) AX and BX provide a significant volume of purchases with regular re-use.

3) AY and BY bring you good revenue, but do not return to you consistently. Come up with bonus programs for them, regularly remind them of yourself, inform them about promotions and new products.

4) AZ and BZ, despite decent volumes of purchases, they return to you unpredictably.

5) Those who are in group C, it is worth reconsidering and partially abandoning them.

6) With participants CX and CY work on increasing the average check.

7) B CZ the most uninteresting buyers for you end up. Don’t waste your managers’ time on them; stop working with them.

ABC analysis: changing the business process of attracting customers

What to do next with ABC analysis?

Raise the majority of buyers to category A

Based on the results of ABC analysis, plan tasks for each of the target counterparties and measure their capacity:

Implement a buyer qualification process. It is important to understand that ABC analysis must be done regularly and, based on its results, indirect criteria must be drawn up to determine the portrait of your target audience (for examples of criteria, see the article). They will help you determine at the entrance whether a potential buyer fits your target audience and whether it is worth spending time on him. Add this stage to your .

Based on the results of the ABC analysis, make additional fields for the portrait of the target audience in CRM and prohibit moving from stage to stage without filling them out.

ABC analysis can also be carried out for individual segments: partner and retail channels. It will be useful to look at this analysis by product line.

This way you will kill two birds with one stone: you will be able to draw a conclusion about which products bring you the most revenue and which ones you need to give up. ABC analysis by segments of counterparties will allow you to find out what needs to be offered to them here and now.

ABC analysis: studying the product range

ABC customer analysis is just one of the sections for studying the current base. There is another approach - ABC inventory analysis.

Traditionally, the importance of a product is identified by 2 parameters - sales volume and profit received. Again, the Pareto rule is adopted as a general hypothesis. According to it, 20% of assortment items provide 80% of the profit.

A huge number of companies in the world have repeatedly conducted ABC analysis of their assortment. The conclusions almost always come down to this ratio:

• 10% of assortment items (group A) provide 80% of turnover;
• 15% of assortment items (group B) provide 15% of turnover;
• 75% of assortment items (group C) account for 5% of turnover.

Assortment analysis

Taking all this into account, the entire range can be divided into groups according to degree of importance:

• group A – the most significant goods, which make up the most valuable and working part in the assortment;
• group B – goods of medium level of importance;
• Group C – the least important goods. Essentially, these are “candidates for relegation.” New products sometimes fall into this group. Their significance usually requires further confirmation over time.

It is important to understand that ranking products into groups can occur according to different criteria depending on the goals.

Goal 1: reduction of assortment. In this case, products are analyzed in terms of sales volumes and profitability.

Goal 2: Cost-effectiveness study. Goods are distributed depending on the level of profitability and turnover ratio.

Goal 3: Reduce inventory holding costs. Here we analyze the assortment by turnover ratios and occupied warehouse space.

ABC analysis: look at the assortment

In businesses of various types, experts note the effectiveness of such a tool as ABC assortment analysis.

Example for a cafe. Here ABC analysis is needed to optimize the menu. To do this, the share of each position in profit and turnover is determined. To do this, you should use a specific research algorithm:

1. A special table is generated in which data on cost, selling price and number of sales per month / six months / year is entered for each product.

2. Using a formula, products are ranked on a scale from 1 to 100 depending on their share in profit and turnover.

3. Positions are allocated to groups A, B and C.

If the position on this scale is in the range from 1 to 50, then this is category A. If the assortment group falls in the range from 50-80, then it is entered in B. Everything that is below the line “80” is “unqualified” "

The products that ended up in groups A and B are significant and effective, as they generate profit and make up almost the entire turnover of the cafe. Group C should undergo “rehabilitation” or expulsion. In the first case, it is necessary to stimulate demand. In the second, hopeless case (usually when the position is on the list of outsiders not for the first time), such a product should be disposed of.

ABC analysis: complement it with XYZ analysis

In addition, XYZ analysis is used as a complementary tool and to obtain a clearer picture.

XYZ analysis is a tool that divides products according to the level of fluctuations in their consumption and regularity of shipment.

A slightly more complex method is used here, which involves obtaining the so-called coefficient of variation. Simply put, flow fluctuations. The coefficient itself shows the deviation of flow rate from the average value and is expressed as a percentage.

The following parameters are used:

• volume (quantity);
• sum;
• the amount of realized trade margin.

As a result, we get the same distribution of goods, but in other categories - X, Y, and Z. Products in these categories are characterized by the degree of stability of their behavior.

Thus, category X includes assortment groups whose deviation varies from 5% to 15%. Sales for these items are easily predictable and easy to plan, since they have a stable consumption level.

Category Y includes everything that has a coefficient of variation from 15% to 50%. Sales for such items are more difficult to predict. And here, most likely, we are talking about goods with seasonal demand.

Combining the results of ABC and XYZ analysis

Combining both types of analysis (ABC and XYZ) has 3 important advantages.

1. You will be able to optimize your inventory management structure based on the most reliable data.
2. You know which product’s share should be increased in your assortment matrix.
3. You understand how to redistribute personnel depending on qualifications and experience: who should work on one product and who should be transferred to another.

ABC analysis: monitoring migration

It is important to remember that ABC analysis will provide you with an x-ray snapshot of the state of your current customer base. You should use this tool regularly and then you will see the dynamics of movements of counterparties and products from category to category, the so-called migration.

If the company operates in the B2B segment, then you can track migration across several sections.

1. Migration by buyer
2. Migration by product
3. Migration by managers
4. Change in the quality of the seller's portfolio by volume of shipments according to ABC
5. Monitoring the regularity of purchases for XYZ in the employee’s portfolio

If a company operates in the B2C segment, then migration monitoring should be carried out by product. Initially, as a guideline for identifying categories, take the consumption standards of a particular product depending on the average salary.

ABC analysis: how to increase profitability

It should be noted the high degree of practicality of such a technique as ABC analysis. Company marketing and strategic branding decisions also depend on the insights this research provides. Let's give an example.

Problem

Client Oy-li imported components for lighting equipment from China. The main buyers are manufacturers and intermediary wholesalers. The company was faced with the task of reducing the risks of dependence on a single Chinese supplier. Therefore, it was decided to sell imported goods under its own brand. This way, it would be possible to get rid of the “dictation” of the supplier and change it if necessary, without explaining this step to your counterparties.

Such a strategic and useful decision in every sense caused displeasure on the part of the mediators. They resisted it because they believed that their customers would contact the brand holder directly.

In order to understand who really is a significant customer and whether it will be possible to do without some of them in the future, an ABC analysis of suppliers - consumers of the importer's products was carried out.

As expected, the proceeds were distributed between two groups of buyers: manufacturers of lighting equipment - 60%, intermediaries - 40%. At first glance, in such a situation, abandoning the “rebellious” resellers would be tantamount to closing the business. We began to analyze who buys more often and more.

But the intermediaries ended up in the AY category. This meant that although they covered good volumes, their stability left much to be desired.

This situation seemed alarming to Oy-li experts. And that's why. Reseller counterparties supply components to the same manufacturers. Logically, their volume of purchases also had to be uniform in order to cover the regular needs of manufacturing clients. However, this did not happen. Orders arrived with unpredictable frequency. And sometimes just from case to case.

Solution

To understand what was going on, we decided to conduct an ABCXYZ analysis of the assortment matrix. And here a very interesting detail emerged. As the study showed, intermediaries never purchased a strictly limited set of components from which the manufacturer could assemble the final product. On the contrary, each time they made “chaotic” purchases: for completely different items and irregularly. The obvious conclusion was that the resellers used the services of other suppliers and simply re-ordered what they were missing.

The study also demonstrated another unpleasant feature of working with intermediaries: the importing company had almost zero profit from interacting with them. Intermediaries received too high discounts from the seller.

Result

Taking into account all the clarified circumstances, the Oy-li client was recommended to continue working towards its own branding, without relying on the opinion of resellers. A set of measures was also developed that made it possible to reach new volumes with manufacturers.

Since all the solutions were implemented, the importing company almost immediately increased its profitability by 15% and had the resources to focus on working with manufacturers.

ABC analysis: conclusions

So, with the help of ABC analysis, the customer base and assortment are examined, which allows us to determine who and what brings the greatest results. An assessment supplemented with XYZ analysis, which will show the regularity of customer orders and purchases of a certain type of product, will be even more informative.

ABC analysis cannot be carried out, look at the results and calm down. It is necessary to make such cuts periodically. And based on them, make efforts to ensure that some buyers or products migrate to higher-income categories. For this purpose, buyers need to be stimulated - offered promotions, bonuses, special conditions: if there is potential for client development, this opportunity should not be missed.

It is also important to be able to part with some clients and some products. If they make rare and small purchases or are not in stable demand, then why waste effort on them? It is better to direct actions to those groups that bring the best results.

Proverbs don’t appear on their own... Sometimes you get into such a jungle of analytics that your hand inevitably reaches for the liquor cabinet (oh well, we know there’s one in every office).

But let's talk a little about something else.

In retail, logistics, warehouse and inventory management there is such a thing as ABC analysis. Many theoretical publications have already been written about it. And everything seems to be relatively simple and clear, but is it really so?

When a category manager or marketer of a retail chain comes close to conducting ABC analysis, he inevitably has a whole bunch of questions, hesitations and doubts. It is with them that we will work in this article!

Let's go through the algorithm of actions for ABC analysis in grocery retail chains, exceptions to the rules that must be taken into account, and show an example of conducting an analysis for the product group of Alcoholic beverages (yes, exactly those half-liters).

If anyone is hearing about ABC analysis for the first time, here it is.

how it is done.

ABC analysis- This is the most common method of studying assortment. It is based on the Pareto law, applicable to many aspects of life. Its essence for retail is that 20% of products provide 80% efficiency, and the remaining 80% of products provide only 20%.

ABC analysis is a method with which you can determine the contribution of each product to the turnover and profit of a store, and distribute products into categories for effective assortment management.
To do this you need:

1. Sort all products by selected criterion (for example, turnover).
2. Calculate what percentage the turnover of each product is of the total turnover of the product group.
3. Calculate cumulative (or accumulative) interest by adding the percentage to the amount of previous interest.

We define the boundaries of categories that should differ significantly from each other.

1. We build a cumulative curve.
2. We connect the extreme points of the curve with a straight line.
3. Find the point of tangency of the line parallel to the resulting straight line. This point will determine the boundaries of category A, for which the nature of the accumulation of the qualitative criterion is homogeneous.
4. Similarly, we connect the boundary point of category A and the extreme point of the curve with a straight line.
5. We find the point of tangency of the line parallel to the resulting straight line and determine the boundaries of category B.

When conducting an ABC analysis, the first thing that needs to be done is to determine

How, Why and What will we use it for?

1. What is the purpose of the analysis?
2. What will be the objects of analysis?
3. By what criteria?
4. What percentage will be optimal for ABC analysis?
5. Over what time period should the analysis be carried out? and with what frequency?
6. How to divide products into A, B, C categories?
7. What will be the interpretation and actions based on the results of the analysis?

Purpose of analysis depends on the existing problem or, why are we doing it at all? Any analytics serves to achieve some goal, ABC analysis is by no means an exception. A clear vision of the goal is already half the success of marketing activity.

The goal predicts what we can achieve using ABC analysis, so it may differ even depending on who is conducting the analysis. Category managers most often analyze product sales, store managers analyze turnover, and marketers analyze the inclusion of goods in customer receipts.

The most popular goals are:

• identify product groups that bring the greatest profit;
• optimize the assortment;
• highlight leading and outsider products;
• manage inventory and supplies;
• compare indicators with the previous period, analyze changes.

Here it is necessary to approach the analysis quite carefully. For example, to optimize the assortment, analysis of the entire assortment of a store or chain will yield practically nothing. After all, we cannot leave only bread, milk and alcohol in the store, although these groups will be the most popular. But in the context of a separate product group, you can easily track products of group C (outsiders in terms of turnover and number of sales) that need to be disposed of.

Criteria. Again closely related to the object and purpose of the analysis.
The most common:

• turnover;
• revenue;
• profitability;
• number of sales;
• number of receipts, entry into receipts - frequency of purchases of goods.

Percentage. Unfortunately, the average values ​​​​proposed by the Pareto principle do not always correspond to reality. In reality, a category manager or store manager, when determining the percentage, is guided, first of all, by his experience, goals and analysis criteria, and the specifics of the assortment of a product group, store or retail chain.
80-15-5,
70-20-10,
50-30-20,
and even 40-40-20, these are all possible options for percentage ratios of categories A, B and C.
A wide spread indicates a variety of situations and the impossibility of focusing on a universal relationship between category boundaries. Thus, a category manager of a large retail chain can afford to remove a significant number of category C products from the assortment; the store shelves will not be empty in any case. Another thing is the manager of a small retail chain of 2-3 stores, where the release of 100-200 products will have a detrimental effect on the breadth of the range presented.

A period of time. Often, conducting an ABC analysis is too expensive in terms of using the working time of marketers, category specialists or store managers, and the results of such an analysis will, to put it mildly, not be obvious due to the cyclical nature of product sales by day of the week or season.

For example, an analysis of the entire product range can be carried out once every six months in order to analyze which products and product groups are the most important and what has changed compared to the previous period.

Analysis of goods in each product group is usually carried out once every 2 months, with possible options of once every 3 months. It all depends on the size of the assortment and the capabilities of the network analysts.

Division into A, B, C categories.
When analyzing a store’s product range, a marketer can use one criterion - for example, the profitability of a product or product group, but the data obtained is not always useful enough.

Therefore, cross-analysis is used according to several criteria at once. Yes, this approach is not simple, but using a larger number of criteria allows you to better see the existing situation. During this process, several options are possible:

1. Sequential division into categories.

It is worth using if the range of product groups is too large. First, the assortment is analyzed according to the first criterion (for example, turnover), then each resulting category is analyzed again according to the second criterion (number of sales), etc. As a result, we get subcategories with a relatively small list of products that are convenient to work with.

2. Parallel division into categories.

We carry out ABC analysis simultaneously (in parallel) according to several criteria, creating categories like AA, BC, etc...

Using 2 criteria, say Income and Number of Sales, we already get 9 categories:

This approach is more complex, gives a larger number of product categories, but allows you to obtain extensive information about each category.

For example, using 3 criteria for parallel analysis, products that receive AAA are the most important products for a retailer. They generate significant income, are often purchased, and generate revenue. This means they must be constantly available, with uninterrupted supplies and good reserves.

Products of the ABA, BAA, AAB categories are also quite important and should be actively worked with. For example, a product is included in category A in terms of revenue and profitability, and in category B in terms of sales. It’s worth finding the best place on the shelf for it, or carrying out promotional activities, and the store will receive a significant profit. Another option is a product group with category A in terms of sales and profitability, and category B in terms of revenue. For goods in this category, a revision of the pricing policy is possible, so a slight increase in the price of goods will lead to an increase in store revenue.

3.Usage synthetic approach to defining categories.

For each criterion, a weight coefficient (WC) is determined, depending on its significance for the purpose of the analysis.
For example, for analysis, Turnover is more important than the Number of sales of a product, and the Number of sales is more important than Entries in receipts.

For each product, a synthetic indicator is calculated.

Next, it is necessary to rank the results obtained.

This approach makes it possible to characterize each product item included in the classification with one number and conduct an ABC analysis as if only one criterion were used.

Interpretation. The results of the ABC analysis should be carefully studied; hasty decisions should not be made.
The idea of ​​classic ABC analysis remains unchanged in any case - the distribution of goods into categories for further work. The analysis allows us to identify products that require maximum attention from marketers, category managers, and managers for their qualitative impact on the activities of the retail chain, while limiting the scope of management to the required minimum.

The number of category A is always minimal, category C is the maximum. At the same time, category A has priority in terms of maintenance and work with it. Category B has a standard level of service, category C - if goods are not removed from the range, then they have the lowest level of service and attention.

Things to remember or exceptions to the rules

Promotional goods. If during the period taken for ABC analysis you had promotions in your retail chain or in a separate store, then the results of sales of promotional goods can significantly affect the reliability of the analysis. Here it is important for the marketer to decide whether to exclude products covered by the promotion from the data set for analysis, or to make a certain amendment for them depending on the conditions of the promotion.

Luxury goods. Products that are not included in the main assortment of a store or chain (sold less than 2 times a week or even much less often), but when sold can bring significant income. They can be included in the data array for ABC analysis, where with a significant probability they will fall into category C. But such products are important for the store’s assortment, which means they cannot be displayed. At the same time, due to the low frequency of sales, it is inappropriate to allocate space in the store warehouse for luxury goods; it is easier to organize their purchases upon sale.

New products. Anyone understands that no matter how advertised a new product is, at first its sales will be significantly lower than proven brands. But at the same time, new products are absolutely necessary in any store. There are possible solutions here.

New products are not included in the analysis and cannot be removed from the assortment during the first few months of sales.

If it is technically too difficult to exclude new products from the data set, they are assigned the label “New”, and when interpreting the results of ABC analysis, such products are not subject to reduction.

Another option is to automatically include new products in category A. Why is he bad? The fact that a certain number of new products in category A shifts other products lower in the ranking.

Missing products. For various reasons, sometimes a product may not be on the store shelves or in the warehouse. That is, in principle, it could be sold, and there was demand, but there is no data for analyzing sales of the product. Therefore, it is useful when interpreting ABC analysis to know the date of the last arrival of the goods at the store.

Let us give an example of conducting ABC analysis.

So, purpose of analysis- selection of goods for promotions, reduction of the range of product groups.

Object of analysis- the main assortment of the “Alcohol” group throughout the entire distribution network.

A period of time- 2 months.
The analysis will be carried out using a parallel approach of 2 criteria: Turnover and Number of sales. The choice of these criteria directly depends on the purpose of the analysis. The managers of the retail chain needed to reduce the number of products that took up space on the shelves and did not significantly affect the turnover of the product group as a whole.

Analysis of the main assortment will allow us to obtain more accurate data without taking into account seasonal or unavailable products.

Percentage.
The optimal ratio in this option would be 75-95-100 according to the selected criteria due to the specifics of the product group.
In the screenshot below we see the number of product items that are included in each category A, B and C and the percentage of the category from the total indicator.

For greater clarity of the relationships between categories, consider them in a diagram.

Interpretation. Let's analyze the results obtained.
Analysis is possible using both tabular data and visualizations.

The first goal is to select products for promotions.
Category AA By Turnover and Number of Sales, 162 product positions fall, as can be seen in the screenshot below.

We can visualize data for each category.

For example, now to build visualization we used the following indicators:
horizontal axis - number of sales for the selected period;
vertical axis - turnover for the selected period;
circle diameter - % of the turnover of the selected category. Other options for constructing a graph are possible, depending on the goals of the ABC analysis.

As we can see, GreenDay Organic Life is the leader in sales in this retail chain in the AA category by a significant margin.

The best-selling brands are GreenDay and MEDOFF. Work with suppliers of such goods should be very well established; they are the ones who supply us with leading goods. It is possible to create special better conditions for them, additional space on shelves, organize promotional activities, etc.

But we consider it inappropriate to carry out promotions for goods of category AA; these goods sell well even without a promotion.

In this case, it is better to plan promotion for the AB category, which significantly affects the store turnover, and the number of sales of the group’s products will increase as a result of promotional activity.

The results of selecting products in category AB can be seen in the figure below.

As you can see, the most successful promotions will be for Georgian cognacs and wines, as well as Klinkov brand cognacs.

The second goal of our analysis was optimizing the assortment and getting rid of unsold goods . Let's deal with category SS.
Here visualization simplifies the analysis even more. As we remember, luxury goods can also fall into this group. For example, in the picture below there is Scotch whiskey with a price above 800 UAH. per bottle sold only 2 times in 2 months, but brought significant profits.

But products along 2 axes tending to zero and with a small circle diameter, which do not in any way affect the overall turnover, should be removed from the assortment - they are not sold and only take up space on the shelves. As an example, in the picture, the wine “Sun in a Glass” was sold only 2 times in 2 months for 32 UAH. per bottle and therefore does not affect turnover in any way.

Thus, ABC analysis allowed us to distribute the products included in the Alcoholic Beverages product group into 9 different categories and develop recommendations for the retail chain to optimize the assortment:

• Category AA - leading products, the highest priority category, products must always be in stock, careful control of inventory levels is necessary;
• Category AB - products that will bring maximum efficiency during promotions;
• categories VA, BB, BC, SV - average products, average level of inventory management and shelf placement;
• Category CC - outsider products; a detailed analysis of the category and removal of the lowest-performing products from the assortment is required.

One of the most common methods of assortment analysis is ABC analysis, which is based on the Pareto principle. The latter, in turn, states that 80% of the causes are responsible for 20% of the consequences. This rule, also called 80/20, means that in any process a small percentage of reasons (20%) are vital, and the remaining reasons (80%) do not have a serious impact on the final result.

This rule can be applied to various socio-economic phenomena and business processes. For example: 80% of the work is completed in 20% of the time; 80% of the company's profit comes from 20% of customers; 80% of warehouse space is occupied by 20% of inventory; 80% of sales volume is provided by 20% of sellers; 80% of problems are caused by 20% of defects, etc.

In each specific case, this proportion may deviate from the exact parameters of 80/20 and be 90/10 or 65/35. However, this does not change the essence and is that the largest percentage of the result is provided by a smaller percentage of costs.

As part of the assortment analysis, this rule says that 20% of products provide 80% of the store’s turnover.

ABC analysis method will allow you to determine those 20% of goods that are a priority for the store. In essence, this method involves ranking the product range according to various parameters. Traditionally, the entire assortment is divided into three groups of products depending on their contribution to the turnover and profit of the store:

1) goods of group A - the most important goods, providing the first 50% of results;

2) goods of group B - goods of medium importance, providing another 30% of the results;

3) goods of group C - the least significant goods, providing the remaining 20% ​​of the results.

Traditionally, ABC analysis is carried out in three stages.

Stage 1. Determination of the object of analysis and parameters for its assessment.

The objects of analysis can be:

product groups;

commodity items;

suppliers.

Evaluation criteria may include:

sales volume (in physical and monetary terms);

turnover;

inventory, etc.

Stage 2. Compiling a list of analysis objects in descending order of the value of the selected parameter.

Stage 3. Determination of groups A, B and C, for which it is necessary to calculate the share of the parameter from the total sum of parameters with a cumulative total and distribute the objects of analysis into groups in accordance with the obtained values.

Let's consider ABC analysis by product groups using a specific example of a grocery store operating in the “shop near home” format.

Stage 1. Within the framework of this analysis, the object of research is product categories, including:

milk products;

bakery products;

confectionery;

alcoholic products;

fruits and vegetables;

meat, sausage;

juices, water.

To carry out the analysis, it is necessary to systematize information on the turnover of each product group for a certain period (month, quarter, year). After this, it is necessary to calculate the share of each product group in the total turnover of the store (table).

Stage 2. Sort product groups in descending order of their share in turnover.

Trade turnover of product groups for the 1st quarter of 2010

Table

ABC analysis of store assortment

Thus, the ABC analysis shows that the main share of the store’s turnover is provided by such product groups as dairy products, baked goods and meat, sausages, which belong to group A. Product groups - juices, waters and fruits, vegetables belonging to group C, need development and require additional sales promotion actions, for example in the form of price reductions or product range expansion.

However, ABC analysis by product groups gives only a superficial idea of ​​the structure of the store’s assortment. For a more detailed and in-depth analysis, it is advisable to conduct a similar analysis for product categories within product categories, since it is more informative and allows you to more effectively manage the assortment.

For a deeper study of the assortment, you can use XYZ analysis. It allows you to create a more complete picture of the trading process in the store.

XYZ analysis also involves dividing the store’s assortment into groups X, Y and Z, and the criterion for this analysis may be the profitability of goods or the stability of their sales.

In this case, most often the need to conduct an XYZ analysis arises when the store is faced with the task of analyzing the assortment simultaneously according to several parameters and presenting its results in the form of a matrix.

Let's consider the combination of ABC and XYZ analysis using the example of a food store.

The following product groups are the object of research:

milk products;

bakery products;

meat and sausages;

confectionery;

alcoholic products;

fruits vegetables;

juices, water.

The combined analysis includes the following steps.

Stage 1. Conducting ABC analysis. The criterion for dividing goods into groups within the framework of this analysis is the share of the product group in the total turnover of the store. The results of the analysis are presented in table.

ABC analysis

Stage 2. Conducting XYZ analysis. At this stage, the criterion for dividing goods into groups is the share of the product group in the store’s gross profit. The results of the XYZ analysis are presented in table.

Table

XYZ analysis

Stage 3. Combined analysis (ABC- and XYZ-). It is necessary to combine the results of ABC and XYZ analysis, as a result of which the entire assortment of the store is divided into 9 segments based on two criteria - the share of the product group in the store’s turnover and the share of the product group in the store’s profit. The results of the combined analysis are presented in table.

Table

ABC and XYZ analysis

The analysis made it possible to identify the most profitable and least profitable product groups for the store.

Thus, the most profitable product groups for a store are AX, BX, AY - groups that provide the greatest contribution to the turnover and profit generation of the store.

Groups requiring special interventions to improve their effectiveness include BY and CY. These groups have sufficient potential, but in order for them to move into the group of profitable products, it is necessary to optimize the assortment and pricing policy in relation to these groups.

Finally, the least profitable and poorly traded products include the BZ and CZ segments. These product groups require special attention from store management.

From the article you will learn about free Excel add-in for ABC-XYZ analysis.

This add-on will help you do ABC - analysis and XYZ - analysis with one keystroke.

This add-on once became the idea for creating Forecast4AC PRO.

In this article we will tell you:

• About the capabilities of the add-in for ABC XYZ analysis;
• How to use it to do ABC analysis;
• How to do ABC XYZ analysis;
• And where can I download the add-on for free?

You can download the add-on on the Buyers Club website page download

If the button is not pressed and the menu does not appear, then enable macros. You can read how to enable macros in Excel in the article "How to enable macros in Excel"

Let's start with the ABC analysis settings.

I have an opportunity:

1. In addition to groups ABC, you can add groups AA, D and E. To do this, check the boxes for the corresponding group.
2. Set boundaries for each group

If in group “AA” there is 15%, in group “A” 50%, in “B” 80%, “C” 95%, “D” 99%, then

The “AA” group will include positions that make a significant part of the sales volume (or other analyzed indicator) greater than a given limit, in our case - more than 15% of the total volume. If you use this group, then the products that fall into it are excluded from the ABCDE analysis, and the analysis will be done for the remaining products.

• Group “A” includes positions that make 50% of total sales (or other analyzed indicator).
• Group "B" - positions that, by sales volume (or other indicator), make from 50% to 80% of total sales.
• Group "C" - positions that, by sales volume (or other indicator), make from 80% to 95% of total sales.
• Group "D" - from 95% to 99% of total sales.
• Group "E" - the remaining 1% of total sales.

Now let's look at the settings for XYZ analysis:

We wrote about the use of XYZ analysis in forecasting in the article

Let's move on to the settings.

1. As in ABC analysis, it is possible to set the boundaries of XYZ groups;

2. And also together with ABC XYZ analysis, it is possible to derive sigma, average, coefficient of variation:

Let's move on to consider examples of analysis calculations using the add-in.

1. ABC - analysis.

To do this, the data must have the following presentation (in our example, the name of the goods and sales volumes for 2012 in a column, in the attached file is the “ABC analysis” sheet):

Place the cursor in the first cell of the column and click on the A-Z analysis button:

The program will display groups for each analyzed position in the adjacent column:

To do this, the data must have the following representation (1 Excel line - 1 time series, the number of series is not limited, in the attached file - the "ABC_XYZ analysis" sheet):

We select the entire area with the analyzed data, starting from the upper left cell, where we want to start the ABC XYZ analysis, ending with the lower right cell, where we want to finish the analysis.

If there is a lot of data and it is inconvenient to circle it with the mouse, then for quick selection you can use the keyboard shortcut ctrl+shift+end.

Place the cursor in the upper left cell with the data:

and press the key combination ctrl+shift+end, Excel will automatically select the area, starting from the top left cell and ending with the bottom right cell, as in the picture below:

Now we press the “A-Z analysis” button, and the add-on does an XYZ analysis for each time series, and also automatically summarizes the data for each series and does an ABC analysis.

As a result, in continuation of the data for each time series, the program displays:

• in the first column - groups ABC,
• in the second - groups XYZ,
• in the third (if checked) - sigma,
• at 4 - average,
• 5 - coefficient of variation.

So, with one keystroke, using the add-on from the Buyers Club, you can do ABC and XYZ analysis.

You can download the add-on from the Buyers Club website download

After downloading, open the add-on and click the “Add A&Z analysis to menu” button. You will see the "A-Z Analysis" button in the Excel "Add-Ins" menu.

Join us!

Download free forecasting and business analysis apps:

• Novo Forecast Lite- automatic forecast calculation V Excel.
• 4analytics - ABC-XYZ analysis and emissions analysis Excel.
• Qlik Sense Desktop and QlikViewPersonal Edition - BI systems for data analysis and visualization.

Test the capabilities of paid solutions:

• Novo Forecast PRO- forecasting in Excel for large data sets.