The relative value of the planned task and the implementation of the plan. Percentage of plan completion: calculation, examples

Most analysts working in corporations must use various kinds of mathematical operations in Excel to get the company's operating performance. Calculations such as percentages of the amount, relative deviation from the budget or possible markups arising from the performance 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 the current achievement of the set goal, they mean a relative comparison of current indicators, with the planned indicators that need to be achieved. Mathematical operations for calculating this formula in Excel are very simple. It is necessary to divide the current indicators by the planned ones and display the value of the result in the percentage format of the cells. Thus, we will get a percentage value showing the share of the 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 so far only 80 pieces 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: \u003d 80 / 100.

How to calculate the percentage of plan completion in Excel

It does not matter how the task is set: the percentage of the goal, the implementation of the budget or sales plan as a percentage - it all relates to the same task. It is calculated in the same way. The figure below shows a list of regions. On the contrary, each region next to the column indicates the desired goal and the actual implementation of the plan. Please note that in the last column where the result of the plan execution is indicated as a percentage, the format of the cells 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.

Little 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 features. It is enough just to enter the formula in the first empty cell of the last column (D2), and then copy it by filling in the rest of the cells.

How to calculate the percentage of completion of the overall plan

Now let's complicate the task. Suppose we need to separately compare each actual indicator in relation to the general set plan for all regions. The task at hand is illustrated in the figure below:

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

Note that the denominator of the formula uses an absolute reference to cell $E$2. The dollar signs tell us that the reference to the cell with the value of the general plan is blocked. This ensures that it doesn't change when you copy the formula to other cells in the Fraction 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 got the same overfulfillment of the general plan - 105%. The final values ​​in percentage coincided with us, which means that all calculations of the formulas are correct.

Relative value of the planned task(planned target indicator) is the ratio of the planned level of the indicator to its level achieved in the previous period (or in the period considered as the base one).

The relative value of the planned task characterizes the prospects for the development of the phenomenon
OVTR = planned level for the future (next) period / actual level of the current (previous) period

Example: in 2007 the number of employees was 120 people. in 2008, it was planned to reduce production and bring the number to 100 people.
Solution:
OVTR =(100/120) *100% = 83.3% - 100% = -16.7%.
The company planned to reduce the number of personnel by 16.7%.

Relative value of plan execution

Relative value of plan execution(plan implementation indicator) characterizes the degree of implementation of the plan.
TFR = actual level of the current period / plan of the current period

Example: in 2007 the number of employees was 120 people. in 2008, it was planned to reduce production and bring the number to 100 people. But the number of employees for the year increased to 130 people in a year.
Solution:
ROVP \u003d (130 / 100) * 100% \u003d 130% - 100% \u003d 30%.
The actual number of employees exceeded the planned level by 30%.

There is a relationship between and the relative value of the planned task and the relative value of the plan implementation, expressed in the formula: OVVP = OVD / OVPV

Example: the company planned to reduce the cost by 6%. The actual decrease compared to the previous year was 4%. How was the cost reduction plan implemented?
Solution:
ATS \u003d (96 / 100) * 100% \u003d 96% - 100% \u003d - 4%
OVTR \u003d (94 / 100) * 100% \u003d 94% - 100% \u003d - 6%
RWP = 96% / 94% = 102.1% - 100% = -2.1% the actual level exceeded the planned one by 2.1%.

Example: an insurance company in 1997 entered into contracts in the amount of 500 thousand rubles. In 1998, she intends to conclude contracts for the amount of 510 thousand rubles. The relative value of the target will be equal to 102% (510 / 500).

Suppose the influence of various factors led to the fact that in 1998 the insurance company concluded expensive deals in the amount of 400 thousand rubles. In this case, the relative value of the execution fee will be equal to 78.4% (400/510).

The relative values ​​of the dynamics, the planned task and the fulfillment of the plan are related by the following relationship.

The planning process is an integral part of any success. The presence of goals specified in indicators allows you to control the movement of a person or organization in a certain direction, gives clarity to the activity. This allows you to quickly move to effective action. However, an equally important condition for achieving success is the ability to control the results of their activities. A person should have a good idea at what stage he is and how much more he has to do. This article will tell you how to calculate the percentage of the plan completed.

Why do you need to count

There are many areas of human activity, the results of which can be accurately quantified. In order to carry out their activities effectively, to get the maximum benefit from their actions, they resort to planning. The presence of specific goals allows you to adhere to exactly those indicators under which the activity makes sense.

A prime example in this case is budget planning. In order for the organization to remain profitable and receive the funds necessary for development, indicators of income and expenses are developed. The implementation of the plan is a guarantee of the successful development of the company.

In order to properly build their activities and achieve these indicators in a timely manner, it is necessary to imagine at what stage the organization is in relation to this goal. It is for a clear positioning of a person or organization on the path to achieving results that the calculation of the percentage of the plan is applied. It allows you to properly build your activities so that all the necessary indicators are achieved just in time.

How to calculate the execution of the plan

When it comes to how to calculate the percentage of the plan, it means the ratio of its two components: those indicators that have been achieved and those that need to be achieved. In sales, this is the total number of purchases made by customers in relation to the total planned sales volume. When planning a budget, this is the amount of money earned at the moment in relation to the amount of finance that needs to be earned over a certain period of time.

Thus, to determine the percentage of completion of the plan, it is necessary to find the quotient of the results achieved at the moment and those planned for a certain date, and then multiply the resulting number by 100.

PVP \u003d TR / ZR * 100

• PVP - the percentage of the plan;
• TR - current results;
• ZR - planned results.

For example, a real estate agency plans to hand over 28 apartments this month. At the moment, 6 are rented. So, the percentage of the organization's plan at the current time is 21. It is this information that serves as the basis for intensifying the company's further activities and searching for new tenants.

PVP = 6 / 28 * 100 = 21.428

Conclusion

In most areas of activity in which results are clearly quantifiable, a person or organization resorts to planning values, the achievement of which is a necessary condition for ensuring successful development. This operation is applied in the field of sales, economic activities of large organizations, small projects, ordinary families, as well as in many other areas. Achieving the planned indicators requires a clear positioning along the entire path towards them. It is for the purpose of exercising control over the approximation to the plan that the formula for the percentage of the plan is used.

The implementation of the plan is an indicator that is very often used not so much in statistics as in the economy of an organization. The thing is that the analysis of the implementation of planned actions is the most important part of the analysis of sales revenue, productivity, cost and a number of other important indicators of the enterprise. But to help calculate the level of implementation of the plan, and more often overfulfillment or underfulfillment helps the relative value of the plan.

As already noted, the three relative values ​​are interconnected. They are combined into a common block of mutually complementary relative values. The relationship formula in this case looks like this: ATS = OVPV x OVVP, but we will talk about this in more detail in the third part.

So, Relative value of plan execution , we will call it for short OVVP . In some textbooks, in particular Shmoylova's Theory of Statistics, this relative value has a slightly different name. Relative plan completion rate , well, the essence of the calculation itself and its principle, of course, will not change.

The relative value of the implementation of the plan shows how many times the actual level is more or less than planned. That is, by calculating this relative value, we can find out whether the plan is overfulfilled or underfulfilled, and what is the percentage of this process.
Similar to the calculation of the plan target, the execution of the plan is calculated based on two indicators. However, there is a fundamental difference here; for the calculation, indicators of the same period of time are used (in the planned task, these were two different periods). Included in the calculation:
Upl - the planned level for the current year.
Uf.t.g. - the actual level of the current year.

Calculation of the relative value of the implementation of the plan (RTI)

We will calculate the fulfillment of the plan, as well as the percentage of fulfillment and the percentage of overfulfillment, using similar formulas, as in the calculation of the planned task.
1. Coefficient form- characterizes how many times the actual value for the current period exceeds the planned indicator for the current period.

3. Growth rate shape allows you to determine by what percentage the plan is overfulfilled or underfulfilled.

We will carry out calculations using the indicated formulas and analyze the results obtained.

Example. The output in 2015 actually amounted to 157 million rubles, while the planned figure for the same period was 150 million rubles. Determine the relative value of the plan, the percentage of the plan and the percentage of overfulfillment of the underfulfillment of the plan.

Given: Solution:
Upl 2015 - 150 million rubles. OVVP = 157 / 150 = 1.047

UV 2015 - 157 million rubles. % VP \u003d 1.047 x 100% \u003d 104.7%

Define:Δ% VP \u003d 104.% - 100% \u003d + 4.7%
ROVVP, %VP, Δ%VP
Thus, we get:
- The relative value of the implementation of the plan was 1.047, that is, the actual indicator exceeds the planned one by 1.047 times.
— The plan was fulfilled by 104.7%.
— The plan was exceeded by 4.7%.

It should be said that when calculating the growth rate, the resulting data can be negative , that is, there will be an underfulfillment of the plan.

As already noted, this relative value is a whole complex with two other relative values, you can view the link, and features.

Practice #1.

Average values.

Exercise 1.

Task 2. Indicators of financial performance and tax burden of commercial banks in the region for the reporting year.

It is required to determine the average values ​​of all features presented in the table.

Task 3.

There is data on wages at the enterprise for the month:

the average salary and the number of certain categories of workers are known (columns 1 and gr. 2);

the average wages of certain categories of workers and the share of workers of these categories in the total number of employees of the enterprise are known (columns 1 and gr. 4);

the average salary and wage fund of certain categories of workers are known (column 1 and gr. 3);

the number and wage fund are known (column 2 and gr.3).

METHODOLOGICAL INSTRUCTIONS

Exercise 1. Data on the work of two organizations are presented:

In the example under consideration, the unit of the population is one enterprise, therefore, among the signs presented in the table, the planned and actual volume of output are primary. Therefore, to calculate the average value of each of these features, it is required to apply the form of a simple average.

The sum of the planned output of all

Average volume planned by enterprises

output = ______________________________________ ;

Number of enterprises

Similarly:

The sum of the actual output of all

Average volume of actual enterprises

output = ______________________________________;

Number of enterprises

The next feature in the table - the percentage of plan completion - is a relative value calculated by the formula:

Percent Actual output (F)

plan execution (B) = ______________________________________
.

Production plan (P)

The average value of the percentage of plan completion can be represented as a ratio of generalized values ​​of the same features, each of which will cover the entire set of units under study.

Total actual volume

Average percentage of products of all enterprises

execution = _________________________
.

plan for the total output of all

set of enterprises according to the plan

enterprises .

Since, as already noted, the unit of the population in our example is one enterprise, the output of one worker is a secondary feature. Hence, to calculate the average value of production, it is required to apply the weighted average formula. The following ratios are the starting points for such a calculation.

Total actual volume

The actual production of the enterprise ( )

production of one = _________________________________.

working ( ) Number of workers of the enterprise ( )

In accordance with the above method for determining the average value of a secondary feature, we can write:

Actual output

Average actual output produced by two enterprises

one worker = ________________________________

Number of workers of two enterprises

;

To determine the average output of one worker, it is necessary to first calculate the number of workers employed at each enterprise. In accordance with the initial data, this is possible by the formula
. Having carried out the necessary substitution, we obtain the following expression for the desired average, which corresponds to the form of the harmonic weighted average.

.

Substituting the numerical values ​​into the formula, we get:

The basis for calculating the average values ​​of a secondary attribute (relative values) is the initial ratio of attributes that determine the logical formula of the averaged relative indicator. For each indicator used in economic analysis, only one initial ratio can be compiled to calculate the average. If, for example, it is required to calculate the average size of a bank deposit, then the initial ratio will be as follows:

The amount of all deposits (thousand rubles)

ISS \u003d __________________________. (2.20)

Number of deposits

If it is necessary to determine the average interest rate on loans issued for the same period, then the following initial ratio will be required:

Total amount of interest payments (on an annual basis, thousand rubles)

ISS \u003d ________________________________________________________. (2.21)

Total amount of loans granted (thousand rubles)

To calculate the average wages of employees of the enterprise, it is necessary to divide the total wage fund by the number of employees.

In the case when one of the final indicators is unknown, the calculation of the average is made on the basis of the initial data on the value of the averaged secondary attribute for each individual unit of the population and the attribute associated with it - the weight. Thus, the average of the secondary feature has the form of a weighted average. If the values ​​of the denominator of the original ratio are known, but the values ​​of the numerator are not known, then the average is calculated using the arithmetic mean formula. If the values ​​of the numerator of the original ratio are known, but the values ​​of the denominator are not known, then the average is calculated using the harmonic mean formula.

For example, to compare the level of tax burden and the financial condition of credit institutions, the following data is used (Table 2.4)

Task 2. Indicators of financial performance and tax burden of commercial banks in the region for the reporting year.

It is required to determine the average values ​​of all features presented in the table.

In the example under consideration, the aggregate unit is one bank, therefore, among the features presented in the table, the primary ones are the profit and equity of banks. Therefore, to calculate the average value of each of these features, it is required to apply the form of a simple average.

The amount of profit received by all banks

Average size (P)

profit received by banks = ______________________________________ ;

for the reporting period Number of banks (n)

=186910.25 thousand rubles.

The amount of own funds of all banks

Average volume of own

Bank funds (F)

for the reporting period = ______________________________________;

Number of banks (n)

=1346926.25 thousand rubles

The next sign in the table - return on capital is a relative value calculated by the formula:

Profit received by the bank (P) Profitability

Capital (B) = ______________________________________
.

Bank equity value (F)

The average value of the percentage of profitability can be represented as a ratio of generalized values ​​of the same features, each of which will cover the entire set of units under study.

The total amount of profit,

received by all banks

Average percentage

return on equity = _________________________
;

Amount of own funds

all banks

13,88%.

To calculate the average value of the tax burden, it is required to apply the weighted average formula. The following ratios are the starting points for such a calculation:

Tax payments

on capital = _________________________________.

Bank equity value (F)

Tax payments (N)

on assets \u003d Value of the bank's assets (A)

In accordance with the above method for determining the average value of a secondary feature, one can write down:

;

To determine the average tax burden on capital, it is necessary to first calculate the amount of tax payments for each bank. In accordance with the initial data, this is possible using the formula:
.

Having carried out the necessary substitution, we obtain the following expression for the desired average, which corresponds to the form of the weighted arithmetic average.

*100=4,31 %;

;

To determine the average tax burden on assets, it is necessary to first calculate the value of assets for each bank. In accordance with the initial data, this is possible according to the formula:
.

Having carried out the necessary substitution, we obtain the following expression for the desired average, which corresponds to the form of the harmonic weighted average:

=0,96%.

In some cases, the weights can be presented as relative values ​​of the structure (in percentages or fractions of a unit). Then the formula for the arithmetic weighted average will look like:

(2.12)

where
- the share of each group in the total number of population units (frequency). If the frequencies are expressed in fractions (coefficients), then
and the arithmetic mean formula is simplified:
.

Consider methods for calculating the average value.

1. Determine the average volume of output at the enterprise.

This is a primary feature, therefore, we will use a simple arithmetic average:

2. Determine the average labor productivity. In this case, the initial data for calculating the PT may be different.

PT is a secondary symptom.

In order to choose the right formula for averaging a secondary feature, it is necessary to compose the initial ratio of the average(ISS), which reflects the logical formula of the average indicator.

For our example, the ISS looks like this:

To find the average value of the PT, it is necessary to find the quotient of the division of the final calculations for VP and NZ for two enterprises:

- this form of calculation of the average is called the aggregate or implicit average (because the individual values ​​of the averaged feature are not present in explicit form).

Consider 2) the case.

Now the denominator is unknown in the ISS - CZ, but for each enterprise it can be found by expressing from the ISS:

, i.e. f-lu avg.harm.vzv., where in quality. weight favors VP

3) Now in the ISS the numerator is unknown - VP, then

Substituting this expression into the AIS to calculate the average PT, we get:

this is f-la cf.ar.vzv., where the weight is yav. CHZ

Task 3.

There is data on wages at the enterprise for the month:

Data on remuneration of certain categories of employees of the enterprise

In order to summarize data on the wages of employees of certain categories, it is necessary to calculate the average wage level for the enterprise as a whole. The initial information for calculating the average wage can be different:

the average salary and the number of certain categories of workers are known (columns 1 and gr. 2);

the average wages of certain categories of workers and the share of workers of these categories in the total number of employees of the enterprise are known (columns 1 and gr. 4);

the average salary and wage fund of certain categories of workers are known (column 1 and gr. 3);

(gr.2 and gr.3)

In order to choose the right formula for calculating the average wage, we compose the initial ratio of the average(ISS), reflecting the essence of the averaged feature, its logical content. The initial ratio for finding the average wage will look like this:

Obviously, knowing two elements from the ISS, you can always find the value of the third.

To find the average level of wages at the enterprise, it is necessary to find the quotient from dividing the final calculations by wage funds and the number of employees in four selected categories:

Let's consider how this formula will be transformed depending on the nature of the initial information.

In the first case, when there are data on average wages and the number of employees of certain categories, the numerator is not known in the EIS, i.e. the size of the payroll. For each category of workers, the wage fund can be defined as

Substituting the expression for F into the formula for finding the average wage , we get the formula arithmetic mean weighted. The role of the sign-weight is performed by the number of employees:

Let's calculate the average salary for our example:

The average salary at the enterprise is 17,845 thousand rubles.

Let us consider the second case, when not the absolute numbers of employees of certain categories are known, but their relative values ​​- specific weights (shares) in the total number of employees of the enterprise ( d=H/∑H). In this case, the formula for the arithmetic weighted average will take the form:

if d are specific weights expressed as fractions ( ∑ d= 1),

if d– specific weights in percent ( ∑ d= 100%).

Let's use the second formula.

In the third case, the average wage at the enterprise must be determined on the basis of data on average wages and payroll funds for certain categories of workers. Now the denominator is not known in the ISS - the number of employees, but for certain categories it can be found using the formula:

.

Substituting this expression into the formula for calculating the average wage, we obtain the formula average harmonic weighted:

,

where the wage fund acts as the weight.

The average salary for the enterprise, calculated by the harmonic mean formula, will be:

In the fourth case, when data on the number and wage funds of certain categories of workers are known, i.e. both the numerator and denominator of the AIS are known, the calculation of the average wage for the enterprise comes down to substituting the data into the original formula:

This formula for calculating the average value is called aggregate average or implicit average, since the individual values ​​of the averaged feature are not explicitly present in the calculation.