Accounting
ERP
Financial Management
Consolidation Worksheet Template: Combining Entities, Eliminations and Noncontrolling Interest
Last updated: October 17, 2026 · Independent guide · We do not sell rankings or coverage.
A consolidation worksheet places each entity’s trial balance side by side, adds a column of elimination and adjustment entries, and produces the consolidated balances that go into the group’s financial statements. The standard layout runs from left to right: account, parent, each subsidiary, combined total, eliminations, and consolidated. Below is a copyable column layout for Excel, a full worked example with intercompany, investment-in-subsidiary and noncontrolling interest (NCI) entries, and the tie-out checks that prove the worksheet is right before you publish numbers.
How to use this template
Build the workbook with one tab per entity trial balance, one tab for elimination entries (Elims), one consolidation worksheet tab and one tab for the statements. Paste each entity’s period-end trial balance into its own tab with a column mapping every local account to a group code; the worksheet pulls balances by group code with SUMIFS.
Use one sign convention everywhere: debits positive, credits negative. Every column then sums to zero, which makes the first tie-out check a single formula. Keep consolidation entries only on the Elims tab and never in any entity’s own ledger. Under US GAAP you consolidate an entity in which you hold a controlling financial interest, and ASC 810-10-15-8 states that “the usual condition for a controlling financial interest is ownership of a majority voting interest” (Deloitte Roadmap, no date shown).
The consolidation worksheet template
Tab 1: consolidation worksheet columns. Set these headers in row 4 and start account rows in row 5. The formulas assume each entity tab holds the amount in column C and the mapped group code in column D.
| Column | Header | What goes in it | Example formula (row 5) |
|---|---|---|---|
| A | Group account code | One code from the group chart of accounts per row. Every entity maps to these rows. | Typed; no formula |
| B | Account name | Group account name, with intercompany accounts on their own rows (1150, 2150, 4100, 5100). | Typed |
| C | Statement line | Balance sheet or income statement line the row rolls up to (for example, “Cash” or “Revenue”). | Typed; used by SUMIFS on the statements tab |
| D | Parent | Parent trial balance, signed: debits positive, credits negative. | =SUMIFS(TB_Parent!$C:$C,TB_Parent!$D:$D,$A5) |
| E | Sub A | First subsidiary trial balance, same sign convention. Note the ownership percentage in the header. | =SUMIFS(TB_SubA!$C:$C,TB_SubA!$D:$D,$A5) |
| F | Sub B (add one column per entity) | Each further subsidiary. Insert columns before G so the totals expand. | =SUMIFS(TB_SubB!$C:$C,TB_SubB!$D:$D,$A5) |
| G | Combined | Sum of all entity columns before any consolidation entry. | =SUM(D5:F5) |
| H | Eliminations and adjustments | Net of every consolidation entry on the Elims tab for this account, signed. | =SUMIFS(Elims!$D:$D,Elims!$B:$B,$A5)-SUMIFS(Elims!$E:$E,Elims!$B:$B,$A5) |
| I | Consolidated | The number that goes to the consolidated statements. | =G5+H5 |
| J | Entry references | The entry numbers that touch this row (E1, E5), so a reviewer can trace them. | =TEXTJOIN(", ",TRUE,UNIQUE(FILTER(Elims!$A:$A,Elims!$B:$B=$A5,""))) |
Tab 2: elimination journal (Elims). Every consolidation entry lives here as a balanced, referenced journal, so column H on the worksheet is always a formula and never a typed number.
| Column | Header | What goes in it | Rule |
|---|---|---|---|
| A | Entry ref | E1, E2 and so on. One reference per balanced entry. | Each ref must sum to zero |
| B | Group account code | Must match a code in column A of the worksheet. | Data validation list from the worksheet codes |
| C | Description and support | What the entry removes and where the support is (reconciliation, ownership schedule, acquisition memo). | Required for every line |
| D / E | Debit / Credit | Positive amounts only, one side per line. | Total debits equal total credits |
Tie-out check block. Put these checks above row 4 on the worksheet tab, format any failure in red, and release nothing until every check passes.
| Check | Formula or test | Expected result | Worked example |
|---|---|---|---|
| 1. Each entity trial balance | =SUM(D5:D500) for each entity column | 0 | 0 for Parent and Sub Co |
| 2. Nothing unmapped | Source trial balance total of absolute values compared with the mapped column total of absolute values | Difference of 0 | 0 |
| 3. Intercompany pairs agree before elimination | Receivable on one side equals payable on the other; intercompany sales equal intercompany purchases | Difference of 0 | $25,000 vs $25,000; $150,000 vs $150,000 |
| 4. Eliminations balance | =SUM(Elims!D:D)-SUM(Elims!E:E), and each ref nets to 0 | 0 | $1,325,000 debits and credits |
| 5. Intercompany and investment rows clear | Consolidated column for 1150, 2150, 4100, 4500, 4600, 5100, 6500 and 1600 | 0 | All 0 |
| 6. Consolidated column balances | =SUM(I5:I500) | 0 | 0 |
| 7. NCI rollforward | Opening NCI + NCI share of net income − dividends to NCI = closing NCI | Agrees to row 3500 | $200,000 + $40,000 − $10,000 = $230,000 |
| 8. Parent share of net income | Parent net income excluding subsidiary dividends + ownership % × subsidiary net income | Agrees to consolidated net income attributable to parent | $360,000 + $160,000 = $520,000 |
| 9. Goodwill | Consolidated goodwill equals the acquisition calculation less any impairment | Agrees | $400,000 |
Worked example: Parent Co and an 80%-owned subsidiary
All figures in this section are an illustration. On January 1, 2026, Parent Co bought 80% of Sub Co for $800,000 in cash. At that date Sub Co’s equity was $600,000 (common stock $100,000 and retained earnings $500,000), and for simplicity its book values equal the fair values of its identifiable assets and liabilities. The fair value of the 20% noncontrolling interest was $200,000. Parent Co carries the investment at cost in its own books. Both report in USD, and the worksheet date is December 31, 2026.
During 2026, Parent Co sold goods costing $100,000 to Sub Co for $150,000, and Sub Co sold all of them to outside customers before year end, so no intercompany profit remains in inventory. Parent Co charged Sub Co a $60,000 management fee, of which $25,000 was unpaid at year end. Sub Co earned net income of $200,000 and paid a $50,000 dividend, so Parent Co received $40,000 and the outside shareholders received $10,000.
Step 1: load and combine the trial balances. Credits are shown in parentheses.
| Group account | Parent | Sub Co (80%) | Combined |
|---|---|---|---|
| 1000 Cash | $300,000 | $150,000 | $450,000 |
| 1100 Accounts receivable, third party | $400,000 | $250,000 | $650,000 |
| 1150 Intercompany receivable | $25,000 | 0 | $25,000 |
| 1200 Inventory | $350,000 | $300,000 | $650,000 |
| 1500 Property and equipment, net | $1,200,000 | $500,000 | $1,700,000 |
| 1600 Investment in Sub Co | $800,000 | 0 | $800,000 |
| 2000 Accounts payable, third party | ($475,000) | ($425,000) | ($900,000) |
| 2150 Intercompany payable | 0 | ($25,000) | ($25,000) |
| 3000 Common stock | ($1,000,000) | ($100,000) | ($1,100,000) |
| 3100 Retained earnings, January 1 | ($1,200,000) | ($500,000) | ($1,700,000) |
| 3200 Dividends declared | 0 | $50,000 | $50,000 |
| 4000 Sales, third party | ($1,850,000) | ($900,000) | ($2,750,000) |
| 4100 Intercompany sales | ($150,000) | 0 | ($150,000) |
| 4500 Management fee income | ($60,000) | 0 | ($60,000) |
| 4600 Dividend income from Sub Co | ($40,000) | 0 | ($40,000) |
| 5000 Cost of goods sold, third-party goods | $1,300,000 | $350,000 | $1,650,000 |
| 5100 Cost of goods sold, goods bought from Parent | 0 | $150,000 | $150,000 |
| 6000 Operating expenses | $400,000 | $140,000 | $540,000 |
| 6500 Management fee expense | 0 | $60,000 | $60,000 |
| Check: trial balance total (must be 0) | 0 | 0 | 0 |
Step 2: post the consolidation entries on the Elims tab.
| Ref | Account | Debit | Credit |
|---|---|---|---|
| E1 | 4100 Intercompany sales | $150,000 | |
| E1 | 5100 Cost of goods sold, goods bought from Parent | $150,000 | |
| E2 | 4500 Management fee income | $60,000 | |
| E2 | 6500 Management fee expense | $60,000 | |
| E3 | 2150 Intercompany payable | $25,000 | |
| E3 | 1150 Intercompany receivable | $25,000 | |
| E4 | 4600 Dividend income from Sub Co (80%) | $40,000 | |
| E4 | 3500 Noncontrolling interest (20%) | $10,000 | |
| E4 | 3200 Dividends declared (Sub Co) | $50,000 | |
| E5 | 3000 Common stock (Sub Co) | $100,000 | |
| E5 | 3100 Retained earnings, January 1 (Sub Co) | $500,000 | |
| E5 | 1700 Goodwill | $400,000 | |
| E5 | 1600 Investment in Sub Co | $800,000 | |
| E5 | 3500 Noncontrolling interest (fair value at acquisition) | $200,000 | |
| E6 | 7900 Net income attributable to NCI | $40,000 | |
| E6 | 3500 Noncontrolling interest | $40,000 | |
| Total | Must agree | $1,325,000 | $1,325,000 |
E1 removes the intercompany sale and Sub Co’s matching cost, leaving only the outside sale and Parent’s original $100,000 cost. E2 and E3 remove the management fee and the unpaid balance. E4 removes the dividend, because cash moving inside the group is not income to the group; the 20% paid to outside shareholders reduces NCI. E5 replaces the investment with Sub Co’s net assets, recognizes goodwill of $400,000 ($800,000 paid plus $200,000 NCI fair value less $600,000 net assets) and sets up opening NCI. E6 gives the outside shareholders 20% of Sub Co’s $200,000 net income.
Step 3: read the consolidated column.
| Group account | Combined | Eliminations | Consolidated |
|---|---|---|---|
| 1000 Cash | $450,000 | 0 | $450,000 |
| 1100 Accounts receivable, third party | $650,000 | 0 | $650,000 |
| 1150 Intercompany receivable | $25,000 | ($25,000) | 0 |
| 1200 Inventory | $650,000 | 0 | $650,000 |
| 1500 Property and equipment, net | $1,700,000 | 0 | $1,700,000 |
| 1600 Investment in Sub Co | $800,000 | ($800,000) | 0 |
| 1700 Goodwill | 0 | $400,000 | $400,000 |
| 2000 Accounts payable, third party | ($900,000) | 0 | ($900,000) |
| 2150 Intercompany payable | ($25,000) | $25,000 | 0 |
| 3000 Common stock | ($1,100,000) | $100,000 | ($1,000,000) |
| 3100 Retained earnings, January 1 | ($1,700,000) | $500,000 | ($1,200,000) |
| 3200 Dividends declared | $50,000 | ($50,000) | 0 |
| 3500 Noncontrolling interest | 0 | ($230,000) | ($230,000) |
| 4000 Sales, third party | ($2,750,000) | 0 | ($2,750,000) |
| 4100 Intercompany sales | ($150,000) | $150,000 | 0 |
| 4500 Management fee income | ($60,000) | $60,000 | 0 |
| 4600 Dividend income from Sub Co | ($40,000) | $40,000 | 0 |
| 5000 Cost of goods sold, third-party goods | $1,650,000 | 0 | $1,650,000 |
| 5100 Cost of goods sold, goods bought from Parent | $150,000 | ($150,000) | 0 |
| 6000 Operating expenses | $540,000 | 0 | $540,000 |
| 6500 Management fee expense | $60,000 | ($60,000) | 0 |
| 7900 Net income attributable to NCI | 0 | $40,000 | $40,000 |
| Check: column total (must be 0) | 0 | 0 | 0 |
Consolidated net income is $560,000, of which $40,000 is attributable to NCI and $520,000 to Parent Co. Total assets of $3,850,000 equal liabilities of $900,000 plus equity of $2,950,000 (common stock $1,000,000, retained earnings $1,720,000 and NCI $230,000).
Step 4: run the checks. The last column of the tie-out table shows this example’s results. Check 8 is the quickest test of the investment and NCI entries; adjust it if you also have unrealized inventory profit or amortization of fair value step-ups.
Walking through each part of the template
Mapping and the group chart of accounts
Most consolidation errors start in the mapping. Give intercompany receivables, payables, sales, purchases, fees, loans and interest their own group codes, and require each entity to book intercompany activity only to those accounts.
Entity columns
Load each trial balance at the same period end, after the entity has closed and reconciled its books. Translate foreign subsidiaries into the reporting currency on the entity tab first, with the translation difference on a cumulative translation adjustment row.
Eliminations
ASC 810-10-45-1 states that “intra-entity balances and transactions shall be eliminated” (PwC Viewpoint, no date shown). Fix mismatched intercompany balances in the entity books before you eliminate; a plug on the Elims tab hides the error. Unrealized profit in inventory, intercompany loans and multi-currency differences need their own elimination entries, which this example does not cover.
Investment in subsidiary and goodwill
Under US GAAP, ASC 805-20-30-1 requires the acquirer to measure a noncontrolling interest in the acquiree at its fair value at the acquisition date, and goodwill includes the fair value of that NCI (Deloitte Roadmap, no date shown). Keep an acquisition schedule (price, NCI fair value, net assets, step-ups, goodwill) and repeat E5 from it every period. If the parent uses the equity method in its own books, also eliminate its equity in subsidiary earnings and the subsidiary’s post-acquisition retained earnings.
Noncontrolling interest
ASC 810-10-45-20 requires net income or loss and comprehensive income or loss to be attributed to the parent and the noncontrolling interest (Deloitte Roadmap, no date shown). NCI is reported within equity, and consolidated net income and comprehensive income are presented with the parent and NCI portions shown separately (Deloitte Roadmap, no date shown). Give NCI its own balance sheet row and its own income statement row, as in rows 3500 and 7900, so the statements tab can pick them up directly.
Common mistakes
- Typing numbers into the eliminations column. Hard-coded amounts cannot be traced, and they survive into next month’s copy with stale values. Drive column H from the Elims tab.
- Eliminating only the parent’s share. Intercompany balances and profit are removed in full even when you own 80%; NCI is handled through its own entries.
- Measuring NCI at its share of book value. US GAAP requires fair value at acquisition, so NCI and goodwill both come out wrong if you take 20% of net assets.
- Forgetting the dividend split. Eliminating the whole subsidiary dividend against the parent’s dividend income leaves the worksheet out of balance by the NCI share.
- Rolling the workbook forward without refreshing mappings. A new local account maps to nothing and drops out; check 2 catches it.
- Different period ends or cutoffs. If one side books an intercompany charge on the 31st and the other on the 2nd, check 3 fails. Our guide to general ledger reconciliation covers the account reconciliations each entity should finish before it submits a trial balance.
When to move from a spreadsheet to software
This workbook suits a parent and two or three single-currency subsidiaries with modest intercompany activity. It starts to fail when entities repost after you have loaded their trial balances, when you add foreign currencies and translation, when several people edit the same file during close, or when the auditors ask you to trace a consolidated figure back to source transactions. Our article on the wider ERP-led financial close covers the steps around consolidation.
Oracle NetSuite OneWorld identifies intercompany sales and billing lines, intercompany inventory transfers and drop shipments, and journal lines marked for elimination, then generates elimination journals at period end, with currency differences posted to a Cumulative Translation Adjustment-Elimination account (Oracle NetSuite Help, no date shown). The documentation we reviewed does not describe automatic NCI entries, so plan for those as recurring journals.
Sage Intacct sells consolidation as a subscription in three tiers. Domestic Consolidation auto-eliminates inter-entity transactions, Global Consolidation adds currency translation with automated cumulative translation adjustments, and Advanced Ownership Consolidation auto-generates non-controlling interest entries based on ownership percentages of at least 50% (Sage Intacct Help, last modified October 8, 2026). Advanced Ownership is the only tier that automates the NCI entries in this template.
Microsoft Dynamics 365 Business Central combines business units into a consolidated company, but Microsoft states that “processing consolidation eliminations is a manual process” (Microsoft Learn, April 7, 2026). You still prepare the entries in this template; you post them as journals instead of keeping them in a spreadsheet.
How we researched this
We reviewed the pages that rank for “consolidation worksheet” on October 17, 2026. They were mostly vendor blogs and glossaries (LiveFlow, Qoyod, DualEntry) and an IFRS training site (CPDbox); two gave worked entries, one of them under IFRS, and none gave a copyable layout with formulas and tie-out checks. We based the accounting on ASC 810 and ASC 805 as presented in the Deloitte Roadmaps and PwC Viewpoint, and checked the software capabilities against vendor documentation from Oracle, Sage and Microsoft on October 17, 2026. The worked example is our own illustration. We have no commercial relationship with any vendor named.
Related guides
- Trial Balance Template: Prepare, Check and Fix It
- Month-End Close Checklist Template (2026)
- Record-to-Report Process: Steps, Owners and Systems
Frequently asked questions
What columns does a consolidation worksheet need?
At minimum you need the account, one column per entity (parent first, then each subsidiary), a combined total, a net eliminations column and a consolidated column. Adding a statement-line mapping column and an entry-reference column makes the worksheet much easier to review and to turn into financial statements.
How do you calculate noncontrolling interest on a consolidation worksheet?
Start with NCI at its fair value on the acquisition date, add the NCI ownership percentage of the subsidiary’s net income for each period, and subtract dividends paid to the outside shareholders. In the example, $200,000 plus 20% of $200,000 less $10,000 gives closing NCI of $230,000.
Do you eliminate 100% of intercompany transactions with a partly owned subsidiary?
Yes. ASC 810-10-45-18 says the amount of intra-entity income or loss eliminated is not affected by a noncontrolling interest. The ownership percentage affects how the result is attributed between the parent and NCI, not how much is eliminated.
Why doesn’t my consolidation worksheet balance?
The usual causes are an entity trial balance that does not sum to zero, a local account that was never mapped, an elimination entry with unequal debits and credits, or a subsidiary dividend eliminated without the NCI share. Run the checks in order, because each one narrows down where the difference sits.
How many entities can you consolidate in Excel?
There is no fixed limit, but the workload grows with each entity, currency and intercompany flow. A spreadsheet stays manageable for a handful of single-currency entities; foreign subsidiaries, partial ownership and frequent reposting are the usual reasons to move to software.
Sources
- Deloitte, Roadmap: Consolidation, Appendix D.1 General Consolidation Principles (quoting ASC 810-10-15-8), no date shown. dart.deloitte.com
- PwC Viewpoint, Consolidation and equity method guide, 8.2 Intercompany transactions (quoting ASC 810-10-45-1 and 810-10-45-18), no date shown. viewpoint.pwc.com
- Deloitte, Roadmap: Business Combinations, 6.4 Partial Acquisitions (ASC 805-20-30-1 and 805-30-30-1), no date shown. dart.deloitte.com
- Deloitte, Roadmap: Noncontrolling Interests, 1.7 Presentation and Disclosure (ASC 810-10-45-16), no date shown. dart.deloitte.com
- Deloitte, Roadmap: Noncontrolling Interests, 6.1 Attribution of Income and Other Comprehensive Income (ASC 810-10-45-20), no date shown. dart.deloitte.com
- Oracle NetSuite Help, Intercompany Elimination Overview, no date shown (checked October 17, 2026; Oracle sells NetSuite). docs.oracle.com
- Sage Intacct Help, About the Consolidation subscriptions, last modified October 8, 2026 (Sage sells Intacct). intacct.com
- Microsoft Learn, Consolidating financial data from multiple companies (Business Central), April 7, 2026 (Microsoft sells Business Central). learn.microsoft.com
Sherman Hsieh
Independent analysis of enterprise software — ERP, CRM and more
