TAKEAWAYS
The visual below shows a typical consolidation worksheet. You have the list of accounts, and group entities in the columns. You will need to bring in the trial balance data of each group entity into this worksheet. This can be achieved using formulas or functions, or using Power Query.
Power Query is an extract, transform, load (ETL) function in Microsoft Excel. It was first introduced over 10 years ago as an add-in and later became a core feature in Excel. This marked a significant enhancement in Excel’s data processing capabilities, making it easier for users to handle complex data transformations without needing to know how to code.
Power Query allows users to create connections between a workbook and various data sources, such as another workbook, multiple workbooks in a folder, a database, or even data transmitted through an application programming interface (API). Once the connection is defined, any changes to the data can be updated simply by clicking the refresh button in Excel to repull the data. This feature eliminates the repetitive tasks of manually pulling and cleansing data that accountants typically do in every reporting cycle.
1. Prepare the trial balances
First, you need to download the trial balance for each group entity from your accounting system. To use Power Query to combine the trial balances of multiple group entities, the structure should be relatively consistent. The data should ideally be in the same worksheet, start at the same row and have the same heading labels, although the sequence of the columns doesn’t necessarily need to be identical.
If the trial balance format is not consistent across the group entities, you’ll need to perform intermediary steps to make it consistent. This can be done using Power Query or other Excel functions.
All the trial balances should be saved into the same folder and renamed to reflect the respective entities’ name, like this:
2. Load the data
In Excel, create a new workbook. Then, go to the Data tab, navigate to the Get & Transform Data group, and click Get Data > From File > From Folder. I am using Excel 365 for this illustration. If you’re using an older version of Excel, the buttons may be in a different location. Look around within the Data tab and you should be able to find them.
Choose the folder where you saved all the trial balances. Avoid saving the consolidation worksheet in the same folder as it may cause issues, and, it’s simply not neat. Then, click Combine & Transform Data.
Select the worksheet which contains the trial balance and click OK.
This will bring you to the Power Query Editor, a low-code, intuitive user interface.
3. Data cleansing
All the data from the files are now pulled into a table, with one group entity after another, in a flat format. Don’t worry if you don’t like the format; this is just the backend. We will perform further manipulation to produce a consolidation worksheet in the format shown in the first screenshot above.
If your accounting software produces separate debit and credit columns in the trial balance, you’ll need to collapse them into a single column, with positive numbers representing debit balances and negative numbers representing credits. Having two separate columns for the amounts is not very convenient for subsequent manipulation. If your accounting system has already collapsed the balances into one column, you can skip this step.
To collapse the debits and credits into a net column, go to Add Column tab, and click on Conditional Column.
The logic here is: If the Debit column has a value (that is, does not equal null), return the value from the Debit column. Otherwise, return the value from the Credit column. When entering the Output and Else fields, click on the dropdown box and choose “Select a column”, then select the respective column.
After you have everything set up as shown above, click OK. You should see a new column called “Net”, and a new applied step will appear in the right pane.
However, there’s an issue: In the Net column, credits are also presented as positive numbers. To change credits to negative values, go to the formula bar and add a minus sign before [Credit]. Then press Enter.
You can explore other transformation functions to address any additional data cleansing needs. Beyond the Transform and Add Column tabs in the ribbon, you will find more functions hidden in the right-click menu of the column headers.
4. Load
After cleansing the data, it is time to load them into a report. Go to the Home tab, click on the arrow beside Close & Load, then select Close & Load to…
Select PivotTable report.
5. Set up the Pivot Table
Set up the Pivot Table in the following manner:
I recommend further formatting the Pivot Table. First, remove all subtotals as they are not needed in this context. To do this, go to the Design tab, click on the Subtotals menu, and select “Do Not Show Subtotals”.
Next, in the Design tab as well, go to the Report Layout menu and select “Show in Tabular Form”.
The end result will look like this:
6. When the next reporting cycle comes
In the next reporting cycle, all you need to do is extract the updated trial balances and save them in the same folder. Then, go to the Data tab and click Refresh. Boom! The task that used to take minutes or even hours is now done within seconds.
We did jump ahead a bit by diving straight into the steps to get things done. However, before you start implementing Power Query for consolidation, make sure to read the following:
Consolidate at general ledger account level
I prefer to do consolidation at the general ledger (GL) account level, though some prefer to do it at the financial statement caption level. Consolidating at the GL account or trial balance level gives you more detailed information in the consolidation worksheet. It may seem like a lot more work to do it at such a granular level. However, if you train your assistant (Excel) properly, the additional effort required from you is not that significant. Excel will be doing the work. Even if you have a thousand GL accounts, Excel can handle it quickly. The benefits of working at such a granular level are immense. When you need a breakdown of a financial statement caption, it is readily available.
To generate the consolidated primary statements, you will need to map each account to the respective financial statement captions. Based on that mapping, you can generate the primary statements using functions. If you are interested in exploring Power Query further, you can even load the mapping into Power Query and merge it with the consolidated trial balance to generate a primary statement.
Common chart of accounts
To perform a meaningful trial balance consolidation, all entities within the group should adopt a common chart of accounts. This may seem intuitive but, in practice, it’s not always the case. This is particularly true in small and medium-sized enterprises (SMEs) and startups, where things move quickly, and the importance of a common chart of accounts is often overlooked. Additionally, it’s crucial to use dedicated accounts for intercompany balances and transactions that are subject to elimination – these should not be mixed with external transactions.
Elimination
You might be wondering: Did I miss out on the elimination? In school, we were taught to pass consolidation journal entries to eliminate intercompany balances. However, unless you are dealing with the consolidation of a more complex group, you can achieve what I call natural elimination by grouping them. This is why it is essential to have dedicated accounts for intercompany balances and transactions.
In your mapping table, map the intercompany accounts to the same caption, allowing them to be naturally eliminated. This way, in your final output, those intercompany accounts will be netted to zero.
Other elimination adjustments
If you need a dedicated elimination column for unrealised profits elimination or other adjustments, consider adding another trial balance file in the folder for an adjustment “entity”.
Some accounting tasks, like data preparation for consolidation, can be mundane and time-consuming. However, with the right tools (not necessarily the most sophisticated ones) and the right approach, you can let computers do what they excel at, freeing up time for human accountants to continue adding value in areas that require our expertise.
Have you read my earlier article, “Dynamic Array Formulas In Excel”? Dynamic arrays provide the ability to create scalable templates that adapt effortlessly to varying data sizes and complexities. They streamline your accounting processes, and help ensure that your work is efficient and future-proof.
Ng Xian Hui, CA (Singapore), is Founder of Backbone Pte Ltd.
© 2024 Institute of Singapore Chartered Accountants