There is data on the activities of the enterprise for the retrospective period (Table 2.2).
Required:
make a forecast for the next three years using the CAGR method and regression analysis;
compare the results of forecasts and justify the choice of enterprise development strategy.
Table 2.2–Railway freight turnover value
|
Freight turnover |
Values by year, million t-km |
||||||
Option 1. Let's determine the forecast values based on the average annual rate of change in indicators using formula 2.1. For local freight turnover, the average annual rate of change in values will be equal to
Forecast values of indicators with this approach are determined by formula (2.2). Then for local freight turnover the value for the first planned year will be
11312.12 million t-km.
The remaining values are determined similarly. The calculation results are summarized in Table 2.3.
Table 2.3–Calculation of forecast values of cargo turnover
|
Freight turnover, million t-km |
Value for 7th year |
Forecast for the years |
|||
Option 2. Let's determine the forecast values for the local freight turnover indicator using regression analysis. Analysis of the presented statistical data allows us to choose a linear form of the function to describe the pattern of changes in target indicators over time, so we will use formulas 2.3 and 2.4.
We summarize the intermediate values for calculating the coefficients of the regression equation in Table 2.4.
Table 2.4 – Values for calculating the regression coefficient
Then the system of equations (2.4) will take the form
After solving this system, we obtain the values =8243.143 and =410.607; and the regression equation takes the form
V p = 8243.143+410.607· t
Where t - year for which the forecast is made: t = 8, 9 and 10 years.
The calculation of other indicators is carried out similarly.
Table 2.5–Forecast of freight turnover values
|
Freight turnover, million t-km |
Odds |
Forecast for the years |
|||
It is necessary to take into account that the total cargo turnover is a complex indicator, and is defined as the sum of cargo turnover in local traffic, import, export and transit. Therefore, when forecasting complex indicators, they must be decomposed into components (in our case, local, import, export and transit), the predicted values of the components must be assessed, and the value of the complex indicator must be found using the formulas of the corresponding dependencies. Let us analyze the forecast value of the total cargo turnover, calculated according to two options, and the values obtained by summing up the forecasts of the elements included in the cargo turnover (Table 2.6).
Table 2.6–Comparison of cargo turnover forecast results
|
forecast |
Values by year |
|||
|
values | ||||
As can be seen from the table, with the first calculation method there is a significant increase in the discrepancy in the value of the total cargo turnover with increasing forecast period. When calculated using the correlation method, the values are the same.
To select the final values, we will display graphically the values of the completed total cargo turnover and the predicted values calculated using two options (Figure 2.1). The second option (regression model) more accurately reflects the further pace of development of production volumes. The final choice of the variant of forecast values for the development of the enterprise is carried out if there are forecast values for all target indicators characterizing the development strategy of the enterprise.
Figure 2.1 – Comparison of cargo turnover forecast results
The realism and feasibility of the company's budget largely depends on how correctly the product sales plan was drawn up and, accordingly, the revenue was forecasted. This solution offers several ways to plan sales, from which you can choose the most suitable one for the specifics of the company’s activities.
Advantages and disadvantages
The decision reveals in detail and with examples the procedure for planning sales volumes in physical and monetary terms, as well as coordinating the sales plan with the budget of income and expenses, and cash flow. If sales planning is the prerogative of the commercial service, the proposed methodology will be useful to the business owner to check the validity and correctness of the stated figures.
Since most companies operate in a competitive environment and business success depends on the ability to sell products, we will consider the option when the sales plan serves as the starting point when drawing up a budget.
How to organize sales planning
Sales are usually planned by businessmen and economists. The first of them predict the state of the market, relationships with customers, determine the value of sales and (or) price growth rates; the latter provide analytical material (based on accounting and (or) management reporting). Depending on what criteria are especially important for the enterprise, the sales plan can be structured in different ways: by counterparties, product range, price groups, conditions, payments, etc. Sales can be planned over a horizon of either a month or several years. As a rule, they are forecast for the year broken down by month and for the next few years - without breakdown. If necessary (difficult financial situation and the threat of cash gaps), greater detail is possible - for example, only the first (nearest) quarter is disclosed on a ten-day basis, and then a monthly plan is given.
How to prepare a sales plan
For planning “from what has been achieved”, the basis is information on the dynamics of sales (in physical and value terms) for the previous period, comparable both in duration and seasonality with the planned one. This requirement can be difficult to meet, since sales are usually forecast in the fourth quarter, when the year has not yet ended and the results for it have not yet been summed up. In this case, information is used on actual sales for the past 9 or 10 months and planned sales for the time remaining until the end of the year (November–December).
If a company applies different VAT rates or is engaged in several types of activities that provide for different taxation systems, then it is especially important for it to forecast sales in value terms without VAT - this way the plan will be more correct. This can also be recommended for companies that apply the standard 18 percent VAT. In the future, when clarifying the areas of use of the basic forecast (for example, to prepare a cash flow budget, to calculate the tax burden, to set tasks for the sales department, etc.), revenue with VAT should be calculated.
Depending on the range of products, the number of counterparties and other business features, various methods for planning sales volume can be used: for one product, with detail by counterparties and product range, taking into account not only the final cost, but also its components (quantity, price, resource limitations) .
The easiest way to plan sales is to take the sales volume for the base period (the one that is taken as a basis, for example, last month or the same month of last year - when planning by month) and adjust it to the desired increase using formula 1.
Formula 1. Calculation of sales plan
This method is used when the company produces only one product, and sales are planned for one month or there are no seasonal fluctuations in demand throughout the year.
Take into account the sales structure.
Sales volume can be forecast in detail, by product and/or customer. Calculations are carried out according to formula 1, but data for the base period is taken in the same analytics (products or customers). Moreover, target sales growth rates will also have to be set individually for each type of product (customer). The forecast is formed for the year as a whole or by periods - but only in the absence of seasonal fluctuations in demand. When planning by client, coefficients are set depending on the state of business of the counterparties (for example, if the purchasing company is actively developing, you can plan an increase in sales), based on the agreements reached, as well as on the basis of expert assessments of merchants (see table 1. Sales plan in value terms by counterparties).
Table 1. Sales plan in value terms by counterparties
A product-by-product sales plan is formed taking into account individual sales growth rates for each product, depending on whether it is intended to increase sales or withdraw the product from the market (see Table 2. Sales plan in value terms by product).
Table 2. Sales plan in value terms by product
You can also provide a two-level structure of the sales plan:
- by counterparties (buyers) and the range of goods they purchase (see Table 3. Sales plan in value terms by counterparties and products);
- by product range and its customers (see Table 4. Sales plan in value terms by product line and customers).
This method allows you to prepare a more detailed plan. Target ratios are set taking into account both the state of relationships with customers and the company’s intentions to promote its products.
Table 3. Sales plan in value terms by contractors and products
| Counterparty | Nomenclature | |||
| LLC "Elochka" | Sweets "Breeze" | 1500,00 | 1,015 | 1522,50 |
| Candies "Grilyazh" | 1000,00 | 1,040 | 1040,00 | |
| Sweet tooth sweets | 1500,00 | 1,070 | 1605,00 | |
| Sweets "Sunny" | 1000,00 | 1,050 | 1050,00 | |
| Total | 5000,00 | 1,044 | 5217,50 | |
| LLC "Castle" | Sweets "Breeze" | 5000,00 | 1,010 | 5050,00 |
| Candies "Grilyazh" | 2000,00 | 1,040 | 2080,00 | |
| Sweet tooth sweets | 2000,00 | 1,075 | 2150,00 | |
| Sweets "Sunny" | 1000,00 | 1,015 | 1015,00 | |
| Total | 10 000,00 | 1,030 | 10 295,00 | |
| LLC "Zebra" | Sweets "Breeze" | 1000,00 | 1,110 | 1110,00 |
| Candies "Grilyazh" | 500,00 | 1,090 | 545,00 | |
| Sweet tooth sweets | 1500,00 | 1,100 | 1650,00 | |
| Sweets "Sunny" | 1000,00 | 1,040 | 1040,00 | |
| Total | 4000,00 | 1,086 | 4345,00 | |
| Kangaroo LLC | Sweets "Breeze" | 7500,00 | 1,010 | 7575,00 |
| Candies "Grilyazh" | 9500,00 | 1,040 | 9880,00 | |
| Sweet tooth sweets | 2000,00 | 1,050 | 2100,00 | |
| Sweets "Sunny" | 1000,00 | 1,030 | 1030,00 | |
| Total | 20 000,00 | 1,029 | 20 585,00 | |
| Total | 39 000,00 | 1,037 | 40 442,50 | |
Determining sales growth rates for counterparties, taking into account the products they purchase, gives slightly different results than planning only for customers or only for types of products. Taking into account the two-level sales structure, it is necessary to analyze not only the trends in relationships with the counterparty, but also the state of the market, to correlate the interests of the enterprise in promoting a particular product with the needs and capabilities of customers. This work is more difficult, but its results are more valuable for the company.
Table 4. Sales plan in value terms by product range and customers
| Nomenclature | Counterparty | Sales volume for the base period, rub. | Sales growth rate, units | Planned sales volume, rub. |
| Sweets "Breeze" | LLC "Elochka" | 1500 | 1,015 | 1522,50 |
| LLC "Castle" | 5000 | 1,010 | 5050,00 | |
| LLC "Zebra" | 1000 | 1,110 | 1110,00 | |
| Kangaroo LLC | 7500 | 1,010 | 7575,00 | |
| Total | 15 000 | 1,017 | 15 257,50 | |
| Candies "Grilyazh" | LLC "Elochka" | 1000 | 1,040 | 1040,00 |
| LLC "Castle" | 2000 | 1,040 | 2080,00 | |
| LLC "Zebra" | 500 | 1,090 | 545,00 | |
| Kangaroo LLC | 9500 | 1,040 | 9880,00 | |
| Total | 13 000 | 1,042 | 13 545,00 | |
| Sweet tooth sweets | LLC "Elochka" | 1500 | 1,070 | 1605,00 |
| LLC "Castle" | 2000 | 1,075 | 2150,00 | |
| LLC "Zebra" | 1500 | 1,100 | 1650,00 | |
| Kangaroo LLC | 2000 | 1,050 | 2100,00 | |
| Total | 7000,00 | 1,072 | 7505,00 | |
| Sweets "Sunny" | LLC "Elochka" | 1000,00 | 1,050 | 1050,00 |
| LLC "Castle" | 1000,00 | 1,015 | 1015,00 | |
| LLC "Zebra" | 1000,00 | 1,040 | 1040,00 | |
| Kangaroo LLC | 1000,00 | 1,030 | 1030,00 | |
| Total | 4000,00 | 1,034 | 4135,00 | |
| Total | 39 000,00 | 1,037 | 40 442,50 | |
Consider factors influencing sales growth
The amount of revenue is influenced by two indicators: price and sales volume in physical terms. When planning, you can take into account the desired dynamics of each of them. Various sources of growth (price and quantity) are taken into account when forming the target percentage of increase (growth) in sales (see formula 2 Calculation of the target percentage of sales growth):
Formula 2. Calculation of the target percentage of sales growth
For example, businessmen were given a task: to increase sales by 10 percent. However, it is not specified what should be the source of this growth. The goal can be formulated more clearly: to increase the quantity of goods sold by 5 percent while prices rise by 6 percent. In this case, the target sales increase will be equal to 11.3 percent ((100% + 5%) × (100% + 6%) : 100% – 100%). When using this method of sales planning, you need to take into account the two-level structure of the product sales forecast - it can be disclosed both by type of product, divided by counterparties, and vice versa (see Table 5. Sales plan taking into account price dynamics and sales volumes). If the company has a large assortment of products or a wide range of contractors, product range or clients, it is better to combine them into groups. For example, counterparties can be aggregated by region, scale of procurement, purpose of purchasing goods, payment methods, etc.
Table 5. Sales plan taking into account price dynamics and sales volumes
| Counterparty | Nomenclature | Fact | Price growth coefficient, units. | Sales volume growth rate, units. | Sales growth rate, units. | Plan | ||||
| price, rub. | Quantity, kg | Sales volume, rub. | price, rub. | Quantity, kg | Sales volume, rub. | |||||
| LLC "Elochka" | Sweets "Breeze" | 50,00 | 30,00 | 1500,00 | 1,05 | 1,06 | 1,113 | 52,50 | 31,80 | 1669,50 |
| Candies "Grilyazh" | 100,00 | 10,00 | 1000,00 | 1,03 | 1,06 | 1,092 | 103,00 | 10,60 | 1091,80 | |
| Sweet tooth sweets | 25,00 | 60,00 | 1500,00 | 1,04 | 1,07 | 1,113 | 26,00 | 64,20 | 1669,20 | |
| Sweets "Sunny" | 40,00 | 25,00 | 1000,00 | 1,05 | 1,05 | 1,103 | 42,00 | 26,25 | 1102,50 | |
| Total | – | 125,00 | 5000,00 | – | –- | – | – | 132,85 | 5533,00 | |
| LLC "Castle" | Sweets "Breeze" | 40,00 | 125,00 | 5000,00 | 1,07 | 1,09 | 1,166 | 42,80 | 136,25 | 5831,50 |
| Candies "Grilyazh" | 100,00 | 20,00 | 2000,00 | 1,04 | 1,08 | 1,123 | 104,00 | 21,60 | 2246,40 | |
| Sweet tooth sweets | 20,00 | 100,00 | 2000,00 | 1,06 | 1,05 | 1,113 | 21,20 | 105,00 | 2226,00 | |
| Sweets "Sunny" | 40,00 | 25,00 | 1000,00 | 1,10 | 1,06 | 1,166 | 44,00 | 26,50 | 1166,00 | |
| Total | – | 270,00 | 10 000,00 | – | – | – | – | 289,35 | 11 469,90 | |
| LLC "Zebra" | Sweets "Breeze" | 50,00 | 20,00 | 1000,00 | 1,08 | 1,10 | 1,188 | 54,00 | 22,00 | 1188,00 |
| Candies "Grilyazh" | 100,00 | 5,00 | 500,00 | 1,09 | 1,06 | 1,155 | 109,00 | 5,30 | 577,70 | |
| Sweet tooth sweets | 25,00 | 60,00 | 1500,00 | 1,11 | 1,10 | 1,221 | 27,75 | 66,00 | 1831,50 | |
| Sweets "Sunny" | 40,00 | 25,00 | 1000,00 | 1,06 | 1,09 | 1,155 | 42,40 | 27,25 | 1155,40 | |
| Total | – | 110,00 | 4000,00 | – | – | – | – | 120,55 | 4752,60 | |
| Kangaroo LLC | Sweets "Breeze" | 34,90 | 215,00 | 7500,00 | 1,20 | 1,10 | 1,320 | 41,88 | 236,39 | 9900,00 |
| Candies "Grilyazh" | 95,00 | 100,00 | 9500,00 | 1,09 | 1,03 | 1,123 | 103,55 | 103,00 | 10 665,65 | |
| Sweet tooth sweets | 20,00 | 100,00 | 2000,00 | 1,08 | 1,04 | 1,123 | 21,60 | 104,00 | 2246,40 | |
| Sweets "Sunny" | 40,000 | 25,00 | 1000,00 | 1,06 | 1,06 | 1,124 | 42,40 | 26,50 | 1123,60 | |
| Total | – | 440,00 | 20 000,00 | – | – | – | – | 469,89 | 23 935,65 | |
| Total | – | 944,90 | 39 000,00 | – | – | – | – | 1012,64 | 45 691,15 | |
Situation: how to forecast revenue receipts based on the sales budget
To prepare a cash flow budget, it is necessary to plan sales by month, preferably by counterparties, as this will allow you to take into account the dynamics of accounts receivable. Revenue is forecast including VAT. If the company does not apply special rates of this tax (10% and 0%), then the entire planned sales volume is multiplied by 18 percent (see table 8. Sales plan in value terms with VAT for the cash flow budget). Otherwise, you will need to group counterparties and sales by them, and then multiply the resulting sales volumes by the corresponding tax rates. When drawing up a cash flow budget, do not forget to adjust the sales plan for the growth and repayment of accounts receivable. If the terms of payment for all counterparties are the same (for example, payment within 14 calendar days after shipment), you can clarify the general sales plan for carryover receivables. Under different payment conditions, it is necessary to group buyers according to the duration of the deferment (see table 9. Adjustment of the sales plan in value terms with VAT for the cash flow budget).
Table 6. Sales plan in value terms with VAT for the cash flow budget (fragment)
| Counterparty | January | … | December | Total for the year | ||||||
| Sales growth rate, units | Planned sales volume, rub. | … | Sales volume for the same period last year, rub. | Sales growth rate, units | Planned sales volume, rub. | Sales volume for the same period last year, rub. | Sales growth rate, units | Planned sales volume, rub. | ||
| LLC "Elochka" | 500,00 | 1,05 | 525,00 | … | 400,00 | 1,05 | 420,00 | 6000,00 | 1,05 | 6300,00 |
| LLC "Castle" | 600,00 | 1,04 | 624,00 | … | 700,00 | 1,04 | 728,00 | 7800,00 | 1,04 | 8112,00 |
| LLC "Zebra" | 300,00 | 1,10 | 330,00 | … | 150,00 | 1,10 | 165,00 | 3000,00 | 1,10 | 3300,00 |
| Kangaroo LLC | 2000,00 | 1,03 | 2060,00 | … | 1500,00 | 1,03 | 1545,00 | 21 000,00 | 1,03 | 21 630,00 |
| Total | 3400,00 | – | 3539,00 | … | 2750,00 | – | 2858,00 | 37 800,00 | – | 39 342,00 |
| VAT (18%) | 612,00 | – | 637,02 | … | 495,00 | – | 514,44 | 6804,00 | – | 7081,56 |
| Total including VAT | 4012,00 | – | 4176,02 | … | 3245,00 | – | 3372,44 | 44 604,00 | – | 46 423,56 |
Table 7. Adjustment of the sales plan in value terms with VAT for the cash flow budget (fragment)
| Index | January | February | March | April | May | … |
| Accounts receivable at the beginning of the period, rub. | 30 000 | 31 250 | 27 500 | 32 750 | 36 250 | … |
| Sales volume, rub. with VAT, including: | 75 000 | 65 000 | 74 000 | 85 000 | 73 000 | … |
| with deferred payment of 14 calendar days (approximately 50% of sales are paid in the next month) | 50 000 | 45 000 | 57 000 | 60 000 | 55 000 | … |
| LLC "Elochka" | 20 000 | 25 000 | 27 000 | 30 000 | 25 000 | … |
| LLC "Castle" | 30 000 | 20 000 | 30 000 | 30 000 | 30 000 | … |
| with deferred payment of 7 calendar days (approximately 25% of sales are paid in the next month) | 25 000 | 20 000 | 17 000 | 25 000 | 18 000 | … |
| LLC "Zebra" | 10 000 | 10 000 | 10 000 | 10 000 | 10 000 | … |
| Kangaroo LLC | 15 000 | 10 000 | 7000 | 15 000 | 8000 | … |
| Planned accounts receivable, rub., including length: | 31 250 | 27 500 | 32 750 | 36 250 | 32 000 | … |
| 14 days | 25 000 | 22 500 | 28 500 | 30 000 | 27 500 | … |
| 7 days | 10 000 | 5000 | 4250 | 6250 | 4500 | … |
| Receipts taking into account the increase (repayment) of accounts receivable (accounts receivable at the beginning of the period + sales volume - planned accounts receivable) | 73 750 | 68 750 | 68 750 | 81 500 | 77 250 | … |
Situation: how to take into account marketing promotions and periods of shortage in the sales forecast
You need to plan sales based on demand, and not on the dynamics of sales volumes over past periods. After all, demand can be artificially limited by the size of supplies or stock shortages. When underestimated estimates are used for forecasts, this leads to another deficit. The situation with marketing campaigns is the opposite. For some time, demand is artificially increased by the ongoing promotion. If, when planning purchases, we focus on data for this period, then expectations will be unreasonably high.
There are several approaches to processing information during periods of marketing promotions and shortages. One way is to completely exclude periods with unreliable indicators and not take them into account when planning. However, using this approach may result in missing important information about changing sales trends or seasonality. Moreover, the volume of historical data will be significantly reduced. Therefore, it is better to use an alternative method and restore demand - clear it of uncharacteristic peaks and declines. The simplest thing is to replace these values with averages for reliable periods. A more complex option is to use retrospective forecasting to generate data for past periods of marketing campaigns and shortages.
The resulting restored indicators serve as a more accurate assessment of the real demand for products. In addition, based on this information, it is possible to calculate the lost profit from the shortage and the additional profit from the marketing campaign. Sometimes the period of decreased demand after a marketing campaign should be considered unreliable. During it, buyers purchase goods for a longer period than usual. Often a significant rise is followed by a decline in sales. By restoring demand during this period, we can calculate the negative effect of the marketing campaign. Comparison of data (actual for the period of decline in sales after the marketing campaign and taking into account restored demand during the same time) will allow us to assess the profitability of the campaign and make a decision on the advisability of its repetition. After a shortage, on the contrary, there may be an increase in sales. However, it is worth considering what products the company sells. If they can be easily purchased by buyers from other suppliers, then there will be no sharp surge in demand and the data for this period can be considered reliable.
The sales forecasting process is one of the important information tools for planning the economic activities of a manufacturing company. A variety of forecasting models have been developed and are already being used by product managers, based on historical data and analysis of the existing environment. However, to effectively use existing models in a company, it is necessary to organize automated collection of information and establish criteria for assessing the accuracy of the forecast. In addition, when forecasting product sales, managers should be sure to consider the following factors:
- consumer behavior;
- previous and planned product promotion strategies;
- actions of competitors-manufacturers;
- external environment of the enterprise, its changes.
All existing sales forecasting methods can be divided into four main groups: based on judgment; consumer-oriented; sales extrapolation; modeling.
1. Judgment based methods. This group includes methods such as studying the intentions of counterparties, role-playing games, expert assessments, the Delphi method, brainstorming, and a consolidated forecast of the sales service.
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 consumer intentions and behavior are effective if data on previous sales volumes is not available. 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 specific option of the chosen policy. However, here it is necessary to reproduce the situation in which the interaction occurs 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 expert assessment. Let's consider one of them - points method, during which, at the first stage, an expert group of specialists in this field is formed, the number of which must be equal to or greater than 9 people, the composition of the group must 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 volume. Then, by expert means, the degree of importance, or rank, of each selected parameter is established. To predict or calculate the beneficial effect and each cost element, each class of objects of the same purpose has its own point system, since the beneficial effect and cost elements are influenced by their own factors or parameters.
Important to remember!
The method of expert assessments differs significantly from studying the intentions of counterparties, since if an expert is asked to assess the dynamics of the market, he is not required to be representative, quite 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 the individual results with equal weights. The accuracy of forecasts obtained using this method can be improved by using Delphi-type procedures.
Delphi method. It is one of the varieties of the expert assessment method. Its essence lies in the iterative procedure for obtaining an integral indicator with a consistent reduction in the variance of discrepancies between expert estimates. The specificity of this method is that the generalization of the research results is carried out through 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, as well as for a longer period. Depending on the purpose of the forecast, from 10 to 150 experts can be involved in obtaining expert assessments.
Brainstorming method(or brainstorm). Like the Delphi method, it is a variation of the expert assessment 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 pre-developed scenario.
The advantage of expert methods is their relative simplicity and applicability for predicting 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 includes the moving average method, exponential smoothing and regression analysis.
Moving average method. One of the well-known methods of smoothing time series, based on the fact that random deviations in average values are mutually canceled out due to the replacement of the initial levels of the time series with an arithmetic mean 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 considered, the average is centered, i.e. is 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 series is shorter than the original one (P - 1) observations, where P- the value of the smoothing interval. The choice of smoothing interval depends on the forecasting goals.
Where t+ 1 - forecast period; t- the period preceding the forecast period (year, month, etc.);;/, + , - predicted indicator; t,_i- moving average for two periods before the forecast; P- 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 one.
When using this method, it is necessary to take into account that data for previous periods are characterized by a base value, 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. Forecasting using the exponential smoothing method 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 theoretical value of the indicator under study for the period preceding the forecast one; Ut- exponentially weighted average for the period preceding the forecast period.
When forecasting using this method, difficulties arise related to the choice of the value of the smoothing parameter a and determining the initial value t/ 0.
The exponential smoothing method is most effective when developing medium-term forecasts.
Regression analysis. This method is a generalization of the time series model. Widely used in practice by specialist managers and easily calculated using Excel. This form of extrapolation is based on regression analysis in which the time period is considered the independent variable.
4. Modeling-based methods (associative category of forecasting methods). They include the method of leading indicators and econometric models.
Leading indicators. When making forecasts in economics, 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 exist in any sector of the economy, and all of them are forced to focus on them. For example, indicators of vehicle inventories at points of sale act as leading indicators for the automotive industry. Very often, changes in the economy are considered to be changes in the level of employment of the population.
Econometric models are large-scale, multi-equation regression models. Currently, they are not particularly popular among managers due to their high cost and the desire of companies to reduce all their costs. However, with their help, it is possible to analyze the consequences of implementing various strategies, plan the dynamics of the market and 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 the explanatory factors. Some of them (for example, fashion) can cause big problems.
In general, the use of econometric models will be effective if there is a strong cause-and-effect relationship between the value being studied (for example, sales) and a set of factors, and also when the form of the relationship is known and can be estimated.
Choosing a method for making a forecast in each specific situation is a complex process. As a rule, a manager always has the opportunity to choose from several alternatives. Typically, in practice, experts use judgment-based methods to prepare short- and medium-term forecasts, and among the quantitative methods, the most popular is the moving average method.
- Fatkhutdinov R.L. Strategic marketing: textbook. M.: JSC “Business School “Intel-Sintez””, 2000. P. 198-200.
- Consolidated forecast of the sales service. Sales volume forecasts are made by sales department specialists. The advantage of this method is that sales department specialists are in close contact with sellers who know their consumers very well, their specific behavior, and the volume of product purchases. Product sales quotas are often set based on these assessments. 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 market research. To collect information about the product market under study, experts often resort to conducting focus groups and surveys of consumers at the point of sale of the product. Let us remember that a focus group is usually understood as a group of respondents, including from eight to ten potential consumers, brought together to discuss a topic in which each of them is interested to one degree or another. The discussion process follows a pre-developed scenario under the guidance of a moderator. The discussion can last up to two hours, although sometimes there is a need 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. Reviews of market conditions. The essence of this method lies in market research and surveying potential consumers of a product regarding the degree of their readiness to purchase the product being analyzed. Typically, 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 findings regarding purchase intention are then translated to the total population of the country. Given the tendency of consumers in real life to overestimate the likelihood of purchasing a product, managers often use the “but to the maximum” approach when preparing a sales forecast, i.e. Only the number of maximum marks (10 points) is counted.
- Sales extrapolation methods (time series methods). They are based on available data regarding sales volumes for pre-
- 2 URL: http://www.ekonomika-st.ru
- Right there.
- URL: http://www.ekonomika-st.ru
Forecasting sales and demand using information technology is no longer unusual. Modern IT solutions make it possible to easily process large amounts of data and calculate all kinds of statistical sales indicators - simple and exponential - on the basis of which most companies' forecasts are formed.
Sales forecasting methods
Average methods make it possible to fairly accurately predict sales of goods with regular demand and make it possible to take into account emissions and seasonal factors. However, when it comes to goods with irregular demand, these methods do not provide the required level of forecast accuracy.
It is not difficult to predict the demand for goods with irregular demand over long periods of time (quarter, half-year, year), but the forecast loses its accuracy in the case of a “week-month” planning horizon.
As a rule, given the high cost of goods with irregular demand, it is quite difficult to determine the optimal level of inventory for these items and make a decision to purchase in excess. ABC and XYZ analysis of these products also does not answer the key question.
- How many goods with irregular demand must be purchased to maintain a reasonable level of service?
Excessive inventories of expensive goods with irregular demand will lead, at best, to the “burying” of a large volume of working capital in the warehouse, which could be used for other purposes. Or to the formation of “dead stock” or illiquid stock - in the case when we are talking about product items, the collections of which are updated annually: expensive power tools, large premium household appliances, luxury items sold along with regular items.
At the same time, the lack of such goods in stock significantly reduces the possible profit from sales, since the profit from the sale of one unit of an expensive product can sometimes exceed the profit from the sale of a standard product by tens of times.
Example of sales forecasting using the BRT method
Let's assume that sales data for such a product can be presented in the following table:
Let’s say that the delivery time for a product from the moment it is ordered from the supplier until it arrives at the warehouse is four days, and the current balance in the warehouse is 1 piece. The number of items sold in a given period is 30 pieces.
- In what quantity should the goods be purchased now, taking into account the delivery time of the goods?
When calculating on the basis of average sales, we would receive an average sales value of the product in the amount of: 30 pieces / 31 days = 0.97 pieces per day, and the sales volume during delivery would be about 4 units, more precisely 0.97 pieces * 4 days = 3.9 pieces.
Having one item in stock, we can assume that we need to order three more items to replenish stock. However, sales analysis shows that selling five or more units of goods is not such an unusual situation. And if we purchase only three pieces of goods, we will not be able to satisfy demand and will deprive ourselves of sales.
- How much product should be kept in stock and what level of service can be guaranteed to customers in this situation in order to ensure that maximum demand is met without spending unnecessary money on large purchases?
The above analysis based on average sales does not answer these questions.
Therefore, to predict irregular sales, it is extremely important to use special methods that allow analysis of irregular events. Relatively recently, methods based on the so-called Bootstrapping statistics began to be developed. One such method used in the analysis of irregular and sparse series is a method called Bootstrapping Reaction Time (BRT)*.
The difference between the BRT method and the calculation of averages is in determining the most likely sales volume for the order delivery period, rather than calculating the average daily sales volume. In our case, this delivery period is four days.
- Which sales forecast option is most appropriate based on the available data?
To find the answer, let's make a table of all possible options based on the available data. To do this, we divide our series in order into reaction periods (order delivery times): first from 1 to 4 days, then from 2 to 5, then from 3 to 6, etc. - a total of 28 possible options.
In the far right column we received many options for how much of a product can be sold during a selected period of time (four days) - we got a range from 0 to 11 pieces. How can we understand which of these values best meets our requirements? To do this, let's create a frequency histogram - it will show how often one or another value occurs in the sample:
- How many clients is our company ready to provide unconditional availability of goods?
By “unconditional availability” we mean the following situation: if on average they buy 10 pieces daily, but there was a case that someone bought 100 pieces, then “unconditional availability” means that we should have 100 in stock pieces of goods.
High product availability means you can provide a higher level of service to your customers, but you also have a large amount of stock in your warehouse.
Lack of goods in stock - a low level of availability - means that we purchase fewer goods for future use, but we also reduce the quality of service, not being able to ship the goods to the client on time.
- What percentage of customers can we serve - sell goods, discarding the factor of stock availability?
As a rule, this value fluctuates around 80-91%. For our example, we will focus on the availability level - 80%. We “discard” the remaining clients - 20%, believing that for them we are not ready to store large stocks of goods in the warehouse and will not be taken into account in the purchasing plan.
What do these numbers mean for our analysis? This means that, based on our histogram, we need to determine the maximum value of sales volume in such a way that the total frequency of demand for smaller sales volumes is as close as possible to our chosen level of availability.
In managerial logic, this can be interpreted as follows: we must select the possible maximum demand that will arise from 80 out of 100 of our customers during the selected reaction time (order delivery time).
For our sample, this value is 8 pieces, which would cover the requirement of 21 out of 28 possible outcomes (if we had chosen an availability level of 70/10, then this would be a value of 5 pieces, which would cover 20 possible outcomes out of 28 possible).
In management logic, the value we found of 8 pieces can be interpreted as follows: when serving 8 out of 10 clients, within 4 days they will buy a total of less than 8 pieces of goods, and the purchase will be equal to 8 - 1 = 7 pieces. This result differs significantly from the value obtained by calculating the “simple average”.
Thus, the BRT method provides more accurate and reasonable analytics for goods that should be available to customers, even if they are purchased quite rarely, but with some consistency.
Whether you need to forecast expenses for the next year or project expected results for a series in an exponential experiment, you can use Microsoft Office Excel to automatically create future values based on existing data, or to automatically create extrapolations of values based on linear calculations and growth trends.
You can fill a series of values that correspond to a simple linear trend or an exponential fit using the fill handle command or row. To enhance complex and nonlinear data, you can use worksheet functions or the regression analysis tool in the Analysis Package add-in.
Automatic series completion for linear best trend
In a linear series, the step value, or the difference between the first and next value in the series, is added to the starting value and then added to each subsequent value.
To complete the series for the linear best trend, follow these steps:
Drag the fill handle in the direction you want, increasing the values or decreasing the values.
Advice: row(tab " home", Group " Editing", button " Fill ").
Automatic row completion for exponential growth
In a growth series, the initial value is multiplied by the step value to obtain the next value in the series. The final product and each subsequent product are then multiplied by the desired value.
To complete a series for an exponential trend, follow these steps:
Select at least two cells containing the starting values for the trend.
If you want to improve the accuracy of the trend cycle, select additional starting values.
Hold down the right mouse button, drag the fill handle in the direction you want, increasing the values or decreasing the values, release the mouse button, and then select a command growth trend to the context menu.
For example, if the selected starting values in cells C1:E1 are 3, 5, and 8, drag the fill handle to the right to fill with increasing trend values, or drag it to the left to fill with decreasing values.
Advice: To manually control row creation or fill it using the keyboard, click row(tab " home", Group " Editing", button " Fill ").
Manually filling in linear trend or trend values
When pressing the command row You can manually configure how to create a linear trend or exponential trend, and then enter the values using the keyboard.
In a linear series, the initial values are applied to a least squares algorithm (y = mx + b) to create the series.
In a growth series, the initial values are applied to the exponential curve algorithm (y = b * m^x) to create the series.
In any case, the step value is not taken into account. The series created is equivalent to the values returned by the trend or growth function.
To enter values manually, follow these steps:
To fill the entire row down the sheet, click columns.
To fill a row on a worksheet, click lines.
Select the cell in which you want to start the row. The cell must contain the first value in the series.
When you type the command row, the resulting series replaces the original highlighted values. If you want to keep the original values, copy them to another row or column, and then create a series by highlighting the values you copy.
On the tab home in Group Editing click the button Fill and select Progression.
Do one of the following:
In field step Enter the value by which you want to add the row.
In chapter type select an option linear or height.
In field stop value Enter the value at which you want to stop the series.
Note: If there are multiple starting values in a series and you want Excel to create a trend, select the checkbox trend .
Calculate trends by adding a trend line to a chart
If you have data that you want to predict a trend for, you can create a trend line on a chart. For example, if you have a chart in Excel that displays sales data for the first few months of the year, you can add a trend line to the chart that shows the overall sales trend (increasing or decreasing) or displays planned trends for the months ahead.
This procedure assumes that you have already created a chart from existing data. If you haven't already done so, see Create a chart.
If you selected the option polynomial, enter in the field order the highest value for the independent variable.
If you have chosen moving average, enter in the field period the number of periods that will be used to calculate the moving average.
Click the chart.
Click the data series to which you want to add a trendline or moving average.
On the tab Layout in Group analysis click the button trend line, and then select the type of regression trend line or moving average you want.
To adjust settings and format a regression trendline or moving average, right-click the trendline and select Trend line format .
Select the trendline, lines, and effects options you want.
Notes:
In field " based on series» lists all the data series in the chart that support the trend lines. To add a trendline to another series, click its name in the box, and then choose the options you want.
If you add a moving average to a scatter chart, the moving average will be based on the order of the x values displayed in the chart. You may need to sort the x values before adding the moving average to get the desired result.
Perform regression analysis using the Analysis Pack add-in
If you need to perform more complex regression analysis, including calculating and plotting residuals, you can use the Regression Analysis tool in the Analysis Package add-in. For more information, see Download the analysis package.
In Excel for the web, you can calculate values in a series using worksheet functions, or click and drag a fill handle to create a linear trend of numbers. But you can't create a rising trend with a fill handle.
Here's how to use a fill handle to create a linear number trend in Excel for the web.
Project values using the worksheet function
Using the FORECAST function The FORECAST function calculates or predicts a future value using existing values. The predicted value is the y value corresponding to the given x value. The x and y values are known; the new value is predicted using linear regression. This feature can be used to predict future sales, inventory needs, and consumer trends.
Using the trend or growth function The trend and growth functions can clip future values y, which extend a straight line or exponential curve that better describes existing data. Additionally, they can only return values y by known values x for the best line or curve size. To display a line or curve that describes existing data, use existing values x And y, returned by the trend or growth function.
Using the LINEST function or LINEST function You can use the LINEST or LINEST function to calculate a straight line or exponential curve with existing data. The LINEST function and the LINEST function return various regression statistics, including slope and best-fit line intercept.
Function | Description |
|---|---|
|
Project values |
|
|
Project values that follow a straight trend line |
|
|
Note: This page has been automatically translated and may contain inaccuracies and grammatical errors. It is important to us that this article is useful to you. Was the information useful? For convenience also (in English). |
Completed by teacher Povolotskaya M
What should a retired woman do to earn money?
What is the punishment for illegal business activities?
VET engineer: job description (sample) Regulations on VET at a mining enterprise
Arkady Rudoy, UGMK: “now all manufacturers are banging on one door