Anonymous
Share this content
16

MTD and multiple sheet spreadsheet

MTD and multiple sheet spreadsheet

Didn't find your answer?

Hi all,

We currently prepare our VAT return in excel and with the end of the transitional period in sight we do have a question regarding the work done in the spreadsheet. After the excel spreadsheet is automatically populated some information is classified wrongly (example: EU sales instead of an export, Export instead of an outside scope,...) This is caputered while preparing the VAT return and the transaction data is then move to the correct sheet (there is a sheet for domestic sales, EU sales, Export sales,...). This is done by copy and pasting from the wrong sheet to the correct sheet.   Section 4.2.1 of the MTD notice mentions that "HMRC does not consider the use of ‘cut and paste’ or ‘copy and paste’ to select and move information, either within a software program or between software programs, to be a digital link.".  Since cutting and pasting the transactional data from one excel sheet to another excel sheet (same excel workbook) is obviously 'within a software program' we are wondering how others solve this? There will always be errors and these are captured while preparing the VAT return... 

Replies (16)

Please login or register to join the discussion.

By SteLacca
25th Feb 2020 13:13

It's impossible. MTD improves accuracy, and so corrections aren't necessary. HMRC said so, Guv.

Thanks (2)
avatar
By paulwakefield1
25th Feb 2020 14:15

I think this is one case where cut/copy and paste is potentially quite risky.

How it can be tackled depends on the nature of your source data.

My initial reaction would be to have a data validated column classifying the transactions. For errors, just change the classification. With the use of filtering, sorting and/or find and replace, this could be done very quickly for multiple transaction corrections. But the source will drive the solution.

Thanks (0)
avatar
By SXGuy
25th Feb 2020 14:35

The best method would be to find out why your source data is so wrong in the first place and improve on it. If manual intervention is needed to copy figures over to another spreedsheet then something must be very wrong with the original way you capture the information.

Or perhaps an easier way would be to have 1 spreadsheet which you can correct and allocate properly and also use to submit the vat return from.

Or the 3rd option would be to have yet another sheet which imports figures and sorts it all automatically.

Thanks (0)
Hallerud at Easter
By DJKL
25th Feb 2020 14:42

What are you copy pasting, individual transactions or sub totals of these?

If the former then if each individual transaction is in the excel sheet how it got there is academic. I prepare one of ours with a CSV file downloaded and manipulated and then individual transactions copy/pasted into excel cashbook, that is fine, the cashbook is the first book of prime entry, the sheet I use for the download is more akin to a painter's palette.

Thanks (0)
avatar
By Victor28
25th Feb 2020 15:02

Hi all, Thank you for replies.

A bit more information. The different sheets in the excel workbook are populated with macro's. Every single excel sheet in the workbook contains a macro with some conditions. Example: The macro of the first sheet downloads the domestic transactions. The macro of the second sheet downloads the EU sales,... . This works fine for 99.5% of the cases. There are however situations whereby the initial classification is not done correctly the first time in the accounting software. (example: a transaction that is an actual export is actually an outside scope of UK VAT sales.) While preparing/reviewing the correctness of the VAT return, this transaction will be identified and classified correctly by cutting the whole transaction date (invoice number, date, amounts,...) to the correct sheet in the same workbook.

Each workbook makes a total that links to the VAT return which is also in that same workbook.

Thanks (0)
Replying to Victor28:
avatar
By paulwakefield1
25th Feb 2020 15:31

Personally I would prefer a single data table including a column for the type of transaction. I imagine that the data import contains the type information; if not, it can be easily added. This would then just require an edit of the transaction type on those items with errors.

Lots of ways of achieving that although Power Query may well be a good way forward if you are having multiple downloads - Dump the lot into a folder and PQ can just tidy them up, keep only the relevant data and combine them all. You could use a pivot table to analyse the data. But there are many alternatives.

Out of interest, can the accounting software not produce a VAT return which can be exported as a csv file for an MTD compliant submission? It would seem simpler and then all the corrections could be done in the original software and just totals for Boxes 1-9 exported.

Thanks (0)
Replying to paulwakefield1:
avatar
By Victor28
25th Feb 2020 16:30

Working with different tabs has the advantage that it is very clear which sale is reported as which type of transaction (export, EU sale,...). Working with a single data table would eliminate that. That's why we would like to keep it.

The reason why the accounting software does not produce a VAT return is that it is an old system. They are working on moving to another accounting system in a couple of years.

Thanks (0)
By ireallyshouldknowthisbut
25th Feb 2020 15:45

The chances of HMRC sitting behind you whilst doing your spreadsheet seems pretty slim to me.

Id not beat yourself up about a theoretical VAT inspection when they watch you do it, and you actually tell them...... At which point all they can do is what exactly? Advise you to do it properly and not be a naughty boy? So long as you file via MTD its "box ticked aren't we great" from HMRC's POV.

Thanks (0)
Replying to ireallyshouldknowthisbut:
avatar
By paulwakefield1
25th Feb 2020 16:04

Possibly true. But I would be nervous anyway cutting and pasting between multiple sheets. The chances of it getting fouled up at some point is high and, unless there are strong reconciliation procedures in place, it may well not be picked up.

Thanks (0)
Replying to paulwakefield1:
avatar
By Victor28
25th Feb 2020 16:25

Thanks, the reconciliation done is indeed done is such manner that any errors are captured. Dividing the data in different sheets helps with the visibility of each type of transaction.

Thanks (0)
Replying to Victor28:
avatar
By paulwakefield1
25th Feb 2020 17:03

I take your point re visibility but that could for instance be achieved by colour coding each transaction according to type using conditional formatting.

Thanks (0)
avatar
By johnhemming
25th Feb 2020 16:14

Cutting and pasting is not a digital link (unless it has a form of automation). However, I read this as a question of creating the digital records.

How the digital records are created does not as far as I understand prevent using all techniques (typing, interfaces, clipboard, OCR processing).

What matters is that you can work back from the totals and see an automatic calculation of the totals (the digital link).

Thanks (0)
Replying to johnhemming:
avatar
By Victor28
25th Feb 2020 16:45

1) The first step that is done: Accounting system -> Excel via macro's => digital link
2) The second step that is done: in the excel workbook the macro's load the transaction data (on invoice level) in different worksheets based on some parameters. Hence, 99.5% of the transactions is correctly clasified. The problem lies with the 0.5% of the transactions (invoices) where a wrong clasification is done and currently this is solved by manually cut and pasting these to the correct sheet.
If we would link the invoices that are clasified wrongly to an "adjustment" sheet the digital link would be in place and we can then work with a formular that makes sure the correct figures are reported.

Example: an EU sale for 100 GBP is incorrectly loaded in the worksheet "export". We could then link the cell containing the incorrect amount 100 GBP in the worksheet "export" with an extra worksheet called "adjustment". In this sheet we can then increase the amount "total EU sales" with 100 GBP (with linked cell, thus digital link in place) and decrease the amount for "total exports" (with linked cell, thus digital link in place). This is a recipe for disaster though and very cumbersome. I can't imagine this can every increase the accuracy of the VAT return.

Thanks (0)
Replying to Victor28:
avatar
By Matrix
25th Feb 2020 16:49

I suggest that you should adjust in your accounting software and then extract a new download.

Thanks (0)
Replying to Matrix:
avatar
By Victor28
25th Feb 2020 16:58

Thanks for the response, but this is not possibly (the system doesn not has that functionality.

Thanks (0)
Replying to Victor28:
avatar
By johnhemming
25th Feb 2020 18:34

The thing you need to work out is where your "digital records" are. From what you say they could all be in the excel data in which case you are OK. However, this may be some thing to take offline and talk about in a phone call (after which a summary can be posted by agreement). If you would like to try that email me at [email protected]

Thanks (0)
Share this content

Related posts