Provide ChatGPT with mapped entity P&Ls, reporting-currency assumptions, and evidenced intercompany transactions, then use it to draft a side-by-side consolidation with explicit eliminations and control checks. Treat the output as a consolidation working paper to verify, not an automatic group sign-off.
I need to prepare a multi-entity P&L consolidation working paper in Excel.
CONSOLIDATION SCOPE
- Reporting period: [e.g. month / quarter / year ended ...]
- Group reporting currency: [e.g. GBP]
- Entities included: [list]
- Ownership / consolidation basis: [state which entities are subsidiaries included in full;
if this is only a management consolidation, say so]
- Accounting / management reporting basis: [e.g. IFRS / local GAAP / management reporting]
ENTITY P&L DATA
Provide one mapped row per reporting line:
Group Line ID | Group Line Item | Entity A | Entity B | [Entity C...]
[Paste figures already translated into the GROUP reporting currency,
or use the Multi-currency tab first.]
SOURCE CONTROL TOTALS
For each entity provide:
Revenue | Operating Income | Net Income
[or other agreed control totals so the mapped P&L can be tied back to source]
INTERCOMPANY ELIMINATION INPUT
Provide one row per evidenced intercompany item:
Interco ID | Seller Entity | Buyer Entity | Seller P&L Line |
Buyer P&L Line | Seller Amount in Group Currency |
Buyer Amount in Group Currency | Source Ref / Invoice / Journal Ref
[Paste rows]
Rules:
1. Do not infer line mappings from similar names. Use Group Line ID / mapping supplied.
2. Do not invent intercompany transactions, amounts or counterparties.
3. For each Interco ID, compare the seller and buyer amounts in GROUP currency.
4. If the two recorded amounts differ, flag the item as UNRESOLVED INTERCO DIFFERENCE.
Do not force either side to equal the other and do not plug the difference.
5. For a matched intragroup revenue/expense item, eliminate the evidenced seller income
and buyer expense from the appropriate consolidated lines.
6. Do not assume every intercompany item has zero effect on consolidated Net Income.
Flag items involving inventory profit, asset transfers, dividends, financing,
tax effects or other special treatment for accounting-policy review.
7. Consolidated line formula:
= SUM(Entity columns in group currency) + Elimination
8. Recalculate subtotals from consolidated detail rather than hard-coding them.
9. Add controls:
- each entity's mapped P&L ties to its source control totals
- total elimination entries reconcile to the intercompany schedule
- unresolved intercompany differences are listed separately
- consolidated Gross Profit / Operating Income / Net Income recalculate from detail
10. Do not recommend or post entity adjusting journals. If books differ, show what evidence
is missing and which entity records need investigation.
OUTPUT
1. Entity mapping / source-control check
2. Consolidation table
3. Intercompany elimination schedule
4. Unresolved differences list
5. Consolidated subtotal formulas
6. Audit/control checklist
Use tab-separated tables suitable for Excel.
Use identical line-item names across all entities before pasting. Works in ChatGPT.
I need to translate foreign-entity P&Ls into the group presentation currency
before consolidation.
REPORTING SETTINGS
- Group presentation currency: [e.g. GBP]
- Reporting period: [dates]
- Accounting / management reporting basis: [state]
- Translation policy I am authorised to use: [state explicitly]
ENTITY DATA
For each entity provide:
Entity | Functional / reporting currency | P&L line items | local-currency amounts
[Paste data]
FX INPUTS
For each entity / line type provide the APPROVED translation rate or method:
[Example only:
Entity B | EUR | transaction-date rates, or approved period-average approximation | ...]
Do not look up, estimate, average or invent exchange rates.
Requirements:
1. State the quote direction before calculating, e.g. 1 EUR = 0.86 GBP means:
GBP amount = EUR amount × 0.86.
2. Translate income/expense items using the rate method I supply.
Do not automatically assume a single average rate is appropriate for every P&L line.
3. If my policy uses an average rate as an approximation, label it as such and use only
the supplied approved rate.
4. Keep local-currency amount, FX rate/method and group-currency amount in separate columns.
5. Recalculate translated subtotals from translated detail.
6. Intercompany items:
- translate both entities' recorded amounts into GROUP currency using their applicable
supplied rate/method
- compare the translated amounts
- flag any difference; do not force them to match
7. Do not assume an FX difference on an intragroup monetary item disappears merely because
the underlying intragroup balance/income/expense is eliminated. Flag monetary-item FX
differences for accounting-policy review.
8. Do not extend this P&L workflow to balance-sheet translation without separate instructions
for closing rates, equity/historical rates and translation differences.
9. Show source-currency → group-currency control totals for every entity.
OUTPUT
- Translation table per foreign entity
- FX assumptions/rates used
- translated control totals
- intercompany comparison in group currency
- consolidation-ready P&L data
- exceptions requiring review
Keep this tab focused on the P&L. Balance-sheet translation needs its own policy for closing rates, equity/historical rates and translation differences; do not extend the P&L logic mechanically.
I have an intercompany difference that I need to investigate. INTERCO ID / description: [ ] Entity A evidence: - Entity: - P&L line: - Amount in local currency: - Amount in group currency: - FX rate / method: - Period: - Invoice / journal / source reference: Entity B evidence: [same fields] Please: 1. Recalculate the difference in GROUP reporting currency. 2. Do not assume the cause from the amount alone. 3. Compare period, source reference, currency/rate, account mapping and recorded amount. 4. List only explanations supported by the evidence, and separately list what evidence is still needed to distinguish timing, FX, mapping, dispute or missing posting. 5. Do NOT tell either entity to post an adjusting journal unless the source evidence and accounting policy I provide demonstrate the correct period, account and debit/credit. 6. Until resolved, keep the difference in an "Unresolved Intercompany Differences" schedule. 7. Show the elimination only for the amounts actually supported by both sides; do not create a balancing plug. 8. If the item is an intragroup monetary balance in different functional currencies, flag possible FX accounting consequences for specialist review rather than assuming all currency effects eliminate.
Use this to investigate the evidence behind an intercompany difference. ChatGPT should not choose a cause or adjusting journal from the variance amount alone.
| Line Item | Entity A | Entity B | Total |
|---|---|---|---|
| Revenue← £150k interco | £1,200,000 | £800,000 | £2,000,000 |
| COGS← £150k interco | £480,000 | £490,000 | £970,000 |
| Gross Profit | £720,000 | £310,000 | £1,030,000 |
| OpEx | £320,000 | £140,000 | £460,000 |
| Op. Income | £400,000 | £170,000 | £570,000 |
| Net Income | £280,000 | £120,000 | £400,000 |
| Line Item | Entity A | Entity B | Elim. | Consol. |
|---|---|---|---|---|
| Revenue | £1,200,000 | £800,000 | −£150,000 | £1,850,000 |
| COGS | £480,000 | £490,000 | −£150,000 | £820,000 |
| Gross Profit | £720,000 | £310,000 | £0 | £1,030,000 |
| OpEx | £320,000 | £140,000 | £0 | £460,000 |
| Op. Income | £400,000 | £170,000 | £0 | £570,000 |
| Interest/Tax/Other | £120,000 | £50,000 | £0 | £170,000 |
| Net Income | £280,000 | £120,000 | £0 | £400,000 |
Elim. Revenue: −£150,000 (A's interco sale) Elim. COGS: −£150,000 (B's interco cost) Matched service elimination: Revenue −£150,000 COGS −£150,000 Net Income effect of this matched service elimination = £0 Op. Income £570,000 − Interest/Tax/Other £170,000 = Net Income £400,000 ✓ Do not generalise this zero-profit effect to every type of intercompany transaction.
| Line Item | Entity A GBP | Entity B EUR | GBP+EUR? Total |
|---|---|---|---|
| Revenue← €100k interco | £1,200,000 | €950,000 | 2,150,000 ✗ |
| COGS← €100k interco | £480,000 | €380,000 | 860,000 ✗ |
| Gross Profit | £720,000 | €570,000 | 1,290,000 ✗ |
| OpEx | £320,000 | €210,000 | 530,000 ✗ |
| Net Income | £280,000 | €192,000 | 472,000 ✗ |
| Line Item | Entity A (GBP) | Entity B (GBP) | Elim. | Consolidated |
|---|---|---|---|---|
| Revenue | £1,200,000 | £817,000 | −£86,000 | £1,931,000 |
| COGS | £480,000 | £326,800 | −£86,000 | £720,800 |
| Gross Profit | £720,000 | £490,200 | £0 | £1,210,200 |
| OpEx | £320,000 | £180,600 | £0 | £500,600 |
| Op. Income | £400,000 | £309,600 | £0 | £709,600 |
| Interest/Tax/Other | £120,000 | £144,480 | £0 | £264,480 |
| Net Income | £280,000 | £165,120 | £0 | £445,120 |
Example assumes an APPROVED 0.86 GBP/EUR rate for all shown Entity B P&L lines: Entity B (GBP) = EUR × 0.86 This is an illustration, not a rule that one average rate is always valid. Translate each intercompany side using its applicable supplied method, then compare in GBP. Op. Income £709,600 − Interest/Tax/Other £264,480 = Net Income £445,120 ✓
| Transaction | Entity A records | Entity B records | Difference |
|---|---|---|---|
| IT Services | £150,000 Revenue | £145,000 OpEx | £5,000 ✗ |
| Mgmt Fee | £30,000 Revenue | £30,000 OpEx | £0 ✓ |
| Check | Entity A | Entity B | Result |
|---|---|---|---|
| Recorded amount | £150,000 | £145,000 | £5,000 unresolved |
| Period / source ref | Input needed | Input needed | Cannot diagnose yet |
| FX / mapping evidence | Input needed | Input needed | Review required |
Known: Entity A = £150,000 Entity B = £145,000 Difference = £5,000 Not proven: • timing • FX • mapping • dispute • missing posting Keep £5,000 in Unresolved Intercompany Differences. Do not post or plug an adjustment until source evidence proves the treatment. ✓
Map each local account/line to a controlled Group Line ID. Do not rename source accounting data merely to make the text match; preserve the source and perform the mapping in a separate table so each entity can still be tied back to its original P&L.
For each transaction state: the selling entity, the buying entity, the amount, and which P&L lines it affects on each side. Don't forget management fees, intercompany loan interest, and shared service allocations — these are intercompany transactions too and must be eliminated or they inflate consolidated OpEx.
If Entity A records £150,000 and Entity B £145,000 in group currency, the £5,000 difference needs evidence-based investigation. The debug prompt should keep it unresolved until period, source reference, FX, mapping and accounting treatment establish the reason.
Use the average period rate for all P&L line items. The closing rate is for balance sheet items. State the rates explicitly in the prompt — ChatGPT applies them to add a converted column per foreign entity before consolidating.