Manaz Afzal Book a consultation

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.

What a read-only review checks firstWeek 1
IDFindingEvidenceRisk if left
F1Accounts with overlapping purpose96 accounts in 22 groupsSpend split, reports unreadable
F2Duplicate contacts234 likely duplicates in 1,046Spend by supplier wrong, payments misapplied
F3Catch-all account balance$31,840 across 212 linesCosts hidden from the right lines
F4Bank rules on accounts to be archived27 rulesRules break when accounts are archived
F5Tracking categories half usedTwo schemes, both partialBranch reporting impossible
F6Locked periods not setNo lock dateOld 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

Clean-up control flowA Xero export feeds a mapping sheet; Make runs a dry run, then applies signed batches to Xero, logging every change; items needing a decision go to an exceptions list.020040060080exportbatch 1..nchange logsignedcannot decidechangesA · FILEXeroAccounts, contacts,rules, historyB · PLANMapping sheetOld to new, mergepairs, reasonsC · DRY RUNMake, no writesPredicted changesto the logD · SIGN-OFFBatch approvalBookkeeperand ownerE · APPLYMake, batchedRecode, merge,archiveF · DECISIONSExceptionsOwner, due datenothing is written to Xerowithout a signed dry runhistory is recoded, never deletedCHANGE CONTROL · BATCHEDManaz Afzal
Plan in a mapping sheet, dry run, sign, apply, logSolid: live data path · Dashed: exception path

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.

Google Sheets · Account mappingLive

Batch plan

Old accountCode→ New accountLinesStatus
Motor vehicle expenses6140Vehicle running costs412Signed
Car costs6145Vehicle running costs96Signed
Fuel6150Vehicle running costs388Signed
Subbies6310Subcontractors140Batch 3
Contract labour6320Subcontractors58Batch 3
Sundry6990Split by line212Decision

Every old account has one destination and a reason. Split rows go to the exceptions list.

Xero · Merge contactsLive
Keep
ContactSupplier 031 Pty
Transactions312
Merge into it
Supplier 031
Supplier 031 (old)
Check
Open bills0 on merged records
Bank rules2 repointed
CancelMerge

Merges only after open bills and bank rules on the duplicate are moved.

Make · Scenario · apply batchLive
dry runapplyROWRead batchsigned rows46?Modedry run / applyLOGPredictwrite to logXRecode linesXero API1094AArchive oldafter zero✓Verifybalances equal!Exceptionscannot apply

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-level build6 modules
  1. Module AExport and lock

    Full export of accounts, contacts, rules and history; a lock date set before work starts.

  2. Module BMapping sheet

    Old to new for every account, merge pairs for contacts, a reason for each row.

  3. Module CDry run

    Make predicts every change into a log without writing to Xero.

  4. Module DBatches

    Applied in five signed batches, smallest risk first, with a balance check after each.

  5. Module ECatch-all

    The $31,840 is split line by line to the right accounts or listed as a decision.

  6. 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.

Mapping sheet columns
ColumnMeaningRule
Old code and nameAs exportedNever edited
New accountDestinationMust exist and be active
ReasonWhy the moveRequired
Batch1 to 5Lowest risk first
Dry run resultPredicted lines and amountMust match the export
Signed byBookkeeper and ownerBoth before apply
Contact merge rules
SignalWeightAction
Same business number or bank accountStrongMerge pair proposed
Same email domain and similar nameMediumReviewed by a person
Similar name onlyWeakListed, not merged
Open bills on the duplicateBlockMove bills first

Worked figures

Walkthrough arithmetic: the file before and after the five batches.

Before and afterWalkthrough figures
Accounts214 → 11896 merged
Contacts1,046 → 812234 merged
Catch-all$31,840 → $0212 lines recoded
Bank rules repointed27none broken
Batches5each dry-run first
Decisions listed9with owners

Overlapping accounts merged, by group

Vehicle4 → 1
Subcontractors3 → 1
Software5 → 1
Office6 → 2
Sundry3 → 0

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

AreaIncluded
ReviewExport of accounts, contacts, rules and 24 months of history, with findings
Mapping sheetEvery account and merge pair with a reason
Make scenarioDry run and batched apply with balance checks
Catch-all clearingLines recoded or listed as a decision
Rules and trackingBank rules repointed, one tracking scheme
Exceptions listDecisions with an owner and due date
Not includedYear-end adjustments, or work for the outside accountant's own file

Milestones, week by week

  1. Week 1

    Review and plan

    • Export and lock date
    • Mapping sheet drafted
    25% of the fee
  2. Week 2

    Batches 1-2

    • Dry runs signed
    • Accounts merged
    • Contacts merged
    30% of the fee
  3. Week 3

    Batches 3-5

    • Catch-all cleared
    • Rules repointed
    • Tracking
    30% of the fee
  4. Week 4

    Handover

    • Exceptions list
    • New-account guide
    • 14 days of fixes
    15% of the fee

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.

Related work snippets

Next step

Bring me the work that repeats.

I scope it in writing, test it against your current process on your own data and hand over everything I build.

Book a consultation