IMPORTANT: This post is specific to New Zealand. If you are not a New Zealand business/taxpayer then it is probably not applicable to you.
So you have a retail outlet and are using Xero to manage your accounting. Great start. But in retail, you have a number of settlement issues. There are sales on account, customers may pay on account over the counter, EFTPOS (debit cards) and various credit cards may settle on different days and some settle gross and some settle net of commissions. How do you handle all this?
First, let me recommend that you consider using a Point of Sale system that integrates with Xero. We use Vend and highly recommend it. Vend has a Register Close function that will handle the balancing of each register and post the appropriate totals to Xero. If you would like to look at implementing Vend please let us know – we are Vend partners and can help you evaluate, implement and integrate properly with Xero.
So now assuming that you need to handle all this manually, you can do it the fast (and sloppy) way, or the more robust way.
The Quick and Dirty Approach
This method is reactive and is great for Xero Cashbook users. It basically waits for the settlement transaction to show up in your bank feed and then you code it through the bank reconciliation screen. Special cases to be aware of are:
- Sales on Account: you will need to manage these manually. Ideally, you would create a Xero invoice and send it to your customer on the spot.
- Payments on Account: When a customer pays you over the counter (cash, cheque or credit card/EFTPOS) you will need to make a note of this. When that method settlement shows up in the bank feed you will need to click “Find and Match” and search for the invoice that is being paid. Then you will need to click “New Transaction” and create a new Receive Money for the balance to be recorded as sales. Alternatively, include the amount (no tax) and code it to a Payment on Account clearing account, and code the payment on the invoice to the same account. He makes it easier to code the deposit but requires a bit more balancing later.
- Credit Card Settlements net of Commission (e.g., AMEX): When these come in click “Add Details” and enter the gross sales in the first line as a positive number (to calculate this divide the net settlement by 1 minus the commission percentage – e.g., if the commission is 3% you would divide the settlement by 0.97). In the second line enter the commission amount as a negative line coded to your Bank Fees or Merchant Charges account. The two lines should total the amount of the net settlement. Remember to use the correct tax codes on each line as they will probably be different. You can use a bank rule for this, but you have to work out the percentages carefully.
- Cash Disbursements: Click “Add Details” and enter the gross sales (before disbursements) in the first line. Then enter the disbursement amount(s) in the following lines as negative amounts. The lines should total the amount of the net settlement. Remember to use the correct tax codes on each line as they will probably be different.
- Everything else: code as Sales
The problem with this approach is that it will not tell you what went missing and does not properly deal with cash over and under. Basically, it just records what hit the bank. The next method is more accurate.
The Full Monty Approach
In this option, we create a single Invoice for the day’s total sales. This allows you to split out any detailed coding (if you have more than one sales account) and deal easily with differing tax codes if necessary, without having to worry about which settlement method is used. Special cases to be aware of are:
- Sales on Account: Exclude these from your daily summary invoice; you will need to enter separate invoices for these.
- Payments on Account: The best option here is to include them in the Daily Summary total as a separate line (no sales tax, as this has already been accounted for) and code it to a Suspense account (I recommend that you set up a Current Liability account called something like “Account Settlements Pending” and check the “Enable payments on this account” checkbox). Once you have posted the Daily Summary invoice you will need to go into each invoice that was paid on account and record the payment against the suspense account (or select them all in the Awaiting Payment tab and create a Batch Deposit).
- Credit Card Settlements net of Commission (e.g., AMEX): Calculate the amount of the commission and include this as a negative line in the Invoice.
- Cash Disbursements: Include any amounts paid out of the register as negative lines in the Invoice. Remember to use the correct tax codes on each line as they will probably be different.
- Cash not banked: If you do not bank each day then things get a little messier. I suggest that you post the cash not banked portion as a negative line in the invoice to the “Cash Float” account.
The total of this invoice should be the combined total of the various banking/settlement amounts expected. The final step after posting this invoice is to record the settlement amounts as “payments” against the invoice. You can simply wait for these to arrive on the bank feed and simply allocate them, or you can be more proactive and create them now. Simply “Add a Payment” to the invoice for each settlement method (cash deposit, EFTPOS Settlement, AMEX (net of commission) etc.). When these settlements appear in the bank feed they should be able to match against these expected payments. Once you have applied the payments to the invoice the balance should be NIL. If not, you have made a mistake.
Another variation on this (which speeds things up) is to code the expected settlements to clearing accounts right from the coding lines (as negative lines). The result is that the invoice will total NIL. I do this for one of my businesses. We have a repeating invoice that comes in as a draft with all the standard coding lines in it and gets posted as a NIL invoice.
Then all I have to do is code the bank deposit and EFTPOS settlements to their clearing accounts (I have bank rules set up for this). At the end of the week or month I just check to ensure that the cab and EFTPOS clearing account balance to NIL and I know that everything has worked correctly.
“Piecemeal” Option
Rather than one big invoice with payments applied against it you could simply create multiple Receive Money transactions or Invoices for each settlement type. The same special cases as for the daily summary option apply. The advantage of using Invoices over Receive Money is that you can semi-automate this using Excel Macros to generate the Invoices to import into Xero as CSV files (you can’t import Receive Money transactions). The advantage of Receive Money transactions is that they can be changed later whereas Invoices can’t be changed much after payments have been applied.
Once you have got the process setup it usually works very smoothly. If you would like help in getting this setup or in implementing Vend we do offer live remote support. We can have an initial conversation to assess your needs, and then create a consulting engagement to assist you implement this.
