Work snippet
Xero clean-up with a mapped chart of accounts and an exceptions list
Concept DesignIllustrative integration walkthrough. Business details, figures and records are worked examples.
Years of use had left this Xero file cluttered: overlapping expense accounts, the same supplier set up more than once, and a catch-all account holding whatever did not fit. Changes are mapped in a sheet first, then Make applies them in batches with a trial run each time, and anything needing a judgement call goes on a short exceptions list.
The problemDuplicate accounts, duplicate suppliers and a catch-all account make reports untrustworthy.
What changesA mapped, batch-applied clean-up with every open decision on one short list.
- Area
- Xero clean-up
- Tools
- Xero · Make · Google Sheets
- Typical scope
- US$140-450
- Timeline
- About 4 weeks · fixed milestones
The situation and what was breaking
None of the entries was an error by itself. Taken together, the reports could not be relied on.
Over several bookkeepers the chart of accounts had grown to 214 accounts, with four for vehicle costs and three for subcontractors. Suppliers existed two or three times under slightly different names, and anything unclear went to a catch-all account that held $31,840 by the time of the review.
The owner wanted management reports that meant something, without losing history or breaking the bank rules that already worked.
- Profit and loss lines nobody could explain
- Duplicate suppliers splitting spend across records
- $31,840 parked in a catch-all account
- Bank rules pointing at archived accounts
- Every new bookkeeper adding accounts of their own
What good looks likeA chart I can read in one page, one record per supplier, and a list of the few things I need to decide.
Discovery findings
What a read-only review checks first: an export of the chart of accounts, contacts, bank rules and 24 months of transactions, reviewed in a mapping sheet before anything changed.
| ID | Finding | Evidence | Risk if left |
|---|---|---|---|
| F1 | Accounts with overlapping purpose | 96 accounts in 22 groups | Spend split, reports unreadable |
| F2 | Duplicate contacts | 234 likely duplicates in 1,046 | Spend by supplier wrong, payments misapplied |
| F3 | Catch-all account balance | $31,840 across 212 lines | Costs hidden from the right lines |
| F4 | Bank rules on accounts to be archived | 27 rules | Rules break when accounts are archived |
| F5 | Tracking categories half used | Two schemes, both partial | Branch reporting impossible |
| F6 | Locked periods not set | No lock date | Old periods editable by accident |
Architecture: Xero clean up
The mapping sheet is the plan and the audit trail. Make reads it, runs a dry run that writes only to a log, and applies a batch only after the dry run is signed.
Scroll sideways to see the full drawing
The screens your team will use
The plan and the change log are both sheets the owner can read. Xero only changes once a batch is signed.
Batch plan
| Old account | Code | → New account | Lines | Status |
|---|---|---|---|---|
| Motor vehicle expenses | 6140 | Vehicle running costs | 412 | Signed |
| Car costs | 6145 | Vehicle running costs | 96 | Signed |
| Fuel | 6150 | Vehicle running costs | 388 | Signed |
| Subbies | 6310 | Subcontractors | 140 | Batch 3 |
| Contract labour | 6320 | Subcontractors | 58 | Batch 3 |
| Sundry | 6990 | Split by line | 212 | Decision |
Every old account has one destination and a reason. Split rows go to the exceptions list.
Merges only after open bills and bank rules on the duplicate are moved.
Each batch ends with a balance check: totals by new account must equal the sum of the old ones.
How it is built
Plan everything first, then change in small signed batches, with the reports checked after each one.
- Module AExport and lock
Full export of accounts, contacts, rules and history; a lock date set before work starts.
- Module BMapping sheet
Old to new for every account, merge pairs for contacts, a reason for each row.
- Module CDry run
Make predicts every change into a log without writing to Xero.
- Module DBatches
Applied in five signed batches, smallest risk first, with a balance check after each.
- Module ECatch-all
The $31,840 is split line by line to the right accounts or listed as a decision.
- Module FRules and tracking
Bank rules repointed, one tracking scheme, and a short guide for new accounts.
Field mapping and rules
The mapping sheet columns are the contract. Every change in Xero can be traced back to one signed row.
| Column | Meaning | Rule |
|---|---|---|
| Old code and name | As exported | Never edited |
| New account | Destination | Must exist and be active |
| Reason | Why the move | Required |
| Batch | 1 to 5 | Lowest risk first |
| Dry run result | Predicted lines and amount | Must match the export |
| Signed by | Bookkeeper and owner | Both before apply |
| Signal | Weight | Action |
|---|---|---|
| Same business number or bank account | Strong | Merge pair proposed |
| Same email domain and similar name | Medium | Reviewed by a person |
| Similar name only | Weak | Listed, not merged |
| Open bills on the duplicate | Block | Move bills first |
Worked figures
Walkthrough arithmetic: the file before and after the five batches.
Overlapping accounts merged, by group
QA and testing checklist
Every check runs in a copy or on test records first, and each one is ticked with evidence before go-live.
- T1Lock dateSet before the first batch; no change lands before it.
- T2Dry run equals applyLines changed equal the dry run count, batch by batch.
- T3Balance checkTotals by new account equal the old totals after each batch.
- T4RulesEvery bank rule still points at an active account.
- T5ContactsNo merged duplicate has open bills or invoices.
- T6ReportsP&L for the last 12 months agrees in total before and after.
- T7ArchiveOld accounts archived only at zero activity.
- T8RollbackEach batch can be reversed from its log.
Scope
| Area | Included |
|---|---|
| Review | Export of accounts, contacts, rules and 24 months of history, with findings |
| Mapping sheet | Every account and merge pair with a reason |
| Make scenario | Dry run and batched apply with balance checks |
| Catch-all clearing | Lines recoded or listed as a decision |
| Rules and tracking | Bank rules repointed, one tracking scheme |
| Exceptions list | Decisions with an owner and due date |
| Not included | Year-end adjustments, or work for the outside accountant's own file |
Milestones, week by week
- Week 1
Review and plan
- Export and lock date
- Mapping sheet drafted
- Week 2
Batches 1-2
- Dry runs signed
- Accounts merged
- Contacts merged
- Week 3
Batches 3-5
- Catch-all cleared
- Rules repointed
- Tracking
- Week 4
Handover
- Exceptions list
- New-account guide
- 14 days of fixes
Handover and keeping it running
Handover pack
- Mapping sheet the full record of what moved and why
- Change logs one per batch, reversible
- New-account guide when to add an account and when not to
- Exceptions list nine decisions with owners
- Recorded walkthrough the new chart and the reports
- 14 days of fixes for defects in the agreed scope
Keep it running
Files get messy again one account at a time. The guide says when a new account is justified, and a monthly check lists any account created that month.
- Monthly: list new accounts and contacts created
- Monthly: catch-all account at 0.00
- Quarterly: duplicate contact check rerun
- Optional support hours at US$35 per hour, agreed in writing
Typical scope for this kind of project: US$140-450
About 4 weeks · fixed milestones. The systems health check (US$150, fixed) comes first and confirms the scope. Extra hourly work is US$35 per hour, agreed before it starts. After handover, a set block of hours each month is available at the same rate, agreed in writing.
Discuss an engagement.
Outline the operational problem through the contact form. I respond with a recommended starting point, including whether the US$150 systems health check is the appropriate first step.