CASE FILE 01/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

Odoo bank reconciliation for a quarter: four accounts, every line matched or explained

1,636 bank lines across four accounts reconciled to Odoo for a quarter, with 0.00 left unexplained on every statement

Sample build · realistic data, built to show how I'd handle this work · not client work · every figure is computed from the files in the appendix

1,636bank lines across 4 accounts
99.7%matched by a rule
0.00unexplained on all 12 statements
28/28controls pass

01Summary

In this scenario, 1,636 statement lines across four bank accounts are reconciled against 4,327 Odoo journal items for July to September.
1,618 of 1,623 lines (99.7%) match by a rule, and the unexplained difference is 0.00 on all 12 account-month statements.
204 exceptions each get a reason code, an owner and a proposed journal; 28 of 28 formula controls pass.

1,618 of 1,623 lines (99.7%) match by a rule, and the unexplained difference is 0.00 on all 12 account-month statements.
Key point, computed from the sample files

02The scenario

In this scenario an allied health group with four clinics banks with three AUD accounts and one USD account. Plan managers pay up to 12 invoices in one transfer, Stripe and the card terminals settle net of fees, some invoices are short-paid, one day's statement is imported twice, and cheques and deposits straddle month end. Matching that by eye in Odoo would take days, and errors would roll into the next month.

Exhibit 1: Current-state process map: 6 manual steps across 3 roles

Source: sample files

03Issues in the data

Exhibit 2: Issues in the scenario, counted from the sample data

426bank lines that each pay several Odoo items (3,555 in total)

200payee spellings on the statement for 66 partners with more than one

13statement lines imported twice from one day's file

111bank items with nothing in Odoo: fees, interest, receipts never registered

Source: sample files

04How I approached it

A matching engine (match.py) with 14 rules in priority order: duplicates, Stripe payouts, remittances, batches, pay runs, cheques, terminals, references, fuzzy payee names and amounts.
A workbook with 12 standard bank reconciliation statements (account by month) and a dashboard with sign-off, all formulas.
An exceptions register of 204 items with reason code, owner and action, and 189 proposed journals (450 lines) linked to it.
A 28-control tab and a test log, plus a 19-page report and a one-page executive summary.

Exhibit 3: Target design for the scenario

Source: my design and the sample files

05How this applies to your business

Week 1: exports and opening positionRead-only Odoo access, the four statement exports, and an agreed 30 June reconciliation.
Week 2: catch-up and rulesRun the engine on the quarter, clear routine items, set up fee models in Odoo.
Week 3: decisions and lockOwners answer decisions and escalations, journals are posted and the period is locked.
Week 4: first live monthYour team runs the month from the SOP while I watch, then the handover.

Exhibit 4: Responsibilities (R responsible, A accountable, C consulted, I informed)

Activity	Business owner	Specialist (me)	Your team
Export statements and Odoo data	I	C	R
Run the engine and rebuild the workbook	I	R	C
Decide short payments and double entries	A	C	R
Answer escalated items	R	C	I
Post journals and lock the period	A	C	R
Change a matching rule	I	R	C

Source: sample plan

06Controls and checks

Exhibit 5: Control framework: 28 checks, status read from the files

Check	What it catches	Status
Statement lines roll forward to the bank's closing balance (12 account-months)	Proves the import is complete and the duplicates are excluded	PASS
Odoo journal items roll forward to the trial balance (12 account-months)	Proves the Odoo export is complete	PASS
Unexplained difference on all 12 reconciliation statements	Adjusted bank equals adjusted Odoo to the cent	PASS
Unclassified items on the statement side, all 12 statements	Every reconciling item has a reason code	PASS
Bank lines without a match group	Every line is accounted for	PASS
Odoo items without a match group	Every Odoo item is accounted for	PASS
Bank lines in groups = bank lines imported	No line counted twice or dropped	PASS
Odoo items in groups = Odoo items exported	No item counted twice or dropped	PASS
Groups with a repeated group number	Group numbers are unique	PASS
Clean matches (reason MATCH) that do not tie to the cent	A match only counts if it ties exactly	PASS
Matched groups that straddle a month end on one side only	Month-end positions are complete on both sides	PASS
Duplicate import lines detected (R01) and excluded from balances	The 14 Aug file was imported twice	PASS
Stripe report: gross - fee = net on every line	Payout report is internally consistent	PASS
Stripe payouts: report net vs bank amount	Every payout ties to the cent	PASS
Stripe group differences = fees + disputes per report	Fees are booked exactly once	PASS
Proposed journals: total debits - total credits	Journals balance	PASS
Proposed journals out of balance	Every journal balances on its own	PASS
Journal bank lines vs exceptions needing a journal, by account	Journals post exactly what the register says	PASS
Exceptions vs 30 Sep Odoo-side reconciling items, by account	The register explains every reconciling item at quarter end	PASS
After proposed journals: Odoo + journals = statement + in transit, 30 Sep	Posting the journals closes the reconciliation	PASS
Allocations exceeding an open item's residual	No invoice or bill is over-allocated	PASS
Allocations vs REG / REGBILL exception amounts	Allocations post the full amount	PASS
Transfers: operating to payroll vs payroll from operating (bank)	Inter-account transfers net to nil	PASS
Sweeps: merchant to operating vs operating from merchant (bank)	Sweeps net to nil	PASS
Dashboard unexplained differences (all rows)	Sign-off view agrees with the statements	PASS
Dashboard statement lines = counted bank lines	Dashboard covers every line	PASS
Engine regression test: items that disagree with the expected outcome	match.py is checked on every run	PASS
Workbook groups = engine groups	The workbook reflects this engine run	PASS

Source: sample files (Checks / Controls tab, test output or run log)

07What the files show

Exhibit 6: What the sample files show

1,636bank lines across 4 accounts
99.7%matched by a rule
0.00unexplained on all 12 statements
28/28controls pass

Source: sample files, measured by script. Sample data, not a client result.

Statement lines roll forward to the bank's closing balance, and Odoo items to the trial balance, for all 12 account-months.
Every one of the 1,636 bank lines and 4,327 Odoo items sits in exactly one of 1,322 match groups.
After the proposed journals, Odoo plus items in transit equals the bank at 30 September on every account.
The engine's regression test agrees on 5,963 of 5,963 items.

08Risks and how the build handles them

Exhibit 7: Risks and mitigations

Risk	Mitigation built in
A payment is matched to the wrong invoice	Exact amount plus remittance, reference or payee score of 0.85 or more; a reference that points to another customer is escalated
A duplicate import inflates the bank balance	Duplicates are removed first, and a roll-forward control fails if any slip through
Short payments are booked as paid in full	Remittance lines are compared to the invoice and the residual is reopened
An unknown receipt is allocated to tidy the rec	Unexplained items go to an escalation with an owner; nothing is allocated by amount alone
A rule change breaks an earlier case	A regression test compares all 5,963 items with the expected outcome on every run

Source: sample design

09Appendix: files

A. Screenshots of the deliverable

Exhibit 8: Dashboard tab: statement, in-transit, unpresented, Odoo balance and unexplained difference per account and month, with sign-off

Source: sample files

Exhibit 9: Rec Statements tab: a standard bank reconciliation statement for the operating account

Source: sample files

Exhibit 10: Exceptions register: reason code, type, owner, proposed action and journal for each item

Source: sample files

Exhibit 11: Controls tab: 28 formula controls, all PASS

Source: sample files

Exhibit 12: Report page: target month-end process across bank, bookkeeper, matching engine, Odoo and reviewer

Source: sample files

Exhibit 13: Dashboard of the Odoo bank reconciliation sample: 12 account-month statements, each with 0.00 unexplained difference

Source: sample files

B. Downloadable files

File	What it is	Size
Odoo-Bank-Reconciliation-Q1-FY27.xlsx	Workbook: dashboard and sign-off, 12 bank rec statements, exceptions register, proposed journals, match groups, rules, controls and test log	1015.6 KB
Odoo-Reconciliation-Report.pdf	Report (19 pages): current state and data profiling, options, target design, results, controls, cutover, RACI, risks, SOP	786.4 KB
Executive-Summary.pdf	One-page executive summary	146.7 KB
match.py	Matching engine: 14 rules, fuzzy payee matching, reason codes and the regression test	21.9 KB
gen.py	Builds the input data set: bank statements, Odoo exports, remittances, Stripe report	28.5 KB
Odoo-Rec-Q1-FY27-data-and-code.zip	Inputs as exported (CSV), engine output, and the scripts that rebuild the workbook and the report	232.1 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)