Currency Conversion Template Example

  1. In a Oracle Hyperion Planning application that was created with no multiple currencies in addition to the existing dimensions, create a custom dimension named "Currencies" and add these members:

    • Input Currencies

      • USD

      • CAD

      • EUR

      • GBP

      • INR

    • Reporting Currencies

      • EUR Reporting

      • US Reporting

      • Can Reporting

  2. In the "Account" dimension, add these members for FX_Rates:

    • FX_Average

    • FX_Ending

  3. In the "Entity" dimension, add the entity "Company Assumptions".

  4. Using Oracle Smart View for Office or a Planning form, enter exchange rates in these cells:

    • Account—FX_Average and FX_Ending

    • Period—Enter rates for each month

    • Year—FY12

    • Scenario—Current

    • Version—BU Version_1

    • Entity—Company Assumptions

    • Products—000

    • Currencies—Enter an exchange rate for each currency

  5. In Variables:

    1. Create an application level runtime prompt using a member variable named: "Reporting_Currency" for the Currencies dimension, and enter a default value of "EUR Reporting."

    2. Create an application level runtime prompt using a member variable named: "Input Currencies" for the Currencies dimension, and enter a default value of "EUR."

    3. Create a business rule in this Planning application, and drag and drop the Currency Conversion system template into the business rule's flow chart.

      The Currency Conversion Wizard is displayed.

  6. In the Currency Conversion Wizard, perform these steps:

    1. Step1 – Exchange Rate Option

      • In what dimension is your currency? Click the dropdown, and select the Currencies dimension you created above.

      • Select the reporting currency: Click Select button, select Variable, and select the Reporting_Currency variable.

        In this example, we are using a variable for the reporting currency so that the rule can be launched in Planning for each reporting currency.

      • Select the parent member that contains your currency members: Click Select button, and select the Input Currencies parent member.

        The reason that you need to select a parent for the currency members is so that if a currency is added in the future, then this business rule only needs to be redeployed and launched again in Planning. The script of the template will add the new currency to the script, and calculate the reporting currency correctly.

      • What exchange rate (currency) is used for this reporting currency? Click Select button, and select the Input Currencies variable.

        In this example, we are using a variable for the reporting currency so that the rule can be launched in Planning for each reporting currency.

      • Do you want to use the account types for currency conversion? Using the dropdown, select Yes.

        If you select No, you are prompted later in the steps to specify which accounts use Average/Ending.

      • Do you want to calculate Average exchange rates?

        Using the dropdown, select Calculate Average.

      • Do you want to calculate Ending exchange rates?

        Using the dropdown, select Calculate Ending.

    2. Step 2 – POV

      Enter the following for each dimension:

      • Period—Leave period empty so that it will write to all periods

      • Year—“FY12”

      • Scenario—“Current”

      • Version—“BU Version_1”

      • Entity—@Relative(“South”,0)

      • Products—@Relative(“Tennis”,0),@Relative(“Golf”,0)

    3. Step 3 — FX_Average

      • Select members containing the average exchange rates:

        • Account—“FX_Average”

        • Period

        • Year

        • Scenario

        • Version

        • Entity—“Company Assumptions”

        • Products—“000”

      • Leave the Period dimension empty, so that it will use the exchange rate for each period.

      • Leave Year, Scenario, and Version empty, so that they will use the members from the POV input for these dimensions.

    4. Step 4 – FX_Ending

      • Select members containing the average exchange rates:

        • Account—“FX_Average”

        • Period

        • Year

        • Scenario

        • Version

        • Entity—“Company Assumptions”

        • Products—“000”

      • Leave the Period dimension empty, so that it will use the exchange rate for each period.

      • Leave Year, Scenario, and Version empty, so that they will use the members from the POV input for these dimensions.

  7. Save, validate, and deploy the business rule to Planning.

    The application in this example contains the following data:

    Table 4-8 Example Data Part 1

    N/A N/A N/A FY12 Current BU Version_1
    N/A N/A Tennessee Tennessee Florida Florida
    N/A N/A Jan Jan Jan Jan
    N/A N/A G400 G100 G400 G100
    Gross Sales USD 10750 13450 9500 9699
    Gross Sales CAD 10450 10000 14450 13000
    Gross Sales EUR 40000 41450 42450 65000
    Gross Sales GBP 13250 16750 172000 16300
    Gross Sales INR 750000 750000 750000 750000
    Gross Sales US Reporting #Missing #Missing #Missing #Missing
    Gross Sales CAN Reporting #Missing #Missing #Missing #Missing
    Gross Sales EUR Reporting #Missing #Missing #Missing #Missing
    Salaries USD 5000 5000 5000 5000
    Salaries CAD 4500 4500 4500 4500
    Salaries EUR 5500 5500 5500 5705
    Salaries GBP 1200 1200 1200 1200
    Salaries INR 100000 100,000 100,000 100,000
    Salaries US Reporting #Missing #Missing #Missing #Missing
    Salaries CAN Reporting #Missing #Missing #Missing #Missing
    Salaries EUR Reporting #Missing #Missing #Missing #Missing

    Notice that none of the reporting currencies in this table have values.

  8. Launch the rule in Planning. For the Reporting Currency variable, enter EUR Reporting, and for the input currency enter EUR. Click Launch.

  9. For each account in Planning, verify that Eur Reporting now shows the total account in the Euro:

    Table 4-9 Example Data Part 2

    N/A N/A N/A FY12 Current BU Version_1
    N/A N/A Tennessee Tennessee Florida Florida
    N/A N/A Jan Jan Jan Jan
    N/A N/A G400 G100 G400 G100
    Gross Sales USD 10750 13450 9500 9699
    Gross Sales CAD 10450 10000 14450 13000
    Gross Sales EUR 40000 41450 42450 65000
    Gross Sales GBP 13250 16750 172000 16300
    Gross Sales INR 750000 750000 750000 750000
    Gross Sales US Reporting #Missing #Missing #Missing #Missing
    Gross Sales CAN Reporting #Missing #Missing #Missing #Missing
    Gross Sales EUR Reporting 52161391 52168683 52170600 52190796.69
    Salaries USD 5000 5000 5000 5000
    Salaries CAD 4500 4500 4500 4500
    Salaries EUR 5500 5500 5500 5705
    Salaries GBP 1200 1200 1200 1200
    Salaries INR 100000 100000 100000 100000
    Salaries US Reporting #Missing #Missing #Missing #Missing
    Salaries CAN Reporting #Missing #Missing #Missing #Missing
    Salaries EUR Reporting 6963318 6963318 6963318 6965471.887

  10. In Planning, you can launch the business rule as many times as you need to for each reporting currency. You can change the variable inputs each time and enter the desired Reporting Currency and its corresponding exchange rate name for the variables. and for each reporting currency available in the application.

    In this example, you could run the rule two more times, once for US Reporting with USD, and then for CAN Reporting with CAD.