How to Create Actual Vs Budget Variance Reports in Excel

In our personal and industrial financing, it is so much needed to forecast our profits and costs. But things may turn out differently from the forecast in reality. This difference can be calculated as variance which contributes as a significant factor in analyzing the companyโ€™s growth. In this article, I will show you all the steps to create Actual Vs. Budget Variance Reports in Excel.


Download Sample Report

You can download our practice workbook from here for free!


What Is Actual Vs Budget Variance?

Variance is the difference between the budget amount and the actual amount for a particular month/year. Through this calculation, you can determine if a company is in profit or loss. You can also calculate the percentage variance by dividing the variance by the actual amount. So, the required formulas would be like:

Actual Variance = Actual โ€“ Forecast

Percentage Variance = (Variance / Actual) * 100%


Steps to Create Actual Vs Budget Variance Reports in Excel

Say, we have a dataset of a companyโ€™s sales revenue and cost forecast.

Given Monthly Forecast Data

In addition to this, we also have the actual sales revenue and cost for the company for a month in our hand.

Actual Sales Revenue and Cost Dataset

Now, we want to create an actual vs budget variance report in Excel from these given datasets. You can go through the following article to accomplish this target.


๐Ÿ“Œ Step 1: Prepare the Format of Actual Vs Budget Variance Report with Proper Dataset

First and foremost, you need to prepare your dataset for the actual vs budget variance report.

  • To do this, at the very beginning, create a template of your Actual Vs. Budget Variance Report with necessary header columns and rows like the following figure.

Actual Vs. Budget Variance Report Template in Excel

  • Afterward, click on cell C5 and insert the following formula to insert value form your dataset properly.
='Monthly Forecast'!C5
  • Subsequently, press the Enter key.

Insert Data from Monthly Forecast Dataset

  • Following, place your cursor on the bottom right position of the cell.
  • Subsequently, drag the appeared fill handle below to copy the same formula for all the cells below.

Drag the Fill Handle Below

  • Thus, you will get all the budget data from your forecast dataset in column C.

All Data Inserted from the Monthly Forecast Dataset

  • At this time, click on cell D5 and insert the following formula.
='Actual Data'!C5
  • Subsequently, hit the Enter key.

Insert Data from Actual Sales Revenue & Cost Dataset

  • As a result, you will get the actual data inserted in your report accurately.

All Actual Data Inserted from the Actual Dataset

Thus, the formatting of the Actual Vs. budget Variance Report is done.

Read More: How to Prepare Budget for a Company in Excel (2 Suitable Examples)


๐Ÿ“Œ Step 2: Calculate Actual Variance

The next step is to use the formulas to calculate the actual variance of your dataset.

  • To do this, at first click on cell E5.
  • Subsequently, insert the following formula and hit the Enter key.
=D5-C5

Calculate Actual Variance in the Actual Vs. Budget Variance Report in Excel

  • Consequently, you will get the first dataโ€™s variance.
  • Now, place your cursor in the bottom right position of the cell.
  • Subsequently, drag the fill handle below upon its appearance.

As a result, you will get all the actual variances for your dataset successfully.

All Actual Variances Calculated in the Actual Vs. Budget Variance Report in Excel

Read More: How to Calculate Remaining Budget Using Formula in Excel


Similar Readings


๐Ÿ“Œ Step 3: Calculate Percentage Variance

Now, you need to calculate the percentage variance for your dataset.

  • For doing this, initially, click on cell F5 and insert the following formula.
=E5/D5
  • Subsequently, hit the Enter key.

Formula to Calculate Percentage Variance in Actual Vs. Budget Variance Report in Excel

  • Afterward, use the fill handle feature to copy the same formula dynamically for all the other cells below.

Consequently, you will get all the percentage variances of your dataset in the Actual Vs. Budget Variance Report.

Percentage Variances in Excel Actual Vs. Budget Variance Report

Read More: How to Make a Budget in Excel (2 Easy Methods)


๐Ÿ“Œ Step 4: Create a Budget Vs Actual Variance Chart and Finalise Report

Now, for better visualization and analysis, it is very helpful if you add some charts depicting your dataset summary in the report

  • In order to do this, first, create three helper columns named Positive, Negative, and Symbol & Value.

Helper Columns to Create Chart

  • Here, the Positive column will carry the positive values of the actual variances and the Negative column will carry the negative data of the actual variances.
  • Now, click on cell J5 and insert the following formula.
=IF(E5>0,E5,"")
  • Subsequently, hit the Enter key.

Formula to Carry the Positive Variances

  • Afterward, use the fill handle feature to copy the same formula for all the other data below and get the positive values shown in column J.

Positive Variances of Actual Vs. Budget Variance Report in Excel

  • Afterward, click on cell K5 and insert the formula below.
=IF(E5<0,E5,"")
  • Following, hit the Enter key.

Show Negative Variances of the Actual Vs. Budget Variance Report in Excel

  • Next, use the fill handle feature again to fill the data through column K perfectly as per the E column data.

Negative Variances of Actual Vs. Budget Variances Report in Excel

  • Afterward, click on cell L5 >> go to the Insert tab >> Symbols group >> Symbol option.

Access the Symbol Option

  • As a result, the Symbol dialogue box will appear.
  • Following, choose the option Unicode (hex) from the from: option list.
  • Subsequently, choose the Subset: option as Block Elements >> choose the Full Block symbol >> click on the Insert button.

Insert Full Block Symbol

  • As a result, the symbol will be inserted.
  • Afterward, write 2000 in cell L6.

Put the Value 2000

  • Now, click on cell G5 and insert the formula below.
=IF(K5="","",K5&" "&REPT($L$5,ABS(K5)/$L$6))
  • Subsequently, press the Enter key.

Formula to Create a Variance Chart for Negative Variances

  • Afterward, place your cursor in the bottom right position of the cell.
  • Subsequently, drag the fill handle below upon its appearance.
  • Thus, you will get the relative horizontal charts for the negative variances.

All Negative Variances Chart

  • Afterward, click on cell H5 and insert the following formula.
=IF(J5="","",J5&" "&REPT($L$5,ABS(J5)/$L$6))
  • Subsequently, hit the Enter key.

Formula to Create Positive Variance Chart in the Actual Vs. Budget Variance Report in Excel

  • As a result, you will get the positive variance chart for the first data.
  • Afterward, use the fill handle feature downward to get all the positive variances of your whole dataset.

Positive Variances Chart of the Actual Vs. Budget Variance Reports in Excel

Thus, your Actual Vs Budget Variance Report is ready in Excel. And, the final outlook of the report should look like this.

Actual Vs. Budget Variance Report in Excel

Read More: How to Create a Business Budget in Excel (With Easy Steps)


Conclusion

So, in this article, I have shown you all the steps to create Actual Vs. Budget variance Reports in Excel. Read the full article carefully and practice accordingly. I hope you find this article helpful and informative. You are very welcome to comment here if you have any further questions or recommendations.

And, visit ExcelDemy to learn about many more Excel problem solutions, tips, and tricks. Thank you!


Related Articles

Tanjim Reza

Tanjim Reza

Hello! I am Md. Tanjim Reza Tanim. I have just completed my B.Sc from Naval Architecture & Marine Engineering Department, BUET. Currently, I am working as an Excel & VBA content developer. I always had a great fascination with Microsoft Excel and its cool functions and formulas. Here, I am learning every day about new functions and formulas and working on applying MS Excel to the analysis of our real-life problems. I have great enthusiasm for learning any kind of new things, writing articles, and solving real-life problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo