Table 1.1

Plan fulfillment in % =72166 / 68952 *100 % = 104,66 %

That. we can conclude that the plan was exceeded by 4.66%

Absolute deviation= Reporting year fact – Reporting year plan

Absolute deviation= 72166 – 68952 = 3214 million rubles.

That. it can be concluded that the production plan commercial products exceeded by 3214 million rubles.

Relative deviation= Plan completion % - 100

Relative deviation = 104,66 – 100 = 4,66 %

The resulting deviation indicates that the plan was exceeded by 4.66%.

Absolute growth for the year= Reporting year actual – previous year

Absolute growth for the year= 72166 –67485 = 4681 million rubles.

That. Compared to the previous year, there was an increase in production volume by 4681 million rubles.

Growth rate %= Actual reporting year / previous year * 100%

Growth rate % = 72166 / 67485 * 100 % = 106,94%

In the reporting year, compared to the previous year, production in % terms amounted to 106.9%. To determine by how much% the volume of commercial output increased in the reporting year compared to the previous year, we calculate:

Growth rate %= Growth rate % - 100%

Growth rate % = 106,94% – 100 % = 6,94%

In the previous year, the volume of production increased to 72,166 million rubles, and the plan was exceeded by 4.66%, which in absolute terms amounts to 3,214 million rubles. The planned increase in production volume amounted to 1467 million rubles (1), and in fact, products were produced in the amount of 5442 million rubles, so the growth rate according to the plan was 2.2% (2), and actually amounted to 6.94%

1: 68952-67485=1467

2: (68952-67485)/67485*100%=2,2%

The reason for exceeding the plan may be a change in market conditions, an increase in demand for certain types of products, or an improvement in the organizational structure of the enterprise.

2. ANALYSIS OF THE IMPLEMENTATION OF THE PLAN BY ASSORTMENT

Let's determine the percentage of plan fulfillment for the assortment, and summarize the resulting calculations in analytical table 2.1.

Table 2.1

Determining the percentage of plan fulfillment by assortment.

Plan fulfillment in %= Actual product output / Planned product output *100%

Plan completion in %:

50400 / 45360 *100% = 111.11% (plan exceeded by 11.11%)

41600 / 46800 *100% = 88.89% (the plan was underfulfilled by 11.11%)

16500 / 14400 *100% = 114.58% (plan exceeded by 14.58%)

The coefficient of plan fulfillment according to music. for centers was 111.11%, for vacuum cleaners 88.89%, and for televisions 114.58%.

Conditional release (the fulfillment of the assortment plan is taken into account).

If the fact is below the plan, choose the fact.

If the fact is higher than the plan, we choose the plan.

Conditional release / Release of products in value terms according to plan *100%

Assortment plan fulfillment rate = 101360/ 106560 *100 % =

For vacuum cleaners, the plan was underfulfilled by 11.11%, but despite this, music. centers were produced 11.11% more than planned, and televisions were overproduced by 14.58%. In general, there is an underfulfillment of the assortment plan by 4.9%.

Let's carry out graphic modeling: build bar charts in the assortment composition.

The first thing a business owner should do is set goals for several months in advance and monitor their implementation daily. Calculating the percentage of their completion as of the current date allows you to understand what will happen at the end of the month if the manager continues to work in the same mode.

Formulas for calculation:

Percentage of plan completion for the current date = plan / number of working days * per number of days worked

Plan completion percentage = plan actually completed as of the current date / plan as of the current date

After counting, adjust the work of the department to increase applications. The more often you adjust, the better the result.

Take feedback: how many calls the employee made, how many of them were to new contractors, how many commercial proposals were sent (separately for new and current clients). If everything is in order with the employee’s work activity, then the problem is in the conversion rates of the funnel.

Set up a report on incoming contacts, deal duration, bandwidth each stage of the transaction and exit agreements, and work with problematic stages. First with quantitative indicators, then with qualitative ones.

We increase volumes by dividing labor

Volumes can be increased through proper distribution of responsibilities within the department. Divide your subordinates into those who attract new clients and those who work with current ones. Give to one employee large companies, the other - small and medium-sized businesses.

Separate your sales channels:

• Network
• Retail
• Offline
• Distributors
• Retail Online
• B2B end clients

At Henry Ford, this idea led to a 200-fold increase in volumes.

If one department has seven employees, then divide it into two departments. If the company has two managers, then hire a third. Creating competition within the company will lead to an increase in the volume of concluded transactions by 30%.

We generate volumes through the telemarketing department

This idea will be useful for those companies that use cold calling. The goal is to increase their number to 150-200 calls per day. What tools are used to increase the number of calls:

1. Integration of IP telephony with the system (the task is to teach sellers how to print and make calls at the same time)
2. Reverse dialing (the system itself will call the counterparty and connect him with the manager)
3. Substitution of numbers (if the system cannot get through from one number, this does not mean that the employee needs to grab his personal mobile phone and call from it - the system will offer to call from another number)
4. Uploading the database via CRM (it takes staff an average of three working days to find a client base - three days that prevent the results from being achieved)
5. on calls (get the employee to call more to increase sales)
6. Working day map (we exclude types of work that prevent an increase in revenue)

We motivate staff

In order for the manager who is involved in attracting traffic to work more efficiently and ensure an increase in the volume of concluded transactions, adjust it. Award bonuses equal to the percentage of fulfillment of the plan for applications and the plan for the cost of the application. If one plan is fulfilled but the other is not, cancel the bonus. Personal motivation is important for increasing the number of applications.

We increase profits by increasing volumes

To grow profits, a company needs to competently manage costs and increase the volume of transactions. You can start by designating a specific profit point that we want to earn based on the results of the month, quarter, or year.

After assigning a task to your subordinates, check its daily implementation according to the PDCA cycle -

• plan (plan),
• do (do),
• check (check),
• act (act).

The technology allows you to monitor the implementation of the plan, analyze and improve it to achieve ultimate goal.

We conduct a sales funnel conversion audit

What should you pay attention to to increase the number of closed deals? First of all, on satisfaction with the company’s work from the outside target audience. If this is not the case, then check the following points:

Do employees call the right companies (if a company has a low sales funnel conversion rate, this does not mean that the sellers are bad, perhaps they are calling the wrong counterparties. The likelihood of a transaction increases if you conduct an ABCXYZ analysis of the target audience)

Pipeline is an array of all deals that one individual manager is working on at different stages. For sales growth, it is very important that the head of the department controls the progress of transactions along the pipeline.

For each transaction, you need to fill in the following data: deal name (short keyword), counterparty (contact person and decision maker), deal stage, product, probability of closure, deal amount.

The likelihood of closure depends on the stage at which the transaction is at and the contact person on the counterparty's side. For example: a commercial proposal is sent - 30% closing, an agreement is sent - 50%, an agreement is signed - 70-90%.

In the pipeline, it is important to control the dynamics of sales through the funnel and the dynamics of the emergence of new deals. In addition, it is important to track the average bill. This will help you adjust your communication strategy with the client in a timely manner.

Mechanization and automation of production processes is measured by a coefficient, the base of which is taken to be a value equal to one. To calculate its value, we will conditionally assume that the production plan is set in the amount of 400 thousand rubles, in fact, during the reporting period, products were produced in the amount of 408 thousand rubles, then the coefficient of fulfillment of the production plan is equal to 1.02 (408 thousand). RUB 400 thousand RUB). A special form of relative values ​​are percentages, in which a value equal to 100 is taken as the base value. The calculation of the percentage of plan completion will have the following form: 408 thousand rubles. 400 thousand rubles X 100 = 102%.

The same result can be obtained even more in a simple way, namely by the method of percentage differences. To do this, the difference between the coefficients for fulfilling the plan for production, calculated on the basis of cost (Kst = 83,600 / 80,000 = - 1.045) and conditionally natural production volumes (it is possible in standard

The production plan fulfillment ratio is 1.254(100320 80000).

The relative deviation is calculated as the difference between the actual accrued salary amount and the planned fund, adjusted by the production plan fulfillment ratio. The percentage of fulfillment of the production plan is 102.6% (16,440 tubes / 16,023). However, it must be taken into account that only the variable part of the wage fund is adjusted, which changes in proportion to the volume of production. This is the salary of workers at piece rates, bonuses to workers and management personnel for production results and the amount of vacation pay corresponding to the share of the variable salary.

The data in column 5 is obtained by multiplying the indicator in column 4 for each type of product by the coefficient of plan fulfillment for marketable products as a whole for the enterprise in conventional natural units (Kvp)

In the theory and practice of analytical work, several methods are known for calculating the influence of this factor by comparing the amount of profit from sales according to the plan to the actual volume products sold with planned profit, adjusted to the coefficient of plan implementation in terms of sales volume by subtracting from the difference between the profit indicators according to the plan for the actual volume of products sold and according to the plan, the amount of influence of the factor change in sales volume (balance method).

Designations in the algorithms P, P - planned and actual balance sheet profit PJ, nj1 - planned and actual profit from the sale of marketable products (work, goods, services) J, JL - planned total cost and planned cost of the actual volume of products sold K - plan fulfillment ratio by volume of products sold J, JL - planned full cost and planned full cost of actual production of marketable products Kt - coefficient of fulfillment of the plan for the production of marketable products PCF - profit according to the plan for actually sold

We will analyze the ten-day output of commercial products based on table. 1.4. Its data show that in the first five months of the year, ten-day schedules were not maintained. Production volumes in the first two decades are below the plan, and in the third they exceed the plan target. In the next seven months, the schedules overlapped every ten days, the plan fulfillment coefficient for rhythm was equal to 1.

Plan fulfillment ratio to account for rhythm

To identify the influence of the factor of production volume, it is necessary to recalculate variable costs according to the planned estimate, taking into account the coefficient of fulfillment of the plan for the production of marketable products and subtract the planned variable costs from the obtained result. By excluding the size of the influence of the first factor from the total deviation of costs for the maintenance and operation of equipment, a violation of the estimate is determined.

First of all, let's calculate the impact on profit of the volume of product sales. To do this, it is necessary to determine the amount of planned profit (Pp), adjusted by the coefficient of plan fulfillment in terms of sales volume (Kr).

To distribute the relative deviation of actual consumption from the planned one, it is necessary to adjust the planned wages and salaries and the planned number of employees and the coefficient of fulfillment of the production plan. The entire deviation is equal (without taking into account the State Bank coefficient) 1650- -1600- 1.05 = -30 thousand rubles. (saving). Including a) due to lack of numbers (1020-1000 1.05) 1600 = - -30 1600 = -48 thousand rubles. (savings) b) due to exceeding the salary level (1617.6-1600) 1020 = 18 thousand rubles. (overspending) total relative savings 48-18 = = 30 thousand rubles.

To carry out this analysis, it is necessary to perform a number of additional calculations of indicators (by product) the coefficient of fulfillment of the cost plan of the unchanged composition and the adjusted actual costs in the assessment of the planned cost of specific products. The first of these indicators is determined based on the data in table. 7.13 by comparing actual

The amount of additional increase in production costs obtained under conditions of uniform production is determined by multiplying the adjusted volume of actual production in physical terms (by the average coefficient of plan fulfillment for products) by the deviation

K" - the coefficient of plan fulfillment in terms of production volume Dv - the share of the increase in production volume obtained due to a change in the number of employees, which is established in the enterprise plan,

The amount of additional increase in production costs obtained under conditions of a uniform (constant structure) production output is determined by multiplying the adjusted volume of actual production output in physical terms (by the average coefficient of plan fulfillment for products) by the deviation of the actual cost per unit of production from the planned value. According to the table. 8.2, the magnitude of this effect will be with the overall coefficient of plan fulfillment for products, calculated in the estimate at the planned cost -

The amount of increase in the cost of commercial products due to the price factor, calculated under the condition of the same (uniform) growth in the volume of commercial products of all types, will in this case be the overall coefficient of plan fulfillment for production

Let's consider an indicator used in sales such as Run Rate. The management of some organizations considers it the only correct one for predicting the fulfillment of the sales plan, but I would not idealize this indicator. Let us outline its main pros and cons.

So what is Run Rate?
Run - (from English) to go, run.
Rate - (from English) speed, temp, percentage.

So, in sales, Run Rate is an indicator that allows you to determine what percentage/value of the sales plan will be achieved by the sales department at the end of the period, moving at the existing sales pace at the time the indicator is measured.

Let's consider the formulas:

1. To predict the target sales plan completion rate (in percentage):

(number of working days for the entire period/sales value for the entire period)

Example: the sales department at the time of forecasting sold 50 units of product in 10 working days. Sales plan by the end of the month: sell 150 units of products. There are 12 working days left until the end of the month. What percentage of the sales plan is achieved by the sales department at the end of the month?

RR = (50/10)*(22/150)*100%=73%

It turns out that the sales department, selling at the same pace, will reach the target of 73%.

2. The second formula predicts the sales value at the end of the period (revenue, number of units of production, etc.):

(sales at the time of measurement/number of working days elapsed)

number of working days for the entire period

Let's take the above example:
RR= (50/10)*22=110

It turns out that the sales department, working at the same pace, will sell 110 units of products at the end of the month.
In the same way, you can forecast cash revenue at the end of the period.

So, the pros and cons of this method:

1. 1. Using this indicator, it is easy to predict the % of completion of the sales plan at the end of the period, but only if your sales department works evenly throughout the month (period), which is not true in many organizations, and the main volume of sales falls on the end of the month. Why? I will write in another article.

2. 2. Using this method, you can scientifically substantiate your sales forecast by the end of the month for your superiors, using formulas, and make a beautiful presentation.

3. Using this method, you can “bring to your senses” your sales managers, giving acceleration and showing that with such results in fulfilling the sales plan, they will not receive a bonus.

4. This indicator can be applied to a longer period of calculation, for example, not to days, but to weeks, or to months, in order to predict what percentage of the annual sales plan the company will achieve. But this indicator will work again if you apply a correction factor for seasonality, if there is one in the company.

In my work, I would not recommend that you completely rely on this indicator if you need accurate forecasts. A more accurate sales forecast can be built by analyzing a large amount of information for previous periods. In future articles I will write about such methods.

I wish you successful sales!

Most analysts working in corporations must use various types of mathematical operations in Excel to obtain the company's operating indicators. Calculations such as percentages of the amount, relative deviation from the budget or possible markups arising from the completion of all business analyzes. All this must be calculated as a percentage.

How to calculate percentage of a number in Excel

When management asks you to calculate the percentage of current achievement of a goal, it means a relative comparison of current indicators with the planned indicators that need to be achieved. The math to calculate this formula in Excel is very simple. It is necessary to divide the current indicators by the planned ones and display the result value in the percentage format of the cells. Thus, we will obtain a percentage value reflecting the share of implementation of part of the plan. Let’s say in the company’s sales plan it is planned to sell 100 tablets this month, but the month has not yet ended and at the moment only 80 units have been sold. As a percentage, this is mathematically calculated as (80/100)*100. If we use the percentage format of cells in Excel, then we do not need to multiply by 100. In this case, the formula looks like this: =80/100.

How to calculate the percentage of plan completion in Excel

It doesn’t matter how the task is set: the percentage of achieving the goal, fulfilling the budget or sales plan as a percentage - it all relates to the same task. Calculated in the same way. The figure below shows a list of regions. On the contrary, for each region, the desired goal and the actual implementation of the plan are indicated next to it in a column. Please note that in the last column where the result of the plan is indicated in percentage, the cell format has been changed to “percentage”. And the formulas in this column are very simple - the value of the “Sold” column is divided by the value in the “Plan” column = C2/B2.

Not much can be said about this formula. After all, it is based on mathematical calculation. In the formula itself, only cell references are used, so that one value is divided by another. Without any functions. You just need to enter the formula in the first empty cell of the last column (D2), and then copy it by filling in the remaining cells.

How to calculate the percentage of completion of the overall plan

Now let's complicate the task. Let's say we need to separately compare each actual indicator in relation to the overall set plan for all regions. The task is clearly illustrated in the figure below:

This time, regions do not have a column with their own plan. Instead, the "Share" column immediately follows, where each sales figure is compared with the overall plan specified in cell E2. The formula in the Share column this time is =B2/$E$2.

Notice that the denominator of the formula uses an absolute reference to cell $E$2. The dollar signs tell us that the cell reference to the overall plan value is locked. Thanks to this, it does not change when the formula is copied to other cells in the “Share” column. In cell C6 we add up all the percentages to make sure the result is accurate. As we see again in the second figure, we received the same overfulfillment of the general plan - 105%. Our final percentage values ​​coincided, which means that all calculations of the formulas are correct.