Why do I have no VAT due on Sales

[ian_roberts]

@ian_roberts

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.

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.