Designing a Financial Reconciliation System: Objectives, Methods, Scenarios, and Process Steps

Before designing a reconciliation system, we must clarify its purpose, methods, scenarios, and target users, and discuss expectations with the finance team, who are the primary users.

Many newcomers to finance may be unfamiliar with reconciliation.

Why reconcile? Enterprises settle most transactions through banks, but timing differences can cause mismatched balances, known as “unreconciled items.” Accurate bank balances and usable cash amounts require regular bank statement matching and the preparation of bank reconciliation statements.

What are the reconciliation methods?

Document verification: matching ledger entries with supporting documents such as receipts, invoices, or bank transfer records.

Ledger‑to‑ledger verification: comparing related ledgers, e.g., total expense ledgers vs. coupon, procurement, or merchant settlement ledgers, and detailed account ledgers vs. aggregate totals.

Record‑to‑physical verification: comparing recorded asset quantities with actual physical counts.

Typical reconciliation scenarios involve banks, Alipay, WeChat Pay, and other third‑party platforms, each with its own characteristics.

The system’s users fall into three groups: decision makers, business staff, and finance personnel, each with different concerns; gathering their requirements separately helps avoid a “one‑size‑fits‑all” solution.

The reconciliation process can be divided into five steps, which this article details.

1. Data Preparation

Data preparation means gathering the necessary information, such as bank statements. File formats differ: Alipay provides CSV, WeChat Pay provides TXT, and other channels may use XML or XLS.

Note: Different reconciliation methods require different data; for document verification, supporting vouchers (e.g., expense receipts) are also needed.

Multiple accounts per channel may generate multiple reconciliation files daily; the system must handle them.

Files can be downloaded repeatedly; once generated they are immutable, but occasional transmission errors may produce incomplete data, requiring manual re‑download or manual import.

Each third‑party channel has its own file‑generation schedule, which must be considered in the reconciliation timing.

2. Data Cleaning

Data cleaning extracts only the fields needed for reconciliation (e.g., transaction time, counterpart account, our account, amount, order number, transaction type). Typically, only the previous day's transactions (t+1) are processed, and unnecessary rows are discarded.

Sometimes transaction IDs duplicate across channels and must be split according to business rules to ensure uniqueness.

3. Data Verification

Data verification is the core of reconciliation, handling both detailed and summary (total) accounts.

Key checks include:

Missing transactions: ensure the opening balance equals the previous day’s closing balance.

Closing balance = opening balance + inflows – outflows; verify the formula.

Match transactions with vouchers based on amount, parties, and time.

Four typical discrepancy types may appear (see diagram).

Finally, display matched and unmatched results for downstream reporting.

4. Discrepancy Handling

Discrepancies are handled either manually or automatically. They are classified as “optimizable” (must be resolved) or “reasonable” (can be ignored if the cause is known and no asset loss occurs).

Automatic handling often involves auto‑generation of vouchers to offset differences; manual handling includes filling missing vouchers or adjusting for fees and exchange‑rate differences.

Complex cases, such as “messy accounts,” can be time‑consuming to resolve.

For groups with multiple subsidiaries and distinct payment accounts, consolidating receipts into a single corporate account simplifies internal accounting and reduces errors.

5. Result Statistics

Result reporting is essential for both finance and management. Finance needs an overview of accounts, reconciliation duration, success status, aggregated discrepancies, and financial statements (balance sheet, profit & loss).

Management cares about reconciliation time, discrepancy count, and handling efficiency.

These metrics can be visualized with custom charts.

End of the sharing; for real‑world design cases, feel free to leave a comment.

A lively, knowledgeable 90s‑born product specialist is waiting to chat with you!