VAT MTD Reconciliation

Complyax provides two distinct operating modes for VAT reconciliation, each suited to a different stage of a client’s integration maturity. Both modes sit within the VAT MTD module, accessible from the main navigation.

Understanding the Two Modes

At the top of the VAT MTD page, there are two tabs:

Mode 1: MTD File-Based Reconciliation (Manual)

This mode allows a preparer to upload two CSV files — one exported from the HMRC portal and one from the bookkeeping system — and run a fuzzy-match reconciliation between them.

Step 1: Select Client and Period

1. Using the Client selector in the top-right header, select the relevant client.
2. If the client has an HMRC MTD connection, the VAT Period dropdown will automatically populate with their open and fulfilled VAT obligations pulled directly from HMRC.
3. Select the appropriate VAT Period (HMRC Key) from the dropdown (e.g., 24A1, 24A2). Open periods are marked “(Open)” and previously submitted periods are marked “(Fulfilled)”.

Step 2: Upload Reconciliation Files

In the VAT MTD Configuration panel, you must supply two data sources:

File 1 — MTD CSV (HMRC) (Mandatory)

• This is the VAT return data as recorded by HMRC.
• Export this CSV from the HMRC Online Services portal or from the VAT returns section of the client’s filing agent account.
• Required columns: Supplier, VAT Number, Invoice Reference, Date, Net Amount, VAT Amount.

File 2 — Bookkeeping CSV (Xero/QB) (Mandatory unless using live integration)

• This is the transaction ledger as recorded in the client’s bookkeeping system.
• Export from Xero: Accounting → Reports → VAT Return → Export Transactions
• Export from QuickBooks: Reports → VAT Detail Report → Export
• Required columns: Supplier/Vendor/Name, VAT Number, Invoice Ref/Reference, Date, Net/Amount/Subtotal, VAT/Tax.

Alternative: Use Live Bookkeeping Data

If the client has an active Xero or QuickBooks OAuth connection that has not expired, a toggle appears:

• Use Uploaded CSV — Uses your manually uploaded bookkeeping CSV.
• Fetch Live Xero/QuickBooks Data — Pulls transaction ledger data directly from the connected bookkeeping system via API for the selected period.

Step 3: Preview Data (Optional)

Click Preview Bookkeeping Data to review the parsed ledger rows before running the reconciliation. The preview table shows: Supplier, VAT No., Invoice, Date, Net (£), VAT (£).

This step is recommended to verify column mapping was parsed correctly from the uploaded CSV.

Step 4: Execute Reconciliation

Click Reconcile Now. The system will:

1. Parse both data sources (uploaded CSV or live integration data).
2. Apply a Dice similarity fuzzy-match algorithm (threshold: 0.6) to match transactions by supplier name, VAT number, and invoice reference.
3. Classify each transaction row into one of the following anomaly types:

4. Generate an AI VAT Clawback Risk Review — an AI-drafted narrative summarising the highest-risk items, with specific attention to potential HMRC clawback exposure.

Step 5: Review Results

The results screen presents:

Left Panel — Fuzzy Match Results Table

Shows every transaction with its Supplier, VAT Number, Invoice Number, Date, Net Amount, VAT Amount, Anomaly Type (colour-coded badge), and Notes.

• Green “MATCH” — No action required.
• Red “VALUE_MISMATCH” — The VAT or net amount diverges between sources. Investigate and correct in the bookkeeping system.
• Purple “MISSING_EVIDENCE” — Request the supporting document from the client.
• Amber “DUPLICATE” — Review the bookkeeping system for duplicate postings.
• Orange “RATE_ANOMALY” — Verify whether the applied VAT code is correct for this expense type.

Right Panel — AI VAT Clawback Risk Review

An AI-generated narrative identifying the highest-risk items. This is a draft only and requires accountant review before any action is taken.

Assign to Team Member

Use the Assign to dropdown (top right of results) to assign the reconciliation run to a specific team member for follow-up.

Export to Excel

Click Export Excel to download the full results table in .xlsx format for offline review or client delivery.

Step 6: Post-Reconciliation Action Panels

Below the history table, three action panels are displayed:

AI Risk Notes

• Lists flagged high-risk items with severity (High / Medium) and status.
• An accountant can click Confirm Issue to formally log the risk, or Dismiss if the flag is not applicable (e.g., intentional zero-rating under a valid exemption).
• Confirmed and dismissed items can be undone using the Undo button.

Missing Evidence

• Lists transactions where supporting invoices are absent.
• Click the severity badge button next to any item to send an immediate document request to the client through the platform.

Ready for Review

• Lists VAT periods where all anomalies have been resolved or explained.
• Click Mark Ready to formally move a period into the Review Queue for Admin sign-off.

Mode 2: Live HMRC MTD Integration — 9-Box Shadow System

This mode provides a full shadow audit of the client’s VAT position by comparing bookkeeping ledger data (from Xero or QuickBooks) against the figures recorded in the HMRC MTD VAT portal across all nine statutory VAT return boxes.

This mode is intended for:

• Pre-submission review of an Open VAT obligation (verifying the 9-box figures before they are submitted to HMRC).
• Post-submission comparison audit of a Fulfilled obligation (checking whether the submitted figures matched the bookkeeping records).

Step 1: Connect HMRC MTD

The HMRC Making Tax Digital (MTD) Connection card at the top of the tab shows the current connection status.

1. If not connected, click Connect HMRC Portal.
2. You will be redirected to HMRC’s OAuth authorisation page.
3. Log in using the client’s Government Gateway credentials or Agent Authorisation credentials.
4. Upon successful authorisation, the status card will update to Connected.

To disconnect, click Disconnect HMRC and confirm. This removes the stored OAuth token for the selected client.

Step 2: View HMRC VAT Obligations

Once connected, the HMRC VAT Obligations table automatically populates with all VAT return periods retrieved directly from HMRC, showing:

Step 3: Run the 9-Box Shadow Reconciliation

Click Run Shadow Recon (for an open obligation) or Compare Return (for a fulfilled obligation).

The system submits a background job via the BullMQ/Redis queue. Processing occurs asynchronously in a background worker thread. A live progress bar and processing log are displayed in real time as the job runs.

The background worker performs the following steps:

1. Fetches all bookkeeping transactions for the period from Xero or QuickBooks via live API.
2. Classifies each transaction into the appropriate VAT box using the firm’s UK tax code mapping rules.
3. Aggregates the totals for all nine statutory VAT boxes (Box 1 through Box 9).
4. For fulfilled periods only: fetches the values already submitted to HMRC and computes box-by-box variance amounts.

Step 4: Review the 9-Box Variance Report

Once completed, the 9-Box Shadow System Variance Report table is displayed:

For each box, the table shows:

• HMRC Portal Value — the figure already submitted to HMRC (shown only for Fulfilled periods; shown as ”—” for Open periods).
• Shadow System Value — the figure calculated from the live bookkeeping data.
• Variance Amount — the difference between HMRC and Shadow System values (shown only for Fulfilled periods). Zero variance is shown in green; any non-zero variance is shown in red.
• Risk Level — LOW / MEDIUM / HIGH, calculated by the system based on the magnitude of the variance.

Box 5 is additionally labelled Payable or Reclaimable based on whether Box 3 exceeds Box 4 or vice versa, both for the HMRC-submitted figure and the shadow system figure.

Step 5: Review the Bookkeeping Ledger Entries

Click View Bookkeeping Ledger Entries Audit Trail to expand a paginated table of all individual ledger entries that were used to calculate the shadow system 9-box figures.

Each entry shows: Transaction ID, Date, Source (Xero/QB), Tax Code, Description, Net Amount (£), VAT Amount (£).

Pagination is in pages of 25 entries. Use Previous and Next to navigate.

Step 6: Review HMRC Liabilities and Payments

Below the variance report, two additional data panels are displayed (available when HMRC is connected):

HMRC Liabilities Outstanding VAT balances owed to HMRC, showing: Tax Period, Liability Type, Original Amount, Outstanding Amount, and Due Date. Outstanding amounts in red indicate amounts still owed; zero outstanding is shown in green.

HMRC Clearances & Payments Payments that HMRC has confirmed as received and cleared, showing: Date of clearance and Payment Amount (£).

KPI Dashboard (Top of Page)

Across both tabs, four KPI cards are displayed at the top of the VAT page:

Recent Reconciliation Run History

Below the configuration panel (in File-Based mode) and in the Recent 9-Box Runs sidebar (in Live mode), a chronological history of all past reconciliation runs for the selected client is displayed. Each run shows:

• Run ID and timestamp
• Status: QUEUED (waiting in background queue) | RUNNING (in progress) | COMPLETED | FAILED
• A STALE label appears on jobs that were queued or running more than 60 minutes ago and have not completed (indicating a worker failure).

Click a completed run to view its detailed variance report.

Compliance Notes for Practitioners

• All reconciliation runs are permanently stored in the database with full timestamps and status history. This creates an auditable trail of all VAT preparation activity.
• The AI Risk Review narrative is generated using the flagged anomalies as input context. It is not a substitute for professional judgement.
• The system checks for Post-Brexit Postponed VAT Accounting (PVA) and Domestic Reverse Charge (DRC) conditions during the rules engine classification phase.
• The fuzzy-match threshold of 0.6 (Dice similarity) is calibrated to catch supplier name variations (e.g., “BT PLC” vs. “British Telecom plc”) while minimising false positives.