The NJT VAT Return Manager is a Microsoft Excel workbook that provides a quick and secure online filing of VAT Returns to Her Majesty's Revenue and Customs (HMRC). Details may be entered directly or imported from an Excel workbook or imported from VT Transaction+.
This product contains a customised application developed in Microsoft Visual Basic for Applications (VBA). The software runs automatically when the workbook is opened and interacts with the user by displaying various User Forms (Windows). The worksheets are not accessed in the traditional way and are therefore hidden from view.
'Making Tax Digital' (MTD) - Updated on 19th March 2018
I contacted HMRC on 12th March 2018 and will be attending a Developers event on Tuesday, 27th March at the HMRC Shipley office. I Will update this section after the event.
The next step will be to 'Prove Concept'. This will involve developing a piece of software to test 2-way communication with the new HMRC 'SandBox' test environment. Once the concept has been proved, framework development will begin. However, the extent of this development will be limited until HMRC publish their business and technical specifications.
The primary objective is to develop MTD compliant VAT Return software that interfaces with VT Software bookkeeping products. However, interfacing with other Accounts systems is also being considered. This may be accomplished via an intermediate Excel workbook (a bridge) that imports data electronically from the respective accounts systems. This data would be, in turn, imported into the VAT Return software for submission. These are merely indicative ideas at this point and will be pursued further following publication of the necessary HMRC documentation.
Article written by: Neil Cudbertson (Technical Manager)
Latest 'VAT Return Manager' Release - Version 7.4 (9th February 2018)
Summary of Recent Releases
1. V7.4 (09 Feb 2018) - Amended Excel configuration checks to close workbook after displaying message.
2. V7.3 (06 Feb 2018) - Intoduced Excel configuration checks and applied minor improvements.
3. V7.2 (30 Jan 2018) - Improved 'Backup' and 'Restore' functionality and applied minor Defect fixes.
4. V7.1 (25 Jan 2018) - Introduced new pricing structure and applied minor cosmetic changes.
5. V7.0 (24 Jan 2018) - Introduced new option to import VAT Returns from an Excel workbook.
6. V6.0 (21 Nov 2017) - Implemented compulsory changes to communicate with the new Government Gateway system.
Latest 'VAT Return Form' Release - Version 1.0 (23rd January 2018)
Main Features Include:
- Designed for use by single Traders or Agencies that manage returns for multiple Clients.
- HMRC normally accepts submissions within 15 seconds.
- Facility to import VAT returns from the 'VT Transaction+' or 'VT Cash Book' book keeping packages.
- Facility to import VAT Returns from Excel using the custom designed 'NJT VAT Return Form' workbook. This workbook is available to download from this website.
- HMRC responses can be viewed on screen, printed or saved as PDF file.
- Rejected submissions can be amended then re-submitted.
- Retains a full history of submissions including all technical data files returned by HMRC.
- Trader 'submission status' reports can be obtained from HMRC as required.
- Copy receipts can be obtained from HMRC for up to a period of 60 days.
- Facility to backup and restore all data. Can be used, for example, to restore data following a new product release.
- This product has received formal recognition by HMRC for Live and Test submissions and is fully compliant with HMRC security and encryption requirements.
- The Product is listed on the official UK Government website, which can be viewed via www.GOV.UK. Search for 'VAT Returns' then scroll down and click on link 'VAT Returns and EC Sales List: commercial software suppliers'. Then click on link 'VAT: commercial software suppliers'. Then scroll down the alphabetical list to find 'Neilson James Technology'.
Minimum System Requirements
Microsoft Windows: Windows 7 onwards. Both Windows '32-bit' and '64-bit' versions are supported.
Microsoft Excel: Excel 2007 (service pack 3) onwards. Note: only Excel '32-bit' versions support the 'VT Import' option..
Plus a reliable internet connection.
How to install the VAT Return Manager
- Create a folder on your computer. For example, "C:\My VAT Returns".
- Download the VAT Return Manager 'Test Evaluation' workbook and save to the folder you created.
- Open a blank workbook in Excel then change the Options as described in the 'Excel Configuration' section below. Then exit Excel.
- Finally, Open the VAT Return Manager workbook and follow the on-screen instructions.
Excel ConfigurationMicrosoft has taken a much tighter approach to software security. As a result of this, some Excel features are disabled during initial installation. In order for the VAT Return Manager to function correctly, a few changes are required within the Excel Options.
Open a blank workbook. (For recent versions of Excel, Click on 'File' menu, then 'Options', then 'Trust Centre' then click on 'Trust Centre Settings').
Note: For Office 2007, Click on the 'Office' button as the 'File' menu does not exist.
- Trusted Locations: Add the path of the folder where you saved the VAT Return Manager workbook. Also tick the box 'Subfolders'.
- Macro Settings: Tick the box 'Trust Access to the VBA project model'.
- Finally, click on the 'OK' button.
Organising Your Files
Files created by this product are organised using a preset folder structure. Sub folders are created automatically within the folder where the workbook resides. It is recommended that the VAT Return workbook is stored in a specific folder. For example, "C:\My VAT Returns".
Receipt Files: When a receipt is saved as a PDF file it is stored automatically to the sub folder "\Receipts".
Evidence Files: During each VAT Return submission, copies of the full submission and HMRC response are saved to the sub folder "\Evidence". These files are stored in XML format and can be viewed using a 'Text' editor such as NotePad or WordPad.
Data Backup Folder: Each backup created by the 'Backup Data' routine is stored in the sub folder "\Backup".
Excel Folder: When you Import a VAT Return from the 'NJT VAT Return Form' using the 'Excel' button, the routine first searches within the sub folder 'Excel'. This may be a convenient location to store the Excel forms.
The Dashboard Form
The Dashboard is the main controlling form and is displayed when the workbook is opened. Subsequent forms are displayed using the buttons on the Dashboard. For example: Add a new trader, Request and display a 'Trader' status report from HMRC, Create or amend a VAT return, Display a receipt on the screen, Print or save a receipt as a PDF document, renew the product licence.
Certain buttons may be displayed or hidden depending on the condition of a VAT Return. Also the button description may change in particular circumstances. For example, the 'Display Receipt' button is displayed only where the status of the VAT Return is 'Accepted'.
'End to End' Outline Process
Using the Dashboard form:
- If Trader does not exist, click ‘Add Trader’ button (Note: 'Trader' licence allows only 1 trader).
- Select the Trader from the ‘Trader Name’ dropdown list.
- Click on the ‘New Return’ button or highlight an existing 'Draft' return & click on the 'Amend Return' button.
Using the VAT Return form:
- Populate the VAT Return form.
- Note: For users importing from 'VT' Software , populate the Period End, then Click on the 'VT' button and select the desired 'Company' file from the folder/file list.
- Note: For users importing from the Excel 'NJT VAT Return Form' , enter Period End,
then click the 'Excel' button and select the Excel file from the folder/file list.
For further information on usage of the 'Excel' folder, see 'Organising your Files'.
- Click on the ‘Validate & Save’ button.
- Click on the ‘Live’ button within the Submit frame. (Optionally, click on the 'Test' button first).
Using the Submission Form:
- Enter your Gateway User Password then click on the ‘Submit’ button. Note: Normally within 15 seconds a message is displayed denoting a submission success or error.
Using the Dashboard form:
- To view a receipt on screen, click on the ‘Display Receipt’ button.
- To print a receipt, click on the ‘Print Receipt’ button.
- To save receipt as a PDF file, click on the ‘Save as PDF’’ button.
- To close the workbook, click on the ‘Save & Close’ button.
General Note:If the submission is successful, a confirmation message is displayed on screen. If the submission is rejected, an error message is displayed on screen. This error message is saved and can be displayed later using the conditional 'Display Error' button.
The VAT Return Form
The main purpose of this form is to populate the VAT Return and then submit to HMRC. The details can be either entered manually or imported from the custom designed 'NJT VAT Return From' or for VT Software Customers, the details can be imported from the desired 'VT Company' file.
In order to minimise the risk of submission errors returned by HMRC, extensive validation is undertaken prior to submission. This validation process can be initiated by clicking on the 'Validate & Save' button. However, the return is validated automatically at the start of the submission process.
Data Items Explained
- VAT Registration No: This is the 9-digit number registered with HMRC for VAT purposes. This number will be validated during the submission process.
- Gateway User ID: This is the User ID issued by HMRC and is required to log on to the Government gateway during the submission process.
- Trader Name: This is the trader name setup previously. This name can be amended for both current and future submissions.
- Return Type: Either 'VAT100' for normal returns or 'VAT193' for final returns.
- Annual Accounting Scheme: If 'Yes' then Box 10 is enabled allowing entry of the balancing payment amount.
- Sender: Choose from: Company, Individual, Agent, Bureau, Partnership, Trust, Employer, Government, Acting in Capacity or Other.
- Period End: Format YYYY/MM, where YYYY is 4-digit year and MM is 2-digit month. The application will validate against duplicate returns.
- Boxes 1, 2 and 4: Enter amount (2 decimal places). Boxes 3 and 5 are calculated automatically.
- Boxes 6 to 9: Enter amount (no decimal places).
- Box 10: Enter amount to 2 decimal places (Annual Accounting Scheme only).
- Declaration Box: Tick this box before submission.
The Submission Form
The Submission form is normally activated by the 'Live' or 'Test' buttons within the 'Submit' frame, resident in the VAT Return form. This is the final step before submission to HMRC.
For information, no passwords are saved by this product. Additionally, all passwords are encrypted before submission using the HMRC required MD5 128-bit algorithm. The full name is 'Message Digest algorithm 5 cryptographic hash'.
The Submission Process Explained
This process comprises of the following steps:
- Submit the VAT Return to Government Gateway for validation.
- Receive an 'Acknowledgement' or 'Error' response.
- Submit a request to HMRC for acceptance (Called Polling, multiple attempts may be required).
- Receive an 'Accepted' or 'Error' response.
The Submission status is updated following each step. Under normal circumstances the whole process is completed within 15 seconds, during which the status will change from 'Draft' to 'Accepted'.
However, certain circumstances might prevent the process from completing. For example, due to a delay/problem in obtaining a response from the HMRC department. If this situation occurs a message is displayed on screen and the process is ended. The VAT Return can be completed at a later time using the 'Submit' button on the Dashboard.
The Submission Status Explained
- 'Draft' Either a new or rejected VAT Return. Rejected returns can be amended and re-submitted as normal.
- 'Acknowledged' The VAT Return has passed the initial validation by the Government Gateway and is awaiting a response from the HMRC department. The submission process can be continued using the 'Submit' button on the Dashboard. Note: If the subsequent HMRC response rejects the return, the status is changed back to 'Draft'.
- 'Accepted' The VAT Return has been accepted by the HMRC department.
- 'Reset' Either an incomplete submission or test submission is awaiting finalisation. This is merely a tidying up exercise required by HMRC. The process can be completed using the 'Submit' button on the Dashboard.
There are 3 product options available. The unlicensed 'Test Evaluation' product option is free of charge and can be downloaded from this website. The 'Trader' and 'Agency' product options are subject to an annual licence fee. The 'Test Evaluation' workbook can be upgraded to a licensed 'Trader or 'Agency' product option by activating a Licence Key. See section below on 'How to Upgrade to a Licensed Product'.
How to purchase a software licence and activate the product
- Email your Purchase Order to 'support@NeilsonJamesTech.co.uk' specify the Licence Type (i.e. 'New VAT Return Trader Licence' or 'New VAT Return Agency Licence') and include your VAT Registration No (where registered), Company Name, Address, Telephone and Contact Name.
- You will then receive an email containing your new Licence Key, Customer Account Number and a Purchase Invoice.
- Download and open the 'Test Evaluation' workbook or if you prefer, open your already configured 'Test Evaluation' workbook.
- Then click the 'Purchase Licence' button on the Dashboard.
- Finally, enter your Company Name, VAT Registration No/Customer Account Number, Licence Type (Trader or Agency) and the Licence Key, then click 'Activate'.
For VAT Registered Customers: Your VAT Registration No will be used as your Customer Account Number.
For Customers not VAT Registered: Please indicate this in your email and a Customer Account Number will be allocated to you.
How to Renew a software licence
- Email your Purchase Order to 'support@NeilsonJamesTech.co.uk' specify the Licence Type (i.e. 'Renew VAT Return 'Trader' licence' or 'Renew VAT Return 'Agency' licence') and include your VAT Registration No (Where Registered), Company Name, Address, Telephone and Contact Name. In Addition, please provide the 'Customer Account Number' allocated to you during your intial purchase.
- You will then receive an email containing your new Licence Key and a Purchase Invoice.
- Open your workbook, Then click the 'Renew Licence' button on the Dashboard.
- Finally, check your Company Name and VAT Registration No/Customer Account Number, select the relevant Licence Type (Trader or Agency), enter the Licence Key and click 'Activate'