Percentage of the plan implementation by formula in Excel

The most analysts, which working in corporations, have to use different kinds of mathematical operations in Excel to get the company's operational indicators. The calculations such as percentage of the amount, relative deviation from the budget or possible mark-ups arising from the performance of all business analyzes. All this you need to calculate as a percentage.



How to calculate the percentage of a number in Excel?

When the management asks you to calculate the percentage of the current implementation of the goal, it means a relative comparison of current indicators, with the planned indicators to be achieved. The mathematical steps for calculating this formula in Excel are very simple. It is necessary to divide the current indicators into the planned ones and display the result value in the percentage format of the cells. In this way, we will get the percentage value representing to the share of the implementation of the plan`s part. Assume in the sales plan of the company planned to sell this month 100 tablets, but the month has not yet ended and at the moment there are only 80 items were sold so far. In percent this is mathematically calculated as (80/100) * 100. If we use the percentage format of the 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 execution in Excel?

It does not matter how the task is set: the percentage of goal realization, the implementation of the budget or sales plan in percent’s – it all relates to the same task. It is calculated in the same way. In the figure below is displayed to the list of regions. Opposite of each region next to the column indicates the desired goal and the actual implementation of the plan. Note that in the last column, where the result of the plan is indicated in percent, the format of the cells is changed to «Percentage». And the formulas in this column are very simple –there is value of the «Sales» column is divided by the value in the «Plan» column =C2/B2.

Few things to say about this formula: after all, it is based on the mathematical calculation. In the formula itself only references to cells are used, so that one value is divided into another without any functions. It is enough just to enter the formula in the first empty cell of the last column (D2), and then to copy it by filling in the other cells.

How to calculate the percentage of performance from a general plan?

Now we are complicating the problem. Let`s suppose: we need to compare separately each actual indicator with respect to the overall delivered plan for all regions. The task in view, illustrated below in the picture visually:

This time to the regions do not have a column with its own plan. Instead, the «Parts» column immediately follows, where each sales indicator is compared with the overall plan specified in the cell E2. The formula in the «Parts» column this time is as follows =B2/$E$2.

Download calculate percentage fo plan execution in Excel.

Note that in the denominator of the formula uses the absolute reference to the cell $E$2. The dollar symbols are indicated to us, that the reference to the cell with the value of the general plan is blocked. Because of this, it does not change when you copy the formula to other cells in the «Parts» column. In cell C6, we sum all the percentages to ensure the accuracy of the result. As we see again and in the second figure, we obtained the same over fulfillment of the general plan – is 105%. The final values in percentages were coincided, so all the calculations of the formulas are correct.