Skip to content
logo Knowledgebase

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

Created on  | Last modified on 

Summary

You can use an Excel spreadsheet to prepare the VAT values for your VAT submissions. Excel functionality for the Sage MTD module/MTD Adapter can export values to a spreadsheet, and import values from a spreadsheet.

Description

The import from Excel option will only be available if it is included in your licence for Sage MTD module/MTD Adapter.

You can use Excel spreadsheets in different ways to prepare for your MTD submissions:

  • Adjust VAT values taken from your Sage applications by exporting them to a spreadsheet.

    If you've used Sage MTD module/MTD Adapter to collect VAT values from your Sage applications, you can export these to an Excel spreadsheet. You can then adjust these values in Excel, and import them back into Sage MTD module/MTD Adapter.

  • Enter your VAT values using a blank pre-formatted spreadsheet.

    You can use Sage MTD module/MTD Adapter to create a blank Excel template which is pre-formatted and ready for you to enter your VAT values.

  • Enter your VAT values from your own custom spreadsheet.

    If you want to import VAT values from your own custom spreadsheet, you can format your spreadsheet so that it can be imported by Sage MTD module/MTD Adapter.

Resolution

Export VAT Return to a spreadsheet

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.

 

Edit VAT Return in a spreadsheet

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 .
  • Adjustments2 can be manually entered.
  • After the soft landing period1, 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.

Import VAT Return from a spreadsheet

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.

 

Re import a spreadsheet

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.

 

Having problems with importing?

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

Error Reason
There are no worksheets in this workbook that use the correct name. The worksheet used by the Sage MTD module/MTD Adapter must be called Sage MTD. The worksheet (tab) in the Excel spreadsheet that contains your values must be named SageMTD.
A spreadsheet with the name 'xxx.slsx' has already been imported. A spreadsheet with the same file name has already been imported. You cannot import two spreadsheets with the same name in the same submission.
The process cannot access the file 'c:\import.xlsx' because it is being used by another process. The spreadsheet your are trying to import is probably already open in Excel. Make sure the spreadsheet is closed in Excel, then try to import it again.
Bad spreadsheet: could not find the row containing 'xxx'. The named title for a cell is missing from the spreadsheet. If you have created or edited this spreadsheet, make sure it still has the required named cells.
Bad spreadsheet: cell 'D50' contains 'xxx'. This is not a number, as expected for 'Box 1'. A cell in the spreadsheet contains a value which is not of the expected type.

Create your own spreadsheet to import VAT Return

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.
Use a blank Excel template

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.

CAUTION: You can import Excel files in XSLX format, which is supported by Excel 2007 onwards.

 

Additional Information

 

Benefits for your employees above and beyond their payslips

We want to help your business where we can. That's why we are offering Sage Employee Benefits free to Sage customers for the first three months.

Leave your details and we'll call you back