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, change 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.


Download Practice Workbook

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


3 Simple Steps 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!

Calculate Budget Variance in Excel


Step 1: Create Dataset with Proper Information

  • 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.

Calculate Budget Variance in Excel

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

Read More: Budget vs Actual Variance Formula in Excel (with Example)


Similar Readings


Step 2: Calculate 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

Calculate Budget Variance in Excel

  • 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.

Calculate Budget Variance in Excel

  • 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.

Calculate Budget Variance in Excel

  • 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.

Calculate Budget Variance in Excel

Read More: How to Calculate Variance Percentage in Excel (3 Easy Methods)


Step 3: Calculate Budget Variance from Previous Year Data

  • 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

Calculate Budget Variance in Excel

  • 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

Calculate Budget Variance in Excel

  • 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.

Calculate Budget Variance in Excel

Read More: How to Do Variance Analysis in Excel (With Quick Steps)


Things to Remember

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

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. We, the Exceldemy team, are always responsive to your queries. Stay tuned and keep learning.


Related Articles

Wasim Akram

Wasim Akram

Hi! my name is Wasim Akram. I am a graduate in mechanical engineering from Ahsanullah University of Science & Technology. I am passionate about learning new things and writing articles. I write article about Microsoft Excel and it's my favourite thing to do.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo