Financial Consolidation Procedure
Return to Financial Consolidation Model Overview.
The consolidation process has five phases:
- Configuration
- Data Collection
- Preparation
- Consolidation
- Presentation
There are multiple activities in each phase: some manual, others automated.
A significant part of the consolidation process is the consolidation procedure — responsible for creating the consolidated financial statement. The procedure includes all automated tasks from the preparation, consolidation and presentation phases. Manual tasks must be completed before the consolidated financial statement can be created. In order to create the consolidated financial statement, the procedure takes key input data, resulting in added values to the elements in the Consolidation Dimension. The additional input data together with these results form the consolidated financial statement.
Many of the values added are automated journal entries in the posting journal (more information about this coming shortly). For more information on the whole consolidation process, including its steps and dimension, please read the Consolidation Dimension article.
The procedure operates on a so-called slice. A slice is a combination of Version
, Month_YTD
, Scope
. The procedure can be executed for the regular Consolidation Ledger (which contains the Profit and Loss
, Balance Sheet
and Cash Flow
cubes) or the Segment Consolidation Ledger (containing the Profit and Loss (Segment)
, Balance Sheet (Segment)
and Cash Flow (Segment)
cubes). Multiple slices can be processed at once.
This article describes the individual steps of this procedure.
1. Update FXDiff Account hierarchy in BS Account dimension
Conversion into the group currency is integral to the preparation phase: it must occur true consolidation begins. Whilst most of the currency conversion works directly on the existing account hierarchy, there is one exception: the business logic that computes foreign exchange differences on balance sheet accounts using the Historic
currency conversion type (usually equity accounts) implements artificial consolidated accounts in parallel hierarchies in order to function. These artificial accounts, which have names like All BS Accounts with FXDiff Account ...
, are created or updated in this first step. For each group of balance sheet accounts which use the Historic
currency conversion type sharing the same account in the FXDiff
attribute one such technical account is created as parent. These new accounts are children of the All BS Accounts
element.
2. Reverse all automated journal entries
The Financial Consolidation Procedure can be safely executed multiple times, e.g. to reflect changes of the input data or the configuration. Any automated journal entries from a previous execution are reversed before the new automated journal entries are created.
3: Reuse Journal Entries
Reuse Journal Entries is a feature in the Posting journals. It simplifies the creation and maintenance of manual journal entries which apply to more than one Version
, Scope
, or Ledger
. Reuse Journal Entry procedure is part of the Consolidation Job.
Execution
For defining the reusable Journal Entries, follow the subsequent steps.
1: Create a manual journal entry on one Version
, Scope
, or Ledger
combination (the original journal entry).
2: Once you have created the journal entry, open it and then click on the Custom Details on the Journal Entry report. Maintain the following settings:
For an original journal entry, in the consolidation ledger which should also apply to the segment consolidation ledger, check the Segment Consolidation option. You can find this option under Add Detail>Additional Ledger. And in the segment consolidation ledger which should also apply to the consolidation ledger, check the Consolidation option.
For an original journal entry which should apply to additional scopes, for all ancestor scopes (as defined by the parent-child relations in the Legal Entity dimension), click on the Select Ancestors button to preselect these. You can find this button under the Additional Scopes section. And for individual selection, check or uncheck the scopes using the checkboxes in the Additional Scopes
For an original journal entry which should apply to multiple versions, select or unselect the versions in the Additional Versions section.
3: Click on Save and close the Journal Entry Custom Details reports window.
4: Execute the consolidation for the relevant time period to apply the Reuse Journal Entries.
Technical perspective
The Reuse Journal Entries duplicates all manual and posted journal entries having any custom details which match the Version
, Scope
, or Ledger
of the current reporting period. As a result, you will have duplicates of the journal entries in the current reporting period. The origin field of these duplicated journal entries is set to the source journal entry.
Select Ancestors
The Select Ancestors function in the Journal Entry Custom Details reports automatically selects the Ancestor of the scope. For example, when the current journal entry is defined for the AMER
scope, the Additional Scopes section offers checkboxes for all other elements in the Scope
dimension, which are Total Group
and EU
. The Select Ancestors button will select Total Group
because Total Group
is an ancestor of AMER
in the Legal Entity
dimension, and unselect EU
because this is not an ancestor of AMER
and is just a sibling.
Example of how Reuse Journal Entries works
When the current journal entry is defined for the AMER
scope, the Actual
version, and the Segment Consolidation ledger
. The user decides to add the following custom details in the Journal Entry Custom Details report:
- Additional Ledger: Consolidation
- Additional Scopes: Total Group
- Additional Versions: Forecast 3+9, Forecast 6+6, Forecast 9+3
When executing the Consolidation procedure for both ledgers, all scopes, and the Forecast 6+6
version, four duplicated, automated journal entries (1009, 1010, 1011, 1012) are created and posted.
Mapping
Mapping from Consolidation Ledger
to Segment Consolidation ledger
follows the subsequent logic.
-
Entry lines in the
Segment Consolidation ledger
require six additional fields:Segment
,Partner Segment
,Region
,Partner Region
,Custom Dimension 1
, andCustom Dimension 2
. These are populated with the~
(Not applicable) element (more preciselyNA
element as defined in the settings of the specific dimension). -
Entry lines entered for the
Profit and Loss
(Balance Sheet, Cash Flow
) cube are converted to theProfit and Loss (Segment)
(Balance Sheet (Segment)
,Cash Flow (Segment)
) cube.
Mapping from Segment Consolidation Ledger
to Consolidation ledger
follows the subsequent logic.
-
Entry lines in the
Consolidation ledger
require six lesser fields. The content of theSegment
,Partner Segment
,Region
,Partner Region
,Custom Dimension 1
, andCustom Dimension 2
fields are ignored in the copy. If this leads to multiple entry lines with otherwise identical coordinates, these are consolidated by the following rules:
- The debit and credit amounts are netted.
- If a journal entry line results in a zero amount, it is removed from the journal entry.
- If all journal entry lines have been removed, the journal entry is removed. -
Entry lines entered for the
Profit and Loss (Segment)
(Balance Sheet (Segment)
,Cash Flow (Segment)
) cube are converted to theProfit and Loss
(Balance Sheet, Cash Flow
) cube.
4. Subsequent capital consolidation
Journal entries for the capital consolidation cannot be entirely created from the input data of the current reporting period. Instead, all capital consolidation journal entries from the prior reporting period are duplicated in the current reporting period and extended by additional capital consolidation journal entries, which then handle the changes in the current reporting period. Over the course of time, the set of such journal entries will grow. Only journal entries on the entities which have left the group (i.e. divested) are removed from that set.
The procedure identifies the prior reporting period by the Previous Version
attribute of the selected element in the Version
dimension and the Prior Period
attribute of the selected element in the Month_YTD
dimension. The capital consolidation journal entries are those on the C
(Capital Consolidation) level of the Consolidation
dimension.
The duplicated journal entries are not exact copies but are modified as follows:
- Entry lines affecting movements on balance sheet accounts are moved to the opening balance. In other words: The element in the
Transaction Type
dimension is changed toT000
(Opening). - Entry lines affecting a profit and loss account are moved to the opening balance of the retained earnings account. In other words: The cube is changed to the
Balance Sheet
cube, the account is changed retained earnings account and theTransaction Type
is set to theT000
(Opening) element. The retained earnings account can be configured with thebs_account_retained_earnings
key in theConfiguration
folder in the model settings.
5. Adjustments to the consolidation method
One important milestone during the preparation phase of the consolidation is the creation of the Combined Financial Statement. If the group consists entirely of fully-consolidated subsidiaries (entities of which the holding of the group has full control) the combined financial statement is just the sum of all separate financial statement. Otherwise, the figures from the separate financial statements must be adjusted. This step creates these adjustments and saves it on the Adjustment Consolidation Method
element of the Consolidation
dimension for the following cases:
- All profit and loss and all balance sheet figures from legal entities, which are using the Equity consolidation method (
E
), are inverted. - All intercompany relations to partner entities using the equity consolidation method are replaced by figures with the external partner (the
~
element in thePartner Entity
dimension) - All profit and loss and all balance sheet figures from legal entities, which are configured with the
N
(not consolidated) consolidation method, are inverted.intercompany - All profit and loss and all balance sheet figures from legal entities, which are using the Proportional consolidation method (
P
), are reduced to the proportional share of theirConsolidation Rate
. - All intercompany relations to partner entities using the proportional consolidation method are reduced to the proportional share of the partner and the reduction is moved to the external partner.
6. Combined Financial Statement
The combined financial statement is created or updated in this step. Almost all the consolidation measures are computed on this data set. The Financial Consolidation model creates the combined financial statement as a physical copy of values which had been prepared in the previous steps as aggregations and rule calculations. The physical copy defines a new base level which is the same level on which the automated consolidation journal entries are applied. Also, the physical copy increases the performance of the consolidation procedure and any subsequent access to the consolidated financial statement.
The combined financial statement need to be presented in the group currency. The group currency of the scope (the group or one of the subgroups) is currency of the holding entity of the scope. The following explanations use USD
(US Dollar) as an example for the group currency.
In a nutshell, the currency converted values are read from
- the converted currency element for the currency of the holding company, e.g.
> USD
in theCurrency
dimension and - the
Combined Financial Statement (Calculated)
element of theConsolidation
dimension
and stored on
- the explicit currency element of the holding company, e.g.
USD
and - the
Combined Financial Statement
element of theConsolidation
dimension.
In addition the following transformations are applied:
- All amounts in the
Profit and Loss
cube are transferred as described above. - All amounts in the
Balance Sheet
cube on accounts with a non-historic conversion type and on legal entities without business transactions in the current reporting period are transferred as described above - All amounts in the
Balance Sheet
cube on accounts with the historic conversion type and on legal entities without business transactions in the current reporting period transferred depending on the available currency conversion data:- If values converted at the historic rate into the group currency e.g.
USD_Historic
are available the historic rate value is used and stored on the explicit currency element of the holding company (e.g. theUSD_Historic
value goes toUSD
). A typical example of this is the Issued Capital account. Also: any value which is already on theUSD
currency element remains as such. - If values are available in the local currency
LC
but not converted at historic rate into the group currency (e.g. USD_Historic) individual transaction types might be convertible through a conversion type defined in the transaction types. These values are currency converted and stored on the explicit currency element of the holding company (e.g. theLC > USD
value goes toUSD
). A typical example of this is theT111
(Profit (loss)) andT112
(OCI) transaction types being converted at theAverage
conversion rates. - If values are not available in the local currency
LC
but only in the explicit group currency e.g.USD
these are preserved (e.g. theUSD
value goes toUSD
). A typical example of this is manual journal entries in the reporting currency.
- If values converted at the historic rate into the group currency e.g.
- All amounts in the
Balance Sheet
cube on accounts with a non-historic conversion type and on legal entities with a business transaction in the current reporting period are transferred as described above plus the additional mapping:- Incoming units: If a legal entity was not consolidated before (
N
) and has been changed to the full (F
) or the proportional (P
) consolidation method in the current reporting period the opening balance (transaction typeT000
) and any regular movements (transaction typesT100
-T799
) are changed to the Incoming units transaction type (T011
). - Change in consolidation method from equity to proportional or full: If a legal entity was consolidated with the equity method before (
E
) and has been changed to the full (F
) or the proportional (P
) consolidation method in the current reporting period the opening balance (transaction typeT000
) and any regular movements (transaction typesT100
-T799
) are changed to the Change in consolidation method transaction type (T012
). - Outgoing units: If a legal entity was consolidated with the full (
F
) or the proportional (P
) consolidation method previously, and has been changed to become not consolidated (N
) in the current reporting period, the opening balance all values the opening balance (transaction typeT000
) and any regular movements (transaction typesT100
-T799
) are preserved. In addition to this, a new value with the negative total of the opening balance all regular movements is created on the Outgoing units transaction type (T991
). The closing balance of such a legal entity in the combined financial statement would in this case be zero. - Change in consolidation method from proportional or full to equity: If a legal entity was consolidated with the full (
F
) or the proportional (P
) consolidation method before and has been changed to be consolidated with the equity method (E
) in the current reporting period the opening balance all values the opening balance (transaction typeT000
) and any regular movements (transaction typesT100
-T799
) are preserved. In addition to this, a new value with the negative total of the opening balance all regular movements is created on the Change in consolidation method transaction type (T012
).
- Incoming units: If a legal entity was not consolidated before (
- All amounts in the
Balance Sheet
cube on accounts with the historic conversion type and on legal entities with a business transactions in the current reporting period are transferred as described in item 3. plus the additional mapping described in item 4.
This is the last step of the preparation phase.
7. Automated consolidation journal entries
Finally, actual consolidation begins. The consolidation creates new automated journal entries on the consolidation levels Capital Consolidation (C
), Debt Consolidation (D
), Elimination of Unrealized Profit on Inventory (I
) and Expenses and Income Consolidation (S
). The volume of the journal entries is defined through the consolidation rules in combination with the data on the combined financial statement. The exact content of these journal entries is defined by the posting journal instructions of the consolidation rules.
8. Delete reversed automated journal entries
The automated journal entries from previous executions of the consolidation procedure can be completely removed from the posting journal in this step. The execution of this step is optional and can be configured by the keep_reversed_automated_journal_entries key
in the Configuration
of the model settings.
Keeping reversed automated journal entries may increase the audibility, but likely compromise speed.
Implementation
The Financial Consolidation Procedure is implemented as the xdac17_130_Automated consolidation for all subgroups
job in the Consolidation
Integrator project. This is executed with the following parameters:
xfss05_SolutionDatabase
: The database name (without connection)
xdpj03_Version_pattern
: Selected version in regex-quotes (e.g. to allow for a plus sign) and as RegEx pattern
xdpj03_Month_YTD_pattern
: RegEx pattern denoting one or more yyyy-MM_YTD
elements
xdac02_Scope_pattern
: RegEx pattern denoting either a single scope or the list of scopes. The scope(s) must also be consolidated elements in the Legal Entity
and the Partner Entity
dimension.
xdpj03_Consolidation_pattern
: RegEx pattern matching the consolidation levels to be computed (The consolidation levels are single uppercase characters)
xdpj09_User
: The current user. User for the audit trail
xdac17_consolidationLedger
: 1
if the consolidation should process the regular Consolidation Ledger (which contains the Profit and Loss
, Balance Sheet
and Cash Flow
cubes). 0
Otherwise.
xdac17_segmentConsolidationLedger
: 1
if the consolidation should process the Segment Consolidation Ledger (containing the Profit and Loss (Segment)
, Balance Sheet (Segment)
and Cash Flow (Segment)
cubes). 0
Otherwise.
Updated September 27, 2024