Extra Entries in VAT CSV Export!

[thedrumdoctor]

I have been preparing a VAT return and I made a CSV export to look over before submitting. However, the sales entries in box 1 and box 6 have extra entries in them! These entries do not have any corresponding invoice numbers unlike all the other entries. I’ve checked them and some appear to be duplicates of entries with invoice numbers for the same amount and date whereas others are not duplicates.

I have done some editing of invoices to make corrections, in particular, with errors on multi-line invoices and on invoices where VAT has been left off. It seems the VAT export is picking the old entries up.

I’m fine with doing a manual correction from the export for the VAT return, but it seems I can’t really trust the software to cope with invoice corrections and then produce an automatically trustable VAT return. I only bothered to do a CSV export because my gut feeling was the generated VAT return did not like right on the screen. I’m glad I did now! Is this a possible bug?

[Paul_Courtier]

Not a bug. More likely something you’ve done wrong.

When you edited the invoices, were they from a previously submitted vat return period? Did you have to roll back any vat returns?

I’ve never seen a bug as you describe so most likely something you’ve done.

[ian_roberts]

Are you on cash accounting? The ones with no invoice numbers may be payments received but not yet allocated.

The way QuickFile works a vat return will always include any entries (invoices on accrual accounting, payments on cash accounting) that are dated within the relevant period or dated before the period start but not already locked by a previous return. In other words if you submit one return and then subsequently add back dated invoices or credit notes, those will be included in the next return automatically.

[thedrumdoctor]

The invoices are all from the current period. Nothing has been rolled back. If it’s not a glitch, then it must be expected behaviour under certain conditions - if A happens then result = B etc. But what is the expected behaviour?

[thedrumdoctor]

Yes, we’re on cash accounting.

What would happen if invoices from a submitted VAT return were copied as as templates for the next period? Would that cause problems?

[ian_roberts]

I’m not sure from experience, as I’ve only ever run my business on accrual accounting, but I know there are complexities with cash accounting to do with the fact that the VAT is due on the date of payment but QuickFile only knows the correct VAT amount once the payment has been assigned to an invoice. So if you have unallocated payments when you run a return it includes them assuming they’re one sixth VAT, but if you subsequently assign the payment to an invoice then the next return will include an automatic adjustment to account for the difference between the estimated VAT that has already been declared on the payment and the actual amount of VAT on the invoice to which it has been assigned.

Similarly if you’ve assigned payments to an invoice and done a VAT return, but then subsequently edit the invoice to change the VAT amount, it’ll have to adjust the next return to compensate.

[thedrumdoctor]

You’re definitely on the right track with the unallocated invoices. I can see where they are now and it’s to do with my method of reconciling what the boss inputs as sales invoices.

Basically, I set up an ‘all sales received’ holding account under the bank accounts section for all the sales to go into. So at the end of every day, the boss creates a separate invoice in Quickfile for each sale recorded in the manual invoice book (we record sales the old fashioned way). He allocates the invoices as ‘paid’ into the holding account and records the method of payment - cash, PayPal, card payment, bank transfer or eBay sale.

We have live feeds from the business bank account, PayPal, eBay and business credit cards.

In a live example of one of the erroneous entries in the VAT return export, a sale for £900 was received by bank transfer from a customer. The boss recorded the sale on an invoice and recorded it as paid in the all sales holding account.

Later on, I have found the £900 incoming transaction in the business bank account and it is naturally flagged red. So, I go to the ‘all sales’ holding account and find the transaction and de-tag it. The transaction now appears as red in the ‘all sales’ holding account, but now the original invoice the boss raised for the sale has an ‘unpaid’ status.

I then go to the business bank account and tag the £900 incoming amount to the unpaid sales invoice. The invoice regains its ‘paid’ status and the bank account entry is tagged in green. However, the entry in the ‘all sales’ holding account is untagged but in effect, it’s done its job as a ‘placeholder’ for the sale until I can reconcile it with the actual bank transaction. So, I delete it from the holding account believing it’s history and will never rear its head again.

However, I looked at the nominal account entries for the ‘all sales’ holding account and lo and behold, the deleted payment is still there as an ‘unallocated invoice’ and it appears it is these ‘deleted’ entries from the holding account which are finding their way into the VAT return. This is obviously behaviour by design, but I was hoping the ‘all sales’ holding account would be able to provide the facility to allow me to make reconciliation across the live feed accounts without affecting the VAT return. Clearly this is not the case.

The boss hasn’t got time to spend on fiddling with accounting software so as the IT guy, it’s my job to sort out the technical aspects. All he wants to do is record the sales at the end of the day.

If my ‘all sales’ holding account idea isn’t going to work, then perhaps I should tell him to record the sales but leave them as unpaid for me to allocate later on. I didn’t realise ‘deleting’ a transaction from the holding account didn’t really delete them!

[ian_roberts]

@thedrumdoctor

If my ‘all sales’ holding account idea isn’t going to work, then perhaps I should tell him to record the sales but leave them as unpaid for me to allocate later on.

This would be a better way to approach it. If he’s creating an invoice for every sale then the ideal approach would be for him to mark as paid any cash or card sales, but leave unpaid any PayPal, bank transfer or eBay sales for you to tag later from the auto feeds.

The rule for VAT cash accounting is that card/PayPal/eBay sales are considered “paid” on the date the customer carried out the transaction, not the (later) date when you actually receive the funds from the card provider or eBay, but bank transfer sales are “paid” on the date you receive the credit in your bank account. If your boss is currently marking bank transfer sales as “paid” on the invoice date then you lose the benefit of cash accounting if the actual payment doesn’t arrive until the next VAT quarter (e.g. if the sale was on 29th June and the payment arrived on 2nd July, and you’re on calendar quarters, then under accrual accounting you owe the VAT on your Apr-Jun return but on cash accounting you don’t owe it until the July-Sep one).

@thedrumdoctor

However, I looked at the nominal account entries for the ‘all sales’ holding account and lo and behold, the deleted payment is still there as an ‘unallocated invoice’ and it appears it is these ‘deleted’ entries from the holding account which are finding their way into the VAT return. This is obviously behaviour by design

It doesn’t sound right to me, but I’m not QuickFile staff so I can’t look into your account to see what’s really going on. @QFSupport any ideas?

[thedrumdoctor]

Thanks for that advice, I have just advised him to change how he does the sales invoices and leave them as ‘draft’ or ‘sent’ until I discover what is going on. If I have to reconcile every sales invoice, then so-be-it. It’s actually really quick to do and a strangely satisfying experience for a non-accounts person…

As for the retained ‘deleted’ entries from the ‘all sales’ holding account, they are most definitely there in its nominal account so maybe this is something @QFSupport could look into.

[Glenn]

@thedrumdoctor more than happy to look into this for you. Just based on what I’ve read above it would seem that the all sales holding account is an unnecessary complication. Use holding accounts for Ebay and PayPal sales (i.e. where payments to your bank are lumped together, minus the processing fees), but any direct to bank payments (e.g. BACs) from customers just tag the bank payment directly to the open invoice.

If you have any residual items in that account we can definitely take a look at that for you. I’ll drop you a private message so you can share any detailed info.

[thedrumdoctor]

Yes, it does seem I have made things harder! It’s still early days using the software so it’s a bit of a learning curve. On the whole though, it’s been a great move and really added to the financial analysis of the business in terms of tying up the loose ends with the bank accounts and payment streams.

[thedrumdoctor]

This issue was resolved with help from @Glenn and I have learned another lesson in using QuickFile efficiently rather than making things harder for myself!