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.

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

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.

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…

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.

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.

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:

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.

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:

This table has the following fields:

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:

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:

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.