Xyz inventory analysis. An example of ABC analysis to optimize a company's assortment. ABC and XYZ analysis

To analyze the assortment of goods, the "prospects" of customers, suppliers, debtors, ABC and XYZ methods are used (very rarely).

The ABC analysis is based on the well-known Pareto principle, which says: 20% of the effort gives 80% of the result. Transformed and detailed, this law found application in the development of the methods we are considering.

ABC analysis in Excel

The ABC method allows you to sort a list of values \u200b\u200binto three groups that have different effects on the final result.

Thanks to ABC analysis, the user will be able to:

to highlight the positions that have the greatest "weight" in the total result;
analyze groups of positions instead of a huge list;
work according to one algorithm with positions of one group.

The values \u200b\u200bin the list after applying the ABC method are divided into three groups:

A - the most important for the bottom line (20% gives 80% of the result (revenue, for example)).
B - medium in importance (30% - 15%).
C - least important (50% - 5%).

The specified values \u200b\u200bare optional. Methods for defining the boundaries of ABC groups will differ when analyzing different indicators. But if significant deviations are revealed, it is worth considering what is wrong.

Conditions for using ABC analysis:

the analyzed objects have a numerical characteristic;
the list for analysis consists of homogeneous items (washing machines and light bulbs cannot be compared, these products occupy very different price ranges);
the most objective values \u200b\u200bwere selected (it is more correct to rank the parameters by monthly revenue than by daily revenue).

For what values \u200b\u200bcan the ABC analysis technique be applied:

assortment of goods (we analyze profit),
customer base (we analyze the volume of orders),
supplier base (we analyze the volume of supplies),
debtors (we analyze the amount of debt).

The ranking method is very simple. But it is problematic to operate with large amounts of data without special programs. An Excel spreadsheet processor greatly simplifies ABC analysis.

General arrangement:

Indicate the purpose of the analysis. Define the object (what we are analyzing) and the parameter (by what principle we will sort by groups).
Sort the parameters in descending order.
Summarize numerical data (parameters - revenue, debt amount, order volume, etc.).
Find the share of each parameter in the total.
Calculate the fraction with a cumulative total for each value in the list.
Find a value in the list in which the cumulative share is close to 80%. This is the lower limit of group A. The upper one is the first in the list.
Find the value in the list in which the cumulative share is close to 95% (+ 15%). This is the lower limit of group B.
For C - everything below.
Count the number of values \u200b\u200bfor each category and the total number of items in the list.
Find the shares of each category in the total.

ABC analysis of product range in Excel

Let's create a study table with 2 columns and 15 rows. Let's enter the names of conventional goods and data on sales for the year (in monetary terms). It is necessary to rank the assortment by income (which products give more profit).

So we finished the ABC analysis using excel tools... Further actions of the user - application of the obtained data in practice.

XYZ Analysis: Excel Calculation Example

This method is often used in addition to the ABC analysis. In the literature, there is even a combined term ABC-XYZ analysis.

The abbreviation XYZ hides the predictability level of the analyzed object. This indicator is usually measured by the coefficient of variation, which characterizes the measure of the dispersion of data around the mean.

The coefficient of variation - relative ratethat does not have specific units of measurement. Quite informative. Even by itself. BUT! The trend, seasonality in dynamics significantly increase the coefficient of variation. As a result, the predictability index decreases. An error can lead to incorrect decisions. This is a huge disadvantage of the XYZ method. Nonetheless…

Possible objects for analysis: sales volume, number of suppliers, revenue, etc. Most often, the method is used to determine the goods for which there is a steady demand.

XYZ analysis algorithm:

Calculation of the coefficient of variation of the level of demand for each product category. The analyst estimates the percentage deviation of sales from the mean.
Sorting of the product range by the coefficient of variation.
Classification of positions into three groups - X, Y or Z.

Criteria for classification and characteristics of groups:

"X" - 0-10% (coefficient of variation) - goods with the most stable demand.
"Y" - 10-25% - products with variable sales volume.
"Z" - from 25% - goods with random demand.

Let's create a training table for the XYZ analysis.

Group "X" includes goods that have the most stable demand. Average monthly sales deviate by only 7% (item1) and 9% (item8). If there are stocks of these items in the warehouse, the company should put the products on the counter.

Stocks of goods from group "Z" can be reduced. Or even go to these items for pre-order.

Send your good work in the knowledge base is simple. Use the form below

Students, graduate students, young scientists using the knowledge base in their studies and work will be very grateful to you.

ABC sales analysis. Definition

ABC analysis (englishABC-analysis) Is a method of increasing the efficiency and effectiveness of the company's sales system. Most often, the ABC analysis method is used to optimize the nomenclature of a product (assortment) and its stock in order to increase sales. In other words, the purpose of ABC analysis is to highlight the most promising products (or group of products) that bring the maximum profit for the company.

This type of analysis is based on the pattern identified by the economist Pareto: "20% of production is provided, 80% of the company's profits." The goal of the company in conducting this analysis is to identify the key products, and manage this 20% group, which will create control over 80% of the cash flow. The management of sales and cash payments directly affects the financial stability and solvency of the company.

When analyzing products, all products are divided into three groups:

Group "A" - the most valuable goods, occupy 20% of the product range, and bring 80% of the sales profit;
Group "B" - low-value goods, occupy 30% of the product range, and provide 15% of sales;
Group "C" - unclaimed goods, occupy 50% of the assortment, and provide 5% of sales profits.

The goods of the group "A" are targeted, and require maximum attention to their production and sale: their availability in stock, prompt delivery, planning and organization of production and quality control of these products.

ABC analysis of product sales. Stages of the

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

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

An example of ABC analysis of product sales in Excel

Let's look at how to carry out an ABC analysis of product sales in Excel for a cell phone store in practice. To do this, we need the presence of the name of all goods (groups of goods) and their rate of return. The figure below shows the range of goods and the amount of profit for each type.

Product range for ABC analysis in Excel

Next, you need to sort the goods by profitability. We go to the main menu Excel → "Data" → "Sort". The result will be the sorting of product groups by profitability from the most profitable to the most unprofitable.

The next step is to determine the share for each type of product. To do this, we will use formulas in Excel.

Share of sales of each type of product \u003d B5 / SUM ($ B $ 5: $ B $ 15)

Determination of the share of products in the company's sales

At the next stage, the share of groups is calculated as a cumulative total using the formula:

The share of the item in the nomenclature with a cumulative total \u003d C6 + D5

Assessment of the cumulative total profit share for a group of goods

After that, it is necessary to determine the border of up to 80% for the group of goods "A", 80-95% for the group of goods "B" and 95-100% for the goods "C". The figure below shows the result of grouping products into three groups for a cell phone store. So the brands Samsung, Nokia, Fly and LG account for 80% of all sales, Alcatel, HTC, Lenovo account for 15% of sales and Philips, Sony, Apple, ASUS account for 5% of sales proceeds.

After grouping the goods, the company receives an analytical report on which goods provide the main cash receipts. The further goal is to increase sales of target products from group "A" and reduce the share of non effective goods from group "C". In our example, about ~ 30% of all products generate 80% of the company's profits.

Benefits of ABC Analysis

Ease of use and speed analysis to improve sales efficiency. The ABC analysis technique can be used in any enterprise, since it does not require large computing power and databases. All calculations for the nomenclature of goods can be made in a table in Excel.
Reliability of results.The results obtained are stable over time and allow the company to focus its resources and capital in the development of the most promising products. The management of the nomenclature of the most valuable goods allows you to create the financial stability of the enterprise.
Optimization of resources and time.The use of the technique allows freeing up additional resources, both financial and temporary.
Analysis versatility. The ability to apply the ABC analysis methodology for other areas of the enterprise.

Other uses of ABC analysis in a company

Range of use this method improving efficiency in economic systems is extremely wide:

Optimization of the range of goods.
Highlighting key suppliers, contractors, clients.
Improving the efficiency of organizing warehouse stocks.
Optimization of the production process.
Budgeting and marketing cost management.

Disadvantages of ABC Analysis

In addition to the advantages of the technique, it also has disadvantages:

One-dimensional method. ABC analysis is a fairly simple analytical method and does not allow grouping complex multidimensional objects.
Grouping items based on quantity only.The method is not based only on quantify the rate of return for each product line and does not assess the quality component of each product, for example, products of different categories.
Lack of a group of unprofitable goods.In addition to goods that make a profit for the company, there are also goods that make a loss. In this method, such goods are not reflected, as a result, in practice, the ABC analysis is transformed into an ABCD analysis, where group “D” includes unprofitable groups of goods.
The influence of external factors on sales.Despite the rather stable structure of sales according to this model, external economic factors have a strong influence on the assessment of sales volume in the future: seasonality, uneven consumption and demand, purchasing power, influence of competitors, etc. The influence of these factors is not reflected in the ABC analysis model.

Summary

ABC analysis of sales allows you to identify target product groups that provide 80% of the company's profits. This method increases the efficiency of the enterprise, analyzes and optimizes resources, which in turn affects financial sustainability and the company's profitability. The disassembled example shows the ease of using the ABC model to analyze the assortment of goods and sales. The method can be widely used in other areas of the company to identify target groups: customers, suppliers, contractors, personnel, etc.

We divide the article into subtopics:

It should be noted that the second and third stages are creative. Don't think that a standard solution is best for your task. It is necessary to experiment, analyze various objects for all sorts of factors, only then will ABC analysis become a powerful tool for decision making. For example, most people, managing inventory, carry out ABC analysis on one object (assortment position) and one factor (sales volume), while in our example many objects and factors of analysis are indicated. Obviously, multivariate analysis will allow you to make a more balanced decision.

The fourth stage is the formation of an information array for analysis. Modern information systems allow you to easily generate the required array of information and even perform all subsequent actions automatically, of course, not without the help of programmers. However, even at this stage, one may encounter difficulties, for example: determining the time interval of data for analysis, data inconsistency with the real state of affairs (for example, lack of sales by position as a result of a deficit), etc.

At the fifth and sixth stages, the contribution of each object to the overall result is assessed, the objects are ranked in descending order of the selected factor, as well as the calculation of the cumulative total of the share of objects in the total number as a percentage (hereinafter in the abbreviation AO - the share of objects) and the contribution of these objects to the overall result in percentage (hereinafter in abbreviation ВР - contribution to the result). These are simple arithmetic operations with which you shouldn't have any difficulty.

Table 1. Initial data for the selection of groups

The next step is to divide the objects of analysis into groups. There are many methods for selecting groups, here are some of them:

- empirical,
- sum method,
- differential method,
- polygon method,
- tangent method,
- loop method.

The empirical method consists in dividing objects into groups based on the averaged results of previous studies. The most common option assumes the following boundaries: BPA - 80% and RTV - 95%. Then the corresponding values \u200b\u200bof DOA and ORD are found (Table 2). In our example, the border of groups A and B has a BPA value of 80.01%, DOA - 17.33%; the border of groups B and C has a VRV value of 95%, and AOV - 43.26%.

Table 2. Empirical method

Other variants of the empirical method can be used, including dividing into a larger number of groups depending on the number of analyzed objects (for example, VRa - 80%, VRv - 95%, VRc - 99%; VRa - 50%, VRv - 80% , VRs - 95%, VRv 99%, etc.). The advantage of the method lies in its simplicity, and the disadvantage is that the averaged values \u200b\u200bused to distinguish groups do not always correspond to a specific situation. In accordance with the classical proportion, 20% of objects should provide 80% of the result. In our example, this is not observed. The next method is more flexible in this regard.

The sum method assumes the allocation of groups by the sum of DO and BP: the border of groups A and B will be at the point where the sum of DOA and BPA will be equal to 100%; and the border of groups B and C - where the sum of ORD and VRV will be equal to 145% (Table 3). In our example, the border of groups A and B has a BPA value of 81.37%, DOA - 18.62%; the border of groups B and C has a VRV value of 96.37%, ORD - 48.65%. The advantage of this method over the empirical one is in its flexibility, so its results better reflect a specific situation.

Table 3. Sum method

The differential method is based on the average value of the factor for all objects. Those objects for which the factor value is 6 times or more higher than the average factor value for all objects belong to group A. Group C includes those objects for which the factor value is 2 or more times less than the average factor value for all objects. The rest of the objects belong to group B. These are the most common coefficients, there are other options. In practice, the differential method gives too small group A (BPA - within 40-50%, DOA - less than 5%) and large group C. In our example, the average factor is 4998. As a result, the boundary between groups A and B has a BPA value - 46.97%, DOA - 3.06%; the border of groups B and C has a VRV value - 90.73%, ORD - 31.93% (Table 4). Obviously, the results are very different from the results obtained by other methods.

Table 4. Differential method

The disadvantage of this method is the uncertainty in the choice of coefficients, which often leads to incorrect results. There are cases when it is generally impossible to single out group A from the analyzed objects. The advantage of the method is its simplicity, although against the background of the shortcomings it is minimized. In this regard, the application of the differential method in practice is limited.

The essence of the polygon method is as follows. A part of the polygon is inscribed in the ABC analysis curve (based on DO and BP - columns E and F of Table 1) so that the area between the curve and the polygon is minimal (Fig. 1). The results produced by this method are similar to the results of the differential method: group A is too small and group C is too large. In this regard, and also because of its complexity, the polygon method will not be considered in more detail in this article.

Polygon method

The tangent method (proposed by V.S. Lukinsky) consists in dividing the objects of analysis into groups using tangents to the ABC-analysis curve (Fig. 2). We connect the beginning and the end of the graph with a straight line OK, then draw a tangent to the ABC analysis curve, parallel to OK. The tangent point M separates the groups A and B. Now we connect the points M and K and draw a tangent to the ABC-analysis curve parallel to MK. The touch point N separates groups B and C. In our example, the border of groups A and B has a BPA value of 82.39%, DOA - 19.66%; the border of groups B and C has a VRV value of 96.19%, ORD - 47.85%. If necessary, you can continue dividing with tangents and get more groups. The advantage of the method is its flexibility, simplicity and clarity.

Tangent method

It should be noted that the tangent method can also be applied to select groups in XYZ analysis.

Tangent Method in XYZ Analysis

The loop method (developed by Gadzhinsky A.M.) consists in determining the boundaries of the groups in the areas of a sharp change in the curvature of the ABC-analysis curve. It is necessary to restore the normal Г (perpendicular to the tangent line) of a certain length at each point of the ABC curve (Fig. 4). The normal should point to the right of the ABC curve. The end of the normal will outline a loop: while the tangent slides over the area with large values \u200b\u200bof the radius of curvature (the initial part of the graph, group A), the end of the normal will move up and to the right; at the moment the tangent reaches the middle section of the graph with small values \u200b\u200bof the radius of curvature, the direction of movement of the end of the normal changes to the opposite - down and to the left; after the tangent reaches the final straightened section of the ABC curve, the end of the normal again changes the direction of movement to the opposite. Thus, the end of the normal outlines a loop, and the points of the ABC analysis curve corresponding to the moment when the direction of movement of the end of the normal changes, divide the curve into groups A, B and C.

Loop method

At first glance, the description of the method may seem complicated, but it is very simple to implement in Excel (Table 5).

Table 5. Implementation of the loop method in Excel

A dot diagram of the loop is plotted along columns I and J (Fig. 5). Determining the length of the normal to the tangent line (column H) can be somewhat difficult. The value of the normal is specified in OX scale units (ranging from 20 to 200) and is determined by several iterations. If the length of the normal is too large or too small, there will be no loop on the graph. In the process of selecting the length of the normal, it is necessary to find the interval at which the boundaries between groups A, B and C do not change. By changing the value in cell H3, we find the coordinates of the inflection points in the column I and J and highlight the cells with these values \u200b\u200bwith color, as soon as the coordinates of the inflection points at changing the length of the normal will remain in one place (in the highlighted cells) the problem is solved. A further increase in the length of the normal will eventually lead to the fact that the boundaries start to change again. These values \u200b\u200bshould be taken to select groups A, B and C. In our example, the required length of the normal is in the range from 52 to 59. The border of groups A and B has a BPA value of 75.03%, DOA - 13.43%; the border of groups B and C has an RTV value of 93.23%, an ARD of 37.80%. The disadvantage of this method is its complexity and ambiguity in relation to simpler methods.

ABC analysis loop

Thus, the tangent method and the sum method are of the greatest interest for practical use, each of which has its own advantages. After all the objects are divided into groups for all the selected factors, the results of the analysis are interpreted and, on the basis of this, actions are taken to solve the problem posed at the first stage.

Many believe that ABC analysis does not work for their situation and consider the method described above to be invalid. Many novice logisticians and managers make the same mistake they perceive ABC analysis as a strategy, and not as a tool, a method for classifying management objects. And the tool can only be used at the right time, in the right place and for a specific purpose. A person picks up a hammer in order to hammer a nail or crack a nut, and not just because it is a good and necessary thing. In the same way, we adopt ABC analysis, when it is necessary to divide hundreds or thousands of names of objects (stocks, customers, suppliers, distribution channels, etc.) into groups that can be managed according to general principles. And before proceeding with the classification, a number of questions must be answered.

What are we analyzing?

First of all, it is very important to decide on the objects of analysis. A simple example. The firm sells clothes. The assortment includes suits, fashion items and branded items. In practice, these are three different markets. Which one is more important for the company? Perhaps the main thing is the costumes, and everything else is “for quantity”? It's a matter of strategy. But if you analyze the profitability of all products together, then it may well turn out that only brands will be in group A. Hence the imbalance in assortment and inventory management, because suits, according to the results of this analysis, will receive much less attention. To prevent this from happening, obviously, the entire mass of products should be divided into types and ABC should be carried out separately for each. And then there will be three groups A - for each of the markets. In addition, the costumes can be cheap, expensive, or mid-range - they probably shouldn't be mixed in the same basket if the company plans to focus on one of the segments. And then there are already nine groups A, B and C - in each of the segments of each market.

It is equally important to correctly choose the characteristics by which objects are combined into groups. So that it does not work out as in one company (this was also told by the listeners of the seminars): they analyze the goods by value every month and, depending on the results ... rearrange them in the warehouse. Maybe there the intensity of acceptance / shipment depends on prices, and not on demand? Or do people not understand what kind of analysis is being done for what?

For the same products, it is often necessary to carry out ABC analysis 4-5 times - according to different criteria for different purposes. For example, to select an assortment - by cost price, to manage an item in a warehouse - by sales (in inventory control units or units of measure), to determine financing priorities - by profit per unit of goods, etc. And at the same time, the same product can be in different classes according to the results of different analyzes.

Is the skin removed from a newcomer?

An important question - to what class of inventory management should a new product that is just being introduced to the market be attributed? If you just add it to the list and analyze sales for common ground... Let's say you conduct such an analysis at the beginning of each month, and a new product appears on the 20th. Surely, in terms of the number of sales, it will lose this month and end up in group C. So, in the future you will not pay much attention to it, constantly monitor the availability in the warehouse and on the sales shelf? Simply put, deprive a new product of the chance to prove itself in the future. Then did they try to bring it to the market?

Obviously, new assortment positions in group B or C should not appear. This means that at first they should not participate in the “general competition”. For every business there is a concept of the term for bringing a product to the market: one becomes sufficiently known in a month, another in three, and a third in a year. And for this period, a "most favored nation policy" is carried out in relation to the product. He, like a little child, must be brought to the consumer "by the handle". In practice, this means that for the period necessary to bring a new product to the market, a moratorium is declared for it - it is automatically ranked in Group A and "they keep their eyes on it." And only after the end of the established period, the novelty is included in the general lists for analysis.

This is easy to do even when the ABC is automated. In the accounting program, a certain inventory management class is assigned to the article as a periodic variable, i.e. the date is entered. It is compared with the date of the analysis, and if the "distance" is less than the time that the product goes to market, the product itself and all its sales are excluded from the analysis. Thus, you give the product the right to life, do not shoot it on takeoff.

When do we analyze?

It is quite obvious that any analysis and division of goods into groups is possible only on the basis of statistics. Starting a business with no sales experience in this market, is it possible to decide where you will be more successful? After all, the same product can be in group A for one company and in group C for another, if it has a different focus. One firm has 80% of equipment and 20% of spare parts in its assortment, while another has exactly the opposite, although they once started to work in the same way. It's a matter of strategy and specialization. And before doing ABC, you need to understand how the company behaves with stocks, customers, suppliers, on which segments it focuses attention. The "rules of the game" for each product depend on this.

But also in developed business it is not allowed to evaluate goods "when it comes to mind". Especially if there are periodic fluctuations, spikes / falls in sales - let's say, seasonal. For example, some firms conduct ABC analysis regularly, every six months. And they plan to sell the next half of the year following the results of the previous one. And it turns out that we will not carry ice cream that was not sold in winter!

Obviously, it would be more correct to analyze sales for a full cycle - say, a year, from January 1 to December 31. Or take the off-season and the season according to past data and transfer this proportion (but not an absolute value!) To the future, taking into account changes in the external environment.

And if there are two peaks (seasons) a year, and the duration of the first and second is different? Then the analysis for the year will help to reveal only the general trend, and for more detailed planning it is necessary to carry it out for one peak, for the second and in the off-season. And clearly understand whether the tendencies of one surge and another coincide. For example, in the construction business there is a significant increase in sales in the spring and fall. But in the first case, mainly brick and cement are sold, and in the second, finishing materials. Obviously it would be a mistake to design commodity policy on autumn period based on the analysis of the spring.

And it turns out that ABC should not be done when they simply decided that it was necessary, but to take an analogy from past periods, realizing that history will be transferred to the future.

Not just statistics

As soon as period n ends, you tweak its results, take the analogy of the previous period (n-1) and determine the rate of increase / decrease in the trend: t "\u003d tn / tn-1. And by this number (t") you adjust the proportion of the second season. Thanks to this, you can predict how the product will behave in the next season and adjust your actions accordingly.

If, for example, the product in this period was in category B, but the trend line goes up sharply (i.e. sales are growing rapidly), perhaps it is worth paying more attention to it? Perhaps you have new seller (a store) that knows how to sell this product well. And if you don't restock on time, sales won't grow and the product will never go into the top tier. And only due to the fact that the rules of the game are developed according to the previous model, without taking into account the real state of affairs.

Migration of goods between groups

Once again, we repeat that ABC analysis is only a classification method that allows you to divide the active assortment into groups, for each of which its own management strategy is developed. These strategies differ, first of all, in the level of service: for category A it can be 100%, for B - 95, and for C - for example, 90%. But it is important to remember that it is the active assortment that is being analyzed, the one that is directly managed by logistics. After all, each company has so-called custom-made items, which are not kept in the warehouse all the time, but are brought under a specific order. It is not worth including them in the ABC analysis, because one random sale (if, for example, a large contract) can change the whole picture. This product will immediately burst into group A and push everything else into the trash. But will there be the same sale in the next period? To avoid such distortions, it is necessary to clearly highlight the ordered items in an additional segment, except for groups A, B and C, and not take them into account in the analysis.

Another special segment is dead stock. These are either morally obsolete goods that are no longer produced by the manufacturer, or those that we simply cannot successfully sell. They also drop out of ABC because there is no sales for them. Although they actually exist in the warehouse. What to send "to the graveyard" is a matter of strategy. For example, at some point we decide for ourselves that the last n positions of category C, sales of which continue to fall, are “removed from the accounts” - we stop importing and only sell the rest. As “orderlies of the forest”, we cleanse our active assortment of ballast.

As a result, we have five groups of goods, between which there is a constant migration. A new product is introduced, which for a "trial period" is automatically included in group A. But this group has a certain - financial or volume - framework. This means that at the moment of the appearance of a novelty, some other product (or products) is displaced in B and sequentially in C and in customized ones (if the manager comes to the conclusion that for the sake of one or two sales a year it is not worth keeping a constant stock in the warehouse) or to the "dead".

But a reverse migration is also possible - from ordered goods to an active range. This is also defined by such a word as strategy: management determines at what volumes and frequency of orders it is worth creating and maintaining a stock - for example, if 20 customers are interested in a product per month in the amount of 100 thousand rubles.

Thus, we get a system of active management (whether customers, stocks), the circulation of goods in nature: birth, development options, chances and a "cemetery". And there is always an opportunity to update this system according to the principles of natural selection - whoever has grown up pushes the weak out of the warehouse, while the (active) warehouse does not increase. New product pushes obsolete to dead or to spare, and the number of active positions remains the same.

If groups A, B and C are rigidly fixed, the flow of "fresh blood" is hampered by the "garbage" getting underfoot, and no analysis will help to put things in order at this dump.

Impact of chance

Likewise, there can be no strict classification by XYZ - the chances are too great to underestimate the behavior of a product, "pulling" it out of the sales time series.

First, I would like to return to the formula for calculating the coefficient of variation proposed by the author of the article in No. 6 to analyze the stability of indicators:

X is the parameter value for the evaluated object for the i-th period, xav is the average parameter value for the evaluated object of analysis, n is the number of periods.

This formula is offered by many textbooks, without specifying, however, that it is sufficiently "competent" only when working with the general population. But XYZ analysis is usually done on a sample basis. We pulled the product out of the flow and tied it to the average in this particular time period. This means that minus one degree of freedom should appear in the calculations of the coefficient of variation:

The absence of this minus (in the denominator of the numerator) when working with a sample leads to a fluctuation in the result from 3% to 6%. This means that the product may fall into the wrong category.

It should also not be forgotten that, according to the basic laws of statistics, the sample should contain at least 30 values: the more there are, the better the pattern is traced. At the same time, the more periods you take, the more you give the influence of the regularity, you focus on the trend line, and not on fluctuations around the average. Here, too, you have to sit down and pick up the best option n - 30 days, 160 or a year.

Let's look at four options for fluctuating sales volumes over long periods, say, over a year (Fig. 1, 2, 3 and 4). Agree, very different conclusions can be drawn if you analyze the data of the entire graph, between the first and second dotted lines and between the first and third. And only by considering the changes over a sufficiently long time, it is possible to track the trend, i.e. a persistent trend towards an increase or decrease in sales volumes (stocks, expenses, etc.).

Unfortunately, when XYZ analysis is carried out mechanically, on data of a short time interval, a product whose sales are constantly growing may well fall into category Z. Indeed, according to the graphs in Fig. 1 and 4, the coefficient of variation will show that sales are unstable, subject to constant fluctuations (changes). But these changes themselves have certain patterns. And in order to detect this, it is necessary to introduce additional analysis criteria. For example, the autocorrelation coefficient, which allows us to find out whether our data over time is random, constant, or has a certain trend.

Yi - parameter value for the current period,
Yav - average value of the parameter,
k is the number of shifts.

If k \u003d 1, we compare today's sales with the previous period, if k \u003d 2 - with the previous one, etc.

A simple example. Before conducting an ABC analysis, you should check whether the growth in sales of this product is constant or if it is a one-time surge, a contract. Sometimes managers try to initially take into account one-time sales data separately, for example, to put “ticks” in the corresponding invoices. This method can hardly be called reliable - it is too dependent on the human factor: someone will instruct unnecessary "ticks", and someone will forget about them altogether. Therefore, it is better to use mathematical methods. They allow you to almost accurately track the trend.

If, for example, for k \u003d 1 the autocorrelation coefficient will be close to unity (~ 0.7–0.8), for k \u003d 2 - close to 0.5, k \u003d 3 - to 0.3, and for k \u003d 4 it will approach to zero, then it can be clearly stated that there is a trend component - either decreasing or increasing, but subject to regularity. For a random spike, random sales, this value will immediately be very close to zero, it may even have negative meaning... And we immediately see that this sale is random and it makes no sense to include it in the ABC analysis.

Likewise, we can determine the seasonality when the season comes. Using the same autocorrelation coefficient. For some reason everyone forgets about him.

Of course, the same results can be achieved by keeping separate records of retail purchases and large orders for a long time, creating and analyzing the corresponding statistics. Just put a person who will take everything into account and analyze. This takes a lot of time, in my experience - about 2 days for each of the headings. And if there are 10-15 thousand of them in the company's assortment, comments, as they say, are unnecessary. When using probabilistic models, the corresponding calculation takes 5–8 minutes.

Before "sending to circulation"

But even after we have determined whether the increase / decrease in sales is random or constant, the work cannot be considered finished. It is still necessary to find out why the goods were not sold - there is no demand for it or it was simply not in stock? If we have a sales graph similar to Fig. 4, then it is obviously worth comparing with the stock availability graph. If during the period of absence of sales the product was in stock, it means that there really was no demand, and this data can be taken into account in the analysis.

If there was no product, the task becomes more complicated. It is good if managers keep statistics on scarcity and can report how many times the missing item was asked - then you can fill the void in sales with demand (albeit with a certain degree of skepticism if demand is deferred). But more often than not, there is no such accounting, and analysts have to do forecasting. It is simply impossible to count with this "pit": the fact that you have failed stocks is not a pattern of consumption, but a consequence of your influence on this pattern.

The depth and strength of this influence can also be calculated mathematically. In particular, using the correlation coefficient, which is used to measure the tightness of interaction between various attributes (in our case, the availability of stocks and sales).

X; y; - the values \u200b\u200bof the studied pair of features of n objects (i \u003d 1, 2, ..., n);
xsr, usr. is the arithmetic mean of each series of x and y values.

The Rxy value is in the range from -1 to 1. The larger it is, the stronger the relationship between the two features. If Rxy \u003d 0, there is no connection, if negative, the indicators are in inverse relationship.

As a result of all these calculations, it may turn out that the goods were sold a little, not through the fault of the buyers who did not take, but through the fault of the seller, who did not ensure the availability of the goods for sale. So, before abandoning it (driving it into second or third positions), it is worth figuring out how this product would be sold if it were available - i.e. build an appropriate model taking into account the trend component. After all, ABC analysis is carried out in order to manage the product in the future. Logistics is not just fixing and analyzing current events, but also forecasting and prediction.

Is the stability stable?

Certain conditions must be met when performing XYZ analysis. In particular, the level of detail is of great importance here: to calculate sales by day, week or month. A rare item falls into category X at all three levels. For example, bread is sold and bought every day. If we analyze the stability of its sales by week, it can enter category X, and if by day, then most likely, in Y, because there are weekly bursts, when from Friday everyone is overstocked on weekends, they buy little on Saturday, and on Sunday in the evening they buy again with a reserve the next day. In terms of months, this can again be category X.

The level of detail is selected based on what the analysis is for. If for inventory management, then it is clear that the time granularity should be comparable to the order fulfillment cycle. Let's say the delivery time under the contract is a month - in this case, is it worth doing an XYZ analysis by day? - No. But monthly detailing may turn out to be incorrect.

Most likely, here it is necessary to analyze the stability of sales weekly. If the execution of the order takes two days, XYZ should be done in terms of days, if 3-4 months - go to the monthly level of detail.

But this is for operational management. And if, for example, you need data for - are the daily fluctuations so interesting here? Those. XYZ analyzes can also be several for different purposes.

Practical application of ABC analysis

The analysis must begin with the selection of objects, the significance of which we want to determine, and the actual parameters of the objects for which we will carry out the analysis.

An object can be a product, a commodity group, a supplier, a customer, an order, etc. As a parameter, you can select: average or current inventory in rubles, pieces, boxes or pallets; sales volume for the period, product profitability, number of customer orders, etc.

As an example, consider a report on the average inventory for the month in pallets. The objects of analysis are goods; the parameter by which the analysis is carried out is the average inventory per month in pallets (see table 1).

How to perform ABC analysis?

It is very convenient to use MS Excel or any other similar editor for analysis. The procedure is as follows.

1. Sort the objects of analysis in descending order of the parameter value.
2. Calculate the proportion of the parameter from the total sum of the parameters of the selected objects (this is done in order to assess the "contribution" of each object to the overall result).
3. Calculate this share with a cumulative total (this operation is of a technical nature and serves for the convenience of further defining the boundaries for ABC groups).
4. Assign group values \u200b\u200bto the selected objects.

The greatest number of questions is caused by the definition of boundaries during the ABC analysis. In his practice, the author initially used the division into three groups according to the "share with cumulative total" indicator: A - up to 50%, B - 50-80% and C - 80-100%. This distribution fully meets the tasks of a warehouse of a wholesale company or a retail network.

The product is interchangeable, and, accordingly, the entire “assortment tail” falls into group C. But in the case of an analysis of the stock in the warehouse of a manufacturing company or a chain of discounter stores, in which there may be no interchangeability of goods, it became necessary to divide group C, which contains 80% of the entire range, into two smaller groups.

Group A - objects, the sum of shares with a cumulative total of which is the first 50% of the total amount of parameters;
group B - objects following group A - from 50 to 80%;
group C - from 80 to 95%;
group D - the remaining objects, the sum of shares with a cumulative total of which ranges from 95% to 100% of the total amount of parameters.

As a result of the analysis, we received four groups of objects (table 2):

Group A - makes up 20% of the assortment and 49% of the inventory;
group B - 30% of the assortment and 30% of the inventory;
group C - 20% of the assortment and 13% of the inventory;
group D - 30% of the assortment and 8% of the inventory.

Let's say a company is faced with the task of reducing the average inventory. In this case, it is necessary to figure out why the goods of group A are in the warehouse in such a large quantity. Even a slight decrease in stock for only two goods from this group will have a noticeable effect on the total volume of inventory.

Basic stock

* Working stock required to ensure shipment in accordance with the sales plan for the current period.
* Safety stock that allows you to compensate for unplanned growth in shipments and unexpected delays in delivery associated with interruptions in production or availability of goods at the supplier.
Temporary supply

* Seasonal stock. A surplus stock created before a seasonal increase in sales begins.
* Marketing inventory. Additional stock, formed at the time of marketing campaigns, advertising campaigns etc
* commodity stock. A surplus stock created by a competitive market situation.

The reasons for creating an opportunistic stock can be: one-time discounts from suppliers, a predicted or artificially created shortage of goods from suppliers, etc.

Forced stock

* Marriage. Goods that have lost their consumer properties and cannot be further used for their intended purpose.
* Illiquid or hard-to-sell stock. Often this product appears as a result of "creative interaction" between the sales department and the purchasing department: they planned to ship one quantity, but the actual demand turned out to be 10 times less; replaced one supplier with another, but "forgot" to sell the leftovers, etc.

The results of ABC analysis should be used in many ways. A lot of additional information can be obtained by comparing the results of the analysis by one parameter with other parameters of the same object, for example, the shipment of goods for a certain period and the amount of marriage for the goods for the same period (Table 3).

The two Group A products, accounting for 14% of shipments, account for 49% of the inventory. At the same time, two goods of group C account for the same 14% of the shipment, but they make up only 13% of the stock. This means that if for the goods of group C it is possible to ensure shipment with an average inventory of 19 pallets, then it is possible that the same possibility exists for goods of group A.

Having grouped the product by one parameter, compare the result with other parameters. Group D can generate 5% of the income, account for 50% of the inventory and occupy 70% of the warehouse area.

ABC analysis of goods by income will show where money is earned, a similar analysis of costs will allow you to understand what they are being spent on.

If you conduct an ABC analysis of goods in terms of sales in a wholesale company or a retail store, and then evaluate which goods the assortment groups consist of, you can determine which of these groups require expansion and which ones require reduction.

You can analyze goods by the number of units shipped (or the number of orders for them) and, as a result, get 20% of the goods purchased by 80% of customers, determining the attractiveness of the goods for the customer. The same result can be used when planning the placement of goods in the "hot" and "cold" zones in the warehouse or in trading floor store.

ABC assortment analysis

ABC analysis is the most common one that helps to optimize assortment in retail... The increase in sales and the increase in the efficiency of the assortment directly depend on the correct assessment of the profitability of each commodity item, the absence of "stale goods" and goods that do not pay off.

With regard to the formation of the trade assortment, this means that 20% of the goods bring 80% of the income, and vice versa, the remaining four-fifths of the goods bring in only 20%. The result of ABC analysis is the ability to determine the most profitable 20% of goods.

Applying this rule to raw materials, components, industrial enterprise or to the goods trading company, you can take a very simple step to implement logistics.

Define a list of goods ( finished products), which together give you 80% of income or profit. This list will almost certainly contain about 20% of the names (groups) of goods. Name this list A. Next, define the list of products that bring you another 15% of your income. Usually there are about 30% of items. We will call this list B. The remaining goods will be assigned to group C.

You can do the same with raw materials and components. Only the latter, of course, are classified not by income, but by the cost of purchase and storage.

Why all this is necessary? In order to manage different stocks differently. For example, purchase expensive stocks of group A in smaller batches, so as not to deaden capital, and also to carry out their inventory more often and more accurately. On the contrary, the stocks of group C should be purchased in large lots, and the inventory should be carried out "by eye".

Many companies do this analysis without even knowing that they are doing ABC analysis.

After carrying out such calculations, the most important thing is not to make harsh decisions, not to rush to extremes.

The store owner, having identified group C among his goods, which brought a meager income, stopped buying it. Revenues plummeted, far more than the Pareto's projected 5%. When this situation was discussed, we came to the following conclusions: first, the ABC proportion shifted to the remaining goods; secondly, the customer is interested in the opportunity to choose, it is important that his eyes widen, he always buys the same thing, but he enters stores with a poor assortment less willingly. I had to return group C to the store.

It is often not enough for companies to rank only on one indicator (income, profit, turnover, etc.). Nothing complicated. You just need to move gradually - one indicator, then two, then three, etc., and not a dozen at once - there is a danger of choking. Let's say you have made an ABC analysis of products in terms of “income”. Naturally, there is a desire to evaluate the profit of each type of product. One more ABC analysis is done on the “profit” indicator, the following matrix is \u200b\u200bobtained:

There are not three groups: A, B and C, but nine. The table shows the percentages corresponding to the number of product names. If the company is able to cope with such a volume of information, then you can connect next indicatore.g. turnover, etc. It is not difficult to do such an analysis in Excel, but the so-called OLAP (Online Analytical Processing) systems can also be used - software productsspecially designed for this kind of multivariate analysis.

Group A includes product names that contribute the most to sales (more than 50%), group B includes product names with an average contribution to total sales (30%), and group C with a small contribution to total sales (20 % and less).

Conclusions that can be drawn using ABC analysis:

From a cost point of view, it may be desirable to concentrate sales on a small number of products. However, this can reduce the firm's stability in the market and does not take into account the possible growth potential inherent in currently unprofitable products.

Products that fall into group C are problematic for the company, for which it is necessary to resolve the issue of excluding them from the product range, if they are not an addition to other products.

When withdrawing products from production program it is necessary to take into account the contribution of these products to cover fixed and variable costs.

ABC analysis example

Let's show with an example how the ABC analysis technique works. Let's take an assortment of 30 conventional items.

1. The purpose of the analysis is to optimize the assortment.
2. The object of analysis is goods.
3. The parameter by which we will divide into groups -.
4. The list of goods was sorted in descending order of revenue.
5. Calculated the total amount of revenue for all goods.

6. Calculated the share of revenue for each product in the total revenue.

7. Calculated for each product the share on an accrual basis.

8. Found a product for which the cumulative share is closest to 80%. This is the lower bound of Group A. The upper bound of Group A is the first item in the list.

9. Found a product for which the cumulative share is closest to 95% (80% + 15%). This is the lower limit of group B.

10. Everything below is group C.

11. We counted the number of names of goods in each group. A - 7, B - 10, C - 13.

12. Total goods in our example 30.

13. Calculated the proportion of the number of names of goods in each group. A - 23.3%, B - 33.3%, C - 43.3%.

Group A - 80% of revenue, 20% of items
Group B - 15% of revenue, 30% of items
Group C - 5% of revenue, 50% of items

For the list of products from our example:

Group A - 79% of revenue, 23.3% of items
Group B - 16% of revenue, 33.3% of items
Group C - 5% of revenue, 43.3% of items

It should be noted that, knowing the revenue for each product, you can get a lot more useful information, and not just a division into 3 groups. How this can be done, see the table below.

Combined ABC / XYZ analysis

XYZ-analysis is a tool that allows you to divide products according to the degree of stability in sales and the level of fluctuations in consumption.

The method of this analysis consists in calculating the coefficient of variation or fluctuation of consumption for each commodity item. This factor shows the deviation of the flow rate from the average value and is expressed as a percentage.

The parameter can be: sales volume (quantity), sales amount, the amount of the sold margin. The result of the XYZ-analysis is a grouping of goods into three categories, based on the stability of their behavior:

Category X, which includes products with sales fluctuations of 5% to 15%. These are goods characterized by a stable consumption value and a high degree of forecasting.
Category Y, which includes products with sales fluctuations of 15% to 50%. These are goods characterized by seasonal fluctuations and average forecasting capabilities.
Category Z, which includes products with sales fluctuations of 50% or more. These are goods with irregular consumption and unpredictable fluctuations, therefore, it is impossible to predict their demand.

Combined ABC / XYZ analysis

The combination of ABC and XYZ analyzes reveals the undisputed leaders (group AX) and outsiders (CZ). Both methods complement each other well. If the ABC analysis allows you to assess the contribution of each product to the sales structure, then the XYZ analysis allows you to assess sales jumps and its instability. It is recommended to do a combined analysis, where two parameters are used in the ABC analysis - sales volume and profit.

In total, when conducting such a multivariate combined analysis, 27 product groups are obtained. The results of such an analysis can be used to optimize the assortment, assess profitability product groups, assessment of logistics, assessment of customers of a wholesale company.

The advantages of the combined ABC and XYZ - analyzes

The use of combined ABC and XYZ analyzes has a number of significant advantages, which include the following:

Improving the efficiency of the commodity resource management system;
- increasing the share of highly profitable goods without violating the principles of assortment policy;
- identification of key products and reasons affecting the amount of goods stored in the warehouse;
- redistribution of staff efforts depending on qualifications and experience.

Formation of indicators ABC- AND XYZ-analyzes

Before combining the indicators of ABC- AND XYZ-analyzes, it is necessary to conduct an ABC-analysis of goods by the amount of income received or by the amount of products sold for a certain accounting period, for example, for a year. Then an XYZ analysis of these products is carried out for the same period, for example, by the number of monthly sales per year. After that, the results are combined. When combined, nine product groups are determined:

Allocation of nine product groups with combined ABC and XYZ analysis

1) Goods of groups A and B provide the main commodity turnover of the company, therefore it is necessary to ensure their constant availability. As a rule, excess safety stock is created for goods of group A, and sufficient safety stock for goods of group B. The use of XYZ analysis allows you to more accurately customize the inventory management system and thereby reduce the total inventory.

2) Products of the AX and BX groups are distinguished by high turnover and stability. It is necessary to ensure the constant availability of goods, but for this you do not need to create an excess safety stock. Consumption of goods in this group is stable and well forecasted.

3) The goods of the AY and BY groups, with a high turnover, have insufficient stability of consumption, and, as a result, in order to ensure constant availability, it is necessary to increase the safety stock.

4) Products of the AZ and BZ groups with a high turnover are characterized by low predictability of consumption. An attempt to ensure guaranteed availability for all goods of this group only due to excess insurance stock will lead to the fact that the average stock of the company will increase significantly.
Therefore, for the goods of this group, the ordering system should be revised:

Transfer part of the goods to the ordering system with a constant amount (volume) of the order;
- to ensure more frequent deliveries in terms of goods;
- choose suppliers located close to the warehouse, thereby reducing the amount of the insurance stock;
- to increase the frequency of control;
- entrust the work with this group of goods to the most experienced manager of the company, etc.
5) Group C products make up 80% of the company's assortment. The use of XYZ analysis can greatly reduce the time that the manager spends on managing and controlling the goods of this group.

6) For goods of the CX group, you can use the ordering system with a constant frequency and reduce the insurance stock.

7) For the goods of the CY group, you can switch to a system with a constant amount (volume) of the order, but at the same time form a safety stock based on the company's financial capabilities.

8) The CZ group of goods includes all new goods, goods of spontaneous demand, delivered on order, etc. Some of these goods can be painlessly removed from the assortment, and the other part must be regularly monitored, since it is from the goods of this group that illiquid or hard-to-sell inventory from which the company is losing. It is necessary to remove from the assortment the remnants of goods taken on order or no longer produced, that is, goods that usually belong to the category of stocks.

Back | |

Xyz inventory analysis. An example of ABC analysis to optimize a company's assortment. ABC and XYZ analysis

ABC analysis in Excel

ABC analysis of product range in Excel

XYZ Analysis: Excel Calculation Example

Send your good work in the knowledge base is simple. Use the form below

Similar documents

ABC sales analysis. Definition

ABC analysis of product sales. Stages of the

An example of ABC analysis of product sales in Excel

Benefits of ABC Analysis

Other uses of ABC analysis in a company

Disadvantages of ABC Analysis

Practical application of ABC analysis

ABC assortment analysis

ABC analysis example

Combined ABC / XYZ analysis

The last

You need to know