Learn how to reconcile lines on your bank statement with closed transactions.
ZoneReconcile allows users to reconcile their bank statements with 2 types of transactions - open and closed. Open transactions are for example Vendor Bills or Invoices, if these are selected in the popup, a vendor payment or customer payment is created against these transactions respectively. There might be, however, a case that the vendor or customer payment is created before receiving the bank statement, either manually via User Interface, importing via CSV import or by creating a payment batch (DD or EFT) via Electronic Payments module. In this article we explain the supported types for reconciliation of closed transactions, setup to consider for Bank Account Configuration and most used cases & limitations.
Supported Types & How to Reconcile
When the user enters the entity name in the column “Name” on the bank statement line, the popup is triggered, initially offering open transactions for that entity. When checking the “Show Closed Transactions” checkbox, the closed transactions for that entity are displayed, as can be seen on the following screenshot:
In principle, on this generated sublist you may find all transactions fulfilling these criteria:
- The transaction is posted on the bank account OR the payment-in-transit account if set on the Bank Account Configuration (see the Bank Account Configuration setup to consider below)
- The transaction has not been reconciled yet (is not linked to any Bank Statement)
- The checkbox “Exclude from ZoneReconcile” is not checked on the transaction
- The transaction is not voided or memorized
- Transaction’s currency = statement currency, Transaction’s subsidiary = Bank Account’s subsidiary
- The transaction’s date is within the limit of Days Prior and Days after set on the General Preferences.
In practice, these transactions may appear on the list:
- Vendor payment
- Customer payment
- Customer refund
- Vendor Prepayment
- Cash Sale
- Journal entry
Selecting the correct transaction, submitting the popup and saving the bank statement creates a link between the transaction and the bank statement. Based on the configuration, the transaction is only linked or our solution can change the date, exchange rate, amount and the cheque number based on the values from the bank statement line. It is also possible to setup matching preferences to match with closed transactions upon import of the bank statement.
Bank Account Configuration - Setup to Consider
There are some considerations to be made when setting up the Bank Account Configuration regarding the closed transactions.
1. Only link the transaction or adjust the transaction data?
The most important decision regarding the closed transaction reconciliation is the following setting on the each Bank Account Configuration:
There are 3 options here:
- Only link the transaction to the statement, which only adds the link to the bank statement, but does not change anything on the transaction.
- Link the transaction and change the date to the date on the statement - because the date is change, this option automatically triggers change of exchange rate respective for the new date, amount and the cheque number.
- Link the transaction and change only the GL account if an IN TRANSIT ACCOUNT is configured - this selection will only initiate a GL account change, without changing the transaction date.
2. Payment-in-transit or Undeposited funds account in use?
If you work with payments in transit, our solution can confirm these payments in transit, when they are selected on the popup or matched automatically via the matching preferences. Similar for transactions which reside on the Undeposited Funds account, these transactions will get deposited on the Bank Account from the statement they are reconciled from. In order for The Bank Reconciliation SuiteApp to consider these transactions for reconciliation, one must set the Payment-in-transit account or Undeposited Funds account on the Bank Account Configuration as seen below:
3. Matching Preferences
Users may set on the Bank Account Configuration that the closed transactions will be considered for automatic matching upon importing the statement. The preferences are the same as for open transactions:
So one may set the matching based on document numbers or parameters, on exact match of amount within date range or based on the entity name (counter party) if the counter party is filled in on your bank statements.
Most Used Cases
Please see the three most used cases when Closed Transactions Reconciliation is utilized:
1. Reconciling transactions from payment batch
When user creates a Payment Batch via Electronic Payments module, based on the payment file format used, the bank displays the amount paid either as a total sum amount or as individual payments to each entity. In the first case, the total sums needs to be reconciled using the Alt match type and selecting the payment batch as described on the dedicated article. The payment batch itself is not a transaction by definition, therefore the Alternate matching option. If the bank statement contains the individual lines, these are vendor payment transactions that can be reconciled as mentioned in the article. It is also simple to setup automatic matching preference to Match Closed on Document Number/Parameters as the vendor payments document number follow the pattern: payment batch ID / XX, e.g. 380/01, 380/02. In that case all that needs to be done is to check the matching preference and remove the “/” sign from the Additional delimiters.
2. Workaround for special cases
Currently, if the user wants to create a payment and selects more than 30 open transactions (e.g. more than 30 invoices) then the warning is displayed that it is not possible to select combination of more than 30 transactions. In that case, user should simply open the Entity record in Netsuite and create the payment manually in the UI. For Vendor payment that means using the “Make payment” button, for accepting a payment from certain customer that means utilising the so-called button:
This workaround can be utilised also in the case that reconciling the open transactions does not yield the desired result. After the payment is created, follow the steps mentioned above (section supported types & how to reconcile).
3. Undeposited cash sales
Cash Sales transactions can be recorded on the Undeposited Funds GL account and later transferred to the actual bank account. In that case, set up the Undeposited Funds GL account as a Payment-In-Transit account (section Payment-in-transit or Undeposited funds account in use? above) on the Bank Account Configuration so when the cash sales are reconciled via a statement, our solution can automatically change the Undeposited Funds account to the actual bank account from the statement and deposit this way the amount as desired.