It's much more fun, when you can discuss your problems with others...
You are not logged in. Please login or register.
FrontAccounting forum → Reporting → UK VAT - Box 7
Hi,
Could someone advise me RE getting the total for the UK VAT return box 7?
I think the best way currently is to get a Supplier Balances report to get the purchases total - and then deduct the VAT paid from the VAT report.
The only problem is that this is not correct if there are any credit notes etc.
Or is there anyway to get the Tax Inquiry report to include transactions which do not have any VAT on them?
If you create a TaxType of 0% and use it a TaxsGroup, called exempt or so and then place it on the supplier, you can pick up the tax history on the printed tax report.
Ahh...
So are you saying that the amount which is in the Outputs/Inputs for taxes paid only includes the amounts from suppliers who have a TaxType set?
Tried this and it didn't seem to work.
All the non-vat suppliers were already in a group called NON-VAT. I added a 0% tax and added it to that group but it looks like the total is still coming out with the non vat transactions missing.
Currently the only way I've found is this.
Pull up a Supplier balances report for all suppliers and all currencies.
Go through the whole report and mark all the transactions which are invoices - and mark all credit notes.
Total up all the invoices. This may not be too bad - the total for charges should just about be correct - may need a couple of adjustments.
Total up all the credit notes.
Deduct the credit notes form the invoice total.
THEN IMPORTANTLY.
Deduct the VAT on purchases as worked out in the tax report.
You should then have the total purchases without VAT for box 7.
NB - VERY IMPORTANT ALSO.
This method assumes that you have all your transactions which should be added up for box 7 as supplier invoices. So, for example, you should put your bank charges as supplier invoices as they should be totalled into box 7. Box 7 should contain all VATable, zero rated and exempt transactions. All transactions which are 'outside scope' (bridge tolls, MOT charges) should be entered as general ledger transactions so they don't get included in the Suppliers balances report.
Box 7 is a big pain indeed to get.
For supplier you can as joe said create a non-vat group (to not get supplier credit not and export) you can create 2 separate VAT group a NOVAT-export and NOVAT-import group.
However be aware that if you pay VAT at the custom, the input imported will appear twice (as FA reconstruct a fake input amount from the VAT amount).
As for 0-Vated transaction entered as normal payment (like post-office or food), the best I found so far is to add '0VAT' in the memo (on the line) and get the sum with a SQL query.
FA is still not UK VAT friendly at all :-(
Can I firstly say that I am very new to FA. That said, I am very impressed with it, and would like take this opportunity to thank you for publishing this fantastic software.
I would like to add some comments about VAT of my own. (I should also say here that I am not as familiar as I would like to be with sql and/or php, and only understand it at a very basic level).
I am working in a small family-owned and run business in the UK. The business is fully registered for UK VAT, and as such has to account for it, quarterly on an accrual basis.
Further, the business deals with some customers and suppliers within other areas of the EU, which in turn means that Intrastat Reports need to filed as well as normal returns.
I am at this time only testing FA, with a view to putting into full use. I will shortly have a budget available to rollout a new system as part of our final migration to Linux platforms, and will certainly be making a payment to FA.
However, correct and convenient handling, recording and reporting of VAT is something of a key issue for us. It seems to me that there is an issue with the way that FA handles taxed items where the value of the tax is Zero % for whatever reason. This would include transactions (sales and/or purchases) perhaps because the supplier is not registered for VAT, or the transactions fall within the scope of the EU cross-boarder/Intrastat rules. It is therefore not a question of being able to have a tax figure, (which by it’s very nature will of course be Zero), but that of the turnover (sales and/or purchases) associated with those transactions. This in turn would render incorrect values being available for boxes 2, 7, 8 & 9.
So far, I am testing a ‘work-around’ using the Dimensions area. (with some apparent success at least). By setting-up Dimensions firstly for UK Zero Rated Supplies, plus a further for EU Supplies, plus a further for EU Sales, and then appending those to each Customer or Supplier as is relevant, I am then able to use the following reports to extract the additional information needed to supplement the Tax Enquiry and Tax Reports. (Specifically using the Dimension Summary, and the Sales Summary Report using the ‘Tax ID’ filter as a means of identifying customers that are outside of the UK). I have found that this report is of most use when exported to a spread-sheet and suitable filters applied. It would however be most useful if it were possible to apply date ranges to the Dimension Summary Report which it is not currently available as far as I can see.
I will continue with my own testing and learning processes, but I would be most interested to learn if anyone here has either comments on my own work-arounds to make, as well as any other suggestions or ideas or other ways of “skinning the VAT cat”
Thanks
Xero
Hi,
In Malta and perhaps the rest of Europe, we have the same issue with the Zero % VAT. I think the way I setup FA, I get the reports correctly. I did the following:
1. Create a GL Acount ex 2220 VAT - Exempt without Credit (you have to create an account for each different type of VAT)
2. in Tax Types (under Setup) create the Tax Type you need to follow, for the above example:
VAT (Ex w Credit) 0.0 2220 VAT - Exempt with Credit/Exports 2229 VAT - Exempt Purchases for re-sale
3. In Tax Groups place this VAT into either the Domestic VAT or the Intra-Community & Non-EU Trade Group
4. In Item Tax Type create an entry such as: VAT Exempt w/o Credit and uncheck the box that is relevant to this entry.
5. It is important that you set up your Customers and Suppliers in the correct group for TAX type
6. Also items have to be placed in the correct type of VAT category.
The only problem I found with the above is when an item that is placed in the group to be charged VAT for sale, but is bought as a capital expense. In this case the use of Direct Invoice does not produce the correct results.
Please note: I am not an accountant and although I have checked the above with my accountant, I might be missing something important. So please check thoroughly the above and if possible report back with success or failure.
Carmelo
Carmelo
Thank you so much for your thoughts and comments.
I must say that I have found the tax part of the FA Wiki somewhat hard to follow, and clearly, I had misunderstood some of the concepts expressed there. However, your words have thrown a new light upon it for me.
I guess that it's all about the setup, not just of the Tax itself, but also of the Items, Customers and Suppliers too.
Meanwhile, my testing and evaluation is now moving along nicely courtesy of your guidance.
Many thanks once again
Xero
What would be nice is either:
1. On the Sales side we have a Sales Summary Report. It would be great if we had the equivalent on the Purchases side. This would give us a total of the purchases.
What I have done is put all of the purchases which should be in the UK VAT Box 7 on the Purchase ledger (includes Bank charges, rent etc). Anything which should not be in Box 7 (Road tolls, MOT payment etc) goes on as GL transactions.
That way the total of the Purchases report would be fine for us.
2. On the tax report have the following option.
'Include all purchase invoices (including ones with no VAT) in the Outputs/Inputs.
Currently we are pulling out the Supplier Balances report, going through all the listings and totally up the invoices and deducting credit notes and IMPORTANTLY deducting the VAT from the total - this gets us the Purchases total for Box 7.
Just to be clear...
We pull the Supplier Balances report out as a spreadsheet. Then go down the 'Charges' column and delete all 'Totals' and 'Open Balances'. Then get the spreadsheet to total up the invoices in that column.
We then look for all the Credit notes (which are in the Credits column) and total them up. These are then deducted from the invoices total.
In the UK we also need to deduct any invoices which are generated from suppliers who are outside the EU.
Lastly - IMPORTANTLY - we then deduct the VAT on purchases (from the tax report) from the total. This then gives us the amount which is suitable for Box 7.
NB - for this to be correct all purchase transactions which should be in box 7 should be entered under the Purchases section via a supplier. This would include bank charges, rent etc. All transactions which should NOT be in the VAT purchases total (MOT, Local authority charges, bridge tolls, etc) should be entered directly as GL transactions - this means they will not show in the total for the Supplier Balances report.
I was thinking about trying to create a generic tax report for FA that would create an Australian BAS, as well as an NZ GST or UK VAT report, and have the options of creating a journal to balance off the GST/VAT accounts and create a payment to the tax office. Please have a look at kwikpay.co.nz/download/bas.docx and let me know what you think.
From your description I think it may need improving to handle UK VAT, but I'd like your feed back.
Thanks
Alastair,
My comment from the point of view of UK VAT:-
Such a report and ability to create a payment journal 'on the fly' so to speak would be most helpful.
As a general comment, the way in which FA handles and reports on VAT is the only reason that has delayed our using it in our production environment. I am neither an accountant or a PHP programmer, and have had a great deal of trouble following the wiki, and getting the required information in a reliable and structured manner.
I'd be willing to pay for functionality of this sort
Xero
Xero,
I wrote a small script to fetch the figures directly from the database, without involving FA at all, which allows me to create monthly (Swedish) VAT reports, and also generate a balancing transaction.
The fact that it is done well outside FA may be a problem, but in my case it's rather an advantage since it can easily be run from a cron job. Also, the balancing transaction has to be processed (in my case using curl) by the "Import Multiple Journal Entries" plugin, but that is not an issue since I already happen to use it to import daily sales figures from a web server.
There isn't much "programming" involved. Just send a few queries to the database and assign the results to the appropriate fields, before outputting an XML file. (I'm not at all sure if HMRC will accept files for upload, so you might just want to print/email the report for someone to enter the figures on the website. The same might apply to the balancing transaction, unless you already happen to have some means of importing it. Or feel adventurous enough to insert it into the database yourself. As for me, I was quite happy not to have to go down that route.)
Most of the work is actually mapping your accounts (or combinations thereof) to the fields on the form, so you shouldn't have to be much of a programmer to create something similar. Besides, any language would do as long as it allows you to access a MySQL database. (PHP does have the obvious advantage of running on any server that runs FA, though.)
I'm all for doing it "properly", but this just might point you in the right direction for getting something up and running before that happens.
And, for what it's worth, the wiki doesn't make sense to me either... 
Hello, regarding the Wiki, you are welcome to participate with updates/corrections.
/Joe
BTW, thanks for your tips.
/Xero
AS I said, the main problem of the Box 7 is the "0 VAT" transaction problem. This has been sorted in FA but not released yet, because it's still under test, but it should be release soon.
Once it's been release doing a UK VAT report should be straight forward. But you'll need to have run the full quarter with the "new" code and configuration, so all the 0 VAT transaction are recorded properly. By that time, I probably would have done a UK VAT report compatible with the new version and so write a report as well.
Just be patient (and do it manually for now)
/Elax
Elax,
Many thanks for more of your comments and thoughts. The prospect of extending the functionality of FA in the way you describe sounds very interesting. I am of course resolved to be patient and look forward to seeing the fruits of your good work in the future.
tm,
Maybe I should try to make the time to learn more about the mysteries of PHP and SQL. I am reasonably happy with simple command scripts, but many of the concepts in programming languages that look so dynamic I do tend to find rather intimidating.
Once again - everyone - thank you so much for your help
Xero
/Xero
AS I said, the main problem of the Box 7 is the "0 VAT" transaction problem. This has been sorted in FA but not released yet, because it's still under test, but it should be release soon.
Once it's been release doing a UK VAT report should be straight forward. But you'll need to have run the full quarter with the "new" code and configuration, so all the 0 VAT transaction are recorded properly. By that time, I probably would have done a UK VAT report compatible with the new version and so write a report as well.Just be patient (and do it manually for now)
/Elax
Waiting for the EU VAT problem to be solved to move my accounts to FA. My hopes are on the release of FA 2.4. Any idea when?
@demille
The current version 2.3.17 already includes some VAT improvement which should be enough to deall with EU VAT.
For example there is a new column in Journal Entry form allowing you to enter the amount of corresponding to a VAT account entry. This allow to enter 0-VAT.
/Elax
@demille
The current version 2.3.17 already includes some VAT improvement which should be enough to deall with EU VAT.
For example there is a new column in Journal Entry form allowing you to enter the amount of corresponding to a VAT account entry. This allow to enter 0-VAT./Elax
I have 2.3.14 for the moment. I will upgrade to 2.3.18 and check this out. Thank You for the info.
As I can remember it was not a problem to enter 0 VAT, the problem was to get the totals of 0 VAT turnover for VAT-tax declaration.
As I can remember it was not a problem to enter 0 VAT, the problem was to get the totals of 0 VAT turnover for VAT-tax declaration.
Which is essentially the same problem. You couldn't get the total of 0-VAT because they were no ways to tell FA the net amount of 0-VAT transacation. Now that you can, you'll get the 0 VAT turnover just as any other VAT turnover (as long as ALL 0-VAT transaction have been entered properly).
/Elax
FrontAccounting forum → Reporting → UK VAT - Box 7
Powered by PunBB, supported by Informer Technologies, Inc.
Currently installed 4 official extensions. Copyright © 2003–2009 PunBB.