As you know, trade involves the purchase of goods and their subsequent sale. Trading will be successful if you can stock up in advance on those goods that will be in demand, rather than spending a lot of money on a product whose relevance will soon decrease. A properly stocked inventory of goods can bring you good income and compensate for the troubles caused by the uncertainty of the trading business, and xyz analysis will allow you to analyze your sales and draw conclusions about the predictability (or, conversely, unpredictability) of demand for a particular product. Agree, it is much more convenient to purchase goods that are in constant demand, rather than spending most of the budget on a product that is in demand today, but will lose its relevance tomorrow. So, what is good about xyz analysis, how does it interact with abc analysis ohm and is it possible to carry it out at home using Microsoft programs Office Excel?
What is xyz analysis?
In contrast to the abc xyz analysis, the analysis divides the entire store assortment into groups that differ in the predictability of demand for the product. In fact, abc analysis allows us to find out which product brings in the most revenue (or sells best), and xyz shows the uniformity of demand for the product.
How to conduct a full xyz analysis? First you need to create full list the goods you sell and collect information (that is, find out sales volumes). All the data you collect is entered into a table, and using standard Microsoft features Office Excel finds the coefficient of variation for each product (formulas are given below). Next, the list of products is sorted by increasing coefficients of variation and divided into three categories - X, Y and Z.
The coefficient of variation varies from 0 to 1. Group X includes goods whose demand falls in the range from 0 to 0.1. Category Y are products whose coefficient of variation ranged from 0.1 to 0.25. Group Z includes all other products - that is, products with a coefficient of variation of more than 0.25.
Detailed description of categories
Category X includes goods with the smallest coefficient of variation - that is, those for which demand does not change over time (or changes, but only slightly). The sales volume of such goods is easiest to predict. Example: if you sold n cases of alcohol this month, then next month your sales will also be n cases.
Category Y includes goods whose consumption changes over time, but not critically. As a rule, “seasonal” goods fall into this group - that is, those for which demand can be approximately predicted.
Category Z is the most unpredictable. It includes goods for which demand cannot be predicted. Example: if you purchased n cans of canned food this month, this does not mean that next month you will sell n cans again. Sales may double or, on the contrary, drop to zero. Of course, such products are difficult to deal with, and some of them can be wisely avoided.
General table for abc-xyz analysis
If you have used abc analysis and then conducted xyz research, you can use a combination of these methods to deepen your knowledge of your business's product offerings. Create an assortment matrix that will help identify the most in-demand and most illiquid products. Create a table (3x3, a total of 9 cells). Label the columns with the letters "A", "B" and "C", and the lines with the letters "X", "Y" and "Z". Now go through the list of products and divide them into nine cells depending on their contribution to total revenue (abc analysis) and sales predictability (xyz analysis). Based on the results of this table, you can safely form the assortment of your store!
Thus, cell AX will contain goods that are in stable, predictable demand and provide your company with maximum profit. Of course, these are the undisputed market leaders! Remember to focus on cells AX, BX and AY. Cells BY and CY represent products that have good potential and need marketing promotion. Also, xyz-abc sales analysis will identify the most unprofitable and unpredictable products - they will remain in cells BZ and CZ. Analyze the reasons for their unpopularity. Perhaps these products could benefit from a price cut, or they may not be needed by customers and may be discontinued.
How to do xyz analysis?
So let's start exploring! Qualitative xyz analysis consists of several important stages:
- choice of object (for example, food);
- selection of a parameter (number of product units sold per month);
- selecting a period (it is best to analyze data collected over at least 12 months). Remember: the longer this period, the more accurate the analysis results will be.
- determination of the coefficient of variation for each of the goods indicated in the list. The coefficient of variation is a value that shows how much the monthly sales of a particular product differ from its average sales for the entire period we are studying.
To execute abc-xyz analysis in excel, just use the following formulas:
Here V is the coefficient of variation, σ is the standard deviation, Xi is the sales volume of a particular product for i-th period time, X with a cap is the average number of sales for the entire period, and n is the number of time periods studied (for example, 12 if you are studying data for 12 months).
After you find the values of the coefficients of variation for each of the goods, you just have to arrange them in increasing order of the coefficient and divide the goods into categories X, Y and Z. If V is greater than 0, but less than 0.1, the product falls into group X; if V is more than 0.1, but less than 0.25 - to group Y; if V is more than 0.25 - to group Z.
xyz analysis of a grocery store
Let's consider a specific abc-xyz analysis - the example concerns a food supermarket, all of whose products we will conditionally divide into the following product groups: bakery products, dairy products, meat products, alcoholic products, confectionery, canned and deep-frozen foods.
Having performed an abc analysis, we find that category A includes dairy products and alcoholic products, that is, the supermarket’s revenue from these goods is the most significant. Group B includes meat products and bakery products, and group C includes deep-frozen products and any canned products that are sold less frequently and, accordingly, bring in less profit.
By performing xyz analysis, we will understand which types of products have stable demand and which ones have unexpected demand. Thus, group X includes consistently selling meat and alcohol products. Category Y combines bakery products, dairy products and frozen semi-finished products, the demand for which is much more difficult to predict. The most unpredictable were canned foods and confectionery products.
Having compiled a 3x3 table and distributing goods according to it, we conclude that the greatest attention should be paid to alcohol, meat and dairy products, because they are the ones that provide the grocery store with the largest and most stable profit.
Recommendations for handling goods from different columns/columns
If you have conducted a product analysis and distributed the entire range of your enterprise into nine cells, our tips will tell you what to do with each of these products.
So, the products that fall into the columns signed with the letters A and B provide the company with the main turnover and provide maximum profit. Products from columns A and B cannot be discontinued from production or sale; they must be in stock 24 hours a day, 7 days a week, otherwise you will lose your profit, and customers will go to another store or contact representatives of another company. Products from column C require periodic control. If you don’t have enough time for painstaking business planning, products from column C can be given minimal attention.
Line X contains the most stable and best-selling products. By conducting a detailed xyz analysis and finding out how many products from category X were sold last month, you can purchase the same amount of these products for the next month, and most likely you will not be mistaken. Make sure that there is always a certain stock of goods from category X in the warehouse.
Category Y represents products for which demand fluctuates over time. Analyze these products - they may be subject to seasonal fluctuations. For example, if you remember Easter, then increasing consumer interest in chicken eggs will not come as a surprise to the supplier. 
Products from category Z cannot be predicted, so it is best not to buy a large number of goods “for future use” and not to keep them in a warehouse, but to provide for the delivery of such products “to order” or as demand arises.
Remember that goods from cells AX and AY are in the most stable demand. It is best to free up maximum funds for the purchase of goods from this category, saving on the purchase of goods from the CZ group.
Items in the BY bin need a good safety stock, so ensure they are always available in your warehouse.
Product groups from cells AZ and BZ sell well, but their sales volume is difficult to guess. Transfer products from these cells to separate system orders - for example, deliver them often, but in small quantities, controlling sales volumes. The most experienced manager of the enterprise should work with these groups of goods; it is unacceptable to entrust them to a novice.
In addition, reduce the safety stock of products from the CX group, and form the safety stock of products from the CY cell only if the company has free in cash. Products from the CZ group can be safely removed from sales, and the money saved can be distributed among more “promising” cells of the table.
This is a tool that allows you to study the product assortment, determine the rating of products according to the specified criteria and identify that part of the assortment that
which provides maximum effect.
The assortment is usually analyzed according to two parameters: sales volume (quantity sold) and profit received (realized trade margin). ABC analysis is based on the Pareto rule, according to which 20% of assortment items provide 80% of the profit.
Practice shows that 10% of assortment items (group A) account for 80% of turnover; 15% of assortment items (group B) provide 15% of turnover; 75% of assortment items (group C) account for 5% of turnover.
Taking this into account, the entire range trading enterprise can be divided into groups according to importance.
- Group A - very important products that should always be present in the assortment. If sales volume was used as a parameter in the analysis, then this group includes top sellers by quantity. If trade margin was used as a parameter in the analysis, then this group includes the most profitable products.
- Group B – goods of medium importance.
- Group C - the least important products, these are candidates for exclusion from the assortment and new products.
The first stage of ABC analysis is to identify goals. If the goal is to reduce the assortment, then sales volume and profit are selected as the main parameters. If the goal is to identify and reduce the costs of maintaining inventories, then the turnover ratio, the volume of illiquid stock and the occupied warehouse capacity are selected as the main parameters. If you want to study profitability, then the turnover ratio and the level of profitability are selected as the main parameter. ABC analysis data helps optimize the product range.
With all the many advantages of this type of analysis, there is one significant disadvantage: this method does not allow assessing seasonal fluctuations in demand for goods.
XYZ analysis
XYZ analysis is a tool that allows you to divide products according to the degree of stability of sales and the level of fluctuations in consumption.
The method of this analysis is to calculate the coefficient of variation or consumption fluctuation for each product item. This coefficient shows the deviation of flow rate from the average value and is expressed as a percentage.
The parameter can be: sales volume (quantity), amount of sales, amount of sales trade margin. The result of XYZ analysis is the grouping of goods into three categories, based on the stability of their behavior:
- Category X, which includes products with sales fluctuating from 5% to 15%. These are goods characterized by stable consumption levels and a high degree of forecasting.
- Category Y, which includes products with sales fluctuating from 15% to 50%. These are products 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 ABC analysis allows you to evaluate the contribution of each product to the sales structure, then XYZ analysis allows you to evaluate sales jumps and its instability. It is recommended to do a combined analysis, where ABC analysis uses two parameters - 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 the profitability of product groups, evaluate logistics, and evaluate the clients of a wholesale company.
Advantages of combined ABC and XYZ analyzes
Using a combined ABC and XYZ analyzes has a number of significant advantages, which include the following:
- increasing 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 goods and reasons affecting the number of goods stored in the warehouse;
- redistribution of personnel efforts depending on qualifications and experience.
Formation of ABC- and XYZ-analysis indicators
Before combining the indicators of ABC and XYZ analyses, it is necessary to conduct an ABC analysis of goods by the amount of income received or by the number of products sold for a certain accounting period, for example, a year.
Then an XYZ analysis of these goods is carried out for the same period, for example, by the number of monthly sales for the year. After this, the results are combined.
When combined, nine product groups are determined:
| AX |
BX |
CX |
| AY |
BY |
C.Y. |
| AZ |
BZ |
CZ |
Identification of nine product groups using combined ABC and XYZ analysis
1) Products groups A and B provide the main turnover of the company, so 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 is created for goods of group B.
Using XYZ analysis allows you to more accurately configure the inventory management system and thereby reduce the total inventory.
2) Products groups AX and BX distinguished by high turnover and stability. It is necessary to ensure constant availability of goods, but for this there is no need to create excess safety stock. The consumption of goods in this group is stable and well predicted.
3) Products groups AY and BY with high turnover, they have insufficient stability of consumption, and, as a result, in order to ensure constant availability, it is necessary to increase the safety stock.
4) Products groups AZ and BZ with high turnover, they are characterized by low predictability of consumption. An attempt to ensure guaranteed availability for all goods in a given group only through excess safety inventory will lead to the fact that the company's average inventory will increase significantly. Therefore, the ordering system for products in this group should be revised:
- transfer part of the goods to an ordering system with a constant amount (volume) of the order;
- ensure more frequent deliveries of some goods;
- select suppliers located close to the warehouse, thereby reducing the amount of safety inventory;
- increase the frequency of monitoring;
- entrust work with this group of products to the most experienced manager of the company, etc.
5) Products Group C make up up to 80% of the company's product range. The use of XYZ analysis can greatly reduce the time that a manager spends on managing and monitoring the products of this group
6) By product CX group You can use an ordering system with constant frequency and reduce safety inventory.
7) By product CY groups you can switch to a system with a constant amount (volume) of the order, but at the same time create a safety stock based on the financial capabilities of the company.
8) B CZ product group all new goods, goods of spontaneous demand, supplied to order, etc. are included. Some of these goods can be painlessly removed from the assortment, and the other part needs to be regularly monitored, since it is from the goods of this group that problems arise.
Once upon a time, a store owner, who was also a seller, could easily remember all the products in his assortment. Tell about the features of each, the story, how effective the product is, knew exactly how it is sold, when to order more...
With the development of retail, managing the movement of goods requires different approaches. Sales accounting and analytics systems and assortment management complement the experience of store or retail chain employees.
Serious decisions, such as removing a product from the range, are not made so easily. Both the category manager and the store manager need justification for such actions.
Therefore, one type of analysis is not enough. A combination of several types is used (in other words, cross-analysis).
In this article, using the example of the “Confectionery” product group, we will consider the main approaches to organizing cross-analysis. We’ll also find out who is to blame for the fact that Raffaello is a product with unstable sales.
When working with the assortment of a chain or store, a cross-approach is used, which includes ABC and XYZ analyses.
What is the point and why do we choose them?
ABC analysis- this is a common method of studying the assortment, with which you can determine the contribution of each product to the turnover and profit of the store, distribute products into categories for effective management assortment.Basic pros ABC analysis:
- rationalization of assortment management - makes it clear the importance of goods, distributes the efforts of store managers and category specialists;
- quick results and quick application of management decisions;
- if carried out regularly, comparison with previous periods makes it possible to track stages life cycle goods.
- many additional factors must be taken into account, such as new products, luxury products, etc.
- you need an array of data for analytics for a certain period: a database of receipts, sales or other retail data;
- Stability in the goods market is important; if some unforeseen situations occur (sharp inflation, political unrest, etc.), ABC analysis data may be inaccurate.
XYZ analysis determines the stability of product sales over a certain period. The results allow you to categorize products and allocate storage space, inventory levels, and delivery arrangements.
pros XYZ analysis:
- data for managing the range of goods and warehouse stocks, organizing work with suppliers;
- setting different options delivery for different categories goods;
- using analysis to predict demand stability;
- identification of problem stores with unstable sales;
- identification of commodity holes, correction of the goods supply system.
- What is needed, just like for ABC, is stability of indicators, without market shocks;
- several years of data are required for a full analysis;
- it is difficult to work with seasonal goods, and there are quite a lot of them in retail;
- cannot be used on products with a short life cycle.
Combining the results of ABC and XYZ analyzes is a popular approach to product assortment management. Both methods complement each other well. If ABC analysis allows you to evaluate the contribution of each product to the sales structure, then XYZ analysis allows you to evaluate sales jumps and its instability.
By combining and using a cross-approach, we obtain the status or place of each product in the assortment of a product group, store or entire retail chain.
The use of combined analysis provides a number of additional advantages:
- identification of products with stable sales, significant for the turnover of a store or chain, and unprofitable products;
- increasing the share of profitable products without violating the principles of assortment policy;
- determining the reasons affecting the quantity and location of goods stored in the warehouse;
- redistribution of personnel efforts to manage the assortment and its warehouse stocks.
Methods for conducting combined analysis
There are 2 ways to conduct cross-analysis: sequential and parallel.The choice of one of them depends on the purpose and desired results. Let's explain in more detail.
Sequential method assumes that the analysis is first carried out according to one of the types, according to a separate criterion. Next, for each of the resulting categories, analysis is applied according to 2 criteria, or type, etc.
Visually it looks like this.
This approach is used for large data sets. For example, if cross-analysis is carried out across the entire assortment of the chain, across a large product group.
The second aspect is that such an analysis requires significant analytical efforts by its organizer. It is necessary to determine the importance of each criterion for future analysis and build the analysis structure in the right order.
For example, the goal of cross-analysis is to optimize space in a store’s warehouse, in which case the first criterion will be the stability of sales, i.e. XYZ analysis, the second criterion is the number of sales of each product according to ABC, the third is turnover, again according to ABC.
When choosing another goal - say, identifying luxury goods - the first analysis criterion will be Store Turnover, then the Number of Sales and Entry into Receipts. But the stability of product sales using XYZ analysis is not so important here.
Those. sequential analysis can be carried out if the number of products in a product group is large enough, if it is necessary to determine a policy for working with the group, develop a strategy for managing and promoting the necessary products.
Parallel The approach involves constructing a category matrix based on a given number of criteria, analyzing and working with each category in a cell of the matrix.
This is what the matrix for cross analysis of ABC and XYZ looks like. Usually, the analyst, in any case, simplifies it further and determines the general format of work for several categories.
For ABC analysis, the matrix can be built according to several criteria. For example, in the picture below A by turnover + A by number of sales + X by sales stability.
Let’s analyze the results obtained and go through only the key (boundary) categories:
АААХ - for leading products;
АААZ - goods that are important for the network’s turnover, but with unstable sales, which definitely need to be paid attention to;
BBBY - for average products;
CCCZ - for outsider goods.
Category AAHH- 28 products out of 1260.
The sweetest products. They provide the main turnover of the network, so their constant availability is necessary.
If we analyze the products included in this category, these are mainly chewing gum, cookies and popular children's sweets.
As you can see in the picture below, greatest number sales of chewing gum sold at cash registers also have a fairly significant impact on the turnover of the product group.
There are two options for working with the category - creating excess “safety” stock or organizing “just-in-time” delivery, since goods are sold consistently and an accurate sales forecast is possible.
But it is worth remembering that AAX category products are key to the store’s operation, so if delivery fails at any stage, this can lead to significant problems in the store’s operation.
Why, given the high number of sales, significant impact on store turnover and frequent receipts, is sales of goods in this category so difficult to predict?
Visualization of key indicators - turnover and number of sales - showed that Raffaello sweets have the greatest impact on the turnover of the chain and its product group.
Judging by our life experience, they are quite popular. Why are their sales so unstable, the coefficient of variation is 0.73?
Having opened sales of a specific product, we received an answer to the question.
Sales of sweets of this brand simply take off twice a year - on February 14 and on March 8. Hence the instability.
- transfer part of the goods to an ordering system with a constant order amount (volume);
- ensure more frequent deliveries of some goods;
- choose suppliers located close to the store, thereby reducing the amount of inventory;
- increase the frequency of monitoring.
Sales of goods are not stable enough to ensure constant availability, it is necessary to maintain a warehouse stock for insurance.
You can switch to a system with constant time or order quantity.
CCCZ, as the bottom category of the matrix, objectively the most problematic products - 85 entries.
It is important to remember that this may include new products, luxury goods, custom-made goods, etc. Therefore, the category requires a thoughtful analysis of each position, and only then radical action.
The screenshot below shows these products located in the graph area near 0 and having virtually no effect on the turnover of the retail network.
One of the most common methods of assortment analysis is ABC analysis, which is based on the Pareto principle. The latter, in turn, states that 80% of the causes are responsible for 20% of the consequences. This rule, also called 80/20, means that in any process a small percentage of reasons (20%) are vital, and the remaining reasons (80%) do not have a serious impact on the final result.
This rule can be applied to various socio-economic phenomena and business processes. For example: 80% of the work is completed in 20% of the time; 80% of the company's profit comes from 20% of customers; 80% of warehouse space is occupied by 20% inventory; 80% of sales volume is provided by 20% of sellers; 80% of problems are caused by 20% of defects, etc.
In each specific case, this proportion may deviate from the exact parameters of 80/20 and be 90/10 or 65/35. However, this does not change the essence and is that the largest percentage of the result is provided by a smaller percentage of costs.
As part of the assortment analysis, this rule says that 20% of products provide 80% of the store’s turnover.
ABC analysis method will allow you to determine those 20% of goods that are a priority for the store. In essence, this method involves ranking the product range according to various parameters. Traditionally, the entire assortment is divided into three groups of products depending on their contribution to the turnover and profit of the store:
1) goods of group A - the most important goods, providing the first 50% of results;
2) goods of group B - goods of medium importance, providing another 30% of the results;
3) goods of group C - the least significant goods, providing the remaining 20% of the results.
Traditionally, ABC analysis is carried out in three stages.
Stage 1. Determination of the object of analysis and parameters for its assessment.
The objects of analysis can be:
product groups;
commodity items;
suppliers.
Evaluation criteria may include:
sales volume (in physical and monetary terms);
turnover;
inventory, etc.
Stage 2. Compiling a list of analysis objects in descending order of the value of the selected parameter.
Stage 3. Determination of groups A, B and C, for which it is necessary to calculate the share of the parameter from the total sum of parameters with a cumulative total and distribute the objects of analysis into groups in accordance with the obtained values.
Let's consider ABC analysis by product groups for specific example grocery store, operating in the “shop near home” format.
Stage 1. Within the framework of this analysis, the object of research is product categories, including:
milk products;
bakery products;
confectionery;
alcoholic products;
fruits and vegetables;
meat, sausage;
juices, water.
To carry out the analysis, it is necessary to systematize information on the turnover of each product group for a certain period (month, quarter, year). After this, it is necessary to calculate the share of each product group in the total turnover of the store (table).
Stage 2. Sort product groups in descending order of their share in turnover.
Trade turnover of product groups for the 1st quarter of 2010
Table
ABC analysis of store assortment
Thus, the ABC analysis shows that the main share of the store’s turnover is provided by such product groups as dairy products, baked goods and meat, sausages, which belong to group A. Product groups - juices, waters and fruits, vegetables belonging to group C, need development and require additional sales promotion actions, for example in the form of price reductions or product range expansion.
However, ABC analysis by product groups gives only a superficial idea of the structure of the store’s assortment. For more detailed and deep analysis It is advisable to conduct a similar analysis for product categories within product categories, since it is more informative and allows for more efficient management of the assortment.
For a deeper study of the assortment, you can use XYZ analysis. It allows you to create a more complete picture trading process in the shop.
XYZ analysis also involves dividing the store’s assortment into groups X, Y and Z, and the criterion for this analysis may be the profitability of goods or the stability of their sales.
In this case, most often the need to conduct an XYZ analysis arises when the store is faced with the task of analyzing the assortment simultaneously according to several parameters and presenting its results in the form of a matrix.
Let's consider the combination of ABC and XYZ analysis using the example of a food store.
The following product groups are the object of research:
milk products;
bakery products;
meat and sausages;
confectionery;
alcoholic products;
fruits vegetables;
juices, water.
The combined analysis includes the following stages.
Stage 1. Conducting ABC analysis. The criterion for dividing goods into groups within the framework of this analysis is the share of the product group in the total turnover of the store. The results of the analysis are presented in table.
ABC analysis
Stage 2. Conducting XYZ analysis. At this stage, the criterion for dividing goods into groups is the share of the product group in the store’s gross profit. The results of the XYZ analysis are presented in table.
Table
XYZ analysis
Stage 3. Combined analysis (ABC- and XYZ-). It is necessary to combine the results of ABC and XYZ analysis, as a result of which the entire assortment of the store is divided into 9 segments based on two criteria - the share of the product group in the store’s turnover and the share of the product group in the store’s profit. The results of the combined analysis are presented in table.
Table
ABC and XYZ analysis
The analysis made it possible to identify the most profitable and least profitable product groups for the store.
Thus, the most profitable product groups for a store are AX, BX, AY - groups that provide the greatest contribution to the turnover and profit generation of the store.
Groups requiring special interventions to improve their effectiveness include BY and CY. These groups have sufficient potential, but in order for them to move into the group of profitable products, it is necessary to optimize the assortment and pricing policy regarding these groups.
Finally, the least profitable and poorly traded products include the BZ and CZ segments. These product groups require special attention from the store management.
Forecast averages economic indicators -
it's like telling a person who can't swim
that he will calmly ford the river, because
its average depth is not more than four feet.
(Milton Friedman)
The prerequisites for this article were numerous questions from pharmacy directors and purchasing department managers regarding optimal structure inventories of a retail enterprise and methods for forecasting the sales of seasonal goods.
In the November 2004 issue of the Pharmaceutical Review, we already wrote about the ABC analysis method and its use in pharmacies.
Let us remind you
ABC - analysis...
The progenitor of ABC analysis was the Pareto rule, discovered in 1897 by the Italian economist Vilfredo Pareto(Vilfredo Pareto, 1848-1923), which says: 20% of assortment items bring 80% of the profit. In practice, for pharmaceutical retail companies, the ABC analysis is as follows:
10% of assortment items (group A) gives 80% of turnover;
15% of assortment items (group B) gives 15% of turnover;
75% of assortment items (group C) account for 5% of turnover.
So, 25% of assortment items make up 95% of the turnover, recalculating the units of the average pharmacy - 750 - 2,000 items.
With all the many advantages of this method, there is one significant disadvantage: this method does not allow assessing seasonal fluctuations in drugs.
Seasonal drugs from group “A” can migrate to both group “B” and group “C”, placing a serious burden on the inventory balances of the pharmacy, and an ill-considered reduction of group “C” can lead to a reduction in the assortment of the pharmacy and, as a result, a decrease in turnover, in the same time; the remaining goods will be distributed again according to the same principle. To make decisions about optimizing the pharmacy assortment and inventory structure, the most progressive managers use a combination of ABC analysis and XYZ analysis, which will be discussed below.
XYZ analysis….
XYZ analysis is a method that allows you to analyze and predict the stability of certain business processes or business objects (for example, the stability of sales individual species goods, predictability of market behavior various groups buyers, fluctuations in the level of consumption of certain resources, etc.).
In combination with ABC analysis, this is the identification of undisputed leaders (group AH) and outsiders (C Z).
The result of XYZ is a grouping of resources into three categories:
In combination with ABC analysis of XYZ, this is the identification of undisputed leaders (group AX) and outsiders (C Z). In total, when conducting a combined analysis, we obtain nine groups of goods:
Group X includes products with sales fluctuations during the year from 5 to 15%,
group Y - from 15 to 50%; group Z includes products with unpredictable sales fluctuations and, as a result, not predictable.
For whom is XYZ analysis of greatest interest:
First of all, these are distributors and manufacturers who have their own warehouses in Russia.
It is no secret that any purchase of drugs is associated with high costs for the company (logistics, storage, etc.), as well as with direct risks, such as writing off goods due to their expiration date. Maintaining accurate, balanced purchasing is a priority for both wholesale and retail enterprises, especially in the pharmaceutical industry, where many drugs are seasonal and therefore subject to significant fluctuations in sales.
ABC analysis and XYZ analysis complement each other perfectly. If ABC analysis allows you to evaluate the contribution of each drug to the sales structure, then XYZ analysis allows you to evaluate sales jumps (its instability).
Methodology for performing XYZ analysis.
The XYZ analysis technique consists of assigning a coefficient of variation to each product item.
The coefficient of variation…
In our case, this is a coefficient showing the deviation of the analyzed indicator from the arithmetic mean. The coefficient of variation is expressed as a percentage.
For example: a coefficient of variation value of 1% or less indicates that average sales for the drug are little subject to seasonal (or other) fluctuations; a coefficient of variation value of 25% is a signal that for these drugs the average sales value is not correct.
Calculation of the coefficient of variation (V).
Formula for calculating the coefficient of variation:
Where, x i — parameter value for the assessed object for i- th period,
x is the average value of the parameter for the analyzed object of analysis,
P - number of periods.
Using the example of several drugs, let us consider the combination of ABC analysis and XYZ analysis methods.
First stage. Definition of objects of analysis.
The objects of analysis can be: product group, product unit, suppliers, customers, etc. In our case, the objects of analysis are commodity units (see Table No. 1, column 1)
Second phase. Determination of the analysis parameter.
Analysis parameters can be: sales units in pcs.; sales volume in rubles; income in rubles, average inventory in rubles; number of orders in rub. etc. In our example - sales in pcs. (see table No. 1, column 3)
Third stage. Determination of analysis periods.
Analysis periods: week, month, quarter, half year, year.
In our example - a quarter. (see Table No. 2).
For example, let's take several drugs ranked in accordance with the ABC analysis method, with the drug Valocordin drops - without a pronounced seasonality, the drug Vitrum - subject to seasonal fluctuations, the drug Claritin - with a pronounced seasonality. All three drugs have rank A and are on priority purchases, and the volume of purchases is calculated based on average sales.
Table No. 1.
Name | Sales for the quarter, pcs. | Average retail price, rub | Sales for the quarter, rub | arithmetic sales, month/piece |
|
Vitrum No. 100 | |||||
Claritin tab No. 7 | |||||
Valocordin drops 20 ml |
Fourth stage. Calculation of the coefficient of variation.
Calculation of the coefficient of variation for these drugs.
Table No. 2. Sales, month/piece
Name | |||
Vitrum No. 100 |
Calculation:
a) calculate the radical expression
Radical expression (vitrum) = ((Sales January 04 (Table No. 2) - Average sales (Table 1) DEGREE 2+ Sales Feb.04 (Table No. 2) - Average sales (Table 1) DEGREE 2 +Sales March 04 (Table No. 2) - Average sales (Table 1) DEGREE 2)/3 (number of periods))
b) extract the root, divide by average sales and convert to percentages
V (vitrum) = ROOT(radical expression)/16*100%=10%
To perform calculations in Excel:
Cell A3= January sales
Cell A4= February sales
Cell A5= March sales
Cell B5= average sales
Cell A7=radical expression
Calculation cell formula for radical expression : =((DEGREE(A3-B5;2)+DEGREE(A4-B5;2)+ DEGREE(A5-B5;2))/3); cell A7
Calculation cell formula for the coefficient of variation: = SQRT(A7)/16
The cell format must be set to PERCENTAGE.
For Vitrum No. 100, the coefficient of variation was 10%, according to XYZ analysis is the rank of X.
Table No. 3. Sales, month/piece
Calculation:
V (claritin)= ROOT ((Sales Jan.04 (Table No. 3) - Average sales (Table 1) DEGREE 2+ Sales Feb.04 (Table No. 3) - Average sales (Table 1) DEGREE 2 +Sales March 04 (Table No. 3) - Average sales (Table 1) DEGREE 2))/3 (month)/15 (average sales)*100%
V (claritin) = 31%, XYZ analysis rank - Y.
Table No. 4. Sales, month/piece
V (valocordin)= ROOT ((Sales January 04 (Table No. 4) - Average sales (Table 1) DEGREE 2+ Sales Feb.04 (Table No. 4) - Average sales (Table 1) DEGREE 2 +Sales March 04 (Table No. 4) - Average sales (Table 1) DEGREE 2))/3 (month)/51 (average sales)*100%
V (valocordin) = 6%, XYZ analysis rank - X.
So, we got the following coefficients of variation:
Vitrum No. 100 - 10% - rank AX
Claritin No. 7 - 31% - rank AY
Valocordin 20 ml - 6% - rank AX.
From the pharmacy’s point of view, perhaps these are small deviations - a few packages; these indicators look completely different in distribution companies, where tens of thousands of packages are counted and demand jumps of 10% are significant.
Product items are grouped according to increasing coefficient of variation in each ABC subgroup, i.e.:
AX group - up to 15% coefficient of variation;
AY group - from 15% to 50% coefficient of variation;
AX group - over 50% coefficient of variation;
BX group - up to 15% coefficient of variation;
BY group - from 15% to 50% coefficient of variation;
BX group - over 50% coefficient of variation;
CX group - up to 15% coefficient of variation;
CY group - from 15% to 50% coefficient of variation;
CX group - over 50% coefficient of variation;
How often should you perform XYZ analysis...
XYZ - analysis in a pharmaceutical enterprise is recommended to be carried out at least once a quarter, and it is possible to adhere not to a strictly calendar quarter, but, for example, to conduct seasonal analytical sections.
Scope of application of XYZ analysis…
To optimize the assortment:
1. reducing the share of CZ drugs;
2. increasing the share of AX drugs;
3. a group of drugs with rank X may have some stock, at the same time, purchases for a group of drugs with rank Z should be carried out with the utmost care (for pharmacies, work on such drugs can proceed according to individual order).
To assess the profitability of product groups:
It is clear that the group of drugs with index X has a high turnover, and drugs in this group are included in the so-called TOP (leaders) of sales. As a rule, distributors (pharmacies) try to offer attractive (special) prices for such drugs and give additional discounts, but these groups, when assessed, show high distribution costs due to the high intensity of turnover. This situation can have a significant impact on the overall profitability of the groups. Managers of distribution companies making a decision to reduce prices TOTAL for the entire price list should take this fact into account and consider in more detail discount programs for product categories with index X.
To evaluate logistics.
The group of goods AX, AY are goods with a high turnover rate; when calculating the demand for them, it is necessary to take into account the delivery speed in order to eliminate the occurrence of defects in them. Distributing companies usually introduce an additional time factor for these groups.
To evaluate the company's clients.
XYZ analysis has also proven itself well in assessing the company's clients. For the most part, this applies to distributors. Using XYZ analysis in relation to your clients (pharmacies, branches), you can build forecasts for future sales, develop special programs for regular loyal (not subject to various surges in orders) clients. And also carry out various activities to transfer clients from group Y, Z to group X.
In this article we looked at the combination of two analysis methods: ABC and XYZ, which can be used both as independent methods, and comprehensively. Moreover, the analytical data obtained in the second case will be more informative.