Wasim Akram
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”.
Read More: Budget vs Actual Variance Formula in Excel (with Example)
Similar Readings
- How to Calculate Coefficient of Variance in Excel (3 Methods)
- Calculate Mean Variance and Standard Deviation in Excel
- How to Calculate Variance Using Pivot Table in Excel (with Easy Steps)
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/C6Where,
- 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.
Read More: How to Calculate Variance Percentage in Excel (3 Easy Methods)
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.
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.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
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.
