Bank reconciliation for the year-end close

Tahlia Trading Company is a fictional Saudi distributor of household goods, food and electronics with one operating bank account at Rawabi Bank, recorded in the general ledger as account 1110. Every supplier payment, customer receipt, payroll transfer, GOSI and VAT payment passes through it, about 1,600 statement lines over 2024 and 2025.

The external auditors arrive in January for the FY2025 audit and will ask for a bank reconciliation at 31 December 2025: the bank balance on one side, the book balance on the other, and every difference listed, classified and explained. Last year the reconciliation was done by hand in a spreadsheet and took a week; it also missed a transfer that the bank had posted twice.

This year the finance controller wants it done in SQL so it can be rerun every month. The matching key is the bank reference: it appears on the statement line, on the vendor payment and on the journal entry that moved cash. But the data has the usual real-world complications. Cheques clear weeks after they are written, foreign SWIFT payments arrive on the statement with a SAR 75 charge deducted, monthly bank fees carry no reference at all and the last quarter's fees were never booked, some customer receipts were booked in the month after the bank credited them, two deposits on the last days of December are not in the ledger yet, and one outgoing transfer appears twice on the statement.