Reconcile Bank Payment Files

Goal

Reconcile imported bank payment files with property tax receivables to ensure all payments are properly recorded, matched, and accounted for in municipal financial records.

[Screenshot: Bank Reconciliation Dashboard showing daily reconciliation status with bank deposits, system totals, and variance indicators]

When to Use This Workflow

• End-of-day processing: Daily reconciliation of bank deposits with payment processing

• Monthly reconciliation: Comprehensive review of monthly bank statements and payment records

• Audit preparation: Ensuring complete and accurate payment documentation for audits

• Variance investigation: Resolving discrepancies between bank records and system totals

• Period-end closing: Financial period closing procedures and account reconciliation

• Exception resolution: Addressing unresolved payment matching or posting issues

Prerequisites

Before starting this workflow, ensure you have:

• Bank file formats: Understanding of bank file specifications and data layouts

• Reconciliation accounts: General ledger reconciliation account setup and mapping

• Variance procedures: Established procedures for investigating and resolving discrepancies

• Authorization levels: Appropriate permissions for reconciliation adjustments

• Supporting documentation: Access to bank statements, deposit slips, and payment records

• Cutoff procedures: Clear understanding of daily and monthly cutoff times

[Screenshot: Bank Account Configuration screen displaying connected bank accounts with file format specifications and reconciliation settings]

Step-by-Step Instructions

Step 1: Gather Reconciliation Data

1. Navigate to Reports → Payment Reconciliation

2. Select reconciliation period (daily, weekly, or monthly)

3. Generate payment processing summary report showing:

   • Total payments processed

   • Total dollar amount

   • Payment method breakdown

   • Unmatched payment totals

4. Obtain corresponding bank statement or deposit summary

5. Collect any supporting documentation (deposit slips, wire transfer confirmations)

[Screenshot: Payment Processing Summary report displaying totals by payment method with reconciliation period selector]

[Screenshot: Bank Statement Upload interface allowing import of electronic bank statements for automatic comparison]

Step 2: Compare Bank Deposits to System Totals

1. Review bank statement deposits for the reconciliation period

2. Compare bank deposit totals to system payment processing totals:

   • Online payments (credit card, ACH)

   • Wire transfers

   • Cash/check deposits

   • Lockbox payments

3. Identify any variances between bank and system totals

4. Note timing differences (payments in transit)

[Screenshot: Bank vs System Comparison grid showing side-by-side totals by payment type with variance calculations and status indicators]

Step 3: Investigate Variances

1. For each variance identified, determine the cause:

   • Timing differences: Payments processed on different dates

   • Bank fees: Processing fees deducted by bank

   • Processing delays: System or bank processing delays

   • Data errors: Incorrect amounts or missing transactions

2. Document variance investigation findings

3. Determine if variance requires adjustment or explanation

[Screenshot: Variance Investigation screen showing variance details with investigation notes fields and resolution status tracking]

Step 4: Review Unmatched Payments

1. Access Payments → Unmatched Payments report

2. Review payments that couldn't be matched to properties:

   • Total count and dollar amount

   • Age of unmatched payments

   • Common patterns or issues

3. Prioritize unmatched payments by age and amount

4. Research high-priority items for resolution

[Screenshot: Unmatched Payments Analysis showing aging buckets, amount distributions, and priority ranking for resolution]

Step 5: Process Reconciliation Adjustments

1. For legitimate variances requiring adjustment:

   • Create adjustment entries in reconciliation module

   • Post bank fees to appropriate expense accounts

   • Correct data entry errors

   • Process timing adjustments for in-transit items

2. Document all adjustment entries with supporting explanations

3. Obtain required approvals for material adjustments

[Screenshot: Reconciliation Adjustment Entry screen with account coding fields, supporting documentation upload, and approval workflow]

[Screenshot: Bank Fee Processing interface showing automatic fee recognition and general ledger posting options]

Step 6: Complete Reconciliation Documentation

1. Prepare reconciliation summary showing:

   • Beginning balances

   • Payment activity for period

   • Adjustments and corrections

   • Ending balances

2. Attach supporting documentation

3. Have reconciliation reviewed and approved by supervisor

4. File completed reconciliation in permanent records

[Screenshot: Final Reconciliation Summary report with beginning balances, activity, adjustments, and ending balances formatted for supervisor approval]

Example Scenario

Monthly Bank Reconciliation:

• Bank statement shows $500,000 in deposits for March

• System payment reports show $498,500 in processed payments

• Investigation reveals:

  • $1,200 in bank processing fees not recorded

  • $300 timing difference for payment processed after cutoff

  • $15 data entry error corrected

• Final reconciliation: $500,000 = $498,500 + $1,200 + $300

• All variances documented and approved

[Screenshot: Monthly Reconciliation Example walkthrough showing actual bank statement vs system reports with variance resolution steps]

Common Errors & How to Fix Them

Timing Differences

Problem: Payments processed on different dates between bank and system
Solution:

• Establish consistent daily cutoff times

• Track payments in transit separately

• Use deposit date vs. processing date consistently

• Create timing difference schedule for period-end

[Screenshot: Timing Difference Tracking screen showing in-transit payments with expected clearing dates and reconciliation impact]

Bank Fees Not Recorded

Problem: Bank processing fees cause variances in deposit totals
Solution:

• Set up automatic bank fee posting where possible

• Create standard journal entries for common fees

• Review bank fee schedules regularly

• Include fee estimates in daily processing

[Screenshot: Bank Fee Setup screen displaying fee schedules and automatic posting configuration for different transaction types]

Processing Delays

Problem: System or bank delays cause reconciliation variances
Solution:

• Identify common delay patterns and adjust procedures

• Communicate with bank about processing schedules

• Build delay expectations into reconciliation procedures

• Monitor processing times for improvement opportunities

[Screenshot: Processing Delay Analysis showing historical delay patterns and performance metrics for different payment channels]

Data Entry Errors

Problem: Incorrect payment amounts or property assignments
Solution:

• Implement data validation controls

• Require dual approval for large payment entries

• Review payment batches before final posting

• Correct errors promptly and document changes

[Screenshot: Data Entry Error Correction screen with before/after comparison and audit trail documentation]

What Happens After This Workflow

Immediate Results:

• Reconciliation Complete: Bank deposits reconciled to payment processing records

• Variances Resolved: All discrepancies investigated and explained or corrected

• Documentation Filed: Complete reconciliation documentation maintained

• Accounts Balanced: Reconciliation accounts properly balanced and closed

Follow-up Actions:

• Exception Follow-up: Continue research on unresolved items

• Process Improvements: Identify opportunities to reduce future variances

• Management Reporting: Provide reconciliation summary to finance management

• Audit Preparation: Ensure documentation meets audit requirements

Related Workflows

• Workflow – Process Property Tax Payments

• Workflow – Apply Customer Payments to Properties

• Workflow – Handle Payment Discounts

Tips for Success

• Perform reconciliations promptly while information is readily available

• Maintain detailed documentation for all variances and adjustments

• Establish standard procedures for common reconciliation issues

• Use system automation where possible to reduce manual effort

• Communicate regularly with bank representatives about processing procedures

• Monitor reconciliation metrics to identify process improvement opportunities

• Train backup staff to ensure continuity of reconciliation procedures

Screenshot Placeholders

[Screenshot: Reconciliation Workflow Overview diagram showing daily, weekly, and monthly reconciliation processes with decision points]

[Screenshot: Multi-Bank Reconciliation Dashboard for municipalities using multiple banking relationships with consolidated reporting]

[Screenshot: Automated Reconciliation Engine settings showing matching rules and tolerance levels for automatic variance resolution]

[Screenshot: Historical Reconciliation Trend Analysis displaying variance patterns and process improvement metrics over time]

[Screenshot: Bank Communication Interface for electronic exchange of reconciliation data and exception resolution with banking partners]

[Screenshot: Supervisor Reconciliation Review screen with approval workflow and exception authorization controls]

[Screenshot: Audit Trail Report showing complete reconciliation history with user actions, timing, and supporting documentation]

[Screenshot: Reconciliation Performance Metrics dashboard tracking processing time, accuracy rates, and exception resolution statistics]

[Screenshot: Month-End Reconciliation Checklist with automated validation of all required reconciliation steps and approvals]

[Screenshot: Variance Aging Report showing unresolved reconciliation items by age with escalation procedures]

[Screenshot: Bank Statement Import Wizard guiding users through electronic statement upload and format validation]

[Screenshot: Real-time Reconciliation Status Monitor showing current processing status across all bank accounts and payment channels]

[Screenshot: Exception Resolution Workflow showing escalation paths for different types of reconciliation variances]