XYZ is not what an acronym lover might think. This is a tool like a slide rule. A simple adaptation for a long time made life easier for those who love mathematics and those who can not 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. As if they love the score. Allegedly love silence. Proverbs about the fact that money refers to analysis in the same way as to silence and account, are not present. But someday it will certainly be.
Because money is like plants that grow well when they are well looked after. And that means acting based on an analysis of many factors. What is the soil? Sour or calcareous? What fertilizers should be applied? The gardener analyzes dozens of factors before making a decision.
But other businessmen would be poor gardeners if their money turned into carrots or cabbage, and offices and warehouses in beds. Because they rely more on randomness and intuition. And the analysis ... well, what is the analysis? Things are going uphill, which means it’s so clear that everything is right. But no, that means not fate.
Meanwhile, a person who relied on the laws of money as a gardener on the laws of crop production has already created a principle that could help both luck and intuition. His name was Pareto.
The rule that was discovered, forgotten and remembered
Wilfredo Pareto was Italian. By the age of sixty, he had accomplished a lot. He was engaged in philosophy, sociology, mechanical engineering and economics.
He discovered the law, which is now called the "Pareto principle." True, he discovered it in 1906, and they called the rule by his name only in 1941. Perhaps this was not a discovery, just as he was not the principle of inheritance formulated by the monk. Mendel simply counted the number of yellow peas in the crop on the pea bed of his monastery.
Monk Mendel became the father of genetics, not knowing about it. I did not have time to learn about my fatherhood and Pareto. But current analysts are in full swing with his 20/80 formula.
Only 2 numbers. But without them, there would be no examples of ABC XYZ analysis.
A bit about patterns
The rule formulated by Pareto, undoubtedly, is the action of some law of nature. This law is probably yet to be discovered. When this happens, they will again remember Pareto.
Someone Fibonacci, also 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. It obeys the ratio of pineapple cells, flower petals and spirals of mollusk shells. What law underlies these phenomena, so far from rabbit breeding, is unknown. Till.
Such is the Pareto principle. It was formulated to correlate the size of wealth and the number of people who own wealth. It turned out that 20 percent of people in Italy own 80 percent of the wealth, while the remaining 80 percent of the population are satisfied with the remaining 20 percent.
It's funny that, like Mendel, he started with peas. As if he had harvested from the garden and began to count. 80 percent of the peas lay in 20 percent of the pods. How could he have thought that he would set an example for ABC xyz analysis for future analysts?
It turned out that the magic ratio is not only valid for peas and the proportion of rich and poor. It operates in politics, sociology, computer technology. It is a sin not to use it in business. Moreover, it was intended for him.
ABC. Start of 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, a supplier or customers: everything that can be characterized by money.
The second letter says that if an 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 expenses for this purpose. Anything.
The third letter means the last action of the analysis: the division of objects into three groups. The first group will include objects that give a total of 80% of the parameter. In the second and third, those that in total will give 20%. The second is divided into two parts: the larger one, with 15% of the parameter sum and the smaller one, which gives the remaining 5%.
These three groups are also indicated by the letters A, B and C. If, for example, the revenue from the sale of goods is analyzed, then group A will include those that accounted for 80% of the revenue. Group B will be represented by an assortment that brings to the cash register 15% of the total sales. The remaining 5% will be distributed among the goods of the last group.
“All this is 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 a breakdown of the product into groups. The first group brings the main revenue. The second is 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 behind the trees. From the list of a thousand products, he identified the main, promising and worthless. What to do with this data is up to the analysis customer. To increase the purchase of some, to free the warehouse from others, or completely abandon the third depends on many other factors.
But for a full analysis of this is clearly not enough. In addition, the reliability of the data is higher, the longer the period under consideration. As a rule, this is six months or a year. And you need to react to changes more often.
This is where XYZ appears. In the example calculation of XYZ analysis.
The second boot in a pair
One author wittily compared both analyzes with a pair of boots. Boots can be worn one at a time, but it would be better if you wear both at once. XYZ analyzes the same products, but from a different angle.
By analogy with his predecessor, he also divides the goods into 3 groups, which are denoted by X, Y and Z. But the principle of splitting is different here. Weight, the importance of the goods in the total revenue do not play any role here. They are already defined in the previous analysis.
The parameter by which the grouping occurs is called difficult: the coefficient of variation. Without going into the mathematical details of the standard deviation, it can be defined as the spread of data relative to a certain average value.
The data may be the same revenue, income, turnover as in the previous case. Their choice is determined depending on the purpose of the analysis. The analysis execution algorithm is almost the same as that used for ABC.
XYZ Analysis Example
Since the analysis period and the list of product ranges have already been generated, the following steps are recommended:
- Break the period into components. Monthly or weekly, depending on the purpose of the analysis.
- In each row, determine the average value of the data for the columns of the period.
- Get the deviation of the data from the average value.
- Determine the coefficient of variation line by line.
- Sort the assortment of product groups by coefficient.
- Identify the group to which the product belongs.
The result is a table with monthly fluctuations (variations) in the deviation of sales from the average 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 for the period fell into group X. Therefore, the demand for them is stable and not subject to fluctuations. They, therefore, can be put on the counter, unloading storage capacity.
Group Z included goods whose demand fluctuates around zero. Their stocks should be reduced, and perhaps go to trade on orders.
The criterion for classifying a product as a particular group here is not the Pareto principle, but a rather arbitrary range of variation coefficient values:
- X (high sales stability) up to 10%;
- Y (volatile demand) 11% to 25%;
- Z (random, unpredictable demand) more than 25%.
Percentage of the criterion may vary according to specific conditions. If the criterion changed in the range of 15, 35, more than 35 the table would look different:
The naked eye can see how, due to the expansion of the stability range, group X increased and group Y decreased. To perform this example of calculating XYZ analysis in Excel, it is enough to change the formula in the column "group".
Combining Results
The time has come to combine both analyzes. This is sometimes referred to as "ABC analysis - XYZ". An example of ABC XYZ analysis in excel is given below.
In the column “XYZ Group” an example of XYZ assortment analysis is given.
The AH group represents the best products that occupy a major place in profit generation. Their share is greater than all others, and they are in constant and steady demand.
The exact opposite is the CZ group. These are problematic products, which are often ballast, and require separate solutions.
Now imagine that all these calculations need to be done not over a dozen goods, but over a dozen thousand. But Pareto’s days have passed and almost nothing needs to be done with your hands.
Excel to the rescue. And not only
The easiest way to do this kind of analysis in the good old Excel. It was originally aimed at processing tabular information. The greatest difficulty is the introduction of primary information.
In our case, this is a list of goods and sales volumes. But almost any accounting program has the ability to export data, so it remains 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 on any parameter involved in the algorithm.
There is "1C: Enterprise 8", in which analysis is the basis of management accounting.
Finally, there are calculators online.
XYZ limitations
No matter how perfect a particular tool is, it has limitations and shortcomings. For the considered analysis methods, the following are considered disadvantages:
- limited number of ranking levels;
- the object in question should be present in all periods of analysis;
- the one-dimensionality of each individual report.
- incorrect analysis with changing dynamics.
The limitation of the ranking levels is that three ranges are not enough for correct conclusions.
The absence of an object in at least one reporting period completely deprives the analysis of meaning. For example, in some month the goods were not delivered.
One-dimensionality is expressed in the fact that analysis is possible only in one parameter, and this is almost always small.
Restrictions on the constant change in dynamics will lead to the fact that a stably selling product with a rising price in each period will fall into the group of unstable goods.
Partially, the effect of the latter restriction can be observed using the XYZ analysis of the product range as the stability range has changed.
Other analysis methods
The disadvantages and limitations of the methods considered can be partially solved by the presence of other types of analysis. For example, Wikipedia has at least 3 more species:
Each of them solves some of their problems and answers questions that cannot be solved by others.
Think, think, analyze and ask questions.