Using Currency Exchange Rates in Dynamic Planning

Dynamic Planning supports downloading currency exchange rates from Planful Structured Planning, Consolidation, and Reporting applications. Since currency exchange rates fluctuate over time, it is best to maintain them in only one place. Many companies use the exchange-rate feature in Planful applications and would like to use the same rates in Dynamic Planning without re-keying them.

Ensure that you have exchange rates defined in Structured Planning, Consolidation, and Reporting applications. Go to Settings, Currency, Exchange Rates.

Here is the basic process in Dynamic Planning to use exchange rates from Planful Structured Planning, Consolidation, and Reporting applications:

• Define a Scope on HACPM_Financial that sets up variables for one Scenario and one Time frame (such as year).

• Define and Run a Calculation on HACPM_Financial that downloads the exchange rates based on the scope. You can download only one scenario and one time frame (such as a year) at a time. A system-defined model is created, called HACPM_ExchangeRates.

• Set Up Your Model to Use Exchange Rates This can be any type of model.

• Define and Run a Map that pulls the exchange rates from HACPM_ExchangeRates and copies them into your model.

• Define a Formula on your model that calculates the common currency as local currency X exchange rate.

• Define and Run a Calculation that runs the map and executes the currency formula.

• Optionally, Add the Calculation to Views and Reports that display the common currency data using the Calculation on the Save property.

Define a Scope on HACPM_Financial for Exchange Rate Download

The purpose of this scope is to limit the amount of exchange-rate data to be downloaded from Planful applications to the Dynamic Planning HACPM_ExchangeRates model. Only one combination of Scenario and Time is supported per exchange-rate download.

1. Login to SpotlightXL.

2. Select Model, Scope.

3. Select the model HACPM_Financial from the drop-down.

4. Give the scope a name, such as Exchange Rate Selections. Optionally, enter a description.  

5. Under Dimension, specify the Scenario dimension. Specify Filter Type as FixedMember. Under Filter Value, either specify the member name or create a variable.

6. Under Dimension, specify the Time dimension. Specify Filter Type as FixedMember or MemberAndBelow. Under Filter Value, either specify the member name or create a variable.  

7. Save the scope definition.

Define and Run a Calculation that Creates HACPM_ExchangeRates

The purpose of this calculation is to download the exchange rate data from Planful applications and create a system-defined model called HACPM_ExchangeRates.

Only one combination of Scenario and Time is supported per download, so you must use a scope to limit the volume of the download.

1. Login to SpotlightXL.

2. Select Model, Calculation.

3. Select the model HACPM_Financial from the drop-down.

4. Give the calculation a name, such as Download Exchange Rates. Optionally, enter a description.  

5. Under Type, select the option DownloadExchangeRates.

6. Under Name, enter the name of the Scope that defines the Scenario and Time dimensions you want to download.

7. Save the calculation.

Note:

DownloadExchangeRates is a calculation type that can be added to any calculation on any model. You are not required to define the calculation as part of the HACPM_Financial model as shown above. However, as a best practice, we suggest defining the calculation on HACPM_Financial so you can easily find it again.

If your Scope contains variables, they appear under the Variables column.

1. Select the Variables cell.

2. Click Variables Manager from the ribbon.

3. Use the Member Selection icons to select one member from the Scenario dimension and one member from the Time dimension, then click Update.  

4. Click Save.

5. Click Run.

If the calculation is successful, you can click the Model drop-down and see that HACPM_ExchangeRates has been created.

Set Up Your Model to Use Exchange Rates

Select the model in your Dynamic Planning application that will use the exchange rates. This should be a model that contains currency values. The model should have at least two currency members in order to benefit from using an exchange rate: one member showing values in its native currency, and the other member calculated by multiplying the native currency by the exchange rate to produce a number in a common currency.

Note:

The model can be of type Analytic or Master.

HACPM_ExchangeRates uses 4 dimensions:

• CurrencyType: contains the types of currency rates, such as AVG (average monthly), EOM (end of month), or HistAvg (historical average).

• Currency: contains the names of all the currencies, such as USD, CAD, EUR, and AUD.

• Scenario: contains the full list of possible scenarios used in Planful applications.

• Time: contains multiple years, quarters, and months.

You can view these dimensions and their members by selecting Model, Dimension, and selecting HACPM_ExchangeRates from the Model drop-down.

Your model must have equivalent dimensions to the Scenario and Time.

Your model is not required to have equivalent dimensions to Currency and Cur Type, however, if your model will support data in multiple currencies, then we recommend you create a Currency dimension.

The Maps section below provides several examples of mapping to models with and without Currency and CurType dimensions.