Financial Consolidation Procedure

Return to Financial Consolidation Model Overview.

The consolidation process has five phases:

  1. Configuration
  2. Data Collection
  3. Preparation
  4. Consolidation
  5. 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

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:

  1. 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.

  2. 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

  3. 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.

Notes:

  • The next time the Consolidation procedure is executed this procedure will automatically reverse any reused journal entries with section two of this article (2. Reverse all automated journal entries) which is described above, and will then create new automated journal entries.

  • Reuse Journal Entries is a repeatable process as the results from previous runs are reverted and recreated according to the current definition by the system automatically.

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, and Custom Dimension 2. These are populated with the ~ (Not applicable) element (more precisely NA 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 the Profit 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 the Segment, Partner Segment, Region, Partner Region, Custom Dimension 1, and Custom 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 the Profit 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:

  1. 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 to T000 (Opening).
  2. 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 the Transaction Type is set to the T000 (Opening) element. The retained earnings account can be configured with the bs_account_retained_earnings key in the Configuration 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:

  1. All profit and loss and all balance sheet figures from legal entities, which are using the Equity consolidation method (E), are inverted.
  2. All intercompany relations to partner entities using the equity consolidation method are replaced by figures with the external partner (the ~ element in the Partner Entity dimension)
  3. 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
  4. 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 their Consolidation Rate.
  5. 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 the Currency dimension and
  • the Combined Financial Statement (Calculated) element of the Consolidation dimension

and stored on

  • the explicit currency element of the holding company, e.g. USD and
  • the Combined Financial Statement element of the Consolidation dimension.

In addition the following transformations are applied:

  1. All amounts in the Profit and Loss cube are transferred as described above.
  2. 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
  3. 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:
    1. 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. the USD_Historic value goes to USD). A typical example of this is the Issued Capital account. Also: any value which is already on the USD currency element remains as such.
    2. 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. the LC > USD value goes to USD). A typical example of this is the T111 (Profit (loss)) and T112 (OCI) transaction types being converted at the Average conversion rates.
    3. 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. the USD value goes to USD). A typical example of this is manual journal entries in the reporting currency.
  4. 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:
    1. 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 type T000) and any regular movements (transaction types T100 - T799) are changed to the Incoming units transaction type (T011).
    2. 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 type T000) and any regular movements (transaction types T100 - T799) are changed to the Change in consolidation method transaction type (T012).
    3. 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 type T000) and any regular movements (transaction types T100 - 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.
    4. 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 type T000) and any regular movements (transaction types T100 - 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).
  5. 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 March 27, 2024