Fixing Bank Reconciliation “Out of Balance” problems in Xero
Xero is a highly complex accounting environment. While the developers have worked wonders to make it simple and user friendly, this approach often masks the fact that it is a complicated accounting system under the hood! The bank reconciliation process in Xero, if provided with correct data and intelligent user interaction, should result in a perfect bank reconciliation where everything balances as expected. But the reality is that this does not always happen.
I have previously written about the most common bank reconciliation errors in Xero, which is a good primer for understanding the items listed on your Reconciliation Report. This article is specifically dealing with the scenario where your “Statement Balance” in Xero does not agree with your actual bank statement. I’ll warn you now – this can be technical and complicated. Novice users should not play around in most of these areas. If you identify that you have problems PLEASE get a Xero Certified Advisor to help you fix it. We offer paid support and can quickly address most problems.
We’re working here in the Reconciliation Report in Xero. My earlier article describes the transactional content (Outstanding Payments/Receipts and Un-Reconciled Bank Statement Lines). Here is what you need to know about the main balances.
- Balance in Xero – this is the accounting ledger balance. It is the sum total of all the debits and credits recorded in the bank account in Xero, whether reconciled to the bank statement or not.
- Statement Balance – this is a calculated balance within Xero of all the reconciled transactions plus the un-reconciled statement lines. This is what Xero thinks that you bank statement balance should be based on the information provided to it.
- Imported Statement Balance – this is the statement balance provided to Xero by a direct bank feed or through an OFX file import or similar. Most CSV or QIF file imports do not contain the statement balance, and (to the best of my knowledge) Yodlee bank feeds do not import the statement balance. If a statement balance is not available for that ending date Xero does not display this line on the Reconciliation Report.
Most Common Causes for Out of Balance Errors
Out of Balance errors are NOT caused by bad data entry when entering transactions into Xero (by themselves). These may cause transactional reconciliation errors, which is the subject of another article, but not Out of Balance Errors. I also assume that you have bank feeds on this account or at least you are manually importing bank statements to reconcile (if you don’t then you will have other problems – all of your own making and should see professional help). Here are the most common reasons for Out Of Balance errors:
- Incorrect Conversion Balance – when setting up your Xero ledger for the first time you had the chance to enter an opening balance for the bank account. If you entered a number that was different than you bank statement balance at conversion date then you will have problems. The most common reason to enter a different balance (other than error or laziness) is that you entered your ledger balance and there were outstanding payments/receipts that hadn’t yet hit the bank statement. If this is the case you will need to (1) change the Conversion Balances to only post the actual bank statement balance to the bank account, (2) in Conversion Balances post the total of the reconciling items to Suspense, and (3) in the Bank account enter the reconciling items as Spend/Receive Money transactions coded against Suspense and dated prior to the conversion date. You have probably already coded the incoming statement lines for these reconciling items so you may need to delete these (remove and redo, then void the incorrect spend/receive money) and re-match the bank statement lines to the pre-conversion transactions you have just entered.
- Manually Reconciled Transactions – using the “Mark as Reconciled” function should be rare. It should never be used when you have reliable bank feeds coming in as it is the most common cause of problems created by my clients (“Oh yeah – that should have been reconciled, let me just mark it as reconciled”). These can easily be found by looking at the Statement Exceptions tab on the Reconciliation Report or by sorting the Transactions tab of the Bank Account screen by reconciliation status (you’re looking for the black ticks). You may need to investigate each manually reconciled transaction to determine whether it is valid and, if not, ‘remove and redo’ it (this will permanently delete the spend/receive money transaction – so do this very cautiously or use the unreconciled/unmark as reconciled function).
- Duplicate Bank Statement Lines – unfortunately these are much more common that I would like, especially in Yodlee feeds. Xero does attempt to identify them, but not as reliably as I would like. They are also very hard to identify. You could troll through the bank statement import logs (bank statements tab in the bank account screen) but that could take forever. The next step is to compare your Xero generated statement to the physical statement – see below.
- Missing Bank Statement Lines – before bank feed starts – when you set up a bank account in Xero the bank feed data might be missing the first few weeks. You can tell when your feeds started by looking at the Bank Statements tab in the bank account screen and sorting on start date (click twice to get the oldest at the top). The easiest way to solve this tis to import the missing data from your bank account and then reconcile these transactions. If you have already manually reconciled (marked as reconciled) then you should check that you have done it correctly.
- Missing Bank Statement Lines – after bank feed starts – this is somewhat common with Yodlee feeds, unfortunately, and also very hard to find. The only option is to compare your Xero generated statement to the physical statement – see below.
- Deleted Bank Statement Lines – users can too easily delete a bank statement line from the reconciliation screen and thereby throw your reconciliation out of balance. The easiest place to see if this has happened is by looking on the Statement Exceptions page of the Reconciliation Report.
Comparing your Xero ledger to your Bank Statement
If you have exhausted all the above and are still out of balance, then you need to compare your Xero system to the actual Bank Statements from the bank – line by line if necessary. Fortunately it doesn’t take too long if you do it right (and smartly). You will need you physical (or electronic) bank statements that give you a running or daily closing balance if possible. Here’s the process:
- Start with the oldest date that you know there was a problem. Generate a Reconciliation Report in Xero for the account, switch to the Bank Statement tab and ensure that the date range covers the same date range as the statement you have (or that you can compare it to a section on the statement with balances).
- Compare the opening statement balance in the Xero report to the opening statement balance on your bank statement. If they are not the same go back one month at a time until you find a period where they agree. If this never happens, you probably have a conversion balance problem – see above, then seek professional help.
- Now that you have a safe starting point, jump forward one week at a time until you find the point that they are no longer in balance. Go back daily until you find the last day where it balanced. You have now identified the (first) day where there is a problem.
- Now go through the transactions for that day and compare the Xero bank statement report against the actual bank statement. If possible, tick them off as you go. You are looking for transactions on one side that are not on the other, or transactions where the amounts differ (less likely).
- If it is on the bank statement but not in Xero – check to see if it is in the transaction list (may have a different date). If it is there with a different date and is manually reconciled you may want to change the date so that it matches up with the bank statement. If there and not reconciled you should mark it as reconciled. If not there you should create it and mark it as reconciled.
- If it is in Xero but not on Bank Statement you should check to see if it was manually reconciled. If so, it probably needs to be deleted
- If on both but for different balances then you probably need to change Xero to agree with the bank statement.
- Now you should prove that the closing balance for the day in Xero (just refresh the reconciliation report) agrees with the bank statement. If not, go back and repeat/continue step 4.
- Now update the reconciliation report for a recent date to see if that has fixed the problem. If not, repeat from step 2 for the next month (after the one you have just fixed) and keep rolling forward until you find the next error.
Keeping your bank reconciliations balanced is an essential process in any accounting system. Xero makes that very easy with the automatic feeds. We check this for all our clients before we file a GST (Sales Tax) return throughout the year. You should make sure that this is checked regularly throughout the year so that things don’t get out of control!
Still, having trouble?
If you are having trouble getting your bank reconciliation to balance properly we offer a paid problem-solving service. Just visit www.businessexpressaccounting.com/xero-support where you can book a live remote Xero Support session. We’ll be happy to help you get your Xero ledger back to full health.