Accounting

ABC customer analysis. Problem on ABC and XYZ analysis

18.10.2020

In this article, we will consider in detail and analyze in practice XYZ sales analysis.

XYZ analysis. Definition

XYZ analysis ­( EnglishXYZ-analysis) is a method of classifying a company's resources into three groups based on their variability (stability). The objects of application of XYZ analysis can be various economic indicators of the company: sales volume, revenue, material costs, number of suppliers, etc. One of the most common areas of application of this method is the identification of goods that have stable demand (sales volumes), seasonal and random. This allows you to optimize inventory and free up additional resources.

XYZ analysis of product range. Calculation formula

XYZ assortment analysis consists of grouping products into three groups based on the variability of their sales over past periods. To carry out the analysis, it is necessary to implement the following sequential algorithm.

At the first stage, the coefficient of variation of sales volume (demand level) is calculated for each product group. Sales variability is assessed using the coefficient of variation. The formula for calculating the coefficient of variation is the ratio of the standard deviation to the mathematical expectation of sales volume. The formula looks like this:

σ – standard deviation of sales volume;

x i – sales volume during the i-th period;

n is the number of periods of product sales under consideration;

x * – arithmetic mean value of product sales.

The meaning of the coefficient of variation is to estimate the percentage deviation of sales volume from the average value. The greater the variation index, the less stable the sales volume of this type of product.

At the second stage, the product range is sorted by the value of the coefficient of variation and the products are classified into three groups - XYZ based on the values ​​of the coefficient of variation. The table below shows the evaluation criterion and economic description for various groups of goods.

Video lesson: “Sales assessment. Example of XYZ analysis in Excel”

Example of XYZ analysis of product sales volumes in Excel

In order to better understand the meaning of XYZ analysis, consider real example. In our case, there is a cell phone store and there are sales volumes of various brands. For the analysis to be applied correctly, it is necessary that the sales period of consideration be at least 4 months.

Product range and sales volume

The coefficient of variation sales volumes=STDEV(B5:G5)/AVERAGE(B5:G5)

Calculation of the coefficient of variation of product sales

Now it is necessary to classify the goods into a group – “X”, “Y” or “Z”. To do this, we will write a formula that determines the class of the product and use the built-in “IF” formula in Excel. The formula will look like this:

Product group=IF(H5<10%;”X”;ЕСЛИ(H5<25%;”Y”;”Z”))

Grouping products by XYZ classes in Excel

To clearly see the structure of sales sustainability, it is necessary to group by class: Excel main menu → “Data” → “Sorting”.

Summary

The XYZ analysis method allows you to predict the stability of demand for products, sales volumes and inventories. Using the method frees up additional company resources and optimizes their business processes.

ABC and XYZ analysis, their combination. Scope of application, rules of conduct, examples of research using Excel.

Modern marketing and logistics are based on the use of a number of globally proven tools. These tools include ABC and XYZ analyzes that help improve business organization. Their combined use is effective for optimizing business processes and does not require large labor costs or the involvement of highly paid experts.

What is ABC analysis

The meaning of ABC analysis can be considered to be the selection in a business from a large number of similar objects those on which you need to focus the main attention based on a specific chosen goal. This method can be used in different directions: to optimize the assortment, analyze the customer base, and increase sales efficiency.

ABC analysis is based on the ideas of Pareto, who claims that in business only 20% of investments always give 80% of the results. It is this segment that he recommends focusing efforts on.

In ABC analysis, business factors are divided into 3 categories:

  • A - the most valuable resources (20%), the result of which in business is 80%;
  • B - 30% of resources giving 15% of the result;
  • C - 50% of resources, of which the result is only 5%.

The essence of ABC analysis is the ranking of resources according to the results they bring.

What is XYZ Analysis

XYZ analysis is a tool for determining the level of stability or variability in sales. It groups business objects based on the uniformity of sales, identifies fluctuations in different time periods and classifies objects according to the level of predictability. The method can be used to analyze sales of individual goods, services or customer behavior.

If ABC analysis highlights the best-selling products, then XYZ helps to understand how stable the demand for them is.

What questions does research answer?

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

  • product range (we analyze profits);
  • target customer base (we analyze the volume of orders);
  • supplier databases (we analyze the volume of supplies);
  • debtors (we analyze the amount and dynamics of debt).

Who pays you more and more often than others?

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

  1. A - large clients.
  2. B - average clients.
  3. C - small clients.

There is no single standard for which clients can be classified as groups A, B or C. This division depends primarily on the scale of the business under study. The amount that defines a large client for a small retail store can be 200,000 rubles, but in a large wholesale trade the income will be measured in millions. It is the process of analysis that will lead to determining which clients belong to which category.

XYZ analysis answers the question of which customers make purchases regularly, which ones occasionally, and who bought the product only once.

How many target buyers are in your sales funnel?

As a widely used 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 transaction, and then the transition of the client to the status of a regular, loyal and even advocating for the use of a particular brand.

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

ABC analysis shows how many potential clients reach the transaction level, who they are, how they found out about the company, which manager worked with them.

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

Which buyers should you focus your efforts on?

An important criterion for the client’s priority position should be the receipt of high income from him, and fairly stable, and not one-time. Here, combining ABC and XYZ analysis is most effective. As a result, groups of potential clients 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 regular contacts - when the client is ready to spend large sums on purchases, but rarely does so;
  • researching needs, expanding the range - for those who make expensive purchases unpredictably.

Video: ABC analysis as a sales tool

Performing ABC Analysis

ABC analysis involves the following sequence of actions:

  • determine the goals of the analysis;
  • identify the objects that we analyze;
  • select a parameter on the basis of which objects will be classified;
  • evaluate each object according to the classification parameter;
  • sort objects in descending order of parameter value;
  • determine the share of the parameter value for all objects;
  • rank the values ​​of the proportion of parameters on an accrual basis;
  • divide objects into three groups according to parameter values ​​(from minimum to 80%, from 80 to 95% and over 95%);
  • determine the number and composition of objects in each group.

ABC analysis is performed step by step in a certain sequence

As an example, let us give an ABC analysis of the client base of the company Alpha LLC. We will use the Excel spreadsheet program as a tool.

Let's perform ABC analysis:

  1. Our goal is to rank clients from the database according to their degree of profitability.
  2. As the object of analysis, we select 20 clients of the company, which we will anonymously designate from Client 01 to Client 20.
  3. As an analysis parameter, consider the amount of purchases of each client for six months.
  4. Let's compare each client with the amount of revenue received from him for the half-year, and create an initial Excel table containing only two columns: A - list of clients, B - revenue for the half-year. We summarize the total revenue in a separate line.
    At the first stage of the analysis, we draw up a table with a list of clients and the amount of revenue for each of them for the six months
  5. Let's sort clients in descending order of revenue for the six months (menu “Data” → “Sorting” → “Descending”).

    The list of clients is sorted by the amount of purchases for the six months using a special Excel tool
  6. Let's determine the share of each client in the company's total revenue for the six months using the formula: Share = (Revenue from the client) / (Total revenue) * 100%. In order not to enter the formula manually each time, we set the percentage cell format for column C, in the first cell (C2) we set the formula =B2/$B$22, and stretch it to the last column.

    Each customer's share of purchases is expressed as a percentage
  7. Let's calculate the savings share for each buyer. The first line duplicates the client's percentage share; in subsequent lines, the value is calculated by summing this share and the percentage share of the current client. Technically, it looks like this: in the second cell of column E, set the formula =C3+E2, stretch to the last row.

    The calculation of the cumulative share is done using the formula =C3+E2
  8. We will receive a list of clients sorted by the cumulative share of each client. For control: the last line (in our case 21) should contain the value 100%.
    Cumulative shares of clients are automatically displayed in ascending order
  9. Let's divide the list reflecting cumulative shares into three groups:
  10. Let's calculate the share of total revenue and the percentage of the total number of clients in each group. In practice, the proportion of objects in groups A, B and C does not always exactly correspond to the theoretical Pareto value. So, a valuable 20% of the client base should be four clients, but according to the results of calculations there were 5 of them, that is, 25%. But according to calculations, it is clear that they provide the company with 80% of revenue. The same is true for group C. This should not be considered a calculation error. According to the laws of statistics, you can get closer to the theoretical result with an increase in the number of objects, for example, if there are not 20, but 500 clients.
    ABC analysis made it possible to identify the 5 most profitable clients from the Alpha LLC database

Performing XYZ Analysis

The XYZ analysis algorithm is constructed as follows:

  1. Select the object and parameter to be analyzed.
  2. Determine the time frame for the study.
  3. Calculate the coefficient of variation for each object.
  4. Rank objects by coefficient of variation.
  5. Distribute objects into 3 groups:
    • X - coefficient of variation from 0 to 10% - the group is characterized by stability;
    • Y - coefficient of variation from 10 to 25% - the group’s behavior is changeable, but predictable;
    • Z - coefficient of variation from 25% - random, one-time nature of the transaction, demand, etc.

Let's perform an XYZ analysis of the client base of Alpha LLC using Excel:

  1. We select the client base as the object of analysis and consider the amount of purchases for each.
  2. Let's determine the period for which we conduct the analysis. This will be six months of the half year considered in the ABC analysis.
  3. We create a table of customers with purchase volumes for each of the selected six months.
    The source table for XYZ analysis includes a list of customers and the amount of their purchases by month
  4. The coefficient of variation is calculated using a complex formula. Its values ​​range from 0 to 1. Excel provides a special tool for this: if data begins to be entered from line 3 (cells B3-G3), in the free column we enter the formula =STDEV(B3:G3)/AVERAGE(B3:G3), stretch to the last line, give the cells a percentage value. In this option, the coefficient will be displayed as a percentage.

    The coefficient of variation can be calculated using a formula, but it is more convenient to use the Excel tool
  5. For convenience, in the table you can calculate the average sales per month for each client and the standard deviation. But for the results of the analysis, the coefficient of variation will be fundamental. At this stage it must be entered on each client's line.

    The coefficient of variation is calculated in a separate column for each client

    6. We sort the table of clients in ascending order by coefficient value (menu “Data” → “Sorting” → “Ascending”). We divide them into 3 groups. Group X will include clients with a coefficient from 0 to 10%, Y - from 10 to 25%, Z - above this value. If there are few objects, instead of sorting, you can manually enter group membership in a separate column.

    XYZ analysis distributes all clients into three groups

  6. Let's summarize the XYZ analysis of the client base of Alpha LLC. Group X included clients who buy consistently, numbering 8 out of 20. Those included in group Y (7 clients) are characterized by fluctuating demand. In group Z (5 clients), demand is almost unpredictable and is more random than regular. We conclude that the behavior of the majority of the company’s clients is stable or predictable.

    XYZ analysis characterizes groups of objects according to their degree of stability

Combining ABC and XYZ analyzes

Combined ABC and XYZ analysis is considered an effective and versatile tool. The method is based on the formation of a single table, where objects of analysis are distributed into nine groups based on the results of ABC analysis and XYZ analysis.

Inventory analysis is not limited to the first letters of the Latin alphabet. ABC analysis is followed by XYZ analysis. It is after it is carried out that the final matrix is ​​compiled, the assessment of which allows us to optimally form stock in the warehouse

Managing commodity resources in any company involves daily analysis of a large amount of information on the history of sales, inventory, deliveries, returns, etc. If you carefully analyze the information for each product, then there is simply not enough working time for this. Therefore, the question is always about which products to analyze daily, and which ones it is enough to check once a week or even a month.

XYZ analysis allows you to answer this and many other questions.

Mathematical tools

For a better understanding of this method of analysis and the results that its application allows us to obtain, it is necessary to recall several formulas from the institute course in statistics.

Firstly, this formula for calculating the standard deviation of a variation series:

The value of the standard deviation allows you to estimate the degree of dispersion of the values ​​of the options relative to the arithmetic mean. The smaller the standard deviation, the closer to the average the values ​​are.

If the standard deviation in the analysis of sales of one product is 15, and that of another product is 30, this means that monthly sales in the first case are closer to the monthly average and they are more stable than in the second. The standard deviation is very widely used in logistics when planning requirements and when calculating safety stocks.

Second the formula is the coefficient of variation:

The coefficient of variation allows you to compare the stability of sales of several products with different sales volumes. A product with average monthly sales of both 200 and 20 thousand units may have a standard deviation of 100. In one case, the significance of monthly fluctuations will be 50%, in another - 0.5%. Obviously, sales of the second product are much more stable and, as a result, more predictable.

Idea of ​​analysis

The main idea of ​​XYZ analysis is to group objects according to the homogeneity of the analyzed parameters, in other words, according to the coefficient of variation.

You can select a product, product group, supplier, etc. as objects of analysis. Then you need to determine the parameter by which the analysis will be carried out. As a rule, the analysis is carried out on product sales or on the shipment of components from the warehouse. The choice of units of measurement in this analysis is not of fundamental importance.

It is very important to correctly determine the frequency of the data that is analyzed. You can conduct an analysis of the daily shipment of goods from the warehouse, but if most of the goods are not shipped every day, and deliveries are made once a quarter, the result will not be sufficiently indicative. Practice shows that the frequency of data should exceed the frequency of deliveries accepted in your company for most of the goods.

Then you need to calculate the coefficient of variation for each product. To do this, it is convenient to use any spreadsheet editor. In MS Excel, in the “statistical functions” section there is function "STDEV" (range of cells), which allows you to calculate the standard deviation over the selected range. The complete formula that must be entered into the cell to calculate the coefficient of variation will look like this:

STDEV (cell range) / AVERAGE (cell range)

An example of determining product groups when conducting XYZ analysis is presented in table 1. Pay special attention to the presence of zeros in the cells. If there were no sales in one of the periods and there is a zero in the cell, this cell is still taken into account (product 8). If the cell is left empty, the number of periods for which the calculation is made will be automatically reduced (product 6). This is very convenient when analyzing a large number of product items. If the product appeared during the period for which the analysis is being carried out, you can leave the cells empty, and then the calculation will be made only for those periods where there are values.

The next step is to group products according to the coefficient of variation.

  • Group X includes products with a coefficient of variation of less than 10%.
  • Group Y includes products with a coefficient of variation from 10% to 25%.
  • Group Z includes products with a coefficient of variation of more than 25%.

The most common of them is seasonality of sales. The seasons when sales changes occur are known and taken into account in advance when planning the company's work.

Table 1. Conducting an XYZ analysis of product sales data for the first half of the year

Sales volume, pieces Average Standard Coefficient XYZ
Product July August 1 September October November December sales deviation variations
per month
Product 1 90 120 120 180 250 200 160,0 55,1 34% Z
Product 2 150 164 154 152 148 169 156,2 7,7 5% X
Product 3 250 222 255 286 262 288 260,5 22,5 9% X
Product 4 800 858 774 752 792 761 789,5 34,8 4% X
Product 5 100 92 102 101 130 BY 105,8 12,0 11% Y
Product 6 0 272 267 324 262 271 279,2 22,7 8% X
Product 7 1500 1401 1721 1320 1692 1604 1539,7 146,8 10% X
Product 8 0 272 267 324 262 271 232,7 106,1 46% Z
Product 9 4000 4550 4753 4704 4434 4766 4534,5 266,5 6% X
Product 10 200 120 90 140 150 160 143,3 34,0 24% Y

The seasonal coefficient is equal to the ratio of the calculated seasonal trend in a given month to the average value of the seasonal trend.

Taking into account seasonal fluctuations.

The following steps can be taken to analyze data for products that have significant seasonal variations. The simplest thing is to change the group boundaries. The step is indeed the simplest, but, alas, not the most effective, since seasonal fluctuations are only one of the reasons for instability.

A more correct and effective action would be to isolate the seasonal component from the actual data. All the company’s products must be divided into groups that have similar seasonal sales dynamics. Then, for each group, you need to determine the seasonal trend and calculate the seasonal coefficients for each seasonal trend. These coefficients are determined by dividing the sales value of each month by the average sales value for the entire period (according to the seasonal trend). Then you need to divide the actual sales values ​​by the seasonal coefficient.

As a result, we will receive the sales volume of the product without taking into account seasonal fluctuations. Now you can perform XYZ analysis on the obtained data. From the example given in Table 2, it can be seen that after excluding the seasonal factor from sales of product 1, the coefficient of variation decreased to 12%.

The estimated seasonal trend is the sales forecast value for a given month. If forecasting is not used, then you need to take the average of sales in this month for the three previous years. The seasonal coefficient is equal to the ratio of the calculated seasonal trend in a given month to the average value of the seasonal trend. The non-seasonal sales value is obtained by dividing the actual data for the month by the seasonal coefficient for that month.

Thus, the use of XYZ analysis allows us to divide the entire assortment into groups depending on the stability of sales. Based on the results obtained, it is advisable to carry out work to identify and eliminate the main reasons affecting the stability and predictability of sales. When performing a comprehensive analysis of the state of the commodity resource management system, it is most productive to combine the results of ABC and XYZ analyzes.

Table 2. Isolation of seasonal components based on data on actual sales of goods:

Product Sales volume, pieces Average Standard Coefficient
July August September October November December sales deviation variations
per month
Product 1, actual data 90 120 120 180 250 200 160,0 55,1 34%
Estimated seasonal trend 100 120 150 200 220 180 161,7 42,6 26%
Seasonal coefficient 0,62 0,74 0,93 1,24 1,36 1,11 1,0 0,3 26%
Product 1, not seasonally adjusted 146 162 129 146 184 180 157,6 19,5 12%

Combination of ABC and XYZ analyses.

First, an ABC analysis of goods is carried out based on the amount of income received or the amount of goods shipped for the entire accounting period (for example, a year). Then an XYZ analysis of these products is carried out for the entire same period (for example, monthly sales for the year). After this, the results are combined.

OH AY AZ
VX BY BZ
CX C.Y. CZ
  • Products of groups A and B provide the main turnover of the company. Therefore, it is necessary to ensure their constant availability. It is a generally accepted practice that excess safety stock is created for goods of group A, and sufficient safety stock for goods of group B. Using XYZ analysis allows you to more accurately configure the inventory management system and thereby reduce the total inventory.
  • Products of groups AX and BX distinguished by high turnover and stability. It is necessary to ensure constant availability of goods, but for this there is no need to create excess safety stock. The consumption of goods in this group is stable and well predicted.
  • Products of the AY and BY groups with high turnover, they have insufficient stability of consumption, and, as a result, in order to ensure constant availability, it is necessary to increase the safety stock.
  • Products of the AZ and BZ groups with high turnover, they are characterized by low predictability of consumption. An attempt to ensure guaranteed availability for all goods of a given group only through excess safety inventory will lead to the fact that the company's average inventory will increase significantly. For products in this group, the ordering system should be reviewed. Some goods need to be transferred to an order system with a constant order amount (volume), for some goods it is necessary to ensure more frequent deliveries, select suppliers located close to your warehouse (and thereby reduce the amount of safety inventory), increase frequency of control, entrust work with this group of goods to the most experienced manager of the company, etc.
  • Products of group C make up up to 80% of the company’s product range. The use of XYZ analysis can greatly reduce the time that a manager spends on managing and monitoring the products of this group.
  • By products of the CX group You can use an ordering system with constant frequency and reduce safety inventory.
  • By products of the CY group you can switch to a system with a constant amount (volume) of the order, but at the same time create a safety stock based on the financial capabilities of the company.
  • To product group CZ all new goods, goods of spontaneous demand, supplied to order, etc. are included. Some of these goods can be painlessly removed from the assortment, and the other part needs to be regularly monitored, since it is from the goods of this group that illiquid or hard-to-sell inventories arise, from which the company incurs losses. It is necessary to remove from the assortment the remains of goods taken on order or no longer produced, that is, goods usually classified as stock.

If you hire a new and inexperienced employee, then by entrusting him with working with AZ group products, you risk incurring losses during the period when he is gaining the necessary experience. If you entrust him with goods from the CX group, then, after working for a year, he will learn to press three buttons on a computer and send orders to the supplier. If you entrust him with the goods of the CZ group, then he will quickly gain experience, and the company will not suffer much from his experiments, and you do not need to control his every step.

Advantages of the combined method.

So, using a combined ABC and XYZ analysis will allow you to:

  • increase the efficiency of the commodity resource management system;
  • increase the share of highly profitable goods without violating the principles of assortment policy;
  • identify key products and reasons influencing the number of goods stored in the warehouse;
  • redistribute staff efforts depending on qualifications and experience.

And this is not a complete list of the advantages that are realized through the use of the described combined method.



Golubkov E.P.,
Honored Scientist of the Russian Federation,
d.e. Sc., Professor of the Academy of Economy under the Government of the Russian Federation

Methodological issues of conducting ABC and XYZ analysis and combining the results of these two types of analysis are considered. The areas of application of ABC and XYZ analysis are indicated, their advantages and disadvantages are noted.

1. Guidelines for conducting ABC analysis
ABC-analysis is an analysis of the assortment, sales volume to various consumer groups, and inventory by dividing them into three categories (classes), which differ in their significance and contribution to the turnover or profit of the enterprise: A- the most valuable, IN- intermediate, WITH- least valuable(1).

ABC-analysis, regardless of the scope of its application (manufacturing enterprises, wholesale trade or retail enterprises), is carried out in the following sequence.

1. Selection of the object of analysis (we determine what we will analyze - assortment group/subgroup, product range as a whole, suppliers, customers). It is possible to detail the areas of analysis by sales channels and market segments.

2. Determination of the parameter by which the object will be analyzed - average inventory, rub.; sales volume, rub.; income, rub.; number of sales units, pcs.; number of orders, pcs., etc.

Finding a single parameter that uniquely reflects the position of the analyzed goods is a difficult task. This choice depends on a number of factors: type of enterprise, turnover rate, seasonality of demand, etc. As a result, empirically you can try to use different parameters and even identify groups ABC based on the sequential application of several parameters, say, the number of orders shipped, income, number of sales units. As a result, integral groups can be distinguished A, B, C. Previously, the entire possible set of analysis parameters can be ranked according to their importance to select the most preferable ones. For example, the paper provides the following arguments in favor of the choice of evaluation parameters. In a pharmacy they can buy 100 packages of dietary supplements of the brand in a month X and 150 packages of dietary supplements brand Y.

(1) Abbreviation ABC has another interpretation: ABC - activity based costing- operationally oriented cost accounting. In Russian terminology - functional-cost analysis. The main object of management in this approach is recognized not as organizational and production systems, but as the operations performed by them.

It would seem that we need to focus on Y, since more of them were purchased. However, 150 packages of dietary supplements of the brand Y was purchased by only 6 customers - 5 people bought 10 pieces and one bought 100 packs. Dietary supplement brand X 10 people bought 10 packs each.

If you focus on pieces as a significant parameter, you can easily make mistakes when planning purchases. After all, this one client (who bought 100 dietary supplements at once Y) might not appear, and the likelihood that the same customer with the same amount of purchased will appear in the next time period is very low. Conclusion: you cannot focus only on the number of packages. Focusing on the fact of sale guarantees greater accuracy in purchasing.

The cited work proposes a two-factor model ABC-analysis, in which profit and the number of sales facts are used as parameters. Profit is given preference over turnover, mainly due to the fact that many goods are sold at different markups, and accordingly, the income (profit) generated is different. Then each product item is assigned only one index. The first letter of the index is the index assigned by profit; the second is an index assigned according to the number of sales facts.

4. Definition of groups A, IN And WITH.
To determine whether a selected object belongs to a group, you must:

  • determine the value of the parameter (say, sales volume) for the selected units of the object of analysis (for example, for each assortment position of the selected assortment group);
  • calculate the value of the parameter for the selected units as a cumulative total by adding the value of the parameter to the sum of previous estimates, that is, determine the share of the parameter in the total estimate;
  • assign group names to selected objects.
  • Group A- objects, the sum of shares with a cumulative total of which is the first 50% of the total sum of parameter values.
  • Group IN- following the group A objects, the sum of shares with a cumulative total of which is from 50 to 80% of the total sum of parameter values.
  • Group WITH- the remaining objects, the sum of shares with a cumulative total of which ranges from 80 to 100% of the total sum of parameter values.

Sometimes other percentages are specified, such as group A- 15% of reserves, B- 20%, C- 65%.

As a development of the idea of ​​the classical ABC- analysis in the work, it is proposed to introduce a fourth group - illiquid assets, unclaimed products that do not generate income and freeze the working capital of the enterprise.

Deeper mathematical approaches to identifying groups A, B, C discussed in the works.

Having grouped a product according to one parameter, compare the result obtained with estimates based on other parameters. Group WITH can generate 20% of revenue, account for 50% of inventory, and occupy 80% of warehouse space. For example, ABC-analysis of products by sales volume shows which products provide 80% of the company’s turnover. Analyze the same products, but by the number of units (or the number of orders for them) and as a result you will get 20% of the goods purchased by 80% of customers, and this is already attractive for the customer and the company’s turnover. When creating a methodology ABC-analysis used the principle of the outstanding economist Pareto, later named after him. While studying the economic life of Italy, Pareto in 1906 expressed the opinion that 80% of the wealth of Italian society is controlled by 20% of social capital. Towards ABC-analysis, the Pareto principle may sound like this: reliable control of 20% of positions allows for 80% control of resources, be it stocks of raw materials and components, or the product range of an enterprise, or its clientele, or assortment positions of a trading enterprise, or warehouse stocks, etc.

The same result can be used when planning the placement of goods in a warehouse or on the sales floor of a store. Analyzing products by income will show you where you make money. A similar analysis of costs will allow you to understand where the money is spent.

At the same time, it is important to remember that ill-considered reduction of group products WITH(20% of the company's income) will lead to the fact that after some time the remaining goods are distributed according to the same law, but the overall result of your activities for the company may decrease by 50%.

Frequency ABC- analysis depends on a number of factors, and above all on the duration of the life cycle of the product of a given trade group, the seasonality of sales, and the influence of environmental factors. The frequency of the event is selected individually for each trading group. In particular, for trading enterprises in relatively stable environmental conditions ABC-analysis can be carried out once on the first working day of the new month following the analyzed period. ABC-analysis must be carried out over a period of 1 or 2 months, which will allow smoothing out to some extent fluctuations in seasonality, shortfalls, etc.

Data can be taken not for the last month, but for the last six months, taking into account in this way the influence of factors that go beyond one month. At the same time, with less frequent ABC- analysis, say, quarterly, you can miss some important factors and, for example, be left without a profitable product during the season.

to the group A it is necessary to pay special attention and constantly use control (monitoring) and planning procedures. Small changes in profitability, turnover, and prices for this group can lead to significant changes in the financial performance of the enterprise. As a result, daily monitoring of group products is possible A, especially when the technology for conducting such analysis has been established.

Regarding groups IN And WITH, then it makes no sense to analyze these positions every day assortmentally. However, to create the appearance of variety in the assortment, it is advisable to have several assortment items available for each group.

results ABC- analysis for certain categories of analysis, it is advisable to supplement it with the analysis “sales volume - contribution to covering costs (sales revenue minus all variable costs)”. This analysis can be carried out to assess the effectiveness of both individual market segments and trading enterprises purchasing goods from manufacturers.

2. Guidelines for conducting XYZ analysis
This analysis allows for the classification of goods based on a comparison of the stability of their sales volume. The purpose of the analysis is to predict the stability of certain objects of study, for example, the stability of sales of certain types of goods, fluctuations in the level of demand.

At the core XYZ-analysis involves determining the coefficients of variation (ν) for the analyzed parameters. The coefficient of variation is the ratio of the standard deviation to the arithmetic mean value of the measured parameters.

Where xi - parameter value for the assessed object for i- th period; - average value of the parameter for the analyzed object of analysis; n- number of periods.

The square root value is nothing more than the standard deviation of the variation series. The larger the standard deviation value, the further away the analyzed values ​​are from the arithmetic mean. If the standard deviation in the analysis of sales of one product is 15, and that of another product is 30, this means that monthly sales in the first case are closer to the monthly average and are more stable than in the second. If the standard deviation is 20, then with an arithmetic mean of 100 and 100,000 it will have a significantly different meaning. Therefore, when comparing variation series with each other, the coefficient of variation is used. Coefficients of variation of 20 and 0.2% make it clear that in the second case, the values ​​of the analyzed parameters differ significantly less from the arithmetic mean.

XYZ-analysis is carried out in the following sequence.

1. Definition of the object of analysis: client, supplier, product group/subgroup, product item, etc.

2. Determination of the parameter by which the object will be analyzed: average inventory, rub.; sales volume, rub.; income, rub.; number of sales units, pcs.; number of orders, pcs., etc.

Most often, sales values ​​are used for analysis. Inventory is the result of many factors. The stock in the warehouse can significantly depend on the established frequency of deliveries, on the size of the minimum or maximum batch provided by the supplier, on the availability of warehouse space. In any case, the choice of parameter for analysis is best done experimentally, comparing the results obtained when using different parameters.

First two steps XYZ-analysis coincide with the same steps for ABC-analysis.

3. Determination of the period and number of periods for which the analysis will be carried out: week, decade, month, quarter/season, half-year, year.

The frequency of analysis for each enterprise is purely individual. Periodicity XYZ-analysis should be longer than the period from the moment the product is ordered until it is received by the customer. The greater the number of periods, the more indicative the results will be. If for the St. Petersburg chain of companies "Moidodyr" monthly sales were taken for analysis, then almost all products fell into the category Z. But when studying the figures for the quarter, everything fell into place, and X, And Y. As a result, the company abandoned monthly plans and switched to quarterly ones.

Another example. Analysis of sales of milk and bread in a retail store can be carried out based on the amount of sales per week. Deliveries are made every day, sales too. But if we compare the sales of milk and Absolut vodka (which is ordered once a month and sells 1 bottle every 2 weeks), then during this period 99% of the store’s assortment will fall into the category Z, 1% - in category Y. It turns out that we can conclude that we are working in extreme conditions in an unpredictable market. Therefore, in this case, it is advisable to analyze monthly sales.

The analysis of sales and inventory in companies selling household appliances, building materials, spare parts for cars, etc. has special features. The financial plan in a company is often drawn up for a month, but the actually necessary planning horizon should be for six months. Analyzing data with a period shorter than a quarter simply does not make sense. All products fall into the category Z. Using XYZ- analysis, we must remember the reliability of the results obtained, which increases with the increase in the amount of information used. Based on this, the number of periods under study should be at least three.

Seasonality can greatly influence the results of calculations. Here's a typical case. The enterprise is informed about the increase in seasonal demand, the necessary stock of goods is purchased or produced. But due to sales surges, the product moves into the category Z. In this case, it is advisable to act as when launching a new product: compare the deviation of sales for the analyzed period from the forecast. In this case, the accuracy of planning is assessed.

To analyze data on products that have significant seasonal fluctuations, a more correct and effective action would be to isolate the seasonal component from the actual data. All the company’s products must be divided into groups that have similar seasonal sales dynamics. Then, for each group, you need to determine the seasonal trend and calculate the seasonal coefficients for each seasonal trend. These coefficients are determined by dividing the sales value of each month by the average sales value for the entire period (according to the seasonal trend). Then you need to divide the actual sales values ​​by the seasonal coefficient. As a result, we will obtain the sales volume of the product without taking into account seasonal fluctuations. The seasonal trend is the sales forecast value for a given month. If forecasting is not used, then you need to take the average of sales in this month over the previous three years. Now you can carry out XYZ-analysis based on the received data. From what is given in table. 2 examples show that after excluding the seasonal factor from sales of product 1, the coefficient of variation decreased to 12%.

4. Using the above formula, the coefficient of variation is determined for each object of analysis.

5. Grouping of objects of analysis in accordance with the increase in the coefficient of variation of parameters.

6. Definition of groups X, Y And Z. Tabular and/or graphical presentation of the results obtained (Fig. 1 and Table 3).

In the classic version XYZ- analysis when optimizing the range of goods for a category X include products characterized by a stable sales volume, minor fluctuations in their sales and high forecast accuracy. The coefficient of variation value ranges from 0 to 10%.

At the same time, it should be noted that empirically, taking into account the specifics of the scope of application of this method, objects and parameters of analysis, it is possible to establish other gradations of categories X, Y, Z. For example, for the category X the range 0-15% can be selected, for the category Y- 16-50%, and for the category Z- 51-100%.

XYZ-analysis is of interest to distributors and manufacturers with their own warehouses. Any purchase is associated with large costs for the company (logistics, storage, etc.), as well as with direct risks, for example, the write-off of goods due to their expiration date. Maintaining accurate, balanced purchasing is a priority for both wholesale and retail enterprises.

Applying XYZ- analysis regarding your clients, you can build a sales forecast for future periods, develop special programs for regular loyal (not subject to various surges in orders) clients, as well as carry out measures to transfer clients from groups Y, Z to the group X.

Thus, the application XYZ-analysis allows you to divide the entire assortment into groups depending on the stability of sales. Based on the results obtained, it is advisable to carry out work to identify and eliminate the main reasons affecting the stability and accuracy of sales forecasting. In a comprehensive analysis of commodity resource management, the most productive combination of results ABC- And XYZ-analysis.

3. Combining the results of ABC and XYZ analysis
To combine the results obtained, we build a combined matrix. The simplest combination option is to sort both files with analysis results by the index field, then copy the column with groups from one file to another. Better of XYZ V ABC, since the actual value of the share of an object’s turnover has more practical meaning than the coefficient of variation.

As a result of this combination of two indicators - the degree of influence on the final result ( ABC) and stability/predictability of this result ( XYZ) - we get 9 groups of objects of analysis (Fig. 2).

In table Figure 4 provides characteristics of goods and individual items of the assortment policy for different cells of the combined matrix.

Products groups A And IN provide the main turnover of the company. Therefore, it is necessary that they are constantly available. It is a generally accepted practice when, for goods in a group A excess safety stock is created, and for group goods IN- sufficient. Usage XYZ- analysis allows you to develop a more accurate assortment policy and thereby reduce the total inventory.

Products group OH And VX distinguished by high turnover and stability. It is necessary to ensure constant availability of goods, but for this there is no need to create excess safety stock. The consumption of goods in this group is stable and well predicted.

Products group AY And BY with high turnover, they have insufficient sales stability, and, as a result, in order to ensure their constant availability, it is necessary to increase the safety stock.

Products group AZ And BZ with high turnover, they are characterized by low sales predictability. An attempt to ensure guaranteed availability for all goods in a given group only through excess safety inventory will lead to the fact that the company's average inventory will increase significantly. The ordering system for products in this group should be reviewed. Some goods need to be transferred to an ordering system with a constant order amount (volume), for some goods it is necessary to ensure more frequent deliveries, select suppliers located close to your warehouse (and thereby reduce the amount of safety inventory), increase the frequency of control, entrust work with this group of products to the most experienced manager of the company, etc.

Products group WITH make up up to 80% of the company's product range. Application XYZ-analysis allows you to greatly reduce the time that a manager spends on managing and monitoring the products of this group.

By product group CX You can use an ordering system with constant frequency and reduce safety inventory.

By product group C.Y. you can switch to a system with a constant amount (volume) of the order, but at the same time create a safety stock based on the capabilities available to the company.

To product group CZ all new goods, goods of spontaneous demand, supplied to order, etc. are included. Some of these goods can be painlessly removed from the assortment, and the other part needs to be regularly monitored, since it is from the goods of this group that illiquid or hard-to-sell inventories arise, from which the company incurs losses. It is necessary to remove from the assortment the remains of goods taken on order or no longer produced.

In table Figure 5 shows an example of combining results ABC- And XYZ-analysis.

The conjoint analysis matrix can also be used to rationalize the use of employee labor. Products category AX must be serviced by the most experienced and qualified employees, and the group of products falling into the category CZ, can be trusted to beginners. It will be easy for them to work with a category where orders occur less frequently, tolerances for deviations are higher, and only the amount spent on a given product item for a certain period is strictly limited. If you hire a new and inexperienced employee, then assign him to work with group products AZ, you risk incurring losses during the period when he gains the necessary experience. If you entrust him with the goods of the group CX, then, after working for a year, he will learn to press keys on a computer and send applications to the supplier. If you entrust him with the goods of the group CZ, then he will quickly gain experience, and the company will not suffer much from his experiments, and you do not need to control his every step.

So, using the combined ABC- And XYZ-analysis will allow:

  • increase the efficiency of the commodity resource management system;
  • increase the share of highly profitable goods without violating the principles of assortment policy;
  • identify key products and reasons influencing the number of goods stored in the warehouse;
  • redistribute staff efforts depending on their qualifications and experience.

TO merits The analysis methods considered include the following.

  1. ABC-analysis allows you to simply and clearly study a large set of economic data. This method of analysis has received great development due to its versatility and efficiency. It can be used both in the activities of wholesale and retail trade enterprises, and in the activities of organizations that produce goods and services.
  2. results ABC-analysis allow us to further rationalize assortment management activities. It is simpler and easier to control and maintain an assortment of 20 positions than 100. Especially when these 20 positions provide 80% of the profit. As a result, it is only necessary to conduct, say, daily assortment and quantitative control of the availability of goods belonging to the group A. At the same time, not only profitable products are identified, but also products that are in high demand, often cheap.
  3. ABC-analysis allows you to make a fairly quick, but at the same time effective assessment of the state of affairs in the warehouse, and allows you to rationally solve inventory management issues.
  4. Regular comparison of new and old ABC-index allows you to see how many positions (up or down the classification) the product moved. The result of this classification is the ability to see which products are increasingly popular (are in the growth stage according to the stages of the product life cycle), and which are in the decline phase.
  5. Application ABC-analysis helps solve the problems of segmenting consumers, studying demand, choosing effective marketing tools, and rational use of employee labor.

At the same time, the following can be noted flaws these methods.

  1. Possibility of joining a group WITH new products. Difficulties arise in the case of a dynamically changing situation, for example, when introducing a new product to the market (the analogues of which the company has not yet traded) or a one-time acquisition of some product items. When the number of sales of a new product increases weekly, XYZ-analysis will not yield anything, the product will inevitably fall into the “unstable” group Z.
  2. XYZ-analysis is meaningless and for enterprises working on order, they simply do not need such forecasts.
  3. In market segments in which the spread of daily sales values ​​over the course of a month can be 50% or more, the use of XYZ-analysis may be useless, since all products will fall into the category Z.
  4. How ABC- analysis and XYZ-analysis is focused on their use in relatively stable environmental conditions. Crisis events, significant changes in exchange rates, changes in the competitive situation, etc. sharply reduce the predictive value of the results obtained.

Especially it concerns XYZ- analysis, since even in a stable situation, making predictive conclusions based on data for 3-5 time periods must be done with great caution. It should also be recognized that the actual value of the share of turnover of an object has more practical meaning than the coefficient of variation.

Despite the noted shortcomings ABC- And XYZ-analysis are a modern marketing tool, the combined use of which with other methods of analysis helps to solve issues of assortment and pricing policy, selection of market segments and sales channels, inventory management, and increasing the efficiency of using marketing communications tools.

Literature
1. ABC-analysis // http://www.abc-analysis.ru/
2. Afanasyev S.V. Triangle method in FBC analysis / S.V. Afanasyev //Marketing in Russia and abroad. - 2007. - No. 2.
3. Bodryakov Roman. Seminar on ABC and XYZ / Roman Bodryakov // http://www.rombcons.ru/ABC_XYZ.htm/
4. Bodryakov Roman. ABC- And XYZ-analysis: compilation and analysis of the final matrix / Roman Bodryakov // http://www.loglink.ru/massmedia/analytics/record/?id=275/
5. Two-factor ABC-analysis according to the method of P.V. Greek // Remedium.ru/
6. Dibb S. A practical guide to marketing planning / S. Dibb, L. Simkin, J. Bradley. - St. Petersburg: Peter, 2001.
7. Oblakov P.O. To the article “Triangle method in FBC analysis” / P.O. Oblakov // Marketing in Russia and abroad. - 2008. - No. 2.
8. Khamlova Olga. ABC-analysis: methodology / Olga Khamlova // Company management. - 2006. - No. 10.
9. http://www.sf-online.ru/
10. XYZ analysis (scenario) // http://www.4p.ru/index.php?page=17601#/

Also on this topic.


Before you begin the analysis, you need to determine its purpose, what information you want to obtain. Then we determine what data you are going to analyze (object of analysis). The objects of analysis can be:

  1. product groups;
  2. buyers;
  3. suppliers;
  4. sales managers.

The next important point is the determination of the parameters by which the analysis will be carried out. After all, each of the objects of analysis listed above has different parameters of description and measurement.

After completing the steps described above, you can begin performing the analysis. The system analyzes the selected parameter for the period you specify using the data accumulated in the information base. Based on this analysis, the ABC classification will be built in the future.

How to do ABC analysis?

  1. Determine the purpose of the analysis

    Determination of success criteria. ABC analysis can accomplish one of two main goals: reduce purchasing costs or increase cash flow by having the right items available for production or direct sale to customers.

  2. Collect data for analysis

    The most common data usually found in standard accounting is the annual cost per item, including all ordering costs and freight costs if they can be easily calculated.

  3. Sort inventory by importance in descending order

    At the very least, ranking each warehouse item by cost.

  4. Calculate the total effect

    The electronic report calculates the cumulative impact of an inventory item list by dividing the annual cost by the total annual inventory and then adding that amount to the cumulative total percentage spent.

  5. Divide inventory into categories

    You may not get the exact 80/20 ratio that the Pareto principle requires. Focus on the big picture and don't try to follow the 80/20 rule exactly. The goal is to find areas where renegotiating contracts, consolidating suppliers, changing methodology, or implementing e-procurement could provide significant cost savings or ensure more inventory is available.

  6. Analyze products in categories and make decisions accordingly

    The key to this step is follow-up and tracking. Once strategic cost management is implemented on a category basis, periodic review is critical to monitoring the success or failure of decisions.