Reconciling a foreign bank in bank services does not equal the GL balance in the foreign currency. The bank module is in the foreign currency, but the GL transactions are in multiple currencies limiting the ability to positively reconcile the bank account in the sub ledger to the balance in the GL.
For the purposes of our discussion here, we will use the CAD currency as the functional or home currency, and USD as the foreign or source currency of the bank account.
It is not logical for a USD bank account to have any entries listed in CAD dollars. The CAD dollar transaction would be converted to the currency of the bank account and would be shown on the bank statement in USD. However, this is exactly the situation that commonly occurs in our foreign bank accounts in the General Ledger. Entries are made to a USD bank GL account in CAD currency.
A couple of specific reasons for this are:
- during a transfer from another system to Sage. The originating system may only be a single currency system, and the balances are brought into Sage in Canadian dollars.
- upon creating a new database or company and entering opening balances. Opening balances are generally entered using a trial balance as the source document, and this report is always in CAD (functional) currency.
- journal entries done in the GL to the bank account in error in Canadian dollars. All entries to the bank account should be done through Bank Services, which would force the entries into source (USD) currency.
Any of these types of transactions (or others) will setup a situation where the bank account will reconcile in the bank module, but the bank module will not reconcile to the GL.
Additionally, the GL balance may not be able to be reconciled manually, because the balance of the account is a total of CAD dollars plus the converted USD amount.
And we can see the individual currency values for this account from the GL Chart of Accounts screen by clicking on the Currency button. The G/L Source Currency Inquiry box will popup:
In the example above, the USD bank balance in Bank Services, $10,466.52, is trying to reconcile to the USD balance in the GL, $2,774.21. The bank rec cannot take into account the CAD amount of 10,000.
Now that the problem has been identified, what is the solution? If the problem is simply a GL journal entry done in error in CAD dollars, that entry can be reversed. However, in other cases, where the entry may have been a trial balance or multi-lined entry, it is difficult to pinpoint the other side of the entry in order to reverse. In this case, we can do a debit and credit to the same bank account, while switching the currencies. Remove the Canadian amount, and enter the equivalent USD amount. For this example, the correcting journal entry would look like this:
After posting, once all the CAD amounts have been removed, and only USD values remain in the account, we are able to successfully reconcile the account.