Multi-Entity Consolidation (QBO/Xero)

This use case shows how an accounting firm can use Model Reef alongside Xero and QuickBooks to deliver multi-entity consolidation for client groups.

Instead of maintaining brittle Excel workbooks, you will:

• Build one live actuals model per legal entity.

• Build a separate consolidation model that ingests those entity outputs.

• Standardise mapping so you can repeat the pattern across many client groups.

Model Reef currently connects each model to a single ledger file. Multi-entity consolidation is therefore implemented with multiple models and one consolidation model, not a single model connected to many ledgers.

When to use this pattern

Use this approach when:

• A client has multiple entities, often with separate Xero or QuickBooks files.

• You need consistent, repeatable group level P&L, Balance Sheet and Cashflow.

• You want to move away from fragile spreadsheet consolidations.

• You may need eliminations or group reclassifications on top of legal entity results.

If you only have one ledger and simply need divisional reporting, see the Multi Division Model pattern instead.

Architecture overview

1

Entity models

• One Model Reef model per ledger (per Xero or QuickBooks file).

• Each connected to its ledger using the live actuals integration.

• Responsibility: accurate legal entity actuals and, optionally, local forecasts.

2

Consolidation model

• One Model Reef model with branches for each entity and an optional Eliminations branch.

• Responsibility: group P&L, Balance Sheet, Cashflow and Cash Waterfall.

3

Reporting views

• Dashboards and reports in the consolidation model that deliver group and entity breakdowns to the client.

Model Reef connects each model to a single ledger file. Multi-entity consolidation is achieved by creating multiple entity models plus a consolidation model that ingests their outputs.

1

Step 1: Standardise chart-of-accounts mapping

Before building anything, choose a standard mapping scheme you will apply across all entities in the group.

• Define a firm level reporting chart:

  • Revenue categories.

  • COGS categories.

  • Staff categories.

  • Opex buckets.

  • Asset and liability groupings.

• For each entity:

  • Decide how its local COA maps into this standard structure.

  • Document any exceptions or entity specific lines.

Store these decisions in internal working papers or in GitBook so future staff can follow the same mapping.

2

Step 2: Build one live actuals model per entity

For each ledger (Xero or QuickBooks):

• Create a model named for the entity, for example:

  • ClientGroup - Entity A - Live Actuals

  • ClientGroup - Entity B - Live Actuals

• Connect the model to the Xero or QuickBooks file.

• Import and map the Chart of Accounts using your firm level structure.

• Import historical actuals (for example the last 2 to 3 years).

• Optionally add simple forward assumptions so each entity has its own forecast.

Check that:

• P&L and Balance Sheet for each entity match the ledger within materiality.

• Categories align with your standard chart.

These models are your single source of truth for legal entity numbers.

For details, see Build a Live Actuals Model Using Xero.

3

Step 3: Design the consolidation model structure

Create a dedicated consolidation model, for example:

• ClientGroup - Consolidation Model

In this model:

• Create a branch structure such as:

  • Group (root consolidated branch)

    • Entity A

    • Entity B

    • Entity C (if applicable)

    • Eliminations (optional, for inter-entity adjustments)

• Set currency and periodicity to match the entity models.

This model will not connect directly to ledgers. It will ingest outputs from the entity models.

4

Step 4: Export entity level results

From each entity model:

• Export the key series you want to consolidate, for example:

  • P&L by period (Revenue, COGS, Opex, Staff, EBITDA)

  • Balance Sheet by period (Assets, Liabilities, Equity)

  • Optional additional metrics such as headcount or units

• Use CSV exports with clear naming, for example:

  • entity-a pnl monthly.csv

  • entity-a balance-sheet monthly.csv

Ensure export settings are consistent across entities:

• Same period range.

• Same periodicity (for example monthly).

• Same units (for example whole currency or thousands).

5

Step 5: Import entity results into the consolidation model

In the consolidation model:

• Use CSV import to bring in each entity file.

• For each imported series:

  • Create Data Library entries named, for example:

    • Entity A - Revenue, Entity A - EBITDA, Entity A - Assets

  • Tag entries with the entity name and SRC ENTITY MODEL.

• Map each series to variables under the relevant branch:

  • Variables in Entity A branch reference Entity A series.

  • Variables in Entity B branch reference Entity B series, and so on.

You now have branch level variables that mirror each entity's statements inside the consolidation model.

For help on combining sources, see Combine Multiple External Data Sources.

6

Step 6: Add group adjustments and eliminations

Most groups require adjustments on top of legal entity figures, such as:

• Intercompany revenue and cost eliminations.

• Intercompany loans and balances.

• Group reclassifications and management adjustments.

Implement these by:

• Creating an Eliminations branch with variables representing:

  • Negative revenue and expense lines to eliminate internal trading.

  • Adjustments to assets and liabilities where balances cancel out.

• Or, where preferred, adding separate adjustment variables inside the Group branch.

Because branches roll up to Group, the consolidated view will automatically reflect entity results plus eliminations and adjustments.

Document each adjustment in variable notes so you can explain them to the client or auditors.

7

Step 7: Layer consolidated forecasts

Once actuals are in place, you can add group level forecasts in the consolidation model by:

• Projecting entity level series based on growth or margin assumptions.

• Adding group wide drivers (for example group marketing, corporate overhead, group capex).

• Modelling group level debt and equity transactions in the Group or a dedicated Funding branch.

Alternatively, maintain detailed forecasts inside each entity model and import those projections into the consolidation model via CSV, following the same pattern as actuals.

Choose the approach that matches your firm's workflow and the level of detail clients expect.

8

Step 8: Build consolidated reports and dashboards

In the consolidation model:

• Use system reports for:

  • Group P&L

  • Group Balance Sheet

  • Group Cashflow Statement

  • Group Cash Waterfall

• Build custom reports or dashboards to show:

  • Group totals next to entity breakdowns

  • Group versus Entity A, Entity B etc.

  • Group level EBITDA, cash and debt trends over time

These become the deliverables you provide to client management or their external stakeholders.

Operating the monthly or quarterly workflow

1

Refresh actuals in each entity model from Xero or QuickBooks.

2

Export updated entity series.

3

Import them into the consolidation model, replacing or extending prior periods.

4

Re-run the consolidated statements and review:

• Group performance.

• Group cash and debt.

• Any covenant style ratios if applicable.

Over time you can industrialise this process across many client groups by reusing templates and mapping conventions.

Check your work

• Entity models reconcile to their source ledgers.

• Consolidation model shows group results that match your manual or prior consolidation.

• Eliminations behave as expected, with intercompany flows cancelling out.

• The workflow is repeatable each period without ad hoc fixes.

Troubleshooting

Entity exports use inconsistent structures

Standardise the export template you use in each entity model so the consolidation imports remain stable.

Eliminations are hard to trace

Use clear variable names and notes in the Eliminations branch, and, if necessary, create a simple eliminations report to show the adjustments explicitly.

Updating many entities becomes time consuming

Focus on automating naming conventions and export/import processes, and consider limiting detailed consolidation to the most material entities.

Related guides

• Build Cross Branch Drivers & Dependencies

• Build Scenario Aware Data Overrides

• Actuals Import

• Allowed Operators