Purpose of this document

This document describes the design of the EPM Consolidation Engine, and how it works. It is intended for administrators that understand the functionality and architecture of EPM and also have a reasonable knowledge of the rules governing a legal consolidation process. We do not try to explain here all the intricacies of the consolidation rules that can be defined with this tool, let alone the rationale of the accounting principles that drive such rules. This is left to the experts.

This document is structured as a reference manual, i.e. it is not quite suitable as material for a training course. On the other hand, it tries to provide a fairly detailed technical description of all the elements composing the module and how they can be used.

Introduction

The Consolidation Engine module is intended to perform most of the “number-crunching” activities required in the generation of the consolidated statements of a group of legal entities.

These activities include:

• The initialization of the beginning balances, when a new reporting cycle starts.

• The validation of the input data

• The conversion of the local currency data in the desired reporting currencies

• The matching of the inter-company transactions

• The generation of all the consolidation entries for the desired groups of entities (eliminations, adjustments, re-classifications, minority calculations, etc)

• Other calculations

Important remark: while this document is focused on the use of this module within the framework of a statutory (legal) consolidation process, most of the functionalities here described can also be applied to other types of financial reporting applications, because these tend to use a sub-set of what needed in a legal consolidation.

The building blocks of the Consolidation Engine

The heart of the Consolidation Engine is currently a set of SQL-based stored procedures that perform all the appropriate calculations on a user-selectable region of data, and write the calculated results into the database. The behavior of each one of these procedures is controlled by a set of definitions that are contained in some tables of rules. The content of these tables can be edited by the administrator, to make sure that the definitions meet the exact requirements of his specific application. The correct rules to apply to each record of data to process are identified by the value of some properties that are assigned to each individual ACCOUNT or to the members of some other dimension.

Following is the full list of these stored procedures and their associated tables of rules.

Business RuleStored procedure Table(s) of rules
CopyOpening Canvas_ENG_CopyOpeningCanvas_CopyOpening_Rule
Check Canvas_ENG_Validation Canvas_Validation
Canvas_Validation_Detail
Conversion Canvas_ENG_Conversion Canvas_Conversion_Rule
Canvas_Conversion_Rule_Detail
Canvas_Conversion_Formula
Intercompany Canvas_ENG_IntercompanyCanvas_Intercompany
Consolidation Canvas_ENG_Consolidation Canvas_Consolidation_Rule
Canvas_Consolidation_Rule_Detail
Canvas_Consolidation_Formula
Canvas_Repartition
Canvas_Repartition_Detail
Canvas_Consolidation_Formula
EquityPickup Canvas_ENG_EquityPickupCanvas_EquityPickup
Canvas_EquityPickup_Detail
Canvas_EquityPickup_Booking

To run these stored procedures, we make use of our Model’s Business rules to run stored procedures directly from the Data manager, an Excel Workbook using a Macro or the Menu, and to pass to them the appropriate parameters.

With this approach the customer has the possibility to freely decide when and how a process should be triggered. We can, for example, invoke a currency conversion directly from the Automatic Business Rule, whenever a value is changed via Excel or via a data load, or we can decide to run one or more consolidation processes in a batch mode. Also, we can combine one or more of these specialized processes.

Editing the tables of rules

The tool we use today to edit the content of the tables of rules is Excel. We have a Special Menu to update the tables.

Here below are some screenshots that show how to select a table for editing:

Select one Table

Update the selected table…

The requirements of the Application

In order for the Consolidation Engine to work properly, some required elements must exist in the Application using it.

When doing its things for a given application, the consolidation engine will look into other applications to retrieve some of the necessary information. Here is the list of the involved applications.

The MAIN Model

This is the Model for which the process is being performed (this being a currency conversion or a consolidation or some other one). This Model can be named as desired, but we will refer to it as the MAIN Model, in this document.

The FXRATE Model

The currency conversion process makes use of a RATE Model, where the appropriate exchange rates will be searched for each relevant currency. This Model can be named as desired, but we will refer to it as the RATE Model, in this document.

The OWNERSHIP Model

The Name of this Model Must be the Main Model Name + “_Ownership .” The Consolidation process makes use of an OWNERSHIP Model, where the definitions of each consolidation perimeter will be searched for. In particular, such definitions may include:

• The list of companies being consolidated in each group • Their consolidation method • Their consolidation percentage • Their ownership percentage (how much they are owned by the group) • Their control percentage (how much they are controlled by the group) • … All Other Percentage needed for the consolidation

This Model can be named as desired, but we will refer to it as the OWNERSHIP Model, in this document.

The SIMULATION Model

The Consolidation and the Currency Conversion processes make use of an SIMULATION Model, where the result of the simulation are stored. The Model must contain all the same Dimension as the Main Model plus a SIMULATION dimension.

Each one of the above listed Models must contain some required dimensions, while some other dimensions are optional.

All applications will obviously contain at least the four required ENTITY (Legal Entity Type for Consolidation), SCENARIO, TIME and ACCOUNT dimensions (albeit named as desired). However, the following rules apply:

Commonality requirements

The SCENARIO and TIME dimensions must be the same across all three applications, or they must at least contain the appropriate matching members.

The ENTITY dimension of the MAIN Model must be the same used in the OWNERSHIP application or at least it must contain the appropriate matching members.

The GROUP dimension of the MAIN application (see below for the meaning of such dimension) must be same used in the OWNERSHIP application or at least it must contain the appropriate matching members.

The MAIN Model must contain a CURRENCY dimension to store the translated amounts. The consolidation entries, as generated by the consolidation process.

Any additional dimension is optional in the MAIN Model, as far as the currency translation is concerned. For consolidation purposes however, some other requirements come into play, as here below described:

The application may have an INTERCOMPANY dimension, but it is not required for the Consolidation procedure to work, unless some elimination rule makes an explicit reference to this dimension.

The application may have a FLOW dimension (*). This dimension is optional, but, if it exists, it can be used (1) by the currency translation procedure, to detail the changes in the balance sheet generated by fluctuations in the exchange rates and (2) by the consolidation procedure to detail the eliminations applied to the movements of the balance sheet accounts.

(*) While the product does not natively support a dimension of type FLOW, the consolidation engine is able to identify this type of dimension by the existence in it of some required property, as later described.

A BUSINESSPROCESS dimension may exist in the MAIN Model, but it is not required by the currency translation. If it exists, however, the currency translation will be able to recognize which members of such dimension should be translated and which ones should be just copied as they are into the destination currency (see below for details). On the other hand, this dimension is required for the consolidation procedure to work.

Similarly to the FLOW dimension, a BUSINESSPROCESS dimension type is natively supported by the product, the consolidation engine is able to identify this type of dimension.

Additional (user defined) dimensions can be added to the MAIN Model (like product, market, division, etc.), as desired by the administrator. The Consolidation Engine will be able to recognize their existence and take them into account in the process, and even apply some custom behavior to their members.

The RATE Model will use the RATE dimension to detail the different types of rate (Average, End-of-period, etc.). This dimension is usually named RATE.

The ENTITY dimension of the RATE Model is used to store multiple tables of rates.

Finally, the rate Model must obviously include a CURRENCY dimension, where the rates are detailed for each applicable local currency And a REPORTING CURRENCY dimension.

No other dimension may exist in the RATE Model (other than the already mentioned SCENARIO and TIME dimensions).

The OWNERSHIP Model is only needed for the Consolidation process, because the Currency conversion obviously does not need it.

This Model must contain the same SCENARIO, TIME and ENTITY dimensions used by the MAIN application, or, as a minimum, its equivalent dimensions must contain members which match those used in the MAIN application.

The ACCOUNT dimension must contain a few special-purpose members, as later described.

An additional dimension (that we will refer to here as the GROUP dimension) must also be defined in this Model. This dimension is basically a replication of the ENTITY dimension (very similarly to what we do for the INTERCOMPANY dimension), and it is used to provide a storage model for what must be detailed by ENTITY / GROUP / SCENARIO / TIME (namely the definitions of a hierarchy of entities-rolling-up-into-groups, that can be tracked independently for each SCENARIO and TIME combination).

The GROUP dimension is the same one used in the MAIN Model.

Last, the OWNERSHIP Model may contain a SIMULATION dimension, to provide a storage of the Ownership Simulation data.

For additional details on how these information can be used in the OWNERSHIP Model please refer to the documentation of the custom Ownership Calculation task, appended at the bottom of this document.

No other dimension can exist in the OWNERSHIP Model.

Not only the Application must contain some applications, and the applications must contain some dimensions, but also some of these dimensions must have some required properties, which will be used by the consolidation engine to perform its tasks.

Refer to the related sections in this document for a detailed list of the properties required by each procedure, and their content.

The COPYOPENING Business Rule

This Business Rule can be used to initialize a new reporting period with the closing balances of last period from previous year into the opening balances of the current period.

In a legal consolidation application such balances are usually identified as members of the FLOW dimension. In simpler applications, however, it is also possible to store them as additional accounts in the ACCOUNT dimension.

The copy-opening Business rule is handled by a stored procedure called:

The Business rule requires several parameters that must be passed to it in the correct order and format, as here described.

ParameterMandatoryMulti/Single value
1-ScenarioYesSingle
2-TimeYesMulti
3-CurrencyYesMulti
4-EntityYesMulti
5-BusinessProcessYesMulti

The stored procedure Canvas_ENG_CopyOpening is driven by the content of a table called Canvas_CopyOpening_Rule, which is user-maintained. This table has the following fields:

Property NameLengthDescription
Copy Opening Rule255The Label of the Rule. This is the main driving field, it is used In the Account Dimension, in the property Called “CopyOpening_Rule”.
CopyOpening_NumberNumericIf For One Rule more than one row is necessary.This is used when you need more than one row to setup the rule.
Entity255Entity Label, Use it if for one entity you want a special rule for the copy opening. This field may contain a valid base level or parent member from the Entity dimension. If the value is a PARENT member, the rule applies to all base level members below it.
Scenario255This field may contain a valid base level from the Scenario dimension. This is the Source Scenario
Businessprocess255This field may contain a valid base level or parent member from the BusinessProcess dimension. If the value is a PARENT member, the rule applies to all base level members below it. This is the Source BusinessProcess.
Flow255This field may contain a valid base level or parent member from the Flow dimension. If the value is a PARENT member, the rule applies to all base level members below it. This is the source FLOW
Source_Year255This Field may contain the following values:
- Blank: the prior year by default
- Non-blank: the year where to read the opening balances from. It can be an absolute or a relative amount.
The Source Year (Absolute like 2012 or 2010, OR Relative like -1, -2) By Default it is -1.
Examples of values in the OPENING_YEAR property:
• 2004 Search the member from those having “2004” in the property YEAR.
• -2 Search the member from those having in the property YEAR a value equal to the current member’s YEAR property minus two.
Source_Period255The Source Period Absolute or relative) by default it is the last period. This Field may contain the following values:
- Blank: the last period of the year
- Non-blank: the period where to read the opening balances from. It can be an absolute or a relative amount
Examples of values in the OPENING_PERIOD property:
• 03 Use the member with the property PERIOD =”03” from prior year’s list
• -2 Use the member with the property MONTHNUM equal to the current member’s MONTHNUM property minus two (from prior year’s list).
Opening_Scenario255A Valid Scenario Dimension Member. This is the Destination Scenario
Opening_BusinessProcess255A Valid BusinessProcess Dimension Member. This is the Destination BusinessProcess
Opening_Account255Destination_Account (No Destinbation_account = Same as the Source). This field controls the ID of the destination account. If left blank the account will be the same as the source account.
Opening_Flow255This field controls the ID of the destination FLOW. If left blank the FLOW will be the same as the source FLOW. This is the Destination Flow.
Available period255Period Available for the Rule. This field may contains the period where this rule is available. Using this you can exclude some period. By Default the rule is available for All Periods.
Destination_Other255This field may contains the other dimension than the one in the rule for destination. This filed is used to force the destination for the specify dimensions. This is the Destination for Other Dimensions
Copy Converted AmountBooleanTrue The converted Amount (From currency Conversion) will be copied also. If this field contains “True”, the value of converted Value for the source dimensions are also copied. Those values come from the currency conversion Business Rule.
Invert_AmountBooleanInvert Sign, If this field contains “True”, the value of the amount is reversed. Note that this applies to the SIGN amount as stored in the DB.
YTDBooleanYes / No, In a PERIODIC application this field can be set to “Y” to calculate the YTD value to copy.

In addition to the instructions defined in the table, this procedure is controlled assigning a special properties to the ACCOUNT dimension. For Each Account you have to enter the Copyopening rule you want to apply for the Account. This is maintain in the Modeler, in the Dimension part.

The Validation Business Rule

This Business Rule can be used to check the integrity and correctness of the entered values, before signing off such data as “approved”. The action of this procedure is limited to the comparison of several couples of accounts (or sets of accounts) and to post the difference, if any, into some “error” account. The purpose should be to have zeros in all these “error” accounts.

The validation process is handled by a stored procedure called: Canvas_ENG_Validation

This procedure is launched using the Business Rule: Validation

The stored Business rule requires several parameters that must be passed to it in the correct order and format, as here described.

Parameter Mandatory Multi/Single value 1- Scenario Yes Single 2- Time Yes Multi 3- Currency Yes Multi 4- Entity Yes Multi 5- BusinessProcess Yes Multi

The stored procedure Canvas_ENG_Validation is driven by the content of a two tables called Canvas_VALIDATION and Canvas_VALIDATION_Detail, which are user-maintained. Their structure is described here below.

This table has the following fields:

Column NameLengthDescription
Validation Label255A Label For the Validation
Entity255Entity Dimension Member. Use it if for one entity you want a special rule for the copy opening. This field may contain the following values: - A valid base level or parent member from the Entity dimension. If the value is a PARENT member, the rule applies to all base level members below it.
Validation_Type255The operand of the comparison (=, < , >, ⇒, ⇐)
Destination_Other255This field may contains the other dimension than the one in the rule for destination.This filed is used to force the destination for the specify dimensions. This is the Destination for Other Dimensions
Available period255Period Available for the Rule. This field may contains the period where this rule is available. Using this you can exclude some period. By Default the rule is available for All Periods.
ToleranceIntegerNumber.
Dimension_BreakDown255List of dimension
LockBooleanLocking Validation True / False
From255Time Dimension Member. The start time for the availability of the rule.
To255Time Dimension Member. The End time for the availability of the rule.
Destination Account255Destination Account. This field controls the ID of the destination account. It cannot be blank.
Compare Account255Not used

This table has the following fields:

Column NameLengthDescription
Sign+ or -Plus or Minus
Account255This field may contain a valid base level or parent member from the Account dimension. If the value is a PARENT member, the rule applies to all base level members below it. This is the source Account
Flow255This field may contain a valid base level or parent member from the Flow dimension. If the value is a PARENT member, the rule applies to all base level members below it. This is the source FLOW
Dimension Filter255Other dimension
PeriodicBooleanTrue / False. In a YTD application this field can be set to “TRUE” to calculate the Periodic value to Validate

The currency conversion Business Rule

This Business Rule can be used to convert the local currency into Reporting Currency.

The Currency Conversion Business rule is handled by a stored procedure called:

This procedure is launched using a business Rule. The Parameters are:

ParameterMandatoryMulti/Single value
1-ScenarioYesSingle
2-TimeYesMulti
3-CurrencyYesMulti
4-EntityYesMulti
5-BusinessProcessYesMulti

The currency conversion process is handled by a stored procedure called: Canvas_ENG_Conversion

How the currency conversion works

The stored procedure CANVAS_ENG_CONVERSION scans all records found in the selected region of data and translates them according to the Conversion Rule property assigned to the ACCOUNT specified in each record, based on the following mechanism:

- All ACCOUNTS with no Conversion Rule (= blank) will be translated with a factor of 1

- All ACCOUNTS with the reserved Conversion Rule = None will not be translated

- All other ACCOUNTS will be translated according to the definitions contained in the table of parameters called Canvas_Conversion_Rule And Canvas_Conversion_Rule_Detail.

The stored procedure CANVAS_ENG_CONVERSION is driven by the content of tables called Canvas_Conversion_Rule, Conversion_Rule_Detail and Conversion_Formula, which is user-maintained. Those table has the following fields:

ColumnNameLengthDescription
Label255This is the main driving field, controlling the Currency Conversion rule to apply to a given account, according to the value of the Conversion_Rule property of the account. This Value will also be copied to the LST_Conversion_Rule, to be able to select it from the account Dimension.
Column NameLengthDescription
Conversion Rule255The value of the Conversion.
Entity255field may contain a valid base level or parent member from the Entity dimension. If the value is a PARENT member, the rule applies to all base level members below it. This is Used when you want to apply a special rule for one or a group of entity. By Default it is blank.
Flow255This field may contain a valid base level or parent member from the Flow dimension. If the value is a PARENT member, the rule applies to all base level members below it. This is the source FLOW.
Apply_Periodic255To be used if the model is YTD and you want to apply some periodic value for the currency conversion. None If you don’t want to apply The periodic Value – Periodic If you want to - Periodic YTD From Source (The YTD will be calculated from The Source Account
Destination_Flow255This field controls the ID of the destination FLOW. If left blank the FLOW will be the same as the source FLOW. This Must be a base level member. This is the Destination Flow.
Destination_Account255Destination_Account (No Destination_account = Same as the Source). This field controls the ID of the destination account. If left blank the account will be the same as the source account.
FORMULA255A valid formula from the Canvas_Conversion_Formula Table.
Sum_To_Flow255This field controls the ID of another destination FLOW then the previous one. If left blank the FLOW No additional Destination_Flow will be added.
From255Time Dimension Member. The start time for the availability of the rule.
To255Time Dimension Member. The End time for the availability of the rule.
ColumnNameLengthDescription
Label255Field may contain the Formula used for the currency conversion.

This Formula can contain any arithmetic expression combining any defined in the RATE Dimension of the Rate Model. In the RATE_FORMULA field, the rates must be enclosed in square brackets:

Rate Dimension:

Formula’s Examples: [Enrate] [EndRate] – [Average]

The OPENING value of any rate can also be specified adding the prefix “Opening_” to the rate itself. The Opening Value is the value of the last period of the previous year. Formula’s Examples Using the Opening value:

[Opening_EndRate] - [EndRate] [Opening_Average]

The Previous value of any rate can also be specified adding the prefix “Previous_” to the rate itself. The Previous Value is the value of the previous period. Formula’s Examples Using the Opening value:

[Previous_Average] - [Average] [Opening_Endrate]

These OPENING and PREVIOUS rates do not need to exist in the RATE Dimension Of the RATE Model. For example, if there is an [EndRate] rate, the currency translation will also automatically recognize a rate called [Opening_EndRate], which simply corresponds to the [EndRate] rate of the OPENING period (typically last period of last year).

In addition, the RATE Dimension, Formula supports the keywords “[HISTORICAL]” which Leave untouched a value already existing in the destination currency. This keyword can be combined with other rates in the same line. This will not support Opening and Previous Value. Example [HISTORICAL] [EndRate] - [HISTORICAL]

See the following example fr the currency Conversion:

The FX_TYPE field This field can be used to enforce a given set of rules to only apply to a desired set of ENTITIES. If this field has a value, the rule will only be applied to the entities having a matching value in a similarly named property (FX_TYPE) of the ENTITY dimension.

Example:

How the Entity RATE table is selected

Some exception by ENTITY can be applied. For example some entities just entering in the consolidation perimeter may need to be converted at their own specific set of rates. These entities may have a corresponding ENTITY member in the RATE Model.

See the following example.

In addition to the instructions defined in the Canvas_Conversion tables, this procedure can also make use of some special properties assigned to dimensions. These properties will affect the execution of the procedure as here below described.

Supporting properties in the Account dimension For Each Account you have to enter the Conversion rule you want to apply for the Account. This is maintain in the Modeler, in the Dimension part.

Property NameDescription
BuisnessProcess_ClassInput (Input Schedule Or Import From GL)
Manual (Manual Adjustment)
Auto (Automatic Adjustment Or Elimination.)
Only the members with BUSINESSPROCESS_TYPE = I or M are translated
ConversionIf checked the BUSINESSPROCESS is converted.
Property NameDescription
CurrencyMust be a valid Member from the Currency dimension.
This is the Local Currency Of the Entity.
Notice that for the Group, you can use multiple Currency. In this case the Currency Conversion will convert the amount in all currency from this Property for all entity part of the group.
Property NameDescription
REPORTINGIf it is Checked, The Currency Is automatically copied in the reporting_Currency Dimension.
RATE_OPERATORMultiply Or Divide.
Depending on how you want to enter the rate.
MULTI_CURRENCYChecked if you enter Multiple currencies, comma separated.
  • pclegal/docu.txt
  • Last modified: 2013/11/27 10:53
  • by pcheca