QuanticPlanning | Building a Group Consolidation and Intercompany Elimination Model in Anaplan: Counterparty Matching, Unrealised Profit, NCI, and a Bridge That Ties

Most Anaplan estates end up with a consolidation problem whether or not anyone planned for one. The group builds a three-statement model, the regions start planning in local currency, someone adds a second legal entity to a shared services cost centre, and within two cycles finance is asking the same question every quarter: does the plan consolidate the way the actuals consolidate? If the answer is no, the forecast and the statutory numbers tell two different stories and nobody trusts either.

This tutorial covers building a planning-grade consolidation and intercompany elimination layer in Anaplan. It is deliberately scoped as management and plan consolidation, not a replacement for a statutory close tool. Anaplan is excellent at consolidating a forecast across entities, ownership structures and currencies with full traceability; it is not a general ledger and it will not produce your audited statutory filings on its own. Knowing where that line sits is most of the design.

It assumes you are comfortable with lists, subsets, SUM/LOOKUP, and native time. If you have not built FX translation yet, read the multi-currency tutorial first — this model sits directly on top of it.

What the layer has to do

Five jobs, in order. Every structure below exists to serve one of them.

Collect — pull each entity's local-currency trial balance or plan at a consistent account grain.
Translate — convert to group currency with the correct rate type per account, generating CTA.
Eliminate — remove intercompany trading, balances, dividends and unrealised profit.
Apportion — apply ownership percentage, equity accounting and non-controlling interest.
Explain — show the bridge from the sum of the parts to the consolidated result, line by line.

Teams usually get 1 and 2 right and then hide 3 and 4 inside a single "elimination" line item that nobody can reconcile. The explanation layer is what makes the model defensible in a board meeting.

Step 1: The dimensional skeleton

The consolidation layer belongs in its own spoke model, fed from the data hub and from the entity planning models. Keep it thin: it consumes balances, it does not plan them.

List	Type	Notes
`Legal Entities`	Flat, production	Every reporting entity. Include dormant and disposed entities for the periods they were in scope.
`Consolidation Structure`	Ragged hierarchy	Entity → Sub-group → Region → Group. Keep legal structure separate from the management hierarchy.
`Accounts`	Hierarchy	Group chart of accounts, with a flag-driven split of P&L / Balance Sheet / Cash Flow.
`Counterparty`	Flat	A mirror of `Legal Entities` plus a `No Counterparty` member. This is the single most important list in the model.
`Consolidation Steps`	Flat, ordered	`Local`, `Translation`, `IC Elimination`, `Unrealised Profit`, `Ownership/NCI`, `Group Adjustments`, `Consolidated`.
`Currencies`	Flat	Local currencies plus group currency.
`Versions`	Native	Actual, Budget, Forecast, plus scenario versions.
`Time`	Native	Monthly with quarterly and annual rollups.

Two rules that prevent a rebuild later:

Counterparty is a real dimension, not a text property. The moment intercompany tagging lives in a code or a concatenated text field, automated matching dies. Every transactional feed into this model carries entity and counterparty.
Consolidation Steps is a flat list with an explicit Consolidated total, not a formula chain. Modelling each step as a member means every number in the group result has a visible provenance, and new adjustment types can be added without touching formulas.

Step 2: Collecting the local data

Build one input module at the lowest shared grain:

MOD10 Entity Submission
  Applies to: Legal Entities, Accounts, Counterparty, Time, Versions
  Line items:
    Local Amount          (Number, input / imported)
    Source                (List: Data Hub / Entity Model / Manual)
    Submission Locked?    (Boolean)

Three design notes:

Sparsity is the whole game. Legal Entities × Accounts × Counterparty × Time × Versions is a large cell count on paper. Most of it is genuinely empty — only intercompany accounts carry a counterparty. Split the module in two: a non-IC module dimensioned without Counterparty, and an IC module that carries it. Combine them in a calculation module. This routinely cuts the model by an order of magnitude.
Lock submissions. A boolean per entity and period, driven through Dynamic Cell Access, is the difference between a consolidation and a moving target. Pair it with a Workflow task so the entity controller formally submits.
Validate before you consolidate. A simple check module — balance sheet balances, P&L ties to retained earnings movement, no postings to disallowed accounts — run per entity, surfaced as a red/green board. Entities do not get to submit until it is green.

Step 3: Translation and rate types

Translation is a per-account decision, not a per-entity one.

Rate type	Applied to	Source
Closing	Balance sheet monetary items	Month-end spot
Average	P&L and cash flow items	Period average
Historic	Share capital, pre-acquisition reserves	Fixed at acquisition date

Drive the choice from an account property, never from a nested IF:

Rate Applied =
  IF  Accounts.Rate Type = "Closing"   THEN SYS Rates.Closing[LOOKUP: SYS Entity.Local Currency]
  ELSE IF Accounts.Rate Type = "Average" THEN SYS Rates.Average[LOOKUP: SYS Entity.Local Currency]
  ELSE SYS Rates.Historic[LOOKUP: SYS Entity.Local Currency]

Group Amount = Local Amount / Rate Applied

(Divide or multiply depending on your quotation convention; pick one and document it on the module blueprint, because this is the most common silent error in FX models.)

The balance sheet will not balance after translation. That difference is the cumulative translation adjustment, and it is a result, not a plug:

CTA = Translated Assets - Translated Liabilities - Translated Equity (at historic) - Translated Retained Earnings (at average)

Post CTA to its own account inside the Translation consolidation step. If someone later asks "why did equity move when nothing happened?", the answer is already on the screen.

Step 4: Intercompany elimination and matching

This is where the model earns its keep. Model elimination as a pairwise process, not a netting exercise.

MOD30 IC Matching
  Applies to: Legal Entities, Counterparty, IC Accounts, Time, Versions
  Line items:
    Reported by Entity        = Group Amount from MOD20
    Reported by Counterparty  = Reported by Entity[LOOKUP: SYS Mirror Entity, LOOKUP: SYS Mirror Counterparty]
    Mismatch                  = Reported by Entity + Reported by Counterparty
    Mismatch Abs              = ABS(Mismatch)
    Above Threshold?          = Mismatch Abs > Tolerance
    Elimination               = IF Above Threshold? THEN -Reported by Entity + Suspense Allocation ELSE -Reported by Entity

The mirror lookup is the trick: two system modules map entity→counterparty-member and counterparty→entity-member, so each pair is readable from both sides in a single module. Receivable in A against payable in B should sum to zero; anything that does not is a mismatch.

Decisions to make explicitly:

Tolerance. A currency-denominated threshold per account type. Below it, eliminate and sweep the residual to an IC difference account. Above it, the pair goes on an exception report with a named owner and a due date.
Who wins. When a pair does not agree, the model needs a convention — seller-side, buyer-side, or group-decides. Store it as a property on the IC account so the rule is visible rather than tribal.
Direction. Intercompany revenue and cost both go; intercompany receivables and payables both go; intercompany dividends eliminate against the receiving entity's income, not against equity.

Then build the exception board: top mismatches by absolute value, ageing, owner, and comment. In practice the exception board is the product. Finance teams spend their close week there, not in the consolidated P&L.

Step 5: Unrealised profit in inventory

If one entity sells goods to another and the buyer still holds them at period end, group profit includes margin that has not been earned outside the group. Eliminate it:

Unrealised Profit = Closing IC Inventory at Transfer Price
                  × IC Margin %[LOOKUP: Selling Entity]
                  × (1 - Realisation %)

Hold IC Margin % by selling entity and product group — a group-wide average is a shortcut that an auditor will find. Post the entry to the Unrealised Profit step, hitting both inventory and cost of sales, and reverse the prior period's entry so only the movement affects the current period's margin.

Step 6: Ownership, NCI and equity accounting

Store ownership as time-phased data, because it changes:

SYS Ownership (Legal Entities × Time)
  Direct Ownership %
  Effective Ownership %   (product down the legal hierarchy)
  Method                  (List: Full / Equity / Proportional / Out of Scope)
  In Scope From / To      (Date)

Then branch the consolidation by method:

Full consolidation. Include 100% of the entity's results, then split equity and profit between owners and non-controlling interests at 100% - Effective Ownership %. NCI is a presentation split, not an elimination — model it as its own step so the group total is unchanged and the attribution is visible.
Equity method. Exclude line-by-line results entirely; bring in the share of profit as a single P&L line and carry the investment on the balance sheet. A boolean-driven IF at the collection stage is cleaner than filtering downstream.
Mid-period acquisitions and disposals. Use In Scope From / To to build a monthly inclusion boolean. Consolidate P&L from the acquisition month forward and the balance sheet from the acquisition date; never retrospectively. Pre-acquisition reserves stay at historic rate.

This is also where goodwill and fair-value adjustments live, as group-level entries in the Group Adjustments step rather than inside any entity's submission.

Step 7: The bridge — your explanation layer

Build one board that answers the only question executives ask:

Sum of entity results (local, translated)       1,482.0
  Translation / CTA                                (9.4)
  IC revenue and cost elimination                (213.6)
  IC balance elimination                             0.0
  Unrealised profit in inventory                   (4.1)
  Group adjustments (goodwill, FV, provisions)     (7.2)
  = Consolidated group result                    1,247.7
  Attributable to NCI                              (38.9)
  Attributable to owners of the parent           1,208.8

Because every row is a member of Consolidation Steps, this view is a single module with a line chart, a waterfall and drill-to-entity. No special reporting logic and no reconciliation spreadsheet.

Pair it with three standing checks, published to the same board:

Group balance sheet balances to zero, every entity, every period.
Total IC mismatches above tolerance = 0 before sign-off.
Consolidated plan vs. consolidated actuals use an identical elimination structure — same accounts, same counterparties, same steps.

Check 3 is the one that gets skipped and the one that causes the credibility problem. If the plan eliminates differently from the actuals, every variance report has a permanent built-in error.

Performance notes

Keep Counterparty only on the modules that genuinely need it. The split-module pattern in Step 2 is the single biggest performance lever in a consolidation build.
Use a time range on the IC matching modules. Matching detail rarely needs the full planning horizon — current year plus one prior year is usually enough, and the saving is large.
Avoid SUM across two large lists in one line item. Stage the entity-to-consolidation-structure mapping in a system module and reference the mapping, not a nested lookup chain.
Consolidate on a schedule, not on every keystroke. Driving the heavy steps through an action-triggered calculation rather than live formulas keeps entity planners' models responsive during input windows.
Watch the ragged legal hierarchy depth. Effective ownership computed as a running product down six levels is fine; computed with a recursive workaround across a flat list is not.

Where to stop

Anaplan should own: plan and forecast consolidation, scenario consolidation (what does the group look like if we divest this entity?), management reporting by both legal and management hierarchies, and the elimination logic applied consistently to plan and actual.

Leave in the close tool: statutory disclosures and note packs, journal-level audit trail with maker-checker at transaction grain, and local GAAP-to-IFRS adjustment libraries. Trying to recreate those turns a fast planning model into a slow accounting system with none of the controls.

The right target is a group forecast that consolidates on the same rules as the close, in minutes rather than days, with a bridge anyone in the room can follow.

Need this built? QuanticPlanning's Anaplan consultants design and build consolidation, multi-entity and multi-currency planning models — and fix the ones where elimination logic has quietly stopped reconciling. Get in touch to scope a build, or see our Anaplan for the Office of the CFO and Anaplan Model Designing and Building services.