Consolidate Multi-Entity Financials in Excel with Claude AI
Progress1 of 4
1
The consolidated report
2
Prepare entity data
3
Prompt, FAQ & related
4
Quick quiz
Section 01
What the consolidated financial report looks like
Consolidate Multi-Entity Financials in Excel with Claude AI
This course gives you a Claude prompt that takes individual P&L data from two or more entities, applies the intercompany eliminations you specify, and produces a consolidated group-level report in Excel. The output shows each entity's figures side by side, a clear eliminations column, and the final consolidated numbers — ready for board reporting or audit review.
The Consolidated Income Statement
Below is what Claude produces for a two-entity group. Entity A and Entity B are shown individually, intercompany eliminations appear in red, and the Consolidated column reflects the true group position after eliminations.
A
B
C
D
E
1
Line Item
Entity A
Entity B
Eliminations
Consolidated
2
Revenue
1,200,000
800,000
-150,000
1,850,000
3
COGS
480,000
340,000
-150,000
670,000
4
Gross Profit
720,000
460,000
0
1,180,000
5
OpEx
520,000
350,000
-30,000
840,000
6
Net Income
200,000
110,000
30,000
340,000
Entity A sells $150k of services to Entity B — this appears as revenue for A and COGS for B. The elimination removes both sides so the consolidated report reflects only external transactions. The $30k OpEx elimination is a management fee charged between entities.
Each entity's data shown individually so you can drill into any number
Eliminations column removes intercompany revenue, COGS, and management fees
Consolidated column = sum of all entities plus eliminations (which are negative)
Claude flags any intercompany imbalance rather than silently forcing it to zero
This course is part of the AI for Finance Teams collection. For entity-level analysis before consolidation, the financial KPI dashboard course helps you build per-entity dashboards that feed into this group view.
Prepare Your Entity Data and Intercompany Transactions
Claude needs two things: a P&L summary for each entity and a list of intercompany transactions to eliminate. Both must use the same chart of accounts — if Entity A calls it "Revenue" and Entity B calls it "Sales", Claude can't consolidate them without you standardising the names first.
Entity P&L Format
A
B
C
1
Line Item
Entity A
Entity B
2
Revenue
1,200,000
800,000
3
COGS
480,000
340,000
4
OpEx
520,000
350,000
01
Standardise line-item names across entities
"Revenue", "Sales", "Turnover" — pick one and use it everywhere. Claude matches rows by exact line-item text. If the names differ, the consolidation will produce duplicate rows instead of a clean sum.
02
List every intercompany transaction
For each intercompany transaction, state: the selling entity, the buying entity, the amount, and which P&L lines it affects. For example: "Entity A charges Entity B $150,000 for manufacturing services. This appears as Revenue in A and COGS in B."
03
Confirm both sides of each transaction match
If Entity A records $150,000 of intercompany revenue but Entity B only records $145,000 of intercompany COGS, there's a $5,000 imbalance. Identify and resolve these before prompting — or let Claude flag them for you.
04
Specify the reporting currency
If entities report in different currencies, include the exchange rate for each. Tell Claude whether to use the closing rate (for balance sheet items) or the average period rate (for P&L items). This course focuses on P&L, so the average rate is typically what you need.
Common mistake: Forgetting management fees, intercompany loan interest, or shared service allocations. These are intercompany transactions too and must be eliminated. If they aren't in your list, they'll inflate the consolidated OpEx.
The Claude Prompt for Multi-Entity Consolidation
Paste this prompt into Claude with your entity data and intercompany transactions. Claude produces a side-by-side consolidation with a clear eliminations column and flags any imbalances.
Prompt — paste into Claude
I need to consolidate the financial statements of multiple entities into a single group-level report in Excel. Below are the individual entity P&Ls and the intercompany transactions to eliminate.
ENTITY P&L DATA (columns: Line Item, then one column per entity):
[Paste your entity data here — use identical line-item names across all entities]
INTERCOMPANY TRANSACTIONS TO ELIMINATE:
[List each transaction, e.g.:
- Entity A charges Entity B $150,000 for manufacturing services (Revenue in A, COGS in B)
- Entity A charges Entity B $30,000 management fee (Revenue in A, OpEx in B)]
Reporting currency: [e.g. USD]
Exchange rates (if applicable): [e.g. Entity B reports in EUR, use average rate 1 EUR = 1.08 USD]
Requirements:
- Create a consolidation table with columns: Line Item | Entity A | Entity B | [Entity C if applicable] | Eliminations | Consolidated.
- The Consolidated column = sum of all entity columns plus the Eliminations column.
- For each intercompany transaction, create an elimination entry that removes the amount from the relevant revenue and expense lines. Both sides of the elimination must net to zero on the Consolidated column.
- If both sides of an intercompany transaction do not match (e.g. A records $150,000 but B records $145,000), flag the imbalance in a separate row — do NOT force the amounts to match.
- Include subtotal rows for Gross Profit, Operating Income, and Net Income.
- Apply conditional formatting: red background on elimination entries, blue bold on the Consolidated column.
- If currency conversion is needed, add a converted column for each foreign entity showing amounts in the group currency before consolidation.
- Do NOT invent intercompany transactions I haven't listed.
- Do NOT fabricate entity-level figures.
- Do NOT adjust entity amounts to make eliminations balance — flag discrepancies instead.
- Format the output as a tab-separated table I can paste directly into Excel.
Why Intercompany Eliminations Require Precision
Eliminations are the most error-prone part of a manual consolidation. If Entity A records $150,000 of intercompany revenue and Entity B records only $145,000 of intercompany cost, eliminating $150,000 from both lines creates a $5,000 hole in the consolidated COGS. The prompt in this course handles this by flagging imbalances explicitly — Claude never silently forces a match.
For groups where each entity runs its own bank reconciliation and close process, cleaning up intercompany balances before consolidation is a critical step. Once consolidated, you can feed the group-level numbers into a 3-statement financial model for group-level planning.
Common Intercompany Elimination Types
A
B
C
1
Transaction Type
Seller Books
Buyer Books
2
Intercompany sale of goods
Revenue
COGS or Inventory
3
Management fee
Fee income (Revenue)
Management fee (OpEx)
4
Intercompany loan interest
Interest income
Interest expense
5
Shared service allocation
Allocation income
Allocated cost (OpEx)
Each type must be eliminated from both sides. The P&L effect nets to zero on the Consolidated column — only external transactions remain.
Frequently Asked Questions
What are intercompany eliminations and why do they matter?
Intercompany eliminations remove transactions between entities within the same group — for example, one subsidiary selling services to another. Without eliminations, group revenue and expenses would be overstated because the same transaction appears in both entities' books.
Can Claude handle currency conversion for foreign subsidiaries?
Yes. Include each entity's reporting currency and the exchange rate in your prompt. Claude adds a converted column for each entity before consolidating. You specify whether to use the closing rate for balance sheet items and the average rate for P&L items.
How many entities can this template handle?
The structure works for any number of entities. For 2–5 entities, a single-sheet layout works well. For larger groups, Claude can structure the workbook with one tab per entity plus a consolidation tab that pulls from each.
Does this produce a consolidated balance sheet or just a P&L?
The prompt focuses on the consolidated P&L because that is the most common Excel consolidation task. You can extend it to include a balance sheet by adding balance sheet line items and telling Claude to apply elimination logic to intercompany receivables and payables.
Will Claude invent elimination entries I haven't provided?
No. The prompt explicitly instructs Claude to use only the intercompany transactions you list. If an imbalance exists between entities, Claude flags it rather than fabricating an offsetting entry.