Financial Consolidation Procedure

image_pdfimage_print

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

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

5. 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 are available in both the local currency LC and converted at the historic rate into the group currency e.g. USD_Historic 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 T100T799) 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 T100T799) 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 T100T799) 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 T100T799) 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.

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

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

 

image_pdfimage_print