What do you call an unbalanced group consolidated trial balance? A late night.
My background is 16 years of experience working for FTSE100 and Fortune20 multi-national Insurance and Financial Services companies and I have spent a large period of that time both leading reporting and consolidation teams and identifying and implementing process improvements. The article is written from that space and so may well miss the specific challenges of small and mid-sized entities. Hopefully, the concepts will be largely transferable in enough cases to make this an interesting read.
Most group or divisional finance teams will spend a considerable amount of their time and effort preparing consolidated results and reports. A recent 2019 report stated that 25% of organisations take 11 or more days to close their books.
To the uninitiated, consolidation is simply a matter of adding numbers together, removing some inter-company balances and, as my mythical brother’s child would say, “Bob’s my uncle”.
However, having spent many a late night and early morning working on them myself, I know this is not the case and below I will discuss the common challenges I have faced at each of the main stages of a typical consolidation process and my recommendations to minimise the pain.
Hopefully, there may be some nuggets of useful advice but at the worst, you will know that others have been there before you.
Unfortunately, the challenges below are often not distinct and one or more can apply to the same transaction / process.
I use the term “submission” throughout this article, reflecting my formative years when reporting packs were prepared by the group entities and then submitted to group for processing. Many groups now use more real-time systems where business units simply sign off their numbers once local processing is complete. The principal concepts of consolidation remain the same.
However, before we begin to discuss the process itself, I would like to start with some common system configuration points.
1. System configuration
Now, many systems will already be configured and may require significant resource and time to change, if indeed it is possible to do so. However, some of the following suggestions are possible with a simple coding / flag change, or should be considered if you are implementing a new system.
If you are implementing a new system, then please don’t fall into the trap, or pressure, of simply lifting and replacing the old processes into the new system. Unless you already have a smoothly run process, you will simply be running broken processes on a newer platform. An obvious point, but you would be surprised at the number of instances where I have seen this happen.
a. YTD vs period movement submissions
Submissions can either be YTD i.e. closing balances or Period Movements. For the former, balances in the consolidation system are overwritten with the new feed; for the latter, the incremental movement is posted as a journal.
I am a huge fan of YTD as this avoids any complications arising from prior period adjustments in the local ledgers, a practice more common than I would have expected and which can then require a substantial reconciliation effort. One possible way to use Period Movements and avoid this issue, is for the Period Movement to be calculated in the local ledger as the YTD balance less the previous submitted file.
If entities have non-coterminous year ends, then this will need to be adjusted for as part of the submission in YTD feeds to ensure, for example, income is taken to the correct year or Opening Balances are not contaminated.
b. Submission validations
I would strongly recommend building as many validations into the submission files as possible, and experience tells me to make these ‘blocking’ where applicable – people will tend to ignore warning errors initially and then not revisit as other matters become the centre of focus.
Examples of blocking errors would be movements of Opening Balance accounts; IS depreciation is equal and opposite to BS depreciation etc. Warning errors could include expected signage of balances. The validations should be the responsibility of the submitting entity to clear although Group owners for the validation should be available for when problems arise. Group should also have the ability to override these blocks if a solution cannot be found.
c. How to book eliminations
There are many options on how to book eliminations, some systems offer more than others. The common options I have seen are:
i. Elimination / Consolidation entities. These are distinct to the individual entities and can either be set up at different levels in the company structure (for example if sub consolidations are required) or simply at group level. My two main problems with this approach are i) it is difficult to get an entity ‘contribution to group’ figure if eliminations from various entities are grouped together and ii) if using sub consolidation levels, challenges arise when there is a group restructure. This approach more closely replicates a spreadsheet solution where the elimination entries will appear in elimination columns.
These entities should fall under the group’s governance policy, including reconciliation. Unfortunately, I have been in too many companies where this was not the case.
ii. Elimination using a specific journal category, dr and cr in different entity. The main challenge to this approach is that gives rise to unbalanced entities, a concept some finance managers may have difficulty understanding, spending more time on understanding the out of balance than the commercial story.
iii. Elimination using a specific consolidation journal category, but using an offset account to ensure each entity balances. The offset account should of course net to zero at a Group level. This is my preferred solution.
d. Period 13
This will be a contentious topic for many. Except in the case for those businesses (e.g. hospitality) who report in weekly (or four weekly) blocks, I cannot see a valid reason for using a period 13 set up. The two main arguments I have heard are:
i. To record audit adjustments after period 12 is closed. I would argue that these adjustments tend to be small in number, if not amount, and should be processed in a controlled period 12, using appropriate journal categories if required.
ii. To record tax true-ups identified after the financial accounts have been finalised and signed, as part of the annual tax return process. Here I would argue that for financial reporting purposes, these need to be reported in the following financial year and should be booked straight into that year using appropriate accounts to aid reconciliation.
Another contentious issue, I am of the firm belief that Excel has a limited use in the consolidation reporting process (please note my caveat above of only having worked in large multi-nationals!). As an input or analytical tool Excel is fine, but all too often I have seen complex models being built to take an extract from the system and then reallocate or reclassify amounts. These type of adjustments should be done in the underlying systems where at all possible. Similarly, any standard end-reports should be built in the system and not have a trial balance downloaded into Excel and then use INDEX, MATCH or SUMIF formulae, for example, to build your reports. The control environment of Excel is generally poor, with the ability to change and overwrite formulae and lack of version control to name but two weaknesses.
2. Combine the financial statements of each group entity
The first stage in a group consolidation process is to combine the financial statements of each group entity being consolidated. Challenges can occur in the following areas.
a. Preparing for success
The timetable for each entity’s submission should be clearly laid out well before the accounting close and contact details shared. If all the group entities are in the same time zone, then this should provide no challenges. A time for submission should be agreed as part of the closing timetable and submission(s) should be made in accordance of this. If, however, entities are in different time zones, then care and needs to be taken and sensitivity to other countries working hours. Also dropping into this category are countries with differing public holidays. Having the same cut-off time is important mainly for intercompany transactions, where a difference in cut-off times will lead to mismatches (see section 3 below on Intra Group eliminations).
b. Differing Chart of Accounts and/or mapping tables
The more decentralised the local ledgers are, either due to country regulations or acquired heritage systems, the higher the need for consistent mapping tables and the updating / reviewing thereof. I have come across instances where, because of language translation problems, there have been asset accounts mapped into the Income Statement. To help combat this, reconciliation at a lower level as possible is recommended.
c. Differing accounting policies / valuation methods
If an entity uses differing accounting policies to those used at group, then appropriate adjustments need to be made on consolidation. These are commonly made either in specific consolidation entities or using specific journal types. These should be kept separate from general group adjustments / estimates which can, but in my view shouldn’t, be booked in the same place as policy differences. In any case, these entities / accounts should be subject to the same rigorous reconciliations as the rest of the group accounts.
d. Impact of Intergroup arrangements
A subset of c) above are those transactions between group entities where the classification of accounts differ between that recorded at an individual entity level and at a group level. Examples would be property leased from one subsidiary to another. In the lessor it may be shown as an investment property, at group level it would need recording as owner occupied. This could also impact the valuation / carrying value of the asset.
e. Foreign subsidiaries
I could write a whole article on foreign exchange so I will try and keep it short here. If the reporting entities have a different functional currency than group, then currency translation is required as part of the consolidation process. Ideally, the consolidation system will only deal with translation from functional currencies to the reporting currency but many systems are now also used as local ledgers and also deal with translating from transactional currency into functional currencies. Care must be taken in ensuring the correct accounting rules are followed as these are different on moving from transactional to functional, and from functional to reporting. Many companies spend an inordinate time replicating what the system does in calculating the Currency Translation Reserve (CTR), the reserve generated when translating into the reporting currency. In my view it is ridiculous spending a considerable sum of money buying a consolidation system, test its functionality, and then spend a lot of time building and running complex Excel models to do the same thing. A high level model based on NAV should suffice in most cases.
f. Poor quality of submitted data by group entities
I would strongly recommend building as many validations into the submission files as possible, and experience tells me to make these ‘blocking’ where possible – people will tend to ignore warning errors initially and then not revisit as other mattes become the centre of focus.
Examples of blocking errors would be movements on Opening Balance accounts, ensuring IS depreciation is equal and opposite to BS depreciation etc. Warning errors could include expected signage of balances.
The validations should be the responsibility of the submitting entity to clear although Group owners for the validation should be available for when problems arise. Group should also have the ability to override these blocks if a solution cannot be found. Over time, this will result in an improved quality of the submissions made by the group entities.
g. Late adjustments
A process to deal with late adjustments is crucial and there should be a materiality level defined which may (should) be different for soft, hard and regulatory closes.
3. Eliminate intragroup transactions and balances
Once the submissions have been aggregated and converted into the Group reporting currency, the next stage is to eliminate the intragroup balances, more commonly known as intercompany account elimination. I use both terms below. The amount of time ‘in-cycle’ spent on reconciling intercompany mismatches is huge, and for most soft closes, unnecessary, although I have seen businesses who only have hard closes, focus on intercompany balancing on soft closes.
Here are some suggestions for minimising the time spent on this activity:
a. Preparing for success
If your company does have a lot of mismatches, then time is best spent, out of cycle, understanding the key drivers and focusing on the process: how and when balances are agreed, what is the dispute mechanism etc.
b. Timetable and prioritise
Have the inter-company reconciliation process early on in the close cycle, ideally even before the cycle starts, so that your staff can spend time in-cycle adding value.
For me the most important piece of advice is to have the group entites engage and agree balances before the submission date. I mentioned above, the importance of having blocking errors on submissions to improve the quality of the data received. It is certainly more complex, but some systems, especially those operating in real time, will enable you to validate, or at least compare, the intercompany balances with the counterparty before submission.
c. Ease the identification process
Have separate intercompany accounts (and associated suspense accounts) for each major type of relationship. This makes identifying mismatches easier.
Inter-company accounts should be tagged with the counterparty.
A materiality level should also be agreed at relationship level, below which it is not worth investigating. However, care should be taken that the sum of immaterial mismatches at entity level, doesn’t become material at a group level.
Automate wherever possible. Most consolidation systems will auto balance to a suspense account, which can be pre-defined depending on the type of relationship (see having specific accounts above). However, if the group entities have engaged and agreed balances before the close, the balance on this account, at a group level, should be immaterial.
f. Reconcile and settle
Ensure intercompany and related suspense accounts are reconciled and settled regularly. Some businesses, especially those with high volumes, may want to reconcile and settle daily, others less frequently – weekly or monthly. It is very easy for an intercompany current account to become a quasi-loan if settlements do not occur. Your tax team will not like this.
g. Reconciliation rules
Linked in to automation, but also a good rule to have for manual processes where balances are disputed, is “Buyer is correct”. If possible, consider having a process where the buyer is able to book both sides of the transaction.
h. Consider the wider team
Before any new intercompany transactions are set up, ensure all stakeholders are clear on the contract details e.g. any mark-ups, VAT and other tax implications, settlement terms, dispute mechanism, interest rate for outstanding balances, communication of balances at period end, treatment of immaterial mismatches (write-off annually for example).
4. Eliminate the parent’s investment in each subsidiary and recognise goodwill and other business combination related adjustments
The next stage is to eliminate the Investment in Subsidiary balances (IIS) in the parent / holding companies against the corresponding equity in the subsidiary companies.
In my mind, these are really a subset of intercompany accounts (see part 3 above) with some additional complications arising on acquisition and any subsequent disposal or re-organisation.
a. Intercompany accounts
There should be counterparty information available at the account level, at least for the Investment account and similar to Intercompany accounts above, frequent reconciliation of the associated suspense account is a must.
Difficulties tend to arise on subsequent restructuring, disposals and whenever any NCI is involved. This is especially the case when multi consolidation entities have been used and the new structure impacts more than one of them.
c. Investor registers
Having an Investor register which automatically feeds into the consolidation elimination is an advantage and can assist in the reconciliation. In many systems the elimination entries are generated from the register, and so if it doesn’t agree to the value of the Investment in Subsidiary (IIS) in the parent’s book, we already have the beginning of a tick and tie exercise. The larger the group, with holding companies having many subsidiaries, the longer this exercise could take. Having a pre-submission validation between the register and your trial balance, or even better, having the IIS balance populated by the register, is a best practice, where the sysyem allows it.
d. Differing currencies
Foreign currency implications are often a big factor in both mismatches between the Investment in Sub account in the parent (often held at closing rate) and the associated equity accounts in the subsidiary (often held at a mixture of historical, weighted average, or closing depending on the account).
Goodwill is normally booked as part of the consolidation entries and questions can arise over the currency. Remember that under IAS21, goodwill is treated as an asset of the subsidiary and therefore should be expressed in the functional currency of that subsidiary.
f. Pre-acquisition reserves
In order to match the Investment to the appropriate reserves of the subsidiary, it is necessary to identify the pre-acquisition reserves. However, a challenge may arise as pre-acquisition reserves is not a concept normally recognised by the subsidiary. One solution is to advise the subsidiary to book the value of the pre-acquisition reserves into disclosure accounts which are then used in the calculation of the elimination. A second solution, and my preference, is to create a new nominal account in the subsidiary’s books and book the amount of the pre-acquisition reserves here, with an offset to the Retained Earnings account. Locally, both accounts will map to Retained Earnings. The more the subsidiary accounting team is aware of and involved in the consolidation process, the more they can anticipate issues and raise them in a timely manner.
g. Fair value adjustments
Not really a part of the elimination but, at the risk of confusing everyone, I will make a brief mention of them here. Possibly they would be covered, from the Net Asset side under differing accounting policies in 2c and 2d above and there are differing ways of recording the offset to these FV adjustments (directly to Goodwill, to Pre-acq reserves). What is important to remember is that these adjustments are consolidation adjustments, i.e. not recorded in the subsidiary’s individual financial statements and will affect the final Goodwill amount.
5. Allocate comprehensive income and equity to non-controlling interests
The last stage of a typical consolidation process is allocating comprehensive income and equity to non-controlling interests. Now, not all groups will have these, and if you don’t, count yourself lucky (from an accounting point of view), but if you do, then you may face the following challenges:
a. No. of shares vs voting rights
Many systems, if they are automated, will require the ownership % to be entered somewhere. In most cases this will be the % of shares owned, but be careful if the voting rights, which define control, are different to the economic rights.
Similarly, care will need to be taken if there are more than one class of shares issued, with differing rights attached.
b. Indirect ownership
The calculation of the Group’s interest in a subsidiary can become more complex if the ownership structure is not linear, i.e. if a subsidiary is owned by more than one group company. This can be especially challenging if sub-consolidations are performed and the parents are at different levels in the structure.
Similarly if a subsidiary is partially owned by a parent who is also partially owned, then the group interest will be calculated taking into account the indirect interest. This can get even more complicated if the effective control changes the ownership of the subsidiary from an investment / associate, to a joint venture to a subsidiary.
c. Changes mid year
However, the challenge I came across the most was when the ownership changed midway throughout a year. In this case the net income needs to be allocated between the differing ownership periods and this was not possible for many systems, especially those using a YTD submission. Manual adjustments were required, opening up the potential for errors. Luckily, many systems can now deal with this but care needs to be taken to correctly identify the period in which the ownership percentage changes.
If I had to list my top three recommendations for improving the consolidation process, they would be the following. Two of the three relate to process and so are not dependent on the system you are using.
1) Engagement by group entities to reconcile intercompany accounts before the submission date
2) Add blocking controls to the submission, thereby improving the quality of data received by Group
3) Enforce materiality and late adjustment process. There will always be those material adjustments that come in after submission, but reducing these to a minimum, and only when material to Group, will save a lot of time and frustration for the Group finance teams, not only the consolidation team but also the other teams who will need to re-run their models.
As a reward for reading to the end, I will also add in a bonus point, not mentioned above as I didn’t have a post close section.
The one thing, apart from a new system, that made the biggest difference to most of the closes I have worked on is introducing effective post close reviews of the close with all stakeholders. To be effective the outcome of the reviews should be actions, action owners and timelines to resolve for all issues raised. If it just a tick box exercise, then it will just demotivate the team even further.
I hope you have enjoyed reading this quite lengthy article – if you have reached this point I guess you must have. If you have any questions, comments, contrary views (always welcome), please do not hesitate to reach to me.