Seasonal sales planning for changing economic conditions

  • Tutorial

The development of a sales plan, and the provision of this plan, is an acute issue in the current economic situation. An incorrectly drawn up plan leads to direct losses - both in the case of excess storage of goods in the warehouse, and indirect losses - in the event of shortage of goods in the warehouse, which leads to lost profits, poor service, and even extra bonuses to sales managers.

One of the problems that strongly affect the preparation of the plan is the seasonality of sales of certain goods. Some products, for example, running shoes, are more popular in summer than in winter. But heaters sell better in the cold season. These items are seasonal.

The instability is also caused by the unstable macroeconomic situation, when inflation pushes prices up, and declining consumer demand forces them to reduce sales in quantitative terms. In addition to negative factors, positive factors can also influence - both for the company as a whole - if the company is actively growing, and for specific product positions - if you invest a lot in product marketing, then demand for them can grow faster than the company’s growth. All this introduces a corrective element in the forecasts, because it is not so clear to focus on information about the sales history, not taking into account the real situation.

Therefore, when drawing up a sales plan, take into account the seasonal factor, and trends in the company.

How do I calculate a seasonal plan?

What is the seasonal factor - "seasonality"? This is the planned and regular deviation of product sales from the average values. Seasonality is often calculated on a monthly basis per calendar year relative to the previous calendar year for each product for which a sales plan is built and for each outlet individually, and the final plan is compiled by consolidating from the obtained values.

To calculate the coefficients, I recommend calculating in units. If you are calculating in monetary terms, the number of influencing factors increases many times and this, in addition to increasing the volume of calculations, will also greatly increase the chance of error.

Calculating annual seasonality factors is quite simple - you need to take the average monthly sales at the end of the year (the amount of sales per year divided by the number), and then, for each month, calculate the deviation of the actual sales from the average annual.

(Consumption per month / Average annual consumption = Seasonal coefficient)

If we have a sales schedule that looks like this:

According to the calculation results, you should get something like this label for calculation (for 2010):

Seasonal coefficients:

But the task is not to calculate the coefficients as those, and calculate the sales plan according to the current actual values ​​of sales per year. Suppose we conduct an analysis at the end of April 2011 and calculate a sales plan for May 2011:

And our plate will look like this:

The task is to understand how much we should sell for May, taking into account current actual sales and seasonality. To do this, we will bring each month of the current year to a single base, removing from them the seasonal coefficient that we know.

(Actual Consumption per Month / Seasonal Coefficient = Est Average Annual Consumption)

We get the following values:

Which means that if seasonal factors are taken into account, then the expected average monthly per year is 246 pcs / month.

From this, knowing the expected average for the year and the seasonal coefficient in May (calculated at the previous step), we calculate how many sales are expected in the month of May, multiplying the expected average annual sales by the calculated seasonal coefficient: 246 * 1.44 = 354.4 units.

Thus, we continue to formulate a sales plan for each month until the end of the year, adjusting according to actual sales data.

Unfortunately, these laconic calculations are not entirely correct ...

We took into account the influence of seasonal fluctuations, but did not calculate the influence of the general trend. If your demand falls (or grows) by 10% every month for objective reasons, then without taking these movements into account, your newly drawn up plan will become untenable, and, as we said above, it will lead to losses.

How to evaluate the effect of a trend?

Identifying and calculating a trend is a difficult task in itself. I recommend using the method developed by Robert Hodrick and Edward Prescott to analyze business cycles. The main “chips” of the method: the method is more sensitive to long-term trends than to short-term ones (which is what we need), and it can be adjusted to the desired period of trend assessment (which is useful when building a trend both within a year and for several years). You can learn more about this method at the link:

The result of its calculation looks something like this (orange line):

The problem is that such a method is difficult to use in calculations in Excel. But you can try to use simply linear functions by calculating the average monthly sales for the state “at the beginning of the year” and “at the end of the year” (taking into account seasonality), and assessing how it has changed over time. Or simply taking the target value, the one you would like to focus on ("I am sure that sales should grow by 10%").

Be that as it may - the result of the calculations is the monthly coefficients of the "slope" of the trend for each of the goods for each of the outlets for each month where you calculate the sales plan. The problem is also that in a normal situation, inside the year it is not a straight line, but it smoothly bends.

The obtained coefficients are used to adjust the estimates of average annual sales, on which, I recall, we rely on estimates of future sales.

If we assume that in the current economic realities, demand in unit terms will fall by 10% by the end of the year, then the monthly correction factor should be approximately equal to 0.987. This means that by this coefficient we will change the estimated average monthly inside the current year according to the trend coefficient for each month:

(Actual Consumption per month / Sez. Coeff * Trend factor. = Est Average annual consumption)

And the calculation of the current values ​​will look like this:

Noticed that the result was 349.8 pcs. instead of previously calculated 354.4 pcs.? It seems that this is not very much, but if you have billions of revolutions, then such an error costs a lot.

To increase the quality of work with seasonality, it is necessary to recalculate the annual seasonal coefficients for the previous year, relative to the identified trends. But if you do not want to make a large amount of calculations, even such a small refinement for the current year is already able to qualitatively improve planning.

It is important that these calculations were carried out and adjusted regularly, according to actual data, in order to get the most adequate sales plan and understand how you will provide and control it.

In real work, professionals usually use more complex approaches. The calculation is carried out not by months, but by weeks, or, even, by days. Target values ​​are affected by more factors. And the forecast model goes beyond the usual average calculations. But the approach presented above is something that any person involved in planning can apply, even without special tools.

If this is too time-consuming to do in “manual mode”, and if you have 10 outlets and 15,000 products, then welcome to us. Our decision will do everything for you. Mycroft assistantin real time, it automatically collects sales data, analyzes the current state of sales, independently calculates the optimal model of work and influencing factors. And on the basis of the data obtained, it forms a sales forecast for each of the goods at each of the outlets. And on the basis of these forecasts, it issues recommendations on the need for replenishment of stocks, so that you rationally provide this sales plan. So, if you want to optimize the work of the company, but don’t know how best to do it, welcome to us.

Also, we invite you to the webinar "Effective Inventory Management", which is conducted by our partner Corus Consulting. Registration is available at:

Also popular now: