The method is based on the Pareto 20/80 principle. Yes, yes, the same one that has been sounding “from every iron” for many years, but this does not lose its effectiveness. When applied to this method, it can be formulated, for example, as follows:

Only 20% of any goods, customers, etc. bring 80% of all company profits.

But how do you determine these 20% of the stars? This is what ABC sales analysis is for. It allows you to identify leaders and focus your main efforts on them.

As a result of the analysis of goods using this method, the following groups can be distinguished:

And, which includes no more than 20% of positions, but they bring from 70 to 90% of income;
B, in which the middle peasants are concentrated, that is, about 30% of positions, giving about 20% of revenue;
C is the largest group, which usually contains about 50% of all goods sold.

The purpose of the ABC analysis is to prioritize quantitative indicators and focus efforts on working with them.

These figures are not the ultimate truth and may vary depending on the specifics of the business. Still, there are invariably favorites that help him stay afloat, and outsiders that take a lot of effort, but bring a minimum of profit.

If the indicators obtained as a result of the ABC analysis differ by 10-15% from those listed above, this is a permissible deviation. As a rule, the more objects are involved in the analysis, the closer the results are to the classical distribution parameters.

What is XYZ Analysis

What is ABC analysis we have analyzed, but what does XYZ have to do with it? This method is often used in addition to the first and together they create a synergistic effect.

XYZ analysis is a method for determining the variance of an indicator. That is, it can be used to determine how much the value tends to deviate from the average values.

For example, when conducting an XYZ analysis of sales in terms of demand for goods, three categories can be distinguished:

X with a fluctuation from 0 to 10%, that is, goods that are always purchased in approximately the same quantity;
Y, where the variation is 10 to 25%. These are products that have a relatively stable demand, but are subject to fluctuations, for example, according to the season;
Z is a group where fluctuations in demand can range from 25% and more. In this case, it can be called random and it is very difficult to predict anything.

The purpose of the XYZ analysis is to determine the fluctuations in the value relative to the average value and to take into account the level of instability of the indicator in the further development of the business.

Such diversification is extremely justified, because if a product fell into group B by average revenue, but sold well only in December and January (for example, Christmas tree decorations), it would be strange to bet on it in July. The indicators do not always look so obvious and visible to the naked eye, but the XYZ analysis method allows you to identify such fluctuations in any case.

Advantages and disadvantages

Joint ABC XYZ analysis has the following advantages:

Wide range of applications. It can be used to calculate sales metrics, identify key customers, product demand, salespeople's contribution to teamwork, and more.
Simplicity. The calculations are easy and do not require ten analysts – you can really do it yourself. All you need is a spreadsheet in Excel or any other similar program. There are CRM systems that automatically calculate the specified indicators. If configured correctly, all that remains is to click a button to export the data in the desired format.
Efficiency. This is basic research and should be repeated from time to time in every organization.

Like any other method, ABC XYZ analysis has its drawbacks:

Again, simplicity. It does not provide an opportunity to compose a comprehensive picture.
You should not use it as a direct guide to action, for example, immediately part with customers who make rare purchases or get rid of goods with random demand. Additional analysis by other methods is often required.

Using ABC XYZ Analysis

There are many areas of application of ABC XYZ analysis in business:

Profit. In particular, you can consider various factors that affect it;
Demand for goods or services and its fluctuations;
Warehouse balances with their subsequent optimization;
Clients and their division into large, medium and small by the volume of purchases made;
Deliveries, e.g. supplier analysis;
Internal analysis. If the performance of the team is quantified, for example in the sales department, you can measure the contribution of each salesperson to the fulfillment of the plan;
Accounts receivable and its classification.

End-to-end analytics

Automatically collect data from advertising sites, services and CRM into convenient reports
Analyze your sales funnel from impressions to ROI
Set up integrations with CRM and other services: more than 50 ready-made solutions
Optimize your marketing with detailed reports: dashboards, graphs, charts
Customize tables, add your own metrics. Build reports instantly for any periods

What questions does the research answer?

The ABC and XYZ techniques can be used to analyze the following factors:

assortment of goods (we analyze the profit);
target customer base (analyzing the volume of orders);
supplier bases (we analyze the volume of supplies);
debtors (we analyze the amount and dynamics of debt).

Who pays you more and more often than others

The ABC analysis of the customer base can be carried out by the revenue they bring to the business:

A – large clients.
B – average clients.
C – small clients.

There is no single standard of which clients can be attributed to groups A, B or C. Such a division depends primarily on the size of the business under study. The amount defining a large customer for a small retail store can be 200,000 rubles, and in a large wholesale trade, income will be measured in millions. It is the process of conducting the analysis that will lead to the determination of which clients are assigned to which of the categories.

XYZ analysis answers the question of which customers make purchases regularly, which ones do it from time to time, and who bought the product only once.

How many target customers are in your sales funnel

As a widespread marketing analysis tool in all types of trade (retail, wholesale, online), so-called sales funnels are used, the main idea of which is that the transaction process always consists of separate stages. The sales funnel reflects the distribution of customers according to the stages of growth of their usefulness for the seller: from a potential buyer to the conclusion of the first deal, and then the customer's transition to the status of a permanent, loyal and even campaigning for the use of a particular brand.

The concept of a sales line is based on the fact that there are many potential buyers, but a minority reaches the stage of concluding a deal.

ABC analysis shows how many potential customers reach the level of the transaction, who they are, how they learned about the company, which manager worked with him.

Thanks to the clear structure, the analysis of the sales funnel allows you to plan the development of the trading process, monitor the efficiency of personnel, and motivate employees.

Which buyers should you focus on?

An important criterion for the priority position of the client should be the receipt of a high income from him, moreover, it is rather stable, and not one-time. The combination of ABC and XYZ analysis is most effective here. As a result, groups of potential customers are identified with whom you can use different communication methods:

relationship marketing, loyalty programs – for a small but most profitable group of regular customers;
maintaining constant contacts – when the client is ready to spend large sums on purchases, but rarely does it;
researching needs, expanding the range – for those who make expensive purchases in an unpredictable manner.

COUNT, DO NOT COUNT

Completed the theoretical block, learned the basics. We can proceed to the calculation itself. And I have two news for you.

Bad – I will not give you formulas for calculating. Good – I will give you a ready-made excel-table for implementation.

Therefore, I will explain the main meanings, and the calculation in Excel will be done independently when you enter the numbers.

1 Gathering information

If you do not have a purchase history, then you are reading this article for the future, since almost any analysis is done on the basis of data for the past period.

Therefore, for the successful implementation of these two analyzes, you need to collect information. Depending on the goal, they can be different, but I am showing you almost non-replaceable leaders of any table:

Clients / Products
Sales amount
Number of sales
Marginality

You collect this data for a selected period of time. The longer it is, the better. Since you will see the dynamics of the company.

But I only recommend analyzing separately every six months or a year, due to the fact that companies are growing and if you take the average value over several years, you can get incorrect results.

2 Data analysis

After you have collected all the information together, the most boring work begins – all this needs to be disbanded according to the necessary columns.

This process is not fast, but nevertheless it does not require special knowledge. Of course, you can go a smarter way and immediately make your CRM system download such a ready-made report. As they say, everything is in your hands. The main thing is to have a result.

Analysis integration

And only then, when everything is in its place, you can begin to draw conclusions on the column, which is called “combined”.

It is he who will show you the synergy between ABC analysis and XYZ analysis. As you may have guessed, the best customers and products for you are AX (they buy a lot and often), and the worst ones are CZ / DZ (they buy little and rarely / unprofitable).

Decide for yourself what to do with each group. And you don't have to conclude that you need to remove “bad” customers and products.

For example, we categorically forbade our client to remove goods that do not bring money.

Since we know that by removing these products, the client will be forced to buy them from a competing company, which means that we lose full contact with him and the competitor can imperceptibly drag him to the main (marginal) product

3 Upgrade and migration

Your company is developing, your managers, products, conditions are changing. Everything changes except you – the leader ;-).

Therefore, you need to constantly update the database and look at the situation in your company at a given point in time. The interval of changes depends directly on the speed of changes in the market.

The same goes for clients. In addition to the fact that new ones constantly come to you, the old ones also change.

Some grow to the scale of federal companies and their purchases become similar to the Ural mountains (large and large-scale), while others, on the contrary, become bankrupt, since they could not cope with competitors and new marketing trends.

Therefore, you need to constantly update your analytics and monitor how some clients migrate from group to group.

It will also be an indicator of how well you are doing the right job with clients. After all, their purchasing power also depends on how you work with them.

Client migration

To assess a client's potential to migrate to a “nicer” group, you need to follow two strategies.

In the B2B market, you need to ask your customers what and how much they buy from competitors (+ what they would like to buy).

In the B2C market, you just need to understand how much a customer takes from you (maybe he takes 50% from you and 50% from a competitor), based on his norm.

Why you shouldn't use Excel for ABC XYZ analysis

It is often offered to make calculations of ABC XYZ analysis in Excel. Indeed, the functionality of spreadsheets allows calculations. But when it comes to a real business with a huge range, many points of sale, a developed partner network, the use of Excel only complicates business processes.

Problems start immediately:

Imagine you need to do ABC XYZ analysis for at least 50,000 SKUs. Are you sure you want to do this manually?
With a large amount of data, even opening the file will take time. What can we say about calculations.
Excel only allows 1,048,576 rows. If there is more data, then it doesn't matter how fast it will work.
The problem with opening large files is a fairly common occurrence in Excel. It is associated with the use of computational functions, conditional formatting, the creation of large data sets and pivot tables, etc.
Reuse and reporting is difficult due to slowdowns and large amounts of data. After every action you have to wait.

The points listed above are enough to draw a conclusion: a more advanced tool is needed for real calculations.

Principles of ABC and XYZ Analysis

Business process planning today is impossible to imagine without a preliminary study of the market characteristics and product classification. Making decisions in the field of marketing and logistics requires a competent approach. ABCXYZ analysis is an example of a popular technique for optimizing these processes. It is possible to apply this tool without involving a team of experts.

• The essence of ABC analysis

The ABC component is based on the Pareto principle, which is based on the idea that a business gets 80% of the result from 20% of its efforts. Having identified this 20% as a result of the analysis, it is important to focus on them the main attention.

The point of research using the ABC method is to classify processes, goods and services of the same type at first glance. Among these objects, a key group stands out, working with which brings the business the greatest result. Using this tool, you can adjust the assortment, improve customer service and increase sales.

According to the results of ABC analysis, 3 groups of objects are distinguished:

A – 20% of the most valuable resources (it is this group that gives 80% of the results);
B – 30% of average resources in terms of value, their share in the overall result – 15%;
C – 50% of the resources that give only 5% of the results.

ABC analysis allows you to rank resources depending on their value to the business.

• The essence of XYZ analysis

Unlike the ABC component, the XYZ task is to determine the stability of demand for a group of goods or services. This method is used to determine the effectiveness of sales and the stability of customer requests. The grouping of business objects occurs depending on the uniformity and seasonality of the distribution of sales.

ABC analysis identifies the most profitable product categories, and the XYZ component adds information about the stability of demand for them.

In the case of selling services, customers are ranked:

X is a category of customers who consistently and regularly place orders (the sales forecast for this group is quite accurate).
Y – the category of customers who place orders on an irregular basis.
Z – category of customers who made an order once.

• Purpose of research

ABC and XYZ analysis methods are applicable to optimize the following business components:

assortment of products (analysis of the profitability of positions);
target customer categories (analysis of the base by the volume of orders);
suppliers (analysis of the volumes of supplied products);
debtors (analysis of amounts and dynamics of debts).

By doing ABC analysis, you can identify customers who paid you more than others. An XYZ analysis will show how stable their orders are.

Features of the combined ABC and XYZ analysis

ABC and XYZ analysis of customers in the framework of sales optimization allows you to determine the contribution of specific customers to the company's profit. The method includes ranking according to several indicators: the cost of production, its quantity and the stability of demand.

Based on the analysis results, the customer base is divided into two vectors:

ABC categories – depending on the volume of sales;
XYZ categories – depending on the frequency of transactions.

ABCXYZ analysis is carried out according to one of two schemes: classical or modern. In the first case, the ABC-component in the classification is fully consistent with the Pareto principle. The customer base is divided into three categories depending on the volume of sales, following the rule “20% of the effort gives 80% of the results”:

A – clients providing 80% of sales due to a large volume of purchases.
B – clients providing 15% of sales due to average purchase volumes.
C – Clients with small purchase volumes, providing only 5% of sales.

The modern scheme, in contrast to the classical one, assumes the division of category A into two for more detailed elaboration. It turns out 4 groups for comparison:

A – 50% of the total sales;
B – 30% of the total sales;
С – 15% of the sales volume;
D – customers giving only 5% of total sales.

The latter category of customers is most often removed from the comparison. This includes those who no longer shop. If the client has gone to a competitor, it makes sense to classify him as “new” when resuming cooperation in 6-12 months.

The following distribution of customers by categories within the ABC analysis is considered to be the benchmark:

Category	% of the total	% of income received
BUT	twenty	80
B	sixteen	fifteen
C	64	five
Outcome	100	100

This distribution is considered the most balanced. Deviations in one direction or another will indicate exactly how to adjust the sales pattern to improve results.

The point is that any differences from the reference schema are indicative of shortcomings in the sales department. Managers do not consider product potential or pay little attention to customers.

If the percentage of the total number of customers in a category is overestimated, consider that some of these customers can already move to the next group. If the indicator is understated, look for a problem in low segment coverage and shortage of buyers.

Based on the analysis results, you can understand whether it makes sense to allocate a separate employee to work with VIP clients. This subgroup is distinguished from category A if the profit on their orders is more than 10 times higher than the same indicator at the top of the B-category.

The key tasks of ABCXYZ analysis are, in addition to ranking customers by sales volume, to identify patterns of demand depending on the industry. The XYZ component is responsible for forecasting purchases, on the basis of which the calculation of the regularity of sales is made.

Based on the ABCXYZ analysis, a portrait of priority customers is drawn up. Knowing such customers in person, you can provide them with a USP that completely covers their needs. It is important for sales managers to understand which segments they should work with first and how to do it as efficiently as possible.

Based on the results of ABCXYZ analysis, the following conclusions can be drawn:

On the current state of the customer base. It is important to identify deviations from the reference values. If they are, then they are markers of problems with the organization of the sales process.
On the strategy of maintaining key clients. We identify the top categories of buyers who make 80% of the company's profits, and then analyze the approach applied to them. It is important to try to transfer existing developments to category B in order to increase sales and bring a number of customers to the top list.
On the effectiveness of sales methods. The analysis helps you see the strengths and weaknesses of your sales strategy.
About mistakes in working with clients. By reducing the share of clients in one or two categories, one can draw conclusions about ineffective working methods. It is important to correct the sagging moments in time and avoid the repetition of such situations in the future.

Conclusions based on the results of ABCXYZ analysis serve as the basis for a full-scale optimization of the sales department.

ABC analysis procedure

After conducting ABC analysis, you will be able to clearly identify the group of the most profitable clients for the company. This will allow you to competently organize such components of business processes as:

allocation and control of the marketing budget (here it is important to take into account the interests of the most valuable customers);
focus of attention of employees of the sales department and other customer services on category A customers;
development and improvement of USP for top clients;
actions to strengthen the company's position in the market.

The ABC part of the analysis is carried out according to the following scheme:

Selecting metrics to rank your existing customer base

In order for the analysis results to be relevant, it is important to prepare the data for work in advance. A situation often arises when the same client is recorded in the database under several different names. It happens that the customer himself uses several legal entities for procurement, but more often the duplication of information occurs due to the disparate input of data into the database. If the organization does not have a single standard for the introduction of the name of the client company, then there is a high probability that duplicates of the same customer will appear. For example, “Firma1”, LLC “Firma1”, TOV “Firma1”. This factor can have a significant impact on the analysis results, so it is important to get rid of duplicates in advance.

Next, you should select one or more indicators, on the basis of which you will classify customers. Most often, the figures of revenue, sales volume, or marginal income are taken as the basis for ranking. In some cases, the percentage of repayment of receivables is estimated. The choice is made in favor of key points in the sales strategy.

It is most convenient to carry out ABCXYZ analysis in Excel, therefore, data on customers and products is uploaded to this software environment. The ABC method involves compiling a unique list of customers and calculating the volume of sales for each of them.

Analysis of the customer base by segment

Most companies work with clients from several segments. Therefore, after the first step of the analysis, it is necessary to determine the contribution of each group of customers to the overall result. Next, you need to consider the contribution of individual customers within the segment.

Usually there are not so many segments with which the company works – more often 2–4, therefore this stage of the analysis does not cause difficulties. First, the revenue share is calculated for each segment, and then the percentage of the sales margins. The first conclusions can be drawn already on the basis of this stage.

Analysis of segments by specific sales criteria

First, a range of categories is set:

BUT	0%	80%
IN	80%	95%
FROM	95%	100 %

In Excel, create a separate worksheet for each sales figure.

Let's consider an example of registration and calculation based on the revenue indicator. The scheme is as follows:

• We enter the data into the table.

• Sorting in descending order by the column with the sales indicator (in our example, by revenue).

• Summarize the total sales (revenue).

• Add the column “Share” and enter in the cell the formula for calculating the share of each client in the total revenue: (volume of revenue per client) / (total revenue) * 100%.

• We add a column “Cumulative total” – here we calculate the cumulative total by customer shares. For example, the share of Client 15 in the revenue is 27.65%, Client 11 is 25.45%, Client 12 is 7.06%. The cumulative total for these three shares will be as follows: Client 15 (27.65%) + Client 11 (25.45%) + Client 12 (7.06%) = 60.16%.

• Now we define the boundaries of the ABC categories. We find in the column with a cumulative total that row, the value of which is as close as possible to 80%. The client from this line is the last one in category A. Next, we are looking for the value of the cumulative total, close to 95% – this is how we set the lower threshold of category B. All clients from the lines below automatically appear in category C. Now you can add a color indication for grouping: green highlight for A, yellow for B, red for C.

At the end, the results for different indicators are summarized in a single Excel table

The next step is to sort by the criterion that is most strategically important. This can be, for example, a revenue figure.

Cumulative analysis for all sales criteria

This is the most visual part of the analysis. When all results are summarized in a color-coded table by category, it is easy to see the real picture and make appropriate management decisions. For example, Client 15 is categorized as A by both revenue and share of profit margin. This is a valuable and profitable customer for the company. And if you compare it with Client 12, you can see that the latter, although he has a large share in the profit, but makes it through private and small orders. In the long term, such a client uses more company resources than the client in the first example.

It is likely that you will see more than 50% of customers in the red C-zone. The company receives less than 5% of its profits from these customers. It is important to make the right management decisions here. After all, this array of clients brings practically nothing to the company, but at the same time takes up the time of your employees and their attention. You can allocate resources more efficiently. The solution in this situation may even be the end of cooperation with some clients from the C-list.

Conclusions on the results obtained and preparation of appropriate system solutions

Any analysis will be wasted if you do not work out the appropriate management decisions based on its results. It is a misconception that when you see half of your customers in the red zone, you should work with them first. After conducting ABC and XYZ analysis, conclusions should be made more strategic. The focus of the entire company should be on the most valuable customers in the ABC analysis, those who already provide the most profit. The most profitable customers among them are those with the highest hourly income (that is, the ratio of the profit earned and the time spent on transactions).

Decisions based on analysis results:

How to efficiently work with key clients to get even more profit and revenue from them?
How to ensure a rational distribution of the marketing budget for the A-segment?
Which clients should the sales managers focus on?
How to expand the “AA” category in terms of profit and revenues at the expense of clients from groups B and A of these categories (for example, B in terms of income and A in terms of revenue)?

The ABC component of the analysis can be applied not only when ranking customers. Use a similar method to classify all of your products, as well as individual categories. This will identify the products with the highest demand and those that provide the most profit. If you apply the ABC methodology to the work of sales managers, procurement of raw materials and components, then you can draw valuable conclusions in each of these areas.

In addition, it is possible to combine the results of the analysis by customer and product, thereby focusing staff efforts not only on key customers, but also on the products that they prefer.

Other uses for ABC analysis

ABC analysis is applicable not only to assortment. We recently did it for a transport company based on revenue. The owner was developing a loyalty program and wanted to know who to include in it. To do this, information was needed how much percent of the revenue each client brings him and how clients are distributed between groups A, B and C.

In this case, the places of goods in the table were taken by customers and the revenue that each of them brings to the business. Such a table will look, for example, like this (all names and indicators are fictitious, possible coincidences with real ones are random).

The name of the company	Revenue, rubles
LLC “Ural Prostory”	300,000
LLC “South Ural Logistics”	500,000
CJSC “Expert Solutions”	100,000
SP. Ivanov I. I.	50,000
IP Petrov P.P.	70,000
SP Sidorov S.S.	30,000
JSC “Fresh products”	200,000
Total	1,250,000

After the ABC analysis, the table will look like this:

The result of the ABC analysis of the customer base by revenue. Group A brings exactly 80% of revenue, B – 13.6%, C – 6.4%

Now the owner knows on which of the clients he makes the most money, which of them is the middle peasant in terms of the revenue that he brings to the business, and who is the outsider.

The business owner will offer the loyalty program to his clients from group A, in the retention of which he is most interested. And clients from group B with the help of the loyalty program will be stimulated to make more orders and move to group A. He continues to work with clients from group C. But it doesn't make sense to offer them participation in the loyalty program.

ABC analysis rules

It is necessary to conduct an ABC analysis for one indicator that can be measured in money. This can be revenue, profit, purchase amount, accounts receivable (everything owed to the business) or accounts payable (everything owed by the business). All objects of ABC analysis should be tied to numbers: how much revenue or profit each product or client brings, how much the business earns from each supplier or how much we buy from each supplier, how many receivables hang on each debtor, how much the business owes each creditor.
The objects of ABC analysis can be individual goods or groups of goods, customer base, supplier base, debtor base, creditors base.
ABC analysis is carried out within the boundaries of one direction. When a business sells cars, parts and repairs cars at the same time, these are three separate areas. It makes no sense to put cars and spare parts in one plate. These are goods of different price categories and frequency of consumption: we change cars every few years, and we buy spare parts for cars much more often. The objects of ABC analysis should have comparable parameters.
Typically, ABC analysis is carried out to adjust strategic business plans. In such cases, it is held once a year, and the data is updated quarterly. But if the goal is to increase the average check, you can use ABC analysis once a month. This approach will allow you to see how management decisions are reflected in the distribution of profits between groups and categories.

Determine the share (contribution) of each position

This action is expressed by dividing the line item amount by the total sales amount.

Calculate the cumulative percentage in a separate column

The cumulative% is calculated cumulatively, starting from the top rows of the table

Assign each item to the appropriate group A, B or C

Select the groups according to the ABC method for each position on the basis of the “Cumulative contribution” column.

All positions from the beginning of the table to the border of 80% – category A
All positions from the 80% border to the 95% border – category B
All positions from the 95% border to the 100% border – category C

ABC analysis of product sales. Stages of the

The stages of the ABC analysis of the nomenclature of goods and sales of the company (enterprise) are as follows:

Determination of the range of products of the enterprise.
Calculation of the rate of return for each product group.
Determination of the effectiveness of each group.
The ranking of goods and their classification (ABC) by value to the enterprise.

Conclusion

ABC analysis is a tool with which you can sort products, customers, debtors and creditors into leaders, middle peasants and outsiders. Find out on whom and what you earn the most, what and whom you can easily refuse, who owes you the most, and to whom you.

Sources used and useful links on the topic: https://blog.calltouch.ru/kak-vam-pomozhet-v-biznese-sovmestnyj-abc-i-xyz-analiz/ https://hiterbober.ru/money-methods/ abc-xyz-analiz.html https://in-scale.ru/blog/abc-i-xyz-analiz-prodazh-i-klientov https://loginom.ru/blog/abc-xyz https: // sales -generator.ru/blog/abc-i-xyz-analiz/ https://Lifehacker.ru/abc-analiz/ http://PowerBranding.ru/biznes-analiz/abc-method/primer-excel/ https: / /finzz.ru/abc-analiz-prodazh-primer-v-excel.html

Post source: lastici.ru