How to Calculate Budget Variance in Excel – 3 Steps

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):

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

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

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

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