# General Business Category > Accounting Forum >  Excel spreadsheet for VAT reconciliation

## JaunetteK

I am looking for an excel spreadsheet to use to recon my VAT for the year... anyone have anything like this?

----------


## Sparks

Hi, very simple to make. Excell is extremely user friendly. Draw one up with the help of the "help" files. Just a few basic functions which you will quickly get to know inserted on your template and away you go. If you mail me a rough draft of what you want with fictitious accounts I could throw it together for you. Just me details of how you want to read/print it as well. Weekly/monthly/quarterly ....

Why not use your current accounting program?
I doubt it will not have something for it.

----------


## geraldenek

Are you looking for a spreadsheet for a VAT201 reconciliation or for a VAT control account reconciliation?

----------


## JaunetteK

I am using Pastel, I unfortunately do not know all the ins and outs and sadly cant afford to pay for the maintenance contract with pastel hence them not being able to help me.  The bank that finances us does 3 monthly audits and they have requested an excel reconciliation of each account, including VAT.  They want monthly and yearly  :Frown:   I am pretty new to this so dont even know where to start.  I am sitting with study manuals trying to make heads or tails but am getting quite frustrated....  guess a couple of months and all will fall in place.

----------


## JaunetteK

I am looking for both, our auditors want me to do an excel spreadsheet showing that all my VAT balances.  I printed everything from Pastel but just dont know how to put into excel or how to show the layout....

----------


## Neville Bailey

> I am using Pastel, I unfortunately do not know all the ins and outs and sadly cant afford to pay for the maintenance contract with pastel hence them not being able to help me.


Perhaps I can assist you with getting to grips with Pastel? Obviously you will need to weigh up the difference between paying for Pastel Cover and my fees.  :Wink:  

I'm not sure how much you know and don't know, so I can't even give you a quote at this stage. Drop me an email, if you want, explaining exactly what you need training on, and I will see what I can offer...

----------


## Mike Simmonds

If your Auditors are asking for it they should have a template for you, or at least it should be part of their audit process, otherwise what exactly are you paying them for?  However I have a reconciliation that I have been using for the last ten years in Excel, this is a very useful tool as most accounting software packages only report on the 2 months or 1 month that you pull for the report.  They do not allow for prior period adjustments and processing that does have an effect on the VAT liability. Unfortunately I am new to the forum so I have no idea of how to email this to you or attach it somewhere.

----------


## Dave A

> Unfortunately I am new to the forum so I have no idea of how to email this to you or attach it somewhere.


Hopefully this will help.

----------


## JaunetteK

> If your Auditors are asking for it they should have a template for you, or at least it should be part of their audit process, otherwise what exactly are you paying them for?  However I have a reconciliation that I have been using for the last ten years in Excel, this is a very useful tool as most accounting software packages only report on the 2 months or 1 month that you pull for the report.  They do not allow for prior period adjustments and processing that does have an effect on the VAT liability. Unfortunately I am new to the forum so I have no idea of how to email this to you or attach it somewhere.


thanks, I agree that if they want it they should give me the format but guess life is not so simple...  you can email me on jan@batres.co.za  thanks a ton and the forum is great you going to enjoy it

----------


## Mike Simmonds

> Hopefully this will help.


Thanks Dave, I am giving it a try.

----------


## Mike Simmonds

VAT Recon.xls

Here is the VAT recon, the overall idea is to track cumulative income and expenses and the VAT paid or claimed thereon.  Expenses and credit notes applying to prior periods, that will generally not appear on the reports of Accounting Software programs, are one of the main reasons for continually updating this recon before every VAT submission.

I hope it helps you, but please let me know if you have any queries.

----------

Dave A (27-Aug-11)

----------


## Dave A

Sweet!

----------


## risgila

> I am looking for an excel spreadsheet to use to recon my VAT for the year... anyone have anything like this?


Hi

Did you come right with the VAT Spreadsheet, i m looking for one myself, please if you can

Thank you

----------


## risgila

Hi

mine, i am looking for a spreadsheet that i can use to manually capture the the tax invoices and it calculate the rest, for example: i put the exclusive amount then both tax portion and inclusive are calculated or inserted without me punching the actual numbers and vis vesa

If possible please see attached and assist with make it more user friendly in terms of the above

Thanks

----------


## Sparks

Formulas are basic instructions. Just tell Excel what you want.
Select your target cell where you want the answer, type: =
Then put in whatever you want the answer to be: select the cell with the figure you want to reference
Type in the function you want Excel to perform: +,-,* or /
Then type in the reference or select a reference cell
Now hit "Enter" 

To calculate VAT the formula is "=", "Amount cell", "*", "0.14", then hit "Enter"
To add VAT the formula is "=", "Amount cell", "+", "the cell with the VAT amount created above", then hit "Enter"

Remember to select the cell you want the answer to be in first.
Columns or rows can be filled with the same formula by using the "Fill" function of Excel.

----------


## DFS

Good evening all

Can anyone please assist with a VAT reconciliation template? 

Regards
DFS

----------


## risgila

> I am looking for an excel spreadsheet to use to recon my VAT for the year... anyone have anything like this?


Hi

I hope the attached will help you

----------


## DFS

Thank you Risgila, much appreciated

----------

