Share this content
0
755

How to do a VAT reconciliation?

Assistance with a VAT reconciliation

Hi all,

I was wondering how to do a VAT reconciliation - Is there an easy way to do this? From what I understand you are comparing what is in the vat control account compared to the vat which has been submitted to HMRC? (After googling) Is there any easy way to contrast and compare the data in excel?

I did ask my manager how to do this, however they suggested taking the figure due per the return and balancing the remaining with a directors loan account irrespective of what the trial balance VAT control account says. I feel this is massively wrong and inflating a directors loan account without actual justification. 

Replies

By Ruddles
21st Oct 2018 21:43

Ask your manager to read this comment:

“Manager, you’re a clown.”

Thanks (3)
avatar
to Ruddles
21st Oct 2018 21:50

Even as a graduate I know the accounting treatment is not right, but I just need to learn how to do my job properly.

Thanks (0)
By Ruddles
to monkey101
21st Oct 2018 21:58

So does your manager.

Thanks (1)
avatar
to Ruddles
21st Oct 2018 22:18

I know. But I’ve googled other aspects of my job, but Im fairly unclear how to do a proper VAT reconciliation.

Thanks (0)
to Ruddles
22nd Oct 2018 09:03

Ruddles wrote:

Ask your manager to read this comment:

“Manager, you’re a clown.”

How did this guy get to be a manager ?

Does he do this with the bank too ?

FFS.

Thanks (0)
21st Oct 2018 23:42

No no, please stop. This is torture.

Thanks (2)
21st Oct 2018 23:42

p.s. Stop googling how to do your job and start googling a new job. One with a decent manager.

Thanks (1)
avatar
to Tim Vane
22nd Oct 2018 09:05

You guys hiring?xD

Thanks (0)
avatar
22nd Oct 2018 07:14

Like a bank reconciliation.
Download all the vat account (s) transactions for the last complete vat quarter into Excel, and compare these to the transactions that make up the vat return that was actually submitted

The differences between the two are the reconciling items. Then you need to understand why they are there as it may be a systematic error (timing/coding/misposting etc) that means there has been an under or over claim. Or an adjustment may be needed to allow for eg irrecoverable vat on directors personal expenditure. You won't know unless you do the rec or your manager explains

Repeat for previous quarters...

Thanks (1)
avatar
to WhichTyler
22nd Oct 2018 09:17

Thanks, Sir. I appreciate the help. Just want to clarify this in terms of dates etc.

Assume I have year - end accounts made up until the 30th of April 2018. The last vat quarter which fits the financial year is February- April.

Take all the transaction in the vat control account from February to April and compare it to the last return, noting any differences and do this accordingly for each return?

P.s. Is there a reason you work backwards in terms of reconciling the vat returns?

Thanks (0)
avatar
to monkey101
22nd Oct 2018 09:46

Yes
I usually work backwards because the information is more accessible, memories are fresher, the staff you will esk are (probably) the same ones who posted the transactions... Going back tothe year dot is fine in theory but if you can't reconcile the last quarter you haven't a hope of sorting one from 1985

However if you have a recent (<12 months) point where the VAT account matches the following return, you could start from there...

Thanks (2)
22nd Oct 2018 09:26

What are you a graduate in? A reconciliation is a reconciliation, whether bank, cash, VAT or any other balance sheet account.

Take the balance per the books, the balance per wherever else the balance is (the returns, for VAT) and then find out why the two numbers are different. If one or the other needs adjusted because they're wrong then post the adjustments (or list them and give them to whoever's responsibility that is) and then the rest of the items are the reconciling items you list to get from one figure to the other.

Print it off and staple it to your manager.

Thanks (3)
to Duggimon
22nd Oct 2018 09:35

Duggimon wrote:

Print it off and staple it to your manager.

Top tip so far.

Thanks (2)
22nd Oct 2018 09:33

The first step is to ensure that the balance in the accounts is right !

Then compare the entries to what's on the returns. Pick out the differences, list them as errors to either pay or claim, tot them up to give an adjustment to make on the next return. Then Accounts Balance = Return balance ± the adjustment.

If you're on cash accounting, you'll also need to adjust for timing differences, VAT on creditors and debtors, which belong to your year but aren't payable/reclaimable until a later period.

Thanks (0)
avatar
to lionofludesch
22nd Oct 2018 18:31

Thanks- is there an easy way to do this on excel? Or is it matter of going line by line for the control account vs what has been submitted to hmrc?

The majority of our clients are on cash basis. So all vat on debtors/creditors will be included in the vat control account - is there an easy way to determine vat on debtors/creditors in the control account?

Thanks (0)
By Ruddles
to monkey101
22nd Oct 2018 18:56

Oh dear

The status of your clients (cash basis or otherwise) is irrelevant. I would suggest that you ask your manager to explain. Oh wait...

Thanks (0)
to Ruddles
22nd Oct 2018 22:09

Ruddles wrote:

Oh dear

The status of your clients (cash basis or otherwise) is irrelevant. I would suggest that you ask your manager to explain. Oh wait...

Well, maybe. There's an extra step to the reconciliation as advertised in my earlier post.

Thanks (0)
By Ruddles
to lionofludesch
22nd Oct 2018 22:19

Why would the customers’ basis of VAT accounting have anything to do with your own reconciliation?

Thanks (0)
avatar
By Matrix
to Ruddles
22nd Oct 2018 22:41

My understanding is that Monkey and his manager are advising clients, they are in practice. Instead of training clients how to use software properly, preparing their VAT returns for them or resolving errors, the practice gives HMRC’s money to the Director.

Thanks (0)
By Ruddles
to Matrix
22nd Oct 2018 22:42

Ah I see

I had assumed they were in business.

That makes the manager’s position even more precarious (and explains Lion’s remark)

Thanks (0)
avatar
to Matrix
23rd Oct 2018 09:03

Yes, indeed we are in practice. But I’m not advising any clients. Majority of our clients qualify as micro entities or are one band companies. Not really sure how we’re keeping Hmrcs money from them, when we’re simply posting adjustments to the vat account via the directors loan account

Thanks (0)
to monkey101
23rd Oct 2018 09:25

monkey101 wrote:
Not really sure how we’re keeping Hmrcs money from them, when we’re simply posting adjustments to the vat account via the directors loan account

You can't be sure that you're not keeping HMRC's money from them until you do the reconciliation.

The VAT returns might be right. The accounts might be right. You don't know.

Thanks (0)
avatar
to lionofludesch
23rd Oct 2018 09:59

Oh damn. Didn’t even think about it that way. Learned alot on this thread!

Thanks (0)
to Ruddles
23rd Oct 2018 08:06

Ruddles wrote:

Why would the customers’ basis of VAT accounting have anything to do with your own reconciliation?

Because, even if there are no errors, on cash accounting, the balance in the accounts will be different to the balance on the final return. The extra step is adjusting for VAT on debtors and creditors.

Thanks (0)
avatar
22nd Oct 2018 09:50

Are those the manager's exact words? Might they have suggested posting differences on the bank reconciliation to the director's loan?

Although I'd hope that the work is a bit more precise than posting the difference of two figures - clients are notorious for posting duplicates, which should be reversed rather than assumed to have been paid by the director personally.

Thanks (0)
avatar
to the_drookit_dug
22nd Oct 2018 18:39

Basically the trial balance shows a vat control account of around £7,450 at 30/04/2018. But the vat reurn for the period is from 01/03/2018 to 31/05/2018. The return submitted to hmrc was around £3,200.

My managers solution was to pro rata the £3,200 figure (/3*2) giving £2,133. Post the CR to vat control of £2,133 and DR directors loan account.

Thanks (0)
By Ruddles
to monkey101
22nd Oct 2018 20:01

Good grief. Your manager should be sacked.

Here’s a tip that should help you enormously (in addition to the above) - align your VAT quarters with your accounting year.

Thanks (0)
to monkey101
23rd Oct 2018 08:08

monkey101 wrote:

My managers solution was to pro rata the £3,200 figure (/3*2) giving £2,133. Post the CR to vat control of £2,133 and DR directors loan account.

I'm appalled.

Thanks (0)
avatar
to lionofludesch
23rd Oct 2018 09:06

Seriously we’ve done this accounting treatment for a number of clients. Would HMRC check our journals in the event of an enquiry? We’ve not had a single enquiry.

Thanks (0)
By Ruddles
to monkey101
23rd Oct 2018 10:01

monkey101 wrote:

Seriously we’ve done this accounting treatment for a number of clients.


I'm appalled.
Thanks (0)
to Ruddles
23rd Oct 2018 11:19

I'm appalled too.

Thanks (0)
avatar
By gb4242
22nd Oct 2018 10:40

You are all starting too late, all that you are proving is that the accounts are in accordance with the books of account, a laudable and necessary task.
But the real reconciliation is to see if the liability (or asset) is correct. Check sales records for a quarter and ensure that output tax is = 20% of net sales and then gather information on input tax being 20% of vattable purchases. Then check to vat return made. Any reconciling items should represent timing differences between VAT pont and return quarter.
As for the manager then he is merely a reconciling item between good practice and stupidity!

Thanks (0)
to gb4242
22nd Oct 2018 17:55

gb4242 wrote:

But the real reconciliation is to see if the liability (or asset) is correct.

With all due respect, that is asset/liability verification. It is not a reconciliation.

Thanks (0)
By DJKL
22nd Oct 2018 18:21

Split the day book reports out into vat quarters, so if say March, June etc quarter ends, record in say excel from say sales daybooks output vat for say Jan, Feb & Mar then input vat from PDB re same, any other vat inputs and outputs all recorded by quarter, and then compare with actual returns each quarter to see what, if any, differences arise and why.

Doing an overall for year is great if it works but if the current vat creditor does not agree to what it ought to be (say last three months transactions if vat and YE match) then it will not take you that far forward, splitting it out with a total and figures per quarter ought to be far more useful re isolating the differences and takes very little extra time.

Of course this presupposes you have monthly daybook printouts and in an ideal world a set of vat workings for each quarter.

Thanks (0)
avatar
By Matrix
22nd Oct 2018 18:48

If only there was some software to book all this VAT and show the reconciling items.

Maybe the government are right - there is a place for digital record keeping.

Thanks (0)
avatar
to Matrix
22nd Oct 2018 19:45

We’re cloud based. Everything is done on Quickbooks.

Thanks (0)
to Matrix
23rd Oct 2018 08:11

Paper or software - it makes no odds. Reconciliation is all about the hard slog of checking one list against another and deciding what to do with the differences.

Doesn't matter whether you're using a biro or tick boxes.

Thanks (0)
avatar
22nd Oct 2018 21:38

Don't just blame the manager. Someone's clearly promoted those mad skills into that position ;-)

Thanks (0)
23rd Oct 2018 11:37

Guys, the answers here are unhelpful and unprofessional. I assume you weren't born with an innate knowledge of accounting? So how about rather than mocking someone's curiosity or inexperience, you help.

And if you don't want to help, don't reply.

Thanks (0)
Share this content