Bank Reconciliation

The Problem

For mid-market finance teams running on NetSuite, the monthly bank reconciliation is typically a three-to-five day exercise performed by a controller who manually exports a bank statement, pulls the AP payment schedule from a shared spreadsheet, and cross-references both against bill payment records in the ERP. Each of those three data sources uses a different date format, reference numbering convention, and amount field, so aligning them requires VLOOKUP chains, manual sort-and-filter passes, and colour-coded conditional formatting that breaks the moment a colleague edits the file.

The process is mandatory, not optional. Under US GAAP, bank reconciliation is a fundamental internal control required by the COSO framework and tested annually under SOC 1 / SSAE 18 engagements. For Indian entities, the ICAI Guidance Note on Audit of Cash and Bank Balances and SA 505 (External Confirmations) require auditors to independently verify bank balances; a reconciliation that cannot be traced back to source documents forces the auditor to expand confirmation procedures and can result in a qualified opinion. Both frameworks require the reconciliation to be completed and signed off before the period is locked.

At scale — when a company runs 200 or more vendor payments per month across multiple bank accounts — the manual approach introduces systematic risk. Controllers begin cutting corners: they match on amount alone when references differ, they skip investigation of small variances, and they defer unreconciled items to the following month rather than resolving them. A single unposted bill payment in NetSuite can overstate the cash balance on the balance sheet, misstate AP ageing, and trigger an IRS or GST audit query if the discrepancy crosses materiality thresholds.

Cadel's Bank Reconciliation workflow replaces that spreadsheet process with a deterministic three-way match across the bank statement, the AP payment schedule, and NetSuite bill records, producing a structured exception report that a controller can act on and file as an audit workpaper — without writing a single formula.

Why It Matters: Context

A proper bank reconciliation is not simply matching debits to credits. It is a three-way control: (1) the bank statement, which reflects the cash position as seen by the financial institution; (2) the AP payment schedule, which records what the company intended to disburse and to whom; and (3) the ERP bill payment record in NetSuite, which represents what was posted to the general ledger. All three must agree in reference, date, and amount before a transaction can be considered reconciled. Divergence between any two of the three sources constitutes an exception that must be classified, investigated, and resolved before the period can close.

For US mid-market companies, this control is evaluated under PCAOB AS 2201 (for accelerated filers) and is a standard walkthrough item in any SOC 1 Type II audit. For Indian mid-market entities, Section 128 of the Companies Act 2013 requires books of account to give a true and fair view of the state of affairs of the company; a bank reconciliation gap that persists across periods is a direct indicator that the books do not reconcile to external confirmation. ICAI's SA 240 (The Auditor's Responsibilities Relating to Fraud) specifically identifies unreconciled bank items as a fraud risk indicator requiring escalation. Where bank charges or wire fees are not posted to the GL, the expense is understated and the tax deduction is lost.

The consequences of a poorly maintained bank reconciliation are concrete. An undetected duplicate payment drains cash and inflates AP expense. An unposted wire fee creates a book-to-bank difference that auditors must explain. A cut-off error — where a payment clears the bank on 31 March but is posted to the ERP on 1 April — misstates both the closing cash balance and the AP liability under Ind AS 10 (Events After the Reporting Period) or ASC 855 (Subsequent Events). In audit engagements, recurring unreconciled items are among the most common findings that elevate a control from effective to deficient, or from deficient to material weakness.

What This Workflow Automates

1. Document ingestion: Cadel ingests the bank statement (PDF or CSV), the AP payment schedule (Excel or CSV), and the NetSuite bill payment export (CSV or XLSX) as three discrete document types, normalising dates, reference numbers, and amounts to a common schema before any matching begins.
2. Reference normalisation: Payment reference strings are cleaned — whitespace stripped, leading zeros standardised, cheque numbers and wire reference codes aligned — so that references that look different due to formatting are not incorrectly treated as mismatches.
3. Three-way matching: Each bank statement line is matched to an AP payment schedule entry and a NetSuite bill record on the composite key of payment reference and amount; a transaction is marked Matched only when all three sources agree on both fields.
4. Exception classification: Transactions that do not achieve a three-way match are classified into one of six deterministic exception classes: Missing ERP Entry, Outstanding Payment / Timing Difference, Amount Variance, Duplicate Reference Alert, Cut-Off Exception, or Bank-Originated Charges — No AP Source.
5. Delta surfacing: For Amount Variance exceptions, the workflow displays the three-way delta — the difference between the bank statement amount, the AP schedule amount, and the NetSuite posted amount — so the controller can determine whether the gap is a bank charge, partial payment, or FX rounding before posting an adjustment journal entry.
6. Cut-off detection: Transactions where the bank value date falls in one calendar period and the ERP posting date falls in another are flagged as Cut-Off Exceptions with the one-day gap noted, supporting the controller's determination under ASC 855 or Ind AS 10.
7. Audit workpaper output: The reconciliation result is exported as a structured report listing matched items, each exception class with its source-level detail, and a summary count — ready to be attached to the period-close audit file without reformatting.

All six classification passes execute in under 60 seconds regardless of transaction volume, and every output cell is traceable back to the source document line that produced it, so any controller or auditor can re-derive the result independently.

Edge Cases We Simulate

The workflow ships with a battery of synthetic test scenarios that exercise every failure mode we have seen in real-world data. Each scenario produces a deterministic outcome that an auditor or controller can verify in seconds.

Sample Documents

Download or inspect the seeded sample files used to demonstrate this workflow:

Sample Results

In a representative monthly reconciliation run covering a mid-market company with approximately 180 bank statement lines, the workflow matched 161 transactions across all three sources as fully reconciled. Of the remaining 19 exceptions, 7 were classified as Missing ERP Entry (bank debits with no corresponding NetSuite bill payment), 4 were Outstanding Payments where the ERP record existed but the transaction had not yet cleared the bank, 3 were Amount Variance flags carrying a combined three-way delta of $1,340, 2 were Duplicate Reference Alerts on the same wire reference number appearing twice in the bank statement, 2 were Cut-Off Exceptions straddling the last day of the period, and 1 was a Bank-Originated Charge with no AP source. The full classification pass completed in 38 seconds.

One exception class of particular note was the Duplicate Reference Alert: both entries carried the same beneficiary name, the same amount ($8,500), and references differing only by a trailing check digit that the ERP had silently truncated on import. Without automated reference normalisation and duplicate detection, this pair would have matched as two separate legitimate payments, and the duplicate disbursement would have remained undetected until the vendor's next statement reconciliation — a recoverable but material cash leak that the workflow surfaced deterministically without any manual sort or filter step.

Why Automation Wins Here

Replacing the manual three-way bank reconciliation with Cadel's workflow reduces a process that typically consumes three to five controller-hours per month to under two minutes of active work — document upload, a 60-second automated run, and exception review. More importantly, it eliminates the class of errors that arise from manual VLOOKUP matching: reference formatting mismatches treated as exceptions, amount variances overlooked because the delta is small, and duplicate payments that clear the match because both entries happen to carry the same amount. The six deterministic exception classes surface every material discrepancy category required by COSO, ICAI SA 505, and standard SOC 1 walkthrough procedures.

The output artifact is a fully structured reconciliation report with matched item counts, per-class exception counts, three-way deltas for Amount Variance items, and source-document line references for every row. The controller signs off on the exception dispositions — posting a missing bill payment in NetSuite, confirming an in-transit item, or recording a bank charge to the appropriate GL account — and the report drops directly into the period-close audit workpaper file, satisfying both the preparer-reviewer control requirement and the auditor's request for evidence that the book balance has been agreed to the bank statement.

Frequently Asked Questions