A bank reconciliation that explains each difference

A working sample of the reconciliation method from my years as Operations & Finance Lead. Excel formulas match 119 bank lines to 121 ledger entries, explain each difference and reconcile to 0.00.

Role
Operations & Finance Lead
Period
Mar 2013 – Mar 2020 · sample built in Nov 2026
Stack
Excel, Reconciliation, Letters of credit
Evidence
Sample data
Question
Does the ledger agree with the bank? If not, which rows cause the difference, and what explains each one?
Data
One synthetic month (October 2026): 119 bank lines, 121 ledger entries and 12 sample letters of credit. A Python script wrote the data with a fixed seed.
Method
Excel formulas match each row to the other sheet by reference and give it one status. SUMIFS adds the exceptions by type into a standard reconciliation statement. An answer-key sheet checks each planted difference.
ResultSample data
The adjusted bank balance and the adjusted ledger balance are both 479,288.73, so the difference is 0.00. The formulas caught 15 of 15 planted differences.

Synthetic data, built in November 2026. Each name, amount and reference is invented. The workbook shows the method. The records of the office are confidential and are not in it.

119Bank lines
121Ledger entries
15 of 15Planted differences caught
0.00Difference after adjustments

The flow

A bank statement and a ledger record the same money. Their balances are seldom the same. This workbook finds each difference and explains it. Then the two balances agree.

From two records to one balance

Sample data

  1. Two records. 119 bank lines and 121 ledger entries for one month.
  2. Match by reference. A formula finds each reference on the other sheet.
  3. One status per row. Matched, Amount differs, Missing on other side or Duplicate.
  4. Statement. Timing items adjust the bank balance. Errors adjust the ledger balance.
  5. Difference 0.00. Both adjusted balances are 479,288.73.

If not zero, read the rows that are not Matched.

Three checks on the sample

  • Answer key. The data has 15 planted differences. The formulas found all 15.
  • Second calculation. A Python script computed each figure apart from Excel.
  • Excel recalculation. 17 Nov 2026. Each figure agreed to the cent.

Sample data, October 2026. The workbook uses Excel formulas only (COUNTIFS, INDEX, MATCH, SUMIFS). It has no macros.

The real process behind the sample

The sample above uses invented data. This is the import cycle that I ran, step by step, with my part in each step.

The letter-of-credit import cycle

Real data

  1. Proforma invoice. The supplier in China sends the price and terms. I received and checked it.
  2. LC application. The office’s trading company applies to the issuing bank. I made the application.
  3. Shipment. The supplier ships the goods to the site in Dubai. I followed the shipment.
  4. Documents. Bill of lading, invoice, packing list and certificate of origin. I collected the set.
  5. Document check. Each document against the LC terms. I compared each field.
  6. Payment. The bank pays the supplier. The records are reconciled. I reconciled them with the accountant.

Parties

  • Supplier. China.
  • The office’s trading company. Applicant · Vancouver.
  • Issuing bank. Opens the LC and pays.
  • Construction business. End user · Dubai.

Example: On a fastener order, the sizes and counts on the invoice and packing list did not agree with the LC. I found this in step 5, before the bank saw the documents. I asked the supplier for a corrected invoice and packing list. The bank paid, with no discrepancy and no refusal.

The import cycle that I ran for the office’s trading company. My part is at the foot of each step. Step 5 is the key check. This is the real process; the reconciliation on the rest of this page uses sample data.