travo Books Documentation
বাংলা
In this volume · VOLUME 05
Accounting
Chart of Accounts Journals & Ledger Billing & Invoicing Payments & Receipts Payment Reconciliation Multi-Currency Deferred Revenue Commission Accounting Tax (VAT/GST) Period Close
Home / Accounting / Payment Reconciliation

Chapter 5.5 — Payment & Bank Reconciliation

1. Purpose

This chapter defines how travoBooks reconciles its internal ledger against external counterparty records: bank statements, gateway settlement files, BSP weekly statements, and supplier statements of account. Reconciliation closes the loop between what the platform thinks happened and what the bank/BSP/gateway confirmed happened.

2. Why it matters

Unreconciled accounts are where money disappears. Common scenarios in travel: - Gateway settlement net of fees doesn't match captured payments - BSP wire amount differs from expected - Customer wires arrive with no remittance advice (which invoice?) - Bank charges debit silently - Failed-and-retried payments create phantom duplicates

Daily reconciliation is the operational discipline that catches these within hours, not at month-end audit.

3. Industry relevance

Travel agencies typically run four reconciliation streams: 1. Bank reconciliation — internal cash accounts vs bank statement 2. Gateway reconciliation — internal payments vs gateway settlement file 3. BSP/ARC reconciliation — internal BSP-payable vs BSP statement 4. Supplier statement reconciliation — internal AP vs supplier monthly statement

Each follows the same skeleton: ingest external record → match to internal events → flag exceptions → adjust or escalate.

4. Compliance considerations

  • SOC 2 CC7 — monitoring controls; reconciliation is a key detective control.
  • Audit — auditors test reconciliation completeness and timeliness.
  • Treasury policy — daily reconciliation typical; weekly absolute maximum.

5. Business logic

5.1 Bank reconciliation

Input: Bank statement (CSV, MT940, OFX, BAI2).

Process: 1. Import bank statement; each line becomes a bank_statement_line record (raw, unmodified). 2. Auto-match algorithm runs against internal payment/payout records: - Match by (amount, currency, value_date, reference_text). - Fuzzy match by amount within ±0.5% if reference matches. - Match-rate target: 85–95%. 3. Manual matching UI for unmatched lines. 4. Adjustments for bank-side-only entries (bank charges, interest):

Debit  5025 Bank Charges (or 7011 Interest Income)
Credit 1013 Bank
  1. Adjustments for our-side-only entries (rare — usually data error).
  2. Reconciliation report: opening balance + receipts − payments + adjustments = closing balance per both sides; difference must equal zero.

5.2 Gateway reconciliation

Input: Gateway settlement file (Stripe, SSLCommerz, Razorpay, etc.).

Process: 1. Each batch deposits a net amount to bank (gross − fees − refunds − chargebacks). 2. Settlement file lists each transaction in the batch. 3. Match each line to internal payments records by gateway_transaction_id. 4. Compute total fees from settlement; post:

Debit  5026 Gateway Fees                 Σ fees
Credit 1014 Gateway In-Transit            Σ fees
  1. Match the bank deposit to the net settlement.
  2. Flag chargebacks for immediate handling (Chapter 5.4).

5.3 BSP reconciliation

Input: BSPlink billing analysis file (HOT, AGTDATA).

Process: 1. File imported per Chapter 11.3. 2. For each line, match to internal booking_tickets / commission_accruals. 3. Compute per-period BSP totals (gross sales, commission, taxes, ADMs/ACMs, refunds, net to BSP). 4. Compare to internal 2011 BSP Payable balance for the BSP period. 5. Variance analysis: - Commission rate mismatch — supplier paid different commission than expected. - Tax breakdown drift — supplier categorised XT taxes differently. - Missing tickets — issued our side but not in BSP file (data delay or genuine issue). - Phantom tickets — in BSP file but not in our system (orphan). 6. Generate BSP-recon report; variances above threshold open dispute cases.

5.4 Supplier statement reconciliation

Input: Supplier monthly statement (PDF, Excel, CSV, supplier portal).

Process: 1. Operations imports statement (manual upload + OCR for PDF, parser for CSV). 2. Match statement lines to internal AP records by booking-id / invoice-number. 3. Identify: - Booked our side, not on statement. - On statement, not in our books (we missed booking). - Amount mismatches. 4. Reconcile to AP balance. 5. Differences raised with supplier; pending-resolution provisioned.

5.5 Auto-match algorithm

The platform's matching engine combines: - Exact match on reference field - Amount + value-date window match - Customer/supplier identifier fuzzy match (token-based) - Multi-line "split" match (one bank credit covers two invoices) - ML-assisted suggestion (Phase 2) — learns from manual matches

Confidence scoring: HIGH (auto-applied), MEDIUM (suggested for review), LOW (left for manual).

5.6 Exception types

Code Description
BANK_UNMATCHED_CREDIT Customer paid; not yet linked to an invoice
BANK_UNMATCHED_DEBIT Bank charge or unknown debit
BANK_DUPLICATE Same transaction appears twice
GATEWAY_FEE_VARIANCE Computed fee differs from settlement-stated fee
BSP_MISSING_TICKET Issued not in BSP file
BSP_PHANTOM_TICKET In BSP file not in our system
BSP_COMMISSION_VARIANCE Rate mismatch
SUPPLIER_STATEMENT_MISMATCH Amount differs

Each exception has an owner, age, and resolution workflow.

6. Inputs → processing → outputs

(See sub-sections above per recon type.)

7. Module dependencies

Direction Module
Depends on Payments, Payouts, BSP Import, JE Engine, Bank Account config
Depended on by Period Close, Reporting, Dispute Mgmt

8. Security & permissions

Permission Allows
recon.bank.run.partner Run bank recon
recon.gateway.run.partner Gateway recon
recon.bsp.run.partner BSP recon
recon.supplier.run.partner Supplier statement recon
recon.manual_match.partner Manually pair lines
recon.adjustment.create.partner Create reconciling JE
recon.adjustment.approve.partner Approve adjustment
recon.writeoff.partner Write off small variances

Write-offs have per-currency thresholds (e.g., ≤ BDT 100 auto; > BDT 100 requires checker).

9. Validation rules

Code Condition
RECON_OPENING_BALANCE_MISMATCH Opening = previous closing — must equal
RECON_FILE_DUPLICATE Same statement file imported twice
RECON_PERIOD_CLOSED Adjustment hits closed period
RECON_MANUAL_MATCH_AMBIGUOUS Selected match conflicts with existing match
RECON_VARIANCE_EXCEEDS_THRESHOLD Above auto-resolution; requires manual review

10. Error handling

  • Imported statements with parse failures are quarantined with operator notification.
  • Auto-match runs continue even if individual lines fail; failures land in exception queue.
  • A failed reconciliation does not block period close unless the partner config requires it.

11. Real-world examples

A — Daily bank recon

Morning routine: accountant imports yesterday's bank statement. 47 lines. Auto-match resolves 41. Six exceptions: - 3 customer wires without remittance → research, link to invoices. - 1 bank service charge → adjustment JE. - 1 incoming wire from unknown party → contact bank; reverse if return needed. - 1 outgoing standing order to landlord → match to recurring JE.

After resolution, both sides reconcile to closing balance.

B — BSP semi-monthly recon

BSP-BD H1 file (May 1–15) arrives May 18. Internal expected: BDT 4.85M net to BSP. File: BDT 4.88M. - Variance: BDT 30K. - Breakdown: BDT 25K = three ADMs we hadn't yet imported; BDT 5K = commission rate mismatch on one EK route (override structure changed mid-month, not reflected). - ADMs: ingested separately; commission variance: dispute raised with EK. - Wire to BSP: BDT 4.88M per BSP-file authoritative value (avoid penalty). - Variance accrual booked pending dispute resolution.

C — Gateway settlement variance

Stripe settled USD 5,000 net to bank. Internal computation: USD 5,012. Variance USD 12 = one chargeback we hadn't yet recorded. Chargeback JE posted; recon clears.

12. Step-by-step workflow

flowchart TD A[Import External Statement] --> B[Auto-Match Algorithm] B --> C{Match Rate} C -->|≥95%| D[Review Exceptions] C -->|<95%| E[Investigate Quality Issue] D --> F{Exception Type} F -->|Unmatched credit| G[Link to Invoice] F -->|Bank charge| H[Adjustment JE] F -->|Duplicate| I[Investigate; possibly delete one side] F -->|Variance| J[Open Dispute / Write-off] G --> K[Recon Report] H --> K I --> K J --> K K --> L{Balances?} L -->|Match| M[Close Recon Run] L -->|Don't Match| N[Escalate] M --> O[Period-Close Gate Passes]

13. Database tables touched

Table Role
bank_statement_files Raw imports
bank_statement_lines Parsed lines
recon_runs Per-recon execution record
recon_matches Match links
recon_exceptions Open exceptions
recon_adjustments Adjustment JE references
gateway_settlement_files Gateway-side imports
bsp_statement_files BSP statement imports
supplier_statement_files Supplier statement imports

14. Future scalability

  • Real-time bank API matching via Open Banking.
  • AI-driven remittance parsing for unstructured payer references.
  • Automated supplier statement OCR + match.
  • Cross-partner cash pooling views for group-treasury operations.

15. Common pitfalls

  • ⚠️ Reconciling at month-end only. Issues accumulate; daily is the standard.
  • ⚠️ Auto-matching without confidence scoring. Bad matches are worse than no match.
  • ⚠️ Bank charges silently absorbed without JE. They are expenses; book them.
  • ⚠️ Writing off variances without investigation. Small write-offs habitually become large losses.
  • 🔒 Manual matches must be auditable. Every match carries actor/timestamp.