Share this content
0
2211

MTD and sales invoice template in Excel

MTD and sales invoice template in Excel

I wondered if anyone knows how to treat sales information for MTD when the client uses a 'template' in Excel to raise the invoice and prints a hard copy that is sent in a pack to the accountants to be processed into Sage.

My concern is that although the sale is not being recorded in Excel in rows for book keeping purposes, the sales figure is appearing in a cell in Excel.  If the client has for example 12 pages of templates (1 for each month of the year that is overwritten from the year before), per client and has 50 clients.  I would worry about accuracy if the client has to click into each cell on each page to provide their sales figures for the quarter.  They would normally print off the hard copies and put them in their pack together with their purchase receipts, and we would then enter these into Sage directly, for their VAT return.

On looking at Public Notice 70/22 Section 3.2 mentions a a spreadsheet is capable of recording and preserving digital records, then Section 3.2.1 'Digital Links,' mentions, it is one where a transfer or exchange of data is made or can be made electronically between software programs.

In this instance, would it be acceptable for us to continue to enter the information from the printed sales invoices manually under MTD?

Replies

Please login or register to join the discussion.

18th Feb 2019 16:24

If you are currently entering all the separate invoices into Sage, then you're compliant for MTD purposes. Where the information comes from before that is of no concern, they could write it on the moon with a laser for all HMRC care, the point is to have the digital link from the VAT return figures through to the individual invoice figures.

If, however, you just enter the total for the quarter, or some other subtotal of the invoices, into Sage, then under MTD you'll have to start entering the individual invoices.

Thanks (2)
avatar
By SXGuy
18th Feb 2019 16:27

I think your confusing things.

The digital link is the point when the hard copy invoice gets recorded to sage. If the client wants to keep record of sales on a spreadsheet that's separate.

Im guessing they don't file their own vat returns but send you the info, so the digital link is when you input the invoice in to sage.

Thanks (1)
avatar
to SXGuy
25th Feb 2019 11:29

SXGuy wrote:

I think your confusing things.

The digital link is the point when the hard copy invoice gets recorded to sage. If the client wants to keep record of sales on a spreadsheet that's separate.

Im guessing they don't file their own vat returns but send you the info, so the digital link is when you input the invoice in to sage.

After posting the original question here about a client using Excel as a template to raise sales invoices, I finally received a call back from an MTD Technician in the HMRC VAT department, who said that under section 3.2.1 (Digital links) where it states

'A digital link is one where a transfer or exchange of data is made, or can be made, electronically between software progams.'

…..and that by putting figures into a template in excel (a compatible software package), this information would have to be converted to a CSV to import into Sage as an example, and that I am not permitted to enter the sales invoices manually from hard copies that have been printed from Excel.

I have also read further on amongst the answers, and someone else has been verbally given the same information as below:

_______________________
By Di 21st Feb 2019 14:31
I have spoken to the vat helpline and they said that if the invoices were written up manually by the client and I then key them into MTD software, that was acceptable. However if my client kept invoices on an excel template in any form I could not then key them in again, there would have to be the digital link between excel and the software. How many of us have clients whose sole concern is raising sales invoices in the simplest manner possible and letting us sort out the rest. I do know that if we let the clients loose on all areas of the software we are going to be spending ages trying to sort out the mess!
____________________________

Therefore I can only conclude that the client will have to use WORD going forward for sales invoice templates, however, HMRC say that inciting the client to use an incompatible package in light of the Excel digital link, would be going against MTD rules, to get round this.

This is so frustrating as a lot of clients just aren't computer literate enough to deal with this and its going to take so much more time on our part sorting it out.

Thanks (1)
avatar
to Carolynne
25th Feb 2019 12:18

I think this is one of the things that probably needs to be sorted out at a level higher than the helpline.

I have contacted HMRC (just now) about the general principle about the question as to what does require a digital link and what does not and how this can be resolved.

This is clearly a significant issue that will affect a lot of businesses in potentially quite a big way.

Thanks (0)
18th Feb 2019 18:08

Don't worry about any errors.

Accuracy isn't as important as having a digital link.

Thanks (5)
avatar
18th Feb 2019 18:15

Thanks for your replies everyone. Do we know yet what information is going to be submitted. I have also looked at bridging software from Absolute for the spreadsheet work clients, and it seems to bring in boxes 1 to 9 figures for the submission when you watch their video of it. Is MTD extracting more than the 9 box figures going forward and if so, does anyone know what is it extracting?

Thanks (0)
to Carolynne
18th Feb 2019 18:24

Boxes 1-9 for now.

Going forward, anybody's guess.

Thanks (1)
avatar
By SXGuy
to Carolynne
18th Feb 2019 18:44

I've done a mtd submission test using quickfile, and the level of detail from what I saw is shocking.

What I also noticed being added was things like transfers between accounts, dla etc. I'm not sure if it's a hmrc thing or if quickfile doesn't handle the correct nominals in the boxes properly, but there was stuff like, customer details being recorded with each figure. How much of this actually gets sent through I don't know.

Thanks (0)
to SXGuy
18th Feb 2019 18:53

SXGuy wrote:

I've done a mtd submission test using quickfile, and the level of detail from what I saw is shocking.

What I also noticed being added was things like transfers between accounts, dla etc. I'm not sure if it's a hmrc thing or if quickfile doesn't handle the correct nominals in the boxes properly, but there was stuff like, customer details being recorded with each figure. How much of this actually gets sent through I don't know.

All this will be a thing of the past when MTD starts. ©

Thanks (0)
avatar
to SXGuy
18th Feb 2019 20:19

There is no reason why anything should be submitted for MTD other than the figures in the boxes (and a few other codes). In practice it is possible with JSON to submit a lot of data that is then ignored at the other end. I have not tested the HMRC validation to see if this gets rejected. It is, however, not part of the MTD specification.

If people are interested I could do a test submission to HMRC's test server and copy what is sent into this forum so that people can see what actual data is used for a VAT MTD submission.

Thanks (5)
avatar
to johnhemming
19th Feb 2019 08:05

johnhemming wrote:

If people are interested I could do a test submission to HMRC's test server and copy what is sent into this forum so that people can see what actual data is used for a VAT MTD submission.

Yes please John

Thanks (0)
avatar
to Cheshire
20th Feb 2019 19:26

Sorry only just seen this (here is the info)This is the data for a test user:
{
"userId": "709268039244",
"password": "xrosfkdc3ckq",
"userFullName": "Claude Upton",
"emailAddress": "[email protected]",
"organisationDetails": {
"name": "Company YBMQQG",
"address": {
"line1": "42 Palace Gardens Terrace",
"line2": "Ipswitch",
"postcode": "TS3 1PA"
}
},
"saUtr": "6000034571",
"nino": "YS718673A",
"mtdItId": "XVIT00000674915",
"vrn": "881810710",
"vatRegistrationDate": "2006-02-20",
"lisaManagerReferenceNumber": "Z6816",
"secureElectronicTransferReferenceNumber": "111111111189",
"pensionSchemeAdministratorIdentifier": "A8850841",
"eoriNumber": "GB0571681030"
}

This is the test data from a spreadsheet using the above information
VAT RETURN TEST CSV FILE
BOX1 VAT due in this period on sales and other outputs 234
BOX2 VAT due in this period on acquisitions from other EC Member States 123
BOX3 Total VAT due (the sum of boxes 1 and 2) 357
BOX4 VAT reclaimed in this period on purchases and other inputs (including acquisitions from the EC) 100
BOX5 Net VAT to be paid to Customs or reclaimed by you (Difference between boxes 3 and 4) 257
BOX6 Total value of sales and all other outputs excluding any VAT. Include your box 8 figure. 10000
BOX7 Total value of purchases and all other inputs excluding any VAT. Include your box 9 figure. 5000
BOX8 Total value of all supplies of goods and related costs excluding any VAT to other EC Member States 0
BOX9 Total value of acquisitions of goods and related costs excluding any VAT from other EC Member States 0
PERIODEND Period End Date 31/03/2017
VRNVERIFY VAT number (for checking purposes) 881810710 This information has to be the same at the VAT number stored against the legal entity/businesses on the website. The See Vat Data page allows you to update this. (see the DigitalLinks webpage for more information)

This is the test server URL being used including the details of the parameters
https://test-api.service.hmrc.gov.uk/organisations/vat/881810710/returns

This is the data sent:
{"vatDueAcquisitions":"123.00","totalAcquisitionsExVAT":"0","vatDueSales":"234.00","totalValuePurchasesExVAT":"5000",
"totalVatDue":"357.00","netVatDue":"257.00","finalised":true,"periodKey":"18A1","totalValueSalesExVAT":"10000","vatReclaimedCurrPeriod":
"100.00","totalValueGoodsSuppliedExVAT":"0"}

This is the data from HMRC's server
processingDate: 2019-02-20T19:24:47.663Z
paymentIndicator: DD
formBundleNumber: 368623614116
chargeRefNumber: 1XxZ3TgaJ7bjyGrE

Thanks (0)
avatar
to johnhemming
20th Feb 2019 19:28

I have noticed that some of the data went outside the visible area so I have put some crlfs into it
This is the data sent:
{"vatDueAcquisitions":"123.00",
"totalAcquisitionsExVAT":"0",
"vatDueSales":"234.00",
"totalValuePurchasesExVAT":"5000",
"totalVatDue":"357.00",
"netVatDue":"257.00",
"finalised":true,
"periodKey":"18A1",
"totalValueSalesExVAT":"10000",
"vatReclaimedCurrPeriod":"100.00",
"totalValueGoodsSuppliedExVAT":"0"}

Thanks (2)
avatar
By SXGuy
to johnhemming
21st Feb 2019 15:08

pensionSchemeAdministratorIdentifier

Wth has that got to do with vat?

Thanks (0)
avatar
to SXGuy
21st Feb 2019 18:52

SXGuy wrote:

pensionSchemeAdministratorIdentifier

Wth has that got to do with vat?


Nothing.

I am simply giving the full details of the test user. I am doing all of the HMRC Apis (the MTD json ones) so when I ask their server to create a test user I ask for all of the services to be added.

There are a few services missing from that test user that is because it is not a test agent (I am not sure whether it is created as an individual or an organisation). There are also test agents for dealing with the Agents API. (Inter alia)

Thanks (0)
avatar
By SXGuy
to johnhemming
19th Feb 2019 08:06

Id like to see that, thanks.

Thanks (0)
avatar
to johnhemming
19th Feb 2019 10:36

That would be really helpful, thanks.

Thanks (0)
avatar
to SXGuy
21st Feb 2019 17:40

I think this is something that you will need to deal with them about in writing. The Digital links stuff does not need to be done until April 2020 anyway.

Thanks (0)
avatar
21st Feb 2019 11:53

I've been using QuickBooks Online for a particular company. The trouble is that sales records are held on different platforms. In the past we have downloaded details and just posted the monthly totals. Under MTD I see that this is not acceptable as the digital link between the full sales details would not be there. QBO does have an import function which allows sales details to be imported from a csv spreadsheet. It does not record the full invoice details but covers the essential areas such as name, dates of invoice, due dates, amounts and tax rates. However, when inputted this creates individual invoices and, as there is no batch payment processing function, we have to click each individual invoice to record payment. I've messaged QuickBooks about this and they have indicated that they will look at this. Anyone else have a similar experience and solution? Speaking to others I am aware that there are many businesses with this type of problem.

Thanks (0)
avatar
to wrilliams
21st Feb 2019 12:47

You may find it easier to bring everything together in a spreadsheet. They tend to be quite flexible in the way that they handle the import of data.

Thanks (0)
avatar
to johnhemming
22nd Feb 2019 11:22

Thanks John. As the records are maintained on QBO it would be a bit of a chore to download all details to a separate spreadsheet for submission by another method - particularly when QBO is intended to handle the VAT return submission process under MTD. The sales details can be imported to QBO easily enough but with hundreds of sales each month you then have to go through each one to clear payment. A batch payment processing function would help. I have messaged QuickBooks but there response is that QBO does not provide for this but it would be useful for their tech team to consider.

Thanks (0)
to wrilliams
23rd Feb 2019 07:36

Hi

A couple of things spring to mind. If the invoices are all paid at the time of issue you could import them as sales receipts.

If they are paid later then import them and use the QBO bank feed - this will match payments to invoices where 1 payment = one invoice. There is then a matching routine to pick off a number of invoices paid together.

Is it possible to link the source of the invoices to QB - so if it's a web shop or something like Chargebee they can be directly imported automatically. It may be then the client can run the sales ledger in QB

If the invoice software simply produces invoices can the client not simply produce them in QBO?

Thanks (0)
avatar
By Di
21st Feb 2019 14:31

I have spoken to the vat helpline and they said that if the invoices were written up manually by the client and I then key them into MTD software, that was acceptable. However if my client kept invoices on an excel template in any form I could not then key them in again, there would have to be the digital link between excel and the software. How many of us have clients whose sole concern is raising sales invoices in the simplest manner possible and letting us sort out the rest. I do know that if we let the clients loose on all areas of the software we are going to be spending ages trying to sort out the mess!

Thanks (0)
to Di
21st Feb 2019 14:47

Or - you could enter them up from the spreadsheet and then delete the spreadsheet.

Who's to know ?

Thanks (0)
avatar
to Di
21st Feb 2019 15:26

I am not sure that this is right. I don't think if you use a word processor to type up invoices that you need to have a digital link from that. AIUI as soon as you have entered the invoices on an electronic list then the digital link is needed from there.

Excel and excel templates operate as a form of word processor in this instance.

Thanks (0)
avatar
By Di
to johnhemming
21st Feb 2019 15:52

That's what I had hoped but vat helpline said no! Are they correct?

Thanks (0)
avatar
to Di
25th Feb 2019 15:38

Di
That's what they said to me on the technical MTD helpline too!

Thanks (0)
to Di
23rd Feb 2019 07:48

I think the key here is the word kept. It is my impression from above that the client opens excel and his template types in the info and prints it and then closes without saving. The info is not kept so no way of forming a digital link.

If however the invoices are stored then I think HMRC have a point. If the client creates digital records then we must look at linking to them. HMRC will not distinguish between the clients records and the accountants records. It's all one set of data.

Better to find a better way forward. For the client who produces their own invoices in excel could they be trained to use online accounting software? I have clients who now use QuickBooks online and just do their invoicing and we take it from there. No need for any digital links it's all in the software. Maybe eventually they will pick up more of the work.

Thanks (0)
avatar
to coolmanwithbeard
23rd Feb 2019 10:55

An alternative perspective is to work backwards from the submitted VAT data. If there is a digital link to the submitted VAT data from the list of vat input/output then the primary records are being kept and anything else is draft or working documents whether those be electronic or not.

It is obviously a better system to have one which generates the invoices electronically and then posts the relevant transactions from that, but I don't think it is legally required to comply with MTD for VAT.

If you think about the daily cash systems then there is data there that is recorded electronically in some form during the day, but HMRC only require the daily totals.

There cannot be a legal distinction between say using Microsoft word to produce an invoice and using Excel. Both are complex computer environments capable of advanced programming in various ways.

Thanks (1)
Share this content