Budget vs Actual Variance Formula in Excel (with Example)

Get FREE Advanced Excel Exercises with Solutions!

In our daily lives and business purposes, creating a budget is necessary. But, the actual amount might vary with the budget. This variation is determined and analyzed easily using the variance calculation. This article will describe the budget vs. actual variance formula in Excel along with charts.


What Is Variance Formula?

Actual variance is the difference between the actual amount and the budgeted amount. It helps people to learn if he is in profit or loss in business. Moreover, it represents the amount of loss or profit that one has faced.

Basically, you can calculate variance using two formulas. One is for calculating the actual variance, and another is for calculating the percentage variance. As we are calculating the actual variance here, the formula would be:

Actual Variance = Actual – Budget

In case you want to calculate the percentage variance also, the formula would be:

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

An Example of Budget vs Actual Variance Formula in Excel

We have monthly budget amounts and actual sales amounts for a shop in our dataset.

Therefore, we can calculate and depict the budget vs actual variance formula in Excel very easily. Follow the steps below to accomplish this.

Budget vs Actual Variance Formula in Excel


📌 Step 1: Go to Variance Cell

First and foremost, click on the E5 cell as you want your variance calculated here.


📌 Step 2: Write Excel Formula for Variance

Subsequently, put an equal sign (=) and write D5-C5. Following, press the Enter button.

Budget vs Actual Variance Formula in Excel

Read More: How to Calculate Semi Variance in Excel


📌 Step 3: Copy Formula for All Cells

Now, you can find the variance for this month. Next, place your cursor in the bottom right position of the cell. Following, the Fill handle arrow will appear and drag it down to copy the same formula dynamically to all the cells below for all the months.

Budget vs Actual Variance Formula in Excel

Thus, you can find the budget vs. actual variance in Excel for all months. To sum up, the result sheet will look like this.

Budget vs Actual Variance Formula in Excel

Read More: How to Calculate Budget Variance in Excel


How to Create a Monthly Budget vs Actual Variance Chart

In addition to the budget vs. actual variance formula, you can also make an actual variance vs. month chart in Excel. Follow the steps below to accomplish this.

Steps:

  • First and foremost, select the Month column and the Actual Variance column by holding the CTRL key on the keyboard. Subsequently, go to the Insert tab >> click on the Insert Column or Bar Chart icon >> select the Clustered Column chart.

  • Thus, you can create the Actual Variance chart vs. Month chart, where the X-axis represents the month and the Y-axis represents the actual variance.

  • But, as you can see, the graph is scribbled and not so charming to see. So, you can add some edits to the graph for a better and more charming look.
  • To do this, click on the chart area first. Following, click on the Chart Elements icon on the right side of the chart. Subsequently, untick the Axes and Chart Title option and tick the Data Labels option.

  • This will make your chart less scribbled and more attractive. Now, for better visualization, you can change the color of positive variance and negative variance.
  • To accomplish this, right-click on any column of the chart. Following, choose the Format Data Series… from the context menu.

  • This will open a new ribbon named Format Data Series on the right side of the Excel file.
  • Subsequently, 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.

  • As we have chosen green as the first color and red as the second color, our chart will look like this.

  • Moreover, we can widen the columns for better visualization of the whole chart. To do this, just like the 6th step, access the Format Data Series ribbon again.

  • Following, click on the Series Options icon >> Series Options group >> reduce the Gap Width using the arrow buttons. Say, we make it 100%. And the chart will look like this.

Thus, you can create a budget vs. actual variance formula and an actual variance vs. month chart using the results.

Read More: How to Create Minimum Variance Portfolio in Excel


Download Sample Workbook

You can simply download and practice from our workbook here for free.


Conclusion

So, I have shown you the budget vs. actual variance formula along with the actual variance vs. month chart in Excel. Go through the full article carefully to understand it better and apply it afterward according to your needs. I hope you find this article helpful and informative. If you have any further queries or recommendations, please feel free to contact me. Thank you!


Related Articles


<< Go Back to Calculate Variance in Excel | Excel for Statistics | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Tanjim Reza
Tanjim Reza

Md. Tanjim Reza Tanim, a BUET graduate in Naval Architecture & Marine Engineering, contributed over one and a half years to the ExcelDemy project. As an Excel & VBA Content Developer, he authored 100+ articles and, as Team Leader, reviewed 150+ articles. Tanim, leading research, ensures top-notch content on MS Excel features, formulas, solutions, tips, and tricks. His expertise spans Microsoft Office Suites, Automating Finance Templates, VBA, Python, and Developing Excel Applications, showcasing a multifaceted commitment to the... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo