Using a sliding compound mitre saw
istock_02486186_Using a sliding compound mitre saw

Supercharge Excel with power tools: Automate management accounting part 2

by
19th Apr 2017
Partner The Knowledge Base
Share this content

Simon Hurst concludes his two-part series on automating management accounts.

The plan

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:

http://www.tkb.co.uk/PQMA1.html

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.

Getting started

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:

=SUMIFS(AWeb_data[Amount],AWeb_data[Category],F11)

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:

SUMIF

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:

Autocomplete

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:

Range name

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:

Data validation

We can now go to our Sales value cell, E5, and enter our SUMIFS() function:

=SUMIFS(AWeb_data[Amount],AWeb_data[Category],$A5)

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:

Values

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:

Refresh all

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:

Individual

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.

 

Replies (5)

Please login or register to join the discussion.

avatar
By Chirantanpatel
20th Apr 2017 12:03

Hello Simon, Thanks for this article. I would like to know which accounting software is best for small real estate firms. I'm checking capterra & softwaresuggest https://www.softwaresuggest.com/us/accounting-software i'm based in india. But little bit confused which one is best for my businessess

Thanks (0)
Replying to Chirantanpatel:
Simon Hurst
By Simon Hurst
20th Apr 2017 13:34

Hi - thanks for your comment. That's not really my field I'm afraid. Perhaps someone else will be able to help. It may be best to ask your question in the business category of Any answers and to have a look at the Business section of AccountingWEB

Thanks (0)
avatar
By willywkl
28th Apr 2017 04:20

Many thanks and it is really very useful

Thanks (0)
Replying to willywkl:
Simon Hurst
By Simon Hurst
28th Apr 2017 08:07

I'm glad you found it useful - thanks for taking the trouble to add a comment.

Thanks (0)
Replying to willywkl:
Simon Hurst
By Simon Hurst
28th Apr 2017 08:10

It's always helpful to know that articles are useful or otherwise.

Thanks (0)