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.
- 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.
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
- Two records. 119 bank lines and 121 ledger entries for one month.
- Match by reference. A formula finds each reference on the other sheet.
- One status per row. Matched, Amount differs, Missing on other side or Duplicate.
- Statement. Timing items adjust the bank balance. Errors adjust the ledger balance.
- 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
- Proforma invoice. The supplier in China sends the price and terms. I received and checked it.
- LC application. The office’s trading company applies to the issuing bank. I made the application.
- Shipment. The supplier ships the goods to the site in Dubai. I followed the shipment.
- Documents. Bill of lading, invoice, packing list and certificate of origin. I collected the set.
- Document check. Each document against the LC terms. I compared each field.
- 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.
More work
- Stay for the exhale
I turned a yoga teacher's classes into a membership product. I did the strategy, the design, the build and the operations.
- SQL case studies: revenue share, donor segments and a 3NF schema
Three SQL studies on small course datasets. Each one starts with a business question. Every figure is query output that you can run again.
- Monthly owner report for a three-property portfolio
A rebuilt sample of the monthly report that each asset owner received: occupancy, rent roll, NOI against budget, capital projects and the NOI share of each owner.