Hi I must be doing something seriously wromg here.
Previously to calculate my VAT we took the total of VAT purchses as a proportion of total purchases. SO say we had 25,000 of total purchses and out of that £5000 was Vatable then that proportion 20% was then multiplied by our sales to get the figure we owed.
When I come to do my VAT return in Quickfile the amount to be reclaimed is correct but the box for VAT due on sales is empty. WHat am I doing wrong?
I guess it is something I am doing in settings. I have checked both cash or acrrual accounting and that doesn’t change anything.
SO my question is how can I get quickfile to input the VAT due on sales into my tax return?
Thanks for your help
Hi I must be doing something seriously wromg here.
If you download the calculations from the return, are any sales invoices included? Are you on cash or accrual accounting?
Hi Mathew Thank you for your reply.
I don’t actually know what cash or accrual means!!
. As I said above all I did before was work out the proportion of purchses that was Vatable multiplied that by our sales to get the amount owed.
When I input our daily takings I leave the VAT blank as I do not know what proportion of the sales is Vatable. Is this why there are no VAT due on sales. How can I get qucikfile to work out the VAT using the methjod that I used? Is that possible?
I don’t think QuickFile has any special support for the retail schemes so you’ll still have to do the calculations yourself. Project tagging may help you here, if you tag all your purchases that are actually goods for resale (as opposed to overheads like energy bills etc) then you can easily extract the total including VAT and the total amount of VAT on those purchases for the quarter to do the retail scheme calculation:
Total VAT on these purchases, divided by gross total of the purchases, multiplied by gross total of your sales
(This isn’t exactly the way they tell you to calculate it in the apportionment scheme guidelines but mathematically it’ll always give the same result). Then you’d enter this amount as a manual adjustment to your VAT return adding it to box 1 and subtracting it from box 6. Finally you’d need to journal the VAT amount over from credit “manual adjustments” to debit “general sales” to deduct the VAT from your P&L.
Hi, thank you for your reply.
When I raise an invoice for a purchase that is for resale I do not get an option of goods for resale in the drop down menu. What I have chosen is all goods for resale is general purchases…
Have I been doing this wrong? Should I choose another category for items bought for resale? Do I have to go through every invoice and change this?
I see when I do raise an invoice that as you say there is the option for a project tag.Does your suggestion mean I go through every invoice for items for resale and ascribe it to a project called goods for resale?
Once again thanks for your help. I am totally new to all this.
A further query to the above.In my profit and loss report it does separate out the General purchases as Cost of sales so that category is probably right? Is that right?
I get a grand total of general purchases for any given time period but no option to see what proportion is Vatable.When I click on an individual invoice the VAT has been separated out. SO how can I see the breakdown of General Purchses nett and gross of VAT so I can get a proportion of sales that are VATable.?
In you answer you say to manually adjust the VAT return . I have registered for MTD so as far as I see I get no option to manually ascribe values as these are supposed to be automatically filled by the software. How do I get access to my VAT return to manually input the figures if using MTD?
thanks again for your help
I have figured out how to manually input VAT values into the return DOH .
So all that’s left is to separate out the VAT included in the general purchases total?
Hmm, doing this accurately is actually surprisingly fiddly. Ideally you want to start not from a simple search of purchases within X date range but instead from the VAT return “download calculations” backing report which tells you exactly which purchases you’re claiming back VAT for on this return. For each of those you then need to determine the VAT and gross totals that pertain to goods for resale (which may or may not be the same as the overall purchase total, if you have a mixture of general purchases and other things on the same purchase invoice). The two ways I can think of to do this are either using the API or cross referencing the QF… numbers with the purchase items CSV from a full account backup.
There may be shortcuts you can take to do this manually, for example if you know your purchases for resale are always certain suppliers but not others.
Like I say, fiddly, but I can see how to script it quite nicely and I might have a go over the weekend now you’ve piqued my interest. Are all your sales done under the apportionment scheme or do you have some that you do with normal VAT invoices?
Picking up on this specifically, the MTD guidelines do allow for manual adjustments to implement things like retail scheme or partial exemption corrections that by their nature can only be calculated out at the end of the quarter/year. See “example 7” at https://www.gov.uk/government/publications/vat-notice-70022-making-tax-digital-for-vat/vat-notice-70022-making-tax-digital-for-vat#examples-of-where-a-digital-link-is-required
Thanks for your reply.
Everything under general purchases is for resale. But for example a total for an invoice may be say £100 but only say £10 is vatable so when I raise the.invoice I manually put the VAT paid as that applied to the £10 ie £1.66.
We sell a mix of vatable and non vatable goods so each invoice doesn’t have the straight 20% vat paid on it but will be a proportion of the total.
As the VAT has been separated out for each general purchases invoice it would be certainly useful to see the nett and gross totals. I am surprised Quickfile doesn’t have this option. Surely I can’t be the only user who uses a retail scheme like ours.
So once I know the total for the vatable purchases I will have to manually work out the proportion of sales that are Vatable by using this amount. I then manually input this figure into my VAT return
I have enjoyed learning to use QF but only really joined to send my VAT via MTD. if I had known that there was no way of using QF to automatically submit my return using my retail scheme I would have looked for one that did.
Thanks again Ian for your help
Pretty much, yes. Like I say the calculation can actually be simplified because if you just calculate the total amount of VAT you’ve paid on purchases for resale and divide it by the total value (including VAT) of all those purchases, that gives you the effective VAT proportion on your purchases for that quarter, which might be say 0.0436 (4.36%). You can then multiply your total sales by the same proportion to get the apportioned sales VAT.
Mathematically this will give you the same result as working out the proportion of purchases that are vatable at 20% and then treating the same proportion of your sales as 20%-VAT-inclusive, but it’s a simpler formula to work out.
The information is there but it’s spread around in different places. If you “modify columns” in the purchase view (it’s a tiny little light grey on white button at the top right, easy to miss) then you can switch on net and VAT columns in the purchase view, and then if you “export data” from one of those views you will get the net and VAT columns in the CSV export and while that doesn’t include a total you can add them up in Excel. This doesn’t show you the nominal codes however (since one purchase can have several lines assigned to different codes).
The other place is if you do a full account backup, then you get a ZIP file full of CSVs. One of those is “Purchase_Invoice_Items.csv” and that one has one CSV row per purchase invoice line rather than just one per purchase invoice, and therefore that does include the nominal codes and the net and VAT amounts for each line.
So you can cross-reference the QFNNN numbers in that CSV with the list of purchases from the VAT return backing report, filter those rows that have nominal code 5000, and add up the values from those lines to get your total net and total VAT (and add the two to get the total gross - SUMIFS is your friend here), then plug those numbers into the final formula to get the sales adjustment.
HI Ian thanks for your informative reply. I see the modify columns view on the purchase section. What would be handy is the same facility in my general purchases view
As you can see no option to separate out the VAT in purchases for resale. Unless I am missing something?
The trouble is the nominal code view knows nothing about VAT - when you create a purchase invoice it posts the net to the code you select (general purchases), but the VAT goes to the purchase tax control account on your balance sheet. The contra entry for the gross total including VAT goes to the creditors control account, and that is netted off when you actually make payment.
As an example of this, here’s a spreadsheet I created by doing “download calculations” on the VAT return preparation screen. I then imported the
Purchase_Invoice_Items.csv from my most recent backup as a second worksheet in the same book (this was on Google Sheets rather than Excel but the principle is the same). I could then put a couple of formulas against each “PURCHASES” and “PURCHASES FROM OTHER EC MEMBER STATES (Products)” row:
The “net” formula is
=SUMIFS(Purchase_Invoice_Items!G:G, Purchase_Invoice_Items!$B:$B, "="&B110, Purchase_Invoice_Items!$D:$D, 5000)
i.e. sum all the entries in column G (the net) where the QFnnnn purchase reference matches this row and the nominal code is 5000. The formula for the VAT is the same but
H:H instead of
G:G (same row filters, but sum a different column), and the gross total (column J) is
=H110+I110 etc. You can see some different examples in the screenshot, though the “vat” numbers are all 0 as these purchases were all zero rated food items: QF02780 is a multi-line purchase where only some of the lines are code 5000, QF02594 is a completely different code (so the
SUMIFS give 0), and QF02599 is entirely 5000 (so the
SUMIFS results match the purchase totals in columns E and F).
Your final sales VAT is then the sum of column
I, divided by the sum of
J, multiplied by the total of your retail sales.
This topic was automatically closed after 7 days. New replies are no longer allowed.