Work sample 14 / 17Clean-up & reconciliation
Quarter-end bank reconciliation across four accounts, with a month-end finance dashboard
How I bring several bank accounts back into line with the ledger for a quarter, and leave a dashboard that shows each month's position.
- Area
- Clean-up & reconciliation
- Tools
- Stripe · Excel
- Typical scope
- US$1,500–3,000
- Timeline
- About 3 weeks · 4 fixed milestones
Work sample: realistic data, built to show how I approach this kind of project. Not a past client job.
01The problem
When reconciliation falls behind, a quarter of bank lines piles up across operating, payroll, merchant and foreign-currency accounts. Stripe payouts net off fees, customers pay several invoices at once, and some lines were entered twice.
The aim is every bank line matched or explained, every month, with a clear list of what needs a decision from the owner or accountant.
Owners usually notice it as
- Bank feeds months behind
- Stripe payouts that never match an invoice
- Remittances covering many invoices
- Balances that differ from the statements with no explanation
02Scope
A fixed scope agreed in writing before anything is built. The table shows what a project like this usually covers and what it leaves out.
| Area | Included |
|---|---|
| Bank lines | Every line across all accounts for the quarter |
| Matching rules | Rules for references, remittances, Stripe payouts and partner name variants |
| Exceptions | Routine items, decisions and escalations listed with a reason |
| Journals | Proposed journals for fees, interest and corrections |
| Dashboard | Statement, in-transit, unpresented and ledger balance per account and month, with sign-off |
| Not included | BAS lodgement and payroll processing |
Typical volumes for a business of this size. The sample files are sized as described in section 05.
| Milestone | Deliverable | Timing | Share | Weeks 1–3 |
|---|---|---|---|---|
| M1 Exports and rules | Bank, ledger and Stripe exports, matching rules agreed | Week 1 | 25% | |
| M2 Match and explain | Matches, exception list, proposed journals | Week 2 | 35% | |
| M3 Dashboard | Month-end reconciliation dashboard with sign-off | Week 3 | 25% | |
| M4 Review and handover | Accountant review, SOP, walkthrough | Week 3 | 15% |
Typical scope for this kind of project: US$1,500–3,000
About 3 weeks across 4 fixed milestones. The fixed quote follows a US$300 Systems Health Check, which confirms the volumes and the scope first. Your software subscriptions are billed to you by the vendors.
03What I fixed, and why
Construction drawing. Each stage has its own check before the next one starts.
- 01
Profile before matching
How many lines carry a reference, how many remittances cover several invoices, how partner names vary.
- 02
Match by rule
Rules are tried in order, strictest first, and every match records which rule made it.
- 03
Explain the rest
Anything left is an exception with a reason and an owner: routine, decision or escalation.
- 04
Prove each statement
Adjusted bank must equal adjusted ledger on every statement, every month.
- 05
Report month by month
The dashboard shows each account's position and what is still open.
04Checks
Nothing goes live on trust. These checks run before sign-off and stay in the files, so they can be re-run after every change.
- Unexplained difference is zero on every statement
- Every bank line counted once
- No ledger item matched twice
- Stripe payouts equal gross minus fees
- Proposed journals balance
| 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 |
| Ledger journal items roll forward to the trial balance (12 account-months) | Proves the ledger export is complete | PASS |
| Unexplained difference on all 12 reconciliation statements | Adjusted bank equals adjusted ledger 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 |
| Ledger items without a match group | Every ledger item is accounted for | PASS |
| Bank lines in groups = bank lines imported | No line counted twice or dropped | PASS |
| Ledger items in groups = ledger 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 |
Show all 28 checks
| Check | What it catches | Status |
|---|---|---|
| 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 ledger-side reconciling items, by account | The register explains every reconciling item at quarter end | PASS |
| After proposed journals: ledger + 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 | The matching rules are checked on every run | PASS |
| Workbook groups = engine groups | The workbook reflects this engine run | PASS |
Status read from the sample workbook. Each check is a formula, not a typed value.
05Handover
Everything is handed over in editable files you own, with the steps written down so your team is not dependent on me.
In this work sample
1,636 bank lines across 4 accounts, 4,327 ledger items and 1,930 Stripe lines for a quarter, in a workbook with 48,995 formulas and 28 controls.
- Reconciliation workbook with the dashboard
- Matching rules
- Exception list with owners
- Proposed journals for the accountant
- Month-end SOP
06Files in this work sample
Screens from the sample files. Select one to open it full size.
Bank-Reconciliation-Q1-FY27.xlsx › Dashboard
Bank-Reconciliation-Q1-FY27.xlsx › Rec statement
Bank-Reconciliation-Q1-FY27.xlsx › Exceptions
Bank-Reconciliation-Q1-FY27.xlsx › Controls
Bank-Reconciliation-Report.pdf · page 7
PREVIEW.png · one-page overview
| File | What it is | Size |
|---|---|---|
| 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.4 KB |
| Bank-Reconciliation-Report.pdf | Report (19 pages): current state and data profiling, options, target design, results, controls, cutover, RACI, risks, SOP | 785.6 KB |
| Executive-Summary.pdf | One-page executive summary | 145.2 KB |
Tell me what’s bugging you and I’ll tell you where I’d look first.
Send a short note on Upwork about what is slowing the business down. I’ll reply with where I’d start, and whether the US$300 Systems Health Check is the right first step.