17.3 Bank Account Reconciliation & Statement Matching Rules

17.3 Bank Account Reconciliation & Statement Matching Rules

Quick Summary: In Microsoft Dynamics 365 Business Central, periodic cash auditing is executed through the Bank Acc. Reconciliation page (Page 379 / Table 273). The interface presents a dual-pane workspace comparing external Bank Statement Lines (left pane) imported via electronic bank feeds or flat files against open Bank Account Ledger Entries (right pane) recorded in Business Central. Automated matching leverages algorithmic rules based on dates (with configurable tolerance days), monetary amounts, and reference text. Unrecorded bank transactions such as service charges or interest income are resolved directly using the Transfer to General Journal function. Once the Total Difference reaches exactly 0.00, posting closes the matched ledger entries and archives the immutable historical statement to the Bank Account Statement page (Page 383).

---

The Bank Account Reconciliation Architecture (Page 379)

The Bank Acc. Reconciliation card (Alt+Q -> type Bank Account Reconciliations, Page 379 / Table 273 Bank Acc. Reconciliation and Table 274 Bank Acc. Reconciliation Line) is the primary tool for reconciling internal cash ledgers with external banking statements.

Bank Account Reconciliation Dual-Pane Architecture:

[Header: Bank Account No., Statement No., Statement Date, Ending Balance]
═════════════════════════════════════════════════════════════════════════
LEFT PANE: Bank Statement Lines          RIGHT PANE: Bank Account Ledger Entries
(External Source: Imported File / Feed)  (Internal Source: Table 271 Open = Yes)
───────────────────────────────────────  ───────────────────────────────────────
Date       Description   Amount          Date       Doc No.   Type      Remaining
2026-06-01 Customer Dep  $4,500.00 <───> 2026-06-01 DEP-101   Payment   $4,500.00
2026-06-03 Vendor Pmt   -$1,200.00 <───> 2026-06-02 CHK-4002  Payment  -$1,200.00
2026-06-05 Monthly Fee     -$35.00 [Unmatched -> Transfer to General Journal]
───────────────────────────────────────  ───────────────────────────────────────
Total Statement Lines Applied: $3,300    Total Bank Ledger Applied:      $3,300
Statement Ending Balance:     $25,465    Balance on Bank Account:       $25,500
Total Difference:               $0.00 (MUST equal 0.00 to post!)

Document Header Fields

• Bank Account No.: The internal cash account being audited.
• Statement No.: The sequential statement identifier (increments automatically from the Bank Account Card's Last Statement No.).
• Statement Date: The financial cutoff date of the bank statement.
• Balance Last Statement: The ending balance from the previous reconciled statement (non-editable, ensuring continuous statement continuity).
• Statement Ending Balance: The closing cash balance stated on the physical/electronic bank statement. This value drives the reconciliation equation.

Inbound Statement Import Channels

Bank statement lines in the left pane can be populated via:

1. Bank Feeds (Envestnet Yodlee / AMC Banking): Direct API integration automatically pulls daily posted transactions.
2. Import Bank Statement (Actions -> Bank -> Import Bank Statement): Reads electronic statement files (e.g., CAMT.053, OFX, MT940, or custom CSV files) mapped through the Data Exchange Framework.
3. Manual Entry: Users can manually key statement lines directly into the grid.

---

Reconciliation Matching Engines: Automatic vs. Manual

Once statement lines are loaded, users match them against open Business Central ledger entries.

Matching Rules Resolution Flow:

[Imported Statement Lines]                [Open Bank Account Ledger Entries]
           │                                             │
           ▼                                             ▼
     [Action: Match Automatically (Codeunit 1252 Match Bank Pmt. Appl.)]
           │
           ├── Test 1: Amount must match exactly (Positive/Negative sign sensitive)
           ├── Test 2: Date variance <= "Transaction Date Tolerance (Days)"
           └── Test 3: Text pattern search (Check No., External Doc No., Description)
           │
     ┌─────┴─────────────────────────┐
     ▼ Matched (Green Highlight)     ▼ Unmatched
[Applied Amount updated]       [Action: Match Manually]
[Difference = 0.00]             Select 1 Line Left + 1 Line Right -> Match
                                OR Select 1 Line Left + N Lines Right (Lump Sum)
                                OR "Remove Match" to unlink incorrect pairs

1. Match Automatically

Selecting Matching -> Match Automatically runs Codeunit 1252 (Match Bank Pmt. Appl.). The user specifies the Transaction Date Tolerance (Days) (e.g., 3 days):

• Matching Criteria: The algorithm compares Statement Amount with Remaining Amount, verifies that the difference between Transaction Date and Posting Date does not exceed tolerance days, and evaluates text similarity across Description, Check No., and External Document No..
• Visual Feedback: When a match is established, lines on both sides are highlighted in green font, their Applied Amount fields are populated, and the statement line's Difference field drops to 0.00.
• Matching Rules Configuration: Advanced matching behavior can be customized in Bank Pmt. Appl. Rules (Page 1252), defining score thresholds for text comparisons.

2. Match Manually

For transactions where automated rules fail—such as date clearing delays exceeding the tolerance window or differing description text:

• The user highlights one or more lines in the left pane and one or more lines in the right pane.
• Clicking Matching -> Match Manually links the selected records.
• One-to-Many Matching: Critical when a bank statement displays a single lump-sum deposit (e.g., $10,000) that was recorded in Business Central as three separate cash receipts ($5,000, $3,000, $2,000). The user highlights the single statement line on the left and the three ledger entries on the right, then selects Match Manually.

3. Remove Match

If lines are paired erroneously by the automated engine or user mistake, selecting the matched line(s) and clicking Matching -> Remove Match dissolves the link, returning both lines to an unapplied status.

---

Resolving Unrecorded Bank Activity: Transfer to General Journal

Bank statements frequently contain transactions that have never been recorded in Business Central: monthly maintenance fees, wire transfer charges, merchant processing fees, or earned interest income.

Transfer to General Journal Execution Pipeline:

[Unmatched Bank Statement Line: $35.00 Bank Service Fee]
                          │
                          ▼ Action: Process -> "Transfer to General Journal"
[Transfer to Gen. Jnl Dialog]
  ├── Specify Gen. Journal Template (e.g., GENERAL)
  ├── Specify Gen. Journal Batch (e.g., BANKFEE)
  └── Select Balancing G/L Account (e.g., 60300 Bank Fees Expense)
                          │
                          ▼ Click OK
[Populated General Journal Line]
  - Posting Date: Statement Transaction Date (2026-06-05)
  - Document No.: Populated from Statement
  - Amount: -$35.00
  - Account: Bank Account | Bal. Account: 60300 Expense
                          │
                          ▼ Post Journal (F9)
[Automatic Instant Auto-Match]
  - Creates new open Bank Account Ledger Entry (-$35.00)
  - Re-opens Page 379: New entry appears on Right Pane
  - Automatically matched to the Left Pane Statement Line (Difference = 0.00)

Operational Advantages of Transfer to General Journal

Rather than abandoning the active reconciliation, exiting to a general journal, posting the transaction, and re-entering the reconciliation:

1. The user highlights the unrecorded bank statement lines in the left pane.
2. Selects Process -> Transfer to General Journal.
3. Specifies the target journal batch and balancing expense or revenue account.
4. Business Central generates pre-filled journal lines. Upon posting (F9), the system posts the G/L entries, creates the corresponding Bank Account Ledger Entry, and automatically matches it against the initiating statement line in the reconciliation, immediately zeroing out the difference.

---

Pre-Posting Validation & The Reconciliation Formula

Before a bank account reconciliation can be committed, Business Central verifies mathematical balance across several core ledger variables.

$\text{Total Difference} = \text{Statement Ending Balance} - (\text{Balance Last Statement} + \text{Statement Lines Total})$

Non-Negotiable Posting Rules

• Total Difference Must Equal Zero: If the Total Difference field in the bottom summary pane is anything other than 0.00, Business Central aborts posting with the error: "The Total Difference must be 0 when posting."
• Test Report Audit: Choosing Actions -> Posting -> Test Report prints a pre-audit report displaying reconciled lines, outstanding checks (entries open in BC but uncredited by the bank), deposits in transit, and any remaining variances.
• Outstanding Entries: Bank Account Ledger Entries on the right pane that remain unmatched (e.g., outstanding vendor checks that have not yet been cashed) do not block posting. They remain Open = True in Table 271 and will carry forward to future bank reconciliations.

Post vs. Post and Print

• Post (F9): Executes Codeunit 371 (Bank Acc. Recon. Post). It closes the matched Bank Account Ledger Entries by updating their Open status to False, sets Statement Status = Closed, records Statement No. and Statement Line No. on Table 271, and moves the reconciliation to the permanent archive.
• Post and Print (Shift+F9): Posts the reconciliation and immediately generates the formal Bank Account Statement audit report.

---

Historical Archives: The Bank Account Statement Page

Once posted, a bank reconciliation is deleted from the active worktable (Table 273) and preserved permanently in the Bank Account Statement archive (Alt+Q -> type Bank Account Statements, Page 383 / Table 275 Bank Account Statement Header and Table 276 Bank Account Statement Line).

Bank Reconciliation Posting & Archiving Architecture:

[Active Bank Reconciliation: Page 379 / Table 273]
                 │
                 ▼ Action: Post (F9) -> Codeunit 371
       ┌─────────┴─────────┐
       ▼                   ▼
[Ledger Closures]    [Historical Statement Archive]
- Table 271:          - Table 275: Bank Account Statement Header
  Open -> False       - Table 276: Bank Account Statement Lines
  Statement No. = 24  - Accessible via Page 383 "Bank Account Statements"
  Statement Line = 1  - Permanent, immutable audit trail
                      - Supports "Print Statement Report"

Historical Inquiry & Audit Support

Auditors and financial controllers use Page 383 to:

• Review exactly which bank statement lines cleared against which internal ledger entries on any historical date.
• Reprint historical bank reconciliation statements including cleared amounts and outstanding items at that point in time.
• Note: While individual subledger transactions can be reversed, a Posted Bank Account Statement cannot be unposted or reopened. If an error occurred, corrections must be handled in the subsequent reconciliation cycle.

---

Bank Reconciliation Operational Channels Comparison

---

Step-by-Step UI Execution Workflows

Workflow A: Importing Statement, Automated Matching, and Manual Reconciliation

1. Press Alt+Q, type Bank Account Reconciliations, and click New (Page 379).
2. In the Bank Account No. field, select CHECKING.
3. In the header, verify Statement Date (e.g., 2026-06-30) and enter the Statement Ending Balance from the bank statement.
4. Select Bank -> Import Bank Statement and upload the monthly bank file (or select Process -> Suggest Lines for simulated testing).
5. In the action bar, click Matching -> Match Automatically. In the dialog, set Transaction Date Tolerance (Days) to 3 and click OK.
6. Review matched lines highlighted in green. For any remaining unmatched lines (e.g., a lump sum deposit), highlight the statement line on the left and the corresponding multiple receipt entries on the right, and click Matching -> Match Manually.

Workflow B: Recording Unposted Bank Fees and Finalizing Reconciliation

1. In the left pane of Page 379, locate an unmatched bank statement line representing a $50.00 wire transfer fee.
2. Highlight the line and click Process -> Transfer to General Journal.
3. In the popup dialog, select Gen. Journal Template GENERAL, Batch BANK, and specify Balancing G/L Account 60300 (Bank Charges Expense). Click OK.
4. Business Central opens the General Journal showing the pre-populated line. Press F9 to post the journal.
5. Reopen the bank reconciliation. Observe that the $50 fee has generated a bank ledger entry and is now automatically matched to the statement line.
6. Verify that Total Difference displays 0.00.
7. Select Actions -> Posting -> Post (F9). Click Yes to post the reconciliation.
8. Verify that the reconciliation is archived under Bank Account Statements (Page 383).

---

Common Configuration Pitfalls & Exam Traps

• Pitfall 1: Attempting to Post with Non-Zero Total Difference. Business Central strictly blocks reconciliation posting if Total Difference is not 0.00. Even a $0.01 discrepancy will cause the posting routine to halt with a validation error.
• Pitfall 2: Date Delays Exceeding Transaction Date Tolerance. If bank deposits take 5 days to clear over a holiday weekend but Transaction Date Tolerance (Days) is set to 2, Match Automatically will fail to pair the lines despite identical monetary amounts. Users must either widen the tolerance or execute Match Manually.
• Pitfall 3: Reconciling Bank Account Reconciliation (Page 379) vs. Payment Reconciliation Journal (Page 1290). The MB-800 exam tests the distinction between these two pages: Page 379 reconciles bank statements against already-posted Bank Account Ledger Entries. Page 1290 (Payment Reconciliation Journal) is used to import bank statements and simultaneously post customer payments, vendor disbursements, and bank reconciliations in a single step.
• Pitfall 4: Modifying Balance Last Statement. Users cannot manually alter Balance Last Statement on Page 379. This balance is hard-locked from the previous posted statement to ensure immutable financial audit trails. If the starting balance is incorrect, the prior statement was either posted erroneously or transactions were posted with retro-active dates.