Work snippetReconciliation
Xero bank-feed and job-payment reconciliation, with an exceptions queue
How I would approach about 1,900 bank lines a month for a 40-tech HVAC contractor with three branches, so clear payments are matched by rule, grouped and part payments are proposed with a reason, and the rest go to a queue with an owner.
Work snippet with example figures.
- Area
- Reconciliation
- Tools
- Xero · ServiceM8 · Make
- Typical scope
- US$2,200–3,800
- Timeline
- About 5 weeks · 4 fixed milestones
Context and systems map
A 40-tech HVAC contractor with three branches, a finance lead and two accounts staff. Customers pay by card, direct deposit, cheque and through a payments provider. Job invoices come from ServiceM8 and the bank feed lands in Xero, and about 1,900 bank lines a month have to be matched by hand.
The systems in play
In the example a matching layer reads each bank line, finds the job invoice or invoices it pays, proposes the match with a reason, and sends anything it cannot settle to an exceptions queue with an owner.
| System | What it does for the business |
|---|---|
| Xero bank feed | Source of bank statement lines, one feed per bank account. |
| ServiceM8 | Source of job invoices, job numbers and payment status. |
| Matching layer | A scheduled flow that scores each bank line against open invoices and proposes a match. Make in the example. |
| Exceptions queue | A list of lines that need a person, with the reason, the candidate invoices and an owner. |
| Finance lead | Owns the rules and approves any change to them. |
| Accounts staff | Work the queue daily. |
How the data moves
- Bank feed line
- Rules and scoring
- Proposed match
- Auto-approve or queue
- Xero payment applied
- ServiceM8 marked paid
- Daily summary
Problem
What the owner sees
- Accounts staff spending days matching bank lines
- Customers who paid but still get a chase email
- Payments in Xero that never reach the job system
- A bank reconciliation that is always a week behind
Matching is mostly pattern: the reference, the amount, the name. A person does it well but slowly, and the errors are quiet: a payment applied to the wrong invoice, a grouped payment split by guesswork, a deposit nobody can place. The goal is to settle the clear cases by rule and to make the unclear ones visible with context.
| Everyday situation | What goes wrong |
|---|---|
| Customer pays three invoices in one transfer | The line is applied to one invoice and the other two stay overdue |
| Reference holds a job number with a typo | The line sits unmatched until someone searches by hand |
| Card payments paid out net of fees | The amount never equals an invoice, so nothing matches |
| Part payment | The invoice is marked paid or left open, never partly paid |
| Duplicate payment | Both sit on the account and the customer is never refunded |
| Payment received for a cancelled job | It is applied to the next open invoice for that customer |
Approach
Four steps, in this order. Each one ends with a result that is checked.
- LookRead-only review of three months of bank lines and invoices. Every line is classified by hand to build the rule set and the target match rate.
- Agree the rulesMatch keys, tolerance, grouped payments, part payments and what is never matched automatically are written down and signed.
- Build and testThe matching layer runs in suggest-only mode against three past months, then on live lines with a person approving every match.
- Go live in stepsExact matches auto-apply first. Grouped and fuzzy matches follow only after a week with no wrong match.
| Item | Example setting |
|---|---|
| Exact match | Invoice number or job number in the reference, and the amount equal to the open balance |
| Amount tolerance | Zero for auto-apply; up to 0.50 for a proposal, never auto-applied |
| Grouped payments | Allowed when the total equals the sum of open invoices for one customer, and the references name each one |
| Part payments | Applied as a part payment, never rounded up to paid |
| Never automatic | Overpayments, payments with no customer match, and payments over 10,000.00 |
| Queue ownership | Every queue line has an owner and a due date of the next business day |
| Write-back | Matched payments marked in ServiceM8 within 15 minutes |
| Change control | Rule changes approved by the finance lead and logged |
Flow and data mapping
The first table shows the decision path for each bank line. The second shows the fields the layer reads and writes.
| Step | Question | If yes | If no |
|---|---|---|---|
| 1 | Is it a receipt (money in)? | Continue | Ignore and log |
| 2 | Does the reference name an invoice or job? | Look up open balance | Go to step 4 |
| 3 | Does the amount equal the open balance? | Auto-apply payment | Proposal with reason |
| 4 | Does the name and amount match one customer? | Proposal for approval | Exceptions queue |
| 5 | Is it a group of invoices? | Propose a grouped payment | Exceptions queue |
| 6 | Is it a duplicate of a payment already applied? | Queue as possible duplicate | Continue |
| Source or target | Field | Use |
|---|---|---|
| Xero bank line | Date, amount, payee, reference | Match keys |
| Xero invoice | Number, contact, amount due, due date | Candidates for the line |
| ServiceM8 job | Job number, invoice number, status | Cross-check and write-back |
| Xero payment | Account, date, amount, reference | Created when a match is approved |
| Queue record | Line id, reason, candidates, owner, status | Worked daily |
| Match log | Line id, rule used, user or automatic, time | Append-only audit trail |
Edge cases handled
Agreed in advance with the finance lead and the operations manager, and tested before go-live.
| Situation | How it is handled |
|---|---|
| Reference has a transposed digit | Fuzzy match proposes the nearest invoice for the same customer; a person approves |
| Payment from a parent company | Payee name mapped to a customer group, listed for approval |
| Payment equals two invoices minus a credit note | Credit note included in the proposal and shown in the reason |
| Customer pays twice | Second line queued as a duplicate with the first one shown; refund or credit decided by the finance lead |
| Payments provider batch | Payout split into its charges and fees before matching, one customer line each |
| Bank line appears twice in the feed | Detected by id and date, kept once |
| Invoice voided after proposal | Proposal expires; the line goes to the queue |
| Customer overpays | Never auto-applied; queued with the choice of credit or refund |
| Line already reconciled by hand | Skipped and logged |
Worked example
The tables below use one month of bank lines for an example business. They show the shape of the checks, not a client result.
| Outcome | Lines | Share |
|---|---|---|
| Auto-matched exact | 1,486 | 76.8% |
| Matched by rule (grouped payments) | 212 | 11.0% |
| Needs a person | 94 | 4.9% |
| Not a job receipt | 143 | 7.4% |
| Total lines | 1,935 | 100.0% |
| Item | Amount |
|---|---|
| Bank line, reference “Inv 4471 4480 4502” | 3,265.40 |
| Invoice 4471 | 1,420.00 |
| Invoice 4480 | 1,215.40 |
| Invoice 4502 | 960.00 |
| Less credit note CN-0188 | (330.00) |
| Sum of invoices less credit | 3,265.40 |
| Difference | 0.00 |
| Reason | Lines | Owner | Due |
|---|---|---|---|
| Possible duplicate | 9 | Accounts staff 1 | Next business day |
| Overpayment | 14 | Finance lead | Next business day |
| No customer match | 37 | Accounts staff 2 | Next business day |
| Part payment to confirm | 21 | Accounts staff 1 | Next business day |
| Amount outside tolerance | 13 | Accounts staff 2 | Next business day |
| Item | Value |
|---|---|
| Lines received yesterday | 71 |
| Applied automatically | 58 |
| Proposed and approved | 8 |
| In the queue | 5 |
| Oldest queue line | 1 day |
Monitoring and alerting
The layer is watched as closely as the books: a silent failure here means bank lines pile up unmatched, so the alerts focus on volume, age and drift.
| Signal | Threshold | Alert goes to | First action |
|---|---|---|---|
| Bank feed has no new lines | No lines for 36 hours on a business day | Finance lead by email | Feed connection checked with the bank |
| Matching run fails | Any failed run | Manaz Afzal and finance lead | Run replayed from the last good line |
| Queue age | Any line older than 2 business days | Finance lead | Reassigned, reason recorded |
| Auto-match accuracy drift | Any auto-applied line reversed by a person | Finance lead | Rule reviewed; auto-apply paused for that rule |
| Large amount | Any line over 10,000.00 | Finance lead | Never auto-applied; manual review |
| ServiceM8 write-back fails | Any failure | Accounts staff | Paid status set by hand and failure logged |
Test plan
Every check has a written pass condition and a written fail condition before it is run. A FAIL stops the next step until it is cleared and re-run.
| ID | Check | Method | PASS when | FAIL when |
|---|---|---|---|---|
| T1 | Exact match rule | Fifty past exact lines replayed | All matched to the same invoice a person chose | Any different invoice |
| T2 | Grouped payment rule | Twenty past grouped lines replayed | Proposal equals what was applied | Any proposal that differs |
| T3 | Tolerance | Lines 0.01 and 0.50 off | Proposed only, never auto-applied | Auto-applied |
| T4 | Duplicate detection | Same line inserted twice in test | Second line queued | Both applied |
| T5 | Overpayment | Test payment above balance | Queued with options | Auto-applied |
| T6 | Write-back | Approve ten matches | ServiceM8 shows paid within 15 minutes | Any missing |
| T7 | Reconciliation | Xero bank reconciliation for a test month | Statement balance equals Xero, zero unmatched | Any difference |
| T8 | Rule log | Change a rule in test | Change logged with user and time | No log entry |
Go-live and rollback
Go-live is in three steps, each with a one-week gate. Turning the layer off returns the bank lines to the normal Xero matching screen with nothing lost.
| Step | Owner | Gate to continue | Rollback trigger |
|---|---|---|---|
| Suggest-only on live lines | Manaz Afzal | One week, every proposal checked by a person | Wrong proposal rate above 2% |
| Exact matches auto-apply | Finance lead | T1, T3 pass; one week with no reversal | Any wrong auto-applied payment |
| Grouped matches auto-apply | Finance lead | T2 passes; one week with no reversal | Any wrong grouped payment |
| Daily summary and queue live | Accounts staff | Queue worked to zero for five days | Queue grows for three days |
| Rollback | Finance lead | Layer switched off, queue lines reconciled by hand in Xero | Decision recorded with the reason |
Scope and milestones
A fixed scope agreed in writing before anything starts. Each milestone is paid when you sign off its deliverable.
| Area | Included |
|---|---|
| Systems Audit | Read-only review of three months of bank lines, invoices and payments, a 45-minute call, written findings and a fixed quote |
| Rule set | Match, tolerance, group and exclusion rules signed by the finance lead |
| Matching layer | A scheduled flow that proposes and applies matches with a log |
| Exceptions queue | A list with reason, candidates, owner and due date |
| Write-back | Paid status sent to ServiceM8 for each applied payment |
| Daily summary | A short morning message with counts and the oldest line |
| Backtest | Three past months replayed against the rules, with a written accuracy report |
| Handover | Written SOPs, a recorded handover video and files you own, plus 14 days of fixes after go-live |
| Not included | Tax filing and statutory returns. |
| Also outside the scope | Changing bank arrangements. Chasing overdue customers. Payment provider contracts. Software subscriptions (billed to you by the vendors). |
| Milestone | Deliverable | Timing | Share |
|---|---|---|---|
| M1 Audit and rules | Findings, fixed quote, rules in writing | Week 1 | 20% |
| M2 Layer and backtest | Matching layer built, three months replayed, accuracy report | Weeks 2 to 3 | 35% |
| M3 Live in steps | Suggest-only, then exact matches auto-apply | Week 4 | 30% |
| M4 Handover | Grouped matches, queue and summary live, SOPs, handover video, 14 days of fixes | Week 5 | 15% |
Typical scope for this kind of project: US$2,200–3,800
About 5 weeks across 4 fixed milestones. The Systems Audit (US$300, fixed) comes first and confirms the scope.
Describe what is going wrong between your job system and your books, and I will say where I would look first.
Send a short note through the form on the home page. I will reply with where I would start, and whether the US$300 Systems Audit is the right first step.