Problem
Nancy has been forced to move from the North American version of the Bank Reconciliation in Business Central to the world-wide version that everyone else uses. Unfortunately, auto-matching doesn’t work very well with how her payments are recorded and the bank provides transaction activity. In fact, it is incorrectly matching several transactions out of her 600+ each time she reconciles. She didn’t realize how much of a problem this was until about her 5th bank reconciliation after the change. Now she must undo all the bank reconciliations and redo them one-by-one correctly.
A bit of background
Nancy’s company pays many vendors using EFT. When the transactions are recorded in BC, they are individually listed with remit document numbers in the bank and check ledgers. However, the bank records the total amount of all the transactions submitted in the EFT batch as the amount. Checks are also used to pay vendors and employees. These checks may or may not be cashed quickly and frequently involve whole dollar amounts with many transactions of the same amount. And finally, all other transactions are recorded manually, and even if there is a document no. assigned to the transaction in BC, the bank has no unique identifier in the document number field provided with the transaction activity export.
Summary of issues
When the bank activity is imported to BC and auto-match is used, bank statement transactions are matched to the incorrect entry in BC, even when the entry is for a check and includes date, amount and document no. for use in matching.
It was determined that no matter what number of days is used for payment date tolerance when matching, checks and other transactions where the amount is common in the unreconciled ledger entries are frequently incorrectly matched between ledger and statement. For example, check no. 1857 in the amount of $30.00, dated 7/31/23 in statement import may be matched to a manual payment entry in the amount of $30.00, dated 08/15/23 with no document no. in BC ledger when the tolerance input entered was 3 days.
What I learned
What was not ever clear to me is that the Bank Reconciliation auto-match does NOT use the Payment Application rules. Instead of the rules, it uses mostly the following from Codeunit 1252 “Match Bank Rec. Lines”:
Auto Bank Reconciliation matching conditions in Business Central
The matching algorithm basically checks these parameters: Amount, Transaction Date, Related Party Match, Document Number Match, External Document Match, Description Match
Amount: The algorithm looks if the amount on the left matches the right, exactly (sometimes it may accept differences in pennies)
Transaction/Posting Date: When you try to auto-match, a tolerance input is expected. Based on the tolerance input, the dates on either (left & right) pages are matched with each other
Document No Match & External Document No Match: Checks if the imported document number matches (by how much % in nearness) with the existing ledger
Description Match: Checks how “near” the existing description is with the imported description
In a nutshell, all that matters is your Transaction Date & Amount to match, other parameters have a high chance of not being similar/near to each other.
How to proceed
Based on what was learned. I will be recommending that Nancy either match everything manually (not a very desirable solution) or use Suggest Lines to “bring in statement lines” rather than importing the transaction activity for reconciliation from the bank.
By using Suggest Lines with auto-match, then unselecting the lines that did not clear, Nancy’s approach using the new bank reconciliation will be very similar to the method she was forced to leave behind.
Until the problems that US companies must deal with because of paying by check and/or banks not getting or sending manual payment entries with document numbers, matching in BC for bank reconciliation may not be as reliable as we would like.
You may want to check your bank reconciliations for mismatched entries if you have similar use of checks, manual payments and EFT as Nancy.
A vote for change
Currently, unlike the payment reconciliation, there are no application rules that can be controlled by end users. It would be great to have the same application rules controls for the bank reconciliation as on a payment reconcilation journal. Then giving a match confidence to matches would also help when auto-matching. In addition, having rules which are Bank Account specific would be really good!
Use this link to vote to get this change considered then hopefully implemented. In addition, having rules which are Bank Account specific would be really good!
