This article focuses on the reconciliation of a messy supplier account – as this is a common problem we help clients to resolve. The same principle applies to a messy customer account.
Messy supplier account reconciliations
Sometimes, no matter how hard you try. You just can’t reconcile that supplier account in Xero to the statement that the supplier has sent to you:
- You might be missing invoices or payments from your supplier account but you can’t quite figure out which are missing
- You may have made payments on account or by instalment to your supplier and they have matched your payments against different invoices
- You may have just moved to Xero and you are trying to reconcile historic transactions
- Perhaps you have a really busy trading history, and somewhere along the way you and your supplier have fallen out of sync.
- You may have paid your supplier by cash, credit card and BACS so you have multiple payment sources to check in Xero. What a pain!
Whatever the reason, we can go back to Xero and restate our transaction history to make our reconciliation process easier.
Why do we need to do this? Troubleshooting Xero
- Once an invoice is marked as paid in Xero, you can see the invoice is marked as paid, but you can’t see when or how it was paid without drilling down into the transaction. It would be a lot easier to reconcile the supplier account if we could see the dates and methods of payment.
- Say we had an account where we believe all invoices are paid and the account is clear. Our supplier has sent a statement showing an outstanding balance of £1181.25 – our bank statement reconciles so we know that the four payments we have made in this example have cleared our account, so why is there a difference?
It might be easier to reconcile to your suppliers statement if you were able to start with a simple list of invoices and payments. Although none of the payments have been matched to invoices yet, we have a much clearer picture of the full transaction history. We can now see when and how we paid our bills and we can go back to the supplier and provide them with a full transaction history.
How do we un-reconcile our reconciled supplier account?
- First we have to go to the bank and un-reconcile all the payments we have previously matched to invoices and post them as payments on account. Ignore the suggestion to match to an invoice, and click on the Find & Match link.
- Click on the option to post the transaction as an overpayment
- Add details of the overpayment and save.
- Once all transactions are posted to the account, you will have a clean transaction history to reconcile with the supplier.
Using Accounts Payable to help you reconcile
You can run an aged payables report with the ‘Show invoices’ box checked to help with your reconciliation.
Match and tidy up
Once you have agreed to the individual transactions to the supplier statement, you can start to match payments against invoices. You can do this from the supplier account or Aged Payables report – whichever you prefer and whichever helps you identify the missing transactions.
You will be able to identify and post any missing or duplicated transactions and you should be able to agree the account balance with your supplier.
You can either drill down into the invoice or the overpayment to match it against its partner. The quickest way to match payments is to right click on the transaction link, open it in a new tab, reconcile and close. Then refresh the tab (supplier account or Aged Payables) and continue until all transactions are reconciled.
Once reconciled, our supplier account should match to the supplier statement with all payments credited to the appropriate invoice.
As you can see the messy account reconciliation does take a little time and perseverance, but the advantage is the transparency in the transaction history.
The same process can be applied to messy customer accounts.