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.
–
This article is part of our series Spreadsheet Quick Wins for Managers, where we invite spreadsheet pros to share actionable tips and tutorials that managers can start using today. Don’t miss our other Microsoft Excel tutorials on 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
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.
On the right-hand side, you will see a Queries & Connections Panel with the table just loaded.
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.
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.
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.
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.
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.
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.
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.
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
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
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.
Use the following settings:
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.
The dynamic Pivot Table including the timeline looks as follows:
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?
Leave a Reply