Solving NetSuite Bank Reconciliation Differences

This article is relevant if you use NetSuite’s Bank or Account Reconciliation feature and find the differences in amounts to resolve.

Background

NetSuite provides two distinct approaches to bank reconciliation. Initially, it offered a traditional tool for matching transactions with bank statements against “Bank” type accounts in the NetSuite general ledger. Later in the 2010s, NetSuite expanded its capabilities with a more comprehensive Account Type reconciliation tool to facilitate a wider range of general ledger reconciliations. This newer tool was set to eventually replace the original bank reconciliation feature.

Even 10 years ago, in 2014, I discussed some of the challenges of bank reconciliations in my article, “Solving NetSuite Bank Reconciliations Differences to Balance Sheet”.    The fundamentals of that article are still sound.

Both tools incorporate the functionality to designate transactions as either “Cleared” or “Reconciled”.  This flagging distinction allows users to preliminarily mark transactions as cleared based on some type of external verification (reviewing bank statements), facilitating pre-reconciliation activities.   Clearing transactions is particularly useful for managing large transaction volumes and ensuring efficient reconciliation processes.  Most of the time, we expect to perform reconciliations at the end of each accounting period to ensure we maintain accurate financial records.

The reconciliation features in NetSuite are supported by specific data elements like dates and ending balances, which are crucial for the reconciliation process but are only accessible through these specific tools, not the broader SuiteScript or NetSuite platform.    When we do not have the platform to innovate, we need to do our best to drive value from the provided tools.

NetSuite also offers a reconciliation report that organizes data by statement date and account, providing a detailed view of the reconciliation status.  The report helps us quickly see if there are any differences in values (click the image to see an example).  It’s important to understand that you may have reconciled a statement, and something later can happen to throw it off.  The report is dynamically created and thus shows the current status of the historical reports.  Therefore, discrepancies in historical reports may indicate subsequent transactional adjustments need to be addressed.

Addressing Discrepancies in NetSuite Account Reconciliation

Refer to the provided visual example to help understand what an Account Reconciliation Difference in NetSuite might look like. Essentially, such a discrepancy signals potential errors in transaction records that need rectification. The task ahead involves delving into the discrepancy, identifying the underlying issue, and resolving it to ensure accuracy in your financial records.

Enabling NetSuite’s Original Bank Reconciliation for Resolving Discrepancies

When addressing reconciliation discrepancies, a frustrating hurdle is the newer Account Reconciliation feature’s tendency to exclude certain transactions that may require correction.   You might find these problematic transactions in a NetSuite general ledger register or activity report, yet the current tool may not permit their inclusion for resolution.  I suspect many newer users on the platform will be perplexed.  I very well remember my frustration and my elation when I activated the legacy reconciliation tool.

To navigate the issue of working with excluded transactions and regain comprehensive control, follow these steps:

1. Disable the Account Reconciliation Feature: Access the legacy bank reconciliation tool by deactivating the newer Account Statement Matching Tools. This involves editing the settings on the general ledger account definition screen and deselecting the “Use Match Bank Data and Reconcile Account Statement Pages” option. A visual guide for this setting can be found by clicking on the provided image.  The legacy tool, when activated, warns you that it has been deprecated since 2021.1.  My hope is that the legacy tool will never be removed because we need it to solve some reconciliation challenges.
2. Access Historical Bank Reconciliation Data: input the statement date to retrieve the bank reconciliation report that highlights the difference/discrepancy. When prompted for the statement’s end date, enter the date of the reconciliation in question. The system will load the reconciliation and display all the previously cleared transactions.
3. Execute the Reconciliation: Upon revisiting the reconciliation, you may notice numerous uncleared transactions due to recent activities. The legacy tool’s advantage is its transparency, showing all transactions, which is crucial for rectification efforts. Locate or create the necessary transaction and mark it as cleared. Methodically addressing each reconciliation report may be necessary to untangle subsequent discrepancies. The goal is to reduce the unreconciled differences to zero before saving your adjustments.
4. Re-enable the Account Reconciliation Feature (Optional): After resolving the reconciliation issues, you might consider reactivating the newer Account Reconciliation feature. While there’s no immediate drawback to leaving this feature disabled, re-enabling it can facilitate the downloading of financial data and assist in future reconciliations.

Accessing Historical Reconciliation Reports

It might seem unusual, but a direct menu option for reviewing past account reconciliations isn’t readily apparent.  I often find it frustrating when I swear I have seen a helpful report, but I simply can not find it.   There is a specific reconciliation report that shows if there are differences over an extensive data range.  When the Legacy Bank Account Reconciliation system is activated, you will find a History button to drive the report (see associated image).  In the standard Account Reconciliation Feature, the option is also available but may be buried in an ellipse menu (see related image)

In any case, you can navigate to historical reconciliation reports by utilizing a specific URL tailored for this purpose.  Here is the basic URL with some explanations on how to use it.

https://<netsuite account id>.app.netsuite.com/app/reporting/reportrunner.nl?cr=-143&critSpec0=reconbalhist,account,nkey,x,x,IN&fieldVals0=<GL account internal id>

To access a specific reconciliation report, two key pieces of information must be adjusted in the URL:

1. NetSuite Account ID: Inspect the URL on any NetSuite page to find a numerical value, such as 849900. This number represents your unique NetSuite account ID, which should replace `<netsuite account id>` in the URL above.
2. General Ledger (GL) Account Internal ID: While editing a general ledger account, you’ll notice an ID in the URL. This ID is essential for specifying the general ledger account in the report and should replace `<GL account internal id>`.

Alternatively, if you prefer to view reconciliation history for all accounts without specifying a GL account, use the following URL template, substituting `<netsuite account id>` with your actual NetSuite account ID:

https://<netsuite account id>.app.netsuite.com/app/reporting/reportrunner.nl?cr=-143

This approach simplifies access by bypassing the need for a GL account internal ID, providing a comprehensive view of all the previously reconciled accounts and statements. Note: I would like to thank Jenni M., an analyst in our Accounting Practice, for helping me find the menu options for these historical reports.

Optimizing NetSuite Account Reconciliation for High-Transaction Volumes

Note, in early 2108, I offered a number of tips to work with the new account reconciliation feature.   Tips aside, as we work with our most ambitious clients, it is crucial to adapt bookkeeping practices as the transaction volumes grow. A widely recommended approach is to increase the frequency of reconciliation efforts, transforming a potentially onerous task into a more manageable routine.  This strategy not only eases the detail-oriented nature of reconciliation but also aligns with the common practice of reconciling accounts before finalizing the general ledger and releasing financial statements.  Shifting the reconciliation efforts from monthly to weekly or even daily can significantly improve manageability and accuracy.  Importantly, this frequency shifting is valuable for helping reduce the number of days to close the books — a common goal we find in well-run accounting departments.

Exploring alternative reconciliation methodologies is advisable for businesses experiencing exceptionally high transaction volumes, particularly those with robust e-commerce integrations. One such approach is the account clearing model, designed to categorize similar transactions more effectively, mirroring how external processors organize records.  Please see my 2018 article, “Best Practice: Account Clearing Method for NetSuite Electronic Payment Method Reconciliation to understand the approach.  This model can offer a more streamlined reconciliation process by simplifying the identification and handling of transactions.

If you found this article relevant, feel free to sign up for notifications to new articles as I post them.  If you are having recurring challenges reconciling NetSuite accounts, let’s have a conversation.