How to Set Up ERP Bank Reconciliation Rules Without Hiding Exceptions

Quick answer

Configure bank reconciliation rules to match uniquely identifiable transactions first. Keep ambiguous matches, unexplained differences and missing postings in an assigned exception queue. Treat rounding adjustments and transaction creation as separate, approved actions—not shortcuts to a higher match rate. Before release, test duplicate amounts, grouped settlements, incomplete statements and incorrect-match recovery.

The workflow below is a recommended control design for controllers, treasury teams and ERP administrators. Product examples distinguish Dynamics 365 Finance advanced bank reconciliation—including its Modern bank reconciliation feature—from NetSuite’s documented bank-matching workflow. Their settings are not interchangeable. Microsoft distinguishes feature-dependent reconciliation actions, while NetSuite separately documents its matching and reconciliation prerequisites. (learn.microsoft.com)

1. Validate statement completeness before running matching rules

Separate bank-data delivery from reconciliation. In NetSuite, Bank Feeds transfers bank data into the application; matching, submitting matches and reconciling transactions are subsequent workflow steps. A successful transfer is therefore not evidence that the account has been reconciled. (docs.oracle.com)

Before enabling automatic matching, document these prerequisites:

• Correct legal entity, bank account and currency.
• Statement identifier, coverage dates and opening and closing balances.
• Complete transaction references, signed amounts and bank transaction codes.
• Approved posting cutoff and available ERP bank transactions.
• Import history sufficient to investigate missing or repeated deliveries.

Dynamics 365 Finance validates account and currency alignment, statement continuity, date overlap and line-date boundaries. It also checks that opening balance plus signed statement movements equals closing balance. (learn.microsoft.com)

Add an operational completeness check: compare imported line counts and totals with the bank’s source statement wherever available. Preserve the original file or a controlled reference to it.

Common mistake: widening matching rules when the real problem is missing data. If a parser drops references or reverses signs, fix the import mapping before changing reconciliation criteria.

2. Put specific matching rules before broad rules

Use the following as a starting sequence—not a universal ERP preset:

Priority	Proposed rule	Required evidence	If the evidence is insufficient
1	Unique reference and exact amount	Correct account, direction and transaction type	Leave unmatched
2	Exact settlement or batch total	Shared batch identifier and complete components	Route for review
3	Other exact matches	Additional identifying fields and a justified date window	Review competing candidates
4	Approved rounding adjustment	Eligible transaction and approved correction treatment	Leave the difference visible
5	New fee or interest posting	Recognized bank code, approved account and posting authority	Assign to accounting

In Dynamics 365 Finance, rule sets execute top to bottom. Importantly, Microsoft documents that matching rules otherwise select the first qualifying bank transaction. Enable Require manual matching when advanced bank reconciliation matching rules find multiple documents that match on amount where applicable. (learn.microsoft.com)

NetSuite documents a different behavior: when a custom rule finds multiple possible matches, the user must choose the correct transaction. Its user matching rules can be reordered, but system rules cannot; auto-create rules also cannot be reordered under the documented rule-management workflow. (docs.oracle.com)

For each proposed rule, record its account scope, matching fields, sequence, date window, permitted action and exception owner. Inspect what the bank’s reference actually maps to: NetSuite’s documented transaction-number comparison uses imported Transaction ID against NetSuite Check Number, whose underlying meaning varies by transaction type. (docs.oracle.com)

Avoid a final “same amount, any reference” rule merely to empty the queue.

3. Keep date tolerances separate from amount adjustments

Write separate policies for:

• Date variation: how far posting and bank dates may differ.
• Amount variation: whether a monetary difference is permitted.
• Accounting treatment: whether accepting that difference creates a posting.

Start with exact amounts. Add a date window only where the payment process justifies it, and test transactions immediately inside and outside the boundary.

An amount tolerance can have accounting consequences. Dynamics 365 Finance documents an Allowed penny difference for eligible single-statement-line/single-bank-transaction matches. Corrections post when reconciliation is completed; Check and Deposit document types are excluded. (learn.microsoft.com)

Do not assume this setting handles grouped settlements or explains missing fees.

For every approved tolerance, specify:

• Eligible accounts, transaction types and currencies.
• Maximum difference and permitted direction.
• Correction account and required dimensions.
• Review requirements and evidence.
• Whether repeated small adjustments trigger investigation.

Common mistake: treating a tolerance as proof that a difference is immaterial. A configured threshold explains what software may accept—not why the underlying transaction is correct.

4. Work through net deposits, fees and identical payments

The following figures are synthetic. They illustrate control decisions, not a prescribed posting configuration.

A net processor deposit

Assume two customer receipts total $1,000, and a processor withholds a $30 fee:

Settlement component	Amount
Receipt A	$600
Receipt B	$400
Processor fee	−$30
Net bank deposit	$970

Do not match the $970 deposit to $1,000 by allowing a $30 unexplained difference.

Under an illustrative clearing-account design, the receipts first total $1,000 in processor clearing. The settlement then debits bank $970, debits fee expense $30 and credits clearing $1,000. The bank reconciliation matches $970 to $970; the settlement report explains the fee.

If the approved posting design instead produces multiple bank-ledger entries, test whether the product supports matching that signed group. Do not manufacture bank entries simply to make a grouping rule work.

Define the sides explicitly: one statement line to several ERP transactions. NetSuite calls this one-to-many matching and documents comparing the grouped ERP total with the imported amount. Its guidance also cautions against selecting a group when another match is possible. (docs.oracle.com)

Require settlement identification and complete components, not just a coincidentally equal total.

A separate bank fee

Suppose the statement also contains a $15 monthly bank fee absent from the ERP. Treat this as a missing posting, not a matching tolerance.

Dynamics 365 Finance’s Modern bank reconciliation feature supports generating general-ledger vouchers for fees and interest, including through matching rules. Prerequisites include bank transaction types and a bank statement reversal reference number sequence. (learn.microsoft.com)

Before enabling such a rule, approve its classification, posting account, dimensions, accounting date and access controls. Confirm that the fee has not already been posted elsewhere.

Two payments for the same amount

The ERP contains two $250 payments, references PAY-A and PAY-B. The bank shows one $250 debit identifying PAY-A.

Match PAY-A only. Leave PAY-B outstanding. If the imported reference is missing, assign the ambiguity for investigation rather than selecting whichever transaction appears first.

5. Prove the rules in a sandbox

Use a controlled statement and ledger dataset with expected outcomes written before execution.

Test case	Expected control outcome
Unique reference and exact amount	Correct transaction matches
Two identical amounts without a usable reference	No arbitrary selection
Net deposit with a missing fee posting	Unexplained difference remains visible
Complete settlement group	Only supported, eligible components match
Missing settlement component	No forced grouped match
Date just outside the permitted window	Transaction remains unresolved
Repeated statement delivery	No unexplained duplicate processing
Incorrect match	Supported correction restores the intended state

Also test whether a rerun consumes transactions differently after an earlier rule changes.

For each case, retain the rule version, input identifiers, expected result, actual result and reviewer decision. Inspect generated vouchers as well as match statuses.

Before release, have someone other than the rule author review both successful and unsuccessful cases. Promote only the approved configuration, with a rollback plan and a defined first-production review.

These are recommended acceptance tests, not claims that every ERP exposes identical controls or audit fields.

6. Reconcile balances and assign every outstanding item

Do not make “zero unmatched lines” the sole completion criterion. Microsoft documents that advanced bank reconciliation can be completed with unmatched statement lines, which then carry forward. Completion status alone therefore does not demonstrate that all exceptions were resolved. (learn.microsoft.com)

Maintain an exception register with:

• Bank account, statement and transaction identifiers.
• Signed amount and original transaction date.
• Reason: timing, missing posting, ambiguity, suspected duplicate or disputed item.
• Named owner, next action and due date.
• Supporting evidence and reviewer disposition.

Suggested ownership is treasury for bank-delivery and bank-query issues, accounting for postings, and AP or AR for payment identification. ERP administrators should investigate rule or import defects without deciding the accounting treatment.

A worked balance check

Continuing the synthetic example:

Bank statement movement	Amount
Opening balance	$10,000
Net processor deposit	+$970
PAY-A cleared	−$250
Monthly bank fee	−$15
Closing statement balance	$10,705

The ERP already records the $970 settlement and both $250 payments:

$10,000 + $970 − $250 − $250 = $10,470

After posting the separately identified $15 bank fee:

Adjusted ERP bank balance = $10,455

PAY-B remains outstanding:

$10,705 statement balance − $250 outstanding payment = $10,455

Both sides agree, while PAY-B remains visible for follow-up. Its continued existence does not justify deleting it, posting it again or matching it to PAY-A.

Set escalation thresholds appropriate to the payment method and business process; do not invent a single aging limit for every outstanding item.

7. Correct bad matches without erasing the evidence

Distinguish removing a match, reversing reconciliation completion and reversing a posting.

For an open Dynamics 365 Finance advanced reconciliation worksheet, Microsoft documents Unmatch, which returns the associated transactions to the unmatched grids. (learn.microsoft.com)

Completed-reconciliation recovery is feature-dependent. Microsoft’s dedicated reversal documentation states that its process applies to Modern bank reconciliation and describes Reverse reconciliation. It separately documents reversing posted statement transactions and individual vouchers. Do not assume these actions are available in every installation or equivalent to unmatching. (learn.microsoft.com)

Use a controlled correction sequence:

1. Preserve the incorrect match, rule version and relevant posting references.
2. Obtain approval for the correction scope.
3. Confirm the supported procedure for the installed release and feature configuration.
4. Unmatch or reverse only what is necessary.
5. Correct the rule or accounting entry through approved processes.
6. Rerun affected tests and balance checks.
7. Retain the original evidence, reversal references and reviewer sign-off.

The completion packet should explain both what matched and what did not: statement validation, final balances, adjustments, outstanding items, ownership and review evidence.

The practical goal is not the highest automatic-match percentage. It is a reconciliation another reviewer can understand, challenge and reproduce.