Your VAT figures stay in your own spreadsheet — each quarter you'll upload it and Tax Optimiser reads the nine HMRC boxes straight out of it. VAT Settings is where you tell it which sheet and cells to read. You set this up once; after that the quarterly routine is just upload, check, send.
The VAT Settings screen
Open VAT → VAT Settings from the sidebar. You can also open the same settings without leaving your returns: choose Actions → VAT Settings on the VAT Returns list or on a return. The VAT Submission Software name at the top is what appears on HMRC submissions as the originating software — leave it as Tax Optimiser unless told otherwise.
Start from a software template…
If your spreadsheet is an export from a known system, pick it from Software Template — Sage, Access Dimensions, Dynamics NAV, MYOB, Manager.io and others are listed. Choosing one pre-fills the sheet name and all nine cell references; you can still adjust any of them afterwards.
…let AI find the cells…
If your spreadsheet has its own layout, Tax Optimiser can work out the mapping for you. Click Find the cells with AI, choose your spreadsheet (Excel .xlsx, .xls, .xlsm or .csv), tick the box to confirm you understand, then click Find cells with AI.
Before you use it, note that your spreadsheet is sent to a third-party AI provider so it can read the layout. Don’t use this option if that isn’t acceptable for the file.
AI fills in the sheet name and the nine cell references, and shows the figure it found under each cell reference. Each figure is read from your file the same way a normal upload reads it. If a cell holds text instead of a number, it is shown in red. If Box 3 doesn’t equal Box 1 + Box 2, or Box 5 doesn’t equal Box 3 − Box 4, you will see a warning.
Check every figure against your spreadsheet and correct any cell before you press Save. Nothing is saved until you do. AI can make mistakes, and you remain responsible for the return you send to HMRC.
…or map the cells yourself
For your own spreadsheet, fill the mapping in directly:
- Excel Sheet Name — the name of the worksheet tab along the bottom of Excel (for example VAT). It must match exactly, including spaces — it is not the filename.
- The nine cell references — one per HMRC box, such as B4. Each must point at the cell holding that box's final figure.
Press Save. If you'd rather adopt a ready-made layout, the upload screen (next article) offers a Download Example Front Sheet Excel File that matches the default mapping out of the box.
The nine boxes at a glance
- Box 1 — VAT due on sales and other outputs.
- Box 2 — VAT due on acquisitions of goods from EU member states (Northern Ireland traders only; 0 for most businesses).
- Box 3 — total VAT due: Box 1 + Box 2.
- Box 4 — VAT reclaimed on purchases and other inputs.
- Box 5 — net VAT due to (or reclaimable from) HMRC: Box 3 − Box 4.
- Box 6 — total value of sales excluding VAT, in whole pounds.
- Box 7 — total value of purchases excluding VAT, in whole pounds — including purchases that carried no VAT, so don't expect it to be exactly five times Box 4.
- Box 8 — total value of goods supplied to EU member states (NI traders only).
- Box 9 — total value of goods acquired from EU member states (NI traders only).
