Currency Conversion Tables
Currency conversion tables let you take source data from different currencies in a model and convert it into a single currency for aggregation.
Currency conversion tables are defined independently of models; you can then apply a selected table to any model you create. Currency tables are listed on a separate Currency Conversion page, where you can add, copy, and delete tables as required.
A currency conversion table defines exchange rates for all currencies that are in use. The currency conversion is calculated as a source currency multiplied by an exchange rate to determine the equivalent value in a target currency.
Each row in a currency conversion table represents a rate, and has the following settings:
Mandatory Settings
-
Source Currency: The three-letter currency code of the source currency. For example, EUR or USD.
-
Valid From: This is the start date when the rate becomes effective. For example, if the latest rate is set for January 1, 2020, the 2020 data will use this rate, but data for 2019 would use older rates. If there are no rates with Valid From dates that precede the data, it won't be converted.
-
Target Currency: The three-letter currency code of the target currency.
-
Exchange Rate: The source value is multiplied by this rate to convert it into the target currency.
NoteCurrency conversions can be inverted. For example, if you have a rate for converting USD to CAD, the inverse rate can be used to convert CAD to USD in case an explicit rate doesn't exist. However, rates won't be automatically combined to perform indirect conversion across three or more currencies (for example, first CAD to EUR, and then EUR to USD). -
Rate Type: This setting corresponds to the Rate Type property of an Account dimension. Use it to set different conversion rates for different types of accounts.
When you enable currency conversion in a model, a Rate Type column is automatically added to the Account dimension. You’ll have to set a rate type that corresponds to the account type before you can save the model. INC and EXP accounts use the Average rate, that is, the average conversion rate over a time period. That's because they include transactions that take place over the course of the period. AST and LEQ accounts represent what your organization owns and what it owes at the end of the period. They use the Closing rate, that is, the exchange rate at the end of the period.CautionTo convert an account, its rate type must match this setting in one of the valid rates. A blank rate type in the currency conversion table won't match either Closing or Average rate types in your account dimension.
If you are using an older model, which was created before Rate Types were introduced in the Modeler, you'll first need to migrate the model before you enable currency conversion. In this case, an additional Migrate option is available in the Model Preferences. Select this option to start the automatic migration procedure.
Optional Settings
You can use optional settings to maintain specific rates for different categories and versions of data. These settings can help you compare scenarios with different exchange rate forecasts, for example.
-
Category: If you want to create separate rates for one or more categories (such as actuals, budget, forecast, and so on), select a category for the rates. Leave this setting blank to create a generic conversion rate. Generic rates will apply if there aren't any rates for the specific category of the data.
-
Rate Version: You can create a rate for specific versions and scenarios using rate versions. Set the Category to Specific and then type a name for the rate version. You can assign public versions to a rate version in the Modeler, overriding the rate for the category. Users can also apply these rates when creating versions or currency conversions in a story.NoteThese versions and calculations will only use rates from the rate version. Rates without a Category setting won't apply.
You can create a currency conversion table by copying and pasting data from a file, or by importing data from a data source. For details on how to import and schedule currency conversion data from a data source, see Importing Currency Conversion Data.
To create a currency conversion table by copying and pasting:
- Select .
- Select New Currency Conversion Table.
- Select New Currency Conversion Table.
- Provide a name for your table, and select Create.
- Type currency conversion data into the table, or copy and paste data from a file such as a spreadsheet.
- Save your new table.