Bank Data Matching and Reconciliation

Bank reconciliation is a Major part of any institution.Let us see how bank reconciliation is done in NetSuite.

Before moving to Match bank data page we have to consider the two notes shown below regarding the permission for the user and Account setup.

Note 1: To use the Match Bank Data and Reconcile Account Statement pages, you must have the proper permissions assigned to your role.

Financial Institution record permission: –

NetSuite contains financial institution records, which represent banks and credit card companies, or corporate card companies, from which you want to retrieve financial institution data.
Financial institution records contain format profiles, which let you configure settings necessary for manual or automatic data import. You can set up a format profile to import data for use in the bank reconciliation process or for use in employee expense reports.

Note 2: If you want to use the pages for an existing account, check the Use Match Bank Data and Reconcile Account Statement Pages box on the Edit Account page. This box is automatically checked for any new accounts.

Editing Accounts to Use the Match Bank Data and Reconcile Account Statement Pages

The Reconcile Bank Statement and Reconcile Credit Card Statement pages are still functional but are no longer supported.

If your accounts still use these pages, you should edit your accounts to use the Match Bank Data and Reconcile Account Statement pages. When you migrate a held account statement to these pages, NetSuite automatically clears and moves all held transactions to the Reconcile Account Statement page.

A held statement is one you have begun to reconcile but have chosen to complete later.

To edit accounts to use the Match Bank Data and Reconcile Account Statement pages:

Go to Setup > Accounting > Manage G/L > Chart of Accounts.

Click the Edit link to the left of the account you want to modify.

The Account page appears.

Check the Use Match Bank Data and Reconcile Account Statement Pages box.

If this account contains a held statement that you have begun to reconcile but have chosen to complete later, a confirmation message appears. NetSuite gives you two options:

If you want NetSuite to delete all held statements, click OK.

NetSuite checks the box. After you save, NetSuite moves any cleared transactions to the Review and Reconcile subtabs on the Reconcile Account Statement page. These transactions are cleared and ready for reconciliation. In the Review subtab, these transactions have a Cleared label. In the Reconcile subtab, you can check the box next to each transaction and then reconcile the statement.

If you want to reconcile all held statements for the account first before migrating, click Cancel.

The box remains cleared. This account is still only available in the Account list of the Reconcile Bank Statement and Reconcile Credit Card Statement pages.

If this account contains matched or user-cleared transactions pending submission on the Match Bank Data page, you cannot clear the box until you do one of the following:

Submit all transactions for the account.
Undo the match or cleared status of each transaction for the account.

If you modified your account record, click Save.

To match transactions on the Match Bank Data page, Go to Match Bank Data Page.

Bank Data Matching and Reconciliation

When bank data is imported into NetSuite either by Manual Import or Automatic Import, the Intelligent Transaction Matching feature runs reconciliation rules to automatically match imported lines to corresponding transactions. You can manually match exceptions and reconcile transactions, without requiring spreadsheets or third-party tools.

The reconciliation Rules

NetSuite includes system transaction matching rules to automatically match imported bank lines with existing account transactions in NetSuite. These default matching rules run on all transactions. You can view these rules in the System Rules list (previously named Default Rules) on the Reconciliation Rules page (previously named Reconciliation Matching Rules).

Go to Transactions > Bank > Reconciliation Rules.

The Reconciliation Rules page (previously named Reconciliation Matching Rules) displays two sections: System Rules and User Rules. The system rules are in a list that you cannot reorder, but you can inactivate them.

System Rules – The default matching rules in NetSuite that you cannot modify or remove. Default rules automatically match imported bank lines with existing account transactions.
User Rules – Custom rules are that you can create, edit, and assign the order in which they are run. User rules are listed in the User Rules list on the Reconciliation Rules page, and they include the following:

Matching rules – Automatically match imported bank lines with existing account transactions. Intelligent Transaction Matching identifies a match for an imported bank line if an account transaction in NetSuite meets the criteria you specify in the rule. Matching rules are created and edited on the Reconciliation Rules page. You can apply a matching rule to multiple accounts.
Auto-create rules – Automatically create and match the following transaction types:

Deposits and charges for general ledger bank accounts.
Charges and refunds for general ledger credit card accounts.

Bank Data Import

For matching the Bank data, we require the data in NetSuite for that we have to import it. To import Bank data, we have two options as shown below:

Automatic import – If you have the Administrator role, you can use the Format Profile page to set up daily automatic imports.
Manual import – Download the financial statement file from your financial institution, and then import it into NetSuite from the Upload File page. You can import a file using the parser and configuration settings from an existing format profile. If you are importing data for reconciliation, you can also use NetSuite’s default parser functionality.

1. Automatic Import

You can automate bank data imports by configuring format profiles, which are records that enable you to connect to financial institutions to retrieve financial data. If you have the Administrator role, you can automate bank or credit card data imports for matching and reconciliation. You can use a Financial Institution Connectivity Plug-in to automate bank data imports for bank or credit card reconciliation.

This section involves using a custom or standard connectivity plug-in in a format profile that you create. You can use either the standard SFTP Connectivity plug-in available through the Auto Bank Statement Import (ABSI) Suite App, or develop a custom plug-in.

2. Manual Import

You can download a financial statement file from your financial institution and then manually import it into NetSuite for reconciliation or expense reporting. NetSuite encrypts financial data files during processing. Files are removed and not stored after processing.

Preparing CSV File for Import

Go to Transactions > Bank > Banking Import History > Upload File.

NetSuite displays the Upload File page. The Manual Import subtab is open by default.

Under Import Options, choose Import with default parser (CSV, OFX, QFX, BAI2, or CAMT.053).

You can see download file option. Click on that and you will get the csv template for importing bank data.

To ensure successful import:

The column headings must be in the first row in the specified order as follows: Date (MM/DD/YYYY), Payor/Payee Name, Transaction ID, Transaction Type, Amount, Memo, NS Internal Customer ID, NS Customer Name, Invoice Number(s).

Date (MM/DD/YYYY) – The transaction date. Content must be in the format of MM/DD/YYYY. A one–digit month must be preceded by a zero. If you are using Excel, ensure that the date does not get reformatted.
Payer/Payee Name – Payer or Payee name. There is a limit of 70 characters.

Transaction ID – Unique Bank Transaction or Reference ID. This can also be populated with the check number. There is a limit of 90 characters.
Transaction Type – Values in this column must be one of the following transaction types, all uppercase: CREDIT, DEBIT, CHECK, PAYMENT, ACH, INTEREST, DEPOSIT, TRANSFER, FEE, OTHER.
Amount – The amount column must be a single column. If your file contains CREDIT and DEBIT columns, combine them into a single amount column. Positive amounts are CREDITS. Negative amounts are DEBITS, and must begin with a minus sign or be in parentheses.

The amount column must not contain currency symbols or commas instead of decimal places. The amount currency should be in the same currency as the selected account.

Memo (Optional) – There is a limit of 4000 characters for the memo. The Memo field must not include any commas, semicolons or underscores.

NS Internal Customer ID (Optional) – The NetSuite internal customer ID. Content must be numeric. There is a limit of 12 characters.

When you import the statement, NetSuite uses the customer ID first to try to match the statement to a NetSuite customer. If the customer ID is missing, NetSuite checks for a customer name next.

NS Customer Name (Optional) – If the specified customer ID does not match any data, NetSuite uses the value to look up the customer ID. Content is free-form text. The character limit is 512.

When you import the statement, if the customer ID is missing, NetSuite uses the customer name to try to match the statement to a NetSuite customer. If the customer’s name is missing, NetSuite checks for invoice numbers.

Invoice Number(s) (Optional) – A comma-separated list of invoice numbers in a single field, with no spaces between numbers (for example, 132,133,134). If you use a script or comma-separated file instead of the CSV template in a spreadsheet, put quotation marks around multiple invoice numbers (for example, “132,133,134”). For each invoice number in the list, there is a limit of 128 characters. If the imported file specifies multiple invoices for a transaction, they must all belong to same customer and AR account.

If the following details are present remove it before importing:

Empty columns (containing no data) between other filled columns.
Empty rows (containing no data) between other filled rows.
Columns containing opening and closing balances.
Any columns not specified by the template.

Multiple accounts.
Bank account number.

Importing Data Using Default Parsers

You can import bank data into NetSuite with BAI2, CAMT, and CSV files. If your financial institution provides banking data in another format, use a third-party conversion tool to convert the data file to a supported format and then import.

To import a statement using default parsers:

Go to your financial institution website and download your bank or credit card statement in one of the accepted file formats (Preferably CSV).

If you are importing a CSV file, the file must adhere to the provided CSV template and constraints.

Give the statement file a name. The limit for the file name length is 50 characters, which includes the extension.

Go to Transactions > Bank > Banking Import History > Upload File.

NetSuite displays the Upload File page. The Manual Import subtab is open by default.

Click Choose File, or use the drag-and-drop functionality.

Select the name of the file you want to import.

Under Import Options, choose Import with default parser (CSV, OFX, QFX, BAI2, or CAMT.053).

In the Account field, select the bank or credit card account for this import.

If required, in the Character Encoding field, select the method of character encoding associated with the file.

Click Import.

NetSuite displays a progress message. To view the processing status of your import, click Track your status. When the import process completes, you can perform matching and reconciliation.

You can see the reason for failure by clicking of addition sign of each import.

Note: To ensure successful file import, do not modify the downloaded statement file.

Only Successful import can be deleted:

Matching Bank Data

After Setting up the preferences for users Role, setting the account, and importing the Bank data File we can enter into the second major process in Bank reconciliation, Matching Data.

If you have account statements but importing them is difficult or not possible, you can mark corresponding account transactions in NetSuite as cleared without importing the matching bank lines.

On the Match Bank Data page, you can view side-by-side grids of imported bank lines and account transactions in NetSuite. You can do the following:

Match account transactions.
For transactions with no matching bank line, mark them as cleared pending submission.
Exclude imported bank lines.
Review matched and user-cleared account transactions.
Submit transactions to clear them in the system.

After you finish manually matching transactions or marking transactions as cleared, submit them. Go to the Reconcile Account Statement page to review the submitted transactions, include or exclude transactions from the reconciliation, and then reconcile the statement.

Navigation for Match Bank Data:
Transaction>Bank>Match Bank Data

Select the Bank account

In Review Subtab you can view the data that are already matched automatically or Manually:

In Excluded subtab you can view any transaction that is excluded by the user or by Netsuite(System)

If you wanted to exclude you can select the transaction and click on the exclude but an then it appears in exclude subtab as excluded by user and you and restore it when you requires it.

To restore:

In To Be Matched Subtab we can see the transaction that are to be matched.

We can see Add Deposit, add charge and add Journal options. You can use this option in necessary cases like:

Add deposit, if there is an interest income:

After making a deposit we can see the detail in Account transaction side check it and match it

In case of Charge, you create a check.

Journal can be used for other transactions such adding bank charges etc:

Click on add journal after checking the transaction line. Create the Journal and once created click on refresh button on the Account transaction section (marked in blue).

Then check mark the journal and once the difference is zero you can click on Match