Mastering Budget vs. Actuals Analysis: Excel Power Query Tutorial + FREE Template

This budget vs. actuals tutorial will help financial controllers and people working with budgets set an automated report on Excel (+ free template).

Table of Contents

In this tutorial, learn how to create a budget vs. actuals report in Excel using Power Query. Gain insights and track financial performance effortlessly.

As a financial controller, accountant, or CFO, you’re likely familiar with the concept of budget vs. actuals. You know that reporting budget vs. actuals can be both cumbersome and time-consuming, given actuals are administered on a detailed level and budget numbers are recorded on a higher level. With this tutorial, however, we hope to show you it doesn’t need to be some cumbersome.

Why calculating budget vs. actuals

Managers are responsible for actual amounts spent versus the corresponding budgeted amounts per category. The actual amounts may be derived from the accounting system, while the budget amounts are retrieved from the official budget as determined and agreed upon at the end of last year for the current year. A budget is a report by cost/revenue category showing estimated numbers by month for the next year as agreed upon by management. The financial manager will report on these actual versus budget numbers, usually on a monthly basis and with a lot of manual effort. But using the built-in Power Query feature, the reporting procedure can be a breeze.

Budget vs. actual expenditure

Beebole

What’s the difference between budget and actual expenditure?

The key distinction between budget and actual expenditure is that the budget is a planned estimate of future financial activities, while actual expenditure represents the realized costs incurred in practice. The comparison between budgeted amounts and actual expenditures is crucial for evaluating financial performance, identifying variances, and making informed decisions to manage resources effectively. By analyzing these differences, organizations or individuals can assess their financial performance, adjust their future budgets, and take necessary corrective actions.

What’s the difference between budget vs. forecast vs. actuals?

In summary, the key differences are:

  • Budget: Planned estimates of future income and expenses.
  • Forecast: Updated projections based on the latest information and adjustments to the budget.
  • Actuals: The factual data representing the actual financial results that have occurred.

By comparing budgeted amounts, forecasts, and actuals, individuals and organizations can assess their financial performance, identify variances, and make informed decisions to manage resources effectively.

Is budget vs. actuals analysis and budget to actual variance analysis the same thing?

Yes, budget vs. actuals analysis and budget to actual variance analysis are essentially the same thing. Both terms refer to the process of comparing budgeted amounts to actual results and analyzing the differences or variances between them.

Budget vs. actuals analysis involves assessing the financial performance by comparing the planned budgeted figures with the actual results that have been realized. It helps in evaluating how well the organization or individual has met their financial targets and objectives.

On the other hand, budget to actual variance analysis specifically focuses on identifying and analyzing the differences or variances between the budgeted amounts and the actual results. It involves calculating the variances for various income and expense categories, understanding the reasons behind the variances, and assessing their impact on the overall financial performance.

In both cases, the goal is to understand the variations between the planned budget and the actual outcomes, which can provide insights into areas of strength, areas needing improvement, and potential issues or opportunities for financial management and decision-making.

Therefore, while the terms may have slightly different wording, they generally refer to the same practice of comparing budgeted amounts to actual results and analyzing the variances to gain insights into financial performance. This analysis could also be part of a typical finance controller KPIs dashboard.

What you’ll learn in this tutorial: budget vs. actuals with Excel and Power Query

Creating a budget vs. actuals report can involve various labor-intensive steps when using Microsoft Excel the old-fashioned way. Luckily, however, modern Excel contains BI tools such as Excel Power Query and Power Pivot, which make creating a budget vs. actuals template much more attainable.

In this article, you will learn how to set up a dynamic model on budget vs. actuals in Excel using these tools. Apart from this being a painless process, rest assured you’ll end up with an accurate budget vs. actuals report that’s generated automatically. Let’s get started!

What you need

Having access to Microsoft Excel is a must. Then, be sure to download the file below to follow along with the tutorial.

Download this free Excel spreadsheet to follow along with the tutorial.
BUDGET VS ACTUALS IN EXCEL
Download this free Excel spreadsheet to follow along with the tutorial.

Below, you’ll see screenshots for each of the tabs in the Excel file we’ll be working with. Those tabs are: Actual, Budget, Chart of Accounts, and Level.

Actual:

Source sheet for actuals
A look at a spreadsheet tab with Actuals.

This sheet is formatted as a table and is downloaded from an ERP system.

Budget:

Microsoft excel budget spreadsheet
Screenshot of the budget tab.

The budget numbers are recorded in a cross-table style, so it is easy to enter data as months go by. The user records the budget numbers at a higher level.

COA (Chart of Accounts):

COA chart of accounts in Microsoft Excel example source data
A look at the Chart of Accounts (COA), which can be used at an Actual level.

The Chart of Accounts shows all the accounts that can be used at an Actual level and the accompanying level for use in the Budget.

Level:

Table displaying hte order, type and sign to use when looking at budget vs. actuals in Microsoft Excel
This Level table shows the levels, orders, type, and sign.

This table displays the levels and accompanying Order, Type, and Sign. The Order can be used to sort the final report in the desired order and sequence. The Type shows the type of account, i.e., BS (=Balance Sheet) and PL (Profit & Loss). Only Profit & Loss accounts will be presented in the final report. Sign can be used to transform detailed Actual amounts into the correct format. Debits and Credits will be shown as positive numbers.

Step-by-step tutorial: How to create a budget vs. actuals report in Excel with Power Query

Converting the dataset

In order to create a dynamic report, we need to go through the following 7 steps:

  1. Importing the data in Power Query
  2. Transforming the data in Power Query
  3. Loading the transformed data to the Data Model
  4. Creating a calendar table
  5. Creating relationships between the tables
  6. Setting up measures
  7. Creating a dynamic pivot table

Importing the dataset

Place your cursor in the Actual table and select from the Ribbon: Data → Get & Transform Data → From Table/Range. A copy of your data will be placed in Power Query.  To import the next table, you need to exit Power Query by selecting in the Ribbon: Home → Close → Close & Load → Close & Load to … → Only Create Connection → OK.

When analyzing budget vs. actuals, importing dataset is first step
Import data from the Actual table.

On the right-hand side, you will see a Queries & Connections Panel with the table just loaded.

Queries & Connections panel with table loaded
The Queries & Connections Panel appears on the right-hand side.

Now, move to the next tables and load them in the same fashion as the first table.

When you are done, you will see four queries appearing in the Queries & Connections Panel.

Query options for budget analysis in Microsoft
Notice four queries will appear in the Queries & Connections Panel.

Transforming the budget

To transform the data, you need to return to the Power Query environment. You may do so by double-clicking on of the queries in the panel.

In Power Query, you can see all the queries by clicking on the >icon located on the left-hand side of the screen.

See how the queries in Power Query when doing budget analysis
To see all of the queries, click on the > icon.

The Navigator Pane will open, and you’ll see the four queries.

Query options for budget analysis in Microsoft Excel 1
Four queries in the Navigator Pane.

We want to create a long table for DataBudget. Proceed as follows:

  • Click on DataBudget
  • Select the first column (Level)
  • Right-click with the mouse
  • Choose Unpivot Other Columns
  • Rename header Attribute into Date and change format into Date
  • Rename header Value into Amount and change format into Decimal Number
  • Select from the Ribbon: Home → Close & Load → Close & Load and you will return to the active sheet in Excel.

Transforming the actuals

The Actuals are recorded at a detailed level, while the Budget is maintained at a higher level. To be able to connect to the Budget, you must convert Actuals to this higher level. To do so, select from the Ribbon: Home → Combine → Merge queries and select DataCOA as second table.  Click on columns Acc No in both tables. You will notice that the selection matches, and a green check mark is displayed. Click on the OK button.

Actuals must be converted to the higher level of budget
To be able to connect to the Budget, you must convert Actuals to a higher level by merging queries.

A new column will appear on the right-hand side. Click on the double arrow. Deselect all options in the dialog box, select Level, and click on the OK button.

Another step to transform the actuals in microsoft excel
Click on the double arrow, deselect all options in the dialog box, select Level, and click OK.

Since revenues are booked as credit amounts, we want to revert them to positive numbers for reporting purposes. Furthermore, we only want to see Profit & Loss accounts. We need to merge the Actuals with Level and select from the Ribbon: Home → Combine → Merge queries and select DataLevel as second table.  Click on columns Level in both tables. Notice that the selection matches and a green check mark appears. Click on the OK button.

Continue merging data to analyze budget vs. actuals
It’s necessary to merge the Actuals with Level and select from the Ribbon, as shown above.

A new column will appear on the right hand side. Click on the double arrow. Deselect all options in the dialog box, select Type and Sign, and click on the OK button.

Filter column Type on PL accounts by deselecting BS accounts.

Add a new column by selecting: Add column → Custom column. Enter the following details and click on OK.

Transforming data to do budget analysis in microsoft excel
Add a new column.

Complete the transformation by performing the following steps:

  • Change the format of the Amt column in decimal number (1.2).
  • Change the format of the Date column into Date.
  • Select the columns that you want to keep by holding down the control key: Date, Level and Amt.
  • Right-click with your mouse, and from the short menu, select: Remove other columns.
  • Move column Level to the left so it becomes the first column.
  • Change header Amt into Amount.
  • Exit Power Query by selecting: Home → Close & Load → Close & Load.

Loading data to the data model

In the Queries & Connections Panel you need to right-click on all the queries, and from the short menu, select: Load To… and then a dialogue box appears where you select: Add this data to the Data Model → OK.

Adding more data to data model in Microsoft Excel
Now, it’s time to load all of the records for each query into the Data Model.

When you are done, you will see in the Queries & Connections Panel that all the records for each query have been loaded into the Data Model.

Creating relationships between the tables

To create relations between the tables, proceed as follows:

  • From the ribbon you select: Power Pivot → Data Model → Manage.
  • Select: Design → Calendars → Date Table → New
  • Select: Design → Calendars → Mark as Date Table
  • Select: Home → View → Diagram View
  • Create the following relationships by dragging:
    • DataLevel – Level to DataCoa – Level
    • DataLevel – Level to DataActual – Level
    • DataLevel – Level to DataBudget – Level
    • Calendar – Date to DataActual – Date
    • Calendar – Date to DataBudget – Date
Budget vs actuals analysis by creating relationships between tables
Create relationships between the tables.

Creating measures

You need to create the following measures by selecting: Power Pivot → Calculations → Measures → New Measure:

  • Measure name: Type TotalActual
  • Formula: =SUM(DataActual[Amt])
  • Click on Check formula
  • Category: Number
  • Format: Decimal number
  • Decimal places: 0
  • Use 1000 separator: yes
  • Click OK
creating measures in Excel to look at budget vs actuals
Create new measures by clicking Power Pivot → Calculations → Measures → New Measure.

In the same fashion, you will create the following measures:

  • TotalBudget = SUM(DataBudget[Amount])
  • Variance=[TotalActual]-[TotalBudget]

Create a pivot table

Now we are ready to create a dynamic Pivot Table report. Select: Power Pivot → Data Model → Manage → Home → Pivot Table → Pivot Table → New worksheet → OK.

Creating a dynamic Pivot Table report to look at budget vs. actuals in microsoft excel
It’s time to create a dynamic Pivot Table report.

Use the following settings:

Settings for pivot table for budget vs actuals analysis in Microsoft Excel
Settings for the Pivot Table report.

As you can see, the calculated measures are used as values, while DataLevel – Level is used to populate the rows.

Adding a timeline

In order to view the data by month, you can add a timeline. With your cursor placed in the pivot table, choose Pivot Table Analyze → Filter → Insert Timeline → Date → OK.

How to add a timeline to budget analysis microsoft excel
Add a timeline so that you can view the data by month.

The dynamic Pivot Table, including the timeline, looks as follows:

Budget vs. actuals analysis with dynamic pivot table in Microsoft Excel
A look at the Pivot Table and the timeline.

By selecting the desired month, the accompanying numbers in the pivot table will change automatically.

Ready to analyze budget vs. actuals on your own?

By using the steps described in this article, you will create the actual vs. budget report in less time using just a few formulas. Once the source data is changed, you can simply update the report by choosing Data → Queries & Connections → Refresh All.

Implications of budget vs. actuals on project profitability

Project profitability analysis involves assessing the revenue, costs, and profit associated with a project to understand if it is financially beneficial. It primarily focuses on whether the project has or will achieve the desired return on investment.

On the other hand, budget versus actuals analysis is a method of comparing the budgeted costs of a project against the actual expenses incurred. This analysis helps in tracking the financial performance of a project, identifying variances, and implementing corrective actions.

The relationship between these two stems from their focus on project financials and performance. The results from a budget versus actuals analysis can significantly influence project profitability analysis: if actual costs are consistently over budget, it could indicate inefficiencies or unforeseen expenses, leading to lower profitability than initially projected. Conversely, if actual costs are consistently under budget without compromising the project’s output, it could indicate cost efficiency, potentially increasing the project’s profitability.

Therefore, effective budget versus actuals analysis can enable a more accurate project profitability analysis. It allows project managers to monitor costs, identify cost trends, and make necessary adjustments to ensure the project remains profitable. Similarly, project profitability analysis can offer valuable insights for refining the budgeting process, leading to more accurate and realistic budgets in the future.

If you’re a financial manager or someone who works with budgets and reporting regularly, we genuinely hope this tutorial shows that reporting budget vs actuals doesn’t need to be cumbersome or time-consuming.


Don’t miss our other tutorials on topics like Excel Power Query hacks and how to build an automated time tracking dashboard.

The experts who have written or contributed to this article are independent from Beebole, and their contribution doesn't serve as endorsement for our company/tool or their past/present organizations, employers, or associates.
Tony de Jonker is a consultant specialized in finance, business support, and all things Microsoft Excel.

Comments

Related posts

Project cost management: Adjustable forecasting and the Excel OFFSET function

Published: 2023/9/5 | Andrew Childress

Today we’re talking project cost management with adjustable forecasting and the Excel OFFSET function. Follow along below, or watch the ...

Read more

Contractor Payments and Pay Reports Made Easy with Beebole

Published: 2023/2/28 | Andrew Childress

If you have a team of contractors, paying them in a timely manner is a must. You need a speedy ...

Read more

How to Use Google Sheets Pivot Tables, Column Stats, Explore, & QUERY Function

Published: 2021/9/15 | David Benaim

As a manager, perhaps you’ve wondered how to take your financial reporting to the next level or how to amp ...

Read more