Multi-entity P&L consolidation template
By the Analistable team · Updated · 3 min read
The workbook has an Entity data sheet for each company's P&L lines in local currency, an Eliminations sheet for intercompany adjustments and a Consolidated P&L. Enter amounts and FX rates, and formulas convert to the group currency, place each entity in its own column, apply eliminations and total the group operating profit.
Part of our guide: How to consolidate data in Excel
What the template does
Small groups with two to five companies often consolidate by copying each entity's figures into a summary tab. That hides the FX conversion and makes intercompany charges easy to double-count. This template keeps every entity's lines in one long table with the rate applied per row, shows each entity side by side, and posts eliminations in their own column so the audit trail is visible. Background on the pitfalls is in why totals double after consolidating and combining franchise P&L and budget files.
Sheet-by-sheet guide
| Sheet | You fill in | Formulas add |
|---|---|---|
| Instructions | Nothing | — |
| Entity data | Entity, Line, Currency, Local amount, Rate to GBP | GBP amount |
| Eliminations | Line, GBP amount, Note | — |
| Consolidated P&L | Line names in A, entity names in row 1 | Entity columns, eliminations, consolidated total |
How to fill it in
- Enter each entity's P&L lines in Entity data: revenue positive, costs negative, using the same line names for every entity.
- Enter an average rate for the period in Rate to GBP (1 for GBP entities). Change the group currency by relabelling the column.
- Record intercompany charges on Eliminations as equal and opposite amounts: remove the revenue from one line and the matching cost from the other.
- Make sure the entity names in Consolidated P&L row 1 match the Entity column exactly. The check row confirms eliminations net to zero.
How the formulas work
Each row converts to GBP; each entity column sums by entity name in the header and line name in column A:
=ROUND(D2*E2,2)
=SUMIFS('Entity data'!$F$2:$F$100,'Entity data'!$A$2:$A$100,B$1,'Entity data'!$B$2:$B$100,$A2)Eliminations are picked up by line, and the consolidated column adds across:
=SUMIFS(Eliminations!$B$2:$B$50,Eliminations!$A$2:$A$50,$A2)
=SUM(B2:E2)Worked example
UK Ltd reports in GBP, Ireland Ltd in EUR at 0.85 and US Inc in USD at 0.75. UK charges Ireland a 40,000 management fee, which is eliminated.
| Line | UK Ltd | Ireland Ltd | US Inc | Elims | Consolidated |
|---|---|---|---|---|---|
| Revenue | 500000 | 170000 | 120000 | -40000 | 750000 |
| Cost of sales | -300000 | -93500 | -67500 | 0 | -461000 |
| Operating expenses | -120000 | -42500 | -30000 | 40000 | -152500 |
| Operating profit | 80000 | 34000 | 22500 | 0 | 136500 |
Limits and when to move on
- One average rate per entity is a simplification; statutory consolidation may need different rates for different lines, plus balance sheet translation.
- It covers the P&L only — no minority interests, balance sheet or cash flow. Use it for management reporting, and agree statutory methods with your accountant.
- For a simpler multi-unit report with no FX or eliminations, use the monthly reporting consolidation template. When entity trial balances arrive as separate exports, Analistable can load them together and answer questions across them.
Frequently asked questions
- Why are costs entered as negative numbers?
- With one sign convention the operating profit is a simple SUM of the lines, and eliminations can be entered with their natural sign without a separate debit or credit column.
- How do I add a fourth entity?
- Add its rows to Entity data, insert a column before Eliminations on the Consolidated P&L, type the entity name in row 1 and copy the SUMIFS across. Then widen the Consolidated SUM to include the new column.
- Does it work in Google Sheets?
- Yes. It uses SUMIFS, SUM, ROUND and IF, and quoted sheet names that Google Sheets accepts.