Multi-Store Cash-Up Reconciliation

Business context

Each store in a retail network records its daily cash-up: what the till system expected and what was actually counted and banked. Head-office finance needs those records reconciled store by store, day by day, and needs to know quickly where the numbers do not agree.

Challenge

At more than 120 stores, reconciliation becomes a volume problem. Checking each store manually is slow, and differences are easy to miss when they are spread across hundreds of rows. Finance also needed to see recent history — not only the current month — to separate one-off differences from recurring ones.

My contribution

• Designed the reporting model and reconciliation logic for store-level cash-up data.
• Built the views for reconciliation status, exceptions, totals and trends.
• Defined the rolling reporting window: current month plus the three preceding months.
• Added data-quality indicators so missing or late submissions were visible rather than silently treated as zero.

Investigation and methodology

The work started with the questions finance asked most often: which stores have not reconciled, how large are the differences, are they new or recurring, and is the data complete? Each became a measure or view. Treating missing data as a distinct status — rather than as a zero variance — was an important design decision, because a store that has not submitted is a different problem from a store that is out of balance.

Solution design and implementation

The solution is organised around exceptions. Summary totals sit at the top; below them, stores are classified by reconciliation status, with filters for store, region and period. The four-month window keeps the model focused on what finance needs to act on while still showing short-term trends.

Results and business value

Finance teams gained one consistent view of cash-up reconciliation across a network of 120+ stores, covering the current month and the three preceding months. Exceptions are visible without scanning raw records, and incomplete data is flagged instead of hidden.

Technical details

• Status logic: each store-day classified as reconciled, within tolerance, variance or missing submission.
• Rolling window: date filtering anchored on the current month and the three preceding months, so the report moves forward automatically.
• Data quality: completeness by store and period surfaced as its own indicator alongside financial variance.
• Performance: the four-month window limits the model to the data needed for current reconciliation work.

Lessons, constraints and improvement opportunities

• Designing for exceptions first makes a large network manageable.
• “No data” must never look like “no problem”.
• Next steps could include recurring-variance flags per store and agreed tolerance thresholds owned by finance.