Some preliminary calculations must be executed as initial step of the Consolidation Procedure.
These steps (or Procedures) can be even executed separately from the Consolidation Procedure but they are mandatory.
These Procedures work on Layer 01 only (Local Input Layer).
> The entire set of Procedures included in the PRELIMINARY Calculations Logic Group can be executed before and independently from the rest of the Consolidation Procedure. If there is no change in Data there is no reason to re-execute it any time you run the Consolidation Procedure
Consolidation NODES allocation (040 - 00 Allocate local Input across Nodes)
 |
The L01 Data (RUs Trial Balances) and the L02-L04 Data (Local Adjustment) are loaded on the Default Node '99'. The Ownership %s are given per a specific Consolidation Node. Therefore to apply the proper Conso Logics it is needed to 'allocate' the L01-L02-L04 Data from Node '99' to the Consolidation Scope Nodes. 'OPE' and 'PAL' Movements are excluded because re-calculated during the 'Opening Procedure' |
> The Trial Balances are 'allocated' on a Temporary Cube with Conso Node as (o) and then re-stated into the Conso Value cube with Conso Node as (x) thankful to the Advanced Dataflow option 'Open Sparsity'
Open SPARSITY on Plug Accounts
This step is purely tech.
> To optimize the Procedure Speed the Account Dimension has been kept as Sparse (x). The cells on the Plug Accounts for any potentially valid combination of all the other existing dimensions of the Conso value cube must be 'open'. This is done with a 'tricky' Dataflow with the Advanced Dataflow option 'Open Sparsity'.
EXI, ERR and STM for Input and Adjs Layers (040 - 01 Exit, Error and STM Calculation)
On All the Data Input Layers (L01-L02-L04-L07-L12) the following Movements must be calculated :
> EXI (Exit Movement)
All the movements accounts TBs are 'eliminated' for the RUs consolidated at EXIT Method posting the EXI Movement = -(OPE+MOV-EXI)
-The balance is lessen of the EXI Movement itself since the Refer To on the MOVs Movements Group includes the EXI Movement.
- EXIT RUs has the Conso Method Nr = 5
> STM (Short Term Movement)
The STM Movement must be calculated for all the RUs not Consolidated at EXIT Method for all the STM Accounts. This Movement is needful during translation since STM Accounts must be translated at AVG Rate and for them Exchange Rate Difference on Closing Rate must be calculated. Please refer to Translation section.
> ERR (Error Movement)
To balance eventual gaps across the Movements, the ERR Movement must be calculated for all the RUs not Consolidated at EXIT Method for all the MOV Accounts (no STM) posting the ERR Movement = CLO - (OPE+MOV-ERR).
The balance is lessen of the ERR Movement itself since the Refer To on the MOVs Movements Group includes the ERR Movement.
The Account are grouped accordingly with their Movement association in the following Groups :
> ZNA (No Movement) : these Accounts typically have all the TB on the CLO (Closure) Movement and do not require any further detail. All the P&L Accounts belong to this Group. They are never being re-opened (OPE Movement). They're known as Flow Measures and always translated at AVG Translation Rate.
> MOV (Movements) : these Accounts have CLO (Closure) Movement, they have OPE (Opening) Movements and as many Movements as required. The most of the Balance Sheet Accounts are belonging to this Group . They are known as Stock Measure. Typically they're also translated at CLO Translation Rate.
Any Gaps between OPE+MOVs and CLO is posted on the ERR (Error movement)
> STM (Short Term Movements) : these Accounts have CLO (Closure) Movement, they have OPE (Opening) Movements but they do not have any Movements. Some Balance Sheet Accounts belong to this Group. Even if they are STock measure they're translated at AVG Translation Rate.
Any Gaps between OPE and CLO is posted on the STM (Short Term Movement) that pays the same role as ERR for MOV Accounts.
PROFIT for the Period for Input and Adjustments Layers
 |
The Balance Sheet Profit for the Period is a Plug Account that is always calculated as Expenses (+) + Revenue (-). Therefore : > It must never be loaded in the P&L neither in the Balance Sheet > In the P&L can be calculated with a Rule for reporting purpose > In the BS it is calculated with the same logic to lock the TB consistency between P&L and BS > The Plug Account is identified with the relationship with the Tech Account for Select '010 - Profit for the Period' . By default is 'T010 - Profit for the Period'. Refer to the Plug Account section how to change it. |
EXCHANGE RATES (040 - 00 Calculate Exchange Rates)
The TB Translation happens during L06 multiplying the Conso Value cube by Translation Rate cube. The Translation Rates are given by Translation rate rule (AVG or CLO) , Node Currency and Scenario-Period ; these rates must then be 'allocated' across the Accounts and the Movements accordingly to their Translation Rate Rule.
Step 1 - Accounts without Movements translated at AVG
ZNA Account have only the CLO Movement that is translated at AVG Translation Rate
Step 2 - Accounts with Movements translated at AVG
MOV Accounts can be tranlsated at AVG or CLO Translation Rate. This step populates the translation rates for :
- Accounts translated at AVG Translation Rate and Movements translated at AVG Translation Rate
- Accounts translated at CLO Translation Rate and Movements translated at AVG Translation Rate
> The AVG Rate can be keyed in with YTD Rate or Monthly Rate. Depending from the General Setting (Super Setting Dimension) 01 - YTD Average Exchange Rate (Y/N) a different formula is adopted to calculate such Rate.
By default is Yes since it is the most popular choice.
> The different formula (Data Flow) is driven with an If...Then...Else Command based on the Super Settings info-cube Refer to 01 - YTD Average Exchange Rate (Y/N)
> If for any reason the Translation Rate is missing the constant value = 1 is applied to avoid data missing
Step 3 - Accounts with Movements translated at CLO
MOV Accounts can be tranlsated at AVG or CLO Translation Rate. This step populates the translation rates for :
- Accounts translated at CLO Translation Rate and Movements translated at CLO Translation Rate (CLO is included of course)
Step 4 - History Accounts
Some Accounts (tipically Net Equity Accounts) have a punctual Translation Rate that must be keyed in per account and movement. This is mandatory , please refer to the Setting Currency Exchange section. By default no Translation Rate (CLO or AVG is then applicable).
Such Accounts do have an Exchange Rate that is the AVG RAte if the General Setting is on (please see below) or the Historical Exchange Rate that must be manually keyed in the ad-hoc Local Reporting Capsule Screen. Please see related section.
If history account translated TB is missing it is very easy to realize it but the Translation Step fails of course (accounts zeroed) . Depending from the General Setting (Super Setting Dimension) 03 - HIS Rate Account @AVG (Y/N) the AVG Rate replaces the missing HIS Rate. This prevents the Translation Step to fail but it is more complex to debug .
By default is set up = N, since the HIS Rate setup is a mandatory and relevant step of the Conso process.
No MONETARY Account are not translated (fix rate =1) so no Translation Rate is required.
Step 5 - Movements at SPOT Rate
Under certain condition specific Movements (e.g. Purchase Movement) must be translated at a specific Rate regardless the Nature of the Account and the traditional Translation Rates.
This step populates the translation rates for All Accounts with Movements for those Movements that have been identified to be translated @SPOT Rate.
The SPOT Rate can be keyed in the Exchange Rate Edit Screen (please see related section) per Scenario, Movement and Reporting Unit.
The Gap with the CLO Rate is balanced in the EDM Movement during the Translation Procedure Step (L06)