Deleting (very) old data and transactions

[sageNoob]

Sage UK - version 25.1.128.0

Our company has been using Sage for ~20 years. It seems that we have never actually deleted old invoices and cleared audit trails.

As a result, we have invoices dating as back as 2002 and transactions from 1997... it's a nightmare.

As a test, I created a copy of the sage data and cleared all the old invoices. The difference in execution time of ODBC queries was night and day (15 sec vs 0.05 sec).

Therefore, we've decided to Archive all old data from before Apr, 2019. I am however, a noob when it comes to accountant stuff (I'm more in a software role) and I don't know much about how transactions/audit trails or VAT works. So can anyone give me some guidance on how I'd go on about doing this?

Thanks!

[brucedenney]

You need to be EXTREAMLY careful.

You need to find the last year end in the audit trail, it will be a long series of journal credits and debits typically ending with a transaction to retained profit and loss 3200.

Once you know where the year end journal is, you need to check to see if any transactions since that journal have been backdated.

The easy way to do this is to run the audit rail report for transactions after the last transaction in the year end journal and dated before the last day of the previous financial year (the date of that last journal)

If there are any backdated transactions, you can not clear the audit trail to the previous year end, you can only clear it to the date of the earliest transaction you found less one day.

All the data should already be archived, so you can delete it with impunity. BUT before you do, check File>Open>Company Archive and make sure you have got one.

If you don't have one, (unlikely) you can make a mid year archive.

So now you can clear the audit trail, use searched to find old invoices, sales orders, purchase orders and delete them, you can then delete old customers suppliers and nominals that now have no transactions. You can also clear the stock and delete any old products.

My year end guide covers most of this.

https://www.makingithappen.co.uk/sage-5 ... earend.htm

You could also hire someone like myself to do it for you, if you are nervous or unsure of what you are doing.

[sageNoob]

Hi,

I tried running the clear audit trail option on my copy of the sage data. It only deletes about 25% of the transactions, lower than expected and we still have transaction data remaining from ~2000's.

We seem to fulfil the 'checklist' on your webpage... do you think it's because we switched Sage versions in between?

Also, what will you be looking to charge for your services, if we need them?

[brucedenney]

Switching versions is not the issue.

I suspect the issue will be a bank account that has not been reconciled, a VAT period that has not been reconciled or unmatched / unpaid transactions. There are also some scenarios that cause problems, where accounts are never paid in full, in those scenarios you need to create a break in the transaction chains.

Look from the beginning of the audit trail for any N's on Paid, BankRec or VAT Flags. If you see any they are transactions that will stop proper clearing.

Untitled.png

I currently charge £74 per hour and charge by the tenth of an hour. Prices exclude VAT.

[sageNoob]

The early transactions (~ year 2000) have a "-" (hyphen) in the Bank column or the VAT column.

There are a few "N" in Bank but the vast majority of the old transactions have a "-".

I actually called sage support and they have suggested to either make a new company on next year end OR simply delete the invoices older than 2017 in the Invoice module.

The first suggestion is good but offers no immediate improvement. The second option will give me the boost in data transfer speed that I need but I'm worried about what will the consequences of deleting these old invoices all willy-nilly. This would keep the Transaction data as is but would essentially drop the unnecessary rows from the INVOICE and INVOICE_ITEM tables, as I require.

I don't know what to do here... thoughts?

[brucedenney]

- hyphens are not a problem.

You need to find the bank account with the N's and go an reconcile it or set it as not needing reconciliation and then clear the audit trail again.

Deleting the invoices form the invoicing module, will have no impact on the audit trail so whilst worth doing in its own right (I cover it in my guide) it will not help with this issue.

[sageNoob]

- hyphens are not a problem.

You need to find the bank account with the N's and go an reconcile it or set it as not needing reconciliation and then clear the audit trail again.

Deleting the invoices form the invoicing module, will have no impact on the audit trail so whilst worth doing in its own right (I cover it in my guide) it will not help with this issue.

How to set as not needing reconciliation? Can I set it temporary just to clear the transactions and then flip it back on?

We found 32 transactions that are unreconciled, before 2017. There's many more after that for the year 2020 (since the year is still on going). I only need to fix the ones prior to 2017 right? (i.e. the date I'll be selecting for clearing audit trail).

Finally, we have quite a lot of VAT column unreconciled (60k+). Do we need to fix them or that's okay?

Thanks a lot.

[brucedenney]

If you go to the bank account record there is a tick box to have no bank reconciliation on the account, you can untick it after clearing the audit trail.

Screenshot 2020-11-02 113503.jpg

Yes you need to deal with the VAT reconciliation, it appears that you have backdated unreconciled transactions in your accounts, this is not uncommon, normally we include these in the VAT return at the next run as we go, typically these are late entered purchases which we want to claim the VAT back on.

If you are really unlucky then you could have back dated sales and a VAT liability, if this is the case you need to rectify it on the next return to avoid the penalties. Should you have a VAT inspection before you rectify it you should declare it at the outset so as to not get penalties on it.

If you know the period and you know why the return was not reconciled then you could do a dummy return to account for the vat and not submit it. You will need to keep copious documentation to explain this to HMRC should you get asked.

I have encountered some serious misunderstandings about how to do the VAT return including people who ran if for the date range and never reconciled the VAT return. These can be an absolute nightmare to fix and to get to grips with.

I would be inclined to run a VAT return to the end of the last period and include the unreconciled transactions to see what has been missed and most importantly answer the question of why those transactions did not go on previous returns. If you have made historical mistakes then you need to rectify them on the next return. It could be that these are purchase transactions you have never claimed and you have a substantial VAT refund due, you need to run it and find out.

You need to proceed with caution here, if you make a mistake that is in your favour and not in the favour of HMRC, it could be considered tax evasion and you could face serious personal consequences. You must understand why those transactions had not gone on VAT returns in the past. You must have an explanation of why on every vat return since you did not include them (as Sage will prompt you at every run to include them).

Hopefully it is not a serious issue, and the amounts involved are small.