Context: You need to implement a currency conversion in a new Hyperion Planning application. We’ll consider here two different cases : one with a multiple currency input per entity, and the other with a single currency input per entity. Furthermore, many reporting currencies should be calculated based on multiple currency input.
Objective : A currency conversion script / or Business rules needs to be implemented to manage this currency conversion.
Case 1 – Implementation of currency conversion with a multiple currency input.
Hypothesis:
- Multiple currency data input
- Multiple reporting currencies : Euro, US Dollar, Great Britain Pound
- Periodic currency conversion at month level
- Currency triangulation (Euro is used as pivot currency)
Let’s take the example of a consulting company operating worldwide. Let’s consider input of one fictive consultant consulting turnover, Daniel J Kessler, for the Singaporian entity and the asian market, with 3 different currencies of input : Euro, Us Dollar, and Singaporian Dollar.
Let’s consider a March Actual closing performed into an Hyperion Planning application, with following outline configuration:
We’ll see how to correctly convert revenues in that specific configuration.
Input of consulting revenues generating by the consultant activity is shown below:
Revenues are generated in 3 different currencies for a single entity & market.
Now let’s consider exchange rates uploaded in the database. Exchange rates are all uploaded against Euro:
As we input all exchange rates against euro, it will be used a the pivot currency, used to calculate other reporting currencies by triangulation.
As exchange rates are input in unit (local currency / euro), the revenue input in local currency will have to be divided by the exchange rate input at currency level.
In that specific case, for March currency conversion, the “Euro Consolidated” reporting currency will be calculated as follows:
“Euro Consolidated”=5000 (euro input) + 6400 (USD input) / Exchange rate USD->EUR + 7 200 (SGD input) / Exchange rate SGD->EUR = 15 775
Now that raises the question, how to design such calculation in an essbase calculation?
We’ll use the following script syntaxe to convert data in the way introduced above:
Idea is to fix input currency and to apply for each currency the currency conversion, and sum all contributions of each input currencies to get the total “Euro consolidated” value.
The expression “Euro Consolidated” = “Euro Consolidated” + “Scenario”/ “Ex Rate” …. shows we add each local currency contribution one by one to the member “Eur Consolidated”. Essbase processed the calculation one input currency after the other.
Warning : this method requires to reset “Euro consolidated” member before each new conversion.
To calculate other reporting currencies, for instance “USD Consolidated” or “GBP Consolidated”, then the following script syntaxe can be used:
A multiplication between the euro values and the exchange rate is now applied.
Case 2 – Implementation of currency conversion with a single currency input.
- Single currency data input per entity
- Multiple reporting currencies : Euro, US Dollar, Great Britain Pound
- Periodic currency conversion at month level
In this context, revenue is input for each entity in one single currency per entity.
In this case, opportunity to detail in the currency dimension all currencies can be raised. It might be possible to only create a “Local Currency” currency in the dimension, for input of entity currency data, to be converted afterward in reporting currencies.
Note that exchange rate should in that case input per entity, on the “Local Currency” member. Of course for entities sharing the same currency that leads to a multiple input, but that’s ok in terms of workload isn’t it?
Check below what could be this configuration:
Input for the consultant generated revenue is done in the SGD currency for the Singapore entity:
Exchange rate is now input on the entity Singapore, on the “Local Currency” member:
In that case, essbase script is far more easy as we can see below:
As local currency data are directly input on member “Local Currency” in that configuration, and “Local Currency” member also contained the required exchange rate for the entity calculated, a simple division can leads us to required result.
Of course, a block calculation mode will be suggested to get rid of block creation issue.
