Sales Forecasting: Accurate Calculation or Divination? Sales forecasting algorithm in MS Excel Forecasting sales volume adjusted for seasonality.

A sales forecast is one of the important stages of doing business: an entrepreneur must have an idea of ​​how much he will sell, for what amount, with what profitability. Moreover, this should not be just an assumption that “it would be nice”: the sales forecast should be prepared carefully, have a weighty base. Sales forecasting methods vary, ranging from elementary ones to those that are compiled using complex mathematical tools.

Download materials for calculating sales volumes:

How is a sales forecast different from a plan?

“Plan” and “Sales Forecast” are far from the same thing, they are terms denoting different controls.

The plan is a directive concept, it is a task that is set for the manager, a task that he must perform.

Forecast - an assumption that in some future the store will sell a certain amount of goods. A forecast is not a task that needs to be completed, it is an assumption about how a business can develop.

The forecast always has a certain basis, it is never made from assumptions related, for example, to the desire of an entrepreneur to receive one or another benefit in a certain period. Forecasting is always based on a certain base.

Usually the basis for forecasting is data on previous volumes. The most elementary case of forecasting looks like this:

If an entrepreneur sold goods last month for 1.5 million rubles, then other conditions remain unchanged (the store will be in the same place, the traffic will be the same, a serious competitor will not appear in the area, the income of the population will not drop sharply, etc. ) next month, sales will amount to at least 1.5 million rubles.

This is already a forecast, which has a basis and an elementary calculation. Based on it, the entrepreneur will set tasks for his managers for the planned month: to sell products in a total amount of 1.5 million rubles.

This is another difference between a plan and a forecast: a plan is built on the basis of a forecast - first, business parameters (sales volumes, profitability) are forecasted for a certain period of time (month, year), after which the predicted indicators are indicated in plans and distributed to managers.

By time they are divided into:

1. Short-term - for periods within 1 year: for a month, quarter, half a year and a year.
2. Medium-term - this is usually for a period of 1 to 3 years.
3. Long-term - more than 3-5 years.

You can predict sales using the Business.Ru inventory program. With its help, you can analyze revenue, cost and margin, calculate the profitability of products and make purchases based on sales dynamics.

In practice, three main methods are used:

1. Method of expert assessments.
2. Time series analysis.
3. casual method.

Method of expert assessments

What was considered as an example above is also an elementary example of the first method. The method of expert assessments lies in the fact that the definition of certain business parameters, including sales volumes, is based on the opinion of experts and specialists in a particular field of activity.

For example, an entrepreneur selling alcoholic beverages, beer can predict how successfully his business will develop in the short term, based on the findings of experts in this field. If experts say that next year the market will “sink” by 12% (this is an example, of course), then the entrepreneur can reasonably calculate a possible drop in his sales by about 12%.

Conversely, if experts say that, for example, in the 4th quarter, the market for meat and sausage products will grow by 16%, then the owner of a butcher shop can predict an increase in his own sales by approximately the same relative value. Accordingly, managers will be given more ambitious tasks with increased individual targets.

To apply the method of expert assessments, representatives of a larger retailer can not only use the opinions of experts and analysts that are publicly available and free of charge, for example, on the Internet. Larger firms can order individual marketing research: then experts and analysts will conduct a more thorough analysis and make a more accurate forecast for sales specifically for this store (chain).

Time series analysis

These are forecasting methods where forecasting is based on previous sales data. Usually for these purposes it is better to take volumes for the past year by months. If the company has just started its activity, for example, the store opened only 1-2 months ago, then in this case, forecasting must be built based on other parameters, for example, general market trends, etc. And when the business is a year old or more, apply other calculation methods.

For the analysis of time series, in order to calculate the sales forecast, it is necessary to first write out the sales indicators in the table for half a month. To do this, it is better to use the well-known Excel office application.

2015			2016 FORECAST
Month	Sales, rub.	Height
September

The time series is the sales data (column 2) in each month (column 1) of the past year. In our example, an analysis of the volumes in 2015 was carried out, on the basis of which the sales of products were predicted for 2016.

In the table, time series analysis was carried out in order to identify a trend. We see that in January goods were sold in the store for 150,212 rubles, and in February already for 160,547 rubles. The growth was 7%.

Column 3 calculates the growth in each month compared to the previous one, for example, in August, compared to July, sales growth was only 1%, and in December, compared to November, already 6%. At the same time, the average monthly increase in 2015 was 4% (last line of column 3).

It turns out that if in January 2015 we sold goods for 150,212 rubles, then in January of the next year we will sell for an amount of 156,220 rubles, that is, 4% more.

The annual volume of sales in the store will also grow by 4%: from 2.3 million rubles to 2.4 million rubles.

In Excel, all these specified calculations are done elementarily: the formulas are entered manually once, copied into the following cells. Special knowledge is not required for this.

Seasonal analysis of time series

Data on past sales must also be analyzed in order to determine how seasonal trading is and their volumes differ by period. Let's consider another example.

2015			2016 FORECAST
Month	Sales, rub.	Height
September

After analyzing the data for the past 2015, we see that in the summer period from April to July inclusive, seasonality was observed, sales volumes fell - a decrease in column 3.

Accordingly, by applying the seasonally adjusted trend values, we made a correct sales forecast for the next year.

You will be able to predict sales without resorting to complex formulas, calculate the demand corridor by determining the upper and lower limits of future sales, use the universal sales forecast method for any period.

Instead of cumbersome formulas for product demand forecast we use one chart in Excel, which we build based on the company's sales data. The algorithm was deduced independently, based on the advice of familiar businessmen and materials from the Internet. Using the graph, we predict sales for a month, several months or a year. To replicate the experience, you need a version of Excel 2003-2016. In addition, at the end of the article you will find an alternative method that will allow you to build a forecast in a few minutes. However, it is only suitable for the 2016 version of Excel.

Sha d 1. To predict the demand for goods, with collecting company sales data

To begin the analysis, you will need the company's sales data for the entire period of its existence. The more information, the more accurate the forecast. For example, we have sales data from January 2013 to August 2015. We put them in the table (Figure 1).

Best Article of the Month

We have prepared an article that:

✩show how tracking programs help protect the company from theft;

✩ tell you what managers actually do during working hours;

✩explain how to organize surveillance of employees so as not to break the law.

With the help of the proposed tools, you will be able to control managers without reducing motivation.

Step 2. We make a forecast of demand for products for a given period

To predict sales, for example, for a month or for the next year, we use the FORECAST function in Excel. The function is based on linear regression and is designed to predict sales, product consumption, etc.

In cell C34 we write the function:

FORECAST(x; known_y's; known_x's),

x is the date for which the value must be predicted (cell A34);

Step 3. We calculate the seasonality factor for the demand forecast

To take into account seasonal declines and sales growth, using standard functions, we calculate the seasonality factor. To do this, we divide the sums of sales for the first and second years by the total amount of sales for two years and multiply by 12. Using the F4 key, we set absolute references so that the calculation goes exclusively from the range we need (Figure 1).

=(($B$2:$B$13+$B$14:$B$25)/SUM($B$2:$B$25))*12

Next, copy the formula and paste it into cells F2:F13 as an array formula. We complete the input with the key combination: Ctrl + Shift + Enter. If this is not done, the function will return the #VALUE! As a result, for January we get a coefficient of 0.974834224106574, for February - 0.989928632237843, etc. For clarity, you can assign a percentage format to the cells. With the right mouse button, select "Format Cells", then the "Number" tab, and then the "Percentage, two decimal places" tab.

• Seasonal downturns in business: 3 ways to pull sales

Step 4. We adjust the demand forecast for products, taking into account seasonality

Add the calculated coefficients to the existing FORECAST function (cells C34:C45):

To adjust sales, taking into account the coefficient, we use the "INDEX" function (Figure 2).

The first argument in the function is a reference to 12 cells with seasonality coefficients ($F$2:$F$13), the second - the month number to return the coefficient for the desired month (for this we use the "month" function, which returns only the month number from the specified date ). For September 2015, the index formula looks like this:

INDEX($F$3:$F$14;MONTH(A35))

To correct the forecast, you need to multiply the value of "INDEX" by the value of "PREDICTION", which was calculated in step 2. Here's what we get:

FORECAST(A34, $B$2:$B$33, $A$2:$A$33)*INDEX ((97.48%:98.99%:90.38%:94.66%:100.86%:99 .02%:100.66%:110.39%:100.47%:104.82%:105.13%:97.14%);9)

We extend the function to further periods and obtain a seasonally adjusted forecast in cells C34:C45 (Figure 1).

Step 5. Calculate the deviation and build two scenarios

Actual sales rarely match forecasts exactly. Therefore, companies additionally construct admissible upper and lower bounds - sales forecasts for optimistic and pessimistic scenarios. This helps to track the trend and understand if the actual sales figures are outperforming the predicted values. With a large deviation, you can urgently take the necessary measures.

We build the upper and lower boundaries of the demand corridor according to the formula (cell G2 in Figure 1):

CONFIDENCE(0.05 (ALPHA), STDEV(C34:C45), COUNT(C34:C45)),

"CONFIDENCE" returns a confidence interval using the normal distribution. The function takes into account fluctuations in company sales, including seasonal ones.

"ALFA" - level of significance for calculating the confidence level. An indicator of 0.05 means that we will get a forecast with an accuracy of 95%.

"STDEV" - the standard deviation of the general population. Shows how forecasted sales differ from actual sales.

"COUNT" counts the number of months for which we forecast sales.

To get optimistic and pessimistic scenarios, we write formulas in cells D34 and D35 (Figure 1).

Optimistic: =$C34+$G$2 (add the sum of the calculated confidence interval to the forecast sum)

Pessimistic: =$C34–$G$2 (subtract the sum of the confidence interval from the forecast sum)

In order to build a graph based on the data obtained, we copy the values ​​​​from cell B33 into cells C33, D33 and E33. Next, select all the data (A1:E45), go to the "Insert" tab, find the "Charts" tab and then the "Graph" tab. As a result, we get a graph with a demand corridor (Figure 3).

Conclusion. Having built a corridor of demand, we are closely monitoring sales in the new year. In 99% of cases, they develop within the corridor. If not, we analyze sales again and build a new chart.

• How Regular Demand Research Increases Sales Dynamics by 648%

Expert opinion

The method is effective for predicting sales of a small number of SKUs

Maxim Lyulin,

CEO of Aktion-press

I would advise using the method for forecasting in relation to one article - then it will be as accurate as possible. In general, I liked the method for its simplicity and the fact that it allows you to avoid mistakes. It can also be used to forecast sales of a group of products that are similar in characteristics and close in price.

The disadvantages of the method include the difficulty of taking into account changes in prices, the impact of auction activities. In addition, when evaluating sales in rubles, you cannot objectively assess the company's sales share in an industry niche, so you risk losing market share. Your competitors can take advantage of this and offer a product at a lower price.

Expert opinion

The method is ideal for analyzing sales by fixed indicators

Kirill Chikhachev,

CEO of MCFER-press

Before reading the article, I was familiar with the method in theory. Now, having tried it in practice, I can say that I liked it. The method is ideal for analyzing sales by fixed indicators: the number of products, sales capacity, etc. It should also be used for a small number of products: the growth and decline in demand for each of them depends on different reasons. The forecast is extremely clear, logical and accurate. However, for even greater accuracy, I would consider the following points.

It is easier to calculate the maximum and minimum sales values ​​based on two points at the beginning and end of the periods, rather than looking for points through which the straight line should pass.

When forecasting sales for a month, it is more logical to divide the difference between the upper and lower values ​​for the optimistic and pessimistic scenarios not by 12, but by the number of months inside the segment. This way you can more accurately calculate your monthly sales growth.

A necessary element of strategic planning is the establishment of a potential sales indicator. After its definition, a detailed implementation forecast is worked out. It is important to understand the difference between forecasting and planning.

"Plan" and "Sales Forecast" are parts of the same process.

Best Article of the Month

We have prepared an article that:

✩show how tracking programs help protect the company from theft;

✩ tell you what managers actually do during working hours;

✩explain how to organize surveillance of employees so as not to break the law.

With the help of the proposed tools, you will be able to control managers without reducing motivation.

Plan - an indicator that is communicated to the contractor and is subject to full implementation.

The forecast is the estimated level of sales that the owner expects to receive from his store in a certain time interval.

Forecasting is always based on hypotheses and the desired vision of business development, although it is based on specific facts, estimates and results. This concept is not an unreasonable desire for certain benefits.

The scenario is always built on the foundation of the analytical conclusions of business development, previously obtained indicators and market dynamics.

The simplest example of a sales forecast would be as follows: the store sold goods in the last period for a total of 1 million rubles. If we assume that market conditions remain the same, the economic situation in the country and region does not change, and a strong competitor does not appear, then the forecasted sales for the same next period of time will be equal to the indicator of the last period.

Such a sales scenario for a month is justified by specific data, so it becomes the basis of the product sales plan for performers for the future period. We get the current task of the store - the sale of goods in the amount of at least 1 million rubles.

The difference between planning and forecasting is that the former is based on the latter. First, a scenario is compiled for a specific time interval (a sales forecast for a year) based on an analysis of the required indicators, then the data obtained is entered into plans and transferred to management. Goals are made for:

1. Short term (month, quarter, year).
2. Medium-term planning (one to three years).
3. Long-term planning (three to five years or more).

Sales forecast significantly affects the choice of development strategy. For example, forecasting has shown that attracting new customers within the developed boundaries of the area will be more profitable for business than entering a new market. Under such conditions, the entrepreneur will postpone projects to launch products on other trading floors and focus on the growth of sales volumes within the existing territory.

• The sales forecast at its root should have break-even analytics. In the case when the forecast data shows a negative result or activity equal to the break-even point, then the analyzed strategy will not bring benefits to the business.
• In the process of preparing the plan and sales scenario, it is necessary to take into account low indices at the beginning of work, as well as the level of seasonality.
• It should be remembered that the sales forecast within a certain strategy is not a budget, but only serves as the basis for setting goals.

A sales forecast is a tool that allows you to make decisions about selling a product and investing in its promotion. Scenario development reveals potential profitability under certain market conditions and time frames.

To obtain the desired results in business and make extremely accurate forecasts, it is necessary to correctly apply the accumulated experience, possess intuition, and knowledge in the field of trade relations.

The result of the sales scenario will be the formation of a document that reflects information about products and their quantities that are profitable for sale in a certain territory in a specific time interval.

The units of measurement used in the forecast are currency, liters, pieces, etc.

Purpose of Sales Forecasting– determination of trends for a given perspective and formation of a basis for a future implementation plan. Scenario development activities are meant to be followed by budget development, marketing plan development and target achievement.

The forecast of sales volume is directly dependent on the marketing work of the organization, which is planned for use in a particular period. Stimulation of the sales process and active promotional activities determine the volume of product sales and help to create a scenario for the future.

The sales forecast reveals the estimated demand for a particular type of product. Accordingly, when developing this scenario, it is necessary to take into account the work of the closest circle of competitors (development of a chain of stores), advertising activities, and activity in the field of sales growth.

Forecast features:

• A sales forecast is a serious tool in the hands of a manager to obtain the necessary information in order to effectively manage his company. It does not help in the work of motivating and improving the performance of staff. The primary task of the script is to obtain data for further calculations of financial flows in the organization.
• The sales forecast for the year most accurately reflects the digital indicator of the future profitability of the business, which is necessary for planning the expenditure component. Another significant point is the fact that scripting helps to control the correctness of the formation of procurement programs, taking into account the idea of ​​the company's needs for storage space, equipment, and personnel.
• A sales forecast allows top managers of an organization to see specific criteria for understanding target customers, which customers need special relationships or control, management attention, which employee knowledge is needed.

• Time Management, or How to Boycott Plan Eaters

On what principles should the compilation of sales volume be based?

The head of the company is not personally involved in the preparation of the sales forecast. However, he needs to know the main aspects of this work due to the special importance of this process for the activities of the organization.

1. The head of the sales department is obliged to have information on all transactions planned for conclusion, in specific numbers. It is unacceptable to provide the General Director with information about the proposed sale without specifying the client profile and the amount of turnover. Information about the amount of sales should be as specific as possible.
2. It is important to plan for the period in which implementation is expected.
3. Sales managers specify the dates of receipt of revenue. All information is collected by the commercial director, who provides them for consideration by the head of the company. The task of managers is to determine the probability of making a deal.
4. Each probability is assigned a specific coefficient. To be included in the sales forecast, the transaction price is multiplied by the probability index. The commercial department determines the coefficients, after which they are approved by the head of the company. The derived indexes serve as a criterion for monitoring the reports generated by the sales department.
5. It is very convenient to develop a sales forecast in Microsoft Excel. The scenario includes the amounts of turnovers on planned transactions, adjusted by the probability coefficient. The Excel spreadsheet creates pages for each month and separate sections for specific employees. Formulas help to automatically determine the probability of payments and make a final calculation.
6. Drawing up a sales forecast is the direct competence of the commercial director. He is responsible for transferring the finished script to the head of the company, who, in turn, must clearly define the task for the sales staff. The function of managers is to timely enter data into an Excel document. In addition, the staff at the level of automatism must record all intermediate indicators when working with clients in order to subsequently take this information into account in the forecast.
7. The head of the organization controls the activities of the sales department using the information of the generated scenario. To do this, it is not enough to compile a table once; changes must be made regularly. If the manager finds that there were no adjustments on a certain day, this may indicate that the commercial department is not fulfilling its functions.

The main methods of forecasting sales in the enterprise

There are several methods of sales forecasting, ranging from the most superficial, based on management assumptions or historical reporting data, to the deepest, based on strategic models.

Simple (empirical) methods are formed taking into account the assumptions of top managers, the general opinion of the staff and experimental marketing.

The leaders of the organization are usually involved in writing the scenario, but it rarely happens that forecasting is based primarily on the assumptions of the leaders. In most cases, trading companies use analytical data from reports for recent periods, as well as indicators for several past years. In addition, customer surveys are taken into account. After the systematization of the information provided by the personnel, the results obtained in certain areas, or sales volumes for individual types of products, are subject to analysis. Good sellers always know the profile of their client and are ready to give an assessment for the future.

• Valuation of enterprise assets: a reminder for the owner of the company

Trial marketing is optimal for forecasting sales of new products.

№1. Target Sales Forecasting Methods

The sales forecast is calculated using this group of methods in the following order:

• The quantity of products that the organization would like to sell in the planning period is determined.
• An indicator is calculated that will help achieve the target result.

The management of the sales department and the leaders of the organization determine the volume of sales, after which they form detailed plans for the implementation of the main project.

Target forecasting is an effective tool for a company to get out of a difficult period due to low sales with increased competition, while implying work with the same products.

Stage 1. Determine the optimal sales volume. For example, in the current year, sales should be 150 thousand units of goods.

When the product being sold or its equivalent has proven itself in the market and sells consistently, when forming a target forecast, it is necessary to take into account such factors like:

1. Quantitative indicators of sales for past periods.
2. Seasonal dips and increases in market demand.
3. The size of the budget allocated for promotional activities relative to the budget of competitors.
4. Filling the market with equivalent products.

Taking into account these factors, it is possible to determine the sales volume of goods for the next period. In this case, the predicted indicators will correspond to the actual conditions and potential of the organization.

Stage 2. Determine the actions that will help realize the amount of production that is beneficial for the company.

Perform an analysis of all costs required for the purchase and sale of:

• fare;
• for imported products - customs clearance costs;
• when using borrowed funds for the purchase - the amount of interest on loans;
• the cost of selling the product;
• calculation of the amount of profit per unit of goods.

• what advertising tools will be most effective;
• the cost of creating and launching marketing campaigns;
• what kind of advertising will interest the target buyer.

After collecting and systematizing all the data, a sales forecast calculation and a break-even chart are drawn up. The break-even point and schedule are fundamental indicators when developing a product sales scenario.

In the target forecasting process, break-even analytics reveal how soon an organization recovers costs after selling a target volume.

№2. Step by Step Sales Forecasting Methods

The reverse technique is the step-by-step sales forecast. First of all, costs, selling price and profit are subject to calculation. The obtained information and market analytics allow you to make a sales forecast by period.

Stage 1. Step by step scenario development begins with identifying:

• the costs that the company will incur in its activities when selling products;
• the profit that the organization expects to receive;
• the value of the product determined by the market.

For effective forecasting, it is necessary to answer the following questions: questions:

• What price to set for the sale of the planned volume of production?
• What costs are acceptable in order to realize the target turnover with optimal profitability?
• What should be the difference between the total cost of goods sold and the costs incurred? Can you get the desired margin? Will the profit margin be satisfactory?

Stage 2. The market potential is analyzed, the willingness of target consumers to buy goods at a given price.

• Production planning is the foundation for the effective operation of an enterprise

Stage 3. Extrapolation.

For stepwise forecasting work, reported revenue data is of marginal value. With the help of these indicators and information about the volumes of goods sold in past periods, it is possible to identify the exact direction, that is, to determine how seasonal market fluctuations affect turnover, at what time sales increase or decrease is observed. The extrapolation method is based precisely on the analysis of market trends.

Extrapolation- this is the preparation of a forecast for subsequent periods, analytics of costs over the past time, taking into account expected trends. This method is especially useful in areas where change is slow.

Reporting data, systematized by sellers, give a clear vision of sales trends. A detailed study of past sales at different time intervals will help to understand and translate this course for the next periods, thus calculating the sales volumes for the future. This forecast can be considered justified if the market situation does not change radically.

Making an extrapolation will be effective if you get answers from sellers to several questions:

• What deals do you plan to conclude next month?
• What dynamics among competitors do you expect in the next quarter?

Making a sales forecast by extrapolation requires taking into account economic indicators. Usually this percentage and numerical indicators:

1. Changes in bank rates.
2. Exchange rate fluctuations.
3. Proposed changes in taxation.

The breakdown into categories is made by dividing into product groups according to the regional principle (location of sales representatives), by markets. If the price indicator is not applicable in a particular situation, for example, the seller sells several goods at different prices, then this indicator is not used. At the same time, volumes and costs must be determined.

Budget lines "actual" and "deviations" are not needed when generating a sales forecast, but they are of high importance for control. Attention to these indicators helps to monitor the work on the implementation of the forecast.

After collecting all the necessary information, you need to start calculations and build a break-even chart. The break-even chart and the break-even point are critical indicators that are key benchmarks in a sales forecast.

By developing a step-by-step scenario, using a break-even analysis, you can determine whether the organization is able to sell the amount of product that will cover costs and bring tangible profits.

It is possible that the predicted sales volume will reveal a low rate of return. In this case, it is necessary to study the scenario in detail and choose one of the options:

1. Increase the retail price of the product within the possible limits.
2. Reducing the cost component in acceptable terms.
3. One-time price increase and cost reduction.
4. Margin reduction (this is done last).

Expert opinion

"Where we want to go" and "Where we go" methods

Alexander Dorokhin,

It is preferable for an organization to apply two methods of sales forecasting.

The first of the methods can be defined as: "where we want to go."

The second method is “where are we coming from”. Everyone has an underlying assumption.

The head of the company determines which method to give preference to. Following the first path, the organization sets itself large-scale goals for the long term. Such goals always exceed staff forecasts. To accomplish these tasks will require high concentration, productivity and dedication.

After setting a large-scale goal, the company is working out options for achieving the designated tasks and informing the staff about it. With this approach, the enterprise creates a consistent movement towards the main indicator. At the same time, the achievement of an extremely feasible forecast has a rather low percentage of probability, because the goal exceeds the available possibilities and involves the application of super efforts.

In this situation, the head of the company has two main tasks:

• Formulate and set tasks for the employee, define job responsibilities, provide the necessary authority to achieve the predicted result.
• Maintain control over the fulfillment of the tasks assigned to the employee.

The second method of forecasting is characterized by the fact that the sales staff is guided not by the set goals, but by their own indicators in the past periods. “Last month the amount of sales amounted to 130 thousand rubles, therefore, this result can be repeated this month. There is a possibility that the sale will amount to 135 thousand rubles.” If the turnover in the current month falls, then the contractor will prepare a sales forecast for the month, focusing on the last low figures.

Achieving the set results following this method is quite simple, but the efficiency for the enterprise is extremely low. If the staff does not make serious efforts and does not receive appropriate results, the company will stop its growth and development.

• Conducting planning meetings: how to effectively convey information to the team?

How to Calculate Sales Forecast in Excel with Growth and Seasonality

Divide the sales forecast calculation by 3 parts:

1. Calculation of trend indicators.
2. Identification of seasonality data.
3. Forecasting sales volumes.

Calculate the sales forecast by periods for the next two years and three months based on revenue for 5 years.

1. To calculate trend values:

Let's determine the indicators of the linear trend equation y=bx+a using the Excel function =Linear().

To do this, in Excel cells, enter the function = Linear (sales for 5 years; period numbers; 1; 0).

Select 2 cells, in the left - the formula = Linear (), press the key combination in the following sequence (F2 + Ctrl + Shift + Enter). Excel will display for us the values ​​of the coefficients a and b.

Calculate trend values

To do this, we substitute the calculated trend coefficients b and a into the equation y = bx + a, x is the number of the period in the time series. We get y - the value of the linear trend for each period.

2. To calculate seasonality factors:

• We display the deviations of the actual data from the trend indicators. To get the result, we divide the real indicators by the trend values.
• For all months, we derive the average deviations for the last 5 years.
• We determine the overall seasonality index - the average value of the coefficients calculated in paragraph 3.
• We derive seasonality coefficients. Each coefficient from point 3 is divided by the coefficient from point 4.

3. We calculate the sales forecast formula taking into account growth and seasonality:

• We determine the period for which it is necessary to make a forecast. We extend the numbers of the periods of the time series by 2 years and 3 months.
• We calculate the trend values ​​for future periods. In the equation y = bx + a we substitute the obtained trend coefficients b and a, x is the number of the period in the time series. We determine y - the value of the linear trend for each future period.
• We calculate the forecast. For this, the values ​​of the linear trend are multiplied by the seasonality coefficients.

The forecast of growth in sales, taking into account seasonality, is ready.

You can create your own example of a sales scenario by changing the coefficients a and b in a linear trend y = bx + a.

Additional Sales Forecast Factors

In order for the calculation of the sales forecast to be extremely accurate, it is not enough to take into account growth and seasonality, additional conditions that affect sales volume are also important, such as:

1. Advertising activities.
2. Sales promotion work.
3. Introduction of new products.
4. A separate category of buyers with one-time purchases in large volumes.
5. Identification of new sales directions.

How to determine the optimal sales forecast

The sales forecast is compiled on the basis of calculations that make it possible to see the actual state of affairs under promising contracts and projects. For this reason, it is incorrect to call the technological scenario "optimal". Such forecasting is always an objective reflection of the real reality, if all the calculations of the company's managers are made correctly.

Sales Forecast Example

Expert opinion

Accurate sales are 100% low

Alexander Dorokhin,

Head of Distribution Department, Heinz-Petrosoyuz, Moscow

In the work, there are cases when the extremely accurate forecast of product sales turns out to be noticeably underestimated. What is the reason?

If the head of the enterprise challenges the sales manager to provide reliable information about possible sales, the employee always determines the volume that he will complete without much effort. After that, the head of the enterprise makes an analysis of the forecast received from the employee, comparing the indicators with the plan. The data do not match: the plan is higher than the forecast. At the next planning meeting with the manager, the manager reports that the forecast does not suit him and demands that a new, “correct” scenario be prepared, without underestimated sales figures.

If the CEO is again not satisfied with the corrected forecast, he brings to the employee the data that he himself wants to see in the scenario, and requires them to be completed in full. However, the forecast of sales volume, for the execution of which it is necessary to activate all the resources of the sales department as much as possible, cannot be called extremely accurate. In fact, this is a plan, since it is lowered from above and has as its main task the achievement of indicators set for the development of the company. How to convince managers to make a sales forecast that meets the expectations of the manager?

Sales Forecast Management: Key Steps

In order to make an effective sales forecast, it is necessary, together with the commercial director, to establish clear rules:

• Frequency of obtaining a commercial forecast (once a week, once a month or quarter).
• Specific information that should be reflected in the report (revenue, goods sold or sent to customers, etc.).
• In what form to provide a report (graphs, tables, etc.).

It is also necessary to determine the procedure for applying the commercial scenario in the company. It is important to decide whether the motivation system will be associated with a sales forecast that correctly determines the results, make the results of the forecast publicly available to staff or only to managers. Competence for solving these tasks can be transferred to the commercial director. It is worth instructing him to identify the stages of the work of the contractor with customers.

Sales stages:

1. Live meeting, direct interaction with a potential consumer. The manager demonstrates the products.
2. Identification of need. The manager interviews the client in order to determine the desires and motivation for the purchase.
3. Presenting an offer. It is formed after identifying the needs of the buyer.
4. Preparation of the contract, coordination with the client of all its conditions and terms of signing.
5. The conclusion of the contract. The manager signs the agreed contract, then the manager hands it over to the client for signing. The document is drawn up by officials on the part of the buyer, after which it is transferred for execution.
6. Transaction payment. The client transfers the amount of the transaction to the current account or pays in cash.
7. Final agreement of the transaction. The made layout is coordinated with the buyer.
8. The approved document is certified by signatures and a seal.

• Prepare a sales report

It is necessary to provide a structure for the sales forecast report that is convenient for work. The main thing here is to form the implementation scenario “from the bottom up”:

• Managers who work directly with consumers are required to report to the senior manager on the stage at which the process of working with each client is.
• The senior manager, based on the information in the report, identifies why the buyer is not moving forward in the sales business, perhaps he needs help.
• The head of the sales department systematizes all the forecasts made by the salespeople and presents them to the commercial director in the form of a single scenario.
• The commercial director can use this document as the basis for reporting to the CEO regarding the sales forecast for the entire company.

• Assign responsibility for reporting

Important: the commercial director is the person responsible for the accuracy of the forecast. His task is to work with each of the managers to obtain reliable data specified in the sales scenario.

• Reward people for accurate predictions

The commercial director must develop a motivation system for managers of the product sales department. The manager, in turn, should decide whether to link the reliability of the sales forecast with the remuneration of the commercial director and (or) with bonus payments to sales managers.

Each of the methods can be effective. At the same time, making any change in the system of remuneration and motivation of labor, one must act carefully. Employees need to understand the reasons and conditions for changes in payroll. In this direction, an individual approach will be useful. However, the bonus system often becomes expensive to a successful sales forecast.

• Control the process

The result will be brought by weekly or monthly meetings of the commercial director with managers, where current achievements will be highlighted. The frequency of meetings is determined by the product sales cycle. The frequency of making a sales forecast should correspond to it. If a company conducts large expensive transactions, the execution of which takes months, the frequency of the report should be adjusted to the cycles of work on these contracts. The reverse situation develops if the business deals with the sale of advertising. The model for forecasting sales of products and the frequency of its compilation in this area is directly opposite.

• Make sure your sales forecast is met to the maximum

This is a direct function of the head of the sales department.

1. The manager exercises continuous control over how employees perform work to achieve predictable indicators. There is a "no more than one extra try" rule here. If the payment did not pass on the designated day, no one cares about the client's problems.
2. The manager independently determines and calls the head of the sales department the deadline for which he will bring this transaction to a result. This period must be short. If the result is not achieved on the designated day, the boss takes on the issue of completing the transaction. And the leader receives bonuses for the implementation.

• Channels for attracting new customers to the company's website

Why managers underestimate sales forecast and how to deal with it

• Firstly, the contractor often underestimates the amount of the proposed transaction.

In fact, the problem is in the psychological "ceiling". To eliminate this barrier, you need to work with a mentor, and training of good specialists in this field is also very effective. The head of the department is able to detect the problem during the analysis of the final sales forecast. A characteristic feature is that all employees work with different transactions, from the smallest to the largest, while one or two contractors have only small projects.

• Secondly, managers sometimes underestimate the percentage of the probability of a positive closing of the transaction.

The performer will not be able to put the probability below “unlikely”. When more managers have different probabilities for deals, while there are employees who predict only “unlikely”, the manager immediately sees unwanted statistics in the consolidated sales forecast. Workers who are afraid or unwilling to set high targets in a scenario need professional help to eliminate uncertainty or gain missing knowledge and experience. It is extremely undesirable when the procedure for negotiating the contract is underway, but such transactions do not appear in the sales forecast.

The most unpleasant option is when the manager engages in empty talk instead of a negotiation process aimed at a specific result. Such a performer probably does not know what to offer the buyer and what the cost of the transaction will be. The worst thing that can be - the client is taken to the side.

This situation becomes apparent if a manager negotiates in a foreign territory, while his sales forecast does not change. This state of affairs requires the immediate intervention of the manager, as well as decisive action to prevent such cases: from a joint negotiation process to the dismissal of an employee.

Expert opinion

What to do if managers underestimate sales forecasts

Nikolai Kuvshinov,

General Director of Kompraktiks LLC, Moscow

Contractors set a minimum probability in their sales forecasts primarily for the following reasons:

• Insurance in case of a negative outcome in the upcoming period.
• The desire to increase the bonus reward for overfulfillment of planned targets.

The Director General needs to establish the reason for the underestimation in each individual case. The manager can solve this task independently or delegate it to the commercial director. This allows you to identify serious risks at the initial stage, making the necessary adjustments to the plans and the overall perspective of the organization.

When the indicators of one period reflect the excess of the forecast, and the other - underfulfillment, moreover, this situation is of a systemic nature, the following weaknesses are revealed:

• Lack of a clear sales strategy.
• Lack of dialogue with potential buyers for the purpose of cooperation.
• The passive market for the goods sold has been exhausted.

The sales forecasting process is one of the important information tools for planning the economic activities of a manufacturing company. Various forecasting models have been developed and are already being used by product managers, based on historical data from previous periods and analysis of the existing environment. However, for the effective use of existing models in a company, it is necessary to organize an automated collection of information and establish criteria for assessing the accuracy of the forecast. In addition, when making a sales forecast for a product, managers should definitely consider the following factors:

• consumer behavior;
• previous and planned product promotion strategies;
• actions of competitors-manufacturers;
• the external environment of the enterprise, its changes.

All existing sales forecasting methods can be conditionally divided into four main groups: based on judgment; consumer-oriented; sales extrapolation; modeling.

1. Methods based on judgment. This group includes methods such as studying the intentions of counterparties, role-playing games, expert assessments, the Delphi method, brainstorming, and a consolidated sales forecast.

Studying the intentions of counterparties. The essence of this method is that consumers are asked to describe their behavior in various situations. Such surveys to study the intentions and behavior of consumers are effective if there is no data on the volume of previous sales. This method can be recommended to managers when making a forecast when introducing a new product to the market.

Role-playing games. The method is used to take into account the so-called human factor. It is extremely effective in analyzing the possible reactions of the counterparty to a particular option of the chosen policy. However, here it is necessary to reproduce the situation in which the interaction takes place as realistically as possible. In practice, the method is rarely used.

Expert assessments. The essence of this method is to develop a collective opinion of a group of specialists on a particular product. In practice, there are several methods of peer review. Consider one of them - score method, during which, at the first stage, an expert group is formed from specialists in this field, the number of which should be equal to or more than 9 people, the composition of the group should be homogeneous. At the next stage, all members of the expert group collectively determine the most important parameters (3-5) of the object that can affect sales. Then the degree of importance, or rank, of each selected parameter is established by expert means. To predict or calculate the beneficial effect and each cost element, but for each class of objects of the same purpose, its own scoring system is built, since the beneficial effect and cost elements are affected by their own factors or parameters.

Important to remember!

The method of expert assessments differs significantly from the study of the intentions of counterparties, since if an expert is asked to assess the dynamics of the market, he is not required to be representative, on the contrary, each expert is unique. As a rule, from 5 to 20 experts are involved, and the most effective way to obtain a single assessment is to weigh individual results with equal weights. The accuracy of forecasts obtained using this method can be improved by applying Delphi-type procedures.

Delphi method. It is one of the varieties of the method of expert assessments. Its essence lies in the iterative procedure for obtaining an integral indicator with a consistent decrease in the variance of the discrepancies of expert estimates. The specificity of this method is that the generalization of the results of the study is carried out by an individual written survey of experts in several rounds according to a specially developed procedure. The reliability of the method is considered high when forecasting for a period of one to three years, and for a longer period of time. Depending on the purpose of the forecast, from 10 to 150 experts can be involved in obtaining expert estimates.

brainstorming method(or brainstorm). Like the Delphi method, it is a variation of the peer review method. Its basis is the development of a solution after a joint discussion of the problem by experts. Experts, as a rule, are specialists not only in this problem, but also in other fields of knowledge. The discussion is conducted according to a predetermined scenario.

The advantage of expert methods is their relative simplicity and applicability for forecasting almost any situation, including in conditions of incomplete information. A feature of these methods is the ability to predict the qualitative characteristics of the market (for example, changes in the socio-political situation, the impact of the environment on the production and consumption of certain goods).

The disadvantages of expert methods include the subjectivity of expert opinions and the limitations of their judgments.

previous time periods and include moving averages, exponential smoothing and regression analysis.

Moving average method. One of the well-known time series smoothing methods based on the fact that random deviations cancel each other out in average values ​​due to the replacement of the initial levels of the time series by the arithmetic average value within the selected time period. The resulting value refers to the middle of the selected time interval (period).

Then the period is shifted by one observation, and the calculation of the average is repeated. In this case, the periods for determining the average are taken to be the same all the time. Thus, in each case under consideration, the mean is centered, i.e., referred to the midpoint of the smoothing interval and represents the level for this point.

When smoothing a time series with moving averages, all levels of the series are involved in the calculations. The wider the smoothing interval, the smoother the trend. The smoothed row is shorter than the original one by (P - 1) observations, where P- value of the smoothing interval. The choice of smoothing interval depends on the goals of forecasting.

Where t+ 1 - forecast period; t- period preceding the forecast period (year, month, etc.);;/, + , - predicted indicator; t,_ i- moving average for two periods before the forecast; P- the number of levels included in the smoothing interval; y t - the actual value of the phenomenon under study for the previous period; y,_ (- the actual value of the phenomenon under study for two periods preceding the forecast.

When using this method, it is necessary to take into account that data for previous periods are characterized by a base value, a trend, cyclicality (seasonality), and randomness.

The use of the moving average method allows managers to largely smooth out random deviations and make trends (cycles) more obvious.

Exponential smoothing. Exponential smoothing forecasting is one of the simplest forecasting methods, but it is only suitable for forecasting one period ahead. The working formula of the exponential smoothing method is presented below.

Where t- the period preceding the forecast; t+ 1 - forecast period; U[+i- predicted indicator; A- smoothing parameter; y t- fact-

the value of the studied indicator for the period preceding the forecast; U t- exponentially weighted average for the period preceding the forecast period.

When forecasting by this method, there are difficulties associated with choosing the value of the smoothing parameter a and determining the initial value t/ 0 .

The exponential smoothing method is most effective in developing medium-range forecasts.

Regression analysis. This method is a generalization of the time series model. It is widely used in practice by managerial specialists and is easily calculated using Excel. This form of extrapolation is based on a regression analysis in which the time period is considered the independent variable.

4. Methods based on modeling (an associative category of forecasting methods). They include the method of leading indicators and econometric models.

leading indicators. When building forecasts in the economy, certain macroeconomic indicators are used. If the values ​​of these indicators change before changes in the economy, then these indicators are called leading indicators. Leading indicators are available in any sector of the economy, and all of them are forced to focus on them. So, for example, indicators of stocks of cars in the places of their sale act as leading indicators for the automotive industry. Very often, changes in the economy are considered changes in the level of employment.

Econometric Models are large-scale regression models based on several equations. Currently, they are not particularly popular with managers because of their high cost and the desire of companies to reduce all their costs. However, they can be used to analyze the consequences of implementing various strategies, plan the dynamics of the market and the business environment, thereby generating various development scenarios. When choosing this method, it should be taken into account that it will be necessary to predict the values ​​of explanatory factors. Some of them (for example, fashion) can cause big problems.

In general, the application of econometric models will be effective if there is a strong causal relationship between the quantity under study (for example, sales) and a set of factors, and also when the form of the relationship is known and can be estimated.

The choice of a method for constructing a forecast in each specific situation is a complex process. As a rule, the manager always has a choice of several alternatives. Usually, in practice, specialists use methods based on judgments to prepare short- and medium-term forecasts, and the most popular quantitative method is the moving average method.

• Fatkhutdinov R. L. Strategic marketing: textbook. M. : CJSC "Business School" Intel-Sintez "", 2000. S. 198-200.
• Consolidated sales forecast. The forecast of sales volume is made by specialists of the sales service department. The advantage of this method lies in the fact that the sales department specialists are in close contact with the sellers, who know their customers very well, the specifics of their behavior, and the volume of purchases of the product. Based on these estimates, sales quotas for the product are often set. However, as practice shows, sometimes their size is somewhat underestimated by sellers.
• Consumer-oriented methods. Among them, there are two main ones - market testing and market reviews. Market testing. The essence of this approach is to conduct primary marketing research of the market. To collect information about the product market being studied, specialists often resort to conducting focus groups and consumer surveys at the points of sale of the product. Recall that a focus group is usually understood as a group of respondents, including eight to ten potential consumers, brought together to discuss a topic in which each of them is more or less interested. The discussion process follows a predetermined scenario under the guidance of a moderator. The discussion can last up to two hours, although sometimes it becomes necessary to work longer. Focus group discussions are considered qualitative methods because the data obtained cannot (in a statistical sense) be said to be representative of that particular population group. Surveys of the state of the market. The essence of this method is to study the market and interview potential consumers of the product regarding the degree of their readiness to purchase the analyzed product. Usually, a potential consumer is asked to rate the degree of readiness to buy a certain product on a 10-point scale, where 10 points corresponds to the respondent's firm intention to buy this product. The results obtained regarding purchase intention are then transferred to the total population in the country. Given the tendency for real-life consumers to overestimate the likelihood of buying a product, sales forecasters often use a “but to the maximum” approach when making sales forecasts. only the number of maximum marks (10 points) is counted.
• Sales extrapolation methods (time series methods). They are based on available data on sales volumes over the
• 2 URLs: http://www.ekonomika-st.ru
• There.
• URL: http://www.ekonomika-st.ru

The sales forecasting process is much easier than you think and much more useful than you think. This is not an assumption about what will happen in the future. It is expectations + control + management.

You need to analyze and regularly revise your sales forecast. Since sales are directly related to costs and expenses, the forecast helps control the budget. In addition, you have information on the number of products, volumes and distribution channels for this, and all this, as in any other enterprise, is measured by business results.

You should all the more focus on forecasts if you are selling a new product or starting a new business without specific data for past years. However, in any case, the sales forecast does not accurately predict the future. This is clear from the very beginning. What you must do is to identify the drivers of sales and identify interdependencies, that is, connect the points of contact in such a way that you can easily correct the course of your sales behavior.

If you think sales forecasting is difficult, try running your business without any forecasts. It's much more difficult. The fact is that your sales forecast is the basis and foundation of the business plan. Entrepreneurs measure business growth by sales, and your sales forecast sets the standard for costs, profits, and growth. The sales forecast will almost always be the first set of numbers you need to track in your business plan.

Forecasting sales, analyzing actual results, making adjustments - this is actually business planning.

Sales Forecasting - Simple Math

For the business plan, make your sales forecast for the next 12 months and two years after that. Present it as a table with columns. Specify the number of units of the product, the price for them, and then calculate what the sales result should be.

If you are selling services rather than goods, you can simply forecast sales by project or commitment, just as lawyers, accountants, and other consulting professionals do.

How do you know what figure should be hidden in sales forecasts?

The math may be simple, but when it comes to predicting the future, people aren't good enough. Don't try to predict the future accurately months in advance. Instead, seek to understand what drives sales: is it traffic or conversions? Consider the situation with a few examples. Record the results of your data analysis every month and adjust your forecasts. Your guesses will become more accurate over time.

Experience is a huge advantage

Consider the example of a bicycle shop owner who already has experience in sales. He doesn't understand accounting and doesn't understand forecasting as a technology, but he does know his shop and the bike business well. He is aware of all the changes taking place in the market and the existing ways to promote the business. And so he makes competent guesses.

If you personally have no experience, try to find information and make predictions based on the experience of your employees, colleagues, investors and other people with whom you discuss industry issues.

Use past results as a guide

Use the results of the recent past, if your business has them. Compare recent figures with data from previous years and draw the appropriate conclusions.

Perhaps you have new opportunities that stimulate sales growth? Or have you launched new marketing campaigns? Are there new competitors and new challenges? No one wants to predict a decline in sales, but if such a situation is possible, you must be able to respond to it correctly - reduce costs and change focus.

Look for drivers

To predict sales for a new restaurant, you need a table and chair layout to estimate the occupancy of the establishment and to estimate the number of orders in a situation where the restaurant is operating at full capacity. This will not be a random number, but an indicator of how many guests, in principle, the establishment can serve during its operation.

To estimate the level of sales of a new mobile application, you can use the data on the number of downloads of applications related to the subject. In principle, you can use any information from reliable sources on the Internet, from blogs, industry news that talk about the state and trends of the application market.

Take a look at the available data and think about how it might differ in your case. You might be able to guess what percentage of your site's visitors will download the app based on the level of traffic on your site.

Estimate Direct Costs

Direct costs are important because they help calculate profits, which are taken into account as a basis for comparing data in financial documents and indicate the level of profitability of a business. But not all companies have direct costs. In particular, service companies (lawyers, for example) usually have no direct costs, so their profit is 100%.

To form a normal sales forecast, you need to take into account information on the number of units of goods, prices, costs per unit of goods, etc.

Never Make Predictions Spontaneously

Never engage in sales forecasting in isolation from reality. Forecasts flow from strategic plans with their assumptions, milestones and metrics. Marketing activities are closely related to sales, just like every stage of business planning.

You will, of course, change milestones because all business plans change—and you will, of course, have to adjust your sales forecast to fit one another.

Rely on your predictions

Sales forecasting is not a story about guessing exactly what will happen in the future. This is a story about how, by making assumptions, you can effectively manage change—sales, direct costs. And then some indicators may differ from those that you expected. Use this information to improve your business by correcting course and eliminating inefficient business practices.