Finance AI Skill

Bank Reconciliation

Automate bank account reconciliation to identify and resolve differences between GL balances and bank statements. Use when performing monthly bank reconciliations, investigating unreconciled items, managing outstanding items, handling bank errors, or prepar...

Bank Reconciliation

Ensure accuracy of cash balances through systematic reconciliation of GL records with bank statements and resolution of all differences.

Workflow

Reconciliation Process

Trigger: Monthly reconciliation cycle (Days 1–5 post-close); continuous for high-volume accounts; daily for treasury management:

  1. Bank statement acquisition: Automated bank feed (API/SWIFT/BAI2); PDF parsing for non-API banks; multi-currency handling with FX rates.
  2. GL cash balance extraction: Trial balance cash accounts by bank; subsidiary ledger detail; sub-ledger cash (petty cash, petty funds).
  3. Automated matching: AI matches bank transactions to GL entries by amount, date, reference; confidence scoring (95%+ auto-reconcile).
  4. Exception investigation: Unmatched items flagged; categorized (timing, error, fraud, bank fee, interest); researched and resolved.
  5. Outstanding items tracking: Outstanding checks (> 30 days flagged); deposits in transit; recurring unreconciled items escalated.
  6. Adjustment entries: Bank fees, interest income, NSF checks, direct debits not recorded in GL; journal entries posted.
  7. Reconciliation completion: Reconciled balance verified; reconciling items documented; controller sign-off.
  8. Documentation and archival: Reconciliation workpaper archived; supporting documentation attached; audit trail maintained.

Reconciliation Timeline

MONTHLY BANK RECONCILIATION — 5-DAY CYCLE
===========================================

Day 1 (Data Collection):
  ├─ Download bank statements for all accounts (prior month)
  │   API feeds: Chase, BofA, Wells — automated by 8 AM
  │   SWIFT/BAI2: International accounts — manual download by 10 AM
  │   PDF/manual: Local banks without API — download and parse by 12 PM
  ├─ Extract GL cash balances from trial balance
  ├─ Pull sub-ledger detail (AR receipts, AP payments, petty cash)
  └─ Data quality check: All accounts have statements; balances extracted

Day 2 (Automated Matching):
  ├─ Run matching engine (AI-based transaction matching)
  │   Match criteria: Amount + date (± 3 days) + reference/memo
  │   Confidence levels:
  │     High (> 95%): Auto-reconcile (typically 70–85% of transactions)
  │     Medium (75–95%): Flag for review (10–20% of transactions)
  │     Low (< 75%): Manual investigation (5–10% of transactions)
  ├─ Review medium-confidence matches (approve or reject)
  ├─ Categorize unmatched items:
  │   Timing difference: Outstanding checks, deposits in transit
  │   GL error: Missing entry, wrong amount, wrong account
  │   Bank error: Duplicate posting, wrong amount, unauthorized transaction
  │   Unidentified: Requires investigation
  └─ Generate preliminary reconciliation report

Day 3 (Exception Resolution):
  ├─ Investigate unmatched items (> $100 individually researched)
  ├─ Outstanding checks:
  │   > 30 days: Contact payee; confirm if cashed; consider reissuance
  │   > 90 days: Escalate to controller; consider reversal (with approval)
  │   > 180 days: CFO approval for reversal; reissue if payee requests
  ├─ Deposits in transit:
  │   Verify deposit date and bank processing time
  │   Normal: 1–2 business days (same-day for ACH)
  │   Abnormal: > 3 business days → investigate
  ├─ Bank fees and interest:
  │   Identify fee type (monthly service, wire, overdraft, ACH)
  │   Verify against bank fee schedule
  │   Create adjustment journal entries
  ├─ NSF checks:
  │   Identify customer and amount
  │   Rebill customer or write off (per policy)
  │   Create adjustment entries
  └─ Update reconciliation with findings

Day 4 (Review and Sign-off):
  ├─ Controller reviews reconciliation:
  │   All accounts reconciled to zero
  │   Outstanding items within normal parameters
  │   Aging of outstanding items acceptable
  │   Adjustment entries appropriate and documented
  ├─ Exception items resolved or escalated
  ├─ Adjusting journal entries posted
  └─ Reconciliation signed off electronically

Day 5 (Documentation and Archival):
  ├─ Reconciliation workpaper finalized and saved
  ├─ Supporting documentation attached (bank statements, JE support)
  ├─ Outstanding items log updated
  ├─ Metrics recorded: Reconciliation time, exception count, auto-match rate
  └─ Close reconciliation period; lock for audit trail

Reconciliation Template

BANK RECONCILIATION — [Bank Name] — [Account Number] — [Month Year]
=====================================================================

PERIOD END BALANCES:
  Bank statement balance, [Month End Date]:     $XXX,XXX.XX
  GL cash balance, [Month End Date]:            $XXX,XXX.XX

ADJUSTMENTS TO BANK BALANCE:
  Add: Deposits in transit
    [Date]    [Description]       [Amount]
    XX/XX     Customer payment    $XX,XXX.XX
    XX/XX     Wire transfer       $XX,XXX.XX
    Total deposits in transit:                     $XX,XXX.XX

  Less: Outstanding checks
    [Check #]   [Date]    [Payee]           [Amount]
    XXXXX       XX/XX     Vendor A          $X,XXX.XX
    XXXXX       XX/XX     Vendor B          $X,XXX.XX
    XXXXX       XX/XX     Payroll           $XX,XXX.XX
    Total outstanding checks:                   ($XX,XXX.XX)

  Adjusted bank balance:                          $XXX,XXX.XX

ADJUSTMENTS TO GL BALANCE:
  Add: Interest income                           $XXX.XX
  Add: Bank error correction                     $XXX.XX
  Add: Other credits not recorded                $XXX.XX
  Total additions:                              $XXX.XX

  Less: Bank service charges                    ($XXX.XX)
  Less: NSF checks                              ($XXX.XX)
  Less: Wire transfer fees                      ($XX.XX)
  Less: Other debits not recorded               ($XX.XX)
  Total deductions:                            ($XXX.XX)

  Adjusted GL balance:                          $XXX,XXX.XX

RECONCILIATION STATUS:
  Adjusted bank balance = Adjusted GL balance:   ✓ RECONCILED
  Difference:                                    $0.00

OUTSTANDING ITEMS AGING:
  0–30 days:  $XX,XXX.XX  (X items)   ✓ Normal
  31–60 days:  $X,XXX.XX  (X items)   ⚠ Monitor
  61–90 days:  $XXX.XX    (X items)   ✗ Investigate
  90+ days:    $0.00      (0 items)

SIGN-OFF:
  Prepared by: [Name]    Date: [Date]
  Reviewed by: [Controller Name]    Date: [Date]

Outstanding Items Management

Aging and Resolution

OUTSTANDING ITEMS POLICY
==========================

Outstanding Checks:
  0–30 days: Normal; no action required
  31–60 days: Monitor; include in reconciliation notes
  61–90 days: Investigate; contact payee to confirm status
  91–180 days: Escalate to Controller; consider reversal if uncashed
  180+ days: CFO approval for reversal; credit to miscellaneous income
    Reversal entry:
      Dr Accounts Payable (or Cash)    $X,XXX
      Cr Miscellaneous Income         $X,XXX
    Note: Check reissued if payee requests (stop payment fee: $25–$40)

Deposits in Transit:
  0–3 days: Normal (standard clearing time)
  4–7 days: Investigate; contact bank to verify
  8+ days: Escalate to Controller; potential fraud indicator
    Actions: Verify with remitter; check deposit slip; file claim with bank

Recurring Unreconciled Items:
  Definition: Same item appears unreconciled for 3+ consecutive months
  Action: Escalate to Controller; root cause analysis
  Common causes:
    - Timing mismatch (different accounting periods)
    - Missing GL entry (transaction not recorded)
    - Wrong account (posted to wrong bank account)
    - Bank error (duplicate posting, wrong amount)
  Resolution: Correct underlying cause; not just recurring carry-forward

Bank Errors:
  Identification: Transaction in bank but not in GL (or vice versa) with no explanation
  Action: Contact bank immediately; provide transaction details
  Timeline: Bank typically resolves within 5–10 business days
  Documentation: Bank error letter/email attached to reconciliation
  Follow-up: Monitor bank statement for correction entry

Reconciliation Controls

SOX-Compliant Controls

BANK RECONCILIATION INTERNAL CONTROLS
=======================================

Control 1: Timeliness
  - Reconciliation completed within 10 business days of month-end
  - Target: 85%+ of accounts reconciled by Day 5
  - Escalation: Unreconciled accounts flagged to Controller by Day 7
  - Exception: New accounts may need 15 days for first reconciliation

Control 2: Independence
  - Reconciler ≠ bank account signer (segregation of duties)
  - Reconciler ≠ cash disbursement processor
  - Review by independent party (Controller or Finance Manager)
  - Exception: Small companies — compensating control = external review

Control 3: Completeness
  - All bank accounts reconciled (including zero-balance, new, closed)
  - Account inventory maintained and reconciled quarterly
  - Dormant accounts: Reconciled annually even if no activity
  - Verification: Bank confirmation letters annually (external auditor)

Control 4: Documentation
  - Reconciliation workpaper saved with each period
  - Supporting documents attached (bank statements, JE support)
  - Electronic sign-off captured (timestamp, user ID)
  - Retention: 7 years minimum (SOX requirement)

Control 5: Exception Monitoring
  - Monthly review of: Outstanding items > 90 days, unusual bank fees, large adjustments
  - Trend analysis: Reconciliation exception rate by month
  - Fraud indicators: Unidentified credits, unusual debits, round-dollar amounts
  - Reporting: Monthly exception report to CFO

Control 6: Access Control
  - Bank account access: Limited to authorized signers
  - Online banking: Dual authorization for transfers > $25,000
  - Reconciliation system: Access limited to accounting team
  - Annual review: Access rights reviewed and approved by CFO

Edge Cases

Integration Points