The OneStream Community is temporarily frozen until June 29th due to the ongoing maintenance. Please read the blog post here to learn more.
Forum Discussion
IC Elimination and USD Overrides
Our Currency Override formula in the Flow Dim (EndBal_Input) has been working great, no issues. We have been using IC Elim's on all of our Balance Sheet accounts except Equity/Investments. All of ou...
There is a big difference between Currency (what I Statutory Currency) and Auto Translation Currencies (what I call Reporting Currency). This was the mistake we made. We had an Entity structure where we wanted to show our European entity (base - EUR) in USD and have the elimination/override work. The underlying process for reporting currency conversion would not take into account our overrides, thus the issue.
My understanding of the explanation was that for Reporting Currency, it just takes the base currency at parent level and converts to new currency at the FX rate listed. Your override value never comes into play. another way of stating is it consolidates in base currency to parent and then converts. This was way the Elimination value never matched the Import value. Import got converted via the rule and Elim value was just the local value @ FX rate.
Our fix, since our goal was to report in USD all segments, was to create a full structure where all parents were USD. When the parent is in USD and the child in EUR, the consolidation would take into account the override value for USD and store it. Never needing to do any conversion.
So we have two main Statutory Entity rollups. One in local currency so each region can report in their required currency and then one in USD so that we can report all regions/segments in USD. They only tie at the top level for those accounts that have currency overrides.
Here is the code we use in EndBal_Input (FormulaPass2). Accounts are marked in Text2 field if they can have an override.
If api.Cons.IsForeignCurrencyforEntity() Then
If api.Pov.Cons.Name.XFEqualsIgnoreCase("USD") Then
api.Data.Calculate("F#EndBal_Input:O#Import = F#Hist_USD_Override:C#Local:O#BeforeElim", "A#Root.base.where(Text2 = OVD)")
api.Data.Calculate("F#EndBal_Input:O#Forms = F#Hist_USD_Override:C#Local:O#Forms * 0", "A#Root.base.where(Text2 = OVD)")
api.Data.Calculate("F#EndBal_Input:O#AdjInput = F#Endbal_Input:C#USD:O#Adjustments * -1", "A#Root.base.where(Text2 = OVD)")
End If
If api.Pov.Cons.Name.XFEqualsIgnoreCase("EUR") Then
api.Data.Calculate("F#EndBal_Input:O#Import = F#Hist_EUR_Override:C#Local:O#BeforeElim", "A#Root.base.where(Text2 = OVD)")
api.Data.Calculate("F#EndBal_Input:O#Forms = F#Hist_EUR_Override:C#Local:O#Forms*0", "A#Root.base.where(Text2 = OVD)")
api.Data.Calculate("F#EndBal_Input:O#AdjInput = F#Endbal_Input:C#EUR:O#Adjustments * -1", "A#Root.base.where(Text2 = OVD)")
End If
Hope this helps. Once I understood how the calc was happening between the two types of currency reporting and the Elim value, it made sense for us to create the second Entity structure. Starting in the next year, this new structure was correct at all levels for USD.
Thank you for the response. I will review this with the consultant to see if it makes sense for us too. I'm just trying to get equity to work as I have multi-level consolidations as well (SEK/EUR/USD) So no issues having 2 hierarchies? So the 'Local' one does not work at all in USD I assume?
How do you handle Journals that need overrides and how do the movements in the equity roll forward pull the correct USD?
