Constant Fx Rate Analysis

[WAllison]

What is the best way to design and implement constant fx rate analysis?  We want to apply a budget rate to all months for actual/fc/budget.  Entities are loaded in local currency and I thought it would be simple to use an entity hierarchy in which I'd put the custom rates at the parent level so during consolidation it would use the set fx rates.  Unfortunately this is not working as I had hoped, I'm not seeing a difference at the parent level between the "real" hierarchy and the constant rate hierarchy.

Thank you.

[TonyToniTone · Accepted solution]

When base level data is imported into Stage for a base Entity, the data is set at Local for each base Entity.  During the Load Cube process, the data records are loaded into the Cube from Stage and the data records are stored as the Local Currency for the base Entity in the Data Record tables.  For example, if base Entity A has a currency of USD, all the data records for base Entity A will be stored as USD as the currency.  

Once in the Cube, a consolidation would follow this pattern:

For Base Entity -  Local > Translate > OwnerPreAdj > Share > Elimination > OwnerPostAdj > Top.  Top contributes to Local at the direct Parent Entity.

For Parent Entity, the same steps occur - Local > Translate > OwnerPreAdj > Share > Elimination > OwnerPostAdj > Top.  This process continues until the consolidation completes at the Top Entity.  

During the Translate step of the Consolidation, the FX Rates used for translation is directly related to the FX Rates defined on the Scenario.  If FX Rates are not used on the Scenario, the FX Rates default to the FX Rates on the Cube.  

With all this being said, I'm not entirely sure how the custom rates would have been applied to Parent Entities without a custom translation business rule. 

Without getting into the details of your desired Constant FX Rates Analysis process, here is a simple way to setup an interim solution in a Development environment for you to evaluate:

1.  Create a new scenario called Actual_BudgetRates

2.  Create a new FX Rate Type called Average_BudgetRates ( Create more FX Rate Types if needed for your process )

3.  Load FX Rates for Average_BudgetRates

4.  Apply the FX Rate Type to the FX Rates section of the Actual_BudgetRates Scenario

5.  Copy Actual data records from the Actual Scenario to the Actual_BudgetRates Scenario ( Scenario copy formula is the quickest method to copy the data but can also be done via a Data Management Copy Data step )

6.  Create a Cube View with Accounts in rows with Actual and Actual_BudgetRates Scenarios as columns.  Verify that data has copied successfully.  Pick a simple base Entity to test against.  

7.  Run a Force Consolidation on the Actual_BudgetRates Scenario

8.  Refresh the Cube View and verify that the data is using the correct rates for the Actual_BudgetRates Scenario

This is something that can be done relatively quickly to evaluate if this meets your needs.  I would recommend submitting a ticket and discussing this process in more detail with AAS.  As in any design and implementation, it is good to understand the requirements and what you ultimately want for the business outcome.  There are additional considerations to think about with a design and implementation of this process such as formulas, business rules, reporting requirements, and efficient performance processes.