Power Query has been out for over a decade and yet the majority of accountants I meet still seem unaware of what it can do for them.
A possible issue is that it is usually presented in the context of Power BI, and analysing Big Data. And the type of people who like to talk about that also like to talk about other scary things such as VBA and SQL. There aren’t many training resources or articles focussing on Power Query for accountants more generally.
I recently set up a management accounts model using Power Query to combine multiple forecasts.
This is for a startup charity, where we need the ability to forecast at a high level over a five year period but to be able to update that quickly based on current decisions. We also need to do a detailed six monthly forecast for cash flow purposes. We also want to approve changes on a quarterly basis. Finally, we want to be able to assess the quality of our forecasting so that we can continue to improve it.
“Well, you know, it is month end”, I say, and colleagues nod sympathetically and steer clear for a while. For most non-finance types, it’s a mysterious process that seems to cause a lot of stress. For finance types, it is a beast to be tamed.
By “month end” or “month end close”, I’m referring to the process by which a month (or other period) is brought to an end in the financial system. This means trying to get the numbers as accurate as possible for some sort of eventual output. This could be management accounts, a report for a funder, a VAT return or just the sense of achievement and closure.
It means making sure you have accounted for all the expenditure and income for that month. Depending on the organisation you may also carry out certain “month end” tasks such as calculating depreciation, reconciling the bank and other balance sheet accounts.
In this post I set out a few things to consider which might make a month end less stressful.
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.
What would happen in your organisation if you stopped producing management accounts?
I’ve had to delay issuing management accounts a few of times in my professional career. (It’s funny, it feels scary even admitting that, such is the sacred status of the Month End Routine. It makes me feel like I’ve failed as a finance director.)