Bank Reconciliation: Handling Missing Transactions

Some NetSuite users are unaware of an automation improvement that NetSuite launched with their 2017.2 release. With that release, NetSuite introduced a Transaction Matching Engine that matches and reconciles bank and credit card statements without having to manually select each transaction from your bank statement.

The Matching Transaction Engine lets you export an online bank statement or credit card statement from your bank and import it into NetSuite using the “Import Online Bank Statement” feature. And, once you’ve imported the file, it automatically identifies all of the matching transactions.

NetSuite’s Matching Transaction Engine lets you create your own custom matching rules, such as matching a transaction or check number or matching a date and amount. And, from the NetSuite reconciliation window, you can see all of the matches and confirm that they are accurate. This lets you identify and focus on exceptions such as missing transactions. Unfortunately, NetSuite’s Matching Transaction Engine does not automate missing transactions.

Automating Missing Transactions

A common exception that you may experience at the end of the billing period is miscellaneous service fees, such as bank fees. Since you don’t always know what the bank fees will be in advance, you may not be able to record them in NetSuite until you get your final statement. Or you may use your credit card statements to create expense journal entries such as T&E expenses.

Although NetSuite’s matching transaction engine lets you create a Journal Entry for the missing transaction from within NetSuite, missing transactions still require manual work. And, even though creating a Journal Entry is as simple as clicking on a button, it can become an overwhelming task if you’re dealing with large volumes of missing transactions.

Your ideal solution would automate the creation of missing transactions and let you build reusable rules, so it would only take a minute, maybe two, to create the Journal Entries, regardless of the number you need to create.

Celigo CloudExtend Excel for NetSuite

Celigo CloudExtend Excel for NetSuite is designed to solve the problem of how to create missing transactions in NetSuite when you’re trying to reconcile your bank statements.

Missing transactions often include miscellaneous service charges or, depending on your business, they could involve a large number of important business transactions that must be entered into NetSuite before you can reconcile your statement.

With CloudExtend Excel for NetSuite, you can export bank and credit card statements to Excel. You can then use the power of Excel to filter out all rows except those that do not yet exist in NetSuite. Then you simply load this data into the Journal Entry template in CloudExtend and tell it to either create one Journal Entry for all of the rows or one Journal Entry for each row.

Using Excel macros is a great way to speed up repetitive tasks, save time and decrease errors. Once you’ve taken the time to build your lookup tables and macros, they can be used over and over again, reducing the time it takes to create Journal Entries. For more information, check out our video on “How to import credit card or bank statements into NetSuite as Journal Entries.“

Common Use Cases for Celigo CloudExtend Excel for NetSuite

• Converting credit card statement details into NetSuite transactions for recording expenses
• Any NetSuite transactions that you’re not recording on a regular basis – such as bank transfers
• Payroll entry – files from your payroll provider

The advantages include:

• Instant data validation. No need to wait for a file to fail to find out if there are incorrect fields or if names don’t match
• Set up formulas or tables that will start populating your template
• Write macros to streamline repetitive tasks
• Auto-fill: like in NetSuite, if you start typing, it’ll look for matches
• Saved Searches can be automatically dropped into your template

Learn how to manage your NetSuite data securely and seamlessly in Excel in real-time.

Your Monthly Competitive Advantage

Related Posts