Budget vs Actual Variance Formula in Excel (with Example)

What Is Variance Formula?

Actual variance is the difference between actual expenditure and budgeted expenditure.

We can calculate variance using two formulas; one for calculating the actual variance, and another for calculating the percentage variance.

The formula for actual variance is:

Actual Variance = Actual – Budget

For the percentage variance, the formula is:

Percentage Variance = [( Actual/Budget )-1] × 100 %

We have monthly budget amounts and actual sales amounts for a shop in our dataset below. Let’s calculate and depict the variance using this dataset.

Steps:

The variance for January is returned.

The Fill handle icon will appear.

The budget vs actual variance in Excel for all months is complete.

Now let’s plot the actual variance by month in a chart.

Steps:

Select the Month column and the Actual Variance column by holding the CTRL key while clicking on them.
Go to the Insert tab >> click on the Insert Column or Bar Chart icon >> select the Clustered Column chart.

A chart is generated where the X-axis represents the month and the Y-axis represents the actual variance.

Let’s add some formatting to the graph to make it more presentable.

For better visualization, let’s change the color of positive variances and negative variances.

A new ribbon named Format Data Series opens on the right side of the Excel file.

Click on the Fill & Line icon >> Fill group.
Put the radio button on the Solid fill option.
Tick the option Invert if negative.
Choose two colors for a positive and negative variance from the Fill Color icons.