Supercharge Excel with power tools: Automate management accounting part 2by
Simon Hurst concludes his two-part series on automating management accounts.
In the first part, we went step-by-step through the process of combining trial balance totals in three separate workbooks and then summarising the totals using a separate coding chart.
Having used Power Query to deliver an Excel Table containing the summarised totals we want to work with, we are now going on to use those totals to create a flexible set of management accounts.
The files involved are available here, including a file showing where we should be at the end of part one:
The Exeter workbook has not been needed just yet but will be featured towards the end to test how automatic our approach is.
As stated in the Part one introduction, for convenience, we will refer to Power Query throughout, but Excel 2016 Get & Transform and Power BI Get Data operate in a similar way. It's also worth mentioning that Power Query is updated monthly. If what you see differs from what is shown and described, it might be due to the version of the Power Query tools that you are using.
If you followed the part one steps and ended up with a Consolidation Summary that contains the summarised table then you can use this. Otherwise, download Consolidation Summary Part two.
As we mentioned at the end of part one, we can use a SUMIFS() function to extract the total of whichever category name we type into a particular cell:
Because we are just using a single criteria range, we could also use SUMIF() but, as long as compatibility with Excel 2003 and earlier isn't an issue, SUMIFS() does everything that SUMIF() does with the benefit of being able to work with up to 127 pairs of criteria range and criteria rather than just one:
We can create the references to the ranges within our Table of data either by dragging to select the required ranges, or by starting to type the name of our Table. Excel AutoComplete will display Table names that match the characters that we type (together with any matching functions or Range Names). Once we have selected the AWeb_data Table, we can type [ to display the list of Table columns and 'modifiers'. We just need to choose the Category column in this case:
Creating the management accounts
We can now go to our skeleton set of management accounts and add our SUMIFS() formulae. Before we do so, we could consider our category descriptions in column A. We will be using these in the SUMIFS() function to match against our Category column.
In order to make it easier to enter the correct description, and to reduce the possibility of error, we could select all the description cells and set Data Validation up to allow the entry to be selected from a list. We can use the Category column of the CodeChart Table as our list but, in order to ensure that our Data Validation Source expands as the Table expands, we need to allocate a Range Name to our Category cells:
Here, we have selected B2:B14 and typed the name CategoryList into the Name Box before pressing Enter to accept the name.
We can then select our description cells, using Control+Click to select non-adjacent cells, and use Data Ribbon tab, Data Validation to set 'Allow:' to List and set the Source to =CategoryList:
We can now go to our Sales value cell, E5, and enter our SUMIFS() function:
Because our descriptions are all in column A, but our values are in columns D and E, we have made the column part of our reference absolute by preceding the column letter with a dollar sign. We can then copy this formula to all of our value cells:
We can enter our totals using normal sum functions and choose appropriate number formats.
Adding more workbooks
As promised in the previous part, we will test how automatic the process we have created is by adding our fourth file to the Aweb data folder.
Having saved Exeter.xlsx to the folder containing our other three files, we go to our summary workbook and just click on the Refresh All button in the Connections group of the Data Ribbon tab. In fact, we could make the process even more automatic. If we right-click on each of our queries and display the query properties, we can set the queries up to refresh whenever the workbook is opened or at a timed interval. This would remove the need to click on the Refresh All command and make the process completely automatic.
Whichever way we trigger the refresh, it runs through all the steps in our queries, bringing in and summarising our additional workbook's data. The SUMIFS() formulae then automatically include all the cells in each of the relevant columns of the consolidated data table:
We can adapt our SUMIFS() function to include an additional pair of criteria range and criterion to show the results for the individual branches with the name of the branch being entered as the column heading:
Simon Hurst has been consulting, training, lecturing and writing on Excel and other Office software for a while now. He has written a book to help accountants (and others) get the most out of Excel: Essential Excel for Accountants (and others). He also has a very elderly website that includes some useful Excel hints and tips to learn in your lunchtime.