You can be able to use Excel software to compare the actual amounts spent versus corresponding budgeted amounts per category easily. Once you have computed the actuals and budgets, then it is time to compare the two. Excel can do all these for you in the following easy steps.
In this tutorial, we shall look at how to compare actual vs budget.
Comparing actual vs budgeted amount using bar graph
This is one of the neatest and most presentable ways that you can compare actual and budgeted amounts. Say the following values are targeted values and actual values for the Guangzhou-based company.
We can compare the actual vs budget as follows.
1. Select all the original data tables in an open workbook except the variance and grand total.
2. Click on the Insert tab of the main menu ribbon.
3. In the Charts group, click on the Insert Column or Bar chart group options
4. A pop-up screen Insert column or bar chart appears.
5. Select Clustered column on the pop-up screen
6. Click the Total bars in the chart area. Then double click, and the Format data series menu pops up on the right of your Excel Workbook
7. Under the Format Data Series, select the series option.
8. Under the series option, choose Secondary Axis. Then reduce the gap width to make the Actual value series become distinct from the budget value series.
9. Adjust the chart features using the chart design, format, and Format data series pop-up menu until you get the best comparison tables.
Comparing actual vs budgeted amount using bar and marker chart
You can use both lines and bar charts from comparing an actual and budgeted amount in Excel. The bar chart will represent the budgeted amount, while the line graph will represent the actual amount.
1. Select all the original data tables in an open workbook except the variance and grand total.
2. Click the Insert tab of the main menu ribbon.
3. Navigate to Charts group, then click on the Insert Column or Bar chart group options
4. A pop-up screen will appear.
5. Select Clustered column on the pop-up screen
6. Click the Actual amount bars in the chart area. Then right-click and select Change Series Chart Type…
7. A pop-screen Change Chart Type menu appears.
8. In the pop-up Change Chart Type menu, under the Choose the chart type and axis for your data series box, change the Actual Amount chart type from Clustered Column to Line with Markers.
9. Select the Line with Marker (Actual amount) in the chart area, and right-click on it. A pop-up menu appears, and on it, click Format data series.
10. A format Data series pane appears on the left. On it, select Fill & Line tab. Under the Fill & Line tab, select line and choose No line.
11. You can now go to the Marker section. Check the Built-in section under the markers option group. From the Type drop-down list, choose a line
11. Go to the Marker section again in the Built-in section under the markers option group. From the size, you can change the size as you require.
12. You can now add the title etc. There you have compared the actual vs budget in Excel
How to calculate the percent of actual vs budget in Excel
Sometimes you may set a budget, but when it comes to implementing it, you may end up overspending due to Unavoidable circumstances such as price hikes. The best way to visualize such a difference is by using Percentage e ad on an excel template. In the tutorial above, we have seen how to calculate to compare actual vs budget. Now, all we need to do is convert the difference into a percentage.
Follow these simple steps to calculate the percent of the actual budget vs the budget amount.
Step 1: Create five different columns and label them Budget list, Actual Amount, Budget, Variance, and percentage, respectively.
Step 2: Write the budget items under the budget list column.
Step 3: Create a Grand Total Row.
Step 4: Write your budget.
Step 5: Write the actual amount that you have sent on these items.
Step 6: Subtract the budget from the actual amount in cell D4 and drag the formula to the remaining cells to auto-populate the variance automatically.
Step 7: To find the percentage of actual vs budget, we are going to implement the following formula under the percentage section.
Percentage difference = actual – budget /budget * 100
Actual minus budget is equal to variance on our excel template. Therefore will just divide the variance by the budget and multiply it by 100 to get the percentage difference.
Step 8: Drag down the formula to auto-populate the remaining cells. You can apply conditional formatting to improve the visual appearance.
Step 9: Calculate the grand totals, including the percentage total. That’s how to calculate the percent of Actual Vs budget in Excel.
Download the percent of actual Vs Budget Excel Template
Excel percent of Actual Vs Budget Template
Monthly Budget Vs Actual Excel Template
Download a blank template of monthly vs actual Excel template. Fill in your monthly income, and the difference will automatically populate.
How to create a monthly budget in Excel
Excel is good software for planning personal finance. You can take advantage of its formula, cells, rows, and columns to create a budget template. The best thing is that the formulas can auto-populate the differences between your income and your budget every month. In this section, I will guide you on how to create a month-after-month budget that runs from January to December.
Steps to create a budget
1. Have a financial goal
It is easy to create a budget and adhere to it if you have a financial goal. Examples of financial goals include early retirement, buying a luxury car, moving to a dream home, paying student loans, or taking a vacation trip.
2. Set the budget period
Budgets are planned either monthly or annually. It’s easy to plan a monthly budget since you can include day-to-day expenses.
3. List down your sources of income Budget
Before creating a budget, it’s important to list down all your sources of income. This will make sure that every coin is budgeted.
4. Create an Excel budget
Follow the steps below to create a monthly budget using Excel.
Step 1: Write the title for your budget.
Step 2: Write a subtitle for Income sources [Use large fonts, e.g., 20 and bold]
Step 3: List all the income sources below the subtitle
Step 4: Label the last row after income sources as total
Step 5: Skip one row and label the next one as Expenses [bold and use large fonts]
Step 6: List all your expenses and add a totals row at the end.
Step 7: Add the months from January to December on the second row right below the title starting from B2.
So far, your budget template should look like this.
Step 8: Input some data to find out how it works
Step 9: Use the sum function in cell B7 to calculate the total income for that month. For example, in my template, I will use =SUM(B4:B6) because my income sources are spread within that range.
Step 10: Drag the formula to the rest of the months until December
Step 11: Enter the sum function into cell O4 to help automatically calculate the total earnings of the income sources that year. Type in =SUM(B4:M4) and press enter.
Step 12: Drag the formula to apply it in the remaining cells under the income sources section
Step 13: Let’s enter some information under the expenses section. Once you add the expenses, use the SUM function to add the expenses under the total row.
Step 14: Add the totals on the year side.
Step 15: Add a new row below to show the difference between the income and expenses of every row. Subtract the totals of expenses from the totals of income. You can name this row Extra/Short.
Lastly, apply conditional formatting to highlight the cells with less than Zero in red. In addition, you can set a conditional formation to highlight the cells with more than zero using green color. The extra and short row helps to compare the actual vs budget in Excel.
Final Thought
Budgeting helps people to save money for rainy days. The discipline of saving starts by drafting an Excel budget template. Excel is a great platform for customizing budget goals, as t comes with conditional formatting and auto-sum features. We have provided you with free and customizable templates to help you achieve your dream of financial freedom.