Dimension examples
Three worked examples of dimensions in a planning model, with revenue by region, headcount by department, and a P&L by entity.
These examples show one month of each model. The first example uses a dimension that you create. The other two start from templates in Scenario planning → New model.
1. Revenue by region
Create a Region dimension with the items North America, Europe, and APAC. Then build these variables. Apply Region to the first three.
| Variable | Input or formula | Dimension aggregation |
|---|---|---|
| Customers | Input | Sum |
| Revenue per customer | Input | Average |
| Revenue | Customers * Revenue per customer | Sum |
| Blended revenue per customer | Revenue / Customers | Not used |
Set the aggregation of Revenue per customer before you apply the dimension. Then type the item values.
| Region | Customers | Revenue per customer | Revenue |
|---|---|---|---|
| North America | 200 | 50 | 10,000 |
| Europe | 100 | 60 | 6,000 |
| APAC | 50 | 40 | 2,000 |
| Total | 350 | 50 | 18,000 |
Revenue must also use Region. Without it, the formula reads the totals: 350 × 50 = 17,500, not 18,000.
Blended revenue per customer has no Region dimension, so it reads the totals: 18,000 / 350 ≈ 51.43. The total row of Revenue per customer shows 50, the simple average. The blended value gives more weight to the bigger region.
2. Headcount by department
Create a model from the Headcount by Department template. Its Department dimension has Engineering, Sales, Marketing, and G&A. Taxes and overheads and Cost per head do not use the dimension. The other variables do.
| Variable | Input or formula |
|---|---|
| Headcount | Input |
| Average annual salary | Input, Average aggregation |
| Taxes and overheads | Input, 25% |
| Base payroll | Headcount * Average annual salary / 12 |
| Fully-loaded payroll | Base payroll * (1 + Taxes and overheads) |
| Cost per head | Fully-loaded payroll / Headcount |
| Department | Headcount | Average annual salary | Fully-loaded payroll |
|---|---|---|---|
| Engineering | 12 | 150,000 | 187,500 |
| Sales | 8 | 120,000 | 100,000 |
| Marketing | 5 | 110,000 | 57,292 |
| G&A | 4 | 130,000 | 54,167 |
| Total | 29 | 127,500 | 398,958 |
Taxes and overheads has no dimension, so each department reads the same 25%. Cost per head reads the totals: 398,958 / 29 ≈ 13,757.
Headcount uses the Time aggregation Final. A quarter shows the headcount of its last month, not the sum of three months.
3. P&L by entity
Create a model from the Consolidation by Entity template. Its Entity dimension has United States, United Kingdom, Canada, and Eliminations.
| Entity | Revenue | Operating costs | Net profit |
|---|---|---|---|
| United States | 500,000 | 350,000 | 150,000 |
| United Kingdom | 300,000 | 220,000 | 80,000 |
| Canada | 200,000 | 140,000 | 60,000 |
| Eliminations | −50,000 | −50,000 | 0 |
| Total | 950,000 | 660,000 | 290,000 |
Net profit uses Revenue - Operating costs for each entity. Consolidated net margin has no Entity dimension. Its formula Net profit / Revenue reads the totals: 290,000 / 950,000 ≈ 30.5%.
The Eliminations item holds inter-company amounts as negative values. When both sides match, its Net profit is 0.
To rank the entities on the Charts page, add a Listogram card and select Net profit.