Multi-Entity Consolidation and Intercompany Eliminations
How to consolidate two or more entities into one group result in Excel, covering account mapping, the elimination journal and the tie-out check that proves it.
An Australian group with two or more entities produces a set of accounts for each one and a single set for the group. The trading company, the trust that holds the assets, the property entity and any overseas or interstate subsidiary each keep a ledger, a chart of accounts and a trial balance. Combining them into one result is simple arithmetic once the mapping exists, and it is where most of the manual work and most of the errors live.
The part that catches people out is that the group result differs from the sum of the entity results. Every internal transaction is recorded twice, once as income in one entity and once as an expense in another, and adding the trial balances together counts both sides. Eliminations remove those internal amounts and turn a pile of entity accounts into a group result.
What follows covers the mapping that makes the process repeatable, the layout of the consolidation worksheet, the eliminations a typical group needs, the elimination journal that documents them, the tie-out check that proves the numbers agree, non-controlling interests at a basic level, and the failure modes that show up in review.
Mapping Each Entity to the Group Template
Consolidation starts with a mapping, and the mapping is what makes the process repeatable. Every account code in every entity is assigned to one group line, held on a single sheet keyed by entity and account code. The worksheet then pulls amounts by group line, so an account lands in the same place every month whichever entity it came from and whichever code it carries.
| Group line | Trading company | Trust | Property entity |
|---|---|---|---|
| Revenue: sales | 4000 Sales | 4100 Trust income | n/a |
| Revenue: rent | n/a | 4200 Rent received | 6100 Rent |
| Intercompany management fees | 4900 Fee income | 7900 Fee expense | 7900 Fee expense |
| Cost of sales | 5000 Cost of sales | n/a | n/a |
| Property costs | n/a | 7400 Property costs | 6400 Property costs |
| Interest on intercompany loans | 6800 Interest income | 8800 Interest expense | 8800 Interest expense |
| Intercompany loans | 1230 Loan to group | 2300 Loan from group | 2300 Loan from group |
SUMIFS does the consolidation work. One row per group line, one column per entity, and the entity code in the column header is the only thing that changes:
' Mapping sheet: one row per entity and account code that has ever posted
Map[Key] = [@Entity] & "|" & [@Code]
' Trial balance table: Entity | Code | Group line | Amount
TB[Group line] = IFERROR(XLOOKUP([@Entity] & "|" & [@Code], Map[Key], Map[Group line]), "UNMAPPED")
' Consolidation worksheet: one row per group line, one column per entity (B$1 holds the entity code)
=SUMIFS(TB[Amount], TB[Entity], B$1, TB[Group line], $A2)
' Controls, both of which must return zero
=SUMIF(TB[Entity], B$1, TB[Amount]) - SUM(B$2:B$40) ' raw trial balance total against mapped total
=COUNTIF(TB[Group line], "UNMAPPED") ' unmapped account codes
The two controls matter more than the mapping itself. The first proves that every dollar in an entity's trial balance reached a group line, and the second proves that no code fell through the mapping and disappeared. A new account code opening in one entity is normal. A new code that nobody maps is how a consolidation quietly loses a balance, so the unmapped count belongs in the monthly checklist.
The Consolidation Worksheet Layout
One sheet holds the whole consolidation. The columns are fixed, the rows are the group lines in reporting order, and every month produces the same shape.
| Block | Columns | Purpose |
|---|---|---|
| Group line | A | Reporting order, identical every month |
| Entity blocks | B to D | Mapped trial balance for each entity, one block each |
| Total before eliminations | E | Sum of the entity blocks |
| Eliminations | F and G | Journal debits in F, credits in G, each line carrying its journal reference |
| Group | H | Total before eliminations, adjusted for the elimination block |
| Check | I | Tie-out formulas that must return zero |
The elimination block holds the journal debit in one column and the credit in the other, and the group column signs them by the nature of the line. For an asset or an expense line the group column adds the credit and subtracts the debit, and for a liability, income or equity line it does the reverse. Fixed columns matter because the check column and the group totals reference whole rows, and a column inserted for one entity in one month breaks every formula to its right.
What Has to Be Eliminated and Why
Five categories cover most groups. Intercompany management fees are the most common, because a parent company that charges the trust for administration records fee income while the trust records fee expense. Intercompany loans and the interest on them follow, since the parent holds a receivable and the borrower holds a payable, and both ends also carry interest income and expense. Dividends paid by one group entity to another are internal movements of equity, and the recipient records dividend income. Intercompany sales and purchases appear wherever one entity supplies another. At a simple level, the margin on goods that one entity sold to another and that the buyer still holds at period end is also eliminated, because the group has not yet sold the stock outside the group.
Each of these is a double count. The fee eliminated from the parent's income is the same amount removed from the subsidiary's expenses, so the group result falls by the internal income and the internal expense together. The group's reported revenue and costs end up representing transactions with parties outside the group, and the group result represents profit earned from those external transactions.
Building the Elimination Journal
Eliminations belong on their own sheet, one row per journal line, with a reference, the debit account, the credit account, the amount and a short note. Nothing is posted straight into the group column. The worksheet reads the journal with SUMIFS by reference, so any group figure can be traced back to the entry that produced it.
| Ref | Debit account | Credit account | Amount | Note |
|---|---|---|---|---|
| E1 | Fee income (parent 4900) | Fee expense (subsidiary 7910) | 50,000 | Intercompany management fee for the year |
| E2 | Interest income (parent 6800) | Interest expense (subsidiary 8810) | 16,000 | Interest on the group loan |
| E3 | Loan payable (subsidiary 2310) | Loan receivable (parent 1230) | 200,000 | Group loan balance at year end |
| E4 | Dividend income (parent) | Distribution paid (subsidiary equity) | 40,000 | Dividend from the subsidiary to the parent |
| E5 | Cost of sales (parent 5000) | Inventory (subsidiary 1310) | 25,000 | Margin on stock still held at year end |
| Total | 331,000 | Debit total equals credit total |
The journal balances before it is applied, which is the point of holding it separately. The debits total 50,000 + 16,000 + 200,000 + 40,000 + 25,000 = 331,000, and the credits total the same by construction, because every entry has two sides. A journal that does not balance means an entry has one leg, and the group column would carry a break that no other check would explain.
The Tie-Out Check That Proves the Group Balances
The check that proves a consolidation is correct compares group net assets with the sum of the parts, with the ownership split made explicit. Take a trading company that owns 80% of a services subsidiary. The parent's own net assets are 900,000, the subsidiary's net assets are 500,000, the margin eliminated under E5 is 25,000, and the ownership percentage is 80%.
| Check | Amount |
|---|---|
| Parent net assets | 900,000 |
| Subsidiary net assets | 500,000 |
| Unrealised margin in closing inventory eliminated | (25,000) |
| Group net assets | 1,375,000 |
| Attributable to owners of the parent | 1,275,000 |
| Non-controlling interest | 100,000 |
| Owners plus non-controlling interest | 1,375,000 |
| Difference | 0 |
The arithmetic is 900,000 + 500,000 = 1,400,000 less the 25,000 margin eliminated gives group net assets of 1,375,000. The owners' share is the parent's own net assets less the margin elimination attributable to them, 900,000 - 25,000 = 875,000, plus 80% of the subsidiary's net assets, which is 0.80 x 500,000 = 400,000, giving 1,275,000. The non-controlling interest is 20% of the subsidiary's net assets, 0.20 x 500,000 = 100,000. Owners plus non-controlling interest is 1,275,000 + 100,000 = 1,375,000, which equals group net assets, so the difference is zero. The margin elimination is attributed in full to the owners here because the sale ran from parent to subsidiary; where the sale runs the other way, the elimination is split between the owners and the non-controlling interest in the ownership proportions.
' Tie-out block: B4 parent net assets, B5 ownership %, B6 unrealised margin,
' B7 subsidiary net assets, B8 group net assets from the worksheet
Owner share = ($B$4 - $B$6) + ($B$5 * $B$7) ' 875,000 + 400,000 = 1,275,000
NCI = (1 - $B$5) * $B$7 ' 0.20 * 500,000 = 100,000
Break = $B$8 - (Owner share + NCI) ' 1,375,000 - 1,375,000 = 0
' Check cell in column I of the worksheet, on the row that carries the result
= IF(ABS($B$9) > 0.5, "BREAK", 0)
The balance sheet tie covers half the proof. The other half is that intercompany balances net to zero on both sides once the journal is applied.
| Intercompany amount | Parent books | Subsidiary books | Net after elimination |
|---|---|---|---|
| Loan receivable and loan payable | 200,000 | 200,000 | 0 |
| Management fee income and expense | 50,000 | 50,000 | 0 |
| Interest income and interest expense | 16,000 | 16,000 | 0 |
| Dividend income and distribution | 40,000 | 40,000 | 0 |
A balance that does not clear is information about the entities rather than about the worksheet. The usual causes are an invoice raised by one entity and not recorded by the other, a charge posted in the wrong month, or a payment in transit at period end. Each of those belongs in the entity's ledger or in a reconciliation item that clears next month, and the elimination journal is the right place to record what was done about it.
Non-Controlling Interests in Group Equity
Where a group owns less than the whole of a subsidiary, the part it does not own is presented in consolidated equity as non-controlling interest, separate from the equity attributable to the parent's owners. It is measured from the subsidiary's own numbers at the ownership percentage, so a subsidiary with net assets of 500,000 and a 20% outside holding carries 100,000 of non-controlling interest in the group balance sheet.
The same percentage applies to the subsidiary's result. A subsidiary that earns 120,000 in the year produces 24,000, which is 0.20 x 120,000, of profit attributable to the non-controlling interest and 96,000 attributable to the owners of the parent. The balance in equity rolls forward from those two numbers. The subsidiary distributed 50,000 for the year, of which 40,000 went to the parent and is eliminated under E4, and 10,000 went to the non-controlling interest. Opening non-controlling interest of 86,000 plus the share of profit of 24,000 less the 10,000 distribution gives closing non-controlling interest of 100,000, which agrees with 20% of the subsidiary's closing net assets of 500,000. When the roll-forward does not agree with the percentage of closing net assets, something has moved through the subsidiary's equity that the consolidation has not yet captured.
Why the Group Result Differs From the Sum of the Entities
The size of the gap is worth knowing. Take the same group, with the parent's standalone profit at 180,000 including 50,000 of fee income and 16,000 of interest income from the subsidiary, the subsidiary's standalone profit at 120,000 after those charges, and dividend income of 40,000 in the parent.
| Item | Amount |
|---|---|
| Parent profit | 180,000 |
| Subsidiary profit | 120,000 |
| Sum of the entities | 300,000 |
| Less intercompany management fee | (50,000) |
| Less intercompany interest | (16,000) |
| Less dividend income from the subsidiary | (40,000) |
| Less unrealised margin in closing inventory | (25,000) |
| Group profit | 169,000 |
The internal income removed by the journal totals 50,000 + 16,000 + 40,000 = 106,000, and the unrealised margin removes a further 25,000. The group profit is 300,000 less 106,000 less 25,000 = 169,000. The group also reports 80% of the subsidiary's profit inside that figure, since a subsidiary's result is consolidated in full and then split between the owners and the non-controlling interest. A reviewer comparing the group profit with the sum of the entity profits will look for these differences, and a consolidation that carries a bridge from one to the other answers the question before it is asked.
Practical Failure Modes
Four problems account for most consolidations that have to be rebuilt.
Each entity gets mapped differently, so the same concept lands on two group lines. The symptom is a group line that looks too small and another that looks too large, and the fix is one mapping sheet that covers every entity rather than a mapping maintained inside each entity's file.
Intercompany balances do not agree between entities, so an elimination never clears. A receivable of 200,000 against a payable of 198,800 leaves 1,200 sitting in the group balance sheet, and the elimination journal cannot be balanced without knowing why.
Eliminations are posted directly into the consolidated numbers with no journal behind them. The group result may look right, and the next person to open the file has no way to see what was removed or why.
No check column, so a break is discovered by someone outside the finance team. A check column that computes the net assets tie-out, the intercompany total and the journal balance puts the answer on the same screen as the numbers, and a break shows up as a number other than zero on the day the consolidation is run.
Where one of the entities reports in a different currency, its figures reach this worksheet already translated, and the translation reserve sits in equity alongside the amounts discussed here. The consolidation mechanics themselves stay the same.
Frequently asked questions
What has to be eliminated in a group consolidation?
Intercompany management fees and other charges between group entities, intercompany loans and the interest on them, dividends paid by one group entity to another, intercompany sales and purchases, and any margin still sitting in inventory at the end of the period. Each elimination removes an amount that two entities in the group have recorded on opposite sides of the same transaction.
Why does the group result differ from the sum of the entities?
Each entity records its own side of every internal transaction, so adding the entity trial balances together counts intercompany fees, interest, dividends and internal sales twice. Eliminations remove those internal amounts, and the gap between the sum of the entities and the group result is the total of the eliminations plus any unrealised margin held in closing inventory.
How do I check that a consolidation is correct?
Two checks do most of the work. Group net assets must equal the parent's own net assets plus the group's share of each subsidiary's net assets plus the non-controlling interest, and every intercompany balance must net to zero on both sides. A check column on the worksheet that computes both, together with a balance check on the elimination journal, finds a break in the month it happens.
What is non-controlling interest?
Non-controlling interest is the share of a subsidiary's net assets and profit that belongs to owners outside the group. It is presented within equity in the consolidated balance sheet, separate from the equity attributable to the parent's owners, and it is measured from the subsidiary's own result and net assets at the group's ownership percentage.