Dynamic Planning How to Use Currency Exchange Rates

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 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 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.

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.

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 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.

Define and Run a Map that Copies the Exchange Rates from HACPM_ExchangeRates to Your Model

The map defines the rules for connecting your model to the exchange rates model, HACPM_ExchangeRates.

You must include at least one row in the map for each of the dimensions in HACPM_ExchangeRates and rows for any additional dimensions in your model.

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.

The Target Model is your model.

The Type of map must be set to Data.

The Source Model is HACPM_ExchangeRates.

The Transfer option is Leaf.

There is no option to write-back data from your model.

Under Source Dimension, list the four dimensions in HACPM_ExchangeRates and decide where to use them:

• Scenario must map to an equivalent Scenario dimension in your model.
• Time must map to an equivalent Time dimension in your model.
• Currency can be mapped to a dimension (if you plan to use multiple currencies), or to a member (if you plan to take one exchange rate at a time), or can be filtered out (if you want only one currency).
• CurrencyType can be mapped to a dimension (if you plan to use multiple currency types), or to a member (if you plan to take one currency type at a time), or can be filtered out (if you want only one currency type).

The examples below show ways to design the map.

• Example Map with Variables and Currency Dimension
• Example Map for a Model that Uses Multiple Currencies
• Example Map with Currency Mapped to One Member
• Example Map with Currency Filtered Out

You then run the map by creating a calculation that executes the map. See Define and Run a Calculation that Runs the Map and Executes the Currency Formula.

Example Map with Variables and Currency Dimension

This example shows how to use variables for Scenario and Time, plus multiple currencies in your model.

The following map uses a target model with 5 dimensions:

• Scenario: contains Actual and Budget members for 2016.
• Time: includes years, quarters, and months for 2016, 2017, and 2018.
• Account: includes revenue and expense accounts.
• Reporting: includes common currency, local currency, and exchange rate members.
• Currency: includes a list of currencies to be used, copied from HACPM_ExchangeRates.

Scenario and Time are variables that can be set prior to running the calculation.

Currency in HACPM_ExchangeRates is mapped to Currency in the target model, and one currency is mapped. You could add rows for multiple currencies.

CurrencyType in HACPM_ExchangeRates is filtered to not appear in the target model. The AVG member from the source model is the one that will be copied into the intersection of all the other dimensions listed in the map.

With Scenario set to 2016 Budget and Time set to 2016, after loading this data, it appears in the model as follows:

Example Map for a Model that Uses Multiple Currencies

If you plan to use multiple currencies in your model, the best practice is to create a Currency dimension and include all the currencies you plan to use. If you use the same member names as are in HACPM_ExchangeRates, the map is easier to create.

The following map uses a target model with 7 dimensions:

• Scenario: contains Actual, and Budget and Forecast members for 2016 and 2017.
• Time: includes years and quarters only, no months.
• Currencies: includes the same list of currencies as are in HACPM_ExchangeRates.
• Dim1 - Dim4: miscellaneous dimensions for your reporting needs.

Scenario and Time are mapped one-to-one.

Currency in HACPM_ExchangeRates is mapped to Currencies in the target model.

CurrencyType in HACPM_ExchangeRates is filtered to not appear in the target model. The AVG member from the source model is the one that will be copied into the intersection of all the other dimensions listed in the map.

After loading this data, it appears in the model as follows. In this case, you can see that USD is the local currency because it has an exchange rate of 1.

Example Map with Currency Mapped to One Member

If you plan to use a small number of currencies in your model, you do not have to create a Currency dimension in your model. For example, if you are newly expanding to only one international market, you only need to have one member to store the new local currency.

The following map uses a target model with 6 dimensions:

• Scenario: contains Actual, and Budget and Forecast members for 2016 and 2017.
• Time: includes years and quarters only, no months.
• Dim1 - Dim4: miscellaneous dimensions for your reporting needs.

Scenario and Time are mapped one-to-one.

A particular Currency in HACPM_ExchangeRates is mapped to one member in the target model.

CurrencyType in HACPM_ExchangeRates is filtered to not appear in the target model. The AVG member from the source model is the one that will be copied into the intersection of all the other dimensions listed in the map.

After loading this data, it appears in the model as follows:

Example Map with Currency Filtered Out

If you plan to use a only one other currency in your model, you can filter out the Currency dimension entirely.
The following map uses a target model with 6 dimensions:

• Scenario: contains Actual, and Budget and Forecast members for 2016 and 2017.
• Time: includes years and quarters only, no months.
• Dim1 - Dim4: miscellaneous dimensions for your reporting needs.

Scenario and Time are mapped one-to-one.

A particular Currency in HACPM_ExchangeRates is filtered to not appear in the target model. The EUR member from the source model is the one that will be copied into the intersection of all the other dimensions listed in the map.

CurrencyType in HACPM_ExchangeRates is filtered to not appear in the target model. The AVG member from the source model is the one that will be copied into the intersection of all the other dimensions listed in the map.

After loading this data, it appears in the model as follows:

Define a Formula that Calculates the Common Currency

Once you have the exchange rate(s) in your model, you can multiply all foreign values by the exchange rate to arrive at the equivalent in your local or common currency.

1. Go to Model, Formula.
2. Select the model and dimension that contains the values in foreign currency, local currency, and the exchange rate.
3. Give the formula a name.
4. Specify member selections or variables for all the other dimensions in the model under the Dimension column.
5. Specify the members that are to be included in the formula, then enter the formula in column B.

CC (common currency) = LC (local currency) * Exg Rates

Define and Run a Calculation that Runs the Map and Executes the Currency Formula

After you have created the data map and created a formula to use the exchange rate, create a calculation that runs the map and formula.

1. Go to Model, Calculation.
2. Select the model to receive the exchange rate.
3. Give the calculation a name.
4. Select Map under Type, and enter the name of the data map.
5. Select Formula under Type, and enter the name of the formula.
6. Select the Variables column and click Variables Manager to specify the Scenario and Time members to receive.

You can also add a row to download the exchange rates. This ensures that your model always has the latest exchange rate data from Planful applications.

After running this calculation, data appears in the model as follows. The raw data was 100 Euro. The Common Currency member is locked since it is a calculated member.

Add the Calculation to the Views and Reports that Display Currency Data (Optional)

You have the option to add the calculation to views and reports so that each time the user clicks Save, the exchange rates are used to recalculate the common currency member(s).

1. Go to Analyze or Report.
2. Select the view or report that contains the data in local currency and common currency.
3. Select Design View or Design Report.
4. Click Properties.
5. Add the calculation to the Calculation on Save property and click Save.

Example View Properties:

Example Report Properties: