Sales plan. Practical planning

For the fastest and most reliable sales planning, the program has created special tools for planning and adjusting prices and sales volumes of any complexity, including in the face of changing external factors.

☛ Important! An additional conditional future period has been added to all plans. However, only in the plans of loan and rental products can you plan with an estimated horizon of up to 10 years (up to 120 months). At the same time, all calculations - outside the standard horizon - will be taken into account in the conditional future period. That is, the future period will accumulate all the calculations that will go beyond the 36 months of the project.

To calculate the seasonality trend (for example, on a 100-point scale), take the sales trend for the last year (or the weighted average trend over the past few years). Then, in the line where we calculate the seasonality values, opposite the maximum value of the sales volume (this can be several values), put the number 100 (in this example, the maximum value in January). To calculate the values in other cells, you need to divide the value of the current cell by the maximum and multiply by 100. And so - for each cell, for example:

1. Seasonality factor for February = 1350 / 1500 * 100 = 90
2. Seasonality factor for March = 1200 / 1500 * 100 = 80
Etc.

☛ Note, if you want to build a seasonal trend, for example, on a 10-point scale, then multiply it by 10, respectively.

To adjust the values of seasonality, use a special tool - the slider. The admissible values are from "0" to "+100" (when planning sales volumes) and from "-100" to "+100" (when planning prices). The sliders edit the seasonality values (monthly values in percent) during the annual cycle (12 months).

The values of the slider can be set with the mouse or the arrow keys (preliminary, activate the slider, hover the mouse, as shown in the picture).

All periods of seasonality relating to sales by default are set to "100", and is related to price – value "0".

This is due, first of all, to the specifics of calculating trends in sales volumes and prices: prices are calculated as deviations from a given value.

Go to the "Sales plan", select the product and call the editing form.
"sales Planning" (one click with the left mouse button, same F2):

Use the "continue trend" function if the price for a product changes not so often, for example, every six months. This is the easiest and fastest way to fill a series of prices with a trend.

1. step. Enter the value in the cell where planning begins.
2. step. Click the right mouse button (above the cell), and select "continue trend". As a result of the function, the series will be filled to the end.

The result of the "Continue trend" function:







3. step. Select the next period when you want to set a new price. Do step 1,2 - from any subsequent period ...

Function "interpolation of the series by trend" Use the function "interpolation of the series by trend", if you already have a filled series and you need to interpolate the values ​​of this series in the seasonal trend.

☛ Note that the function is called when the series already exists when the "Calculate" button is pressed.

1. step. Enter the data using the "continue trend" function.
2. step. If necessary, set the calculation start period.
3. step. Click the "Calculate" button and select "interpolate the series by trend". As a result, the series will be filled to the end, taking into account seasonality factors.


Steps 1-3 are performed separately - for planning either a price or an amount.

Set a product price, "100", starting from the first month of the project,
as shown in the figure below:

Let, target price will increase by 7% in 5-m and 10-m month. Install the appropriate values (in 5-m and 10-m month) in the seasonality trend of prices as shown in the figure below:

Click "Calculate" and execute point 2: "interpolation of the series by trend". As a result of executing, all the data of a number of prices, starting with 5-th and 10-th month, will increase by 7%.

Data of the calculation result are shown in table:

Use the function "by annual values" for sales planning if you have the following sales parameters: for sales volumes - seasonality of demand and annual sales, for prices - seasonality of price fluctuations and prices for the beginning of the year.

☛ Note that the function is called when the series already exists when the "Calculate" button is pressed.

1. step. Enter the annual sales volume (quantity), respectively, for 1,2 and 3 year.
2. step. Adjust the seasonal factors of sales on a 100-point scale (from 0-100).



3. step. Set the "Price forecast" switch and enter the prices for the beginning of the year, respectively, for 1,2 and 3 yeara.
4. step. Adjust the seasonal price coefficients (from -100% to 100%).



5. step. Click the "Calculate" button and select "Sales plan - by yearly values" .

Example. Consider a simple example of planning a cyclical sales plan for a contingent product. According to the forecasts of marketing expert estimates, when analyzing the regional market, the parameters of sales are determined: for sales volumes - seasonality and annual sales volumes (for 3 subsequent years), for prices - seasonality of price fluctuations and prices at the beginning of the year. That is, the marketing department prepared a table of seasonality factors (based on seasonality factors for past periods) and probabilistic estimates of annual sales volumes of product N::

1. for 1 year - 10,000 units,
2. for 2 yeara - 12,000 units,
3. for 3 years - 15 000 units

Table shows the seasonal demand factors (sales volumes), in percentages:

As can be seen from the table that maximum sales are during the winter months, minimum in summer. When planning sales, you always need to consider probabilistic seasonal cycles predicted by taking into account observations of past and current changes in market conditions.

In addition, it is assumed that the first 3 months the company will be able to realize no more than 50% of the planned volume of sales, the next 2 months – not more than 70%, then 100% of the target sales.

The price of product N, to simplify the example, will be considered without VAT. Let the initial price of the product 100. Suppose that every six months (in the 1st and 6th months) the price will increase by 5%.

1. step. To plan sales volumes, you need to set the option switch - "sales volume". Let's enter the annual sales volumes in the corresponding fields: 10,000, 12,000, 15,000 (for 1, 2 and 3 yeara).
2. step. Set and save the seasonal coefficients (from the table). To set the exact seasonal values, use the arrow keys.
3. step. For price planning, set the corresponding option - "price". We will enter the prices for the beginning of the year: 100, 110, 120 (for 1, 2 and 3 yeara).
4. step. Set and save the price change for 5%, starting at 6 months. Pay attention, at setting of seasonal values ​​of the prices, in 7-12 (subsequent) months the values ​​will be set automatically. To set the exact seasonal values, use the arrow keys.
5. step. Let's perform the calculation "by annual values", as shown in the figure:






6. step. Using the formula (= number-n%), we will reduce sales in the first 3 months by 50%, the next two - by 30%, as shown in the figure:






7. step. Finally, perform a final calculation of sales plan: Main menu → Data → Calculate → to Set the flag "sales plan". Same thing - F9 → Set flag "sales plan".

Table of the result of calculation of the "sales plan" for the product N:

In the result of the calculation of "by annual values," the price for the beginning of each year is taken as the basis for calculating the price of the trend, according to the formula:

Price_n = ₁ × Factor_n,

Where:
Price _n - the price in the n period.
Price ₁ - the price at the beginning of the year.
Coefficient _n - (percentage of seasonality) / 100 in the n period.

As a result of the calculation of "by annual values," the annual sales volume (quantity) is distributed along a number, depending on the seasonality trend, according to the formula:

Quantity _n = Quantity _year × Coefficient _n , where
Coefficient _n = Seasonality _n / ∑ Seasonality _i ,

Where:
Quantity _n - quantity in the n-th period.
Quantity _year - total annual quantity.
Coefficient _n - in the n-th period.

1. step. Enter the number 100 in the "annual sales -1, 2 and 3 year" fields (in this example, the annual sales volume).
2. step. Set the values for seasonality, respectively: 50%, 70%, 60%, 70%, 90%, 10%, 0%, 30%, 70%, 70 %, 80%, 100% . To adjust the values of the sliders, you can use the arrow keys, pre-displaying the seasonality value (mouse hover).
3. step. Click the "Calculate" button and select: "Calculate sales plan - by yearly values".

The calculation of the amount shown in the table:

1. Price, weighted average (price without VAT)
2. Calculation of residuals
3. Calculation needs.

The need for production (purchases) is calculated by the identical formula:

Requirement _[i] = Sales _[i] + (Stocks at the end of the period _{[i + 1]} - Inventories at the beginning of the period _[i] ) - Remains of the past period _[0] ,

Where i is the period.

To calculate the weighted average is performed step-by-step cyclic algorithm when the weighted average price, calculated in each i-th period, enters into a subsequent period, where is calculated according to the formula:

AP _[i] = (Amount of inventory at the beginning_[i-1] * Suzana _[i-1] + Number of purchases _[i] * cost _[i]) / (Quantity _[i-1] + _[i])

Where AP – average price i period.

Select your Budget-Plan Express licenses and pay in your personal account - in rubles or other currency