How to Calculate Budget Variance in Excel (with Quick Steps)

Budget Variance is the difference between the actual amount and the forecasted amount. It helps businesses to analyze reasons for failure, and success ratio in the upcoming year. There are various causes behind this budget variance. Of them, the main reasons are inaccurate budgeting, changes in cost, employee fraud, and changes in the world economy. Most business owners like to know the effects of these circumstances on their businesses. For this reason, it’s very important to calculate budget variance with proper data. In this article, I am going to share with you how to calculate budget variance in Excel.

In the following article, I have described 3 simple and easy steps to calculate budget variance in Excel.

Suppose we have a dataset of a company’s Actual Revenue and Budget Revenue for all types of businesses. In addition, we have the revenue for the previous year. Now we are going to calculate the Budget Variance for this year and the previous year. Stay tuned!

Step 1: Creating Dataset with Proper Information in Excel

First, let’s create a dataset. In order to do that we will add the actual revenue column with the previous year’s generated revenue because to calculate the variance, we will need the difference between these two revenue.

Second, we will add “Budget Variance” and “Percentage Variance” on both sides as we are calculating “Budget vs. Actual” and “Actual vs. Previous Year”.

Step 2: Calculating Budget Variance Using Excel Formula

Now we will calculate the budget and percentage variance by applying simple formulas.
As the budget variance stands for-

Actual Revenue – Budget Revenue

Apply the following formula in the selected cell (E6)-

=C6-D6

Therefore, hit the Enter button to continue.
Hence, pull the “fill handle” down to fill all the cells.

Thus we will get the budget variance calculated for all the services of the company.

This time we will calculate the percentage variance. To do so-
Apply the following formula in the cell (F6)-

=E6/C6

Where,

Percentage Variance=Budget Variance/Actual Revenue.

Therefore, press the Enter button and drag the “fill handle” down.

Finally, we have the budget variance and the percentage variance in our hands. Moreover, to make the data lucrative I have used conditional formatting. You can check this link to make yours.

Step 3: Calculating Budget Variance from Previous Year Data in Excel

In this final step, we will compute the budget variance and percentage variance using the same formulas from the above step.
Above all, choose a cell. Here I have chosen cell (J6) to apply the formula.
Similarly, write the formula down-

=H6-I6

After that, click the Enter button and drag the “fill handle” down.
In summary, you will get the budget variance differing from the previous year.

In the same fashion, select a cell (K6) to put the formula down to determine the percentage variance-

=J6/H6

Now, we have calculated the percentage variance with precision.

In conclusion, our final table will look like the following screenshot where we have calculated the budget variance successfully.

Things to Remember

You can use the “Format Cells” feature to make the output more attractive. Check out this link to learn more.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Calculate Budget Variance.xlsx

Conclusion

In this article, I have tried to cover all the steps to calculate budget variance in excel. Take a tour of the practice workbook and download the file to practice by yourself. I hope you find it helpful. Please inform us in the comment section about your experience. Stay tuned and keep learning.

<< Go Back to Calculate Variance in Excel | Excel for Statistics | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!