When you are adding a new data source to your database, it may include transactions that are recorded in a new currency. Or you might be managing multiple legal entities, each of which has it's own currency. As a result, you need to maintain exchange rates. Datarails makes that easy.

Try it now: Exchange rate management

1. Download the Excel file "Company Three Transactions" here. If the link opens in Google Sheets, go to File --> Download --> Microsoft Excel to download the Excel version of the file. It includes the transactions of a company operating in Canada with the currency CAD for Canadian Dollars. The reporting currency is US Dollars.
2. Access your Datarails practice environment (click here for instructions)
3. Navigate to the Actuals folder, create a filebox for "Company Three" and upload the practice file from Step #1.
4. Next, apply the Data Mapper from "Company One" to "Company Three" as we have done in the previous exercises. Remember to break the connection between the Data Mappers before proceeding, as shown in this video:
5. Remove and reapply the Header to ensure we are picking up all relevant columns.
6. Change the Custom Columns "Entity" to "Company Three" and "Posting Currency" to "CAD"
7. Click Publish & Scan to save the Data Mapper
8. Make sure you are logged into the practice environment via the Datarails add-in. Refer to points 5-8 of the instructions here.
9. Next, we need to configure the exchange rates. Exit the Data Mapper by clicking on the Datarails logo in the top-left corner, navigate to Configuration, then Lookups and click on FX Rates.
10. Click on the Download button that appears when you hover over FX Rates and open the downloaded Excel file.
11. Filter the column "Posting Currency" to show only "CAD". Enter "USD" as the reporting currency for each row. For the sake of this exercise, change both the Currency EOP (End-of-period) rate and the Currency AVG (average) rate to 1.3
12. In the Excel file, navigate to the Datarails ribbon and click Submit (note: if you can't find the Submit button and you see a Connect  button instead, it means you are not connected properly. Follow step 8 and click "Refresh" to see the Submit button).
13. Let's confirm that the FX rates are correctly picked up by the system. Navigate back to the Data Mapper of Company Three and scroll to the right until you find a green column called "Reporting Currency". It should say "USD" and apply the exchange rate conversion in column "Currency EOP Rate".

Note: The end-of-period rate ("Currency EOP rate") is used for Balance Sheet reporting, while the average rate ("Currency AVG Rate") is used for the Income Statement. 

Lookup Settings

1. Let's review the Lookup Settings to help you get a better understanding of how the FX rate lookup works behind the scenes.
2. Click on the three dots next to FX Rates and select Edit Lookup Setting. You will see the following screen:
3. Table Name is the name of the database. You need to make sure that you are linking the lookup to the same database where you mapped your financial transactions. Financials is the default name. If you are unsure, you can check the Table Name by opening the Data Mapper of your financial transactions and checking the top left corner.
4. Sheet Name is simply the name of the sheet where the lookup is located. This is relevant if you are uploading an Excel spreadsheet with FX rates that has multiple tabs.
5. Key Length refers to how many columns are used for creating the Unique Identifier key. It's the key needed to link one table to another one. In this case, the Key Length is 2, which means the two left-most columns will be used as a lookup key. 
6. Key Name refers to the two columns that are used as a lookup key. In this case, it's "Reporting Month" and "Posting Currency".
7. Rescan Option should remain set to "latest in time dimension" so that the most recent update will be considered. 
8. If you'd like to make changes to the Lookup configuration, you can use the pencil icon under Actions. A typical use case for making changes is if you have different entities that each use different exchange rates for the same currencies. In that case, you would add "Entity" as the third column from the left, and change Key Length to 3. That way, the lookup key includes the Entity name in addition to Reporting Month and Posting Currency.

Next: Take the Data Mapper Quiz to receive the certificate

Previous: Mapping a forecast

Updated June 30, 2023 18:19