Multi-entity multi-currency consolidation: turning three entities into one consolidated picture

A US parent, a German sub keeping books in EUR, a Mexican sub keeping books in MXN. The CFO wants one consolidated P&L and one consolidated balance sheet, in USD, by Wednesday.

What does “in USD” mean? The Mexican sub didn’t earn USD; it earned pesos. The German sub didn’t pay USD rent; it paid euros. The translation isn’t a single multiplication — it’s a sequence of decisions about which rates apply to which lines, which transactions to eliminate, and where the residual goes.

This is the work people mean when they say “consolidation,” and it’s the work that most finance teams either do badly in spreadsheets or pay $20K-$80K/year for a tool to do (which also does it badly, just behind a UI). The mechanics aren’t hard. The discipline is.

Three currencies show up in any consolidation problem. They’re different.

• Transaction currency — the currency a specific transaction is denominated in. The German sub buys office furniture in EUR. The Mexican sub pays a Chilean vendor in CLP. Each transaction has its own transaction currency.
• Functional currency — the currency the entity primarily operates in. The German sub’s functional currency is EUR even if it occasionally transacts in CLP. ASC 830 and IAS 21 both define this as “the currency of the primary economic environment in which the entity operates.” It’s a determination, not a choice.
• Reporting currency — the currency the consolidated group reports in. That’s the parent’s choice. Often the parent’s functional currency, but not always.

Two distinct mechanics flow from this:

• Remeasurement happens when a transaction is denominated in a currency other than the entity’s functional currency. The gain or loss hits P&L.
• Translation happens when an entity’s functional currency differs from the group’s reporting currency. The gain or loss accumulates in OCI as CTA — not in P&L.

Conflating these two is the single most common mistake. The first goes to P&L; the second doesn’t. They look identical until they don’t.

Every consolidation worksheet does the same four things, in this order:

1. TRANSLATE   each entity's TB from functional currency to reporting currency.
               P&L items at average rate. BS items at period-end rate.

2. ELIMINATE   intercompany transactions and balances. IC sales / IC COGS net
               to zero. IC AR / IC AP net to zero in the parent's reporting
               currency (mostly).

3. CONSOLIDATE the translated, eliminated entity TBs into a group TB.

4. ROLL CTA    the residual from translation accumulates in CTA, parked in
               equity OCI. Track its movement period over period.

The downloadable workbook below shows each of these in a dedicated tab, with the math exposed.

In order of how often I see them:

• FX rate sourcing. Different sources give different rates. The avg rate for EUR/USD in Q4 2025 differs by source — ECB, Bloomberg, OANDA, the entity’s ERP can all disagree. Pick one source per entity, document it, never change mid-year.
• IC mismatch at period-end. The parent books an IC receivable at the avg-rate transaction date. The sub books the matching IC payable at its own avg-rate transaction date. At year-end, both get revalued at the period-end rate. The two revaluations don’t exactly offset because the rates differ — and that residual is a real FX exposure (P&L), not CTA.
• Opening BS roll. The closing BS in functional currency at Dec 31 of last year must equal the opening BS at Jan 1 of this year. Sounds obvious. ERPs lose this all the time when entity GLs are re-keyed mid-year. Always reconcile the opening roll before starting the period close.
• GAAP vs management-view differences. Local statutory books in Mexico and Germany aren’t US GAAP. Reclasses happen at consolidation. Bookkeeping at the entity level is fine for the local audit; it’s not necessarily fine for the consolidated package.

The pitch from every consolidation vendor: “we handle the FX automatically.” What that means in practice:

• The tool pulls rates from a single feed and applies them globally — great until your auditor wants a different rate source for one entity.
• The tool eliminates IC by automatic matching on entity-pair + period — great until one entity codes the IC sale to a different account than the matching purchase, and the tool silently leaves the residual in “FX gain/loss.”
• The tool produces a CTA line that’s a plug — great if you trust the plug. Not great if you don’t.

Most of these tools are wrappers around the same Excel logic, behind a slower UI, with less visibility into why a number is what it is. The right answer for many finance teams isn’t to buy a tool — it’s to build the consolidation worksheet correctly in Excel, document every choice, and only graduate to a platform when scale (multiple periods × dozens of entities × audit revisions) actually demands it.

The downloadable workbook implements the four steps on a synthetic FY2025 close for a 3-entity group: ParentCo (US, USD functional), EuropeCo (Germany, EUR functional), LatAmCo (Mexico, MXN functional). All data is fabricated.

• README and FX_Rates — what the workbook is and which rates apply.
• <entity>_PL, <entity>_BS, <entity>_TB — each entity’s financials in its own functional currency. These are what the local controller signs off on.
• IC_Schedule — every intercompany transaction and balance, with the rate applied at booking. Note that the loan principals get revalued at period-end — the schedule shows both.
• Consolidation — the worksheet that does the four steps. Each classification (revenue, COGS, cash, AR, etc.) has its own row, with columns for each entity’s functional-currency value, the USD translation, the sum, the IC elimination, top-side adjustments, and the consolidated number.
• TopSide_Adj — two illustrative consolidation-level JEs: a non-GAAP inventory reclass and an audit-fee accrual.
• CTA_Rollforward — opening CTA from prior periods + current-period translation movement + closing CTA. The movement is the difference between translating the entity at the period-end rate vs. translating the income that flowed into equity at the average rate.
• TieOut — three reconciliations. Two must pass; one (the IC AR vs IC AP mismatch) is intentionally non-zero, because that’s the FX exposure on the IC books — the teaching point of the workbook.

Working-capital balances — AR, inventory, AP — are usually the largest BS items in an operating business. When they sit on a non-functional-currency entity, their FX translation drives most of the CTA movement. There’s a whole second piece worth writing about that: how OCI quietly absorbs working-capital FX even when your income statement reconciles cleanly, and what to do when leadership notices that consolidated equity is moving and asks why.

That’s a follow-up. The mechanics in this piece are the foundation it builds on.