Case Study · AI Automation
AI-Powered Cash Reconciliation Automation Case Study
Replacing a spreadsheet-driven cash reconciliation process with a real-time, audit-ready system
How a multi-location sports & fitness franchise moved weekly cash reconciliation across its entire center network from manual spreadsheets to an automated, Snowflake-backed workflow: catching discrepancies in near real time instead of weeks later.
The result: a single source of truth for weekly cash reconciliation across the entire center network, with discrepancies caught in near real time instead of weeks later, every edit and correction fully attributed, and a Finance team reviewing genuine exceptions instead of every row.
The Customer
A multi-location youth sports and fitness franchise
A multi-location youth sports and fitness franchise operating several dozen training centers across the United States.
Each center runs its own daily operations (program fees, retail sales, and point-of-sale transactions), all settled in cash and cheque alongside electronic payments. Every week, each center's General Manager (GM) is responsible for counting, recording, and depositing that week's physical cash, and Finance at the corporate level has to confirm that what was deposited actually matches what the automated sales systems say should have been collected.
With centers spread across multiple states and time zones, and no single system of record, this reconciliation had become one of Finance's most time-consuming recurring processes.
At a glance
- Customer
- Multi-location youth sports and fitness franchise
- Network
- Several dozen training centers across the United States
- Process
- Weekly cash reconciliation, every center
- Data platform
- Snowflake data warehouse
- Solution
- Purpose-built web application replacing spreadsheets and email
The Problem
Weekly cash reconciliation ran entirely on spreadsheets and email
Before this engagement, weekly cash reconciliation ran entirely on spreadsheets and email. Each center's GM manually logged a week's worth of daily cash figures into a shared spreadsheet template, then emailed it to corporate Finance for review. Finance, in turn, had to manually cross-check each center's submitted numbers against separate exports from the point-of-sale system and the bank's deposit records: one center, one spreadsheet, one email at a time.
There was no single place to see the status of all centers at once, no automatic flag when a center's numbers didn't match its sales data, and no record of who had actually entered or changed a given figure. A discrepancy that happened on a Tuesday might not surface until Finance got around to that center's spreadsheet, sometimes weeks later, by which point the “why” behind it was long forgotten.
Challenges Faced Building the AI Reconciliation Automation
No centralized visibility.
Each center's numbers lived in its own spreadsheet. Finance had no single view of which centers were up to date, which were behind, and which had unexplained variances; that picture only existed in someone's head after enough manual cross-referencing.
Manual reconciliation was slow and error-prone.
Comparing a week's hand-typed cash figures against separate point-of-sale and bank exports, center by center, left plenty of room for a transposed digit or a missed row to go unnoticed until well after the fact.
No audit trail.
Once a number was typed into a spreadsheet cell, there was no record of who entered it, when, or whether (and why) it had since been changed. Any correction Finance needed to make was effectively untraceable after the fact.
Centers span multiple time zones.
A single company-wide “end of business day” cutoff doesn't work when centers in different regions close their doors, and their books, at different local times.
More than one person touches a week's numbers.
A center's primary GM, a secondary GM, and occasionally a regional manager covering as a delegate might all enter or adjust the same week's figures. The old process had no way to prevent one person's edits from silently overwriting another's, or to show who had actually entered what.
The physical handoff itself needed a paper trail.
Two different people are typically involved in a single day's cash handling: whoever counts and verifies the starting till each morning, and whoever closes it out and hands off to the bank-deposit run later that day. Capturing that handoff required more than a single “entered by” field, without turning a simple daily task into a multi-step form.
The AI Automation Solution
A purpose-built, Snowflake-backed reconciliation app
Decision Foundry designed and built a purpose-built web application, backed by the client's Snowflake data warehouse, that replaces the spreadsheet-and-email workflow end to end.
A two-stage daily entry workflow.
Each day's cash entry splits cleanly into an opening stage and a closing stage: a Morning Manager-on-Duty counts and verifies the starting cash and saves their half independently; later, a Closing Manager-on-Duty enters the day's closing figures and cash-handling details. Neither has to wait on the other, and a week's numbers build up incrementally rather than needing one person to do it all in one sitting.
Automated reconciliation against source systems.
Every day's entry is checked against the center's point-of-sale feed and bank deposit records automatically, with “over” or “short” variances flagged the moment they cross a configurable threshold, not discovered during a month-end review.
A single Finance review queue.
Every center's week shows up in one place (matched, awaiting the GM's submission, or flagged for correction), with one-click approve/reject and a complete audit trail of every edit, unlock, and correction, fully attributed to who made it and when.
Proactive email alerts.
A center that misses a day's entry, a week whose deposit hasn't been logged by its due date, or an opening cash count that doesn't match the center's configured petty cash now triggers an automatic email to the right person: no one has to remember to go looking for it.
Time-zone-aware scheduling.
Every deadline and “missed entry” check respects each center's own local business hours, instead of a single company-wide clock that's wrong for half the network on any given day.
See the AI Automation in Action
1. Weekly cash entry, in progress.
Each day shows its own status, with the opening balance (Morning MOD) and closing figures (Closing MOD) tracked independently. Wednesday is already flagged $12 short against the automated sales feed, visible to Finance immediately, not at month-end.
2. One queue, every center.
Finance's review queue shows every center's status for the week at a glance (matched, awaiting the GM, needing review, or rejected) instead of opening a separate spreadsheet per center.
3. Petty cash, kept current.
A lightweight periodic review lets Finance confirm or adjust each center's configured starting cash in one pass, instead of it silently drifting out of date.
4. Who's behind, at a glance.
A daily rollup sorts every center by risk tier (at risk, lagging, or on track), so a regional manager can see exactly who needs a nudge without checking each center one by one.
5. Alerts that find people, not the other way around.
When a day's opening count doesn't match the center's configured petty cash, the GM is emailed automatically the same day, not discovered weeks later during a manual review.
The Outcome of the AI Reconciliation Automation
The result is a single source of truth for weekly cash reconciliation across the entire center network: discrepancies caught in near real time instead of weeks later, every edit and correction fully attributed, and a Finance team that spends its time reviewing genuine exceptions instead of manually checking every row of every spreadsheet.
Get In Touch