Xyz analysis of commodity items. ABC-XYZ analysis. ABC XYZ Analysis: Product Line Study

Golubkov E.P.,
Honored Worker of Science of the Russian Federation,
e. PhD, Professor of the Academy of National Economy under the Government of the Russian Federation

Methodological issues of ABC- and XYZ-analysis and combination of 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 ABC-analysis
ABC-analysis is an analysis of the assortment, sales volume to various consumer groups, inventory by dividing them into three categories (classes), which differ in their significance and contribution to the turnover or profit of the enterprise: BUT- most valuable AT- intermediate, FROM- the least valuable (1).

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

1. The choice of the object of analysis (we determine what we will analyze - the assortment group / subgroup, the range as a whole, suppliers, customers). It is possible to detail the directions of analysis by distribution channels, market segments.

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

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

(1) Abbreviation ABC has another meaning: 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 operations performed by them.

It would seem that one should focus on Y because more have been purchased. However, 150 packages of dietary supplements of the brand Y was bought by only 6 buyers - 5 people bought 10 pieces and one bought 100 packs. dietary supplement brands X 10 people bought - 10 packs each.

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

The cited paper 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 with different margins, respectively, and the income (profit) generated is different. Then each item is assigned only one index. The first letter of the index is the index assigned for profit; the second is an index assigned by the number of sales facts.

4. Definition of groups BUT, AT and FROM.
To determine whether a selected object belongs to a group:

• 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 with a cumulative total by adding the value of the parameter to the sum of the previous estimates, that is, determine the share of the parameter in the total estimate;
• assign group names to selected objects.

• Group BUT- objects, the sum of shares with a cumulative total of which is the first 50% of the total sum of parameter values.
• Group AT- following the group BUT objects, the sum of shares with a cumulative total of which is from 50 to 80% of the total sum of parameter values.
• Group FROM- the remaining objects, the sum of shares with a cumulative total of which is from 80 to 100% of the total sum of parameter values.

Sometimes other percentages are indicated, such as the group A- 15% stocks, B- 20%, C- 65%.

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

Deeper Mathematical Approaches to Group Extraction A, B, C considered in the works.

After grouping the product by one parameter, compare the result with the ratings based on other parameters. Group FROM can bring 20% ​​of income, make up 50% of the inventory and occupy 80% of the warehouse area. For example, ABC- analysis of goods by sales volume shows which goods 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 client and the company's turnover. When creating a methodology ABC-analysis used the principle of the outstanding economist Pareto, later named after him. Studying the economic life of Italy, Pareto in 1906 expressed the opinion that 80% of the welfare of Italian society is controlled by 20% of social capital. Towards ABC For analysis, the Pareto principle can sound like this: reliable control of 20% of positions allows you to control resources by 80%, whether it be 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 in trading floor shop. An analysis of goods by income will show what you earn money from. A similar cost analysis will help you understand where the money is being spent.

At the same time, it is important to remember that the ill-conceived reduction of the goods of the group FROM(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 activity for the company may decrease by 50%.

Frequency ABC-analysis depends on a number of factors, and above all on the duration life cycle goods of this trade group, seasonality of sales, influence of factors external environment. The frequency of holding is selected individually for each trading group. In particular, for trade enterprises in relatively stable environmental conditions ABC- analysis can be carried out once on the first business day of the new month following the analyzed period. ABC-analysis must be carried out for a period equal to 1 or 2 months, which will smooth out to some extent fluctuations in seasonality, short deliveries, 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, stay in the season without a profitable product.

group BUT should be given Special attention, constantly use control (monitoring) and planning procedures. Small changes in profitability, turnover, prices for this group can lead to significant changes in financial indicators enterprises. As a result, daily monitoring of the products of the group is possible. A, especially when the technology for conducting such an analysis is debugged.

As for the groups AT and FROM, then it makes no sense to analyze these positions by assortment every day. However, to create the appearance of assortment diversity, it is desirable to have several assortment positions for each group.

results ABC- analysis for certain categories of analysis, it is advisable to supplement 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 trade enterprises that purchase goods from manufacturers.

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

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

where xi - parameter value for the evaluated object for i- th period; - the average value of the parameter for the evaluated object of analysis; n- number of periods.

The value of the square root is nothing but the standard deviation of the variation series. The larger the value of the standard deviation, the further 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 they are more stable than in the second. If the standard deviation is 20, then with arithmetic mean values ​​of 100 and 100,000, this will have a significantly different meaning. Therefore, when comparing variation series with each other, the coefficient of variation is used. The coefficients of variation of 20 and 0.2% make it possible to understand that in the second case, the values ​​of the analyzed parameters differ much less from the arithmetic mean.

XYZ-analysis is carried out in the following sequence.

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

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

Most often for the analysis cost indicators of sales are used. 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 lot provided by the supplier, on the availability of storage space. In any case, the choice of a parameter for analysis is best done experimentally, comparing the results obtained using different parameters.

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

3. Determination of the period and the 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 of ordering the goods 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 network of companies "Moydodyr" sales for the month were taken for analysis, then almost all goods 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 plans.

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

The analysis of sales and inventory in companies trading in household appliances, building materials, car spare parts, etc. Financial plan in a company, it is often compiled for a month, but the really necessary planning horizon should be for six months. Analyzing data with a period less than a quarter simply does not make sense. All products fall into the category Z. Using XYZ- analysis, we must remember about the reliability of the results, which increases with an increase in the amount of information used. Based on this, the number of periods under study should be at least three.

Seasonality can seriously affect the result of calculations. Here is a typical case. The enterprise is informed about the increase in seasonal demand, the necessary stock of goods has been purchased or produced. But due to surges in sales, the product goes into the category Z. In this case, it is advisable to act as when launching a new product: to compare the deviation of sales for the analyzed period from the forecast. In this case, the accuracy of planning is evaluated.

To analyze data on goods with significant seasonal fluctuations, it would be more correct and efficient to extract the seasonal component from the actual data. All products of the company should be divided into groups with 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 ratios 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 factor. As a result, we will get the volume of sales of goods without seasonal fluctuations. The seasonal trend is the value of the sales forecast for given month. If forecasting is not applied, then you need to take the average value of sales this month for the previous three years. Now you can carry out XYZ- analysis according to the received data. From the table. 2 examples show that after the exclusion of the seasonal factor from the sales of product 1, the coefficient of variation decreased to 12%.

4. According to 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 representation of the obtained results (Fig. 1 and Table 3).

In the classic version XYZ-analysis when optimizing the range of goods to the category X refers to goods characterized by a stable amount of sales, slight fluctuations in their sales and high precision forecast. The value of the coefficient of variation is in the range from 0 to 10%.

At the same time, it should be noted that empirically, taking into account the specifics of the scope 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 a range of 0-15% can be selected, for 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 high costs for the company (logistics, storage, etc.), as well as direct risks, such as write-off of goods by expiration date. Maintaining an accurate balanced purchase is a priority for both wholesalers and retailers.

Apply XYZ-analysis of your customers, you can build a sales forecast for future periods, develop special programs for permanent loyal (not subject to various bursts of orders) customers, and also carry out activities to transfer customers from groups Y, Z to the group X.

Thus, the application XYZ-analysis allows you to divide the entire range 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 causes that affect the stability and accuracy of sales forecasting. At complex analysis management of commodity resources the most productive combination of results ABC- and XYZ-analysis.

3. Combining the results of ABC and XYZ analysis
To combine the obtained results, we build a combined matrix. The simplest combination is to sort both files with the results of the analysis by the index field, then copy the column with the groups from one file to another. The best of XYZ in ABC, since the actual value of the object's turnover share 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 the stability/predictability of this result ( XYZ) - we get 9 groups of objects of analysis (Fig. 2).

In table. 4 characterizes the goods and individual positions of the assortment policy for different cells of the combined matrix.

Group products BUT and AT provide the main turnover of the company. Therefore, it is essential that they are constantly available. It is a common practice when for the goods of the group BUT an excess safety stock is created, and for the goods of the group AT- sufficient. Usage XYZ-analysis allows you to develop a more accurate assortment policy and thereby reduce the total inventory.

Group products OH and VX distinguished by high turnover and stability. It is necessary to ensure the constant availability of goods, but for this it is not necessary to create an excess safety stock. The consumption of goods in this group is stable and well predicted.

Group products AY and BY with a 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.

Group products AZ and BZ with high turnover, they are characterized by low predictability of sales. Attempting to ensure guaranteed availability for all products in a given group with excess safety stock alone will result in a company's average stock being significantly increased. For the goods of this group, the ordering system should be reviewed. Some goods need to be transferred to a system of orders with a constant order amount (volume), for some goods it is necessary to ensure more frequent deliveries, choose suppliers located close to your warehouse (and thereby reduce the amount of insurance inventory), increase the frequency of control, entrust work this group of goods to the most experienced manager of the company, etc. .

Group products FROM account for up to 80% of the company's product range. Application XYZ-analysis can greatly reduce the time that the manager spends on managing and controlling the goods of this group.

By product group SH you can use a system of orders with a constant frequency and reduce the safety stock.

By product group CY you can switch to a system with a constant amount (volume) of the order, but at the same time form an insurance stock based on the company's capabilities.

To product group cz all new goods, goods of spontaneous demand supplied on order, etc. fall into the category. Some of these goods can be painlessly withdrawn from the assortment, while the other part must be regularly monitored, since it is from the goods of this group that illiquid or hard-to-sell goods arise. inventory from which the company incurs losses. It is necessary to withdraw from the assortment the remains of goods taken under the order or no longer produced.

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

The combined analysis matrix can also be used to rationalize the use of employee labor. Category products AX must be served by the most experienced and qualified employees, and a group of goods that fall into the category cz, can be trusted to beginners. It will be easy for them to work with a category where orders are less frequent, tolerances for deviations are higher and only the amount spent on a given heading per item is strictly limited. certain period. If you hire a new and inexperienced employee, then by entrusting him with the work with the goods of the group AZ, you risk incurring losses during the period when he gains the necessary experience. If you entrust him with the goods of the group SH, then, after working for a year, he will learn to press the keys on the 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:

• improve 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 the reasons that affect the amount of goods stored in the warehouse;
• redistribute the efforts of personnel depending on their qualifications and experience.

To virtues The analyzed methods of analysis include the following.

1. ABC-analysis allows you to simply and visually study a large set of economic data. This method of analysis has been greatly developed 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 producing goods and services.
2. results ABC-analysis allows further rationalization of assortment management activities. It is easier and easier to control and maintain an assortment of 20 positions than 100. Especially when these 20 positions give 80% of the profit. As a result, it is only necessary to conduct, say, daily assortment and quantity control of the availability of goods belonging to the group BUT. At the same time, not only profitable goods are identified, but also goods that are in high demand, often cheap ones.
3. ABC-analysis allows you to make a fairly fast, but at the same time effective evaluation the state of affairs in the warehouse, 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 in the classification) the product moved. The result of this classification is the ability to see which products are growing in popularity (are in the growth phase of the product life cycle), and which are in the decline phase.
5. Application ABC-analysis helps to solve the problems of segmenting consumers, studying demand, choosing effective marketing tools, rational use labor of employees.

At the same time, the following limitations these methods.

1. Opportunity to join a group FROM new products. Difficulties arise in the event of a dynamically changing situation, for example, when a new product is introduced to the market (which the company has not yet traded with analogues) or a one-time purchase of some commodity items. When the number of sales of a novelty increases weekly, XYZ- analysis will not give 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 where the spread of daily sales values ​​during the month can be 50% or more, the application XYZ-analysis may be useless, since all products will fall into the category Z.
4. How ABC analysis, and XYZ-analysis focused on their use in relatively stable environmental conditions. crisis events, significant changes 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, it is necessary to draw predictive conclusions based on data for 3-5 time periods with great care. It should also be recognized that the actual value of the object's turnover share has more practical meaning than the coefficient of variation.

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

Literature
1. ABC-analysis // http://www.abc-analysis.ru/
2. Afanasiev S.V. Triangle method in FBC-analysis / S.V. Afanasiev //Marketing in Russia and abroad. - 2007. - No. 2.
3. Bodryakov Roman. Seminar on ABC theme and XYZ / Roman Bodryakov // http://www.rombcons.ru/ABC_XYZ.htm/
4. Bodriakov 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 "The triangle method in FBC-analysis" / P.O. Clouds // Marketing in Russia and abroad. - 2008. - No. 2.
8. Khamlova Olga. ABC-analysis: methodology / Olga Khamlova // Management of the company. - 2006. - No. 10.
9. http://www.sf-online.ru/
10. XYZ analysis (script) // http://www.4p.ru/index.php?page=17601#/

Also on this topic.

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

XYZ analysis. Definition

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

XYZ analysis of the product range. Calculation formula

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

At the first stage, the coefficient of variation in sales volume (level of demand) is calculated for each commodity group. Sales volatility is estimated 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. The formula looks like this:

σ – standard deviation of sales volume;

x i - sales volume in the period in the i-th period;

n - the number of considered periods of sales of goods;

х * – arithmetic mean value of sales of goods.

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

At the second stage, the product range is sorted by the value of the coefficient of variation and the goods 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 different product groups.

Video lesson: “Evaluation of sales. An example of XYZ analysis in Excel”

An example of XYZ analysis of sales volumes of products in Excel

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

Product range and sales volume

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

Calculation of the coefficient of variation in sales of goods

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 goods, and use the built-in formula "IF" in Excel. The formula will look like this:

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

Product grouping by XYZ class in Excel

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

Summary

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

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

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

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

Once you've completed the steps above, you're ready to begin your analysis. The system analyzes the selected parameter for the period you specified according to the data accumulated in the infobase. 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

   Definition of success criteria. ABC analysis can serve one of two main purposes: reduce purchasing costs or increase cash flow by having the right products available for production or directly for sale to customers.

2. Collect data for analysis

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

3. Sort stocks by importance in descending order

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

4. Calculate the total effect

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

5. Divide stock into categories

   You may not get the exact 80/20 ratio that the Pareto principle requires. Focus on the big picture and don't aim for the exact 80/20 rule. The aim is to find areas where renegotiating contracts, consolidating suppliers, changing methodology, or implementing e-procurement can lead to significant cost savings or more inventory.

6. Analyze products in categories and make decisions accordingly

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

The analysis of reserves is not limited to the first letters of the Latin alphabet. ABC analysis is followed by XYZ analysis. It is after it is completed that the final matrix is ​​compiled, the evaluation of which allows you to optimally form the stock in the warehouse

Inventory management in any company involves the 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 simply will not be enough working time. Therefore, there is always the question of which products to analyze daily, and which are enough to check once a week or even a month.

XYZ analysis provides answers to this and many other questions.

Mathematical Toolkit

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

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

The value of the standard deviation allows us to estimate the measure of dispersion of the values ​​of the options relative to the arithmetic mean. The smaller the standard deviation, the closer the values ​​are to the 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 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 standard deviation equal to 100 can have a product with average monthly sales of both 200 and 20 thousand pieces. In one case, the significance of monthly fluctuations will be 50%, in the other - 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.

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

It is very important to correctly determine the periodicity of the data being analyzed. It is possible to carry out an analysis on 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 adopted 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, under "statistical functions" there is STDEV function (cell range), which allows you to calculate the standard deviation for the selected range. The full formula that needs to be entered in a cell to calculate the coefficient of variation would look like this:

STDEV (cell range) / AVERAGE (cell range)

An example of determining product groups when conducting an 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 the cell contains zero, 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 (item 6). This is very convenient when analyzing a large number of commodity 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 the products according to the value of the coefficient of variation.

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

The most common of these is the 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. Carrying out XYZ-analysis of the data on sales of goods for the six months

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

Accounting for seasonal fluctuations.

The following steps can be taken to analyze data for commodities with significant seasonal fluctuations. The simplest thing is to change the boundaries of the groups. 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 extract the seasonal component from the actual data. All products of the company should be divided into groups with 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 ratios 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 factor.

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

Estimated seasonal trend is the value of the sales forecast for a given month. If forecasting is not applied, then you need to take the average value of sales this month for the previous three 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 unseasonally adjusted sales value is obtained by dividing the actual data for the month by that month's seasonal factor.

Thus, the use of XYZ-analysis allows you to divide the entire range 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 causes that affect the stability and predictability of sales. In 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 the seasonal component based on the actual sales data of the product:

Product	Sales volume, pieces						Medium	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%
Item 1, seasonally adjusted	146	162	129	146	184	180	157,6	19,5	12%

Combination of ABC and XYZ analyzes.

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

OH	AY	AZ
VX	BY	BZ
SH	CY	cz

• Goods of groups A and B provide the main turnover of the company. Therefore, it is necessary to ensure their constant availability. It is generally accepted practice when an excess insurance stock is created for goods of group A, and sufficient for goods of group B. The use of XYZ-analysis allows you to fine-tune the inventory management system and thereby reduce the total inventory.
• Goods of groups AX and BX distinguished by high turnover and stability. It is necessary to ensure the constant availability of goods, but for this it is not necessary to create an excess safety stock. The consumption of goods in this group is stable and well predicted.
• Products of AY and BY groups with a high turnover, they have insufficient consumption stability, 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 products of a given group only at the expense of excess insurance commodity stock will lead to the fact that the average commodity stock of the company will increase significantly. For the goods of this group, the ordering system should be reviewed. Some goods need to be transferred to a system of orders with a constant amount (volume) of the order, for some goods it is necessary to ensure more frequent deliveries, choose suppliers located close to your warehouse (and thereby reduce the amount of insurance inventory), increase frequency of control, entrust work with this group of goods to the most experienced manager of the company, etc.
• Group C goods make up to 80% of the assortment of the company. The use of XYZ-analysis can greatly reduce the time that the manager spends on managing and controlling the goods of this group.
• For goods of CX group you can use a system of orders with a constant frequency and reduce the safety stock.
• For products of the CY group you can switch to a system with a constant amount (volume) of the order, but at the same time form an insurance stock based on the financial capabilities of the company.
• To product group CZ all new goods, goods of spontaneous demand, supplied on order, etc. fall into place. Some of these goods can be painlessly withdrawn from the assortment, while the other part must be regularly monitored, since it is from the goods of this group that illiquid or hard-to-sell inventories arise, from which the company is losing. It is necessary to withdraw from the assortment the remains of goods taken on order or no longer produced, that is, goods that usually belong to the category of stocks.

If you hire a new and inexperienced employee, then by entrusting him with work with the goods of the AZ group, you risk incurring losses during the period when he gains the necessary experience. If you entrust him with goods of the CX group, then, after working for a year, he will learn to press three buttons on the computer and send requests 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, the use of combined ABC and XYZ analysis will allow:

• improve 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 the reasons that affect the amount of goods stored in the warehouse;
• redistribute the efforts of staff depending on qualifications and experience.

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