Build a Multi Division Model
This guide shows you how to set up a model where a single business is split into divisions (for example Retail, Online, Wholesale) with separate results and a combined view.
Before you start
You should know:
Which divisions you want to model.
Which revenue and cost lines belong to each division.
Which costs are shared across the whole business.
What you will build
You will create:
One branch per division plus a group branch.
Variables that live in the appropriate division.
Shared variables at group level.
Divisional and consolidated reports.
Steps
Assign revenue to divisions
For each division:
Select the division branch.
Create Revenue variables specific to that division:
Revenue - Retail - In store salesRevenue - Online - Subscriptions
Set drivers, timing and delays for that division only.
This ensures each division’s revenue is isolated correctly.
Assign COGS and direct costs
For each division:
Create COGS variables in the same branch.
Model COGS as:
Cost per unit linked to that division’s units.
Or percentage of that division’s revenue.
For direct opex or staff that belong to a division:
Create Opex and Staff variables in that division branch.
Result: each division has its own gross profit and contribution margin.
Model shared overheads
Shared overheads (for example HR, Finance, Head Office rent) should be modelled at Group branch level:
Select the Group branch.
Create Opex and Staff variables for shared costs.
Optionally document allocation logic in the variable notes (for example allocation by revenue, headcount or some other metric).
You may also choose to explicitly allocate shared costs into divisional branches using additional variables referencing group level drivers.
Review division level results
For each division branch:
Filter the P&L to that branch.
Check:
Revenue and COGS belong only to that division.
Direct costs are correctly placed.
Division level EBITDA behaves as expected.
Use charts or dashboards to compare:
Revenue growth.
Gross margin.
EBITDA margin across divisions.
Review group level results
At the Group level:
Open P&L:
Confirm that group Revenue equals the sum of all divisions plus any group level revenue.
Confirm that group COGS and Opex include both divisional and group level shared costs.
Open Balance Sheet:
Check AR and AP created by each division’s delays.
Ensure Assets, Liabilities and Equity are balanced.
Open Cashflow and Cash Waterfall:
Inspect how each division contributes to overall cash generation and consumption.
Scenario based division analysis
To test division level scenarios:
Duplicate the model into different scenarios (for example Base, Optimistic, Pessimistic).
Adjust assumptions in one or more divisions:
Different growth rates.
Different margin improvements.
Different cost reduction plans.
Compare results at division and group level.
Check your work
All division specific revenue and costs sit in the correct branch.
Shared overheads are modelled centrally or clearly allocated.
Division level P&Ls make sense in isolation.
Group P&L equals the sum of divisional results plus any group specific items.
Cash Waterfall aligns with expectations at division and group level.
Troubleshooting
Related guides
Last updated