CIS deductions reconciliation for subcontractors
“match the xero sales invoices to the contractor payment and deduction statements and our bank receipts, show invoice total, expected deduction, amount deducted, amount received and status: Agrees, No statement, Wrong deduction, Receipt differs”
match the Xero sales invoices to the Payment and deduction statements (from your contractors) and QuickBooks bank receipts, show invoice total, expected deduction, amount deducted, amount received and status: Agrees, No statement, Wrong deduction, Receipt differs, using 20% of invoice labour
What the agent built
7 steps, each one an ordinary Mammoth step you can open and change.
- FileXero sales invoicesCSV export · 24 rows
- FilePayment and deduction statements (from your contractors)CSV export · 22 rows
- FileQuickBooks bank receiptsCSV export · 24 rows
- StepNumber invoice linesStep 1
- StepNumber statement linesStep 2
- StepFind receipt invoice numbersStep 3
- JoinMatch statement lines22 of 24 paired
- StepKeep one statement per invoiceStep 5
- JoinMatch bank receipts24 of 24 paired
- StepShow invoice match statusStep 7
- ResultInvoice payment reconciliation24 rows · 4 flagged
- ScheduleRuns when you sayProposed
On the sample files, every step runs and the result has 24 rows.
- Download Xero sales invoices sample (24 rows)Xero: Business > Invoices > All Invoices > Export
- Download Payment and deduction statements (from your contractors) sample (22 rows)Each contractor sends you a payment and deduction statement within 14 days of the end of every tax month; key each one into a sheet with one row per invoice
- Download QuickBooks bank receipts sample (24 rows)QuickBooks: Reports > Transaction Detail by Account > choose the bank account > Export to Excel
What you get back
The result of that run, the rows to look at first.
| Invoice number | Invoice total | Expected deduction | Amount deducted | Amount received | Status |
|---|---|---|---|---|---|
| INV-1007 | 230 | 38 | · | 192 | No statement |
| INV-1018 | 300 | 37 | · | 258 | No statement |
| INV-1004 | 360 | 40 | 60 | 323 | Wrong deduction |
| INV-1015 | 250 | 34 | 50 | 200 | Wrong deduction |
| INV-1001 | 240 | 30 | 30 | 210 | Agrees |
| INV-1002 | 300 | 44 | 44 | 256 | Agrees |
| INV-1003 | 300 | 36 | 36 | 264 | Agrees |
| INV-1005 | 200 | 35 | 35 | 165 | Agrees |
| INV-1006 | 360 | 48 | 48 | 312 | Agrees |
| INV-1008 | 360 | 41 | 41 | 319 | Agrees |
| INV-1009 | 240 | 32 | 32 | 208 | Agrees |
| INV-1010 | 300 | 42 | 42 | 258 | Agrees |
How it works
How to use the Invoice payment reconciliation workflow
- 1Get early accessWe invite people in small batches, job by job. You'll land on this workflow, already built and working on sample files.
- 2Swap in your exportsUpload your own files as they come out of your tools. The agent checks the workflow on your rows.
- 3Change it in words, then turn it onSay what to change and see the new result before anything saves. Then choose when it runs.
Invoice payment reconciliation: questions
How do you reconcile CIS deductions?
Which export should you use?
What happens when a row does not match?
How do you change the rules?
Get early access to this workflow
It opens already built and working on sample files. Swap in your own exports to see your result.
Workflows like this
Bank to ledger, payouts to deposits, returns to books. Every row comes back matched, or flagged with the reason.
- Bank reconciliationEvery line matched, flagged for a date check, or missing from one sideSee the workflow Template
- Payment reconciliationEach payout matched to its bank deposit, flagged for a date check, or missingSee the workflow Template
- Match names to a master listEach record with its master name (and ID), and how it matchedSee the workflow Template
- Cash reconciliationEach day's takings matched to a bank deposit, or flagged as not bankedSee the workflow Template
- Match customer payments to open invoicesMatch bank payments to QuickBooks open invoices by customer and amount, and see what is still unapplied.See the workflow Built by the agent
- Compare two listsCompare two exported spreadsheet lists by email, see matches, and label rows where name and email agree or differ.See the workflow Built by the agent