Work sample 11 / 14Accounting clean-up

Xero clean-up and unbilled hire-day check for an equipment hire group

How I'd approach a year of loosely coded Xero invoices, duplicate customers and a paper asset log for an equipment hire group, so every asset on hire has an invoice line or sits on an unbilled hire-day report, and every change is logged.

Worked example on representative data.

Area: Accounting clean-up
Tools: Xero · Asset register · Airtable (optional)
Typical scope: US$2,000–4,000
Timeline: About 4 weeks · 4 fixed milestones

01Context

An established equipment hire group with several branches and a mixed fleet. It has a year of loosely coded Xero invoices, customers that exist more than once across branches, and an asset log kept on paper.

The systems in play

In the example the group wants to know how many hire days go unbilled, and to give the accountant a ledger that can be trusted.

Table 1Who uses what

System	What it does for the business
Xero	Invoices, contacts and the chart of accounts. Hire revenue, delivery and recoveries are mixed in one or two accounts.
Asset log (before)	A paper log of what went out, to whom and when it came back.
Asset register (after)	A table of every asset, branch and hire dates, checked against what was invoiced. An Airtable app is an optional extra that drafts hire invoices for approval.
Accountant	Needs revenue by type and branch, and needs changes to be traceable.

02Problem

What the owner sees

Hire days that went out but were never invoiced
Revenue that cannot be split into hire, delivery and recoveries
The same customer entered at two branches
No one able to say what was changed in the ledger and why

Clean-up work fails when it is done by hand with no record. Each change has to be made by rule where possible, reviewed where not, and logged.

Table 2Where the drift starts

Everyday situation	What goes wrong
Generic coding	Hire, delivery, fuel and damage charges all go to one sales account
Duplicate customers	Two contacts for one customer, each with part of the history
Hire extended by phone	The asset stays out, the invoice is not extended
Off-hire not recorded	An asset is back on the lot, but still shown as out
Transfers between branches	An asset moves branch mid-hire and the revenue goes to the wrong one
Locked periods	An old invoice cannot be edited, so a correction is made in the wrong place

03Approach

Four steps, in this order. Each one ends with a result that is checked.

LookA read-only export of a year of invoice lines, contacts and the asset log. Nothing is changed.
Agree the rulesThe target chart of accounts, the coding rules and the merge rules are written down and approved.
Clean upLines are re-coded by rule with a review queue, duplicates are merged after sign-off, and the asset log becomes a register.
CheckEvery asset on hire has an invoice line, or appears on an unbilled hire-day report, and every change is logged.

Table 3Coding rules agreed in writing (example settings)

If the line description contains	Code to
hire, day rate, weekly rate	Hire revenue: equipment
delivery, cartage, pick-up	Delivery income
fuel	Fuel recharge
damage, cleaning, missing	Damage and cleaning recoveries
no rule, or more than one rule	Review queue

04What the setup includes

What the setup includes, and why each piece is there. The detail of how it is configured is agreed in the scoping call.

Show the setup, layer by layer

Table 4The setup, layer by layer

Layer	What is set up and why
Backup and baseline	A full export of contacts, invoices and reports before anything changes, so every change can be compared to the original.
Chart of accounts	Hire revenue split by type, with branch as a tracking category.
Re-coding by rule	Invoice lines matched to the coding rules. Lines that match none or several go to a review queue for a person.
Duplicate contacts	Possible duplicates are listed with the evidence. A merge happens only after sign-off, because a merge in Xero cannot be undone.
Asset register	Each asset with branch, day rate, on-hire and off-hire dates and the invoice that covers them.
Unbilled hire-day report	For each asset, hire days on the register compared to days invoiced. The gap is the report.
Change log	Every re-code and merge is logged with the before and after, the user and the date.

How the data moves

Export and baseline
Rules approved
Lines re-coded
Review queue cleared
Duplicates merged after sign-off
Register checked against invoices
Unbilled hire-day report

05Edge cases

These are agreed with the accountant and tested on a copy of the data before anything is changed in Xero.

Table 5Edge cases and how each is handled

Situation	How it is handled
Invoice in a locked period	Not edited. A journal in the open period, approved by the accountant, moves the amount.
Reconciled or paid invoice	Re-coded only where Xero allows it, and the payment and bank match are left as they are
Contacts with the same name at different companies	Kept separate. Matching also uses email, phone and address.
Merge of two contacts	Backed up first and signed off by the owner of the account. It cannot be undone in Xero.
Hire extended after the invoice	The extra days appear as unbilled on the report until a further invoice or an approved credit covers them
Minimum hire period or weekend rate	Day counts follow the rate rules in writing, so the register and the invoice use the same count
Transfer between branches mid-hire	Revenue follows the branch that held the asset on each day
Bond or deposit held	Recorded as a liability, not as revenue, and released against the final invoice
Credit note	Counted against the days it covers, so a credited day is not reported as unbilled
Invoice-number conflicts	Numbers are never changed by the clean-up. A conflict is listed for a person to resolve.
Who changed what	Every change is in the change log, with the user, the date and the reason

06Worked example

The tables below use representative data for one fleet. They show the shape of the checks, not a client result.

Table 6Re-coding run (sample data)

Step	Lines
Invoice lines reviewed	2,400
Coded by rule	2,052
Sent to the review queue	348
Queue: re-coded by a person	301
Queue: left as they were, with a note	47

Table 7Duplicate contacts (sample data)

Step	Pairs or contacts
Possible duplicate pairs found	126
Merged after sign-off	98
Kept separate (different companies, same name)	28
Contacts before	1,340
Contacts after	1,242

Table 8Hire days against invoices, September (sample data)

Asset	Branch	Day rate (US$)	Hire days	Days invoiced	Unbilled days	Unbilled value (US$)
A-101 Mini excavator	North	210	14	14	0	0
A-114 Generator	South	85	28	21	7	595
A-122 Scissor lift	North	140	13	13	0	0
A-130 Trailer	East	60	16	10	6	360
A-141 Compactor	South	95	7	7	0	0
A-150 Light tower	East	120	11	0	11	1,320
Total			89	65	24	2,275

07How the result is checked

Each stage has its own check, and the change log lets the accountant compare any figure to the baseline.

Table 9Checks and pass conditions

Check	How	Pass condition
Totals unchanged	Invoice totals and tax before and after re-coding	Equal. Only the account and tracking change.
Coverage	Every invoice line compared to a rule or a review decision	No line left uncoded
Merges	Contact history before and after each merge	Every invoice still belongs to one contact
Register to invoices	Hire days on the register compared to days invoiced	Every on-hire asset has an invoice line or sits on the unbilled report
Accountant review	A sample of changes compared to the baseline	Differences explained
Change log	Every change has a before and after	Complete

08Scope and milestones

A fixed scope agreed in writing before anything starts. Each milestone is paid when you sign off its deliverable.

Table 10Scope: what a project like this covers

Area	Included
Health Check	Read-only export and review of invoices, contacts and the asset log, a 45-minute call, written findings and a fixed quote
Rules	Target accounts, coding rules and merge rules, approved in writing
Re-coding	Invoice lines re-coded by rule, with a review queue
Contacts	Duplicates listed, merged after sign-off, with a backup first
Asset register	Built from the paper log and checked branch by branch against invoices
Unbilled report	A hire-day report and a repeatable weekly check
Optional extra	An Airtable app that drafts hire invoices for approval, quoted separately
Handover	Written SOPs, a recorded walkthrough and files you own, plus 14 days of fixes after go-live
Not included	Tax filing or payroll.
Also outside the scope	Changing past tax or filings. Moving to different accounting software. Software subscriptions (billed to you by the vendors).

Table 11Fixed milestones

Milestone	Deliverable	Timing	Share
M1 Health Check and rules	Findings, a fixed quote, backup and rules in writing	Week 1	20%
M2 Re-coding and review queue	Lines coded by rule, queue cleared, change log in place	Weeks 2 to 3	35%
M3 Contacts and register	Duplicates merged after sign-off, register built	Week 3	30%
M4 Unbilled report and handover	Report, SOPs, walkthrough, 14 days of fixes	Week 4	15%

Typical scope for this kind of project: US$2,000–4,000

About 4 weeks across 4 fixed milestones. The Systems Health Check (US$300, fixed) comes first and confirms the scope.

Describe what is going wrong between your job system and your books, and I'll say where I'd 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 Health Check is the right first step.

Get in touch All work samples