Help and ideas for busy numbers people
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.
Continue reading “Multiple forecasts with Power Query”
“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.
Continue reading “Why is month end such a hassle?”
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.
Continue reading “Automating management accounts with Power Query”
I’ve written more generally about how to have a smooth year end accounts process. This is a more specific piece about how to build an audit-friendly year end accounts model.
I’ve included examples for download to illustrate the principles.
Continue reading “Preparing a year end accounts model”
I’m ashamed to admit I’m one of those who initially thought of Power Query as being a toy for hardcore financial modelling, or manipulating enormous data sets within multinational companies.
This may be because these are often the sort of examples provided. It’s very easy as a busy finance professional in a small or medium finance team to assume based on this that there are better things to spend your time on.
It was only after my second attempt at learning Power Query that I truly grasped how revolutionary it could be for all finance professionals and teams. If you use Excel to do anything at all, the chances are Power Query will allow you to do it better. In this article I’ve set out four areas where knowledge of a bit of Power Query could transform your professional life.
Continue reading “Why you need Power Query even if you don’t use Big Data”