Build a Consolidated Forecast Model
This guide shows you how to build a consolidated forecast where multiple branches or entities roll up into a single set of financial statements and valuations.
Before you start
You should already have:
A working model for at least one entity or division, or
Clear assumptions for each business unit you want to include.
It is helpful, but not required, to have historicals imported from PDF, Excel, Xero or QuickBooks.
What you will build
You will create:
A branch tree with one branch per entity or division.
Variables and drivers in each branch.
A consolidated parent branch that automatically rolls up results.
Consolidated P&L, Balance Sheet, Cashflow and Cash Waterfall.
Optionally, a combined valuation for the group.
Decide your consolidation structure
Common consolidation structures:
Multi division Root: Group Child branches: Division A, Division B, Division C
Multi entity group Root: Group Child branches: Entity A, Entity B, Entity C
Hybrid Root: Group Entity A with child branches for divisions Entity B with child branches for divisions
Sketch your ideal structure before building.
Create branches for each entity or division
In the branch panel, create a Group or Consolidated root if not already present.
Under the root, create one branch per entity or division.
If you need extra detail:
Add child branches for stores, regions or projects.
Example:
Group
Entity A
Entity B
Shared Services
Add or import variables into each branch
For each branch:
Populate Revenue variables:
Use imports (PDF, Excel, Xero, QuickBooks, ticker) where available.
Or create manual revenue variables with units and prices.
Add COGS variables:
Link to units or use percentage of revenue.
Add Opex and staff variables:
Model local cost structures by entity or division.
Add Assets and liabilities:
Capex, loans, and any entity specific balances.
Keep naming consistent across entities, for example:
Revenue - SubscriptionsCOGS - SubscriptionsOpex - MarketingStaff - Sales Team
Configure tax and financing per entity
If entities have different tax or financing arrangements:
For each entity branch:
Set tax rate and payment frequency where entity level tax is relevant.
For group level financing:
Create loans and equity variables in the Group or Holding branch.
Optionally allocate interest or costs to entities via additional variables.
Check entity level statements
For each entity branch:
Filter the P&L, Balance Sheet and Cashflow to that branch.
Confirm:
Revenue and costs line up with expectations.
Working capital behaviour (AR and AP) makes sense.
Local capex and debt behaviour is correct.
Fix any mapping or timing errors at this level first.
Validate consolidated statements
Now review the Group level:
Open the consolidated P&L:
Check that group Revenue equals the sum of entity Revenues.
Check that group EBITDA equals the sum of entity EBITDA, plus any group adjustments.
Review the Balance Sheet:
Ensure Assets = Liabilities + Equity at group level.
Confirm that AR, AP, loans and equity roll up correctly.
Review the Cashflow Statement and Cash Waterfall:
Confirm that operating, investing and financing cashflows reflect all active branches.
Check the bridge from EBITDA to net cash via Change in Net Working Capital.
Add consolidation specific adjustments
Some consolidation adjustments are group level only:
Group wide overheads or central teams.
Intercompany eliminations (if you choose to model them).
Group level tax or financing.
To model these:
Create additional variables in the Group branch to represent overheads or funding.
If you wish to model eliminations:
Create offsetting variables that remove intercompany transactions at group level.
Keep these adjustments clearly named and documented.
Check your work
Each entity or division has its own variables and statements.
Group statements equal the sum of enabled branches plus group adjustments.
Balance Sheet balances at entity and group levels.
Cash Waterfall matches the Cashflow Statement.
Valuation is being read at the group level, not an individual entity.
Troubleshooting
Related guides
Last updated