On this page, we provide options that potentially can be used to enhance the matching rated
Introduction
During import, the Bank Reconciliation offers the option to match automatically against unreconciled transactions. The matching algorithm that is used can be defined for each bank account configuration individually. When the matching rate isn’t as expected the first step is to start identifying why this happens. The matching logic works with a fixed logic and due to this is often easy to identify why the system wasn’t able to identify the expected transaction. We recommend in this situation to start with creating a document where mismatches are recorded with detailed information on why it did not match. After a certain period, this document should give insides in which situations are most often encountered, and based on this information a next enhancement step can be taken.
Creating insights
The first step is to identify the mismatches and see why the mismatch happened. After a fixed period of monitoring the most common situation can be identified and from there it is possible to investigate if there is a potential way to enhance the matching. For recording this information we suggest creating a document with the following layout
Statement ID/Link | Line number | Reason why it didn’t match | Reaons why it did select an incorrect transaction |
|---|---|---|---|
Example ID 1 | 12 | Invoice number prefix was not present in the bank description | |
Example ID 4 | 5 | Invoice number was only the last 5 digets | |
Example ID 6 | 3 | Incorrect amount was paid | |
Etc. | Etc. | Etc. |
Investigating mismatches
There are two types of mismatches:
No match, but one was expected
Match present, but incorrect
Both situations require a different approach to investigate and conclude why it did occur. This investigation is in certain situations time consuming but important to do correctly otherwise the steps to improve the system will not provide the expected outcome.
Investigation no match
When a bank statement transaction line isn’t matched, the first step is identifying the expected transaction. If this does exist in Netsuite the next step will be to investigate how the matching for the bank account is set up. To retrieve this information navigate in the main menu to SETUP->BANK RECONCILIATION->BANK ACCOUNT CONFIGURATION. Open in this list the bank account configuration that is related to the bank on which the match didn’t happen. On the bank account configuration, there are several matching options and each of them will have different behavior. As a user, you most likely will see the logic why you were able to find the correct match and this logic must be compared to the applied matching rules. If for example, your expectation is that it should match the amount and this function is not enabled the root cause is simple to adjust.
The rules the Bank Reconciliation provides are explained on the setup record, but this information can also be retrieved by triggering the field level helped from the checkbox. To trigger this simply click on the field name.
The most common reasons we see as Fast Four are explained below with a suggestion to improve the matching rate.
Resolve no matching
The transaction number in the description from the bank is not containing the prefix used in Netsuite
An example for this would be: INV-000123 is in the description from the bank actually 000123. In this situation, an option could be to extend the Netsuite system and the bank reconciliation matching parameters. We only recommend taking this action when there are multiple occurrences present in the created insights document. Creating this additional matching option exists out of 2 steps.
Creating an additional transaction body field
Creating an additional matching parameter.
Expected at his point is that the administrator knows how to create an additional transaction body field. On this new transaction body field, a formula should be used to populate this new field. The formula will simply take the transaction id and split it on your findings and take the expected value as the value to populate the field with. The formula for this particular example will look as follow.
It is important that the store value checkbox is not checked. The formula used in this situation trims the invoice number on a fixed set of characters:
LTRIM({tranid}, 'INV-')
On the transaction, we now see the following:
The next step is to extend the bank account configuration so it does take this parameter as a matching value. To do this navigate to SETUP->BANK RECONCILIATION->BANK ACCOUNT CONFIGURATION and open the related bank account configuration. Now scroll to the bottom of the page and click on the subtab labeled “Additional Parameters&Fields” and press on the button labeled “New Banks Statement Matching Parameter”. In this screen enter the name of the newly created field and after selecting the transaction id field will be automatically populated.
Save the configuration and directly create the next entry. In this entry type directly in the field labeled “Transaction Field Id” the value “tranid” and save. This must be done otherwise the system will only use your newly crated field due to the fact that this custom configuration will overwrite the standard logic. From this moment onward the system will check not only the standard transaction ID from Netstuite but also your customized transaction ID field and matching should increase.
Resolve incorrect matching
The description did not hold any kind of transaction number
When there isn’t any reference provided by the customer or payee then the match on document number will not be able to find any positive matches. At this point, a potential step forward could be to match on the amount. For match on the amount the Bank Reconciliation does provide 2 options
Match open/closed on exact match of amount within date range
Match open/closed on entity name and match open/closed transactions for that entity
We recommend using the second option when possible. This option does require that the bank does provide a counterparty name, if this is not present the system can not use this matching option. The match on an entity does bring a self-learning algorithm that understands automatically that a specific name from the bank could be different inside your Netsuite account, so this means that when enabled the system will become better over time in matching.
The first options should only be used if you are certain that amounts a pretty unique otherwise this rule could lead to incorrect or unexpected matches. With this option, the system only looks at the amount and does not take the entity into consideration.
Transaction not within date range
The Bank Reconciliation SuiteApp