Sage 200 - Use Excel to prepare VAT submissions in the Sage MTD Adapter

You can export the VAT information in the Sage MTD module/MTD Adapter to an Excel spreadsheet.

You might do this to adjust the VAT values that were taken from your Sage applications. Once you have adjusted the values in Excel, you can import the spreadsheet back in to Sage MTD module/MTD Adapter.

1. Make sure that the companies you want to export are listed in the Selected companies.
2. Select Export to Excel.
3. Enter the file name for the spreadsheet and click Save.

You can edit the spreadsheet to suit your needs; for example, by adding rows or columns, including formulae and calculations, and adding new worksheets.

If you're using a spreadsheet produced by the Sage MTD module/MTD Adapter:

• The Calculated VAT Values in boxes 1-9 are used for the VAT submission, these figures should not be manually entered.  These are calculated by totalling the Source Values for each company along with the value in the Adjustments column. It may be necessary to amend the formulas in boxes 1-9 to include additional companies which are added to the sheet .
• Adjustments² can be manually entered.
• After the soft landing period¹, the Source Values should not be entered manually (including manual transposition by pasting).  The figures should be populated by a digital link2 to the source data, for example an Excel formula could be used to pull data from another spreadsheet which has been created by none-Sage accountancy software for one or more of the companies in a groups.
• You can edit the Submission Information that contains the company name, VAT registration number, and start and end dates.

1 - The soft landing period is either – 1st April 2019 to 31st March 2020 or if HMRC has given a specific direction to a business to start following the Making Tax Digital rules for VAT periods starting on or after 1st October 2019, the soft landing period is 1st October 2019 to 30th September 2020.
2 - For more information on adjustments and digital links please see Vat Notice 700/22 Making Tax Digital for Vat.

Do not change the name of the worksheet or the named cells containing the total values, as these are required to re import the spreadsheet.

• The worksheet must be named SageMTD.
• The company name must be in the cell named CompanyName
• The VAT registration number must be in the cell named VATRegistrationNumber.
• The period start and end date must be cells named StartDate and EndDate.
• The VAT values to import must be in cells named TotalBox1 - TotalBox9.

You may be asked to justify the VAT calculation that you submit to the HMRC.

You can use Excel to import information from Excel.

1. In the Sage MTD Adapter, select Excel as the application.
   
   The import from Excel option will only be available if it is included in your licence for the Sage MTD module/MTD Adapter.
2. Select the Excel spreadsheet (xlsx format) that contains your VAT values. When the spreadsheet has been imported, the company name will be displayed in the Company list.
3. Select Choose Company, then check the VAT period (there should only be one) and click OK.
4. To check the imported values, select Show Details.
   
   To view the associated spreadsheet linked to these values, click View Excel.

If you have already imported a spreadsheet and added the company to Selected companies, then you will not be able to import values from the same spreadsheet again. The imported company details must be removed before you can import them again.

To re import the spreadsheet:

1. Select the company from the list of Selected companies.
2. Select Remove Company.
3. Select Excel as the application and import the spreadsheet again.

If you have any problems when importing your spreadsheet, check these errors and how to fix them.

You can create our own Excel spreadsheet containing your VAT values and import these in to the Sage MTD module/MTD Adapter.

There are two ways to do this:

• Generate a blank Excel template from the Sage MTD module/MTD Adapter.
• Use your own custom spreadsheet.

You can create a blank Excel template which is pre-formatted and ready for you to enter your VAT values.

1. Within the MTD Adapter select Tools, Generate Blank Excel Template.
2. Enter the number of companies you want to enter VAT figures for.
   • If you choose 1 or more companies, the spreadsheet will include a column for each company to enter its VAT figures, and also a separate column for adjustments. The values for all the companies and adjustments will be added together, and only the total values will be imported and used for your VAT submission.
   • If you select zero companies, the spreadsheet will only contain the boxes for the VAT submission values.
3. Enter the Company Name and the VAT Registration Number for the company that will be used for the VAT submission.
4. Enter the VAT period Start Date and End Date.
5. Click OK to generate a blank Excel spreadsheet.
6. Enter your VAT values and any calculations to the spreadsheet.
7. Once you have entered your VAT details in the spreadsheet, import it back into the Sage MTD module/MTD Adapter, to complete the VAT submission.

Use your own custom spreadsheet

You can use your own custom Excel spreadsheet to import VAT values. You can organise your spreadsheet however you want, provided that you use specific names for the cells that contain the VAT information to import. To name a cell in Excel, enter the name in the cell reference.

• The worksheet must be named SageMTD.
• The company name cell must be in the named cell CompanyName.
• The VAT registration number must be in the named cell VATRegistrationNumber.
• The period start and end date must be named cells StartDate and EndDate.
• The VAT values to import must be in named cells TotalBox1 - TotalBox9.