First off, the punchline: The add-in works well for reporting on Income Statement accounts in a Xero multi-currency environment, not so well for Balance Sheet accounts.
Why is this?
The add-in works by summing up Xero journal lines that meet the criteria you specify in the =SCOTT custom function. Contrasting this approach with how Xero does the math to present balances for certain Balance Sheet accounts affected by foreign currency transactions. To accommodate the unrealized currency gain, Xero uses certain “system” accounts to adjust the balance of certain Balance Sheet accounts. Unfortunately, these adjustments are not recorded as journal lines, so the add-in is powerless to adjust the balances.
Here is a deeper dive into the Xero accounts affected by multi-currency:
810 Bank Revaluations
A Xero “system” account that Xero does not give us access to. A “zero” balance will be reported by the add-in.
815 Unrealized Currency Gains
Works just fine with the add-in.
820 Realized Currency Gains
Works just fine with the add-in.
Bank accounts
Xero will show bank accounts that have been “revalued” for changes in currency exchange rates. However, the add-in will always report the Bank balance, using the sum of reconciled transactions in the home currency.
Accounts Receivable & Accounts Payable
Accounts Receivable / Payable – Xero will adjust the balance of these accounts on a Balance Sheet for transactions in different currencies, when unpaid items exist. These adjustments are made by Xero, “behind the scenes”, using the system accounts mentioned above. The add-in will always report the account balance in your home currency, which will show a different number than the balance sheet if you have outstanding invoices rendered in a foreign currency.
So, what to do?
If your organisation is making use of Xero multi-currency, we recommend you only use the add-in for Income Statement accounts. Optionally, you can use the add-in for Balance sheet accounts, if you are fine with showing the balances in your home currency, not reflective of unrealized currency gains.