NJT VAT Return Manager (MTD)

(Bridging software for online filing of the MTD compliant VAT Returns to Her Majesty's Revenue and Customs)

Importing from Microsoft Excel



The following section describes the detailed technical requirements for importing from a Microsoft Excel workbook, using the NJT standard format.

One of the main principles of Making Tax Digital is that any transfer of data must be undertaken digitally. To facilitate businesses that use Excel based Accounting systems, a function is provided to allow preprared VAT returns to be imported, via an Excel workbook, that contains a worksheet conforming to a 'Standard Format' as defined by NJT. The pre-requisite being that businesses are able populate the worksheet as per the following example.

Example:
MTD Excel Import Example
1. Workbook and Worksheet Definition:

2. 'MTD-VAT' Worksheet Cells Definition:

3. Example of a single VAT Return workbook:

Assuming you already use an Excel workbook to calculate your VAT Return. This may include manual adjustments etc. This workbook would already contain other worksheets that hold your adjustments details. It is assumed that you already have formula based cells that contain your 'Nine Box' totals for the VAT Return.


Steps for making the workbook compatible with the MTD software
  1. Add a new worksheet within the existing workbook and set the worksheet name to 'MTD-VAT'.
  2. If you wish, populate the cells A1 to A13 as defined in the example screenshot. Note: This is for your information only, and is not used by the MTD software.
  3. Either type your 'Business Name' in Cell B1 or enter a formula linking it to your 'Business Name' already present in a cell elsewhere in the workbook.
  4. Either type your 'VAT Registration No.' in Cell B2 or enter a formula linking it to your 'VAT No.' already present in a cell elsewhere in the workbook.
  5. Either type your 'From Date.' in Cell B3 or enter a formula linking it to your 'From Date' already present in a cell elsewhere in the workbook.
  6. Either type your 'To Date.' in Cell B4 or enter a formula linking it to your 'To Date' already present in a cell elsewhere in the workbook.
  7. In cells B5 to B13, enter a formula linking the cells to the existing totals already created elsewhere in the workbook.

You may wish to create an Excel template or an empty workbook containing all the layouts and formulas. Alternatively, after you have completed and saved the current VAT Return workbook, you may choose to 'Save As' the workbook, giving it a different name in preparation for your next VAT Return. The objective being that you only create the formulas once.


4. Example on how to create a 'Local Link' to another Worksheet within the same Workbook:

Scenario: Let's say you have an existing workbook that contains a worksheet called 'Trans'. Worksheet 'Trans' contains your transactions including the calculated totals for 'Boxes 1 to 9'. The 'Box 1' total value is stored within the cell 'E20'. 'Box 2' total in the cell 'F20' etc.
Also you have added the New worksheet called 'MTD-VAT' and you now wish to create a formula in cell 'B5' that points to the 'Box 1' total in worksheet 'Trans'.

  1. Click in the cell 'B5' within the 'MTD-VAT' worksheet.
  2. Type "=" (an equals sign without the quotes).
  3. Click on the 'Trans' worksheet, then click in the cell 'E20'.
  4. Then press the 'Enter' key.
  5. This will create a formula in cell 'B5' that links to the cell 'E20' within worksheet 'Trans'
    The contents of cell 'B5' would now be =Trans!E20.
  6. You can then repeat the above steps for the remaining cells.
5. Example on how to create an 'External Link' to a Worksheet within a separate Workbook:

Scenario: Let's say you have two workbooks called 'Inputs.xlsx' & 'Outputs.xlsx', that each contain a worksheet called 'InputTrans' & 'OutputTrans' respectively. Worksheet 'OutputTrans' within workbook 'Outputs.xlsx' contains your sales transactions, including the calculated total for 'Box 1'. The 'Box 1' total value is stored within the cell 'E20'.
Also you have created a new workbook called 'My VAT Return.xlsx' and added the New worksheet called 'MTD-VAT'. You now wish to create a formula in cell 'B5' that points to the 'Box 1' total in worksheet 'OutputTrans'.

  1. Open Workbooks 'Outputs.xlsx', 'Inputs.xlsx', then open 'My VAT Return.xlsx'.
  2. Within the workbook 'My VAT Return.xlsx', click in the cell 'B5' within the 'MTD-VAT' worksheet.
  3. Type "=" (an equals sign without the quotes).
  4. Click on the workbook 'Outputs,xlsx', then click on the 'OutputTrans' worksheet.
  5. Click in the cell 'E20' then press the 'Enter' key.
  6. This will create a formula in cell 'B5' that links to the cell 'E20' within external worksheet 'OutputTrans'
    The contents of cell 'B5' would now be ='[Outputs.xlsx]OutputTrans'!$E$20.
  7. You can then repeat the above steps for the remaining cells, creating external links to the workbooks 'Inputs' & 'Outputs' respectively.
Note: If you subsequently open the workbook 'My VAT Return.xlsx' without first opening the linked workbooks, you will be prompted to update the links, in which case you would open the linked workbooks then click on the 'Update' button.
If you do not wish to change the links you can click on the "Don't Update" button. For information, the links will be expanded to include the full folder path.
For example ='C:\My Folder\[Outputs.xlsx]OutputTrans'!$E$20.