Consolidated Currency Translation with Management Reporter GP Legacy Provider

Section 1: Dynamics GP and Currency Translation Setup


A. Functional Currency

B. Create Exchange Tables and Enter Rates

C. Multicurrency Access

D. Multicurrency Setup

E. Rate Types

F. Translation Type for Accounts



Section 2: Management Reporter Consolidated Report


A. Create Report

B. Translation Types


Section 1:

Dynamics GP and Currency Translation Setup


A. Functional Currency



Confirm the functional currency in both companies.



Microsoft Dynamics GP menu >> Tools >> Setup >> Financial >> Multicurrency





Note All remaining steps in this section must be done in the company where the conversion will happen. In this example, the CAD company will be used.


B. Create Exchange Tables and Enter Rates


Create three Exchange Tables to demonstrate the different translation types. Enter rates for each.


Microsoft Dynamics GP menu >> Tools >> Setup >> System >> Exchange Table


Create an Average Table:





Exchange Table ID: CAD-US AVG

Description: Canadian to US Average

Currency ID: Z-US$

Rate Frequency: Monthly

Rate Calculation Method: Divide

Transaction Rate Default: Previous Date (See notes on the Transaction Rate Default in this section.)

Search for Unexpired Rates: Unlimited



Click Rates:





For this example, we have entered rates and dates where it will be easy to see the translation. Rates and dates will vary in your system. Be aware that you must enter valid date ranges for Management Reporter to notice the rates for a given period.



Also be aware that Dynamics GP assumes that you are entering rates to go from your reporting or originating currency back to your functional currency. In this example, the rate is from CAD back to US. In Management Reporter, the rate will seem like it is multiplying because it is going from US to CAD.



Create a Current Table:




Exchange Table ID: CAD-US CUR

Description: Canadian to US Current

Currency ID: Z-US$

Rate Frequency: Monthly

Rate Calculation Method: Divide

Transaction Rate Default: Previous Date

Search for Unexpired Rates: Unlimited



Click Rates:




Create a Historical Table:






Exchange Table ID: CAD-US HIST

Description: Canadian to US Historical

Currency ID: Z-US$

Rate Frequency: Monthly

Rate Calculation Method: Divide

Transaction Rate Default: Previous Date

Search for Unexpired Rates: Unlimited


Click Rates:




Transaction Rate Default Notes



Select a Transaction Rate Default option to determine the exchange rate that will be used when multicurrency transactions are entered. Every time that you enter a transaction, a currency must be selected. If there is not an exchange rate for the transaction date, the option selected here will be used to select the exchange rate.



Exact Date Select if you want the default exchange rate only to be an exchange rate with the same date as the transaction date. If there is no exchange rate for the transaction date, there will be no default exchange rate.



Previous Date Select if you want the default exchange rate to be the rate for the closest previous date, if no rate exists for the transaction date. If an exchange rate exists for the transaction date, that rate will be used as the default exchange rate. You will also have to enter the number of previous days that you want to search for an unexpired exchange rate.



If you have selected to use a previous date as the transaction rate default and there are no unexpired rates for a previous date within the number of days that you have specified as a search limit, the closest future date will appear as the default exchange rate.



Next Date Select if you want the default exchange rate to be the rate for the closest date after the transaction date, if no rate exists for the transaction date. If an exchange rate exists for the transaction date, that rate will be used as the default exchange rate. You will also have to enter the number of previous days that you want to search for an unexpired exchange rate.



If you have selected to use the next date as the transaction rate default and there is not one, then the closest unexpired rate prior to the transaction date will appear as the default exchange rate. Only the number of days that you have specified to search will be used to determine an unexpired previous rate.



Note The Previous Date setting is recommended as that is how FRx Currency Translator functions.



C. Multicurrency Access



Grant the company access to the exchange tables for each currency.



Microsoft Dynamics GP menu >> Tools >> Setup >> System >> Multicurrency Access






D. Multicurrency Setup


Set up the Multicurrency Setup window.


Microsoft Dynamics GP menu >> Tools >> Setup >> Financial >> Multicurrency




Reporting Currency: Z-US$

Exchange Rate: 1.0000000

Rate Calculation Method: Divide

Currency ID Z-US$:

Default Transaction Rate Types:

Financial: AVERAGE

Sales: SELL

Purchasing: BUY




E. Rate Types



Configure the rate types for the exchange tables.



Microsoft Dynamics GP menu >> Tools >> Setup >> Financial >> Rate Types





If the AVERAGE, BUY, or SELL rate types are not in the Available Rate Types list, then they are assigned to other exchange tables. Unassigning them from those tables will let you assign them to the new tables.



F. Translation Type for Accounts



Set the Currency Translation Type for the accounts that you want to translate. This setting controls whether an account will use an Average, Current, or Historical translation. The setting works with the Multicurrency Setup window (from step D) to determine which exchange table to use.



Cards >> Financial >> Account





Section 2:

Management Reporter Consolidated Report


A. Create Report


This section assumes that both companies have the same chart of accounts. This section also assumes that the segment descriptions are the same for both companies. It is okay if the accounts and descriptions are different; it just requires more design work and is not covered in this document.



1. Create a row that pulls in the desired accounts.



File >> New >> Row



2. Create a tree that links to both companies. Dimensions are not required unless you want to break out the data.


File >> New >> Tree




3. Create a column that handles the translation and breaks each company into its own column. In this example, there is a CAD Translated and a CAD Non-Translated column to show the translation in action. The only cell that tells Management Reporter to translate is the Currency Source cell.



File >> New >> Column





4. Create a report definition.


File >> New >> Report Definition







5. Generate the report and note the translated results.



Here are some things to note:



1. The first column is $1,000 posted in USD and is a non-translated amount.

2. The second column is the translated amounts for the CAD company.

3. The third column is $1,000 posted in CAD in the CAD company and is a non-translated amount.




B. Translation Types

Here is how each translation type works with Dynamics GP and Management Reporter.



Current – Current is an exchange rate based on each column date, which comes from the Report Date, and the column or row modifiers. Each column will use an individual rate. This translation type is typically used with balance sheet accounts and a YTD column. 


Average – Average is a single exchange rate for each period. Management Reporter does not actually calculate an average rate as it is expected that people will enter the average for the period into the exchange table. Each period is calculated at the average rate for that period and any YTD results are summed from the period totals. This translation type is typically used with income statement accounts.



Historical – Historical is an exchange rate based on the transaction date. The transaction date is used to find the rate for that time period and that is the rate used for the translation. This translation type is typically used with non-monetary assets, such as inventory, fixed assets, long term liabilities, or equity / retained earnings.



C. Entering translated beginning balance information

You can use the Currency Translation window to translate beginning balance information for average and historical currency translation types. You also can enter translated beginning balance amounts for a new translation currency or correct translated beginning balance amounts. You don’t translate beginning balances for the current translation type because the current translation type uses the report date for the exchange rate.

After you set up a translation, beginning balances along with translated beginning balances for the translation currencies are created by closing a year.



To enter translated beginning balance information:

1. Open the Currency Translation window. (Microsoft Dynamics GP menu >> Tools >> Routines >> Financial >> Currency Translation)
   
   
   
2. Select the Year. Only the available open and historical years are available.
3. Select the Currency ID. Only the currencies that you selected as translation currencies in the Multicurrency Setup window are available for the company you are using.
4. Select the Currency Translation Type.
   
   Accounts with beginning balances that match the currency translation type display in the scrolling window along with any previously entered translated amounts. If you are using International Financial Reporting Standards (IFRS) ledgers, each account/reporting ledger combination displays as a separate row in the scrolling window.
5. To update multiple accounts with a single exchange rate, enter the exchange rate that will be used when calculating the translated debit or credit amount.
6. Mark the check box by the account numbers in the scrolling window that you want to translate beginning balances for.
7. Click Calculate to calculate the translated amounts for the selected accounts using the exchange rate.
8. You can enter a different exchange rate for an account in the scrolling window to translate amounts. The exchange rates entered in the scrolling window will not be used if you choose Calculate. You also can enter translated amounts.
9. Click Save to the translated balances.