CASE FILE 02/13CLASS: SAMPLE BUILDDATA: REALISTIC, NOT CLIENTTYPE: Bookkeeping & reconciliation
Sample build: realistic data, built to show how I'd handle this work. Not client work.
Sample buildBookkeeping & reconciliation
Xero clean-up and hire billing check for a full year, with an Airtable, Glide and Make asset app
2,524 Xero invoice lines re-coded for a year, and 60,258.70 of unbilled hire found by rebuilding the asset log
- 2,524invoice lines re-coded or queued
- 98.2%coded by a rule
- 60,258.70hire under-billed, found from the log
- 29/29controls pass
01Summary
- In this scenario a fencing, scaffolding and roofing business has a year of Xero sales (2,538 lines) coded to two accounts, 153 contacts with duplicates, and hired equipment tracked on paper dockets.
- 2,478 of 2,524 lines (98.2%) are coded by 13 rules to four income accounts and a Service line tracking category; 46 go to a person, and 83 lines on the wrong tax rate are listed by BAS quarter.
- Rebuilding every 4-week hire cycle from the cleaned asset log shows 60 site-assets under-billed by 60,258.70 and 8 over-billed by 2,578.50; 29 of 29 formula controls pass.
2,478 of 2,524 lines (98.2%) are coded by 13 rules to four income accounts and a Service line tracking category; 46 go to a person, and 83 lines on the wrong tax rate are listed by BAS quarter.
Key point, computed from the sample files
02The scenario
In this scenario a fencing, scaffolding and roofing business codes all its income to 200 Sales or 260 Other Revenue, and 200 defaults to GST Free, so some sales go out with no GST. Contacts were keyed twice by different staff. Fence panels go out on paper dockets typed into a log later, and the 4-weekly hire invoices are raised from memory, so nobody knows what was on site against what was billed.
Source: sample files
03Issues in the data
Source: sample files
04How I approached it
- A rules engine (engine.py): 13 categorisation rules, contact matching by tax number then name and email domain, asset log clean-up, a rebuild of every hire cycle and a regression test.
- A workbook with 25 tabs and 26,141 formulas: re-coded invoice lines, revenue by account and service line, 12 recode journals, tax review by BAS quarter, contacts, a chart-of-accounts fix map, the clean asset ledger, fleet reconciliation and the hire billing check.
- An Airtable base, Glide screens and four Make scenarios, with a simulated July billing run (76 draft invoices) and a re-run that creates none.
- A 29-control tab, a test log, a 19-page report and a one-page executive summary.
Source: my design and the sample files
05How this applies to your business
- Week 1: ledger fixesPost the 12 recode journals, fix 200's default tax, create the new accounts and tracking, merge contacts.
- Week 2: decisionsThe owner clears the review queue; the accountant advises on BAS corrections; credit notes for over-billing.
- Week 3: assetsYard recount, catch-up invoices for confirmed gaps, Airtable loaded from the clean ledger.
- Week 4 to 8: app liveGlide for the yard, Make in draft-only mode beside the office for one cycle, then the office approves drafts instead of keying.
| Activity | Business owner | Specialist (me) | Your team |
|---|---|---|---|
| Post journals and chart changes | A | R | I |
| Decide review lines | R | C | I |
| Record movements in the app | A | I | R |
| Approve Make draft invoices | A | C | R |
| Change a rule or scenario | A | R | C |
Source: sample plan
06Controls and checks
| Check | What it catches | Status |
|---|---|---|
| Lines counted + voided = lines in the export | Nothing dropped or double counted | PASS |
| Income by rule (Category Rules) = income in the export | Every line falls under exactly one rule or the no-rule bucket | PASS |
| Lines by rule = counted lines | Same, by count | PASS |
| Revenue by Service total = income in the export | The new view loses nothing | PASS |
| Before (200 + 260) = after (Revenue by Service) | Re-coding moves income, it does not create it | PASS |
| Recode journals: debits = credits | Journals balance | PASS |
| Recode journals: months out of balance | Each monthly journal balances on its own | PASS |
| Recode journals: net credit to 4010-4040 and 835 = rule-coded income | Journals match the line-level coding | PASS |
| Rule-coded lines with no account | Every rule gives an account | PASS |
| Review queue = counted lines needing a person | Nothing needing a decision is hidden | PASS |
| Tax Review lines = counted lines with the wrong tax rate | Every tax error is listed | PASS |
| GST effect: Tax Review = Invoice Lines | Two independent calculations agree | PASS |
| Contacts whose master is missing or merged again | One level of merge, no chains | PASS |
| Contacts kept separate (similar name, different tax number) that were merged | No false merges | PASS |
| Invoice lines by contact = lines in the export | Every line belongs to a known contact | PASS |
| Duplicate dockets left in the clean log | Each docket counted once | PASS |
| Duplicate dockets removed = DUPLICATE issues | Every removal is on the issues list | PASS |
| Sites below zero at 30 June | Wrong-site returns all resolved | PASS |
| Asset Position total = net units in the log to 30 June | Position is complete | PASS |
| Fleet: should own - yard - on hire - not returned - variance | Fleet adds up | PASS |
| Stocktake variance = STOCKTAKE VARIANCE issues | Variance is raised, not absorbed | PASS |
| Billable unit-weeks: Billing Check = Hire Cycles in FY26 | Billing check is complete | PASS |
| Invoiced hire unit-weeks: Billing Check = Invoice Lines | Every hire line is matched to a site | PASS |
| Hire credits: Billing Check = Invoice Lines | Credit notes are counted | PASS |
| Make July run: repeated idempotency keys | A re-run cannot double bill | PASS |
| Make July run: amount = unit-weeks x rate | Draft amounts are right | PASS |
| Exceptions: billing rows = pairs not agreeing | Every billing gap is on the register | PASS |
| Exceptions: open asset issues | Every open asset issue is on the register | PASS |
| Engine regression: line coding disagreements | Engine matches the answer key on every line | PASS |
Source: sample files (Checks / Controls tab, test output or run log)
07What the files show
- 2,524invoice lines re-coded or queued
- 98.2%coded by a rule
- 60,258.70hire under-billed, found from the log
- 29/29controls pass
Source: sample files, measured by script. Sample data, not a client result.
- Income before (200 and 260) equals income after (4010 to 4040, 835 and the review queue): 1,758,394.15.
- Each of the 12 monthly recode journals balances, and together they equal the line-level coding.
- No site is below zero after the clean-up, and the fleet adds up: register, purchases and losses against yard, on hire and not returned.
- The engine agrees with the answer key on all 2,538 lines, 29 duplicate contacts, 83 tax errors and 408 hire site-assets.
08Risks and how the build handles them
| Risk | Mitigation built in |
|---|---|
| Re-coding changes reported income | Controls: before (200 + 260) equals after, and each monthly journal balances |
| Two different businesses are merged | Tax numbers are never ignored: 6 look-alikes are kept apart, and the test counts 0 false merges |
| A wrong-site fix moves the wrong docket | Each fix needs a single candidate and is listed with its reason; anything ambiguous stays open for the yard |
| Make bills a cycle twice | Idempotency key Site|Asset|Cycle start: the simulated re-run created 0 new drafts |
Source: sample design
09Appendix: files
A. Screenshots of the deliverable
Exhibit 8: Dashboard tab: income, tax, contacts, assets, billing and controls, every figure a formula
Source: sample files
Exhibit 9: Invoice Lines tab: each Xero line with its rule, category, new account, service line and expected tax
Source: sample files
Exhibit 10: Hire Billing Check tab: billable unit-weeks rebuilt from the asset log against unit-weeks invoiced, per site and asset
Source: sample files
Exhibit 11: Make scenarios tab: S1 to S4 module by module, with the idempotency check and error handling
Source: sample files
Exhibit 12: Airtable schema tab: tables and fields for sites, customers, assets, movements, hire periods, billing runs and exceptions
Source: sample files
Exhibit 13: Controls tab: 29 formula controls, all PASS
Source: sample files
Exhibit 14: Report page: target process from docket to paid invoice across the yard, Glide and Airtable, Make, Xero and the office
Source: sample files
Exhibit 15: Overview of the Xero hire billing sample build: invoice lines coded by rule, review queue, GST to correct, hire under- and over-billing and the July draft run
Source: sample files
B. Downloadable files
| File | What it is | Size |
|---|---|---|
| Xero-Hire-Billing-Cleanup-FY26.xlsx | Workbook: re-coded invoice lines, revenue by service line, recode journals, tax review, contacts, chart fix map, asset ledger, fleet reconciliation, hire billing check, Airtable/Glide/Make design, controls and test log | 707.7 KB |
| Xero-Hire-Billing-Report.pdf | Report (19 pages): data profile, findings, options, target design, results, controls, tests, cutover, RACI, risks, SOP | 753.8 KB |
| Executive-Summary.pdf | One-page executive summary | 139.5 KB |
| engine.py | Engine: categorisation rules, contact matching, asset log clean-up, hire cycle rebuild, Make run simulation and the regression test | 23 KB |
| gen.py | Builds the input data set: Xero exports, the transcribed asset log, fleet register and stocktake | 26 KB |
| Xero-Hire-Billing-FY26-data-and-code.zip | Inputs as exported (CSV), engine output, and the scripts that rebuild the workbook and the report | 214.4 KB |
Sample build: realistic data, built to show how I'd handle this work. Names are placeholders and no client data is used.
Have a similar problem in your business?
Hire me on Upwork (opens in a new tab)