How to formulate and calculate the company's sales plan. The implementation of the sales plan can be easily checked in Excel Sales planning for the next year

It is not always enough, and often possible, to display the plan-fact in Excel in the form of a small chart. Sometimes the report on the implementation of the sales plan includes a lot of positions and is presented in the form of a boring table, where the plan is displayed in one column, the fact in the other, and the percentage of completion in the third.

Well, for example like this:

Just? Yes. Informative? I guess, yes. Clearly? No.

At the same time, Excel has a very simple data visualization tool with which you can increase the visibility of such tables by an order of magnitude. It's the Conditional Formatting tool, which sits almost in the middle on the Home ribbon.

You can use all three formatting options: bar graphs, color bars, icon sets.

Here is an example of using histograms:

You can immediately see who is ahead, who is behind, and by the size occupied by the histogram in the cell, without looking at the numbers, you can evaluate the execution of the plan.

To get such histograms, you need to select the cells with data on the execution of the plan and select "Conditional Formatting" - "Histograms".
I recommend setting the maximum value to one (100%) in the formatting rules. So that cells with overflow do not affect the scale and the line at 50% would be exactly in the middle.
It looks like this:

Color and other things can be changed in the settings.

Using color scales gives a very visual result, but for my taste it is too colorful:

A very neat result is the use of a set of icons with mini-pie charts in reports on the implementation of the sales plan. Limit values ​​are adjustable.
The main thing is to put in the value type "Number". For some reason, the percentage option gives me an incorrect result.

Bonus.
And another option for visually displaying the plan-fact in the sales report is the so-called thermo-diagram.
Very similar to the bar charts from the first example, but looks neater due to the fact that the chart rectangle is in a box.

But to draw this you have to tinker a little.

1. Add a column with units somewhere on the side. This will be a plan equal to 100%.

2. Based on the column with data on the execution of the plan and the auxiliary column, we build a regular histogram


3. Click on the bars of the chart, select "Format Data Series" and set the series overlap to 100%. We reduce the side gap so that the lines are thicker.

4. Remove the filling of rows with auxiliary units. We set the color of the border to be the same as that of the row with the values ​​of the execution of the plan.
5. The maximum value on the horizontal axis is set equal to one. Overfulfillment of the plan will not be shown. It should turn out like this:


6. Remove all unnecessary - title, legend, axes, chart area fill, grid, borders. We stretch the construction area to the borders of the diagram area, filling the entire place, leaving a few gap pixels on the right.
7. The final touch - move the chart to the data column and adjust the scale. Everything!

Consider planning sales and cash flow using an autoregressive model. Estimating future cash flows is important for both the owner of the company and investors to determine its effectiveness in the future.

Planning of sales and cash flows of the enterprise

Forecasting sales and cash flows is an important task for a company. Evaluation of future receipts from the sale of products allows you to plan cash flows that can be used to improve the efficiency, productivity and value of the enterprise for investors.

Purpose of Sales Evaluation- assessment of the effectiveness and efficiency of the enterprise, break-even point and financial safety margin in the future.

Purpose of cash flow estimation– assessment of the company's potential for the development of innovations and implementation investment projects.

A company's sales and cash flows are closely related to each other by the following formula:

where: CF i ( Cash flow) - cash flow.

Sales and Cash Flow Planning Methods

There are many different methods for forecasting sales (cash flows): the moving average model ( MA,MovingAverage), autoregression model ( AR, Autoregressive), moving average autoregression model ( Autoregressive Moving Average model, ARMA), the Box-Jenkins model, etc. In this article, we will analyze forecasting using the autoregressive model in more detail.

Autoregressive Models (EnglishAR) are used to describe stable (stationary) processes in the economy, when the future values ​​of the predicted quantity are influenced by previous values. Autoregressive models (AR) are used in forecasting both macroeconomic indicators (GDP, inflation, etc.) and for assessing microeconomic indicators: the volume of future sales, net profit, cash flow, etc.

Sales and cash flow forecasting model

where:

Y i - forecast cash flow or sales volume

Y i -1 - the value of cash flow and sales in the previous period;

ξ is a random variable (white noise).

An example of sales and cash flow planning for an MTS OJSC enterprise in Excel

Let us analyze a practical example of planning sales (revenue) and the volume of cash flows of an enterprise of MTS OJSC. This enterprise was chosen for analysis because it has a stable network of distributors and a constant demand for products, which allows an adequate assessment. The figure below shows the company's revenue and cash flow for 10 years. The data was taken from the official reporting of the enterprise. Cash flows were the sum of the net profit of the enterprise and depreciation (Form No. 5, p. 640 + Form No. 2, p. 190).

Sales volume and cash flow for MTS OJSC

Graphically, the change in sales volume and cash flow is as follows:

As we can see from the figure, the company's cash flow changed dramatically in 2009 due to the large amount of accrued depreciation, which greatly distorts the dynamics of cash flow changes. Let's make a forecast for two years ahead of the company's sales volume and cash flow using the AR model.

Initially, to build a model, it is necessary to determine the tightness of the relationship between the closest values ​​of sales (cash flow). To do this, it is necessary to evaluate the regression with a shift in the sales volume series. A lag of one year was taken, because it is previous sales that have the maximum impact on future values.

On the next step it is necessary to calculate the values ​​of the regression coefficients between series and series with lags of one year. Let's use the add-in: Excel Main Menu → "Data" → "Data Analysis" → "Regression". We calculate the parameters separately for forecasting revenue and cash flow. An example of estimating sales volume is shown in the figure below.

We got the base values ​​in the regression model for revenue (sales volume). So the alpha coefficient (α) in the regression model is 16851967162, and the beta coefficient (β) is 1.04. The obtained statistics on the regression model has the following important indicators for assessing its adequacy and forecasting accuracy. The first thing you should pay attention to is the R-squared indicator ( coefficient of determination), which shows the quality of the model on a scale from 0 to 1. In our example, the quality of the model is high and is 0.97. Model exponent criterion-F close to 0, which indicates the stability of the model. statistic P-meaning reflects the adequacy of data values ​​coefficient (alpha, beta) for the resulting model, it is less than 15% for both coefficients, which meets the standards.

Regression model quality scores in Excel

Similarly, a model for planning the cash flows of an enterprise is built. As a result, the resulting sales and cash flow forecasting models are described using the following equations:

Let's make a forecast based on the obtained models of sales volume and cash flow values ​​for two years ahead. Using formulas in Excel, we will make a forecast for the model.

Sales forecast by modelAR=$B$19+B6*$B$20

Model Cash Flow ForecastAR =$ C$19+$ C$20* C6

Visually, sales planning will look like this. There is an upward trend for two years ahead.

Graphically, the cash flow forecast for two years ahead will not change much.

Forecasting the cash flow of an enterprise using an autoregressive (AR) model

The use of cash flow forecasting methods allows you to evaluate the performance indicators of investment projects, read more about project evaluation methods in my article: ““. There are also other methods for planning the company's sales volumes: XYZ-analysis, ABC-analysis, which have also proven themselves in practice. So the ABC analysis method for practical example disassembled in the article: "".

Master class: "How to calculate a sales plan"

Summary

The use of autoregression (AR) methods for planning future sales volumes (cash flow) is justified if the company has a stable network of distributors and buyers for its products. The advantage of using this valuation method is the ability to take into account the impact of the previous sales volume (cash flow) on future values. In situations of economic crisis and instability, the assessment can change greatly under the influence of macroeconomic factors and global trends.

Commercial organizations are created with one goal - to maximize profits. To this end, planning tools, optimization labor processes. The task of the manager is not only the effective operation personnel, but also planning the company's sales for the near future.

Today we will talk about what is a company sale plan, consider characteristics procedures, development rules.

Planning - effective tool achieving the set goals, successful management business.

A sales planning plan is a document that implies the development of a general strategy for the company's development. A department with the same name cannot work effectively without a description. general provisions and directions for further professional activity.

The development process is accompanied by the preparation of accompanying documentation and an analysis of the current situation. Life often brings surprises, which are not so easy to manage.

It's important to know! An experienced manager is obliged to calculate the reasons for not fulfilling the tasks set, to minimize their destructive impact on professional activity companies.

For a step by step guide to creating a sales plan, watch this video:

Features, goals and principles of planning

The task of authorized persons is not so much to predict the future, but to assess the available resources. Based on them, a preliminary model for the further work of the organization is created.

However main feature process - to create a motivating effect on staff. Make the immediate goals of the company their tasks, stimulate the activity of the work process.

Sales planning has several goals:

• development of logistics for the distribution of available resources to priority areas. Coordination of the work of subordinates in the field of optimization of the actions performed;
• assessment of the abilities and performance of persons responsible for sales;
• control general activities organizational structure;
• creation effective methods management, development of a strategy for further development. you will learn what conflict management strategies exist in an organization.

It's important to know! The document indicates the volume of goods that need to be sold for a certain time period.

Depending on the direction of the impact, there are: general (designed for all subordinates), individual (corrects and sets the rhythm of work individual employees) plan.

A professional manager adheres to the following principles when drawing up a company's sales plan:

1. Attainability - at the stage of setting goals, it is important to take into account the capabilities of the team and the potential of the product being sold. At the same time, it is impossible to build on the indicators of the previous period, since the market situation depends on many factors: the popularity of products, their seasonality, the activities of competitive structures, the improvement or deterioration of the country's economic indicators. It is important to take into account all of the above nuances when drawing up a document.
2. Concreteness and measurability - the plan must operate only with numerical indicators, perhaps it is unacceptable here.
3. Time constraints - the presence of clear deadlines for the implementation of tasks stimulates employees to achieve positive result. Evaluate the capabilities of each manager, otherwise the miscalculation will be written to the account of the responsible person.
4. Resource availability - setting unrealistic deadlines or tasks does not help motivate subordinates (the sale of 100 TVs in less than a month is an unbearable burden).
5. Unity - the sales plan provides for clear cooperation and symbiosis with the rest of the departments of the object in question. The provisions of the document should not conflict with the real situation within the structural parts of the company.
6. Continuity - the interchangeability of tasks for the sales department is necessary to maintain the efficiency and working "tone" of subordinates.
7. Flexibility is a bad plan that does not tolerate correction. During the development phase, it should be possible to change some aspects of the plan.

It's important to know! It is not enough to draw up a high-quality document, the company's personnel should have the opportunity for the practical implementation of the plan.

An example of filling out a sales plan.

Rules for performing assigned tasks

Consideration of the topic of sales planning requires a view from not only the manager, but also his subordinates. For them, it is stressful and at the same time motivation to increase personal effectiveness.

Here are a few basic rules that will help you achieve a positive result:

• inventory available tools that are potentially suitable for the implementation of the plan. If the manager sees that his capabilities are not enough to complete the tasks, you need to contact the higher management. What is it you will learn in the publication at the link;
• use the potential of the "sales funnel". It consists in converting ordinary visitors outlet into buyers;
• Segment the most profitable customers and pay maximum attention to their service. The success of the operation will bring a promotion for the current month;
• work on increasing the average bill - complementary goods will help here, the cost of which allows you not to think about the seriousness or importance of the proposed purchase;
• inform potential buyers about the company, main activities, product range;
• calculate the percentage of completion - to do this, divide the actual indicators by the planned ones and multiply the value by 100.

It's important to know! According to statistics, only 30% of citizens clearly know what they want to buy. The vast majority rely on the support of sales managers.

Why the plan fails

There are quite a few reasons, but marketers consider a few of the most popular options:

• the presence of gross errors at the planning stage - these include: incorrect statement of the problem, its unattainability in the current conditions;
• low competence of subordinates - managers do not have sufficient knowledge and skills to sell their goods. It will help to conduct training courses for staff or to monitor their work, testing. you will find out what factors and circumstances influence the increase in labor productivity;
• insufficient motivation - the introduction of a rating system, bonuses and competitions will awaken the hidden potential of employees, make them more proactive. The interest of managers in achieving results is the main task of the manager.

It's important to know! The above list of "sharp corners" is far from complete, but reflects the complexity and high level of responsibility at the stage of developing a company's sales plan.

Checklist for creating a plan

The document development process itself consists of many steps and operations, the consideration of which will provide the following numbered list:

1. Goal setting - the manager indicates the tasks, the implementation of which is a priority for the staff or a particular manager. The list and achievability of the goal should be consistent with the SMART strategy, which reflects relevance, time frame, achievability and measurability.
2. Analysis of market opportunities - the person in charge analyzes the situation of the macro and meso environment, the market and the niche occupied by the company. Taking into account most of the risk factors will significantly increase the "survivability" and relevance of the plan.
3. Analysis of indicators for the last month / quarter - the operation will help determine the optimal seasonality of the goods sold, identify additional factors that affect sales performance.
4. Forecast - drawing up an objective and subjective opinion about further development events. The second is based on expert assessment, the first operates on historical reports.
5. Adjustment of the sales forecast - comparison of the results of forecasting and previously defined goals.
6. Planning for the sale of goods - step-by-step instruction to achieve the desired result, setting tasks according to the scale of importance and priority.
7. Practical implementation of the plan.
8. Control and adjustment - support of the plan at the stage of its implementation, smoothing out "sharp corners", mistakes made at the development stage.

It's important to know! The deviation of actual indicators from planned ones is a sign of mistakes made by the responsible person. His professionalism or capabilities were not enough to take into account all the factors that affect the final result.

Characteristics of indicators and analysis of the results

Experts identify the following sales indicators that characterize the productivity of the staff:

• the number of goods sold per hour;
• average sales figures;
• the number of items purchased by one consumer;
• conversion rate and quality of service ratio;
• magnitude ratio wages and sales volume.

It's important to know! The above example is relevant for the retail sale of products of a low or medium price category.

Company sales plan template.

Any use of materials is allowed only if there is a hyperlink.

The analysis of the received data and results is carried out by entire departments using specialized software and approaches. The procedure involves the passage of several stages:

• characteristics of changing market conditions;
• calculation of profitability of regional sales;
• consideration of up-to-date information about the sold goods;
• comparison of marketing activity and the results obtained (sales volume);
• correction of pricing factors, commercial conditions;
• modifying the plan and creating a backup strategy.

Conclusion

The company's sales plan is an effective tool for motivating mercenaries to achieve their goals. Clearly defined tasks set the vector for the company's development, create conditions for increasing the efficiency of the labor process.

You will learn how to correctly build a sales plan for a company here:

The main goal of any trading enterprise is to make a profit. Profit directly depends on the volume of sales, which, in turn, is determined by many factors. The sum of these factors determines the company's sales potential, and hence the amount of goods / services that can be sold by a particular company in a given market sector. To ensure the development of a business, you need to correctly assess your capabilities and plan its activities. Therefore, any commercial enterprise sooner or later faced with the question "how to make a sales plan." Properly developing a sales plan means setting specific goals and objectives, that is, structuring the activities of each department of the company, giving their work the necessary direction.

For start-up entrepreneurs, it is the minimum value that should form the basis of the sales plan. Types of planning are distinguished by timing:

• Promising ( strategic planning for 5-10 years).
• Current (developed for a year, taking into account the 5-year plan and clarifies its indicators).
• Operational and production (allows you to divide the tasks of current planning into shorter time periods - annual, quarterly, monthly plan).

For start-up entrepreneurs, it is the minimum value that should form the basis of the sales plan.

How to plan

There are different approaches to creating a sales plan. Analysis methods used in calculating the planned sales volume are usually divided into subjective (a survey of buyers, sellers, management decisions) and objective (product testing on the market, analysis of sales of previous periods, demand analysis statistics).

When planning sales volume, the company should pay attention to the following factors:

• Seasonal (depending on the season, season, month).
• General dynamics of the market (it is developing or shrinking).
• Competition.
• Changes in legislation.
• Increase / decrease in the product range.
• Pricing policy of the organization.
• Existing and planned clients/customers.
• Development of distribution channels.
• Employees (sales quotas).
• The activity of the company in the market ().

Exponential Smoothing

Based on the analysis of historical data, an effective economic tool called "exponential smoothing" is used. It can be used to make short-term forecasts.

Using a smoothing constant (from 0 to 1), you can guess how much product will be needed in the same period next time (day of the week, month, quarter). The exponential smoothing model is calculated using the following formula:

Smoothing constant x Actual demand in the current period + (1 - Smoothing constant index) x Forecast for the current period.

For example, in a week the store sold 750 toys with the previous forecast of 720, the constant is 0.3, the predicted demand for the next week is 0.3 x 750 + (1 - 0.3) x 720 = 729.

With an average level of sales, the constant is 0.2-0.4, during the period of increasing volume - 0.7-0.9 (for example, on holidays and pre-holiday days).

Time series analysis

Time series analysis also uses historical data, but allows you to identify longer-term trends. The analysis process includes the following steps:

• Calculation of the trend (general trend of change in sales volume).
• Definition of seasonal variation (regular fluctuations in sales).
• Determination of residual variation (abnormal deviations associated with force majeure or one-time promotions that affected sales).

The multiplicative model is built according to the formula:

Sales Volume = Trend x Seasonal Variation x Residual Variation.

extrapolation method

The extrapolation method is based on the analysis of the average annual growth rates of sales for previous years. For example, if a company has been increasing its turnover by an average of 20% over the past 4 years, it can be assumed that next year the increase will be the same.

These and similar methods allow you to write a sales plan based on previous periods.

Create a sales plan

The principles of the normative formation of the plan are that the necessary parameters are fixed that you need to strive for: the number (amount) of sales per department/employee, wholesale purchases, retail or total sales. You can build a sales plan based on the desired increase in turnover (total sales). An example of a sales calculation plan is shown below.

The first stage is to determine the seasonal dynamics of sales

This is the calculation of seasonality coefficients. Let's take a small toy store as an example. To calculate the seasonal factor, you need to take the sums of sales for the previous year and get the average monthly value:

yy/mm	1	2	3	4	5	6	7	8	9	10	11	12	Total	Wed
2016	55000	65400	72000	71000	83000	85000	87000	78000	80000	85000	90000	95000	946400	78336,6
K (seasonal factor)	0,697	0,829	0,913	0,900	1,052	1,077	1,103	0,989	1,014	1,085	1,141	1,204	12

940,040 / 12 = 78,336.6 (average monthly sales).

The formula for calculating the seasonality coefficient (K):

K = N1 / SZ, where N1 is the amount of sales for a certain month, and SZ is the average annual value.

For example, for January 2016, K will be 55,000 / 78,336.6 = 0.697, for February - 0.829. For more reliable forecasting, data from previous years should be analyzed in a similar way.

The second stage is to determine the next goal

Based on the results of the past year, a target for the next year is drawn up. For example, increase turnover by 15%.

940,040 + 15% = 1,081,046 is the expected turnover. Let's define SZ (average value) - 9,069 per month. To achieve this goal, a campaign is planned to increase profits (hiring additional employees, expanding the range).

The third stage - we plan monthly

To calculate, it is necessary to multiply the obtained SZ by the corresponding seasonal factor:

yy/mm	1	2	3	4	5	6	7	8	9	10	11	12	Total	Wed
2016	55000	65400	72000	71000	83000	85000	87000	78000	80000	85000	90000	95000	940040	78 336,6
K (seasonal factor)	0,697	0,829	0,913	0,900	1,052	1,077	1,103	0,989	1,014	1,085	1,141	1,204	12
2017	63250	75210	82800	81650	95450	97750	100050	89700	92000	97750	103500	109250	1088360	90690,667

This approach allows us to get the numbers that the purchasing department and salespeople should be guided by in order to fulfill the intended plan. By dividing the plan into months, you can quickly monitor its implementation and make adjustments to the company's policy.

By dividing the plan into months, you can quickly monitor its implementation and make adjustments to the company's policy.

You can make sales quotas - this is the amount of goods that the seller / manager needs to sell. The presentation of planning results in the form will allow the manager and manager to better monitor its implementation.

The establishment of a sales plan (volume) can come both from the company's management (“from above”), and “from below” (in this case it is formed by the sales and purchasing departments). Practice shows that the most effective planning takes into account information from all these sources, since the direct tools - the collection and analysis of sales and supply data - are "located" by managers (heads of sales and purchase departments). They better represent the real state of affairs.

After drawing up the plan, it is necessary to take care of its organization (distribution of tasks by departments), additional motivation of employees, control of execution. In any case, the structure of the company determines the methods of planning the scope and monitoring the implementation of the plan. It is not possible to create a generic planning template. The planning system should be developed taking into account the needs of a particular company.