Common formulas: currency and multiple entities
Formulas that consolidate entities, remove inter-company amounts, and convert local currency to a base currency.
A group with more than one entity needs one consolidated P&L. In Eigenn, you put the entities in an Entity dimension. Each formula runs one time for each entity, and the total row adds the entities. When the entities use different currencies, each entity converts to the base currency before the total.
The formulas on this page come from the Consolidation by Entity, Multi-currency Consolidation, and FX Exposure Stress templates. To start from a template, go to Scenario planning → New model, type the template name, and press Enter.
Consolidate entities in one currency
In Consolidation by Entity, Revenue, Operating costs, and Net profit have the Entity dimension.
| Variable | Formula |
|---|---|
| Net profit | Revenue - Operating costs |
| Consolidated net margin | Net profit / Revenue |
The Eliminations item holds inter-company amounts as negative numbers. The total row then removes them. With the example inputs:
| 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 row | 950,000 | 660,000 | 290,000 |
Consolidated net margin has no dimension. It reads the totals: 290,000 / 950,000 = 30.5%.
Convert each entity to the base currency
In Multi-currency Consolidation, each entity enters its figures in its own currency. FX rate to base holds one rate for each entity.
| Variable | Formula |
|---|---|
| Revenue (base) | Revenue (local) * FX rate to base |
| Operating costs (base) | Operating costs (local) * FX rate to base |
| Net profit (base) | Revenue (base) - Operating costs (base) |
| Consolidated net margin | Net profit (base) / Revenue (base) |
| Entity | Revenue (local) | FX rate to base | Revenue (base) |
|---|---|---|---|
| US (USD) | 500,000 | 1 | 500,000 |
| UK (GBP) | 300,000 | 1.27 | 381,000 |
| Germany (EUR) | 250,000 | 1.08 | 270,000 |
| Eliminations | -40,000 | 1 | -40,000 |
| Total row | 1,111,000 |
Read the totals of the base rows only:
- The total row of Revenue (local) adds amounts in different currencies. It has no meaning.
- FX rate to base uses the Average dimension aggregation. Its total row is not a rate for the group.
Enter eliminations in the base currency and keep their rate at 1. FX rate to base is an input in each period, so you can type a different rate for each month.
Test a change in the rate
FX Exposure Stress converts foreign revenue and finds the break-even rate.
| Variable | Formula |
|---|---|
| Revenue in base | Foreign revenue * FX rate (base per unit) |
| Net profit | Revenue in base - Domestic costs |
| FX break-even rate | Domestic costs / Foreign revenue |
Add a scenario that changes FX rate (base per unit). Then compare the scenario with the base case.