Automating management accounts with Power Query

Power Query gives you many more tools beyond standard Excel for automating management accounts. This post sets out some points to consider. It is not intended as a step-by-step guide, because one size does not fit all, and the path you take will vary depending on what you want to report and your source data.

However, the key message is that Power Query makes complex and lengthy transformations really easy, and completely repeatable. Whatever you want to do, you can do it better with Power Query.

Start with the outputs

Power Query is an add-on to Microsoft Excel, and as such, the Twenty Spreadsheet Principles apply to Power Query models and the process of automating management accounts.

In particular, point (8), “Design for Longevity” and point (9) “Focus on the Required Outputs”.

Power Query is going to give you a whole lot of new toys to play with, but it’s important to think through what you want your planning, reporting and analysis processes to look like.

How many reports do you have to produce? How often? Is it just at organisational level or do you have to work at budget holder level? Do you want budget holders to be able to drill into transactional detail?

Think about your budgeting and forecasting requirements as well as your reporting requirements. Over what period(s) do you need to forecast? How will this data be gathered and presented? How often do you reforecast?

What comparators do you want to present – prior year, budget, both?

Overview of the Power Query approach

Once you have decided when you want to end up, you then need to design a model to get you there.

The process for automating management accounts in Power Query looks something like this:

1. identify your input sources. Typically this will be your actuals, i.e. what you have actually spent, some kind of budget information, and a chart of accounts to translate all of this into reporting categories
2. Set up separate queries for all of your input sources, designed to get them into a consistent format, and then Merge Query to produce one data table containing budget and actual data
3. Create management accounts from this master table. You have a number of options here which are discussed further on in this post.

Actuals

Actuals should come directly from your accounting system. Using Power Query’s “connect to database” option, you can, in most cases, connect directly into the data tables of your accounting system, without needing to run separate reports. This makes automating management accounts much simpler and more robust.

You can extract the data at transaction level or at trial balance (account code level). I tend to do the former unless it’s a really really big data set. After all, with Power Query, it’s easy and quick to summarise large amounts of data.

If for some reason you cannot connect directly to the database, you will need to settle for the next best thing, which is a report that you can run quickly and export into Excel. You can then point your query to this spreadsheet.

Budgets

Before I discovered Power Query, I always found incorporating budgets (and forecasts) the hardest part of the management accounts process.

If you’ve done management reporting, the chances are you will have, at some point in your life, have dealt with an enormous nasty “Master Budget” spreadsheet, which crashes half the time and no one else can read or use. It can be therefore be a very involved process to update forecasts, because the management accountant has to spend time copying budget holder data into the master template, perhaps from more usable templates.

Power Query makes it easy to combine data from multiple source files, so you can design a basic template for budget holders to use which makes their life easier. You can then gather all the templates in one place, point a Power Query at the folder, and create a master budget file from all of the individual templates. Even better, if you carry out a reforecast exercise or want to work on next year’s budget – just copy across to a new folder and point the same query at it. [UPDATE: I’ve written a more detailed blog post about this here.]

Another cool trick with Power query is the magic “Unpivot” function which transforms data like this….

Budget line	Apr	May	Jun
Widgets	124	197	144
Oojamaflips	83	73	94
Thingummies	109	76	148

To this, with just one button click:

Budget line	Month	Amount
Widgets	Apr	124
Widgets	May	197
Widgets	Jun	144
Oojamaflips	Apr	83

You can design all sorts of templates to help make your budget holders’ lives easier, for example, templates that automatically phase expenditure based on a profile that you set. You can also use Power Query to pre-populate templates with actuals.

Chart of Accounts / Reporting categories

A chart of accounts table is invaluable in translating accounts data and budget data into a format for reporting. For example, you might just want to report Sales, Cost of Sales, Staff costs and Other Expenses. Using a chart of accounts table, you can map budget lines and actuals into the categories you want to report against.

In “legacy” Excel, you would have to do this with VLOOKUPs, which take up large amounts of processing power and are not always the easiest to use. Power Query lets you “merge queries” which means you can merge chart of accounts, budgets and actuals to create a combined data table.

Calendar / Period mapping

Increasingly I also incorporate a calendar or a list of periods into my workings. You don’t necessarily need this, as it is possible to do all sorts of wonderful date transformations in Power Query. For example, you can add a column to automatically add the month end date to any transaction date. So if all you’re doing is reporting monthly data or even year to date by month, you don’t need this.

However, if you are trying to forecast across a range of periods, having this table is a useful helper. I like to use a quarterly rolling forecast approach. This means that I may be presenting future data in a combination of months, quarters and years. It feels like an ugly hack, but the easiest way to do this is to have a mapping of specific months to quarters and financial years and also create a “reporting period” column. That way I can choose to report a future date as a month in its own right, or as part of a quarter, or as part of a financial year. And what’s more, I can update it by changing a couple of cells.

How to transform the raw data into management accounts

There are a number of options here. If you are just producing one set of accounts at organisational level, you can use Power Query to further group your data, calculate variances etc.

If you need to produce several levels of report (eg whole organisation and budget holder level) you may want to look at Power BI for presenting the data. Power BI is free to download and play with, but you will need a subscription if you want to share it with other members of your organisation.

If you have to create multiple reports, but are stuck with using plain old Excel, then pivot tables are a good place to start. You can either create individual pivot tables for each area you need to report, or you can create one big ugly pivot table and then extract the data into individual reports using GETPIVOTDATA formulas.

Going beyond accounting data

The scenario I’ve outlined above is for a pretty vanilla set of management accounts. You may want your accounts to incorporate other numerical data or other performance data. Again, there are so many things you can automate with Power Query. For example, if you always wanted to show the top five referral channels, you could write a query that would take a list of all referrals and automatically produce the top five.

Want to find out more?

I offer 90 minute online power query demonstrations, which illustrate some of the points made in the post. I can also work in more detail with you to help you design the solution you need for your business, and teach you the necessary Power Query that you will need along the way. Please have a look at my power query page for more information.