--- name: bank-reconciliation description: 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 preparing reconciliation documentation. Triggers on phrases like "bank reconciliation", "bank rec", "reconcile account", "outstanding checks", "deposits in transit", "bank fees", "reconciliation exception", "unreconciled items", "bank statement matching". --- # 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 - **Multi-currency accounts**: - Translation: Bank balance in foreign currency translated at month-end spot rate - FX gain/loss: Difference between transaction date rate and settlement date rate - Reconciliation: Match by original currency; FX impact tracked separately - Example: €10,000 deposit at 1.0800 = $10,800 GL; settled at 1.0900 = $10,900 bank → $100 FX gain - **Intercompany cash transfers**: - Timing: Transfer initiated Day 30, settled Day 1 next month → deposit in transit - Matching: Both entities must reconcile (one shows disbursement, other shows receipt) - Netting: Intercompany netting arrangements → single net settlement per period - FX: Cross-border transfers → FX rate at transfer date vs. settlement date - **Petty cash and imprest funds**: - Reconciliation: Physical count of cash + receipts = fund total - Frequency: Monthly count; surprise counts quarterly - Documentation: Petty cash log with date, amount, purpose, approver - Threshold: Fund total typically $500–$5,000; replenishment when < 50% remaining - **Bank fees and charges**: - Monthly service fee: $10–$50 per account (waived with minimum balance) - Wire transfers: $25–$50 outgoing; $15–$25 incoming - ACH transactions: $0.10–$0.50 per transaction - Overdraft: $30–$40 per occurrence (avoid through cash management) - Insufficient funds (NSF): $25–$35 per occurrence - Review: Monthly fee statement against bank fee schedule; contest incorrect charges - **Fraud detection indicators**: - Unauthorized transfers: Outgoing wire not initiated by company - Identity theft: New payee added to ACH file without authorization - Check fraud: Altered check amount; counterfeit check - Account takeover: Login from unusual IP/location; password reset request - Actions: Freeze account immediately; file police report; notify bank fraud department - Prevention: Positive pay (bank verifies check details); dual authorization; regular account monitoring - **Credit union and non-traditional banks**: - Statement delivery: May be slower (PDF upload, not API) - Reconciliation: Manual matching more common (lower auto-match rate) - Controls: Same standards apply regardless of institution type - Consideration: Evaluate API availability when selecting banks ## Integration Points - **Banking APIs**: Plaid, SWIFT gpi, bank直Connect (Fiserv), Salt Edge — automated statement feeds, real-time balances - **ERP/GL**: NetSuite, SAP, Oracle — GL cash balances, trial balance, journal entry posting - **Reconciliation tools**: BlackLine, Trintech, Cadency, Mendix — automated matching, workflow, sign-off - **Treasury systems**: Kyriba, SAP Treasury, GTreasury — cash positioning, payment execution, account management - **Payment platforms**: bill.com, Stripe, Adyen — payment confirmation, receipt tracking - **Document management**: SharePoint, Google Drive, NetDocuments — statement archival, workpaper storage - **SOX compliance**: AuditBoard, SAI360 — control testing, evidence collection, exception monitoring - **Fraud detection**: SAS Fraud Framework, Actimize — anomaly detection, pattern analysis - **BI tools**: Tableau, Power BI — reconciliation dashboards, exception trending - **Alert systems**: Slack, Microsoft Teams — threshold alerts, exception notifications