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
Stored procedures and tables of rules
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 Rule | Stored procedure | Table(s) of rules |
---|---|---|
CopyOpening | Canvas_ENG_CopyOpening | Canvas_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_Intercompany | Canvas_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_EquityPickup | Canvas_EquityPickup Canvas_EquityPickup_Detail Canvas_EquityPickup_Booking |
How to run the stored procedures
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.
Required Applications
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.
Required dimensions
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.
Requirements of the MAIN Model
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.
Requirements of the RATE Model
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).
Requirements of the OWNERSHIP Model
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.
Required properties
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:
Canvas_ENG_CopyOpening
The 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 Canvas_CopyOpening_Rule table
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 Name | Length | Description |
---|---|---|
Copy Opening Rule | 255 | The Label of the Rule. This is the main driving field, it is used In the Account Dimension, in the property Called “CopyOpening_Rule”. |
CopyOpening_Number | Numeric | If For One Rule more than one row is necessary.This is used when you need more than one row to setup the rule. |
Entity | 255 | Entity 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. |
Scenario | 255 | This field may contain a valid base level from the Scenario dimension. This is the Source Scenario |
Businessprocess | 255 | This 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. |
Flow | 255 | This 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_Year | 255 | This 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_Period | 255 | The 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_Scenario | 255 | A Valid Scenario Dimension Member. This is the Destination Scenario |
Opening_BusinessProcess | 255 | A Valid BusinessProcess Dimension Member. This is the Destination BusinessProcess |
Opening_Account | 255 | Destination_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_Flow | 255 | This 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 period | 255 | Period 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_Other | 255 | This 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 Amount | Boolean | True 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_Amount | Boolean | Invert 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. |
YTD | Boolean | Yes / No, In a PERIODIC application this field can be set to “Y” to calculate the YTD value to copy. |
Property Dimension link to the COPYOPENING procedure
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.
The Canvas_Validation table
This table has the following fields:
Column Name | Length | Description |
---|---|---|
Validation Label | 255 | A Label For the Validation |
Entity | 255 | Entity 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_Type | 255 | The operand of the comparison (=, < , >, ⇒, ⇐) |
Destination_Other | 255 | This 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 period | 255 | Period 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. |
Tolerance | Integer | Number. |
Dimension_BreakDown | 255 | List of dimension |
Lock | Boolean | Locking Validation True / False |
From | 255 | Time Dimension Member. The start time for the availability of the rule. |
To | 255 | Time Dimension Member. The End time for the availability of the rule. |
Destination Account | 255 | Destination Account. This field controls the ID of the destination account. It cannot be blank. |
Compare Account | 255 | Not used |
The Canvas_Validation_Detail table
This table has the following fields:
Column Name | Length | Description |
---|---|---|
Sign | + or - | Plus or Minus |
Account | 255 | This 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 |
Flow | 255 | This 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 Filter | 255 | Other dimension |
Periodic | Boolean | True / 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:
Canvas_ENG_Conversion
This procedure is launched using a business Rule. The Parameters are:
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 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:
The Canvas_Conversion_Rule table
Column | Name | Length | Description |
---|---|---|---|
Label | 255 | This 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. |
The Canvas_Conversion_Rule_Detail table
Column Name | Length | Description |
---|---|---|
Conversion Rule | 255 | The value of the Conversion. |
Entity | 255 | 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. This is Used when you want to apply a special rule for one or a group of entity. By Default it is blank. |
Flow | 255 | This 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_Periodic | 255 | To 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_Flow | 255 | This 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_Account | 255 | Destination_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. |
FORMULA | 255 | A valid formula from the Canvas_Conversion_Formula Table. |
Sum_To_Flow | 255 | This field controls the ID of another destination FLOW then the previous one. If left blank the FLOW No additional Destination_Flow will be added. |
From | 255 | Time Dimension Member. The start time for the availability of the rule. |
To | 255 | Time Dimension Member. The End time for the availability of the rule. |
The Canvas_Conversion_Formula table
Column | Name | Length | Description |
---|---|---|---|
Label | 255 | Field 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.
Property Dimension link to the COPYOPENING procedure
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.
Supporting properties in the BUSINESSPROCESS dimension
Property Name | Description |
---|---|
BuisnessProcess_Class | Input (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 |
Conversion | If checked the BUSINESSPROCESS is converted. |
Supporting properties in the ENTITY dimension
Property Name | Description |
---|---|
Currency | Must 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. |
Supporting properties in the CURRENCY dimension
Property Name | Description | |
---|---|---|
REPORTING | If it is Checked, The Currency Is automatically copied in the reporting_Currency Dimension. | |
RATE_OPERATOR | Multiply Or Divide. Depending on how you want to enter the rate. |
|
MULTI_CURRENCY | Checked if you enter Multiple currencies, comma separated. |