How to consolidate multi-entity accounts without spreadsheets

‍

The short version

• Consolidation is a prolonged, spreadsheet-basedprocess for many finance teams, with figures out of date by the time they’reproduced.
• Growth multiplies the problem. Consolidating 10entities is more than five times harder than consolidating two.
• A unified system can automate the error-proneprocesses, so consolidated information is available continuously instead ofafter a period-end scramble
• Consolidation is a multi-step process, fromclosing the books to eliminating intercompany transactions and adjusting fornon-controlling interest.
• Many organisations have cut consolidatedreporting form a lengthy manual task to something that happens instantly.

‍

For Narinder Uppal, getting a real-time look at how his business was doing financially was impossible. Too many hours of puzzling over spreadsheets stood between him and a comprehensive group report.

As Finance Director of The Recruitment Group, he was in a situation familiar to many FDs. Producing consolidated reports was such a prolonged and frustrating spreadsheet-based process that the figures were out of date before they were ready.

"As a complex business, if we had an intercompany transaction, sometimes our accountants would do one side of the transaction and forget to do the other," he said. "And when we were doing a month-end reconciliation, everyone would be scratching their heads."

Consolidation is unavoidable for an organisation with more than one entity. But the laborious work of doing it manually, over a period of weeks, is not.

Why consolidation gets harder as a group grows

For many organisations, timely consolidated reporting is impossible because each entity in the group is set up as an isolated entity in its finance system. The finance department spends much of its time logging in and out of user accounts (sometimes even switching between different software), so group reporting requires pulling together data in spreadsheets.

For other organisations, consolidation is done through software bolted onto the core finance system, which saves labour but still means the full group picture can only be produced when reports are run at the end of an accounting period.

This is one of those challenges that multiplies exponentially with growth – so while consolidating two entities might be manageable, consolidating 10 is many times more painful. If any of those entities deal in different currencies or different accounting periods, the problems that spring from doing it manually are magnified even further.

What happens during consolidation

Whether they take place automatically or in an extended sprint at month-end, these are the processes that have to be gone through to produce consolidated financial statements.

Closing all entities and collecting the data

Nothing meaningful can be done until the books of each entity have been closed so that the trial balances and financial statements can be exported.

Mapping the chart of accounts

A chart of accounts is really just a list of categories, against which every transaction has to be recorded. But if you don't have a single chart of accounts operating throughout all the entities in a group, this phase of consolidation can be hugely frustrating. Everything has to be mapped across to the group's chart of accounts, so even a minor variation in the way things are described requires the same time-consuming clean-up.

Matching and eliminating intercompany transactions

Transactions that take place between the entities in a group need to be stripped out of the group P&L. If Entity A sells something to Entity B for £50,000, the credit and corresponding debit need to be eliminated from the group accounts. Overlook that intercompany elimination and you overstate assets and liabilities by counting the same money twice. And if someone's forgotten to record one side of the transaction, the finance team have to spend time hunting down the mistake amid the pressure of month-end.

Converting into the preferred currency

If entities in the group do business in different currencies, this can add more steps to the consolidation process. Income and expenses have to be recorded at the exchange rates that applied when transactions took place. Monetary assets and liabilities, on the other hand, must be recorded at the period's closing rate. That can involve a lot of looking up exchange rates to calculate the conversions.

Adjusting for non-controlling interest and goodwill

If an entity isn't entirely owned by the group, you'll need to account for non-controlling interest. So if the group owns 75% of that entity, the non-controlling interest is 25% and profits will have to be attributed accordingly.

Goodwill might be an intangible concept in the world outside accounting – but for finance people, there's an established way of working out the value of a group's acquisitions and it's important to get it right. It's calculated as the difference between the price paid for assets and the fair value of those acquired assets – and its value needs to be reviewed for impairment losses as realities change.

Reconciliation and consolidation

After all the steps above, you'll have a trial balance. If the debits don't equal the credits, it's detective work time, as you hunt for the missing transaction, the keyboarding error or the broken Excel formula that's caused an error or omission to creep in.

Continuous consolidation vs period-end consolidation

In many organisations, consolidation is a lengthy manual process that tries to organise transactions from weeks or months before. One reason it's so time-consuming is that errors tend to be much harder to root out when time has passed and a lot more data has entered the system.

In an ideal world, finance teams would enjoy continuous consolidation, so the group picture was always available and correct. That can happen if systems are up to the job:

All this means there's no need to laboriously close the books on each entity before consolidation can begin. It's done as you go. But it's almost impossible to achieve real-time group consolidation by manual means. It can only happen if you have a system built for it.

How do you know it's time to move on from spreadsheets?

There's no single indicator that it's time to move on from manual, spreadsheet-based consolidation to a more powerful system. Every finance leader will make their own judgement about when the pain of sticking with existing processes becomes worse than the upheaval of changing systems. (Related reading: Why month-end takes so long and how to speed it up.) But some of the questions to consider include:

Where iplicit fits

Many a finance team will face the problems of consolidating in spreadsheets after doing the day-to-day work in basic or outdated finance systems. It's a key trigger for the move to a more suitable accounting system.

"Our previous finance system was Exchequer (now owned by Advanced)," says Jill Smith, Senior Finance Manager at Barwood Capital, who manages more than 30 active legal entities plus around 50 dormant ones. "It was outdated and operated on a siloed basis, requiring separate logins for each entity. This made day-to-day operations inefficient and increasingly impractical as the business expanded."

The company moved to a unified system with iplicit. "Intercompany transactions are now automatically mirrored between entities. Consolidated fund reporting can be produced instantaneously," she says.

Danielle Disley, Group Finance Manager at Abbey Group, found Sage 50 "very clunky" when it came to managing recharges between a group of companies. "Making sure you raised an intercompany invoice, and that it was all reconciled correctly, was a nightmare – especially when you might get a bill to split between seven companies," she says. Now, a purchase invoice is entered to the system and recharged correctly straight away. She can examine P&Ls "at the click of a button, without having to log in and out of different companies".

Narinder Uppal drastically simplified multi-entity accounting at The Recruitment Group. He says: "The ability to run consolidated accounts at the touch of a button turns a previously daunting task, where normally a lot of data extraction and manipulation is required, into something very straightforward."

The bottom line

Consolidating the accounts is essential, so everybody gets it done somehow. But there's a huge difference between achieving it after an extended manual process and having a complete group view in front of you continuously.

If your systems are equal to your organisation's size and complexity, the group's figures can be correct not just once a month or quarter but continuously.