Fixing Common Bank Reconciliation Problems in Xero

The Bank Reconcile screen is one of my favourite places in Xero. It was, and still is, what makes Xero a clear leader over the other cloud-based accounting systems. If used properly it puts an end to unnecessary data entry and reconciliations. But there is always a catch – if used properly! It is possible to be unreconciled even if you no more entries in the reconcile screen or if the Statement Balance equals the Balance in Xero.

You can check to see if your account is actually reconciled by running the Reconciliation Report. There is a link to this report right on the bank account page (beside the “Manage Account” menu – in most cases), from the Manage Account menu or from the Reports-All Reports page.

If your account is properly reconciled then there should be no transactions listed in the report and the “Balance in Xero” should equal the “Statement Balance” (a calculated balance based on the bank statement lines imported into Xero). You should compare the “Statement Balance” from this report to the actual balance from the physical bank statement (or online) to verify that this is what the bank also says. If your bank feed includes a balance (all the major banks standard accounts do, but some of the credit card and Yodlee feeds do not) then the report may also say “Imported Statement Balance” and if this is not the same as the “Statement Balance” it will show you a “Balance out by” error amount.

When we do HST returns and management accounts for our clients we always check the bank reconciliation for all bank accounts to ensure that everything is up to date.

There are three main types of errors that show up on the Reconciliation Report:

• Un-Reconciled Statement Lines: These are transactions still on your Reconcile screen that you have not yet allocated. It is best to reconcile these and regenerate the reports before your move on to other issues. If you are expecting to be able to match it against an existing transaction that is not showing up try the following:
  • Missing transfer leg – check to see if the other leg of the transfer was coded in Xero correctly. It might be that the other leg hasn’t been coded yet (in which case you can simply code it here) or that it was coded to the wrong account (in the other account “Remove and Redo” the transfer from the transaction tab and now you can correctly code it.
  • Missing Invoice/Bill – first check to see that the invoice/bill has actually been entered and approved. If not, enter or approve it so that it will be available to allocate the bank statement line to. If it is there but not showing up then it is probably already allocated to payments/receipts. Open in invoice/bill and see the payment history beneath the total. If you can find the erroneous payment you should consider using “remove and redo” on the payment which will free up the invoice/bill for allocation again.
• Outstanding Receipts/Payments: These are Receive/Spend Money transactions that you have entered into Xero that have not been matched off against bank feeds. Common causes are:
  • Original bank statement line coded rather than allocated – this is the most common mistake. Either Xero does not recommend a match or the user overrides it and codes it directly as a new spend/receive money rather than allocating against existing transactions. If you are really sure that the transaction has actually been received then we need to see how it was originally handled. To find the incoming statement line go to the “Bank statements” tab and click on the “Start Date” column to sort. Now locate the transaction date that the original transaction happened and click on it. Now find the exact transaction – it should have a green tick next to it. Now click on that and you will see how it has been allocated. From here you can “Remove and Redo” the entire batch or click into the matched transaction(s).
  • Transaction in the wrong bank account – this is also fairly common and is where a payment or spend/receive money has been entered into the incorrect bank account. There is no way to change the assigned bank account, so you need to “Remove and Redo” the incorrect transaction and do it again. This won’t affect any invoices/bills that the payments are attached to but will destroy any spend/receive money and attached documents.
• Balance Out: This is much more complicated to debug and usually will require more of an expert eye to sort out. Most likely there have been deleted bank statement lines or missing bank statement lines that have caused this. Here are some things to look for:
  • Click on the “Statement Exceptions” tab in the Reconciliation Report and see if anything is listed there. If there are you may need to investigate these further.
  • Click on the “Bank Statements” in the bank account screen. Sort by start date. Look for any dates that are missing or dates that overlap.
  • If you can’t find the reason go to the “Bank Statement” tab of the Reconciliation Report. Compare periods to your actual bank statement. Start with the most recent month and check the starting balance against the bank statement. Keep going back one month at a time until you find one that agrees with the bank records. Now compare the actual transactions to see what is different. It could be duplicate transactions or missing transactions. If duplicate transactions then one of the bank statement lines will need to be deleted. If missing transactions then they will have to be imported manually.

If you are uncomfortable about any part of the process, or not sure you are doing the right thing, please don’t hesitate to contact us. Clients on a monthly fee plan are covered by our “no fee 10 minute rule” for fixing up small problems. Otherwise we will charge you a reasonable fee for the expertise we provide to help you keep you ledger up to date.

Still having problems?

If you are still having trouble getting your bank account to reconcile. Never fear. Not only is Fuel a Xero Partner, but we have Xero Certified staff. In addition, we are a Xero award-winning firm (2012 Most Valuable Professional award to Peter McCarroll for his contribution to the Xero Community Forum – helping people solve their Xero problems).

Our clients are eligible for our “10 minute no-charge” support policy (conditions apply). If you are not a Fuel Accounting client we can still help – please send an email (getincontrol@fuelaccounting.ca) or call us on 647-367-0876 and we can help you sort out your Xero problems quickly and easily (fees apply).