Automatic reconciliation across banks and currencies, with around 80% of receipts matched automatically

The company is an international trader serving customers in both Europe and Asia. It uses several bank accounts to receive and make payments in different currencies, including euros, US dollars and renminbi.

Its ERP already handled sales invoices, purchase bills and the general ledger, but bank statements still had to be exported from various online banking platforms by finance staff, then tidied up and reconciled by hand.

Each bank used different file formats, transaction descriptions and value timing, so finance staff spent a great deal of time working out where payments had come from.

The challenge

Bank transaction formats were inconsistent

Banks supplied statement files as CSV, Excel or other standard formats.

Dates, amount signs, currencies and transaction descriptions were all represented differently.

One receipt could cover several invoices

Combined payments, partial payments, deducted bank charges and cross-currency transactions meant that simple amount matching could not cover every case.

Cash information lagged behind

Management could see receivables and payables, but found it hard to get a timely picture of the real pressure on future cash flow.

SHEPHARD's digital solution

Step 1: Establish a standard bank transaction model

Transactions from every bank are mapped to a single data format.

Standard fields include account, transaction date, booking date, amount, currency, payer, transaction reference and the bank's unique identifier.

Where data has no stable unique identifier, a composite key and manual review prevent duplicate imports.

Step 2: Build a reliable statement import process

Records are kept in the original file order and with the bank's transaction identifiers.

Date formats, decimal separators, encodings and debit/credit direction are all normalised.

Duplicate records, file parsing failures and unusual transactions generate clear alerts, and an import audit log is retained.

Step 3: Introduce automatic matching suggestions

Candidate matches are generated from transaction references, customer details, amounts and open invoice balances.

Dedicated logic handles one receipt covering several invoices and several receipts covering one invoice.

Transactions that cannot be matched reliably are left for manual confirmation rather than being forced through automatic posting.

Step 4: Strengthen multi-currency handling

Transaction currency, original invoice currency, company functional currency and the applicable exchange rate are each recorded separately.

Exchange differences, bank charges and the related journal entries are handled in line with the company's accounting policies and its ERP finance configuration.

Step 5: Build a treasury view

Bank balances, open receivables, payables due and confirmed payment schedules are brought together.

Management can analyse the cash position by company, account, currency and time period, and produce short-term cash flow forecasts.

Implementation approach

We started with a single bank account to test data import and reconciliation.

Duplicate detection, partial payments, combined payments and bank charge handling were validated against historical transactions.

Only after sign-off from finance was the solution extended to other banks and currencies.

Business value

Finance staff spend far less time repeatedly reformatting bank files and searching for transactions by hand.

The company has more timely information on receivables, payables and cash, and treasury management no longer depends entirely on a concentrated reconciliation at month end.

Results (approximate): Automatic payment matching approx. 80%; working days to close the month approx. 6→3; automatic statement import approx. 95%.

Metrics tracked on an ongoing basis: Automatic statement import rate, automatic payment matching rate, number of unreconciled items, working days to close the month, cash forecast variance.

What this case teaches us

The most important part of finance automation is not having the system generate journal entries automatically, but ensuring that every accounting entry has a reliable source, clear rules and an audit trail that can be reviewed.

*To protect client confidentiality, company details in this case study have been anonymised; screenshots are illustrative and results are rounded approximations.*