Abc sales analysis findings. ABC analysis - what is it

ABC-analysis (ABC-analysis) - a method that allows you to determine the most significant resources of the company in terms of gross sales and gross profit.

In marketing, the most popular is ABC assortment analysis. It is carried out both for an individual brand, and for the company as a whole. The method allows you to identify unprofitable or low-profitable groups of goods, timely improve and optimize the assortment portfolio.

Method Description

The purpose of ABC analysis is a simple, convenient and visual ranking of any resources in terms of their contribution to profit or sale. Thanks to this ranking, one can correctly prioritize activities, focus on the use of limited company resources (labor, time, investment, etc.), identify excessive use of resources, and take timely corrective measures.

Frequency of ABC analysis: at least 1 time per year, strategically, on a quarterly basis.
To make strategically correct decisions, the results of this method are recommended to be watched in dynamics over several periods. A monthly analysis can be carried out, but this period of time is too small to implement the decisions made and too small to track the dynamics of the situation
Advantages of ABC analysis: versatility, simplicity and clarity.
Limitations of ABC analysis: the method is too mathematical, sometimes it may not take into account the strategic goals of the company.
For example: developing categories will always be in category “C”, as in the short term they will have a minimal contribution to the company's sales / profits

Do you know the theory and you only need practice?

The boundaries of the main groups

The ABC analysis method is based on the Pareto Rule, which reads as follows: 20% of the effort provides 80% of the result.

The method is built on the principle of classification of the analyzed resources into 3 groups A, B and C:

A-group: provides 80% of sales / profits, usually 15-20% of all resources
In the group: provides 15% of sales / profits, usually 35-20% of all resources
C-group: provides 5% of sales / profits, usually 50-60% of all resources

The boundaries of the groups 80% -15% -5% can be changed and can be set individually by each company.

Which indicator to use as the basis for ABC analysis - profit or gross sales - is also up to the analysis performer. In each case, it all depends on the objectives of the analysis.

For example, the goal is as follows - the company needs to increase profitability in a short time. In this case, it is advisable to conduct an analysis based on the contribution of each position to the total profit.

There may be another goal: to focus sales efforts on the best-selling goods — in this case, it is more expedient to choose gross sales.

Types of ABC Analysis

The ABC analysis technique is widely used in various industries and activities due to its versatility. It can be used in strategic and tactical management, planning and budgeting, logistics and company inventory management. Varieties of ABC analysis:

ABC analysis of goods of an individual brand or the entire assortment
ABC analysis of company stocks
ABC analysis of raw materials and any purchased materials
ABC analysis of customers or consumer groups
ABC analysis of suppliers
ABC analysis of the performance of units and analysis of labor resources
ABC budget analysis. investment or any costs

What conclusions can be drawn based on ABC analysis

After dividing all the goods into ABC groups, decisions are made regarding each product group. The main directions of conclusions that can be made as a result of the ABC analysis:

1 Group A- The most important resources, the locomotives of the company, bring maximum profit or sales. The company will suffer heavy losses with a sharp decrease in the effectiveness of this group of resources, and therefore, the resources of group A should be tightly controlled, clearly predicted, often monitored, be as competitive as possible and not lose their strengths.

For this group of resources should be allocated maximum investment, the best resources. The successes of group A should be analyzed and maximally translated into other categories.

2 group B - a group of resources that provide good stable sales / profits of the company. These resources are also important for the company, but can be moderated at a calmer and more moderate pace.

These resources are usually relatively stable in the short term. Investments in this type of company resources are not significant and are necessary only to maintain the existing level.

3 group C - The least important group in the company. Usually, the resources of group C pull the company down or do not generate income. When analyzing this group, you need to be very careful and first of all understand the reason for the low contribution.

Prepared using source: P. Gopalakrishnan, M. SundaresanMaterials Management: An Integrated Approach, 2004

Ready-made solutions

We have a ready-made template with which you can easily put the theoretical knowledge of this article into practice. Download an example an example for conducting ABC analysis of the assortment can be in the section.

ABC-analysis (ABC-Analysis) is a way of classifying (ranking) company resources according to a specific parameter, most often of significance. Read how to do an abs analysis, where it is applied and what are its benefits.

What is this article about:

Scientific and applied progress in the field of economics and finance does not stand still and offers an increasing number of methods and techniques for optimizing various aspects of the business. Over the past decade, managerial tools have been enriched by state-of-the-art “hype” assessment methods based on neural networks and big data (“Big data”). But traditional methods are still relevant and practical, it is too early to discount them. One of these classic methods is ABC analysis.

The essence of ABC analysis

The avs analysis is based on the rule of thumb 80/20 or the Pareto principle. In the general case, the rule says that only a small part of the efforts made brings most of the effect - 80 percent of the result is achieved only through 20% of the actions. For us, this means that only 20% of customers give 80% of revenue, and 20% of goods generate 80% of revenue.

Goods or services should be divided into categories - A, B and C. It is customary to classify assets as group A that provide just 80 percent of the result. For example, if it is necessary to get rid of low liquid stocks, then goods with regularly high demand will be assigned to group A. If you need to distribute the limits by organization receivables between customers - buyers with a consistently high volume of purchases, paying on time. Next, we consider all categories in more detail.

Download and get to work:

What will help : The regulation allows to reduce stock balances of goods, increase inventory turnover and, as a result, reduce the cost of their storage. Planning is based on ABC analysis.

What will help : Useful for ABC customer analysis, analysis of the effectiveness of the existing system of discounts and bonuses and other sales conditions.

Category A

The most valuable customers or products in your portfolio fall into category A. Products in this category generate the lion's share of revenue without absorbing an equally large (proportionate) share of company resources. This category is likely to consist of a very small number of customers or money-makers.

What formed this category is a topic for careful control, manual control and an individual approach when it comes to customers. Category A also implies that the loss of a client or problems with a product from this category have a noticeable negative impact on the business as a whole.

Category B

Products or customers, ranked in ascending or descending order, who are in the middle of the list, most likely fall into category B. Many mistakenly believe that this is a sub-segment of goods, customers, resources, which contributes to the overall “basket”, but not large enough, if we talk about its role in the total result. This approach is not true. Category B should be primarily attributed to the fact that under certain conditions, efforts and costs, it can go to category A. It is generally accepted that category B is formed by positions that give the next 15% contribution to category overall after category A.

Category C

What will help : maximize profits by managing stocks and not store money in a warehouse.

How to help: identify and eliminate surplus stocks, implement a system for monitoring and optimizing stocks.

Application of the abs method for stock analysis

ABC analysis is a great tool for inventory control. Its use is useful in determining which of the storage units is most affected by storage costs. It also provides the basis for forming an inventory management strategy.

In fact, each product in the warehouse (or unit of storage of raw materials) generates costs - each storage location costs money: rent on a pallet, utility costs, salaries of warehouse personnel, depreciation of warehouse equipment, etc. Therefore, it is important to keep this source of costs under control.

The use of ABC analysis in inventory management is based on the same principles as the segmentation of customers or goods. We rank warehousing goods or product groups according to the parameters of interest and allocate them into groups according to the contribution to the overall result for the selected parameter. The parameter for this analysis is determined by the objectives:

if the goal is to reduce the use of warehouse space, then it is necessary to rank product categories according to the number of pallet spaces occupied;
if there is a need to evaluate the turnover of goods, then shipments to customers should be taken as a parameter;
information on the most quickly used stocks of raw materials can be very useful when the topic of “volume discounts” begins to appear in the procurement issue and then we analyze the turnover of stocks of raw materials with the parameter “shipment (vacation, write-off) to production”.

There is no standard threshold in abc analysis to determine which products fall into which category. Threshold categories as well as parameters should be determined specifically for the analytical problem. Even the number of categories can be greater than three. However, the basis for creating categories applies the same principles as for analyzing customers / products.

Category A is the category of the most valuable products.
Category B is slightly larger and contains products that are of less value.
Category C is the largest, with the least contribution to the total value of the analyzed parameter.

Here is an example of some threshold indicators for category A:

20% of products, accounting for 70–80% of the annual shipment to customers;
20% of the range of raw materials and materials, which make up 70–80% of the annual shipment to production;
20% of suppliers of raw materials, which account for 70–80% of the annual volume of purchases.

The traditional proportions for the categories - 80/15/5 - can also be used to assess stocks, but this is not an axiom as mentioned above.

In order to concentrate the main business resources on key customers, they conduct ABC XYZ analysis. This is a study of the customer base in two directions: by volume (ABC) and frequency of purchases (XYZ).

What do the letters meanABC XYZ:

Group A - customers with the highest volumes of purchases
Group B - counterparties with medium purchases
Group C - customers with a small volume of purchases

ABC XYZ analysis allows you to understand:

Who pays you more and more often than others;
How many target buyers are in yours;
Which customers need to concentrate on?

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

ABC XYZ Analysis: Product Line Research

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

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

Product segmentation by the indicated groups can be carried out according to other criteria, depending on the objectives of the study.

2. Reduction in assortment. This is a classic study on volume purchases and profitability. As a result, goods / services that appear in categories C or X will be eliminated. 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 storage space.

ABC XYZ Analysis: Buyer Migration

But simply ABC XYZ analysis is not enough. Further, it is important to control the migration of clients from category to category. Those. You should know how many customers you have in categories A, B, C, how they move between them. As well as how they move along the XYZ axis.

How to track migration in the B2B segment

In order to stimulate migration to category “A”, you need to measure the share in the client and make a survey on 3 important issues through phone calls:

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

These 3 questions immediately give you a huge field in order to expand your share in the client. Record all answers and consider them when conducting ABC XYZ analysis. Calculate your share in these customers and analyze how you can increase it.

How to track migration in the B2C segment

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

You can collect the base, see how many customers order in relation to this data. Based on this, calculate your share in the client and plan how much you can add. And further 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 they do now. Find out the reason for the decrease in purchases.

ABC XYZ analysis: stock holding

Abc Xyz analysis: measurement of results

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

Therefore, you need to constantly work here on the current base, and on the new base, and on those who fell into group A. Then you will see a completely different dynamic in your business.

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

We examined the main points of the ABC XYZ analysis, what results it gives, and how to work with these data. Perform ABC XYZ analysis in your company and focus all your resources on attracting and servicing buyers from the AX category.

One of the key management and logistics methods is ABC analysis. With its help, you can classify the resources of the enterprise, goods, customers, etc. by degree of importance. At the same time, according to the importance level, each of the above units is assigned one of three categories: A, B or C. Excel has in its luggage tools that make it easier to conduct this kind of analysis. Let's figure out how to use them, and what constitutes ABC analysis.

ABC analysis is a kind of improved and adapted to modern conditions version of the Pareto principle. According to the methodology of its implementation, all elements of the analysis are divided into three categories according to the degree of importance:

Category A - elements having in aggregate more than 80% specific gravity;
Category B - elements whose combination is from 5% before 15% specific gravity;
Category C - the remaining elements, the total combination of which is 5% and less specific gravity.

Some companies apply more advanced techniques and break down elements into 3 or 4 or 5 groups, but we will rely on the classical ABC analysis scheme.

Method 1: sorting analysis

In Excel, ABC analysis is performed using sorting. All items are sorted from largest to smallest. Then, the cumulative specific gravity of each element is calculated, based on which a certain category is assigned to it. Let's find out, using a specific example, how this technique is applied in practice.

We have a table with a list of goods that the company sells, and the corresponding amount of revenue from their sale for a certain period of time. At the bottom of the table, the total revenue for all items of goods is hit. The task, using ABC analysis, is to divide these products into groups according to their importance for the enterprise.

Select the table with the data cursor, holding the left mouse button, excluding the header and the final row. Go to the tab "Data". Click on the button. "Sorting" "Sort and filter" on the tape.

You can also do differently. Select the above range of the table, then move to the tab "Home" and click on the button "Sort and filter"located in the tool block "Editing" on the tape. A list is activated in which we select a position in it. Custom Sorting.

When applying any of the above actions, the sorting settings window is launched. We look so that around the parameter “My data contains headers” a check mark has been set. In case of its absence, install.
In field "Column" indicate the name of the column containing the revenue data.

In field "Sorting" you need to specify by what specific criterion the sorting will be performed. We leave the predefined settings - "Values".

In field "Order" set a position "Descending".

After making the specified settings, click on the button OK at the bottom of the window.

After performing the specified action, all elements were sorted by revenue from largest to smallest.

Now we should calculate the specific gravity of each of the elements for the total. We create an additional column for these purposes, which we will call "Specific gravity". In the first cell of this column, put a sign «=» , after which we indicate a link to the cell in which the amount of revenue from the sale of the corresponding product is located. Next, set the division sign ( «/» ) After that, indicate the coordinates of the cell, which contains the total amount of sales of goods throughout the enterprise.
Given the fact that we will copy the specified formula to other cells in the column "Specific gravity" using the fill marker, then we need to fix the address of the link to the element containing the total amount of revenue for the enterprise. To do this, make the link absolute. Select the coordinates of the specified cell in the formula and press the key F4. In front of the coordinates, as we see, a dollar sign appeared, which indicates that the link has become absolute. It should be noted that the link to the revenue value of the first item in the list ( Product 3) must remain relative.

Then, to make calculations, click on the button Enter.

As you can see, the proportion of revenue from the first product listed in the list was displayed in the target cell. To copy the formula to the range below, put the cursor in the lower right corner of the cell. It transforms into a fill marker that looks like a small cross. Click the left mouse button and drag the fill marker down to the end of the column.

As you can see, the entire column is filled with data characterizing the share of revenue from the sale of each product. But the specific gravity is displayed in a numerical format, and we need to transform it into a percentage. To do this, select the contents of the column "Specific gravity". Then we move to the tab "Home". On the ribbon in the settings group "Number" There is a field displaying the data format. By default, if you did not perform additional manipulations, the format should be set there "General". We click on the icon in the form of a triangle located to the right of this field. In the list of formats that opens, select the position "Percentage".

As you can see, all the column values \u200b\u200bwere converted to percentage values. As expected, in line "Total" indicated 100% . The proportion of goods is expected to be in the column from larger to smaller.

Now we should create a column in which the accumulated share with a cumulative total would be displayed. That is, in each row, the specific gravity of a particular product will add the specific weight of all those products that are located in the list above. For the first item in the list ( Product 3) the individual specific gravity and the accumulated share will be equal, but for all subsequent ones, the accumulated share of the previous element of the list will need to be added to the individual indicator.
So, in the first row we move to the column "Accumulated share" column indicator "Specific gravity".

Next, set the cursor to the second cell in the column. "Accumulated share". Here we have to apply the formula. We put a sign "equally" and add the contents of the cell "Specific gravity" same row and cell contents "Accumulated share" from the line above. We leave all links relative, that is, we do not manipulate them. After that, click on the button Enter to display the final result.

Now you need to copy this formula into the cells of this column, which are located below. To do this, use the fill marker, which we already resorted to when copying the formula in the column "Specific gravity". In this case, the line "Total" no need to capture, since the accumulated result in 100% will be displayed on the last item from the list. As you can see, all the elements of our column were filled after that.

After that we create a column "Group". We will need to group products into categories A, B and C according to the indicated accumulated share. As we remember, all elements are distributed into groups according to the following scheme:
- A - before 80% ;
- B - the following 15% ;
- FROM - the remaining 5% .
Thus, for all goods, the accumulated share of the specific gravity of which is included in the border until 80% assign category A. Goods with a specific gravity of 80% before 95% assign category B. The remaining product group with a value greater than 95% accumulated specific weight assign category C.

For clarity, you can fill these groups with different colors. But this is optional.

Thus, we divided the elements into groups according to the level of importance, using ABC analysis. When using some other techniques, as was already mentioned above, splitting into a larger number of groups is used, but the principle of splitting remains almost unchanged.

Method 2: use a complex formula

Of course, the use of sorting is the most common way to perform ABC analysis in Excel. But in some cases, it is necessary to conduct this analysis without rearranging the rows in the original table. In this case, a complex formula will come to the rescue. As an example, we will use the same source table as in the first case.

Add to the original table containing the name of the goods and the proceeds from the sale of each of them, a column "Group". As you can see, in this case we can not add columns with the calculation of individual and cumulative shares.

Select the first cell in the column "Group"and then click on the button "Insert function"located near the line of formulas.

Activation in progress Function Wizards. We move to the category Links and Arrays. Select function "CHOICE". Click on the button OK.

The function argument window is activated. SELECTION. Its syntax is presented as follows:
SELECTION (Index_Number; Value1; Value2; ...)

The objective of this function is to output one of the indicated values, depending on the index number. The number of values \u200b\u200bcan reach 254, but we need only three names that correspond to the categories of ABC analysis: A, B, FROM. We can immediately enter in the field "Value1" symbol "A", in field "Value2" — "B", in field "Value 3" — "C".

But with an argument "Index Number" you have to thoroughly tinker with it by integrating a few additional operators into it. Set the cursor in the field "Index Number". Next, click on the icon in the form of a triangle to the left of the button "Insert function". A list of recently used operators opens. We need a function SEARCH. Since it is not in the list, then click on the inscription "Other functions ...".

The window starts up again. Function Wizards. Again we move to the category Links and Arrays. Find a position there “SEARCH”, select it and click on the button OK.

SEARCH. Its syntax is as follows:
SEARCH (Searched_value; Viewed_array; Match_type)

The purpose of this function is to determine the position number of the specified element. That is, just what we need for the field "Index Number" the functions SELECTION.

In field Viewed Array You can immediately specify the following expression:

It should be in curly brackets, as an array formula. It’s not hard to guess that these numbers ( 0 ; 0,8 ; 0,95 ) indicate the boundaries of the accumulated share between groups.

Field Match Type optional and in this case we will not fill it out.

In field "The sought value" set the cursor. Then again through the above pictogram in the form of a triangle we move to Feature Wizard.

This time in Function wizard move to category "Mathematical". Choose a name SUMMS and click on the button OK.

The function argument window starts SUMMES. The specified operator sums the cells that meet a specific condition. Its syntax is:
SUMMES (range; criterion; sum_range)

In field "Range" enter the column address "Revenue". For these purposes, set the cursor in the field, and then, holding the left mouse button, select all cells in the corresponding column, excluding the value "Total". As you can see, the address was immediately displayed in the field. In addition, we need to make this link absolute. To do this, select it and press the key F4. The address stood out with dollar signs.

In field "Criterion" we need to set a condition. We enter the following expression:

Then immediately after it we enter the address of the first cell of the column "Revenue". We make the horizontal coordinates at this address absolute by appending a dollar sign from the keyboard in front of the letter. We leave the vertical coordinates relative, that is, there should not be any sign in front of the digit.

After that, do not click on the button OK, and click on the name of the function SEARCH in the formula bar.

Then we return to the function argument window SEARCH. As you can see, in the field "The sought value" appeared data set by the operator SUMMES. But that is not all. Go to this field and add the sign to the existing data. «+» without quotes. Then we enter the address of the first cell of the column "Revenue". And again, we make the horizontal coordinates of this link absolute, and leave them vertical relative.

Like last time in the run Function wizard looking for the desired operator in the category "Mathematical". This time, the desired function is called "SUM". Select it and click on the button. OK.

Operator Argument Window Opens SUM. Its main purpose is to summarize data in cells. The syntax for this statement is pretty simple:
SUM (Number1; Number2; ...)

For our purposes only a field is needed "Number 1". Enter the coordinates of the column range in it. "Revenue"excluding the cell that contains the totals. We already performed a similar operation in the field "Range" the functions SUMMES. As at that time, we make the coordinates of the range absolute by selecting them and pressing the key F4.

After that, click on the button OK at the bottom of the window.

As you can see, the complex of introduced functions performed the calculation and returned the result to the first cell of the column "Group". The first product was assigned a group "A". The full formula that we used for this calculation is as follows:
SELECTION (SEARCH ((SUMMES ($ B $ 2: $ B $ 27; "\u003e" & $ B2) + $ B2) / SUM ($ B $ 2: $ B $ 27); (0: 0.8: 0.95)) ; "A"; "B"; "C")

But, of course, in each case, the coordinates in this formula will be different. Therefore, it cannot be considered universal. But, using the guidance given above, you can insert the coordinates of any table and successfully apply this method in any situation.

However, this is not all. We performed the calculation only for the first row of the table. In order to completely populate a column with data "Group", you need to copy this formula to the range below (excluding the row cell "Total") using the fill marker, as we have done more than once. After the data has been entered, the ABC analysis can be considered completed.

As you can see, the results obtained using the option using a complex formula do not differ at all from the results that we carried out by sorting. All products are assigned the same categories, but the lines have not changed their starting position.

Excel can greatly facilitate the ABC analysis for the user. This is achieved using a tool such as sorting. After this, the individual specific gravity, the accumulated share and, in fact, the division into groups are calculated. In cases where changing the initial position of the rows in the table is not allowed, you can apply the method using a complex formula.