Help to reconcile cash VAT accounting to BS?

Suggestions needed on identifying a possible error

Didn't find your answer?

I am preparing the accounts for a company that uses cash accounting for their VAT returns. I have reconciled the output VAT to turnover accounting for VAT on opening and closing debtors and bad debt and this was fine. But I cannot reconcile the outstanding VAT to the balance sheet. BS is showing £2500 debit balance not including the final VAT return in the year. After accounting for the VAT on closing debtors and creditors I have calculated a debit balance of £7,000. They are a construction company that provides mostly Z/R services so I'm expecitng a debit balance. I've check for unallocated payments and receipts and there are none. I've gone through the VAT account and there isn't any unsuall adjustments or journals. The company uses Quickbooks Online and the data is in good order. Any suggestions on where to look next....?

Replies (15)

Please login or register to join the discussion.

avatar
By SXGuy
14th May 2020 15:36

If they use quickbooks then why are you not looking at the reports it produces? Surely quickbooks handles the fact the vat is prepared using cash accounting and the bs would show the correct figures?

Thanks (0)
Replying to SXGuy:
nsa newquay
By adam kay
14th May 2020 16:12

Oh believe me I've been using nearly all of QBs reporting feature, but I'm not just finding anything that would cause this problem. Sadly the online version is quite a flexible for reporting as the desktop version, especially with VAT.

Thanks (0)
avatar
By susieq
14th May 2020 15:52

Hi
Does the VAT return run in line with the year end? If not, you will have inputs on payments made after the last return, but these are not included in closing creditors.
Just a thought.

Thanks (0)
Replying to susieq:
nsa newquay
By adam kay
14th May 2020 16:13

It's good suggestion, but yes, the VAT return period does run inline with the year end. I wish that was the answer...

Thanks (0)
avatar
By Ian C1965
14th May 2020 16:25

Is the VAT on debtors and creditors correct? I have seen VAT on debtor and creditor reports given as the VAT on whole invoices even though mostly paid. So, for example, an invoice of £100 & VAT would show £20 VAT on the debtor report even if only one penny remained unpaid.

Thanks (0)
Replying to Ian C1965:
nsa newquay
By adam kay
14th May 2020 16:43

Quickbooks online doesn't have a proper report showing VAT on debtors and creditors. So I exported the usual ledger reports, added a VAT column and check every balance for VAT. I'm triple checked this and since the VAT to turnover reconciliation works, I am confident the debtors report is correct and the creditors reports is mainly one large supplier with VAT charged.

Thanks (0)
paddle steamer
By DJKL
14th May 2020 16:57

Depending on volume of transactions ticking between vat return analysis and NL account may actually be the simplest approach, or try doing the rec at each vat quarter to narrow down the search period for the error (hopefully not too many different errors) A first year apprentice will minimum four different coloured pencils may assist.

Really silly question, the exercise does work with the opening balance at start of year/period, it did reconcile NL to vat report/creditors/debtors?

Second silly question, the difference you have is not one of the required prior year/ period reconciling items?

Thanks (0)
avatar
By Charles_Bennett
14th May 2020 17:53

Is the creditors figure correct? I have seen in construction industry clients their subcontractors entered via the supplier ledger but the amounts shown as outstanding being the net after deduction of CIS, which has been entered as a credit on the bill to a CIS account. If you just take 1/6 of the supplier balance you are therefore perhaps only calculating 5/6 of the actual VAT on the invoice.

Thanks (0)
avatar
By frankfx
14th May 2020 20:50

Why not ring QB support.

But you may have to dig deep with the support person, as they have close to zero accounting knowledge or why the reconciliation is vital for your peace of mind.

Persevere and do not take " no" for an answer

QB often defeats logic.

There are some reports that are not obvious .

Thanks (0)
avatar
By AlexJane
24th Jun 2020 10:40

If I can add into this thread please ......When you say is VAT on debtors correct - what should be the actual VAT on debtors at any month end given there are payments on account at a month end for which VAT can have been accounted for on earlier VAT returns etc? Having done tick backs myself, this is where I am beginning to lose sight of - what am I actually trying to reconcile to?

Thanks (0)
Replying to AlexJane:
avatar
By Matrix
24th Jun 2020 12:19

If payments on account have been made then the debt would not be included in debtors. You just divide by 6.

Thanks (0)
avatar
By AlexJane
25th Jun 2020 09:14

An aged debtors list is a net total of all outstanding, unallocated SL transactions. But VAT due at the month end is not simply that total/6, POA element shall need to be added back as that VAT has already been paid over (but surely it is the TOALl POA made rather than just the unalloacted POA remaining on the ledger!?)

i.e. a client pays £6500 last July - 1/6th of that would be paid over on the July return. As of today £1,200 is the only remaining unallocated element of that £6500 on the debtors list

Add back required to show total VAT due on debtors (invoices) = £6500/6 ? YES

Thanks (0)
avatar
By richael
01st Mar 2021 19:00

Hi Adam,

I work for QuickBooks, and just wanted to let you know that there are now two reports which can show you the outstanding VAT on debtors and creditors. You can add the additional outstanding VAT column by customising the A/R ageing detail report and the A/P ageing detail report. Here is the care article:
https://quickbooks.intuit.com/learn-support/en-uk/accounts-payable/show-...

It's a bit late for your initial query but I hope that helps you to know that it's there now.

Regards,
Richael O'Brien

Thanks (0)
Replying to richael:
avatar
By frankfx
01st Mar 2021 19:52

i note your response.

please look at the thread today

VAT QB Nightmare

https://www.accountingweb.co.uk/any-answers/qbo-and-domestic-reverse-cha...

Please kindly provide a swift and comprehensive anwser and solution.

Your key customer case are caught by this, as seen on TV.

ACTION

Reply by 2nd March 202 1 ?

thankyou

Thanks (0)
Replying to frankfx:
avatar
By richael
02nd Mar 2021 10:26

I can completely understand your point of view and I know that our Product team are working CIS as a priority. If you head over to the quickbooks community article, you can join the conversation where an expert can help - https://intuit.me/383Key5

Thanks (0)