Conditional formatting (5)
Lists and ranges (5)
Macros (VBA procedures) (63)
Miscellaneous (39)
Excel bugs and glitches (3)

Sales forecast in Excel

Download the file used in the video tutorial:

Did the article help? Share the link with your friends! Video lessons

("Bottom bar":("textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24," textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance" :30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600 ,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500," textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; width:100%; height:100% ; background-color:#333333; opacity:0.6; filter:a lpha(opacity=60);","titlecss":"display:block; position:relative; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; position:relative; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff; margin-top:8px;","buttoncss":"display:block; position:relative; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive": "","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40))

What will be the profit of the company this year? What will the company's balance sheet look like? What will happen to the money? The answers to these and many other CEO questions can be found in the company's three main budgets: the forecast balance sheet, the income and expense budget, and the cash flow budget. They repeat the usual forms financial statements with the only difference that they contain planned data, and not the actual results of the company's work. It is possible to draw up such budgets quickly using the principle of double entry.

The main idea of ​​such forecasting is that each item of the operating plan, whether it is the purchase of goods from the supply plan or the cost of goods sold from the sales plan, is considered as a business transaction and is planned twice.

How to make a forecast balance

A quick way to predict what revenue will be at the end of the year. As initial data for compiling the forecast balance, the operating budgets of all departments and the opening balance (balances on items at the beginning of the planning period) will be needed. The opening balance sheet items determine the main items of the forecast balance sheet. In the asset - cash, accounts receivable, inventory, etc. In the liability - accounts payable, payroll, retained earnings, etc. Operating budgets and the data contained in them set a list of items of the second level (subitems) of the forecast balance.

Further everything is quite simple. Consistently, one by one, it will be necessary to analyze articles from operating budgets, determining what the essence of this or that indicator appears in the plans of each unit, what kind of business transaction is behind it, and in the calculation of which articles of the forecast balance it participates. And accordingly, for each item from the operating budgets, several corresponding sub-items are assigned to the items of the forecast balance. For example, the purchase of goods on a deferred basis is reflected in the sub-item "Receipt of goods from the supplier" of the article " Inventory". That is, active. Also, this operation is shown in the sub-item "Accrual of debt for goods" of the article "Accounts payable". That is, passive.

Having sorted through all the contents of operating budgets, the financial service will receive a complete list of sub-items of the forecast balance.

How to work with planned figures

In the forecast balance, first, balances are entered for items at the beginning of the period. Data source - opening balance. For example, the amount of 120 thousand rubles, that is, the balance under the item "Cash", is copied from the opening balance sheet into the form - in the column "January", section of the article "Cash", in the line "Balance at the beginning" (see table) . Similarly, the balances are filled in for the rest of the consolidated items.

Now we return again to operational plans divisions. Let's start with a sales budget compiled by the commercial department of a notional wholesale and distribution company. retail. Suppose the first article in it is "Retail Sales". For January, the plan for them is 1600 thousand rubles. In fact, this is information about the planned shipments of goods in selling prices to retail outlets. At retail, the company supplies goods on a deferred payment basis. Obviously, these future business transactions will affect items in the forecast balance sheet such as customer receivables and ultimately profits.

Accordingly, the amount retail sales is reflected in the sub-item “Accrual of debt retail outlets" of the article "Accounts receivable" in the asset. And in liabilities under the sub-item "Retail revenue" to the item "Profit of the current year". Similarly, all data from operating budgets is somehow reflected in the forecast balance sheet. The forecast balance can easily be reduced to a short, that is, traditional, form, by "folding" all the sub-items. And the analytics for the item of the forecast balance "Profit of the current year" is nothing but the budget of the company's income and expenses.

Forecast balance of an average trading company (extraction), thousand rubles

Articles	Jan.	Feb.		Nov.	dec.
ASSETS	1 701	2 110		7 652	6 717
Cash:
balance at the beginning	120	1030		1737	2652
receipts from retail outlets	100	1 600		1100	2000
Accounts receivable from buyers:
balance at the beginning	100	200		3000	2000
retail outlets debt	1600	1600		1100	2000
payment for retail outlets	1600	1600		1100	2000
wholesale customer debt	200	200		2000	2000
payment wholesale buyers	100	200		3000	2000
balance at the end	200	200		2 000	2 000
LIABILITY	1701	2110		7652	6 72
Accounts payable:
balance at the beginning	200	700		950	1450
arrears for goods	700	930		2100	1000
payment to the supplier for the goods	200	700		1330	2100
Retained net income:
balance at the beginning	220	220		220	220
balance at the end	220	220		220	220
Profit of the current year:
balance at the beginning		283		1120	1149
retail revenue	1600	1600		1100	2000
retail cost	480	480		330	600
wholesale revenue	200	200		2000	2000
wholesale cost	100	100		1000	1000
balance at the end	283	564		1149	1349

A company can plan only those indicators that it is able to manage, for example, most of the costs. The rest of the indicators - demand, risks, competitors' actions - can only be predicted. part is not sufficiently detailed and often not substantiated. Right choice methods for forecasting the company's income and taking into account all significant factors affecting the value of the forecast, will make it more accurate.

A company needs a revenue forecast not to determine future financial performance, but to develop a strategy and tactics for the forecast period. It must be remembered that the forecast is not an end in itself. Therefore, forecasting methods should not be particularly accurate, but should only correctly reflect the specifics of the business and correctly indicate the direction management decisions accepted by the company.

Forecasting methods

All forecasting methods used in the analysis can be divided into expert and statistical. Let's consider these methods in more detail.

Expert Methods

When using expert methods, a group of specialists (experts) is surveyed. As a rule, top managers act as experts inside companies - general, commercial, financial director, production director, etc. Consultants, financial analysts, marketers involved in market research, and other specialists can act as external experts.

Such forecasting methods are used by almost all companies, but they are more suitable for assessing the development of an unstable market, which is difficult to describe using mathematical formulas and dependencies, as well as for long-term forecasting. The success of the application of expert methods depends on the number and qualifications of experts who can be involved in the work.

Statistical Methods

If the market is relatively predictable and the company has data on the previous dynamics of the predicted indicator or on the dynamics of the factors that influence it, then it is advisable to use statistical methods for short- or medium-term forecasting. These methods are based on the assumption that in the future the analyzed indicator will change according to the same laws as in the past. Statistical methods of varying complexity are used by almost all market-oriented companies, using either Excel or specialized statistical programs (SPSS, Statistica, etc.).

Let's consider two statistical methods - building a trend and the method of chain indices.

Building a trend. Most of the statistical forecasting methods are based on the construction of a trend, that is, a mathematical equation that describes the behavior of the predicted indicator. The most common example of such an equation is the dependence of sales on time. The dynamics of the indicator can be described as a straight line (linear trend) or a curve ( non-linear trend) When constructing the line equations, the following rules should be followed:

• if you only need to define general trend or compare the growth rates of various indicators, you can limit yourself to a linear trend;
• if sales grow "like an avalanche" (for example, when a product becomes fashionable), an exponential trend is used. However, this method can only be used for short-term forecasts: such a rapid growth in most cases cannot be long-term, since in a competitive industry, an increase in demand will cause an increase in supply from competing companies. Therefore, already in the next planning period, the trend will have to be revised;
• if seasonal fluctuations are observed in sales volume (for example, by seasons), a polynomial trend is used;
• if sales first grew, and then stabilized at a certain level, or, conversely, were first high and then decreased, then a new stable level is determined using a logarithmic trend.

In addition to time, the trendline equation can include previous predicted values ​​(autoregression), averaged values ​​(moving average method), etc. Sometimes the predicted value is not homogeneous. For example, sales volume construction company may depend on the amount of advertising, the volume of mortgage lending, and even on GDP. Then the trend equation includes the values ​​of the quantities that affect it (possibly with a time shift) with some coefficients (multiple regression).

Method of chain indices. If it is necessary to take into account seasonal fluctuations in the forecast, the method of chain indices can be applied. To do this, first, chain sales indices are calculated (the ratio of sales volume of each subsequent period to the previous one) and the average value of this index for each period (month) over several years is found. Then the sales volume of the last reporting period is multiplied by the index of the next (planned). The resulting value is the forecast for the first forecast period. To calculate the forecast for the second and subsequent periods, the procedure is similar. The chain index method can be combined with other forecasting methods.

Forecasting stages

To make a revenue forecast, it is necessary to determine the future values ​​of the company's sales volume in physical terms and evaluate changes in pricing policy. Their product will give a forecast of the company's income in value terms.

Consideration of factors

Forecasts of prices and physical volume of sales are recommended to be compiled separately, since the dynamics of these indicators may be different. Both of these components depend on a large number of factors, the values ​​of which can vary significantly (demographic conditions in the region, the dynamics of household incomes, the state of industries in which substitute goods are produced, etc.). Accordingly, to begin with, it is necessary to identify factors that can affect the value of the forecast, that is, relevant factors. As a rule, analysts focus on the forecast of external factors that the company cannot influence. But do not forget about internal factors such as advertising policy, changes in assortment, opening of new offices, etc., as they can have a significant impact on the value of the forecast. It is also necessary to determine how each factor affects the predicted indicator.

Building predictive values

Then you need to understand how the relevant factors will change over time. This can be done using one of the forecasting methods described above. It should be noted that sometimes factors can change abruptly. To reflect such changes, correction factors are usually used, obtained by analyzing statistical data (seasonality) and information about expected changes (reports from the State Statistics Committee of Russia, expert opinions). The values ​​of the correction factors must be economically justified. The forecast value of the factor is corrected by multiplying the forecasts obtained using the trend by correction factors. After the forecast values ​​of all factors affecting the company's income are obtained, the pessimistic, optimistic and most probable values ​​of sales volume and prices are calculated.

Personal experience Sergey Pustovalov, CFO of Talosto (St. Petersburg)

We make a forecast of the company's development for a period of five years, broken down by years and business areas, and calculate it for the pessimistic, optimistic and most realistic scenarios. The main factors influencing the financial forecast of the company are the gross product by sales markets, investments in advertising, the actions of competitors, and the growth of market segments. The target indicator for us is the market share of the company - it must grow faster than the market, or at least along with it. Thus, under the pessimistic scenario, growth target segments market is considered to be minimal and amounts to 15% per year.

To determine the dynamics of the external environment, we use not only statistical data for past periods, but also forecasts from the State Statistics Committee of Russia, the Ministry of Economic Development and Trade, data from news agencies, forecasts and results of industry monitoring prepared for us by marketing agencies, as well as reviews of investment funds. The final value of revenue is obtained by adjusting the baseline forecast by a factor (or factors) that takes into account the impact of these indicators.

Example of Forecasting Company Revenue

Santechnika LLC is a rapidly growing company specializing in wholesale trade finishing materials and economy plumbing. The factors influencing the volume of sales are shown in the figure. Let's consider building a forecast for one of the main factors - sales of sanitary ware for new residential buildings for 2005-2006.

The most important factor influencing the volume of sanitary ware sales is the volume of housing construction. For several years, the market has seen an intensive growth in construction volumes and prices for apartments, which has slowed down in the last two years and now shows a tendency to stabilize. As a result of forecasting by statistical methods, it was found that the dependence of the volume of housing construction on time is best described by a logarithmic trend. To build a basic forecast, the following equation was obtained:

Y=14.762Ln(x) + 18.313,

where Y is the volume of housing construction;
х - time (by years, 1998 is taken as х = 1);
14.762 and 18.313 - calculated coefficients.

Real estate market analysts expect significant changes in the market over the forecast years. Some experts expect a sharp increase in demand for new housing and growth in sales by 20% per year from the baseline forecast (optimistic forecast). Other experts believe that the market is "overheated", so in 2005 investors who bought housing for speculative purposes will start selling it, which will lead to a drop in sales of new apartments by 40%. Then, in their opinion, the market will stabilize and in 2006 it will grow by 20% from the level of 2005 (a pessimistic scenario). In turn, banking specialists believe that the implementation state program for the development of mortgages will contribute to an increase in housing construction by 3% annually. Other external factors will remain unchanged. All received forecasts are summarized in Table. one.

Now it is necessary to determine how the forecast will change under the influence of internal factors. In order to maintain and increase the market share in the forecast period, it is planned to expand the range of goods sold at the expense of modern expensive sanitary ware. Demand for such sanitary ware is constantly growing, so due to the change in the assortment, the company plans to increase its sales by 20% (optimistic forecast), in the worst case - by 10% (pessimistic). Now we can summarize the effect of internal and external factors for 2005 (see Table 2). It is easy to compile a similar table for 2006 as well.

Although the sales forecasts for new home sanitary ware vary greatly (from -28.2% to +43.6%), they provide important information to the firm's management. Managers can see how sales volumes will change if the housing boom continues or if prices for "speculative" housing start to fall. Having such forecasts, they can develop scenarios for the company's actions in a given situation and determine ways to achieve a positive financial result in any eventuality.

Scenarios for the future

For each income forecast option, it is necessary to develop an appropriate cost scenario. This is done using the company's existing budget model 1 . Then the projected income and planned expenses of the company are brought together and four boundary development options are obtained (see Table 3). For each option, the main financial budgets are built - BDDS, BDR and the forecast balance. Analysis of the results obtained from the point of view of the company's strategy and its financial performance allows us to develop an action plan for each of the development scenarios, taking into account their inherent risks. As a result, the company should receive the most probable scenario of its development and a developed set of measures and actions in case the actual indicators deviate from their predicted values.

Personal experience

Denis Ivanov, CEO CJSC Financial Reserve (Moscow)

In our company, the forecast of future income every six months is made by the heads of revenue-generating departments. Usually, future result their work is expressed by one value, but the error error (range of values) is determined. The planning and economic department draws up a master plan and indicates the boundary values ​​​​of possible deviations. If there is a possibility of an event that significantly affects the forecast value, then the planning and economic department determines the values ​​of income and expenses for such a scenario.

These scenarios are processed in the planning and economic department, which adds a range of future exchange rates and interest rates to the forecast data. Then, taking into account future expenses, a payment schedule is determined, after which the accounting department makes a forecast for net profit and develops tax planning activities.

For the timely identification of deviations from the forecast, it is necessary to develop a set of benchmarks. The selection of indicators that will be used to analyze the execution of the forecast begins even during the forecasting and planning of revenue and expenses. It is then that the key indicators (market share, prices, physical volume of sales, labor productivity, etc.) are determined, on which the forecast depends, and their initial values. In the number of benchmarks, you must also add parameters on which the financial result will depend (receivables turnover, profitability, etc.). Tracking changes to selected key indicators, managers can see which forecast and with what deviations is implemented in practice, and make decisions in accordance with previously developed scenarios.

Personal experience

Oleg Frakin, Financial Director of Wine World Holding LLC (Moscow)

For forecasting to be effective, forecast performance data must be used to make decisions in the current situation, and not based on the results of the past. At the same time, it is impossible to force departments to quickly process expenses and incomes, for example, in Excel. This requires a specialized program that allows you to collect, plan and analyze data, conduct plan-fact analysis until the end of the reporting period. Otherwise, there will be no efficiency and any forecast will lose its meaning, since the opportunities for its implementation will be missed.

In companies for which most of the costs are fixed (like ours), there is another "brake" for operational management within the forecast. If the sales plan is not met, I cannot, for example, fire half the staff in order to reduce fixed costs. You can only regulate variable costs. Partially, this problem can be solved by converting fixed costs into variables, that is, using outsourcing, for example, in the IT service, but this is not always possible. As a result, the work scenarios for our company are quite obvious: if we earned 20% less than planned for the period, then I can save a maximum of 5% of the pre-approved amount.

Forecast errors

Evaluation of one development option

The most common mistake is to evaluate one development option. Majority Analysts Russian companies do not consider it necessary to calculate several options for the development of events. In the best case, planning is carried out by product groups (range), regions or distribution channels, when only one set of forecast parameters (price and volume) is calculated for each direction, which, as a rule, is underestimated “for safety net”. Subsequently, when assessing the sensitivity of the financial model to the input parameters, financiers can analyze changes in the company's key financial indicators in relation to these parameters. However, mere analysis is not enough - a plan of action is needed. Otherwise, the company may not be ready for a significant excess (decrease) in sales compared to the forecast.

Most often, when forecasting, the extrapolation method is used, that is, determining the relationship between model parameters based on data from past periods and transferring these dependencies to future ones. For example, a company forecasts sales growth of 10% per year for five years in advance, while not taking into account current market trends and possible economic events (for example, Russia's accession to the WTO). As a result, such forecasts may turn out to be useless in a year. Therefore, extrapolation is suitable only as a tool for "harvesting" predictive values.

Personal experience

Evgeny Dubinin, Deputy Financial Director of the construction company "LEK-Moscow"

It is wrong to use only mathematical methods in forecasting, even the most complex ones, because in this case the economic meaning of events is not taken into account. If the forecast of the US dollar in early 1998 had been made by a mathematician who did not understand anything about economics, he would have built a dependence that did not take into account the current situation with the country's domestic debt, and could not have predicted the devaluation of the ruble.

Underestimating or ignoring factors

This error appears when trying to take into account future changes in the external and internal environment of the company. Relevant factors are often defined in a simplistic way, underestimating both their individual and cumulative influence. For example, for real estate, the relevant factors will be not only the growth of personal income and lower mortgage interest rates, but also the demographic situation.

Incomplete accounting of proposed changes

The proposed changes should be adequately taken into account both in the revenue and expenditure parts of the budgets. Otherwise, a situation may arise when additional income will be planned without taking into account additional expenses. Most often, this applies to semi-fixed costs: salaries of management personnel, advertising, communications, etc. There is also the opposite option, when the company plans to cut costs, believing that this will not affect income in any way.

The desire to wishful thinking

Many people, due to their own psychological characteristics, do not want to assess the situation realistically. Executives often prefer a positive outlook on the development of their company's business. This leads to the fact that the company is not ready to withstand negative trends in the external environment.

So how do you predict earnings? The answer to this question is quite clear: it must be done in such a way that the company's management can make informed, rational decisions about the company's strategy and tactics in various areas based on it. Do not chase complex mathematical methods, trying to guess the future. Income forecast needs to be made adequate economic processes company and the conditions of the market in which it operates.

You can make an approximate forecast of the profit of the enterprise yourself. How? What for? The impact of the profit forecast on the investment attractiveness of the enterprise (10+)

Classical (fundamental) analysis - Profit forecast

Let's add the monthly fund to the amount received wages, based on the consideration that in the event of a deterioration in the situation, it may be necessary to reduce up to a third of the staff, paying them three salaries. Let's add other obligations known to us (sponsorship of a football team and other strange projects), which cannot be quickly eliminated. We will receive the full annual amount of obligations. Let's compare it with the projected annual profit. If liabilities are less than profit, we have a very good company in front of us.

Is it possible to buy shares of companies with worse performance? Yes, but cheap. These are risky investments, there should be few of them in the portfolio. In addition, you need to understand how the company will get out of this situation. You need to be confident that the company can handle it. If in doubt, it is better to look for another investment object.

Too low debt also does not indicate the effectiveness of the company. In conditions of virtually zero interest rates, the ability to work correctly with borrowed capital absolutely necessary.

Profit Forecast

We will estimate profit without revaluation. The revaluation simply reflects the increase in the nominal value of the company's fixed assets, usually caused by inflation. Fortunately, most of our companies do not revalue fixed assets, because, roughly speaking, the revaluation, if it is positive, leads to the need to pay income tax on this amount.

Let's take last year's income statement as a basis. If there is an overestimation in it, then subtract it from the final indicator. Let's make a profit forecast for the next year, adjusting the income statement of the last year and taking into account:

• commodity price forecasts,
• price forecasts for finished products,
• company management forecasts for changes in production volumes,
• upcoming tax changes.

Let's determine the ratio of profit for the coming year to the current capitalization. This is the amount that the company will earn you per year per ruble of investment. If it suits you and you also have no complaints about the management system (see above), then the asset is a clear candidate for purchase.

Profit distribution. Theoretically, the distribution of profits (in the sense of what part will go to dividends and what will be reinvested) does not affect the attractiveness of the asset, since the reinvested profit increases the fair price of the share, which provides shareholders with the necessary income. Moreover, dividends are subject to taxes, and the price growth of shares prior to their sale is not taxed. However, there is a circumstance to which attention must be paid.

How effectively is profit invested? If the board of directors proposes to shareholders not to distribute part of the profit and reinvest it in the business, then information must be provided on in which projects this profit will be invested. If there is no specific investment plan, then, most likely, the profits are going to be stolen.

When comparing the calculated profit from investing in a company with other alternative investments (bank deposits), one should keep in mind inflation. The resulting profit is cleared of inflation, since we did not take into account the revaluation of fixed assets in the calculation. This means that in addition to the profit received, your principal amount of investments will grow on average at the rate of inflation or more, while the funds on a bank deposit only generate income, but the principal amount of investments depreciates. Thus, quite roughly, to compare investments in bank deposits and investments in equity assets, it is necessary to add the projected percentage inflation to the projected profit from an equity asset in percent. And compare the amount already received with the deposit. For example, a company will provide a yield (profit / capitalization) of 10% per annum, inflation according to government forecasts of 8%, in total, for comparison with a deposit, you need to take a rate of 18% per annum.

Unfortunately, errors occur periodically in articles, they are corrected, articles are supplemented, developed, new ones are being prepared. Subscribe to the news to stay informed.

If something is not clear, be sure to ask!
Ask a Question. Article discussion.

More articles

Industry, index funds, mass investors, speculators - technical...
Features of industry investors, funds, mass investor, speculators - those...

We invest in understandable simple projects. We analyze attachment objects. ...
A good investment in clear and simple projects. Minimum intermediaries. Availability of pl...

Trading robots, algorithmic strategies - feedback, practical experience...
Trading robot - my practical experience of earning. Buying and testing trade...

Algorithmic investment strategies, trading robots, profitability,...
Algorithmic strategies, trading robots - their capabilities and limitations....

Investments, investments. Bank deposits, deposits. Features, Benefits...
Features of investments, investments in bank deposits. Benefit, real profitable...

Buying a car on credit. Is it profitable? How to evaluate. We are planning to buy...
On credit or cash? What is the best way to buy a car? We are planning to buy a...

Additional income, earnings. Credit card. Making money with plastic...
How, abandoning cash payments, replenish your budget with tens of thousands of rubles ...

Buy, spend too much, overpay. Empty unreasonable purchases, tr...
How to buy what you need at the right price? Save money smartly...

The most important issue of managing the process of profit formation is the planning of profit and other financial results taking into account the findings economic analysis. main goal when planning is the maximization of income, which allows you to provide financing for a larger amount of the needs of the enterprise in its development. In this case, it is important to proceed from the amount of net profit. The task of maximizing the net profit of an enterprise is closely related to optimizing the amount of taxes paid within the framework of the current legislation, and preventing unproductive payments.

Profit planning is an integral part financial planning and an important area of ​​financial and economic work at the enterprise. Profit planning is carried out separately for all types of enterprise activities. This not only facilitates planning, but also matters for the expected amount of income tax, since some activities are not subject to income tax, while others are subject to higher rates. In the process of developing profit plans, it is important not only to take into account all the factors affecting the magnitude of possible financial results, but also, having considered options production program, choose the one that provides the maximum profit.

In the process of developing profit forecasts, the following methods can be used:

Method for determining return on invested capital,

Technical and economic calculations (normative),

Settlement and analytical,

direct account,

Economic and statistical (moving average method, simple regression equation, double average method, extreme points method, production function, logarithmic),

Economic and mathematical (multiple correlation-regression model),

Method of optimization models,

A method based on the use of marginal income in calculations.

At the heart of any of the above methods of profit planning is the relationship between profit, sales volume and distribution costs. The calculation of the rate of return on invested capital is based on the relationship between the turnover of productive capital and return on sales. The amount of profit (minimum, maximum, required) as an objective function depends on the strategy chosen by the enterprise.

As the market relations the main method of forecasting becomes the method of providing an appropriate return on invested capital. When forecasting using this method, the problem of maintaining the achieved level of return on invested capital and increasing it (if this is provided for by the adopted strategy) is solved. Means for increasing the level of profitability on invested capital can be an increase in the volume of sales of goods due to the introduction of additional retail space, finding additional sources of commodity resources, accelerating the turnover, providing additional services, reasonable tactics in the field of pricing and management of financial resources, reducing distribution costs.

The initial data for forecasting profits using this method are: information on the amount of invested capital (according to the latest balance sheet data); projected level of return on invested capital, staffing for the enterprise, the projected values ​​of the wage fund, all costs and material costs, the current tax rates, etc.

When determining the minimum or required amount of profit as a target function, the enterprise proceeds from the forecast value of its capital: forecasts of the interest rate of banks and the rate of return on capital (average return on capital).

The predicted amount of capital depends on its composition and factors (sources) that contribute to its growth (decrease). These can be bank loans, the issuance of bonds and their value, the sale of shares and their price, the rate of inflation.

The amount of necessary profit at the level of self-financing is determined based on the needs of the enterprise in financing measures for its production and social development, fulfilling obligations to the state and creating appropriate funds (risk, reserve, fund for paying dividends, etc.).

The need of the enterprise for resources to finance activities related to production and social development enterprises are defined as follows.

First, the share of all taxes and obligatory payments from profit in its total value is determined, which has developed in the reporting period. In cases where the taxation procedure changes in the planning period, the amount of taxes must be clarified taking into account these changes. Then the amount of necessary profit (Mon) is calculated to meet the needs of the enterprise in financial resources and the creation of appropriate funds:

Mon \u003d (K x NPK) / (100 - Sp) (4)

where Sp is the average level of taxes and obligatory payments as a percentage of the balance sheet profit, K is the capital of the enterprise, rubles, NPK is the rate of return on capital, %.

The calculation of the break-even point (self-sufficiency), the point of critical sales volume, and the self-financing ratio is of great help in in-depth understanding of the essence and quantification of the target profit, in choosing the optimal planned solution for profit and profitability.

The optimal target profit is the amount of profit that implies full and effective financing of all its on-farm needs for funds and allows, with stable rates of deductions from profits, to participate in the formation of state and local budget revenues.

Profit underpinning trade allowance, in terms of its safety margins, it should be sufficient for 2-3 years in advance to compensate for the need for capital investments (taking into account the funds of the depreciation fund), replenish the growth of own working capital, for the formation of appropriate funds.

The method of direct counting is used, as a rule, with a small assortment of products. Its essence lies in the fact that profit is defined as the difference between the proceeds from the sale of products (net of taxes on proceeds) at the appropriate prices and its full cost of sales.

The direct counting method is simple and accessible, but it does not allow one to identify the influence of individual factors on the planned profit, and with a large range of products, it is very laborious.

The analytical method of profit formation is used with a large assortment of products. The advantage of this method is that it allows you to determine the influence of individual factors on the planned profit. With the analytical method, profit is determined not for each type of product manufactured in the coming year, but for all comparable products as a whole. The calculation of profit by the analytical method includes the following steps:

Definition basic level profitability as a quotient of dividing the profit of the reporting year by the full cost of comparable marketable products of the same period,

Calculation of the volume of marketable products in the planning period at the cost of the reporting year and determination of profit on marketable products, based on the basic level of profitability,

Accounting for the impact on the planned profit of various factors: reducing (increasing) the cost of comparable products, improving its quality and grade, changing the assortment, prices, etc.,

Determining the profit of the planned year, taking into account the influence of one factor - changes in the volume of comparable marketable products - based on the basic level of profitability and the planned volume of marketable products at the cost of the reporting year,

Assessment of the impact on the planned profit of changes in the cost, prices, assortment, grade, on the basis of which the planned profit and the planned level of profitability are adjusted. When forming a profit, it is also necessary to take into account the profit that can be received from the sale of the balance of finished products in the warehouse, the profit in goods shipped.

To generate profit from other sales, from non-operating transactions, you can use both traditional and special methods (expert, statistical, economic and mathematical, etc.).

One of the most effective tools operating profit management is a marginal analysis, which is based on the study of the relationship between the three groups of the most important economic indicators: costs - the volume of production (sales) of products - profits, and forecasting the value of each of these indicators for a given value of others. On the basis of marginal analysis, various methods of generating profit from sales are based. One of these methods is the profit generation method based on CVP - analysis (analysis of the relationship between costs, sales volume and profit), which allows you to identify the role of individual factors in the formation of profit from sales and ensure effective management this process in the enterprise. This method is based on comparing the proceeds from the sale of products with the total amount of costs, subdivided depending on their response to changes in production volume into variable and fixed costs.

Margin analysis methods are used to:

Calculation of the break-even point and profit planning according to the ratio "costs-output-profit" (costs-volume-profit),

Profit planning based on the effect of operating leverage (lever),

Profit planning based on marginal (additional) costs and marginal revenue.

The balance method of planning is characterized by the establishment of material and cost proportions in indicators. The method involves the use of mutually balanced calculations (tables), in one part of which resources are indicated, and in the other - the directions of their use. Correct Definition resources will mean a reasonable direction of their use according to existing needs. In planning, such balances are often used as: a) natural (material); b) cost; c) labor; d) intersectoral, etc. So, the company’s turnover plan necessarily requires the calculation of its commodity supply plan, which is carried out using the balance method, and the balance of cash income and expenses of the population of the region - determining the sources of their receipt of funds and directions for their spending, which is also performed using the balance method .

The experimental-statistical planning method is characterized by orientations to the results actually achieved in the past, by extrapolation of which the plan of the desired indicator is determined. This planning method is quite simple, but it has significant drawbacks: target, calculated in this way, reflects the current level of work with its underutilized reserves and errors in the past.

Normative method of planning (or method of technical and economic calculations ) uses regulations and norms. The method of technical and economic calculations can be used in three versions. According to the first, the calculation of gross income for the planned period is based on the predicted structure of trade and the current norms of trade allowances.

According to another option, the predicted value of gross income is determined by direct calculation of the profitability of each source of receipt of goods, each concluded supply contract, taking into account the links in the movement of goods, the influence of the main factors. The total gross income is calculated as the sum of the gross income that can be received trading company from the sale of goods under all concluded contracts, from all possible sources of receipt of goods and from the action of each factor separately.

Gross income under this method is also calculated by multiplying the volume retail trade on the average level of gross income achieved in the previous period, if this satisfies the goals of the enterprise.

The most easy-to-use calculation and analytical method for forecasting gross income. Its essence lies in the fact that on the basis of reporting data for the past period of the current year and a study of the dynamics of the level of gross income for the two previous years, the expected level of gross income for this year. This expected level of gross income is taken as the base value for forecasting the amount of gross income.

Among the economic and statistical methods, the moving average method is most widely used for forecasting purposes. The essence of the method is to equalize the level of gross income using the moving average method of a dynamic series (4-5 years) and spread the identified trend in the development of gross income for the future.

The calculation of gross income using economic and mathematical methods involves the solution of a multifactorial model using a computer.

With relatively stable prices and projected business conditions, profit is planned for a year within the current financial plan. The current situation makes annual planning extremely difficult, and enterprises can make more or less realistic profit plans by quarter. Since, since 1993, profit planning has been "tied" to the calculation of advance payments for income tax and the procedure for making them to the budget, the preparation of quarterly plans becomes necessary. Profit tax payers are interested in the fact that the difference between the amount of advance tax payments declared by them and the actual payments is minimal. However, the more important goal of profit planning is to determine the ability of the enterprise to finance its needs.

The object of planning is the planned elements of profit, mainly profit from the sale of products, performance of work, provision of services. The basis for the calculation is the volume of the production program, which is based on consumer orders and business contracts.

In the most general form, profit is the difference between price and cost, but when calculating the planned profit value, it is necessary to clarify the volume of products from the sale of which this profit is expected. It is necessary to distinguish the planned amount of profit per commodity output from the profit planned for the volume of products sold. Profit on commodity output is planned on the basis of cost estimates for the production and sale of products, which determines the cost of commodity output for the planned period.

The least squares method allows you to predict the future values ​​of any indicator based on its previous values. It consists in approximating the analyzed indicator by a mathematical function, in the simplest case - a linear one, that is, a function of the form y=ax+b. For known values x i , y i we can write down the system of equations:

If we denote the year through x, taking 2008 as a reference point (x=0), and through y the predicted indicator, then for 2010 (x=2) the system solution will be as follows:

Extrapolation of form No. 2 by the least squares method (Table 18) shows further growth revenue and cost. Although relative cost growth is projected to exceed revenue growth, gross margin is expected to increase by 7.37% as revenue exceeds cost and relative growth has a larger impact on gross margin. Profit before tax will increase not only due to the growth of profit from sales, but also due to the growth of profit from other income and expenses, and its growth will be 9.82%. Net profit growth will be 9.3%.

Table 18 - General forecast of profits and losses in form No. 2

Having predicted in a similar way the revenue and cost per unit of production and the volume of its sales, it is possible to calculate the projected values ​​of gross profit for each type of product (Table 19) and for the whole enterprise and, accordingly, net profit (Table 20). This is a more efficient approach than the above forecasting of profit only on the basis of its previous values.

This forecast shows a significant increase in gross profit from the sale of grain (including for wheat - by 43.66%), primarily due to a significant excess of revenue growth per centner of products compared to the increase in its cost (for wheat - 14.53% vs. 7.69%). Gross profit from the sale of sugar beet will decrease by 39.92% mainly due to a decrease in its sales volume by 39.31%.

Table 19 - Forecast of gross profit by type of product and in general for the enterprise based on forecasting the factors of its formation

The sale of cattle meat in 2010 will bring a small loss, since with almost equal values ​​of revenue and cost, the latter will increase slightly more. Sales volume of pig meat in 2007-2009 is stable, therefore, it is not expected to increase in 2010 either. 24.01%. Despite the projected increase in the volume of milk sales, the increase in cost and decrease in revenue per centner will lead to a decrease in gross profit from milk sales by 5.46%.

As a result, the total gross profit will increase by 5.77%, which will lead to an increase in net profit by 7.84%.

Table 20 - Forecast of net profit in form No. 2 based on the data obtained in Table. 19 gross profit values

Another way to predict profit is to calculate it based on predicting the results of the impact on profit of the dynamics of the factors of its formation based on the results factor analysis(Table 21), however, if data are available for less than 4 years, the mathematical forecasting mechanism degenerates, and its results do not differ from the results of a simple extrapolation of the total gross profit (Table 18).

Net income can also be forecast based on value forecasts production means and the level of profitability of the enterprise (Table 22). This forecast shows an increase in fixed and current assets by 16.39% and 12.03%, respectively, which will lead to an increase in net profit by 6.04%, despite a decrease in the level of profitability of the enterprise by 6.88%.

Table 21 - Forecast of gross profit by type of product and in general for the enterprise based on the results of its factor analysis

Table 22 - Forecast of net profit by the dynamics of production assets

The dispersion of the received forecast values ​​of net profit was 3.1%. In any case, data for at least 5-7 previous years are required for sufficiently accurate forecasts.

So, according to the results of the received forecasts in 2010, gross profit is expected to grow relative to 2009 for grain crops and pigs, which confirms the prospects of these types of products, and its decrease for sugar beet, cattle and milk. Further growth of the company's gross profit and its net profit is also expected.