Over at the Excel Community, we have just completed a five-part series that started with several separate workbooks, each containing simple lists of nominal codes and balances…
…and ended up with a set of formatted management accounts:
Our aim was to completely automate the process, so that we could just add new or updated workbooks to a particular folder and our formatted accounts would be updated to include the new figures without any manual intervention beyond just opening the workbook containing the final accounts.
We used the Power Query tools (now a built-in part of Excel 2016) to consolidate our separate tables of data and summarise the values by nominal code categories using a TB structure table. This started off just as a means of allocating individual nominal values to the summary categories used in our management accounts, but ended up also including attributes used to fully automate the formatting of our accounts:
Building on the proven format of the TV reality show, each of the five posts ended with a public vote. Here's a quick overview of the process together with the voting results. Note that, for some of the votes, the sample size may have fallen below the level of statistically significane and that the links included are to Excel Community posts. Details of the community and how to join are shown in the note at the end of this post.
Part 1 introduced the objectives of the series and set out the background of the Power Query tools. The vote asked the question: What is your current experience of the Power Query tools? The results indicate that less than 20% of votes currently used the tools at all, with the remaining voters expressing interest in their use:
Part 2 compared different methods of consolidating the data from our separate trial balance worksheets and asked the specific question: What is the best way to consolidate separate ranges of data, of varying length, into a single table? Perhaps surprisingly, support for the use of Power Query was unanimous (although the sample size was modest):
Part 3 showed how to completely avoid ever having to use the notoriously error-prone Excel VLOOKUP() function again. The vote pitted Power Query against VLOOKUP() and the result was a 'thumbs down' for lookup:
Part 4 took us very close to our formatted accounts target, but left some significant issues unsolved, such as the creation of our totals without the use of cell-based formulae. Our vote asked whether we were going to be able to resolve these in the final part. The result showed commendable confidence in the abilities of the Power Query approach, with 100% of the one voter going for 'Yes'.
Part 5 justified the confidence of our voter as we ended up producing our formatted accounts without using any cell-based formulae. Once again, the result of our vote suggested acceptance of a move away from the old-fashioned way of using Excel:
Of course, the results suggesting an imminent revolution in the way Excel is used could well have been skewed by the membership of the Excel Community – perhaps the wider IT Faculty membership will redress the balance in favour of the likes of VLOOKUP() and SUMIF() – the votes are all still open…
This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business. There is also an online forum where you can ask questions and share ideas with other community members.
Thanks for all the comments. I'm glad to have provoked some discussion on the subject.
David having mentioned the ICAEW Academy courses, I feel it only fair to mention that I also run courses and undertake consultancy on Power Query and the associated tools.
Final comment is that I recognise the desire to keep using excel and we will see how this space develops over the years. Personally I would come at this from the perspective of the sort of insight you think you would like to get from your accounts, extensible the solution is and how easy it is to share. I've used Power BI for some time now and whilst I would not suggest that I've found an out and out replacement for excel it does offer a level of interactivity, robustness of solution and audit trail that is at least interesting/compelling.
That said you will create slightly less of an excel beast using the power query route that then traditional vlookup/sumif copy paste with a smattering of vba plus linkup to xlsx/xlsm/xls options.
Malcolm, regarding your point on late journal entries the best approach to that would be to create a datasource, potentially within excel which expressed the late journals, updating the NL codes during the power query load process.
I can't think why you would be using other tools to bring data into power query given that it is an extract, transform and load product with access to as many sources as you can think of, including the web, be it the internal or external.
Hi Marcus, I'm very much a visual learner, however the best resource reference tool for learning about power query (known as M) is the book www.amazon.co.uk/.../1615470344 which I had to read from cover to cover a number of times.
An interesting set of articles in bringing Power Query to the attention of members. I think there will remain a use for formula, not least, to take this example, if there needs to be a facility for late journal entries modifying the draft accounts. A more complex example might be investment pricing using Black-Scholes: I suspect it could be quite a challenge to do that without formulae or VBA.
I've been also experimenting with the next interation of PQ - using the Excel BI tools to bring external data into these type of queries e.g. automated currency conversion and getting basic investment prices. That, though, I find considerably clunky at present and wondered what other people's experience of BI is.