XYZ is not what an acronym lover might think. It is a tool similar to a slide rule. A simple device for a long time made life easier for those who love mathematics and those who cannot stand it. XYZ analysis is an example of a method that saves time. And time is money.
Money loves counting and analysis
There are many proverbs about money. They seem to love counting. They seem to like silence. There are no proverbs that money is related to analysis in the same way as to silence and counting. But someday it will certainly be.
Because money is like plants that grow well when well cared for. And this means acting based on the analysis of many factors. What is the soil like? Sour or calcareous? What fertilizer should be applied? A gardener analyzes dozens of factors before making a decision.
But some businessmen would be bad gardeners if their money turned into carrots or cabbages, and offices and warehouses into garden beds. Because they rely more on luck and intuition. And the analysis … well, what is the analysis? Things are going uphill, so it is clear that everything is right. And no, it doesn'tdestiny.
Meanwhile, a man who relied on the laws of money like a gardener on the laws of crop production, had already created a principle that could help both luck and intuition. His name was Pareto.
The rule that was discovered, forgotten and remembered
Vilfredo Pareto was Italian. By the age of sixty, he had done a lot. Studied philosophy, sociology, mechanical engineering and economics.
He discovered the pattern, which is now called the "Pareto principle". True, he discovered it in 1906, and the rule was named after him only in 1941. Perhaps this was not a discovery, just as the principle of inheritance formulated by the monk was not. Mendel simply counted the number of yellow peas in the pea patch of his monastery.
Monk Mendel became the father of genetics without knowing it. I did not have time to find out about my paternity and Pareto. But today's analysts are using his 20/80 formula with might and main.
Only 2 numbers. But without them there would be no examples of ABC XYZ analysis.
A little about patterns
The rule formulated by Pareto is undoubtedly the operation of some law of nature. This law is probably yet to be discovered. When this happens, Pareto will be remembered again.
Someone Fibonacci, also an Italian, discovered a series of numbers based on the reproduction of rabbits. Named after him, he still finds his presence in various areas of nature. The ratios of pineapple cells, flower petals and spirals of mollusk shells obey him. What lawlies at the basis of these phenomena, so far from the reproduction of rabbits, is unknown. Bye.
This is the Pareto principle. It was formulated for the ratio of the amount of we alth and the number of people who own we alth. It turned out that 20 percent of people in Italy own 80 percent of the we alth, while the remaining 80 percent of the population is content with the remaining 20 percent.
It's funny that, like Mendel, he started with peas. As if he removed the crop from the garden and began to count. 80 percent of the peas were in 20 percent of the pods. How could he think that by doing so he sets an example of ABC xyz analysis for future analysts?
It turned out that the magic ratio works not only for peas and the proportion of rich and poor. It operates in politics, sociology, computer technology. It's a sin not to use it in business. Especially since it was intended for him.
ABC. Start analysis
The 20/80 principle successfully formed the basis for analyzing the state of various business factors. The first three letters of the alphabet, ABC, which became the name of the method, symbolize the sequence of the analysis process.
The first letter indicates the need to select an object for analysis. It can be product groups, supplier or customers: everything that can be characterized by money.
The second letter says that if the object is selected, it is necessary to determine the characteristics for comparison. Those for which money is a unit of measure. You can select revenue, income, or costs for this purpose. Anything.
The third letter means the last action of the analysis: separationobjects into three groups. The first group will include objects that add up to 80% of the parameter. In the second and third, those that in total will give 20%. The second is divided into two more parts: a large one, with 15% of the sum of the parameter, and a smaller one, which gives the remaining 5%.
These three groups are also denoted by the letters A, B and C. If, for example, the proceeds from the sale of goods are analyzed, then group A will include those that gave 80% of the proceeds. Group B will be represented by an assortment that brought 15% of the total sales to the cashier. The remaining 5% will be distributed among the products of the last group.
"That's all good," the novice analyst will say, "But where is the example of XYZ analysis announced at the beginning?"
A little patience. A little more about ABC
If we ignore the numbers, then the whole analysis comes down to the breakdown of goods into groups. The first group brings the main revenue. The second - most of the rest. The third goes to goods that mean almost nothing to the enterprise. And it's all? Yes.
ABC did the main thing: it made it possible to see the forest for the trees. From a list of a thousand goods, he singled out the main, promising and worthless. What to do with this data is up to the customer of the analysis. Increase the purchase of some, free the warehouse from others, or completely abandon the third depends on many other factors.
But this is clearly not enough for a full-fledged analysis. In addition, the reliability of the data obtained is the higher, the longer the period under consideration. As a rule, this is six months or a year. And you need to respond to changes more often.
Herethis is where XYZ comes in. In the XYZ analysis calculation example.
Second pair of boots
One author wittily compared both analyzes to a pair of boots. The boots can be worn one at a time, but it's better if you wear both at the same time. XYZ analyzes the same products but from a different angle.
By analogy with its predecessor, it also divides goods into 3 groups, which are denoted by X, Y and Z. But the principle of division is different here. The weight, the importance of the goods in the total revenue does not play any role here. They are already defined in the previous analysis.
The parameter by which the grouping takes place is complexly called: the coefficient of variation. Without going into the mathematical details of the standard deviation, it can be defined as the spread of data around some average value.
The data can be the same revenue, income, turnover as in the previous case. Their choice is determined depending on the purpose of the analysis. The algorithm for performing the analysis is almost the same as that used for ABC.
XYZ analysis example
Since the analysis period and the product range list have already been generated, the following steps are recommended:
- Split the period into components. Monthly or weekly depending on the purpose of the analysis.
- In each line, determine the average value of the data for the columns of the period.
- Get the data deviation from the mean.
- Determine the coefficients of variation line by line.
- Sort the range of product groups by coefficient.
- Determine the group to which thecommodity.
The result is a table with monthly fluctuations (variations) of sales deviation from the average value for the entire period. By the way, this table is an example of XYZ analysis in Excel
Products with the smallest deviation from the average sales volume for the period fell into group X. Therefore, the demand for them is stable and not subject to fluctuations. They, therefore, can be laid out on the counter, unloading storage capacity.
Group Z contains goods whose demand fluctuates near zero. Their stocks should be reduced, or maybe switched to order trading.
The criterion for assigning a product to a particular group here is not the Pareto principle, but rather an arbitrary range of values of the coefficient of variation:
- X (high sales stability) up to 10%;
- Y (volatile demand) 11% to 25%;
- Z (random, unpredictable demand) greater than 25%.
Criterion percentages may vary according to specific conditions. If the criterion changed in the range of 15, 35, more than 35 the table would look different:
With the naked eye, you can see how the X group increased and the Y group decreased due to the expansion of the stability range. To perform such an example of calculating XYZ analysis in Excel, just change the formula in the "group" column.
Combining results
It's time to put both analyzes together. This is sometimes referred to as "ABC - XYZ analysis". An example of ABC XYZ analysis in excel is shown below.
The "XYZ group" column shows an example of XYZ assortment analysis.
AX Group represents the best products that occupy the main place in generating profits. Their share is larger than all the others, and they are in constant and steady demand.
Complete opposite - group CZ. These are problematic items that are often ballast and require separate solutions.
Now imagine that all these calculations need to be done not over a dozen goods, but over tens of thousands. But the days of Pareto are over and there is almost nothing to do with your hands.
Excel to the rescue. And not only
The easiest way to do this kind of analysis is in good old Excel. It was originally aimed at processing tabular information. The biggest difficulty is entering primary information.
In our case, this is a list of products and sales volumes. But almost any accounting program has the ability to export data, so it remains only for a person to correctly formulate the task and interpret the information received.
There are other programs. For example, "ABC sales analysis 1.0". Allows you to create up to 20 reports for any parameter involved in the algorithm.
There is "1C:Enterprise 8", in which analysis is the basis of management accounting.
Finally, there are online calculators.
XYZ limits
No matter how perfect this or that tool is, it has limitations and disadvantages. For the considered methods of analysis, the disadvantages are:
- limited number of ranking levels;
- the object in question must be present in all analysis periods;
- unidimensionality of each individual report.
- incorrect analysis with changing dynamics.
Limitation of ranking levels is that three ranges are not enough for correct conclusions.
The absence of an object in at least one reporting period makes the analysis meaningless. For example, in some month the goods were not delivered.
One-dimensionality is expressed in the fact that analysis is possible only according to one parameter, and this is almost always small.
Restrictions on the constant change in dynamics will lead to the fact that a stable selling product with a rising price in each period will fall into the group of unstable goods.
Part of the effect of the last constraint can be seen in the XYZ analysis of the product range when the stability range has changed.
Other analysis methods
Disadvantages and limitations of the considered methods can be partly solved by the presence of other types of analysis. For example, "Wikipedia" has at least 3 more types:
- FMR;
- VEN;
- RFM.
Each of them solves some of his own problems and answers questions that others cannot solve.
Think, count, analyze and ask questions.
