Excel consolidation of subsidiary trial balances with Copilot, Claude or ChatGPT
Consolidating subsidiary trial balances in Excel comes down to four mechanical steps: map every local account to one group chart of accounts, add the mapped balances line by line, post elimination entries for intercompany balances, intercompany trading and the parent's investment, and prove that the result still balances. Copilot in Excel, Claude for Excel and ChatGPT for Excel can each read a multi-tab workbook, propose an account mapping, draft elimination entries and write the summing formulas, but none of them knows which balances are intercompany unless the workbook tells it.
The worked example below uses a parent and two wholly owned subsidiaries with different charts of accounts. The aggregate trial balance shows revenue of 850,000 and profit of 115,000; after six elimination entries the group reports revenue of 800,000, profit of 112,500 and total assets of 882,500. Those three figures are the answer key a controller can use to test whatever an AI assistant produces from the same data.
The accounting itself does not change because an assistant is involved. Control, the scope of consolidation and the treatment of non-controlling interests follow IFRS 10 and ASC 810, covered in detail in how consolidation of subsidiaries is performed under IFRS 10 and ASC 810. The workbook mechanics are where the time goes in a small group without a consolidation module, and they are where AI help is most useful and most likely to introduce silent errors.
··········
WHY FINANCE TEAMS CONSOLIDATE TRIAL BALANCES IN EXCEL.
Groups with two to ten entities often run each subsidiary on its own ledger, sometimes on different software, and produce group accounts once a month or once a quarter from exported trial balances. Each export uses its own account numbers and names, so the first job every period is translating "Bank current account" in one entity and "10100 Cash at bank" in another into the same group line.
The second job is removing transactions the group conducted with itself. A sale from the parent to a subsidiary is revenue in one ledger and a purchase in another, a loan is a receivable in one and a payable in another, and the parent's investment mirrors the subsidiary's share capital. Left in place, these items inflate revenue, assets and liabilities. The general logic of these adjustments is set out in consolidation basics and elimination entries.
The third job is control: every period the consolidated trial balance must sum to zero, the intercompany pairs must agree before they are eliminated, and the consolidated profit must reconcile to the sum of entity profits less the eliminations that affect profit. These checks are where an AI-built workbook needs the most scrutiny.
··········
COPILOT, CLAUDE AND CHATGPT IN EXCEL: DOCUMENTED REQUIREMENTS FOR CONSOLIDATION WORK.
The table below summarizes what each vendor's official documentation stated when verified on November 4, 2026. Anything not listed was not documented on the pages reviewed and should be tested before relying on it.
........
Item | Copilot in Excel | Claude for Excel | ChatGPT for Excel |
Eligible plans | Microsoft 365 Premium, commercial Copilot, Copilot Chat-eligible business plans, Personal or Family with AI credits | Claude Pro, Max, Team, Enterprise | All ChatGPT plans (general availability since May 5, 2026) |
Platforms | Windows, Mac, web; chat and plan modes on iPad | Excel on the web, Windows, Mac (minimum builds apply) | Excel and Google Sheets |
Multi-sheet work | Imports data from another Excel file on request | Works across multi-tab workbooks | Reasons across workbooks |
Traceability | Changes visible to anyone with file access | Cell-level citations | Links answers to referenced cells |
Editing control | Requires Automatic calculation | Warns before overwriting data | Asks permission before editing; edits can be undone |
Stated limits | Not available on checked-out SharePoint files on Windows or Mac | No data tables, macros or VBA | Complex formulas may need manual refinement |
........
Microsoft documents that Copilot in Excel can import data from another Excel file when the user names the file in the Copilot pane, which suits groups that keep each entity's trial balance in a separate workbook. Microsoft does not state limits on the number of files or their size on that page. Claude for Excel and ChatGPT for Excel both describe working across tabs and linking answers to cells, which makes it practical to ask where a consolidated figure came from and click through to the source.
For teams that prefer no AI at all for the arithmetic, Excel's own Data, Consolidate command can sum ranges from several sheets either by position or by matching labels. Microsoft notes that labels must match exactly and that labels present on only some sheets still appear in the result, so the mapping to a common chart of accounts has to be done first in either approach.
··········
WORKED EXAMPLE: THREE TRIAL BALANCES AND A GROUP ACCOUNT MAPPING.
The group in this illustrative example has a parent and two subsidiaries, Sub A and Sub B, all reporting in USD, both wholly owned and both formed by the parent, so there is no goodwill, no pre-acquisition equity and no non-controlling interest. During the year the parent sold goods to Sub A for 50,000 at a 25% margin on selling price, and Sub A still holds 10,000 of those goods in inventory. Sub A owes the parent 20,000 for the purchases. The parent lent Sub B 100,000 and charged 5,000 of interest. The parent's investment is 80,000 in Sub A and 50,000 in Sub B.
The first step is the mapping. Each local account receives a group line, and the mapping table becomes a permanent tab that the AI reads every period.
........
Entity | Local account | Group line |
Parent | 1010 Operating bank | Cash |
Sub A | Bank current account | Cash |
Sub B | 10100 Cash at bank | Cash |
Parent | 1250 Due from Sub A | Intercompany receivable |
Sub A | 2150 Due to parent | Intercompany payable |
Sub B | 2400 Loan from parent | Intercompany loan payable |
Parent | 4010 Sales to group companies | Revenue |
........
A first prompt asks the assistant to build that mapping from the exported trial balances, while keeping the decision with the accountant.
The tabs TB_Parent, TB_SubA and TB_SubB each hold a trial balance with columns Account and Amount (debits positive, credits negative). The tab GroupCOA lists the group lines. Create a tab Mapping with columns Entity, Local account, Group line. Map every local account to exactly one group line. Flag any account you are unsure about in a column Review instead of guessing, and flag every account that looks like a balance with another group company.Once the mapping is approved and each trial balance carries a Group line column looked up from the Mapping tab, the aggregation is plain Excel. A formula such as the one below, repeated in one column per entity, adds that entity's mapped balances to the group line in column A; any assistant can write it, and it is easier to audit than a pasted value.
=SUMIFS(TB_SubA[Amount], TB_SubA[Group line], $A2)··········
ELIMINATION ENTRIES FOR THE CONSOLIDATED TRIAL BALANCE.
The second prompt asks for the eliminations, with the facts the assistant cannot infer stated explicitly.
Using the aggregated trial balance on tab Consolidation, draft elimination entries in a new tab Eliminations with columns Entry, Account, Debit, Credit. Facts: the parent's investment of 130,000 equals the share capital of Sub A (80,000) and Sub B (50,000), with no pre-acquisition reserves. The parent sold goods to Sub A for 50,000 at a 25% margin on selling price; Sub A still holds 10,000 of those goods. Sub A owes the parent 20,000. The parent lent Sub B 100,000 and charged 5,000 interest. Do not post anything else. Show that total debits equal total credits.The correct entries, computed independently, are the following. The unrealized profit is 25% of the 10,000 still in Sub A's inventory, or 2,500.
........
Entry | Account | Debit | Credit |
E1 | Share capital (Sub A and Sub B) | 130,000 | |
E1 | Investment in subsidiaries | 130,000 | |
E2 | Revenue | 50,000 | |
E2 | Cost of sales | 50,000 | |
E3 | Cost of sales | 2,500 | |
E3 | Inventory | 2,500 | |
E4 | Intercompany payable | 20,000 | |
E4 | Intercompany receivable | 20,000 | |
E5 | Intercompany loan payable | 100,000 | |
E5 | Intercompany loan receivable | 100,000 | |
E6 | Interest income | 5,000 | |
E6 | Interest expense | 5,000 | |
Total | 307,500 | 307,500 |
........
Entry E2 removes the intercompany sale from both revenue and cost of sales; it leaves profit unchanged because it removes the same amount from each side. Entry E3 is the only elimination in this example that reduces group profit, because the group has not yet sold those goods to an outside customer. Readers who want to check the debit and credit logic of each line can compare it with the approach in journal entries with Copilot, Claude or ChatGPT: draft and check before posting.
··········
THE EXPECTED CONSOLIDATED RESULT TO CHECK THE AI OUTPUT.
The consolidation tab below is the expected result, computed in Python from the illustrative trial balances. Debits are positive and credits are negative, so every column sums to zero.
........
Group line | Parent | Sub A | Sub B | Eliminations | Consolidated |
Cash | 60,000 | 25,000 | 35,000 | 0 | 120,000 |
Trade receivables | 90,000 | 40,000 | 25,000 | 0 | 155,000 |
Intercompany receivable | 20,000 | 0 | 0 | -20,000 | 0 |
Intercompany loan receivable | 100,000 | 0 | 0 | -100,000 | 0 |
Inventory | 70,000 | 30,000 | 0 | -2,500 | 97,500 |
Property, plant and equipment | 300,000 | 60,000 | 150,000 | 0 | 510,000 |
Investment in subsidiaries | 130,000 | 0 | 0 | -130,000 | 0 |
Trade payables | -75,000 | -15,000 | -12,000 | 0 | -102,000 |
Intercompany payable | 0 | -20,000 | 0 | 20,000 | 0 |
Intercompany loan payable | 0 | 0 | -100,000 | 100,000 | 0 |
Share capital | -400,000 | -80,000 | -50,000 | 130,000 | -400,000 |
Retained earnings (opening) | -250,000 | -10,000 | -8,000 | 0 | -268,000 |
Revenue | -500,000 | -220,000 | -130,000 | 50,000 | -800,000 |
Cost of sales | 300,000 | 140,000 | 0 | -47,500 | 392,500 |
Operating expenses | 160,000 | 50,000 | 85,000 | 0 | 295,000 |
Interest income | -5,000 | 0 | 0 | 5,000 | 0 |
Interest expense | 0 | 0 | 5,000 | -5,000 | 0 |
Total | 0 | 0 | 0 | 0 | 0 |
........
Three reconciliations confirm the result. Entity profits are 45,000 for the parent, 30,000 for Sub A and 40,000 for Sub B, a total of 115,000; deducting the 2,500 unrealized profit gives consolidated profit of 112,500, which equals consolidated revenue of 800,000 less cost of sales of 392,500 and operating expenses of 295,000. Total consolidated assets of 882,500 equal liabilities of 102,000 plus share capital of 400,000 and closing retained earnings of 380,500, being opening retained earnings of 268,000 plus the 112,500 profit for the year. Every intercompany line in the consolidated column is zero.
A useful final prompt asks the assistant to run those same checks and report them, so that the reviewer reads a list of passes and failures instead of scanning the grid.
On the Consolidation tab, check and list: (1) each entity column and the consolidated column sum to zero; (2) every intercompany line is zero after eliminations; (3) consolidated profit equals the sum of entity profits less eliminations that affect profit; (4) total assets equal total liabilities plus equity including profit for the year. Cite the cells you used. Do not change any values.··········
REVIEW AND CONTROL STEPS FOR AI-ASSISTED CONSOLIDATION.
The most frequent error in this task is a plausible but wrong mapping. An assistant will usually map "Due to parent" correctly, but an account named "Other payables" that contains a balance with a sister company will be mapped to trade payables, and the intercompany pair will then fail to eliminate. The defence is a mapping tab that the accountant approves once and that changes only through a reviewed request, plus a rule that new local accounts appear in a Review column before they flow into the consolidation.
The second error is eliminating balances that do not agree. If the parent shows 20,000 receivable and Sub A shows 18,000 payable because a payment is in transit, an assistant asked to "eliminate intercompany balances" may post 20,000 on both sides and push the 2,000 difference somewhere it does not belong. Intercompany pairs should be reconciled and agreed first, and the elimination should be posted only for the agreed amount.
The third error is overlooking unrealized profit. Removing the intercompany sale from revenue and cost of sales is the step most assistants propose unprompted; reducing inventory for profit the group has not yet earned usually needs the margin and the closing stock to be stated in the prompt. Where entities report in different currencies, translation adds a further layer that the prompt above does not cover; the rules are explained in how foreign operations are translated and consolidated under IAS 21 and ASC 830.
Some decisions stay with people regardless of the tool: which entities are in scope, whether a balance is intercompany, the margin used for unrealized profit, and the approval of every elimination before the consolidated numbers leave finance. Anthropic's documentation for Claude for Excel states that it is not recommended for audit-critical calculations without verification or for final deliverables without human review, and OpenAI notes that complex formulas or edge cases may still require manual refinement.
··········
DATA HANDLING NOTES FROM THE VENDOR DOCUMENTATION.
Trial balances contain payroll, margins and intercompany pricing, so data handling belongs in the review. Microsoft states that changes Copilot saves are visible to anyone with access to the file, that individual Copilot conversations cannot yet be deleted although full activity history can be, and that Copilot in Excel cannot be turned off separately from Microsoft Copilot Chat in organizational settings.
Anthropic states that Claude for Excel sends conversations, including workbook content Claude reads, to Anthropic, that inputs and outputs are deleted on the backend within 30 days except in cases listed in its retention policy, that the add-in does not inherit an organization's custom retention settings and that its activity is not included in Enterprise audit logs, although Enterprise organizations with the Compliance API enabled have sessions included there. Anthropic also warns that spreadsheets from external sources can carry hidden prompt injections, which is relevant when subsidiary trial balances arrive as files from outside the parent's own systems.
OpenAI states that data shared with ChatGPT Enterprise is not used to train or improve its models by default, and that access to ChatGPT for Excel in Enterprise, Edu and Teacher workspaces is off by default until an admin enables it.
··········
AI-ASSISTED TRIAL BALANCE CONSOLIDATION: WHAT THE TOOLS DO AND WHAT STAYS WITH THE CONTROLLER.
All three assistants can map local accounts, aggregate entity trial balances with auditable formulas, draft the standard eliminations and run tie-out checks inside Excel, and each vendor documents some form of cell-level traceability or edit confirmation. In the example, a correct output shows consolidated revenue of 800,000, profit of 112,500, total assets of 882,500 and eliminations of 307,500 on each side; any other figure from the same data points to a mapping, elimination or unrealized-profit error.
The parts that depend on the group's facts remain judgement calls: the consolidation scope under IFRS 10 or ASC 810, the identification and agreement of intercompany balances, the margin for unrealized profit, currency translation and non-controlling interests where ownership is below 100%. A stable mapping tab, agreed intercompany pairs and a written list of checks make the AI's work reviewable; without them, a balanced consolidated trial balance can still be wrong.
··········
FOLLOW US FOR MORE.
·····
DATA STUDIOS
·····
datastudios.org
