Consolidation Guide
Konsolidat consolidates financial data from multiple legal entities into a single group report. The process includes currency translation, intercompany elimination, CTA calculation, top-side adjustments, equity-method accounting for associates, and acquisition/disposal adjustments.
Overview
graph TD
TB[gold_trial_balance<br/>Entity-level amounts] --> CTB[gold_consolidated_trial_balance<br/>FX translation + ownership split]
CTB --> IC[gold_ic_eliminations<br/>Intercompany entries]
CTB --> FX[gold_fx_revaluation<br/>CTA entries]
ADJ[consolidation_adjustments seed<br/>Top-side journals] --> CADJ[gold_consolidation_adjustments]
CTB --> FCTB[gold_fully_consolidated_tb<br/>6-layer union]
IC --> FCTB
FX --> FCTB
CADJ --> FCTB
EM[gold_equity_method_associates<br/>Equity method entries] --> FCTB
ACQ[gold_acquisition_adjustments] --> FCTB
DSP[gold_disposal_adjustments] --> FCTB
FCTB --> CYTD[gold_consolidated_ytd<br/>YTD running totals]
Consolidation Groups
Defined in the consolidation_groups seed CSV:
consolidation_group,data_area_id,entity_name,ownership_pct,reporting_currency,consolidation_method
GROUP_CORP,USMF,Contoso US,100,USD,full
GROUP_CORP,DEMF,Contoso DE,100,USD,full
GROUP_CORP,GBMF,Contoso UK,80,USD,full
GROUP_CORP,JPMF,Contoso JP,51,USD,full
| Field | Description |
|---|---|
consolidation_group | Group identifier (e.g., GROUP_CORP) |
data_area_id | Legal entity code from D365 |
ownership_pct | Parent's ownership (0–100) |
reporting_currency | Group reporting currency |
consolidation_method | Currently: full |
Currency Translation
Rate Selection Rules
| Account Type | Rate Used | Rationale |
|---|---|---|
| Balance Sheet (Asset, Liability) | Closing rate | BS at period-end value |
| P&L (Revenue, Expense) | Average rate | P&L at period average |
| Equity | Closing rate | Simplified; historical rate for full IAS 21 |
The rate is looked up from silver_exchange_rates using the convert_currency() macro with a fallback chain:
- Exact match on
from_currency,to_currency,valid_from ≤ date ≤ valid_to - Latest available rate before the period
- Default to
1.0(same-currency assumption)
Translation Formulas
translated_amount = local_amount × translation_rate
group_amount = translated_amount × ownership_pct
nci_amount = translated_amount × (1 − ownership_pct)
Example: GBMF (UK entity, 80% owned) reports GBP 100,000 revenue, closing rate 1.27 USD/GBP:
translated_amount = 100,000 × 1.27 = 127,000 USD
group_amount = 127,000 × 0.80 = 101,600 USD
nci_amount = 127,000 × 0.20 = 25,400 USD
Tests
| Test | Assertion |
|---|---|
assert_translated_amount_formula | |translated − (local × rate)| ≤ 0.01 |
assert_group_amount_formula | |group − (translated × ownership)| ≤ 0.01 |
assert_nci_plus_group_equals_translated | |translated − (group + nci)| ≤ 0.01 |
assert_nci_zero_for_full_ownership | NCI = 0 when ownership = 100% |
assert_bs_uses_closing_rate | BS accounts use closing rate |
assert_pnl_uses_average_rate | P&L accounts use average rate |
Intercompany Elimination
IC Elimination Rules
Defined in the ic_elimination_rules seed:
rule_id,rule_name,debit_account,credit_account,debit_entity_pattern,credit_entity_pattern,description
IC_001,IC Receivable/Payable,1300,2100,*,*,Eliminate IC receivables against payables
IC_002,IC Revenue/COGS,4000,5000,*,*,Eliminate IC revenue against COGS
IC_003,IC Dividend,8100,3200,*,*,Eliminate IC dividends
The elimination engine:
- For each rule, finds matching debit/credit account balances across entities in the same group
- Calculates the elimination amount as the lesser of the two IC balances
- Posts offsetting entries to zero out the intercompany position
Test
assert_ic_elimination_nets_zero — for each group/year/period, sum(debit_elimination + credit_elimination) must be ≤ 0.01.
Currency Translation Adjustment (CTA)
CTA arises because P&L is translated at the average rate but the balance sheet at the closing rate. The difference is posted as an equity adjustment.
cta_amount = sum(local_amount × (closing_rate − average_rate) × ownership_pct)
Tests
| Test | Assertion |
|---|---|
assert_cta_not_zero_when_rates_differ | CTA is non-zero when closing ≠ average rate |
assert_cta_zero_for_same_currency | CTA = 0 when entity currency = reporting currency |
Top-Side Adjustments
Manual journal entries posted at the group level for adjustments that don't originate from entity GL (e.g., goodwill, purchase price allocation, fair value adjustments).
Defined in the consolidation_adjustments seed:
| Field | Type | Description |
|---|---|---|
consolidation_group | String | Group |
adjustment_type | String | Type of adjustment |
journal_id | String | Unique journal ID |
data_area_id | String | Entity (or group-level) |
fiscal_year | UInt16 | Year |
fiscal_period | UInt8 | Period |
main_account | String | Account |
debit_amount | Decimal(18,2) | Debit |
credit_amount | Decimal(18,2) | Credit |
description | String | Narrative |
Test: assert_topside_journal_balanced — each journal must balance (total debits = total credits).
Fully Consolidated Trial Balance (6-Layer Union)
graph LR
E[Entity Layer<br/>group_amount from CTB] --> FCTB[gold_fully_consolidated_tb]
IC[IC Elimination Layer] --> FCTB
CTA[CTA Layer] --> FCTB
TS[Topside Layer] --> FCTB
EM[Equity Method Layer] --> FCTB
AD[Acquisition/Disposal Layer] --> FCTB
The gold_fully_consolidated_tb model unions six layers (see gold_fully_consolidated_tb.sql):
| Layer | adjustment_type | Source ref() | Amount column |
|---|---|---|---|
| 1. Entity | entity | gold_consolidated_trial_balance | group_amount |
| 2. IC elimination | ic_elimination | gold_ic_eliminations | debit_elimination + credit_elimination |
| 3. CTA | cta | gold_fx_revaluation | cta_amount |
| 4. Topside | (per adjustment) | gold_consolidation_adjustments | net_amount |
| 5. Equity method | equity_method | gold_equity_method_associates | amount |
| 6. Acquisition / disposal | (per adjustment) | gold_acquisition_adjustments, gold_disposal_adjustments | adjustment_amount, gain_loss_amount |
Layer 2 emits two rows per rule (the debit account with debit_elimination, the credit account with credit_elimination). Layer 6 unions acquisition adjustments (journal_id prefixed ACQ_) and disposal gain/loss entries (journal_id prefixed DSP_, posted to the DISPOSAL account).
Test: assert_fctb_entity_layer_ties — entity layer sums tie to gold_consolidated_trial_balance.group_amount.
The equity-method (layer 5) and acquisition/disposal (layer 6) logic is documented in:
Reporting with EPM()
Consolidated data is available via the gold models. For entity-level reporting:
=EPM("USMF", 2024, "FY", "401100")
For consolidated reports, query the fully consolidated models directly via SQL or build summary reports that reference the consolidation gold tables.
Next Steps
- Allocation Guide — Cost allocation after consolidation
- Budgeting Guide — Budget input and spreading
- Data Dictionary: Gold Models — Full column reference