Budget vs actuals: create an automated report on Excel [Free Template]

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.

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

Why 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, which usually takes place on a monthly basis using a lot of manual effort. But using the built-in Power Query feature, the reporting procedure can be a breeze.

What you’ll learn in this tutorial

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 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 this Excel file to follow along with the tutorial detailed below.

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:

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

Budget:

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

COA (Chart of Accounts)

The Chart of Accounts shows all the accounts that can be used at an Actual level and the accompanying level to be used 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.

Converting the dataset

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

Importing the data in Power Query
Transforming the data in Power Query
Loading the transformed data to the Data Model
Creating a calendar table
Creating relationships between the tables
Setting up measures
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 same fashion as the first table.

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

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.

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 righthand 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 righthand 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, you will need to 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 you 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 as 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.

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. Tell us in the comments: Will you be using this tutorial for your next analysis?

Tony De Jonker

Tony de Jonker is a consultant specialized in finance, business support, and all things Microsoft Excel.

Category: Tutorials

Tagged: budget Microsoft Excel reports