MTD and Excel Template Imports

MTD and Excel Template Imports

Didn't find your answer?

I have just had a conversation with Sage and wanted to know if anyone can shed any light on the process of importing information from Excel into bespoke software such as Sage.

I have a template from Sage which has the headings required for import.  A client has a workbook with 12 tabs, each tab holds a monthly invoices where the net, and VAT totals are, by way of example, in cells F30 and F31 on each page.

My understanding is that with the clients workbook open,  I will have to go into the Sage Excel template and under the 'Net Amount' heading select the formula '=F30' of the clients workbook page and under the heading Tax Amount select '=F31' as I have taken MTD to mean once a human has inputted the information into Excel, another human cannot simply type those numbers into anything else and has to bring them in using formulae.

Sage says, I am to manually type the figures into the Sage template from the invoices raised in the clients workbook, in order to import the excel information into Sage.  Maybe I am taking things too far, but is Sage not asking me to manually re-type something in their sheet, that has already been typed into Excel by a human, which I have understood to mean that I can't under MTD.

Any clarification would be much appreciated.

Replies (18)

Please login or register to join the discussion.

John Toon
By John Toon
11th Jun 2019 18:35

For the time being (under the soft landing period) you could copy and paste the necessary info between the sheets, but after that you must (to comply with MTD rules) use a link (formula or otherwise).

What Sage have told you is nonsense.

Thanks (1)
Replying to johnt27:
avatar
By johnhemming
11th Jun 2019 18:48

It depends which are the digital records. Just because some records are digital does not mean that they are the MTD required digital records.

Thanks (1)
Replying to johnt27:
avatar
By Carolynne
11th Jun 2019 21:18

Many thanks Johnt27, the person I spoke to at Sage didn't want to take me on at all, they just kept repeating that millions of companies can't be wrong doing it the way they described.

This evening I was at a CPD event with a guy talking about the Reverse Charge for VAT and I enquired with him about this, and he agreed. The soft landing period will cover you doing this. But after 12 months you will have to use a link such as a formula to populate the cells that are on the templates or middle ware sheets.

Thanks (0)
Replying to Carolynne:
avatar
By johnhemming
12th Jun 2019 07:16

A simple way to look at things is that once something has been typed into the records it has to be transferred electronically. However, the situation is more complex.

You need to have a set of digital records and those records need to link through to the submission. The digital records need a certain level of detail. However, you can type into those digital records.

For example if you keep manual books you can type into the digital records to create them at any time before submitting the vat return (as long as it is submitted on time). Similarly you can actually retype lists of data, but you cannot take a total from one list and put the total in another system (from 1st April 2020).

I am not quite sure from the original question what is going on, but the Sage helpline might be right they might also be wrong.

Thanks (1)
avatar
By Matrix
12th Jun 2019 07:22

If you are only inputting into Sage to comply with MTD then I don’t see the point. If the records are already digital and have totals then why not just use bridging software since you would then be able to continue after April 2020.

Thanks (1)
Replying to Matrix:
avatar
By paulwakefield1
12th Jun 2019 07:46

As Matrix says.

It sounds as if there is no summary sheet on your client's workbook. It would not be difficult to construct a summary page which pickes up the relevant 3 months for a return. This would then give a static source for the bridging software (or indeed the Sage template).

Thanks (1)
Replying to Matrix:
avatar
By Carolynne
12th Jun 2019 08:44

The client only inputs the customer invoices into Excel. All the purchases, Direct debits and bank reconciliation is done in Sage.

Thanks (0)
By Duggimon
12th Jun 2019 08:41

If you are putting the individual invoices, purchases and expenses into Sage then it doesn't matter how it gets there, the Sage records will comply with MTD.

You can retype the figures into a new spreadsheet, use copy and paste, formulas or enter the figures directly. The digital link is only required if you are using subtotals, so long as the level of detail required by MTD is present in Sage there's no need for any further digital link past that point.

Thanks (0)
Replying to Duggimon:
avatar
By Carolynne
12th Jun 2019 08:48

Duggimon, re-typing the figures into a spreadsheet from what I have read is what MTD is making us avoid - not re-typing anything and instead using a template or middle ware to import the information typed the first time. I actually don't think you can re-type an entry. So if the client has typed the information into Excel as an invoice in a template in there. My understanding is that I would have to extract those figures using formula (certainly after the soft landing), to bring the figures in.

Thanks (0)
Replying to Carolynne:
By Duggimon
12th Jun 2019 09:50

You haven't understood what you read then.

There needs to be a digital link from the VAT return to the underlying accounting records. The underlying accounting records are those that record everything at a transaction level.

Therefore if the Sage records record everything at a transaction level, it doesn't matter how the figures got in to Sage.

If it makes you feel better you could print the spreadsheet out and then you're entering figures from paper records rather than digital ones but that would make me sad because I'm a fan of trees.

Thanks (2)
Replying to Duggimon:
avatar
By Carolynne
12th Jun 2019 10:07

I do understand what you are saying and that would be great. I just can't get over the point of if a human has already entered it onto a computer (albeit not in the Sage system), that I am supposed to electronically transfer it into Sage from that system and not physically type it again. Otherwise I could simply take the printed copies of the sales invoices from Excel and type them directly into Sage individually. I will have to ponder on all this, as I just can't get over that one point. Thank you for your response Duggimon

Thanks (0)
Replying to Carolynne:
By Duggimon
12th Jun 2019 10:19

The digital link has to go all the way from the return to digital records, which are recorded digitally at a transaction level with the detail required by the legislation.

That's it, that's all that's mandated. Your problem, I think, is that you are looking at it from the other end and seeking to apply a digital link from the first time it goes on a computer all the way to the return. That is not required and isn't in the legislation.

Look at it the other way round, starting with the return and linking back to the underlying records. As soon as you get to the point that those records are of sufficient detail to comply, you can stop digitally linking. Whatever other copies of those records exist beyond that point are irrelevant.

That is the way the legislation is applied, and that is why there's no problem typing (or copy/pasting or importing) all the entries from a spreadsheet into Sage. How it goes between those two is irrelevant so long as Sage holds the level of detail required to comply, because at that point the requirement for digital links stops.

Thanks (1)
Replying to Duggimon:
avatar
By Carolynne
12th Jun 2019 10:25

I get it now. Thanks so much.

Thanks (0)
By ireallyshouldknowthisbut
12th Jun 2019 09:14

Unless HMRC are going to be actively sat behind you when you do it, I would suggest you get the data in in the most efficient manner.

I cant comment on antiques like SAGE, but in most modern packages imports of data are quite simple so long as you get the header rows matched up. You will however usually need to do it tab by tab, or copy all the tabs into a single sheet as the importing system will want to see a table of data in a flat file.

Thanks (0)
avatar
By Spaghetti
21st Jun 2019 15:39

We import our sales invoices into Sage on a daily basis using an excel csv file from another system without any problems. I have all ready submitted my first vat return through Sage 50. Are you now saying that i am not compliant even though the return was done through Sage

Thanks (0)
Replying to Spaghetti:
avatar
By johnhemming
21st Jun 2019 16:21

If you are importing your invoices I would have thought you were fully compliant (in the sense that you have the digital links required from 1st April 2020).

If you are importing the invoices individually the requirement for digital records is probably satisfied by your Sage system in isolation.

Thanks (0)
avatar
By [email protected]
21st Jun 2019 16:58

How about getting them posted directly from Excel to Xero or QuickBooks Online? 1 click posting in Excel, digital link and saves time :) importing should be a thing of the past!

But yeah, i'd stay away from copying or re-keying, just another thing to remember to stop doing if not..

Thanks (0)
Replying to [email protected]:
avatar
By Carolynne
21st Jun 2019 18:23

Hi Carl
Unfortunately, the client does not use the book keeping package, and would have no idea about posting things online, his knowledge is very limited. This is going to be a problem for customers who are not too savvy with technology.

Thanks (0)