Icon

Bank Reconciliation

<p><strong>Bank Reconciliation</strong></p><p>Account reconciliation is a crucial part of every company's accounting process, and helps to ensure that:</p><ul><li><p>You know where your money is coming from and going to,</p></li><li><p>All your transactions are properly documented, and</p></li><li><p>The internal financial ledgers and statements are in line with the external financial statements</p></li></ul><p>The general idea of reconciliation is to compare two sets of data and check whether the entries in one dataset equal the entries in the other.<br><br>When it comes to account reconciliation, you’re comparing two financial records and want to confirm they are consistent and complete so that only correct values are reported in the general ledger. Discrepancies will uncover mistakes from simple errors through to fraudulent activities.<br><br>This workflow performs one specific type of account reconciliation: bank reconciliation. It’s one of the most popular types of account reconciliation and compares the company’s internally recorded transactions, e.g. in the cash book with transactions recorded externally, e.g. bank statements provided by the bank. </p>

URL: Kaggle dataset https://www.kaggle.com/datasets/fozianazar/bank-reconciliation-statement
URL: KNIME Blog: Bank Reconciliation https://www.knime.com/blog/knime-finance-bank-reconciliation

Bank Reconciliation


This workflow shows a simple bank reconciliation example. It compares the company’s internally recorded transactions, e.g. in the cash book, with transactions recorded externally, e.g. bank statements provided by the bank. The goal is to check whether all transactions appear in both records and are identical. If discrepancies are found, they should be flagged and adjusted.

The data used in this workflow is some sample bank reconciliation data available from Kaggle: https://www.kaggle.com/datasets/fozianazar/bank-reconciliation-statement

Step 1

  • Read the data

  • Bring it into comparable format

Step 2

  • Compare data tables, mark matching transactions, flag missing/different transactions

  • Calculate Unreconciled Amount

Step 3.1

Calculate adjusted bank statement balance. Observe bank statements for:

  • Timing differences

  • Errors caused by bank

Step 3.2

Calculate adjusted cash book balance. Observe cash book entries for:

  • Omissions

  • Errors caused by accountant

Step 4

  • Final check if adjusted closing balances match

Bank Reconciliation Statement
Adjust Bank Statement
Bank Statement
Excel Reader
Adjust Cash Book
Preprocessing
Cash Book
Excel Reader
top: inconsistent cash book entriesbottom: matching cash book entries
Rule-based Row Splitter
Preprocesing
top: inconsistent bank transactionsbottom: matching bank transactions
Rule-based Row Splitter
Bank Statement vs. Cash Book
top: errorsmiddle: omissionsbottom: timing differences
Joiner

Nodes

Extensions

Links