Foreign Currency Configuration, Reporting, and Best Practices

Purpose Configuration Sales - Importing Foreign Currency Orders Open Orders Closed Sales Orders Shipped  not invoiced Invoiced not paid Purchase - Importing Foreign Currency Orders Open Orders Closed purchase orders Received  not invoiced Invoiced not paid General ledger Imports AP and AR Transactional Data Inventory Imports Foreign currency rules Sales orders Purchase orders Journal Entries changed Best Practices Reporting in Foreign Currency Payables and Receivables Summary and Detail Reports Trial Balance, GL Summary, GL History, and Financial Statements Foreign Currency Conversions Sales Order Summary and Detail Reports Viewing Currency History Facility-Specific Currencies                                                                      Realized vs. Unrealized gain Foreign Currency Field and Calculated Field Definitions

Purpose

The main benefit of utilizing foreign currencies in Deacom is increased accuracy when doing foreign currency transactions. Once you have set up Deacom to automatically pull in currency conversion rates according to your base default currency, no more maintenance needs to be done. This will save a lot of time versus constantly updating the conversion rates in the system manually.  This means that each transaction and conversion will be accurate to the day on which it happened rather than using a rate which may have fluctuated. Using this method will also eliminate the need to do any type of conversion with item prices/costs.

Deacom also tracks the rate of each currency in the Currency History table, which makes it very easy to trace back and see what the conversion rate was at the time of the order. This eliminates any second-guessing the original pricing on an order where an unknown conversion rate was used.

 Another useful perk of using foreign currencies is reporting. Many different reports in Deacom can be published in different currencies. This is accomplished via the “Display Currency” field located on Page 2 of the pre-filter. This feature allows users to analyze report results in currencies different from the one used to process the order. The amounts on the reports are calculated and displayed according to what the exchange rate was on the order’s Invoiced Date.

This document will cover configuration, data imports, currency rules, and reporting.  

Configuration

• Set up currencies
  • Accounting>Maintenance>Currencies
    • Enter name
    • Enter Printed Description
    • Enter rate
    • Enter
    • Symbol
    • Make one of the currencies be a default
    • Check Updates Rates Automatically
      • Notes:
        • This is optional, but recommended
        • Note if you are updating rates automatically, the symbol must match what is in Help  - see Accounting Maintenance > Currency Names.
        • If using automatic updates, you need to also check this on the default currency.  If you do not check this on the default currency all conversions will be based on Euros.
• Set up Update time (if using automatic updates)
  • Accounting>Options>General Tab
    • Choose a Currency Rate Bank
      • This the bank we use to obtain the rate and perform the currency conversions.
    • Choose Currency Update
      • This is the time we will bring in the new rate
• Putting a currency on an account
  • Navigate to Accounting>Maintenance>Chart of Accounts
  • Highlight the account you wish to be in a different currency
  • Click modify
  • Enter the currency
  • Click save and exit
  • Note:
    • If the account is in the base currency, you should not enter a currency
    • Once transactions have occurred on an account (including data imports) the currency on the account/facility cannot be changed manually.  It requires a data profile to change the currency.

Sales - Importing Foreign Currency Orders

Open Orders

• to_totdue and to_balance = foreign currency amount.   (not home currency)
• or_price and or_exten for lines should be the foreign currency amount
• to_fcid = foreign currency id
• to_fcrate = Currency rate as of ordered date

Closed Sales Orders

• Rates don’t really matter because it doesn’t hit the gl  just used for info purposes
• Import with same to_fcrate and to_fcrate2 (order vs invoice). Orders should be imported based on the invoiced date. To_fcid = foreign currency id, to_fcrate and to_fcrate2 should be the rate as of invoice date.
• to_totdue = foreign currency amount.   (not home currency)
• or_price and or_exten for lines should be the foreign currency amount

Shipped  not invoiced

• to_fcrate should be the rate as of the ordered date   to_fcrate2 should be 0.  (to_fcrate2 will populated when invoiced.)

Invoiced not paid

• Import with same to_fcrate and to_fcrate2 (order vs invoice).  Orders should be imported based on the invoiced date. (if customer wishes and can provide, the to_fcrate could be as of order date)

Purchase - Importing Foreign Currency Orders

Open Orders

• tp__totdue and tp_balance = foreign currency amount.   (not home currency)
• pu_price and pu_exten for lines should be the foreign currency amount
• tp_fcid = foreign currency id
• tp_fcrate = Currency rate as of ordered date

Closed purchase orders

• Rates don’t really matter because it doesn’t hit the gl  just used for info purposes
• Import with same tp_fcrate and tp_fcrate2 (order vs invoice).   Orders should be imported based on the invoice date. tp_fcid = foreign currency id , tp_fcrate and tp_fcrate2 should be rate as of invoice date.
• tp_totdue = foreign currency amount.   (not home currency)
• pu_price and pu_exten for lines should be the foreign currency amount

Received  not invoiced

• tp_fcrate should be the rate as of the order date   tp_fcrate2 should be 0.  (tp_fcrate2 will populated when invoiced.)

Invoiced not paid

• Import with same tp_fcrate and tp_fcrate2 (order vs invoice).  Orders should be imported based on the invoice date. (if customer wishes and can provide, the tp_fcrate could be as of order date)

General ledger Imports

There must be only one currency in the account when importing. Example you cannot import USD and CAD transactions into the same account.  

• Gl_debits*gl_fcrate = gl_fcdebits  must equal!
• Gl_credits*gl_fcrate = gl_fccredits  must equal!

If currency on the account is empty, gl currency can be any currency

• Gl_debits*gl_fcrate = gl_fcdebits  must equal!
• Gl_credits*gl_fcrate = gl_fccredits  must equal!

If gl fc_id = base/home which is usually 1, fc_rate must be 1

• We can calculate fc_rate if not given; need value in base and FC
• Gl_debits = home currency; gl_credits = home currency
• Gl_fcdebits = foreign currency; gl_fc credits = foreign currency

AP and AR Transactional Data

When customers provide transactional data, open AP and open AR, they need to supply the following information:

• Debits
• Credits
• Rate
• FX debits
• FX credits
• Regardless of the COA Currency (ch_fcid), GL transactions that are in a non-Default Currency will have  gl_debits/credits in the Default Currency, with Currency Rate filled in (gl_frcate), and gl_fcdebits/fccredits in non-Default Currency.
• If currency on the account is not the base currency, all gl records need to be only in that currency.  Gl_fcid must equal ch_fcid

Inventory Imports

Inventory must be imported in the same currency as the facility.

• No currency rate is needed; value must be in foreign currency

Foreign currency rules

Sales orders

• Order entry
  • Uses the currency rate (to_fcrate)   rate at the time of order entry.
• Order Invoicing
  • Uses the  exchange rate at the time of invoicing.  This is to_fcrate2
• Payment
  • Uses the exchange rate at the time of payment

Purchase orders

• Order entry
  • Uses the currency rate (tp_fcrate)   rate at the time of order entry.
• Order Invoicing
  • Uses the  exchange rate at the time of invoicing.  This is tp_fcrate2
• Payment
  • Uses the exchange rate at the time of payment

Journal Entries changed

• Uses the currency on the account and not the currency on the header
• Rate will default using the exchange rate on the date on the header
  • Rate can be changed manually if needed
• All lines will have the same rate.  – revisit after ticket is completed.

Best Practices

Deacom has a few suggestions as best practices for companies transacting and/or reporting in foreign currencies. Below is a guideline for companies that wish to use the foreign currency functionality in Deacom: 

• Ensure an unrealized gain account is setup and assigned on AP, AR, and Cash accounts that are in a currency different from the company’s home currency.
  • The unrealized gain account will be used when setting up Financial Statements for Foreign Currency Accounts.
  • Note there can be a different unrealized gain account by AP, AR and Cash account.
• Understand how to setup Monetary vs Nonmonetary Accounts
  • Monetary Assets and Liabilities: Monetary assets and liabilities are assets and liabilities where amounts fluctuate based on exchange rates.
    • Examples are cash, short- or long-term accounts and notes receivable in cash, and short- or long-term accounts and notes payable in cash.
    • Foreign currency monetary assets and liabilities should be measured at the end of each reporting period using the exchange rate at that date. The offsetting entry should generally be recorded in the income statement as a foreign currency transaction gain or loss.
  • Nonmonetary Assets and Liabilities:
    • Examples are inventories; investments in common stocks; property, plant, and equipment; and liabilities for rent collected in advance.
    • Nonmonetary assets and liabilities are not subsequently remeasured. Once purchased or incurred, nonmonetary assets and liabilities are accounted for in the functional currency of the purchaser.
• Separate AP, AR, and Cash accounts for different currencies is required.
  • This enables reconciliation at month end close
• Ensure currency rates are updated regularly
  • Currency rates can automatically be updated daily through the Deacom Automation Service.
• The Trial Balance, GL Summary, GL History, and Financial Statement reports leverage the "Currency" and "Unrealized Gain" account fields established in the Chart of Accounts.
  • The difference in value for each posting at the time it was made and its value at the current exchange rate is automatically added to the "Unrealized Gain" account specified for that account The Unrealized Gain is virtual balance that doesn't really exist in the GL.
  • Each Unrealized Gain account should be included in the Financial Statements so that gains and losses from transactions are properly captured.
  • Note: Beginning in version 17.02.013, the system logic was enhanced. When posting to an unrealized gain account the system will first try to use the one specified on the cash account(ch_currgain), if empty the system will look to the gain/loss account on the Currency (fc_currgain), if also empty it will use gain/loss account from system options(df_currgain).

Reporting in Foreign Currency

Payables and Receivables Summary and Detail Reports

Between the time of receipt and the time of payment for a Purchase Order created in foreign currency, there is an unrealized gain/loss on the transaction. The parallel to this is the unrealized gain/loss that occurs between the time a foreign currency Sales Order is shipped and the time it is paid. The "currdiff" column in the Payables and Receivables Summary and Detail reports in Accounting Reporting displays the unrealized currency gain/loss. This ensures that the ledger and sub-ledger consistently stay in balance for foreign currency transactions.

Trial Balance, GL Summary, GL History, and Financial Statements

 The Trial Balance, GL Summary, GL History, and Financial Statement reports leverage the "Currency" and "Unrealized Gain" account fields established in the Chart of Accounts. The difference in value for each posting at the time it was made and its value at the current exchange rate is automatically added to the "Unrealized Gain" account specified for that account to show a virtual balance that doesn't really exist in the GL. Each Unrealized Gain account should be included in the Financial Statements so that gains and losses from transactions are properly captured. Note: Beginning in version 17.02.013, the system logic was enhanced. When posting to an unrealized gain account the system will first try to use the one specified on the cash account(ch_currgain), if empty the system will look to the gain/loss account on the Currency (fc_currgain), if also empty it will use gain/loss account from system options(df_currgain).

When running the "GL Detail", "GL Summary", or "Trial Balance" reports in Accounting > Accounting Reporting, the system will use the exchange rate on the posting date(s) of the various transactions in the report when converting home currency postings to the requested foreign currency. For more information on generating Accounting reports, refer to Accounting and Finance Reporting.

Notes: 

• When running the GL Summary report by "End Date," the starting column is calculated using the currency rate as of the COB the day before the starting date, the debits and credits column are calculated using the currency rate as of the transaction date, and the ending column is calculated using the currency rate as of the COB of the ending date.
• Running the GL or Trial Balance reports by End Date will not float foreign currency accounts that do not have an unrealized gain/loss account linked to them. This supports accounting best practice methods.

Foreign Currency Conversions

Report	Account	Transaction	Account Gain/Loss	Balances	Calculation
Base	Base (or none)	Base	Zero	End Date	gl_debits - gl_credits
Base	Base (or none)	Base	Zero	Transaction Date	gl_debits - gl_credits
Base	Base (or none)	Base	Non-zero	End Date	gl_debits - gl_credits
Base	Base (or none)	Base	Non-zero	Transaction Date	gl_debits - gl_credits
Base	Foreign	Base	Zero	End Date	gl_debits - gl_credits
Base	Foreign	Base	Zero	Transaction Date	gl_debits - gl_credits
Base	Foreign	Base	Non-zero	End Date	(gl_fcdebits - gl_fccredits) / fc_rate of gl_fcid as of end date of report
Base	Foreign	Base	Non-zero	Transaction Date	gl_debits - gl_credits
Base	Base (or none)	Foreign	Zero	End Date	gl_debits - gl_credits
Base	Base (or none)	Foreign	Zero	Transaction Date	gl_debits - gl_credits
Base	Base (or none)	Foreign	Non-zero	End Date	gl_debits - gl_credits
Base	Base (or none)	Foreign	Non-zero	Transaction Date	gl_debits - gl_credits
Base	Foreign	Foreign	Zero	End Date	gl_debits - gl_credits
Base	Foreign	Foreign	Zero	Transaction Date	gl_debits - gl_credits
Base	Foreign	Foreign	Non-zero	End Date	(gl_fcdebits - gl_fccredits) / fc_rate of gl_fcid as of end date of report
Base	Foreign	Foreign	Non-zero	Transaction Date	gl_debits - gl_credits
Foreign	Base (or none)	Base	Zero	End Date	(gl_debits - gl_credits) X gl_fcid rate as of gl_date
Foreign	Base (or none)	Base	Zero	Transaction Date	(gl_debits - gl_credits) X report currency rate as of gl_date
Foreign	Base (or none)	Base	Non-zero	End Date	(gl_debits - gl_credits) X end rate of report currency
Foreign	Base (or none)	Base	Non-zero	Transaction Date	(gl_debits - gl_credits) X report currency rate as of gl_date
Foreign	Foreign - same as report	Base	Zero	End Date	gl_fcdebits - gl_fccredits
Foreign	Foreign - same as report	Base	Zero	Transaction Date	gl_fcdebits - gl_fccredits
Foreign	Foreign - same as report	Base	Non-zero	End Date	gl_fcdebits - gl_fccredits
Foreign	Foreign - same as report	Base	Non-zero	Transaction Date	gl_fcdebits - gl_fccredits
Foreign	Foreign - different from report	Base	Zero	End Date	gl_debits X end rate of report currency
Foreign	Foreign - different from report	Base	Zero	Transaction Date	(gl_debits - gl_credits) X report currency rate as of gl_date
Foreign	Foreign - different from report	Base	Non-zero	End Date	(gl_debits - gl_credits) X (1 / fc_rate of gl_fcid as of end date of report) X end rate of report currency
Foreign	Foreign - different from report	Base	Non-zero	Transaction Date	(gl_debits - gl_credits) X report currency rate as of gl_date
Foreign	Base (or none)	Foreign - same as report	Zero	End Date	gl_fcdebits - gl_fccredits
Foreign	Base (or none)	Foreign - same as report	Zero	Transaction Date	gl_fcdebits - gl_fccredits
Foreign	Base (or none)	Foreign - same as report	Non-zero	End Date	(gl_debits - gl_credits) X end rate of report currency
Foreign	Base (or none)	Foreign - same as report	Non-zero	Transaction Date	gl_fcdebits - gl_fccredits
Foreign	Base (or none)	Foreign - different from report	Zero	End Date	(gl_debits - gl_credits) X gl_fcid rate as of gl_date
Foreign	Base (or none)	Foreign - different from report	Zero	Transaction Date	(gl_debits - gl_credits) X report currency rate as of gl_date
Foreign	Base (or none)	Foreign - different from report	Non-zero	End Date	(gl_debits - gl_credits) X end rate of report currency
Foreign	Base (or none)	Foreign - different from report	Non-zero	Transaction Date	(gl_debits - gl_credits) X report currency rate as of gl_date
Foreign	Foreign - same as report	Base	Zero	End Date	gl_fcdebits - gl_fccredits
Foreign	Foreign - same as report	Base	Zero	Transaction Date	gl_fcdebits - gl_fccredits
Foreign	Foreign - same as report	Base	Non-zero	End Date	gl_fcdebits - gl_fccredits
Foreign	Foreign - same as report	Base	Non-zero	Transaction Date	gl_fcdebits - gl_fccredits
Foreign	Foreign - different from report	Base	Zero	End Date	(gl_debits - gl_credits) X gl_fcid rate as of gl_date
Foreign	Foreign - different from report	Base	Zero	Transaction Date	(gl_debits - gl_credits) X report currency rate as of gl_date
Foreign	Foreign - different from report	Base	Non-zero	End Date	(gl_debits - gl_credits) X (1 / fc_rate of gl_fcid as of end date of report) X end rate of report currency
Foreign	Foreign - different from report	Base	Non-zero	Transaction Date	(gl_debits - gl_credits) X report currency rate as of gl_date
Foreign	Foreign - same as report	Foreign - different from report	Zero	End Date	gl_fcdebits - gl_fccredits
Foreign	Foreign - same as report	Foreign - different from report	Zero	Transaction Date	gl_fcdebits - gl_fccredits
Foreign	Foreign - same as report	Foreign - different from report	Non-zero	End Date	gl_fcdebits - gl_fccredits
Foreign	Foreign - same as report	Foreign - different from report	Non-zero	Transaction Date	gl_fcdebits - gl_fccredits
Foreign	Foreign - different from report	Foreign - same as report	Zero	End Date	(gl_debits - gl_credits) X gl_fcid rate as of gl_date
Foreign	Foreign - different from report	Foreign - same as report	Zero	Transaction Date	(gl_debits - gl_credits) X report currency rate as of gl_date
Foreign	Foreign - different from report	Foreign - same as report	Non-zero	End Date	(gl_debits - gl_credits) X (1 / fc_rate of gl_fcid as of end date of report) X end rate of report currency
Foreign	Foreign - different from report	Foreign - same as report	Non-zero	Transaction Date	(gl_debits - gl_credits) X report currency rate as of gl_date
Foreign	Foreign - same as report	Foreign - same as report	Zero	End Date	gl_fcdebits - gl_fccredits
Foreign	Foreign - same as report	Foreign - same as report	Zero	Transaction Date	gl_fcdebits - gl_fccredits
Foreign	Foreign - same as report	Foreign - same as report	Non-zero	End Date	gl_fcdebits - gl_fccredits
Foreign	Foreign - same as report	Foreign - same as report	Non-zero	Transaction Date	gl_fcdebits - gl_fccredits
Foreign	Foreign - different from report	Foreign - different from report	Zero	End Date	(gl_debits - gl_credits) X gl_fcid rate as of gl_date
Foreign	Foreign - different from report	Foreign - different from report	Zero	Transaction Date	(gl_debits - gl_credits) X report currency rate as of gl_date
Foreign	Foreign - different from report	Foreign - different from report	Non-zero	End Date	(gl_debits - gl_credits) X (1 / fc_rate of gl_fcid as of end date of report) X end rate of report currency
Foreign	Foreign - different from report	Foreign - different from report	Non-zero	Transaction Date	(gl_debits - gl_credits) X report currency rate as of gl_date

Sales Order Summary and Detail Reports

 Several reports in Sales > Order Reporting, including the Sales Order Summary and Sales Order Detail reports, can be used to analyze totals in a different currency. This is accomplished via the "Display Currency" field located on the Page 2 tab of the Sales > Order Reporting pre-filter. This field allows users to specify the Currency in which to display the amounts in the report results. In situations where the display currency selected is different than the currency on the order, the reports will convert the amount on the displayed orders to the exchange rate of the display currency as of the sales order invoiced date.

Viewing Currency History

The Currency History table, accessible by navigating to Accounting > Maintenance > Currency History, allows users to see an archived list of all foreign currency rates used in the system. A pre-filter form is available to filter the historical records. The historical rates may be modified or deleted as necessary, however, this is not recommended.

Modifying or deleting historical currency records can be used to change the rate used when creating Financial Statements and Accounting Reports, such as Open A/R and A/P reports at the month end close. If a foreign currency rate does not exist on a day a report is run or a transaction is dated, the system will go back in time until a rate is established for the respective currency. Foreign currency rates are stored on Purchase Orders and Sales Orders so updating the rates will not change the rate stored as part of the transaction.

Facility-Specific Currencies                                                                     

We have added the option to set a facility-specific currency to allow inventory to be tracked in foreign currency. Currencies are maintained via Accounting > Maintenance > Currencies. This option is useful for scenarios in which different facilities may be in different countries; therefore, their home currencies would differ. Users need the ability to report in a different currency, including values for inventory, labor, burden, etc. In association with this option the following fields/features have been added in the system.

• A read-only Currency field on the Edit Facility Item > Costs tab for reference.
• Currency conversion field on BOM roll-ups and all inventory transactions that use standard cost or transfer between facilities.
• Display warning feature to alert users when posting to an account with a different currency than the transaction currency

A currency search box has been added to multiple reports in Inventory and Job Management and the reports have been enhanced to calculate in the specified currency.

Realized vs. Unrealized gain

An unrealized gain is a profit that exists on paper, resulting from a sales or purchase transaction. A gain becomes realized once the payment is received (AR) or paid (AP). Unrealized gains should only be placed on monetary accounts. 

Unrealized gains represent the difference in value for each posting at the time it was made and its value at the current exchange rate. This difference is automatically added to the "Unrealized Gain" account specified for the AP or AR account. 

The unrealized gain account is a virtual balance that doesn't really exist in the GL. It is captured in the "currdiff" field in GL Summary, GL Detail and Financial Statement reports.

Note: Beginning in version 17.02.013, the system logic was enhanced. When posting to an unrealized gain account the system will first try to use the one specified on the cash account(ch_currgain), if empty the system will look to the gain/loss account on the Currency (fc_currgain), if also empty it will use gain/loss account from system options(df_currgain).

Foreign Currency Field and Calculated Field Definitions

Field Name	Definition
TP_FCRATE/TO_FCRATE	Rate when the PO/SO was created
TP_FCRATE2/TO_FCRATE2	Rate at invoicing
GL_RATE	Rate on the date the transaction occurred
FC_RATE	This is the current rate, what you would see if you navigated to Accounting > Maintenance > Currencies