The dataset showcases a company’s Actual Revenue, Budget Revenue and the revenue of the previous year.
To calculate the Budget Variance for this year and the previous year:
Step 1: Creating a Dataset
- Create a dataset. Add the actual revenue column to calculate the variance.
- Add “Budget Variance” and “Percentage Variance” on both sides to calculate “Budget vs. Actual” and “Actual vs. Previous Year”.
Read More: Budget vs Actual Variance Formula in Excel
Step 2: Calculating the Budget Variance Using an Excel Formula
- Enter the following formula in the selected cell (E6):
=C6-D6
- Press Enter.
- Drag down the Fill Handle to see the result in the rest of the cells.
The budget variance is calculated.
Calculate the percentage variance:
- Use the following formula in F6:
=E6/C6
- Percentage Variance=Budget Variance/Actual Revenue.
- Press Enter.
- Drag down the Fill Handle to see the result in the rest of the cells.
- The budget variance and the percentage variance is displayed. Use conditional formatting: click this link.
Read More: How to Calculate Semi Variance in Excel
Step 3 – Calculating the Budget Variance from Previous Year Data
- Choose a cell. Here, J6.
- Enter the formula down.
=H6-I6
- Press Enter.
- Drag down the Fill Handle to see the result in the rest of the cells.
This is the output.
- Select K6 and enter the formula to determine the percentage variance:
=J6/H6
The percentage variance is calculated.
This is the final output.
Read More: How to Create Minimum Variance Portfolio in Excel
Download Practice Workbook
Download the practice workbook.
Related Articles
- How to Calculate Portfolio Variance in Excel
- How to Calculate Variance of Stock Returns in Excel
- How to Do Price Volume Variance Analysis in Excel
- How to Calculate Schedule Variance Using Excel Formula
- How to Calculate Variance Inflation Factor in Excel
<< Go Back to Calculate Variance in Excel | Excel for Statistics | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!