Consolidations with exceptions...
Posted: Thu Oct 09, 2014 7:26 am
Hi All,
We are trying to build in a Constant Dollar calculation into our GL Cube and have been having trouble getting it to calculate properly, mainly due to the way that TM1 is performing the consolidation formulas.
In our GL we have balances in multiple currencies, we report our expenses in AUD$ (and convert all other FX to AUD$) for local reporting but for our Global requirements we need to report as USD$. So that we can get a better picture of what all the variances are we report with a Constant Dollar so that FX variance is removed.
The most basic calculation for the Constant Dollar is for each month, take the aggregate of "USD$ FX rate & Multiply by the number of days in the month" and divide by the Total Days YTD. We are able to get this calculating at the Leaf Level as it's simply the AUD$ * a Constant$ Rate. For simplicity we just load the Monthly, QTR & YTD rates into a Parameter cube & reference them there.
We would also assume that this would also work for period consolidations of QTR & YTD if it wasn't for the fact that we have "Exceptions" to consider...
In our GL we have GL's that are flagged as "USD$" even though the currency in the system is still AUD$, so we have set up an Account attribute called "USDC_Flag" to deal with this. So when you look at these accounts at the leaf level, the USDE & USDC amounts should be the same.
The problem we have is trying to get these "exceptions" to be calculated properly when the Consolidation is taking place. What we have determined is that for the leaf level, we are all good, the Rule works as expected. When these Exceptions are consolidated with Parents that don't have the "USDC_Flag" we lose the calculation for that Exception and the rule calculates as if the exception never existed.
Below is one attempt to get the Consolidation to work properly by tweaking the Leaf (note that LCYE=Local Currency, USDE=USD$ Equivalent)
Below is an old Consolidation Rule that had the issues as per above, the exceptions calculations were reomved:
Has anyone tried similar?
We have the SeparateT&CRules turned on, not sure if that's part of the problem or not...
Thanks,
RJ
We are trying to build in a Constant Dollar calculation into our GL Cube and have been having trouble getting it to calculate properly, mainly due to the way that TM1 is performing the consolidation formulas.
In our GL we have balances in multiple currencies, we report our expenses in AUD$ (and convert all other FX to AUD$) for local reporting but for our Global requirements we need to report as USD$. So that we can get a better picture of what all the variances are we report with a Constant Dollar so that FX variance is removed.
The most basic calculation for the Constant Dollar is for each month, take the aggregate of "USD$ FX rate & Multiply by the number of days in the month" and divide by the Total Days YTD. We are able to get this calculating at the Leaf Level as it's simply the AUD$ * a Constant$ Rate. For simplicity we just load the Monthly, QTR & YTD rates into a Parameter cube & reference them there.
We would also assume that this would also work for period consolidations of QTR & YTD if it wasn't for the fact that we have "Exceptions" to consider...
In our GL we have GL's that are flagged as "USD$" even though the currency in the system is still AUD$, so we have set up an Account attribute called "USDC_Flag" to deal with this. So when you look at these accounts at the leaf level, the USDE & USDC amounts should be the same.
The problem we have is trying to get these "exceptions" to be calculated properly when the Consolidation is taking place. What we have determined is that for the leaf level, we are all good, the Rule works as expected. When these Exceptions are consolidated with Parents that don't have the "USDC_Flag" we lose the calculation for that Exception and the rule calculates as if the exception never existed.
Below is one attempt to get the Consolidation to work properly by tweaking the Leaf (note that LCYE=Local Currency, USDE=USD$ Equivalent)
Code: Select all
['USDC'] = N:
# If the Account is NonFinancial, ignore for USDC calcs
If( Attrs('Account', !Account, 'NonFinancial') @= 'Y',
# Show as Local Currency
['LCYE'],
# If the Period element is a Consolidation...
IF ( ELCOMPN ('Period', !Period) > 1,
# And there are no special USDC flags or is $USD Currency Code
IF (Attrs('Account', !Account, 'USDC_Flag') @<> 'Y' % !CurrencyCode @<> '840',
# Check if the Period Element is a Quarter or not
IF( Scan('Qtr',!Period)>0,
# If a Quarter, use the Quarter Constant Dollar Rate
['LCYE'] * DB('z_ctrl_measures', 'Constant Qtr','Range1'),
# Else use the YTD Constant Dollar Rate
['LCYE'] * DB('z_ctrl_measures', 'Constant YTD','Range1')
),
# If so, use the USDE total
['USDE']
),
# If period isn't a Consolidation...
# Check if there are special USDC flags or is $USD Currency Code
IF( Attrs('Account', !Account, 'USDC_Flag') @= 'Y' % !CurrencyCode@= '840',
# If so, use the USDE total
['USDE'] ,
# If not, use the Monthly Constant Dollar
['LCYE'] * DB('z_ctrl_measures', 'Constant Mth','Range1')
)
)
);
Code: Select all
['USDC'] = C:
If the Element is a consolidation
IF ( ELCOMPN ('Period', !Period) > 1,
If the USD Flag is Y or Currency Code is 840 or Account is NonFinancial, ignore the Cell
If( Attrs('Account', !Account, 'USDC_Flag') @= 'Y' % !CurrencyCode@= '840' %
Attrs('Account', !Account, 'NonFinancial') @<> 'Y',
['LCYE'],
Else, take the Local Currency Amount
['LCYE'] *
If the Period is a Quarter use the Quarter Constant Dollar Rate
IF( Scan('Qtr',!Period)>0,
If yes, use the "Constant Qtr" rate from z_ctrl_measures cube
DB('z_ctrl_measures', 'Constant Qtr','Range1'),
Else assume that the period uses the YTD rate from z_ctrl_measures cube
DB('z_ctrl_measures', 'Constant YTD','Range1')
)
),
Else Ignore the Cell
['LCYE']
);
We have the SeparateT&CRules turned on, not sure if that's part of the problem or not...
Thanks,
RJ