Forecasting company revenue. Revenue planning: we calculate the daily activity of managers. Revenue planning: “smart” the resulting forecast

In almost any field of activity, from economics to engineering, there is a demand to predict the result of a particular action, obtain values ​​and approximate data. There are a lot of different software in this direction. And most of this software has paid features.

The Microsoft spreadsheet processor has in its software a powerful forecasting tool that allows you to build whole line various models and easy to put into practice various methods. Moreover, in most cases, this tool gives more reliable results than paid programs. How and in what way? Let's figure it out.

Forecasting is the search for the pace of development and the result obtained relative to the initial data at a specific time.

Let's consider several ways that can give a predicted result:

1. Trend line

Trend line is a graphical representation of forecasting through extrapolation. Sounds nerdy? In practice everything is simpler.
Let's try to predict the company's earnings in 36 months based on the past 12 years.

Let's build a scatter plot based on the company's initial data, namely its profit for all 12 years. Let's write down the initial data on profit in the table, select all its fields and go to the "Insert" - "Diagram" menu and select point view diagrams.

To build a trend line, select any point on the chart, open the context menu with the right mouse button and select “Add trend line...” from the list. In the approximation selection menu that appears, select the “Linear” type.

Let's make some minor adjustments to the line format: set the “Forecast” for three years, enter “3.0”, and indicate that the reliability value and the equation itself are shown on the diagram.

Using the constructed trend line, we can predict income in three years - it will be more than 4,500 thousand rubles. The reliability of forecasting is considered to be correct at “0.85” units. Forecasting performance will not be successful if the period exceeds 30% of the base period.

2. Using the FORECAST operator

The program's feature set also includes a number of standard forecast creation functions. One of these is the “PREDICT” operator, the syntax of which is: “=PREDICTION(X;known_values_y;known_values_x)”.

Argument “X”, based on our table, is the desired year for forecasting. “Values ​​of y” are profits for the past time. The “x values” are the years during which the data were collected.

We will find out, based on the data already received, the forecast for next year using the “PREDICTION” operator. To do this, insert the “PREDECTION” operator into the profit cell for 2018 using the function wizard.

In the dialog box that appears, we will indicate all the initial data, as described above.

The result obtained coincides with the result of the previous method, so the profit forecast can be considered reliable. For visual confirmation, we can build a diagram.

Another static operator that can be used for forecasting is the TREND operator with the following syntax: “=TREND(Known_y_values; Known_x_values; new_x_values;[const])". The operator arguments are identical to the arguments of the PREDICTION operator.

Let's try to forecast for next year using the "TREND" operator. Insert a function from the Function Wizard into the new cell.

We fill the arguments with initial data and make sure that the next forecasting method copes with its task perfectly - its result is similar to the results of previous steps and is reliable.

4. Using the GROWTH operator

A similar method for forecasting data is the “GROWTH” function, except that it uses an exponential dependence when calculating the forecast, unlike previous methods that used a linear one. Its arguments are identical to those of the TREND operator.

As in the previous steps, we insert the “GROWTH” function into a new cell, fill the arguments with the initial data and compare the forecast result. It also provides reliable data similar to the previous ones.

5. Using the LINEST operator

Another operator that can predict the outcome for a certain period of time is the LINEST operator, which is based on a linear approximation. Its syntax is similar to the previous operators: “=LINEST(Known_y_values, known_x_values, new_x_values,[const];[statistics])".

Let's insert a new function into the cell with the predicted year and fill in the arguments.

Every businessman, his entrepreneurial activity and faced with the need to draw up sales budget, caused by the desire to predict their future income, discovers that, in the absence of special knowledge in this area, it is completely unclear where to start.

In fact, everything is quite simple. Let's look at the simplest ways to draw up a sales budget if the level of sales does not depend on seasonality.

To begin with, let us stipulate that we will be talking about income, that is, revenue, and not net profit.

So, sales budget is a reasonable calculation of the flow of future income by period, which is compiled:

1. based on actually available information on sales for previous periods;
2. based on generalized statistical data based on several probable outcomes of events.

Let's consider the first option. It all depends on what information you have available. If, for example, you have been selling for some time, you can use actual sales data for previous periods.

Forecast for new period it is necessary to calculate not simply by multiplying the previous revenue by two, but based on your planned level of desired income. This figure should contain all the necessary quantities and expenses that ensure the return on investment of the business, as well as the real growth rate, slightly overestimated, so that there is something to strive for.

All these indicators must fit into a format figure, for example - 1.40 - where one is the base value (actual revenue for the previous period), and 0.40 will be overall coefficient, or 40%, which will show how much it will be necessary to increase the implementation of the previous period.

All expenses that relate to the rise in prices of goods and services directly, that is, operating expenses, must be included in the product margin (planned level of profitability). Therefore, planned price increases for transportation costs, purchase of goods and labor costs should be included either as a separate amount or the profitability standard should be increased.

In order to formulate a competent sales plan, you should take into account some important factors. Here they are (with examples of indicative values ​​that may vary in real conditions):

• The natural rate of inflation is 15% per year;
• Natural increase in purchase prices for goods – 7% per year;
• Cost growth transport costs– 5% per year;
• Possible costs for using credit funds – 25% per year;
• The planned percentage of profitability is 20% per year.

Add or remove unnecessary indicators. Each value is calculated in percentage, ultimately adds up (15 + 7 + 5 + 25 + 20 = 72%), showing the overall growth rate by which you need to, by adding one, multiply the actual value of revenue for the previous period, that is, for example, for the year. This is done this way:

Planned revenue for the forecast year = Revenue for the previous year * 1.72

Second calculation option– when there is no actual data, that is, you are starting and want to make an approximate sales budget. There is nowhere to get data from, you don’t have any information, so you will have to rely on verified statistical data and your own ingenuity.

Think about what the target audience of your product will be, who exactly will buy it and where. Find out the size of the area or city where your product will be sold, think about how many people will purchase your product daily and in what quantity.

Look for information on the Internet, or conduct a search. Having received the necessary figures - the number of units of purchased goods identical to yours, the relationship to this product group, approximately calculate three possible outcomes of events - minimal, average and excellent sales.

The minimum sales plan should ensure break-even, that is, the existence of the company to cover at least all fixed costs without income, and, of course, should not be a target, because the goal of entrepreneurial activity is to make a profit.

The minimum plan can be taken as a base value, and you will start from it in further calculations, and you should not fall below this level.

The average and excellent (planned) implementation plan is made based on assumptions about the minimum implementation, increased, for example, by two or three times, or by some standard amount, or taking into account some other influencing factors.

For example, calculate planned revenue using the same formula as for the previous example to ensure the generally accepted standard level of profitability - 20%. Take for approximate calculation all the same values ​​as in the example above, only substitute the minimum plan for the year in the formula instead of last year’s revenue.

Do not overdo it, do not overestimate or underestimate these estimated indicators unnecessarily, and then you will be able to see a more or less clear picture of your future cash receipts.

Forecasting is always a process involving creativity and guesswork. The main thing to understand is that these assumptions must be supported by logic and at least some amount of statistical data as a basis for calculation.

Every investor, starting his own business, first of all wants to predict his income in the future, so he is faced with the need to draw up a sales budget. At this stage, difficulties begin, since not every beginner has enough knowledge in this area. In fact, everything is much simpler than it might seem. In this article we will tell you about the most simple ways compilation and consider ways to calculate revenue.

Revenue calculation options

It is important to note that the article will focus on revenue, and not, it is worth distinguishing between these two concepts.

It is also important to clarify what a sales budget is - this is the name of a reasonable calculation of future income, which is compiled by period taking into account the following parameters:

• Actual information on sales in previous periods;
• Summary statistics.

How to calculate revenue when you have actual sales data

In other words, it all depends on what information you have. In the first case, if you have not been on the market for the first month and you have real sales data that you received in previous sales periods, you can calculate revenue as follows.

To forecast revenue for a new period, it is not enough for you to multiply the previous one by two. You should proceed from the planned level of income that you want to receive. The required figure should consist of all the necessary quantities and expenses that ensure the return on investment of your business. In addition, this figure includes the real growth rate, which should be slightly overestimated so that you have a target level to strive for.

All of the above indicators should make up the format figure. Take for example the figure 1.40, in which 1 is the base value (that is, the amount of revenue for the previous period), and 0.40 is the overall coefficient or the number of percentages that indicate how much you should increase sales.

Formula for calculating revenue if actual sales data is available

To calculate revenue, you need to take into account some significant factors. Let's list the factors with given examples approximate values:

• Increase in the cost of transport costs – 4% per year;
• Increase in purchase prices for goods – 5% per year;
• Inflation rate – 17% per year;
• The planned percentage of profitability is 25% per year;
• Costs for using credit funds are 20% per year.

Each of the proposed values ​​must be calculated with a percentage ratio, and in the end we get 4 + 5 + 17 + 25 + 20 = 71%. This figure shows the overall growth rate. By adding one to it, multiply the actual value of the revenue received in the previous period, say, for the year. Thus, we get the formula: planned revenue for the next year = revenue for last year × 1.71. Thus, you have calculated the planned revenue for the year, dividing it by 12 to get the result for the month.

How to calculate revenue without actual data

If you're just starting out in business and don't have actual sales data yet, how can you calculate revenue? You have neither information nor data; you can only rely on existing statistics.

First of all, you should determine what your target audience will be, that is, who and where will buy your products. You need to obtain information about the population of the city in which you plan to do business and determine the approximate number of people who will purchase your product daily.

You can find this information on the Internet or conduct market research. When the necessary figures are at your disposal, you should calculate three possible options outcome – good, average and minimal product sales. At the same time, the minimum plan should provide you with break-even. In turn, the average and good plan implementation is done on the basis of the minimum implementation, which is increased by approximately 2-3 times, or taking into account certain factors affecting the implementation.

You can calculate the target revenue using the above formula in order to ensure the generally accepted standard level of profitability, which is 20%. You can take the same values, but instead of last year’s revenue, now substitute the minimum plan for the year. You should not overestimate or underestimate the estimated figures, otherwise you will not be able to estimate a more or less accurate amount of your future profit.

• Marketing research in the diploma they do in order to determine two parameters: Number of sales units Cost of sales units Together these two quantities form trade turnover. How to determine...
• Hello, dear reader. The question came today: How to come up with the average (average annual) number of employees if there is no data Answer: You can start from labor productivity. That is, in a year...
• Hello! Can I ask you a question about the diploma? If you can, please answer) The situation is such that due to discounts for debtors, the repayment period for receivables is reduced in...
• Good afternoon, dear guest of the site. Today I received the following question: In general, even such changes are enough to show the impact of the crisis on reporting. Good luck. Sincerely,…
• In order to evaluate the results of implementing activities in the third chapter of the diploma, it often makes sense to come up with a forecast balance and a report on financial results. What is this for…
• Alexander, good afternoon! Could you tell me how to come up with the average (average annual) number of employees if there is no data?) Hello. You can start from labor productivity. That is…
• Hello, Alexander! Sorry to bother you, but I really need your advice. I am writing a thesis on assessing the financial condition of a trading enterprise. It is known that trading enterprises have their own specifics...

Question No. 0008: “Hello! Please tell me how to forecast sales revenue, does it depend on quantity? I compared it with the previous year, but it doesn’t seem right to me! Do you need to multiply the average profit by the growth? How to find this coefficient? I need to create a revenue forecast.”

Answer:

Good afternoon, Alina.

In practice, forecasting sales revenue at enterprises is carried out using two methods.

The first method based on a forecast made by consultants and experts. In the literature, this method is often called “Expert”. Its main essence is that the revenue forecast is compiled based on the opinions of experts and consultants, and the experts can be top managers of the company, and the consultants are usually external. Typically, this method is used to forecast markets that are characterized by their instability, since the use of mathematical concepts and dependencies here is quite problematic. In my practice, I most often came across exactly this method, even in relation to those markets that were well studied.

Second method based on construction mathematical formulas and dependencies. In my opinion, your question relates precisely to the definition of this method. In the literature it is often called “Statistical”.

Let's start with the fact that for this method the moment of collecting information is very important. For example, to forecast monthly revenue for the next year, you need to analyze transactions for each month of the current year. During this analysis, it is necessary to exclude from the calculation major operations(for example, 10% of monthly revenue) in the future they will only reduce the accuracy of the forecast.

So, in order to predict revenue for the next year, you must first calculate chain sales indices. This is the ratio of the revenue volume of each subsequent month to the previous one, after which the average index for the required period is found. Then the revenue volume of the last reporting month is multiplied by the index of the first planned month and thereby we obtain the forecasted revenue value of the first planned month. To forecast subsequent months, it is worth making similar calculations.

An example form for the necessary calculations can be found in the list additional materials to this article or in the “” section of the main menu.

If you have any questions, I will be glad to answer them.