Retail sales analysis.

For sales analysis, use the 2Q1D methodology. 2Q1D - "code" in which a certain sequence is encrypted: two "Q" means Quantity (quantity) and Quality (quality), and one D - Developing (development).

Quantity assumes that in a company, regardless of the area, quantitative indicators are monitored for the daily activity of managers (calls, meetings, presentations, transactions) and the number of leads received. This is the basis of the foundations of any sales, which allows you to maximize the width of the “entrance” to the funnel. However, these indicators are absolutely not “sensitive” to the quality of the process. Therefore, for full-fledged work, you also need to monitor quality parameters.

Quality - an array of quality indicators, monitoring of which makes it possible to influence the "width" of the funnel. The main qualitative indicator of the state of sales is both general and from stage to stage. To increase it, conduct qualitative research such as:

1. Segmentation of the current customer base by contract size, industry, product
2. ABCXYZ analysis
3. Customer migration by category
4. Measuring the capacity of a counterparty by determining its share in the structure of its purchases

Developing involves expanding the business through new channels and products. You build new funnels, track average cost lead.

Sales Analysis: Key Reports

Sales Analysis: Electronic Board

This report is important for analysis because it contains basic information about the situation.

Items that an electronic board should contain:

1. Percentage of completed tasks for the current date

Plan for the current date = number of working days x number of days worked

Percentage of the plan completed = Actual on the current date / Plan on the current date

If there is some kind of seasonality or specialization, then there should be a link to the days. For example, retail business is characterized by days especially active sales(weekends) and regular mode (for weekdays). Then you need to evaluate the work of sellers in shifts.

The main mistake that is made in the analysis at this stage is when the fact that the plan is executed on the current date is divided by the monthly plan, and not by the current date. As a result, incorrect data is obtained. Since at the beginning of the month they always buy worse, and at the end of the month the indicators even out. Therefore, if you need to carry out an analysis on the current date, then divide the fact of execution exactly by the date you are interested in.

1. How much money came
2. How much is left to sell before the end of the week to meet weekly goals

A very convenient point for analysis. It is immediately clear how much each manager needs to get to complete the tasks.

1. Monthly plan

Please note that for new employees it should be less. This is a completely normal practice in many companies.

1. Days of the month
2. How many days have passed (including this week)
3. Plan until the end of the week on a cumulative basis
4. Number of days elapsed, including today

This table can be made both in Excel and linked to the 1C, CRM program. Delegate the function of filling it out to managers so that they enter data upon receipt of money.

Ask your department head to send you this report daily. Using it, you can not only conduct an analysis, but also influence the current situation in time: regulate the submission of applications to managers, influence each figure so that all managers can reach the required indicators in time.

Sales Analysis: visualization of results for the entire department

In addition to the fact that there is an electronic reporting form, you should have a real board in your office on which managers write down their results.

We strongly recommend using both electronic and real board formats. When analyzing, you yourself will be surprised how much a real board affects the result. Recording your own results creates certain emotions for managers as well. Of course, if we evaluate not the work of a call center with 150 employees. Only in this case this advice is not appropriate.

Sales analysis: we analyze the payment plan

Tasks for payments for the week and tomorrow should be set in advance, and not at the beginning of the week or day. For example, on Friday of the previous week and the night before.

This allows you to analyze at a calm pace and makes it possible to restructure tasks for the current situation.

Despite the frequent resistance of managers, it is important to fill out such reports. This allows you to do an analysis in terms of achievements / expectations.

Example of a weekly payment plan:

• Manager
• Client
• Client's annual turnover
• What does the client do
  • Product
  • Rate
  • Sum
  • When will pay
  • Link to client
  • Comment

AT retail you can plan only large checks of regular customers.

An example of a payment task for tomorrow:

• Link to CRM
• Amounts
• The product's name
• Addresses, etc.

Sales analysis: unloading from CRM

In the CRM system, you can make automated settings for sales analysis. But before you do, ask yourself if it's right for you.

Because the main problem with an automated report is that sales managers do not always log into the system to view it.

And if this report is available in Excel format, the manager has the opportunity to look at the situation and analyze sales personally. This is what he has to do on his own every day.

Sales analysis: measuring the "potential" of the buyer

The potential of the buyer must be measured in order to understand to whom and how much you can sell. Sales analysis methods allow you to find out. As a result, you don't waste time on customers." low power". And at the same time, you increase the regularity of shipments and the average bill with counterparties with high purchasing potential.

To understand what potential the buyer has, it is necessary to calculate the penetration rate. Under the unusual word "penetration" refers to the share of your products in the total purchases of the client. Measuring this share is one of the basic sales analysis methods for any business.

Determining the share is done in 2 steps.

1. You survey all regular customers by asking them the following questions:

• “How much do you buy the same product from other companies?”
• “What else do you buy elsewhere that you could buy from us?”
• “What needs to be done so that you buy more from us?”

Get ready to receive information about no more than 60-70% of your current base. Many will simply "get away" from the answer. Therefore, in the future, statistical methods of sales analysis should be connected.

2. You need to analyze yourself or order a market research from a third-party agent. So you will understand exactly what its real capacity is, how many participants operate on it, what niches are still free, etc.

Sales Analysis: Researching Customers and Products byABC XYZ

Sales analysis methods based on the current base allow you to track such indicators as the regularity and volume of shipments. They are important because they are the main indicators of business development.

True, fixed in isolation from any benchmarks, the regularity and volume of shipments for each client are not informative. Therefore, when measuring them, it is customary to use such sales analysis methods as ABCXYZ-research and customer migration from category to category.

The ABCXYZ current base study is a sales analysis method that assumes 3 volume categories and 3 regularity categories. Moreover, these categories can be measured not only by the volume and regularity in the context of the buyer, but also in the context of each article - a unit of production.

ABC group:

• A - purchased in large volumes
• B - is acquired in medium volumes
• C - purchased in small volumes

Group XYZ:

• X - purchased regularly
• Y - acquired (s) irregularly
• Z - bought once

As a result, 9 groups of contractors/products are formed at the intersection of these criteria. Then do 4 steps.

1. Thoroughly study the characteristics of AX, BX are your target customers/products. They are most likely no more than 20%, but they give 80% of the profit.

2. Work with loyalty on CX, AY, BY - they have the potential to move to the top categories AX, BX.

3. Pay close attention to what AZ buyers say. They will give you the most valuable recommendations.

4. And it is better to refuse to work with products and clients that are in the BZ, CY, CZ categories. You spend more than you earn on them.

Sales Analysis: Customer Migration by CategoryABC XYZ

Once the picture is clear, move on to the next method of sales analysis - track the migration of customers and products from category to category and the dynamics of this process.

For this, 4 methods of analyzing sales of the entire mass of current customers are used.

Analysis method 1: migration by buyers

Fill out the following form in CRM.

Analysis method 2: migration in the context of a manager

You can use this table.

Analysis method 3: change in the quality of the seller's portfolio in terms of shipments by ABC.

According to this diagram, one can judge that things are not going well with the seller Ivanov. The number of customers who purchased through it in large volumes (category A) fell more than 3 times over the year.

Analysis method 4: control of the regularity of purchases by XYZ in the employee's portfolio

Sales Analysis: Determining the Customer Loyalty Index

How well buyers treat you, that is, the level of their loyalty, depends on the volume and regularity of transactions in the current base. To understand the degree of their loyalty and influence it, a effective method sales analysis - customer loyalty index (Net Promoter Score - ).

We measure and work with it as follows.

1. We conduct a survey among clients. To do this, we ask 2 questions: “On a 10-point scale, how likely are you to recommend our product to acquaintances / friends / relatives?” and “What needs to be done so that next time you give us 10 points?”.

2. Depending on the number of points given to you on the 1st question, we divide all respondents into 3 categories:

• 0-6 points - critics. They are definitely disloyal to you. How not to make money from such negative feedback. Buyers in this group will never recommend you.
• 7-8 points - neutral. This group is happy with everything. But they are not included in the category of your fans. They won't say bad things, but they won't recommend them either.
• 9-10 points - supporters. Truly loyal customers. Sometimes they are also called "promoters", as they actively recommend you to their relatives, friends, partners.

3. Calculate NPS.

NPS \u003d (Number of those who gave 9-10 points / total number of respondents) - (number of those who gave 6 points and below / total number of respondents)

• 5-10% NPS is an extremely low loyalty indicator. Everything is bad. The company is teetering on the edge. Take urgent action. Such an indicator is considered normal only for a very small number of industries, such as real estate agencies and medical centers.

• 45% NPS is normal. Things are going well for you. But this is not the time to rest on your laurels, as you are clearly not among the market leaders.

• 50-80% NPS is the industry leader. Buyers come back again and again. You are at the top of the market. Try your best to hold your position.

Sales analysis:ARC, CRR, LTV– development indicators

To effective methods business development analysis include LTV (Lifetime Value - customer value), ARC (average revenue per customer - average amount for the period), CRR (Customer Retention Rate - retention rate).

Such an analysis method as measuring LTV shows how much a business earns from each of its customers during its “life” as a company client.

LTV=S*C*P*T

S - average bill;

P - profitability as a percentage of the check amount;
T is the average number of months that buyers remain active).

ARC=S*C*P

S - average bill;
C - average number of purchases per month;
P - profitability as a percentage of the check amount.

Always keep track of something as simple as CRR. This is the customer retention rate. It shows how customers come or go. Its positive change by 5% can give an increase in profit up to 25-95%. Such a correlation was noted in studies by scientists at the Harvard Business School.

CRR = ((E-N) / S) x 100

E - the number of buyers at the end of the period;
N is the number of new customers acquired during this period;
S - the number of buyers at the beginning of the period.

Sales Analysis: Key Features of CRM

The main trend in the field of increasing sales is the automation of business processes. This not only reduces errors, minimizes the so-called human factor, but also helps to analyze the situation.

Tip number 1, which we do not get tired of repeating: for sales growth, the implementation of CRM is necessary. In this case, key functions must be configured in the system. One of them is the automatic entry of each transaction in CRM at the time of the first contact. It is important that each potential client immediately enters the system, and not managers decide whether to start it or not. If this feature is set up, you will be able to analyze sales based on real traffic data.

Also, the function of robotic lead distribution should be set up in CRM, depending on the history of communication with the client. That is, the system should see which of the managers worked with whom. In addition, CRM should automatically distribute "warm" and "hot" leads between employees, depending on their qualifications.

In list common mistakes, which interfere with obtaining an objective analysis of sales, are the following:

1. Work in CRM is carried out from the counterparty, not from the transaction. In the first case, you have at your fingertips, in fact, a contact book. If the work in the system goes on transactions, then you can track how the client moves along, at what stage he is “stuck”.

2. Deals use the “Thinking”, “In progress”, “In progress” statuses, which accumulates customers in the funnel and does not transfer them to the next stages. Get rid of these statuses! Try to immediately identify “extra links” among potential customers, exclude from the list those who will never buy from you. Use informative statuses: “commercial offer sent”, “invoice issued”, “paid”.

3. No CRM integration with the site. In this case, 50% of applications are lost, since managers decide on their own criteria which of the applications is “worthy” of processing.

To analyze sales, you need to know customer loyalty. When auto-dial is set up, the task of compiling an NPS rating is noticeably simplified. By the way, if a company has a periodic product / service sales cycle, then analytics should include such an indicator as the share in the client. It is important to understand how many products the buyer takes from your company, and for which he turns to competitors. There are services that, when integrated with CRM, allow you to organize auto-dialing: the system will transfer the call to the manager when the client picks up the phone. That is, automation helps to use working time employee: he does not have to dial the number again and again, wait for an answer. Another feature of such services is that the system can record all conversations, which is useful when analyzing sales.

Arranging a meeting, returning lost customers, making a commercial offer - for any stage you need to have the right script. We suggest throwing paper versions into the trash and developing scripts using modern platforms. In this case, you can always analyze how managers use scripts, what mistakes were made, at what stage the client was lost, etc.

3. Listen to 2-3 calls of each salesperson per month and enter the results of wiretapping into the development sheet.

4. Collect for each subordinate his development folder.

6. Control the number of incoming and outgoing calls.

7. Control their duration.

We have reviewed the main reports that must be kept for a correct analysis of the situation. Check how you have them set up. Assess how complete the picture you can see thanks to the data that employees provide you.

. Sales analysis- this is the first thing a manager must do to effectively influence the result.
Without sales analysis, it is not possible to organize, motivate and control.

Data collection for analysis

Of course, before doing sales analysis we first need to collect the data on which we will work. In sales, there are certain performance indicators (more often called). Each level of management has its own performance indicators. For example, if you are a supervisor or store manager, then most likely you will look at such things as turnover, the percentage of accessories and services sold, evaluate the average check and customer conversion. And if you are the head of the branch director level, then you will be more interested in revenue from square meter, . For a business owner, net profit and the payback period of investments are important. These are just rough examples, but they show how much KPIs vary depending on the level.

Therefore, as a manager, you need to create a list of those KPIs by which you can measure the effectiveness of your subordinates. Also for implementation sales analysis must be compiled.

Definition of normative KPI indicators

To analyze sales results you must have formed, on which you can rely in the assessment. Where can I get them? Some can be found in the public domain, for example, the average check data can be easily found on the Internet, since this is open data that companies declare during annual reporting, to make sure of this, I opened a search engine and, using the search phrase “Lenta store average check”, immediately found open data for 2012 - 1061 rub. Some KPIs are well-known and do not change, such as store conversion household appliances around 30%.

So, for example, my task is to develop KPI for the salon cellular communication which I just opened. Nearby there is a cellular communication salon competing with us. We go there in the morning and buy something, we perform the same operation in the evening. We do not need purchases, but receipts from this store. Numbers are written on them, by simple subtraction we can understand how many purchases were in this store for that day. Average check about 2500-3000 rubles for a salon, you can google it and find out more precisely, of course, a salon on the Arbat and a salon in Tver will have a different average bill. We multiply and get the average revenue per day. But this is not enough, we still need indicators for accessories and additional services(payment for cellular communications, sale of SIM cards, etc.). It all depends on your knowledge, if they are not enough, you can.

Data analysis

So now you have an understanding of what KPIs you will track, you have benchmarks and you have actual indicators. All that remains is to compare and understand what indicators are worth working on. Sales analysis you need to do it all the time, there is a simple rule: the lower the leader, the more often he collects the results and analyzes them. For example, if you analyze the work of sellers, then you need to make weekly slices and you can influence the work of sellers relatively quickly. But for example, it is better to compare the work of a store by months, since it will take different time. For more correct control and analysis of indicators in sales, the term is often used -.

Consideration of objective factors in the analysis of sales

When analyzing sales and results, it is necessary to take into account objective factors that may affect sales. There are several of them:

• seasonal factors. There is always a season in sales. For example, if you sell water heaters, then the season will be from mid-spring to late summer. This does not mean that they will not be bought in the winter. You just need to lay in the seasonal rise or fall.
• geographical factors. The average check of a store located in a prestigious area of ​​the city is always higher than in an ordinary bedroom. Or if your store is located far from the metro, then the traffic will be much less than that of a store located in mall 2 steps from the subway.
• market factors. If your competitor launched a super sale with insane discounts and strong advertising support, then a drop in sales will be expected.

In fact, you can influence everything else. But these factors must always be taken into account.

An analysis of the assortment will help the owner of the company and draw the right conclusions about which goods should be discarded.

Optimization of the company's assortment

To strengths This decision can be attributed to a clear sequence of actions, adhering to which can significantly improve financial results companies. Among the shortcomings, it can be noted that not always certain measures related to the optimization of the assortment can be justified by numbers.

The main signs of an unsuccessfully selected assortment line:

• increase in stock balances both in kind and in value terms; at the same time, the lack of goods demanded by customers;
• growth in the number of assortment positions, accompanied by a decrease in profits;
• lack of resources for the purchase of goods.

In order to put things in order in the company's assortment, it can be recommended to conduct an ABC analysis of the company's product line, and then develop optimization measures for each selected category A, B and C.

How to conduct an ABC analysis of the company's assortment

To optimize the range, use the profit data for specific product groups and items. This is the most objective and reliable information about the situation on the market where the company operates and the needs of customers. And we are talking about profit in ruble terms. It is not always convenient to use such an indicator as profitability for analysis. If you apply profitability, you will also have to collect data on the turnover of goods, otherwise the results of assortment optimization can be depressing. If you make a decision based only on profitability, then there is a high risk that the company's product line will remain items with a high margin (luxury goods), but in rather low demand. The result - a sharp decline in sales and profits in ruble terms.

The source data can be information uploaded, for example, from 1C to Excel and containing information about profits by product groups. The more honestly the profit is calculated for the goods, that is, all expenses for each group are taken into account as much as possible (purchase prices, delivery, packaging, storage in a warehouse, etc.), the more accurately you can optimize.

It makes sense to start with ABC analysis in terms of profit. Moreover, as practice shows, the use of some more complex methods simply does not justify itself. Data for analysis (groups of goods and profit received by the company on them), as a rule, are taken for the maximum possible period of time (a year or more) in order to smooth out the seasonality of various groups of goods and errors in procurement or production planning, as a result of which goods are temporarily out of stock in stock.

To conduct an ABC analysis, you will need, generated by each specific group of goods, its specific gravity in total amount profits and rank the groups according to this indicator, as well as calculate the share in profits as a cumulative total (see Table 1. ABC analysis of commodity groups).

Table 1. ABC analysis of commodity groups

ABC analysis in relation to the company's assortment involves the allocation of groups in slightly different proportions than it suggests. classical rule Pareto. When conducting an ABC analysis of product groups, it will be quite justified to use the following criteria:

• category A - groups of goods with the largest contribution to profit (share in profit), which together give up to 50 percent of the company's total profit;
• category B - commodity groups that together give about 35 percent of the profit, and their accumulated share in the profit is from 50 to 85 percent;
• category C - groups with the lowest share in profits, bringing the remaining 15 percent (cumulative share from 85 to 100 percent).

After conducting an enlarged ABC analysis (by product groups), it is necessary to expand and increase the depth of groups that are strong in terms of profitability and reduce financially disadvantaged categories.

How to optimize the range of products from category A

Product groups allocated to category A bring the company half of all profits, and their optimization should give greatest effect. But in order to work with this group, you will have to analyze its composition.

It should be noted that a commodity group may be homogeneous in composition or contain commodity subgroups. In order to streamline all this, the concept of the width and depth of the assortment is most often used. The width of the assortment of a product group is determined by non-interchangeable subgroups of goods. The depth of the product group, respectively, is determined by interchangeable goods. Usually they are located within a subgroup. For example, goods similar in their characteristics, produced under different brands.

Group A optimization assumes that the width and depth of the group should be maximized. In practice, most often it is justified to keep no more than 6-7 positions in one subgroup in terms of depth, but there may be exceptions in relation to group A. This is the key idea of ​​optimizing the range of products that bring the greatest profit, and the algorithm for reducing unprofitable items for the company will be as follows.

Step 1. Checking the number of items in subgroups. Within the category A group, an ABC analysis is carried out, similar to the one described above. The bottom line is to determine the subgroups of category C and check the number of items on them.

By universal rule the number of items in each subgroup assigned to this category should not exceed the product of the number of commodity items in group A multiplied by the profit share. To make a choice, an ABC analysis is carried out already for specific commodity items - the next step.

Step 2 Name analysis. The logic of actions at this stage is the same - ABC analysis by profit share with the only difference that the object is the analysis of specific product names (see Table 2. Analysis of product names of the "shampoo" subgroup). Category C goods fall under the reduction. Although there are some exceptions that need to be considered, namely:

• if the company's position is not critical, you should not delete products that have been launched recently from the assortment. It is clear that the profit on them is lower, if only because such goods are sold for less time than all the others;
• Category C may include accessories and related products that stimulate sales of Category A products.

Table 2. Analysis of trade names of the subgroup "shampoo"

It would also be justified to reduce the range of items that fell into category C, getting rid of "unstable" goods. We are talking about those items whose sales fluctuate greatly from month to month. It is quite risky to bet on such a product, since in case of an unfavorable development of events it threatens with overstocking of warehouses, an increase in the volume of illiquid assets, in a word, significant losses for the company.

XYZ-analysis is used to assess the stability of sales of goods. For each product of the analyzed product group, the coefficient of variation is calculated (shows the degree of data deviation from the average value). The simplest way to calculate the coefficient of variation is as the ratio of the standard deviation to the average sales volume. In Excel, the standard deviation is easy to determine using the "STDEV" formula.

• X - variation does not exceed 10 percent. Stable sales, therefore, the main concentration of efforts and resources. Such a product does not promise large losses for the company, even if it is purchased (produced) in a larger volume than required;
• Y - variations within 11-25 percent. A less stable category than X, but nevertheless a fairly reliable product;
• Z - the spread exceeds 25 percent. It is better to remove such a product from the assortment or work with its supply (production) under the order.

Table 3. Sales stability analysis

How to optimize the range of products from category B

Step 1. ABC-analysis of names. It is quite justified to conduct an analysis immediately for commodity items for the entire group, without dividing into subgroups. We definitely leave the positions of category A. Of the remaining goods of categories B and C, we will have to complete the assortment.

Step 2 Select related products. If you focus only on profitable positions and there are no related products (services) in the assortment, then instead of increasing sales, there will be a decrease in revenue. There are two ways to identify related products:

• firstly, expertly - independently conduct such a sample;
• secondly, related products can be found through cross-analysis. It is done by analyzing checks in retail or by analyzing invoices in wholesale companies. The bottom line is to collect data on which sets of goods are most often purchased. And in the end, leave in the assortment those items from category B, which are most often purchased with goods of category A.

Step 3 Keep stable products. Based on the XYZ analysis discussed above, categories X and Y are returned to the assortment. Regardless of sales volume and profit margin. If some goods from month to month consistently (plus or minus 10–25%) bring the company even a small profit, it will most likely be inappropriate to refuse them.

Step 4 Increase the depth of category A groups.

Step 5 Determine what is important to the buyer commodity items. There are several categories of goods that should be in stock:

• "traffic generators" - a category that provides a flow of buyers. These are products with a high purchase frequency. However, they do not necessarily bring significant profits. But, coming for these goods, customers simultaneously acquire other names;
• "cash generators" - a category that provides a large volume of sales, that is, having the maximum turnover in the group's assortment. In conditions of liquidity shortage, it is important not to throw this type of goods out of the assortment in pursuit of profit;
• "Defenders" - a category of goods for which the buyer concludes general level prices in the organization. As a rule, it is built on a price basis from goods - traffic generators.

How justified it is to include image goods in category B product groups depends on the business strategy and its resources.

Step 6 Save in the range of new items. Regardless of the situation in the company, its financial position will be more stable if new products appear in the assortment from time to time. The fact that a product is profitable now does not mean that customers will lose interest in it tomorrow. In order not to miss the moment of "degeneration" of "profit generators", you need to regularly analyze the results of sales for the main products (at least once every six months or a year), track changes in the share in profits.

All other goods from category B product groups can be thrown away and disposed of.

How to optimize the range of products from category C

Category C product groups identified during the preliminary analysis of the assortment should be completely excluded from the assortment and not spent on them. financial resources companies. Of course, making exceptions for items that have appeared in the assortment recently are important related products for category A, traffic generators.

We will analyze ABC analysis in detail theoretically and practically.

ABC sales analysis. Definition

ABC analysis (EnglishABC-analysis) is a method of increasing the efficiency and effectiveness of an enterprise's sales system. Most often, the ABC analysis method is used to optimize the product range (range) and its stocks in order to increase sales. In other words, the purpose of ABC analysis is to identify the most promising products (or groups of products) that bring maximum size profit for the company.

This type of analysis is based on the pattern identified by the economist Pareto: "20% of the products provide, 80% of the company's profits." The company's goal in conducting such an analysis is to identify key commodities, and manage this 20% group, which will create control over 80% of cash receipts. Management of sales and cash payments directly affect 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 profit from sales;
• Group "B" - low-value goods, occupy 30% of the product range, and provide 15% of sales;
• Group "C" - not in demand goods, occupy 50% of the range, and provide 5% of sales profits.

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

ABC analysis of product sales. Stages

The stages of conducting an ABC analysis of the product range and sales volume of a company (enterprise) are as follows:

1. Determination of the product range of the enterprise.
2. Calculation of the profit margin for each product group.
3. Determining the effectiveness of each group.
4. Ranking of goods and their classification (ABC) by value for the enterprise.

Example of ABC product sales analysis in Excel

Let's take a look at how to practically conduct an ABC analysis of product sales in Excel for a cell phone store. To do this, we need to have 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 of the types.

Product range for ABC analysis in Excel

Next, you need to sort the goods by profitability. Go to the main menu Excel → "Data" → "Sort". The result will be a 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 use formulas in Excel.

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

Determining the share of products in the company's sales volume

At the next stage, the share of groups is calculated by the cumulative total according to the formula:

Share of goods in the nomenclature cumulative total=C6+D5

Estimation of profit share by cumulative total for a group of goods

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

After grouping the goods, the company receives an analytical report on which goods provide the main cash flows. The further goal is to increase sales of targeted products from group "A" and reduce the share of ineffective products from group "C". In our example, about ~30% of all products bring the company 80% of the profit.

Benefits of ABC analysis

1. 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.
2. Reliability of results. The results obtained are stable over time and allow the enterprise to focus its resources and capital on the development of the most promising products. Managing the nomenclature of the most valuable goods allows you to create the financial stability of the enterprise.
3. Optimization of resources and time. Using the technique allows you to free up additional resources, both financial and temporary.
4. The versatility of the analysis. The possibility of applying the ABC-analysis methodology to other areas of the enterprise.

Other ways to use ABC-analysis in the company

The range of use of this method of increasing efficiency in economic systems is extremely wide:

• Product range optimization.
• Identification of key suppliers, contractors, customers.
• Improving the efficiency of the organization of warehouse stocks.
• Optimization of the production process.
• Budgeting and management of marketing costs.

Disadvantages of ABC analysis

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

1. One-dimensionality of the method. ABC analysis is a rather simple analytical method and does not allow grouping complex multidimensional objects.
2. Grouping products based on quantities only. The method is not based only on a quantitative assessment of the rate of return for each nomenclature of goods and does not evaluate the qualitative component of each product, for example, goods of different categories.
3. The absence of a group of unprofitable goods. In addition to goods that make a profit, there are also goods that make a loss. In this method, such goods are not reflected, as a result, in practice, ABC analysis is transformed into ABCD analysis, where group “D” includes unprofitable groups of goods.
4. Influence external factors for sale. Despite the rather stable sales structure according to this model, external economic factors, such as seasonality, uneven consumption and demand, purchasing power, the influence of competitors, etc., strongly influence the assessment of future sales. The influence of these factors is not reflected in the ABC analysis model.

Summary

ABC-analysis of sales allows you to identify target groups of products that provide 80% of the company's profit. This method improves the efficiency of the enterprise, analyzes and optimizes resources, which in turn affects the financial stability and profitability of the company. The analyzed example shows the ease of use of the ABC model for analyzing 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.

Sales analysis is one of the important components of conducting modern business. Its significance is due to the possibility of obtaining accurate and reliable information regarding the activities of the organization for a certain period of time. For company managers, sales analysis allows you to quickly get information about the results of work and the effectiveness of making a decision.

What is a sales report

Possible options There are a lot of ways to generate sales reports. At the same time, they can be compiled at different intervals: monthly, quarterly and annually. For the most accurate accounting and prompt acceptance management decisions Sales analysis needs to be done frequently.

Cloud CRM system for sales accounting with the function of analyzing the customer base.
Manage customer relationships, work productively with leads!

Based on the data obtained, it is possible to develop a new improved strategy for the company, depending on a number of factors, such as the situation on the sales market, the popularity of a particular product, competition, etc.

Upon receipt of objective data, it is possible to compare the levels of sales of individual groups of goods in order to identify a weak link, unproductive use of resources and other indicators that, one way or another, affect the work of the company as a whole.

Based on the data obtained, further forecasting of work in the areas of activity or divisions of the company can be carried out. It is important to note that the analysis gives the best results when the process is implemented using special automated systems.

ABC and XYZ sales analyzes

At present, a fairly high level of popularity and practical use have ABC- and XYZ-analysis. Their implementation is one of the main statistical measures at enterprises. various shapes property. Each of these types of analysis has its own advantages and disadvantages, which must be taken into account when evaluating economic activity organizations.

So, for example, the most profitable goods or services can be classified in group A if their profitability compared to other categories is quite high, in the range of 70-80%. This is the most promising and profitable product line for the enterprise, which it must develop and improve.

The second category B will include products and services of intermediate importance, which should be regularly upgraded and strive to move them to a higher category. It is group B that needs to be seriously and scrupulously dealt with in order to bring it an order of magnitude higher than those indicators that are currently available.

Along with the profitability of goods, other indicators also matter. Thus, knowledge about the structure of consumption of certain types of products or services offered is also extremely important for evaluating sales results. XYZ-analysis allows you to structure and distribute the company's resources according to the degree of their consumption. At the same time, materials from the high level consumption (most often bought or used) will fall into category X in group Y - the consumption of which is seasonal, and in Z - those that are used rarely or irregularly.

This type of analysis is an excellent tool for making purchasing decisions. Like ABC-, XYZ-analysis allows, as a result, to compile a holistic view, on the basis of which it becomes possible to predict the activities of the organization.

Analysis of sales by distribution channels

In turn, the analysis of sales by distribution channels allows you to get accurate information about the least profitable methods of selling products on the market. As a rule, such an analysis is carried out regularly, taking into account systematic changes in the state of the market. It is created on the basis of an analysis of orders and sales from various sources, which allows, when comparing delivery indicators and the percentage of sales of products, to determine the significance of a particular sales market.

Analysis of the work of sales staff

Actual and simply necessary in practice can be an analysis of the work of sales staff, which should also be carried out regularly, with appropriate decisions and conclusions for each specialist working in the company.

The purpose of such an analysis is to identify unskilled personnel among all employees, as well as the need for special training, advanced training of company employees in order to achieve higher performance.

Analysis of the client base in the online program Class365

Analysis of the client base is carried out in order to summarize sales for each customer. At the same time, the main advantage of such an analysis can be considered that, in parallel with the classification of customers into relevant categories (from the most valuable to the unprofitable), it is possible to identify groups of goods preferred by these buyers.

In this way, this analysis helps to define your target audience and its basic needs, which can fundamentally affect the sales strategy as a whole.

Any analysis carried out by an organization can be most effective if it is compiled using various CRM systems, the possibilities of which are very impressive.

In the Klass365 online program, you can receive reports on any area of ​​work in just 1 click. To work with the client base, the program has all the necessary functions:

• All information in the counterparty card: from the first call to the invoice
• Sending SMS and email messages
• Analysis of customer activity
• Sending commercial offers, accounts directly from the system
• Editable counterparty status (potential, regular client, etc.)

Thanks to the online program Class365, it is possible to analyze sales by different groups goods, comparing them with each other, or simply form a completely different types reports. This approach will make the data analysis procedure quick and hassle-free for any trade or service enterprise.

Free yourself and your employees from routine work! Get started with Class365 right now!