Doh!!

I am getting the Sage line help number in the morning.  Will feedback.  I had a previous b up but even when I restored and ran checking any changes, I still get different figures.

I backed up the week before.  This week, I processed transactions then recd.  once I realised,  I restored to last back up, looked at last transaction number, went back to updated b up and made a note of transactions that would effect the VAT after that, applied them to the old version then ran the return.  Close but not right!!!!  I will have to scan through unless Sage suggest better.

Although you can't retrospectively run one of the VAT reports, it's possible to 'recreate' the information, albeit in a slightly different format, and with a little bit of effort. I was presented with this very problem when I was called in to sort out someone's mess a year or so back. The way I did it was to add a suitable criterion to an audit trail report, run that and export the data to a CSV file for loading into a spreadsheet, then adding a few more columns to analyse the net and VAT amounts according to the transaction type.

In more detail (which I will actually do for my most recent VAT return in order to jog my own memory...)

Go to the "Financials" screen and click on "Reports". Click on "Audit Trail", then right click on the "Audit Trail (Brief)" report and choose "Edit" from the menu.

When the editor pops up, click on "Reports" on the menu, and then on "Criteria".

Click on "New Criteria" and select "AUDIT_SPLIT" for the first part of the database field, then "VAT_RECONCILED_DATE" for the second. Click on "OK" and "OK" again in the next dialogue. The new criterion should now be listed at the bottom of the list of criteria, so click "OK" to go back to the main report designer screen.

Now click on the "View" menu and then "Preview" - the "Criteria Values" dialogue should appear, with "VAT Reconciled Date" being at the bottom. Click the button next to it (which should currently say "Between (inclusive)" and change it to "Is", and then enter the date of the VAT return date (the quarter end date) in the date field. Click "Okay".

A complete list of all the transactions that were included on the VAT return in question should now appear.

Now, obviously that is just a list of the transactions, and it doesn't show which ones went into which box, and nor is it totaled. This is where the spreadsheet comes into it.

Click on the "File" menu, and then "Export". Navigate to somewhere sensible (eg the documents folder for the client on your computer), change the filetype to CSV (where it says "Save as type" at the bottom) and click "Save".

Now you need to load that CSV file into your preferred spreadsheet - so either load your spreadsheet application, and use "File" and "Load" to load it, or find the file in your documents and double click on it - doing that should load it into your spreadsheet.

Insert a few of rows at the top for headings so that the actual data starts on row 4, and put some suitable headings for the existing columns of data.

This is where it gets complicated...

You *could* head up the blank columns as "Box 1", "Box 2" and so on, and then put in long and messy formulae to pull the VAT or Net amount in depending on the contents of the "Type" column.

However, I'm going to suggest the easier route: We're going to head those columns according to the transaction types, and two will be needed for each one (net and gross) - but don't go (for example) "SR Gross" and "SR net"; instead put the words  "Gross" and "Net" in the cell above (or below) the type.

And don't try to head them all up first; it's simpler to go two columns (one type) at a time.

So in your first two free columm (which is 'K' here) put the transaction type of the first transaction listed (which might be "SI" for example), and below the first put "Net" and below the second put "VAT". Here that means "SI" is in cell K1 and L1, and cell K2 contains "Net" and L2 "VAT".

Note: I'm using OpenOffice Calc, so my formulae will be using its format; I think Excel uses a comma instead of a semi-colon to separate fields.

Now, in cell K4, enter your formula:

=IF($C4=K$1; $H4)

And in L4

=IF($C4=L$1; $J4)

What these formulae do is read the net (in column K) and the VAT (in column L) if the type is "SI" (in my example).

Copy the two formulae all the way down to the bottom of the data.

Go back to the top, and scan down for the first gap; this is the first transaction with a different type - note the type.

Highlight columns K and L, and press CTRL-C. Move to the top of column M and press CTRL-V. Now change the transaction type at the top of M and N to the transaction type noted above.

Go back to the top, and scan down... by now you should get the picture.

Once you have two columns for each transaction type used, add totals at the bottom of them - and from these, you should be able to see that (eg) the "SI" and "BR" "VAT" totals matches "Box 1" for the return, and so on.

Tidy the report up. Print it out. Say hello to Uncle Bob.

It might be a touch more complicated if your VAT return uses the EC boxes/codes - in which case, it should still be possible in theory, but you'll need to edit the starting report further, and do a little more work on the analysis. I haven't done that, and I don't have suitable data to work on to try it. But it should be possible with some thought. :)