To truly optimize your Xero experience, you will want to become a master at using bank feeds. In our first installment we took a quick spin around the block. In our second installment we took a quick peek under the hood and considered some basic bank feed maintenance. In our third installment we gave our Xero feeds a proper tune-up using Bank Rules and Cash Coding. Now we are going to learn how to troubleshoot common bank reconciliations problems so our bank feeds always run nice and smooth.
Reconciling bank accounts in Xero is a unique experience
Small business owners tend to keep pretty close tabs on their bank accounts, since cash flow can make or break a business. Throughout the month the bookkeeper typically keeps a handwritten or computerized register of banking transactions (or maybe both) along with a running balance. Although deposits and checks may be faithfully recorded on a daily basis, there are often a few pesky transactions that escape scrutiny. Most banks offer online access to business bank accounts, which makes it easier to check the current balances and review recently cleared transactions. Then once a month the bank sends out a detailed summary which is used to formally reconcile the business bank accounts to the statements.
Xero takes a more innovative and proactive method to bank reconciliations, one which initially can be perplexing to new Xero users. Reconciling live feeds on a daily basis helps business owners keep closer tabs on their true bank balances. But not having a month-end “moment of truth” tends to make traditional accountants, bookkeepers, and business owners a little nervous.
Why change a time-tested method that has worked for eons?
One disadvantage of the traditional method of bank reconciliations is that some problems won’t be resolved until month-end reconciliations are finished. Math errors or omissions in the in-house register that are not discovered promptly can result in last-minute cash flow issues. Bank errors or fraudulent transactions may not be noticed as quickly. Manually reconciling differences between the daily bank balance and the in-house register balance can be tedious and time consuming, since the bank balance doesn’t reflect outstanding checks and credit card deposits.
That doesn’t mean that Xero bank balances are trouble-free. Even if you match all of your transactions daily, errors can still happen. Occasionally a feed can fail or a transaction can be lost. When transactions are not properly matched to feeds, duplications can occur. Routinely uploading PDF copies of bank statements to Xero Files makes it easier to troubleshoot problems with bank accounts when they occur.
Two essential banking reports in Xero
The “Uncoded Statement Lines” report is one of our favorite research tools. You will find it in the Accounting section of Reports, under “More Reports”. Since it shows bank feed lines that haven’t yet been matched together with corresponding comments from the Discuss tab, it can be a handy worksheet for requesting additional information from other staff members.
A traditional style “Reconciliation Report” is found next to the Manage Account tab on the bank detail page. It shows all outstanding transactions and unreconciled statement lines affecting the balance. Compare this report to the current bank balance or the ending balance of your most recent bank statement. Once everything matches nicely, publishing the report creates a “snapshot moment” that you can refer back to in the future.
You may be wondering what the “statement balance” and the “balance in Xero” that shows up on your Dashboard and the Reconciliation report mean. The “balance in Xero” is the final balance showing in the Xero register in the Account Transaction tab. This includes transactions that have been posted even if they haven’t yet cleared the bank. It doesn’t include any unmatched transactions sitting on the bank feed Reconcile tab.
The “statement balance” includes the transactions that have come through the bank feed, whether they have been matched or not. When all transactions on the feed have been matched, the “balance in Xero” no longer shows up on the Dashboard. Occasionally a third balance called the “imported statement balance” will show up on the Reconciliation report, which probably means you have a problem to resolve.
What does the Account Transactions tabs do?
Many Xero users barely notice that the last two tabs on the bank detail page. We tend to spend most of our time on the Reconcile tab, since that’s where all the bank feed action is happening. You probably won’t need to visit the Bank Statement tab often, but you will want to become familiar with the Account Transaction tab because that’s your actual bank register.
This register contains all saved transactions related to the particular bank or credit card account in question. These transactions have been formally posted in your Xero file and will show up in your financial statements and reports. Note that unmatched transactions on the bank feed Reconcile tab aren’t part of the register yet and won’t show up in Search functions. There is a special search function built right into the Account Transaction tab which is very handy for researching transactions within the bank account register.
Your register may include checks that haven’t cleared the bank and deposits or transfers that haven’t shown up yet on your bank feed. How can you recognize which transactions haven’t cleared?
The far right column within Account Transactions is labeled “status”. All transactions in the register are labeled either “Reconciled” in bright green or “Unreconciled” in bright yellow. Try clicking on the top of the column to sort them by status instead of date order. Typically there will be a few unreconciled transactions on the list, but if there are too many or they are very stale, that’s a sign that some of them simply don’t belong. It may be time to bring in a professional to make serious repairs.
It’s always easier to avoid problems than to fix them. Here are a few suggestions to help keep you on the “straight and narrow”:
When you record payments and deposits directly (instead of allowing them match to the bank feed) make sure that you assign the correct bank account. If they are incorrectly assigned, phantom transactions appear in the wrong bank account and can no longer match to the correct feed. Since payments can’t be edited, in order to fix the problem you will have to remove and recreate the payments.
Be careful of hastily creating a missing transaction as a “Spend Money” or “Receive Money” on the bank feed. If the transaction already exists as a Bill or an Invoice, make sure to find and match it. These kinds of unreconciled transactions often have a nasty twin that shows reconciled. You may be able to discover the duplicates if you search by amount.
Also be careful of using the “Spend Money” and the “Receive Money” functions in stand-alone mode instead of creating them from the feed. If you enter a different amount than what eventually comes through on the bank feed, or assign the transaction to the wrong bank or credit card account, it won’t match, and will likely end up being created a second time.
It’s easier (and often safer) to wait until debit or credit card transactions show up on the feed before creating them. If you feel a need to record the transaction immediately, you might consider scanning the receipt through an App like HubDoc or AutoEntry, or using Xero Expenses to automate some of the coding process.
When do I need to use the Bank Statements tab?
The Bank Statements tab shows when and how transaction feeds were uploaded to Xero. It has two views – statement lines and statements. When is this tab useful?
Problems can easily happen when statements are manually uploaded* – in the wrong format, or maybe even to the wrong account. In this tab you will be able to tell which transactions were uploaded manually and which ones came in through formal feeds.
You can see which transactions have been successfully reconciled and easily delete transactions in bulk or even whole statements at once. Keep in mind that when unreconciled statement lines are deleted, they disappear permanently from the file. If you delete reconciled statement lines, those transactions will still remain posted in the main accounting file.
*Not all banks and credit cards have live feeds available. Even if your bank doesn’t offer a live feed yet, there are several ways to upload statements manually into Xero. It won’t be as sweet as a live feed, but you will still benefit from many of the great reconciliation features Xero offers. Here are a few useful tricks for importing prior bank history or filling in gaps when bank feeds fail:
- You can upload statements from your online banking site in several formats. QBB files (a QuickBooks format) often work nicely.
- To use CSV format, download a template from Xero and follow it closely. Don’t change headings or delete columns, make sure expenses are formatted as negatives (not parenthesis), and use the reference column for the bank description.
- If your bank doesn’t offer download options or doesn’t provide a decent format, you can convert PDF statements into Xero bank imports with document reading Apps like AutoEntry.
Even the best backyard mechanics sometimes need to call in a professional. Your Xero Advisor is experienced at resolving greasy accounting issues, so you can get back to doing what you do best – running your business.
Live bank feeds in Xero are like the open road – they can take you wherever you need to go, but even faster with a little automation innovation.
– Written by Gina Palacio, Owner of Level & True Accounting Services LLC