Free Multi-Entity Consolidation Excel Template
Consolidate two or three companies' trial balances with eliminations and balance checks. By RankerToolAI · October 2026.
Disclosure: this page contains affiliate links, including to Joiin. We may earn a commission if you buy through them, at no extra cost to you. Read our disclosure.
Get the Free Excel Template
Combine up to three companies' trial balances into a consolidated P&L and balance sheet, with an eliminations sheet and built-in balance checks.
No spam. Unsubscribe anytime. Skip the email, download directly →
What's Inside
- Start Here: five-step instructions.
- Group Lines: your group chart of accounts, each line tagged to a statement and section. Edit it to match your management accounts.
- Company A, B and C: paste each trial balance and pick a group line per account from a dropdown. Each sheet checks that debits equal credits and flags unmapped accounts.
- Eliminations: enter both sides of each intercompany balance or transaction; a check confirms they net to zero.
- Consolidated TB: sums every company plus eliminations by group line, automatically.
- P&L and Balance Sheet: consolidated statements by company, eliminations and group total, with a net assets = equity check.
Companies A and B come with example figures (A charges B a 10,000 management fee that B hasn't paid yet), so you can see the eliminations working before you paste your own numbers.
Preview the Template
These are the example figures that come in the file, calculated by its own formulas: Company A charges Company B a 10,000 management fee, and the eliminations remove it from the group totals. Every check reads OK. Figures in brackets are credits.
| Company A | Company B | Company C | Eliminations | Consolidated | |
|---|---|---|---|---|---|
| Income | 260,000 | 120,000 | 0 | (10,000) | 370,000 |
| Cost of sales | 120,000 | 50,000 | 0 | 0 | 170,000 |
| Gross profit | 140,000 | 70,000 | 0 | (10,000) | 200,000 |
| Overheads | 65,000 | 45,000 | 0 | (10,000) | 100,000 |
| Net profit | 75,000 | 25,000 | 0 | 0 | 100,000 |
| Company A | Company B | Company C | Eliminations | Consolidated | |
|---|---|---|---|---|---|
| Non-current assets | 50,000 | 15,000 | 0 | 0 | 65,000 |
| Current assets | 130,000 | 45,000 | 0 | (10,000) | 165,000 |
| Total assets | 180,000 | 60,000 | 0 | (10,000) | 230,000 |
| Current liabilities | 45,000 | 22,000 | 0 | (10,000) | 57,000 |
| Non-current liabilities | 0 | 0 | 0 | 0 | 0 |
| Net assets | 135,000 | 38,000 | 0 | 0 | 173,000 |
| Equity | 60,000 | 13,000 | 0 | 0 | 73,000 |
| Profit for the period | 75,000 | 25,000 | 0 | 0 | 100,000 |
| Total equity | 135,000 | 38,000 | 0 | 0 | 173,000 |
| Check (net assets = equity) | OK | OK | OK | OK | OK |
| Group line | Company A | Company B | Company C | Eliminations | Consolidated |
|---|---|---|---|---|---|
| Revenue | (250,000) | (120,000) | 0 | 0 | (370,000) |
| Other income | 0 | 0 | 0 | 0 | 0 |
| Intercompany income | (10,000) | 0 | 0 | 10,000 | 0 |
| Cost of sales | 120,000 | 50,000 | 0 | 0 | 170,000 |
| Operating expenses | 60,000 | 35,000 | 0 | 0 | 95,000 |
| Depreciation | 5,000 | 0 | 0 | 0 | 5,000 |
| Intercompany expense | 0 | 10,000 | 0 | (10,000) | 0 |
| Cash at bank | 80,000 | 25,000 | 0 | 0 | 105,000 |
| Trade receivables | 40,000 | 20,000 | 0 | 0 | 60,000 |
| Intercompany receivable | 10,000 | 0 | 0 | (10,000) | 0 |
| Fixed assets | 50,000 | 15,000 | 0 | 0 | 65,000 |
| Trade payables | (30,000) | (12,000) | 0 | 0 | (42,000) |
| Intercompany payable | 0 | (10,000) | 0 | 10,000 | 0 |
| Tax payable | (15,000) | 0 | 0 | 0 | (15,000) |
| Loans | 0 | 0 | 0 | 0 | 0 |
| Share capital | (10,000) | (1,000) | 0 | 0 | (11,000) |
| Retained earnings | (50,000) | (12,000) | 0 | 0 | (62,000) |
| Total (should be 0) | 0 | 0 | 0 | 0 | 0 |
| Check | OK |
| Account name | Debit | Credit | Net (Dr - Cr) | Group line |
|---|---|---|---|---|
| Sales | 0 | 250,000 | (250,000) | Revenue |
| Management fees - Company B | 0 | 10,000 | (10,000) | Intercompany income |
| Cost of goods sold | 120,000 | 0 | 120,000 | Cost of sales |
| Wages and overheads | 60,000 | 0 | 60,000 | Operating expenses |
| Depreciation | 5,000 | 0 | 5,000 | Depreciation |
| Bank | 80,000 | 0 | 80,000 | Cash at bank |
| Debtors | 40,000 | 0 | 40,000 | Trade receivables |
| Due from Company B | 10,000 | 0 | 10,000 | Intercompany receivable |
| Plant and equipment | 50,000 | 0 | 50,000 | Fixed assets |
| Creditors | 0 | 30,000 | (30,000) | Trade payables |
| Corporation tax | 0 | 15,000 | (15,000) | Tax payable |
| Share capital | 0 | 10,000 | (10,000) | Share capital |
| Retained earnings | 0 | 50,000 | (50,000) | Retained earnings |
| Totals | 365,000 | 365,000 | 0 | |
| Check: balances? | OK |
| Description | Group line | Debit | Credit | Net (Dr - Cr) |
|---|---|---|---|---|
| Remove management fee charged by A to B | Intercompany income | 10,000 | 0 | 10,000 |
| Remove management fee charged by A to B | Intercompany expense | 0 | 10,000 | (10,000) |
| Remove balance owed by B to A | Intercompany payable | 10,000 | 0 | 10,000 |
| Remove balance owed by B to A | Intercompany receivable | 0 | 10,000 | (10,000) |
| Total net (should be 0) | 0 | |||
| Check | OK |
How to Use It
- Export each company's trial balance for the same period end from Xero, QuickBooks, Sage, MYOB or your ledger.
- Paste each one into a company sheet and map every account to a group line.
- Enter the intercompany eliminations: sales and costs between group companies, and the balances they owe each other.
- Check every "Check" cell says OK.
- Copy the consolidated P&L and balance sheet into your monthly pack.
When to Move Beyond a Spreadsheet
A spreadsheet is fine for two or three simple companies. It starts costing more time than it saves when:
- you consolidate every month and re-export each trial balance by hand,
- a subsidiary reports in another currency,
- new accounts keep breaking the mapping, or
- a board, bank or investor wants the same pack on a fixed date.
At that point a consolidation app connects to each ledger and refreshes automatically. Joiin starts at $23/mo for one company billed annually ($29/mo for two), with eliminations on every plan. Compare the options in our consolidation software roundup or price your company count in the cost calculator.
Download the Template
Free Excel file. Works with any ledger that exports a trial balance.
No spam. Unsubscribe anytime. Skip the email, download directly →
FAQ
Is the template really free?
Yes. You can download it directly without an email. If you enter your email we'll also send the link to your inbox and a few short emails on consolidation; unsubscribe anytime.
Which accounting software does it work with?
Any that can export a trial balance to Excel or CSV, including Xero, QuickBooks, Sage, MYOB and FreeAgent. You paste each company's trial balance into its own sheet.
How many companies does it handle?
Three company sheets are included. To add more, copy a company sheet and add a column for it on the Consolidated TB sheet.
Does it handle foreign currency?
No. Translate each subsidiary's trial balance into the group currency before you paste it. Consolidation software such as Joiin (Pro and Max plans) or Fathom Pro handles multi-currency automatically.
What are the eliminations for?
When group companies trade with each other, those sales, costs and balances would be double-counted. The Eliminations sheet removes them, with two lines per elimination that cancel out.