In-house build
A petty cash system that has run for twelve years
Purchasing needed one person to stop being the only one who could file petty cash correctly. The workbook that replaced them is still in daily use twelve years later.
Problem
Petty cash was recorded by hand, differently by each person filing it. Supplier details were retyped from receipts every time, so the same supplier appeared under several spellings and several TINs. Month-end and BIR-ready reporting meant rebuilding the same summaries from scratch, and only the person who had done it before could do it at all.
Context and constraints
Built inside the purchasing department of the commercial print firm where I work, with no budget, no developer, and no software the company was willing to buy. The constraint was absolute: it had to run on the Excel already installed on every desk, and clerks had to be able to use it without training.
What I built
A data-entry form that validates every field before it will save — refusing bad dates, short TINs, missing expense or purchase types, and returning the cursor to the offending field. Typing a supplier's nine-digit TIN auto-fills their name, address, expense type, and purchase type from history, so the supplier master maintains itself. VAT is split from the gross amount at the moment of entry, so the tax-ready view is a property of the data instead of a monthly reconstruction. Reports for batch, month, company, and batch-code are driven by a data model with slicers and regenerate themselves when a filter changes. A one-click export produces a clean, page-set-up workbook to hand to accounting.
Before / after
Before: handwritten logs, one supplier spelled four ways, and a month-end summary only one person could produce. After: validated single-entry capture, a self-maintaining supplier master, and five report views that rebuild themselves — filed by whoever is at the desk that day.
Result
In continuous production since 2014 and still being edited in 2026 — twelve years, across five annual revisions. The current file holds roughly 9,770 recorded transactions across 35 fields. The clearest evidence is not a percentage: it is that the system outlived every person who was originally trained on it, and that filing petty cash stopped depending on who was in the office. I have since built the same kind of workbook for logistics and accounting, on the same pattern.
What I would improve next
The honest weaknesses are structural, and I would fix them in this order: the data lives inside the application file, so I would separate the records from the tool; versions are tracked by filename rather than by any real version control; and several field positions are hard-coded, which means inserting a column silently writes into the wrong place. None of these have bitten it yet — which is luck, not design.
Tools used
- Excel
- VBA
- Power Pivot data model
- PivotTables and slicers
- Dynamic named ranges
- SoundEx phonetic matching
Artifacts
The interesting decision in this build was the supplier search.
Clerks could not reliably spell supplier names, so exact-match lookup failed constantly and the same company kept getting entered as a new one. Rather than train people to spell, I implemented phonetic matching — the search compares how a name sounds, so a misspelling still finds the existing supplier record. That single change is what stopped the supplier list from fragmenting, and it is why the TIN-based auto-fill has clean history to draw on.
That is the whole approach in miniature: the problem looked like a data-quality problem, but it was a typing problem, and it got solved where the typing happened.