Xero in on Xero
We have covered the power of the bank reconciliation process in Xero in the recent Xero in on Xero articles. Utilizing bank feeds allows us to bring our cash transactions into Xero. From there, we can record transactions on the fly from the bank transactions utilizing the Create feature. We can match the bank transactions to already created transactions in Xero, like invoice or bill payments. We can also utilize the efficient bank rules features that allows us to speed up the bank reconciliation process and provide greater accuracy to our financial reports.
Once we have recorded all of the transactions from the bank feed, we still have some work to do in order to make sure we don’t have any reconciliation differences - enter the reconciliation report. The reconciliation report can be found in the Reports menu, in the Reconcile screen or in the Manage Accounts drop down menu next to each bank account. The reconciliation report provides us with the balance in Xero, any outstanding payments or receipts that have not been matched to a bank transaction, any unreconciled statement lines, and the statement balance. If every transaction was recorded and all payments and receipts have been matched to bank statement line items, the balance in Xero should equal the statement balance. However, as any accountant and bookkeeper can attest to, there will be instances where that is not the case. Here is how to diagnose and fix reconciliation differences.
The first place we should start is the statement balance. The statement balance in Xero is being calculated by summing up all transactions that have come through the bank feed. This is not coming from the bank or the bank statement itself. Therefore, we need to first ensure that the statement balance in Xero agrees to the actual bank statement balance itself.
Obtain the bank statement from the client or utilize a software like Hubdoc to fetch the bank statements for you. If the statement balance in Xero does not agree to the bank statement, then we know we do not have every transaction in Xero from the bank. Before we start digging into individual transactions, run this test back each month prior until the Xero statement balance and the bank statement balance agree. That will pinpoint which month the discrepancies first started.
Once we figured out the month in question, we can then utilize two other features of the bank reconciliation report from Xero. The first is the bank statement tab. This tab shows every transaction that has come through the bank feed. The other tab in the reconciliation report is the statement exceptions tab. This tab shows if any transactions were manually reconciled (meaning that a payment or a receipt was reconciled without matching to a bank transaction), if any bank statement transactions were deleted, or if there are any duplicate transactions. Using these two tabs from the report will allow to see the cause of the difference between Xero and the bank statement. Once the Xero statement balance and the bank statement balance agree, we can move on to our next test.
The second place to look for reconciliation differences is the differences between the balance in Xero and the statement balance. As stated before, if there are differences between these balances they will come from outstanding payments or receipts that have not been matched to a bank transaction. Now the existence of these transactions may not mean anything is wrong. For example, if I received a payment from a customer via a check and marked my invoice in Xero as paid to the checking account on September 30, but did not deposit that check until October 1, the outstanding receipt would show up on the reconciliation report for September 30. However, since this deposit happened subsequent to month end, there would be no issue with the outstanding receipt showing up on the September 30 reconciliation. However, if there is an issue it will likely be that the bank transaction was not matched to the outstanding item and recorded directly into Xero or the outstanding receipt or payment was recorded to the wrong bank account. Let’s take these two potential issues in order.
The bank transaction not being matched up to the outstanding payment or receipt is the most common issue you will find. One of the easiest ways to identify this is to look at the account transactions tab in the Reconcile screen. The account transaction tab will show all bank transactions that have been reconciled plus any unreconciled payments or receipts. By looking at the account transactions tab, we can pinpoint where a transaction may have been recorded and reconciled from the bank statement, but not matched up to the outstanding payment or receipt.
In the example above, there was a rent expense recorded and reconciled directly through the bank feed and a payment recorded from a bill set up in Xero for the same transaction that is showing as unreconciled. To fix this issue, we need to click on the check box next to the transaction that was reconciled and click the remove and redo button. This will remove the transaction we recorded from the bank statement and bring back that bank statement line to the reconcile screen where we can now match it to the outstanding payment.
The other issue could be that we recorded a bill payment or invoice receipt to the wrong bank account. To diagnose this issue, we would go through the same steps as the first issue above by looking at the account transactions tab. If we don’t see a duplicate entry in there, we will then check the other bank accounts to see if this transaction came through a different bank feed. If it did, we need to go back to the account transaction screen and do a remove and redo of the unreconciled payment or receipt. We will either go back to the invoice or bill and record the payment to the correct bank account or go to the correct bank account and match the bank transaction directly to the invoice and bill.
By using the bank reconciliation report and going through common issues, we will have been able to diagnose and fix the bank reconciliation issues. Now, our Xero balance will agree to our statement balance or the remaining reconciling items will be related to a subsequent bank transaction and our bank reconciliation process will be complete. We can kick up our feet now that the bank accounts have been fully reconciled for the month!
Joshua Lance
Author Bio: Joshua Lance is the founder and Managing Director of Lance CPA Group, a virtual CPA firm that focuses on providing accounting and consulting services to craft breweries and digital agencies. A licensed Certified Public Accountant and Chartered Global Management Accountant, Josh is also a family man who calls Chicago home. Before venturing on his own with a mission to help small businesses, Josh spent his early career at a top-10 national public accounting firm before working at an ultra high net worth family office. Josh is also an adjunct lecturer at Northwestern University in Evanston, IL teaching in the Farley Center for Entrepreneurship and McCormick School of Engineering. He enjoys making wine at home, cooking, traveling, and cheering on his favorite football and soccer teams. Josh was honored by being selected to the 2017 class of the AICPA Leadership Academy and was named as one of the 40 under 40 in 2017 and 2018 by CPA Practice Advisor.