Lesson 8 of 13 · 28 min
Bank and payment reconciliation
By the end of this lesson you can reconcile a bank statement to the general ledger with SQL: match on a reference, list outstanding payments, timing differences, unrecorded bank items and duplicates, and prove that the reconciliation closes.
A bank reconciliation explains why the balance on the bank statement differs from the balance of the cash account in the books (GL 1110 Bank – Operating Account). The two are never equal at month end, and that is normal; what matters is that every difference is identified and explained.
In ledger the two sides share a matching key:
- every cash movement on 1110 has
journal_entries.reference= the bank reference; - vendor payments carry the same value in
vendor_payments.bank_reference; - statement lines (
bank_statement_lines) carry it inbank_reference.
Amounts are signed from the company's point of view on the statement: outgoing payments are negative, deposits positive. On the ledger side, debit − credit on 1110 gives the same sign (a receipt debits cash).
This lesson, its 5 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons