IN THIS SECTION

• Overview
• Viewing Exchange Rates when Running Reports
• Viewing and Customising Exchange Rates
  • Base Currency
  • Customising Rates
  • Adding Account Spot Rates
  • Enabling/Disabling Rates
  • Downloading FX Rates
• Reporting Configuration
• FX P&L Multi-Month Conversion Strategy
• Budgets
• Sales Reports
• FX Adjustments
• FX effects and Eliminations

Overview

This section contains Information on how Joiin performs currency conversion and how you can view and customise exchange rates and the settings that control how currency conversion works.

When you run a Joiin report and select a currency that is different from the currency of the select Companies, Joiin will convert the values to the report currency.  By default Joiin uses month end and average historical Foreign Exchange (FX) rates from Open Exchange Rates to do this.  You can change these default rates and enter your own rates - see the Viewing and Customising Exchange Rates section below for more information on how to do this.  You can also configure reports to use different rates for different account types - see the Reporting Configuration section below for more information.

NOTE that Xero uses foreign exchange rates from Xe.com which may differ slightly from the ones Joiin uses - you can modify the Joiin exchange rates to account for these differences if required.

NOTE: when running reports for futures dates Joiin will use the current FX rates.

Viewing Exchange Rates when Running Reports

When you run a report, if currency conversion has been performed, the Exchange Rates that were used can be viewed by clicking on the Fx rates button at the top right of the report results.

This shows the name of each selected company, the period of the currency conversion, the base currency of the company and the currency being converted to with the rate being used.  It also shows whether the rate was a Month End rate or a Monthly Average rates.

Viewing and Customising Exchange Rates

You can view the default exchange rates and enter your own rates using the Exchange Rates tab of the Settings page.  Click on the top-right Settings menu (cog icon) and select the Exchange Rates tab.  You must have Write access rights to access the Exchange Rates menu item.

The Month End, Monthly Average exchange rates and any Spot Rates for each month of the current year will be displayed.  Rates for all currencies of the companies you have added to Joiin will be displayed. You can view other years by selecting a different year from the Year dropdown.

NOTE: it's currently not possible to set rates for a currency that is not one of the currencies of your companies - we are working on adding this ability - in the meantime a workaround is to add an additional company manually setting the currency to the desired currency.

Base Currency

All rates are shown in relation to a Base Currency which is shown at the top of the page and at the top left of each FX rate section. The Base currency defaults to USD but you can change this by clicking on the base currency at the top of the page

This will bring up a popup where you can select the new Base Currency. Select the currency and click the Update button to change the currency. 

NOTE: Custom rates are associated to the Base Currency - so when you change base currencies the custom rates will be replaced with the rates you've entered for each base currency.

NOTE: Changing base currency changes the rate used for currency conversion to be relative to a different currency - this can result in rounding differences that mean the report results are slightly different before and after the change.

e.g.

1 GBP = 0.746116 USD - rate used when base currency is USD
1 USD = 1.344495 GBP - rate used when base currency is GBP

As the rates are only published to 6 decimal places of accuracy, there can be slightly different results when using one over the other.

Customising Rates

You can change the default rates to different rates that you wish to use simply by entering a different value in each cell.

The default rates are shown in grey.  When you enter a different rate it will be shown in blue to distinguish a customised rate from a default rate.

To remove the customised rate and revert to the default rate just delete the custom rate from the field and it will return to the greyed default value.

Once you have made changes to the rates you must save the changes by clicking on the Save exchange rates button at the bottom of the page.  To revert all unsaved changes click on the Cancel button

Adding Account Spot Rates

You can also set specific rates for specific Accounts. This can be useful for Equity accounts (e.g. share capital, owners equity, retained earnings) where the conversion rate used needs to be set to a fixed amount that was used at a particular point in time. This can be important for compliance with IFRS.

To add an account spot rate, click on the Add Spot Rate Account button at the bottom of the page. 

This will bring up a form to select the accounts you wish to set rates for. This will show all the accounts from all your connected companies. You can search for accounts by typing the name/code/type in the search box.

Select the accounts you wish to include and the click the Add accounts button. The accounts will be added in the Account Spot Rates section with the FX rates set to the default Month End values. You can then change the FX values as described in the section above.

Once you have made changes to the rates you must save the changes by clicking on the Save exchange rates button at the bottom of the page. 

You can delete all the spot rates for a particular Account by clicking on the Delete button next to the account.

To revert all unsaved changes click on the Cancel button.

Enabling/Disabling Rates

You can switch custom and spot rates on and off quickly by changing the toggle to the right of each section.

Switching the toggle on will mean any custom or spot rates are used when a report is run. Switching the toggle off will mean the Joiin default rates are used when a report is run. When the toggle is off, the default rates will be shown and it is not possible to enter custom rates.

Downloading FX Rates

You can download your FX Rates to a spreadsheet by clicking on the download icon next to the Year dropdown. A spreadsheet will be downloaded with 3 tabs, one for each section within the FX page.

Reporting Configuration

You can further control which FX rates Joiin uses by changing the settings under Reporting Configuration tab of the Settings page.  Click on the top-right Settings menu (cog icon) and select the Reporting Configuration tab.

There are two FX rate settings which control how the two main types of accounts, Profit & Loss and Balance Sheet, are treated.  For more information on account types see this section.  There is also a setting to control how Joiin adjusts for Foreign Exchange differences.

 Conversion Type can be set to one of three values:

FX P&L Multi-Month Conversion Strategy

When you select a multi-month reporting period (e.g. Jan 20 - Mar 20) on the P&L, Joiin will break the results down by month, apply the FX rate for each month and then combine the results.  This is to ensure the most accurate FX that reflects changing rates over an extended period.  It also ensures that the total matches the total if you were to run the same report month-by-month. 

So that totals align across all Joiin reports it also uses this strategy on the reports where P&L accounts are displayed, namely:

• Trial Balance Report which shows a Year-to-date figure and so may be an accumulation over multiple months.
• Balance Sheet Report which contains a Profit-for-the-year figure which is obtained from the P&L report.

NOTE: If you do not want the FX conversion to be broken down in this way you can select Report Run Date conversion type in Reporting Configuration - see above.

Budgets

Note that currency conversion of Budget figures, which appear when you select the Budget filter on reports, will use the Report Run Date method, by default. Alternatively, Budgets can be specifically configured to use FX rates fixed to a specific month. See Budgets.

Sales Reports

Joiin Sales reports use invoices/bills from the source system to generate report data. The way FX is applied in this case is different to the financial reports. 

If the company's currency is different to reporting currency and an invoice is in the same currency as the company.

 - We apply an end-of-the-month foreign exchange rate for the month report is run. (If the report is run mid-way through the current month, it will use today's rate)

If the invoice is in a different currency to the company's base currency but the reporting currency is the same as the company's currency.

 - We apply a Xero-provided invoice rate. NOTE that this rate may be different to the rate that Xero uses when you run their Ageing reports as they perform a re-evaluation of unpaid invoices at the time the report is run using the current FX rate.

If an invoice is in a different currency than the company's base currency and the reporting currency is different to the company's base currency.

 - We apply Xero-provided invoice rate. NOTE that this rate may be different to the rate that Xero uses when you run their Ageing reports as they perform a re-evaluation of unpaid invoices at the time the report is run using the current FX rate.

 - And then we apply an fx rate for the end of the month for which the report was run (If the report is run mid-way through the current month, it will use today's rate)

FX Adjustments

On the Trial Balance and Balance Sheet reports there may be scenarios where they do not balance due to the way Joiin performs currency conversion.  These scenarios are:

• If you've set your Balance Sheet and Profit & Loss conversion types to different types, the exchange rates Joiin uses to convert Balance Sheet accounts may differ from the exchange rates used to convert Profit & Loss accounts.  
• If you are running a report for a period which is more than one month - the P&L report uses a multi-month conversion strategy (see above) whereas the Balance Sheet uses a single month FX rate (the reporting month)*.

These imbalances are expected and discussed in the International Accounting Standards.

To mitigate these imbalances "FX Adjustment" rows are added automatically by Joiin to offset the differences.  The adjustments will appear as below.

NOTE: the FX adjustment is calculated as follows:

1. Convert all the Balance Sheet account balances for the period using the conversion rate used on the Balance Sheet
2. Convert all the same Balance Sheet accounts using the conversion rate(s) used on the P&L
3. Add each total up and use the difference between the two as the FX Adjustment.

NOTE: You can prevent Joiin from automatically adding this adjustment by switching off the "Add FX Adjustment" flag as mentioned above.

NOTE: If you have added Eliminations which result in the Trial Balance for a single entity not balancing then the FX Adjustment may not reflect the true FX-only difference.

* The crystallisation of BS assets and liabilities is at a given point in time, this is because if they were converted to the functional currency at that date, that's what they are worth, you can't translate historically.

FX effects and Eliminations

If you have eliminated accounts in Joiin then there may be additional anomalies due to currency exchange differences.  For example if a multi-currency inter-company transaction in an entity has been converted from a currency at a particular rate which is different to the rate used to do the currency conversion in Joiin, there may be differences and reports may not balance.

When an account is eliminated in Joiin currently it is simply removed from all reports - no adjustment is made for any differences that might result from FX as described above.  We do plan to add automatic adjustment features soon.

It is possible however to counteract these effects by either setting the exchange rates in Joiin to the same as used in the source system or by manually adding a company to add adjustments values.