CIS reconciliation Google Sheet: track subcontractor deductions, HMRC payments and Xero/QuickBooks checks

Category: Templates & Resources Niche: CIS, reconciliation, Xero, QuickBooks, HMRC

Contents

• What this template does
• Download and make a copy
• How to use it each month
• Reconcile in Xero or QuickBooks
• Common CIS issues and quick fixes
• Automations to save time
• FAQ

What this template does

This simple Google Sheet helps small UK contractors reconcile CIS every month without getting lost in journals and filters. It:

• Totals CIS deducted per subcontractor from your bills
• Matches your CIS300 return amount due by the 19th
• Tracks the HMRC payment or refund and the running balance
• Flags mismatches between your sheet and Xero/QuickBooks reports
• Keeps an audit trail for year-end and HMRC questions

Helpful context: HMRC expects monthly CIS returns and accurate CIS payment and deduction statements. Filing and guidance links: GOV.UK CIS overview and Monthly returns.

Download and make a copy

Make a copy of the template (File > Make a copy) and save it in your Finance > CIS folder in Google Drive alongside your CIS300 PDFs and subcontractor statements.

Columns included:

• Tax month (e.g., 2025-10)
• Subcontractor name and UTR
• Gross labour on bill
• CIS rate (20 percent or 30 percent)
• CIS deducted
• Materials (excluded from CIS)
• Bill reference and link (Drive or Xero/QB URL)
• Paid date
• Included on CIS300 (yes/no)
• HMRC paid (date, amount, reference)
• Running CIS liability balance

Tip: Use a separate tab called “HMRC payments” to log each payment with bank reference so you can tick off reconciliations fast.

How to use it each month

1. End of tax month (5th): export a list of CIS bills for the month from your accounts package.
2. Paste into the sheet. Check rates: 20 percent for verified subs, 30 percent for unverified per GOV.UK.
3. Confirm materials are separated so you only deduct CIS on labour.
4. Check the sheet’s Total CIS Deducted equals your CIS300 draft.
5. File the return by the 19th and send statements to each sub (see Xero’s guide to statements above).
6. Pay HMRC. Log payment on the sheet and reconcile the bank line.
7. If you’re due a refund (CIS suffered offset), log the refund and reconcile to the CIS control balance.

Reconcile in Xero or QuickBooks

Xero reference steps:

• Use Reports > CIS Contractor (Monthly Return, Online Filing and Statements) to cross-check totals and statements (Xero Central).
• For reconciling a payment to HMRC, see community guidance on reconciling CIS payments and fixing mismatches (Xero CIS reconciliation).

QuickBooks reference steps:

• Use the CIS report under Taxes > CIS to confirm the month’s total and statements.
• Reconcile the bank payment against the CIS liability account for that tax month.

Common CIS issues and quick fixes

• Materials not separated on the bill
  • Fix: create a non-CIS materials line and a labour line with the CIS code.
• Wrong CIS rate applied
  • Fix: verify the sub with HMRC and update their card in Xero/QB; correct the bill and refile if needed.
• CIS suffered refunds not matched
  • Fix: post the HMRC refund to the CIS control and tick off the open balance for that month. See Xero thread on reconciling CIS suffered.
• Return total does not equal your sheet
  • Fix: filter by tax month dates, exclude credit notes already applied, and double-check any late bills.

Automations to save time

• Google Drive + Xero/QuickBooks links: paste the bill URL in the sheet so reviewers can jump straight to source.
• Apps Script: auto-calc CIS per line and flag if materials are blank.
• Payment reminders: add a Calendar reminder on the 15th and a second on the 18th to avoid penalties.
• File naming: yyyy-mm-cis300.pdf, yyyy-mm-cis-liability.xlsx, and yyyy-mm-hmrc-payment.pdf so month-end packs are consistent.

FAQ

What dates does a CIS tax month cover?

HMRC’s CIS tax month runs 6th to 5th. File by the 19th following the 5th. Source: GOV.UK monthly returns.

What rates should I use?

20 percent for verified subcontractors; 30 percent if unverified. Source: GOV.UK CIS deduction rates.

Do I deduct CIS on materials?

No. CIS applies to labour only. Materials and certain direct costs are excluded.

How do I send CIS statements?

In Xero, use the CIS Contractor report to generate payment and deduction statements. See Xero guide.

What happens if I file late?

HMRC can issue penalties for late or incorrect returns. Keep a calendar reminder and reconcile weekly so you are not scrambling on the 18th.

Want to slash training times and increase revenue per Engineer? Join our Waitlist: https://trainar.ai/waitlist