Factory manufacturing
istock_gerenme

Supercharge Excel part 6: Automate management accounting step-by-step

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

Simon Hurst brings together all the techniques covered in the five previous parts of the series and shows how to combine trial balance totals in three separate workbooks.

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. Now that we are starting to cover some more advanced Power Query features, 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.

You can refresh you knowledge on the five previous parts below:

The plan

The series so far has covered a range of techniques, from the simple to the more complicated. This time we will go through the process of combining trial balance totals in three separate workbooks and then summarising the totals using a separate coding chart before going on to create a flexible set of management accounts.

We will use a step-by-step approach and the files involved are available here.

If you want to follow each step, download the Bristol, Bath and Taunton workbooks and place them into an empty folder. We have named our folder AWeb data. The Exeter workbook is not needed this time but is a tantalising glimpse of what the next episode will bring.

Download the consolidation summary workbook containing the CodeChart Table and put it anywhere except in the same folder as the other three files.

Consolidation

First, we will consolidate the data from our three workbooks. In each one the structure of our codes and balances is simple:

Simon Hurst

The data is not set up as an Excel Table, but we have structured the worksheet name as Data and the name of the region separated by a dash. This will allow us to restrict our consolidation to just sheets that contain data and to extract the name of each region.

We will consolidate the data from the workbooks in our folder in a separate workbook held outside of the folder. This workbook will eventually contain our formatted accounts but starts off with our coding chart structure. To begin, open the consolidation summary workbook and select the CodeChart worksheet:

Simon Hurst

We have set this up as an Excel Table as we will need to turn it into a query that we can use to summarise our data by merging it with our consolidated data. We have named the Table CodeChart.

Step one: Turn the CodeChart Table into a query

Click anywhere in our CodeChart Table and use From Table to read our Table into the Power Query Query Editor. For now, we just want to make our Table available as a query so we choose File, Close & Load dropdown, Close & Load To…, Only Create Connection. By default, our query will be given the same name as our Table.

CodeChart

Step two: Consolidate the data in our separate workbooks

Staying in our consolidation workbook, we now need to bring in the data from all the sheets that start with 'data' from all the workbooks in our data folder.

We choose New Query, From File, From Folder and Browse to our folder. We should see our three workbooks displayed. We want to edit our new query so we click on the Edit button:

Aweb data

This loads our three workbooks into the Query Editor:

Query Editor

Our folder only contains workbooks but we could click on the Extension dropdown to set a filter to limit the files included to those with a particular extension if we needed to. For our example, we only need the Content column so we can right-click in the Content header and choose Remove Other Columns.

We now need to step through the hierarchy of Workbooks-Worksheets-Cells to get at our actual data. The Content column contains the content of each Workbook. We first need to add a column that will extract the details of each Worksheet.

From the Add Column ribbon tab we choose Custom Column. We need to use a simple formula to extract the details we need. The formula is simple but it's worth being aware of some significant differences between entering a formula in the Query Editor compared to a 'normal' Excel formula.

Firstly, there is no AutoComplete to help you so you need to know the name of the function you want to use and its syntax and, secondly, the Query Editor is case sensitive. If you don't get upper and lower case exactly right, your formula will result in an error. We give our new column the name Worksheets and then type in:

=Excel.Workbook(

We then click on the Content item in the Available columns: list and click Insert and finally close the brackets. Your formula should look like this and at the bottom the prompt should say: No syntax errors have been detected:

Custom column

We should now have a new column containing a Table for each workbook. This Table contains details of each of the worksheets in each workbook. At the right of the column header there is a button that allows us to expand our Table. We just need the Name and Data and we also deselect.

Use original column name as a prefix:

use name as prefix

This expands our Worksheets column into two separate columns: Name, containing the worksheet name and Data, containing all the used cells in the worksheet. We need to extract the region name from the full worksheet name and also filter out any worksheets that don't start with Data.

We select the Name column and, from the Transform group of the Home Ribbon tab choose Split Column, By Delimiter, Custom and enter a dash:

Delimiter

We now have a Name.1 column that should just contain the word Data and a Name.2 column that contains the region. We could filter our Name.1 column to exclude any worksheets that don't start with Data.

Now for the Data column. Again, we use the button in the header to expand the column. We just need to expand Column1 and Column2. We should now see all the used rows for each worksheet in our query. Because our data wasn't set up as a Table, we just have the raw data, including the column headings so we need to rename Column1 as Code and Column2 as Amount by double-clicking in each heading. We can then filter the Amount column to remove any row that contains the word Amount by clicking in the column dropdown:

Column dropdown

We can tidy our query by renaming our Name.2 column to Region and removing the Content and Name.1 columns. We should now have a list of rows showing the region, the code and the amount:Tidy

Step three: Summarise the data by Code category

Next comes the most difficult part of the process. We need to use our CodeChart query to summarise our individual codes into our summary codes.

First, we merge our existing query with the CodeChart query we created in Step one.

From the Query Editor, Home Ribbon tab, Combine group we choose Merge Queries. Our current query will be displayed and we can choose CodeChart from the dropdown for our second query. For the reasons we discussed at length in Part five of the series, we set the Join Kind to 'Full Outer (all rows from both). We click on the Code column of each query to show that these are the columns to use to make the join:

Merge

When we click OK we find that another Table column has been added. We expand this using the button in the header to show the Code.1 and Category columns. We then need to add a column that shows the value in the Code column if there is one, or the value in the Code.1 column if Code is null.

From the Add Column Ribbon tab, General group we choose Conditional Column (this is a relatively new addition to Power Query so if you can't see this option you might need to check that you are on the latest version). Conditional Column guides us through creating a conditional formula. We will call our new column 'Sort code' and set the condition that if the Code column is equal to the value null then use the contents of the Code.1 column. Otherwise, use the contents of the Code column. You can choose to use a Value or a Column from the Value, Output and Otherwise dropdowns:

Conditional column

We then use the Sort code header dropdown to sort the column in ascending order. We then select the Category column and choose to Fill, Down from the Any Column group of the Transform Ribbon tab:

Ascending order

We filter the Code column to exclude any null values as these will occur when there is a value in the CodeChart Table but not corresponding value in any of our worksheets.

From the Transform Ribbon tab, Table group we choose Group By and set the first grouping to Region, then use Add grouping to add a second level of grouping and set it to Category. We will call our new column name Amount and set the Operation to Sum and the Column to the existing Amount column.Group by

We should now have our three worksheets summarised by our Code Chart code categories:

Code categories

We can use File, Close and Load to load this into our workbook as a refreshable Table.

This Table will be the basis of our management accounts. We could, for example, use a SUMIFS() function to extract the total of whichever category name we type into cell F11:

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

Coming soon

Next time we will transform our Table into a set of accounts that can be refreshed with a maximum of one mouse click and then prove the point by adding our fourth workbook.

Profit and loss accounts

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 (2)

Please login or register to join the discussion.

it's my photo
By todlad52
21st Aug 2017 01:24

Any news on when the next article in this series will appear, Simon?

Duncan

Thanks (0)
Simon Hurst
By Simon Hurst
26th Aug 2017 20:08
Thanks (0)