The majority of transactions in a bank statement typically have a matching transaction in an ERP system such as NetSuite. When a deposit is made in the ERP it will match up with a line on the bank statement. Similarly, for every check issued in the ERP there will be a matching transaction on the bank statement. But what about all those orphaned entries in a bank such as credit card fees, automatic payments miscellaneous service charges, or, depending on your business model, a large number of important business transactions?
Likewise companies often have multiple credit cards. Unlike expense accounts where employees need to enter an expense report and get reimbursed, corporate credit cards rarely have offsetting transactions in the corresponding ERP system. These transactions all need to be accounted for.
Manual Data Entry
For most companies, manual data entry has been solution of choice simply because it’s what they started with. As companies grow the volume of transactions grow and then headcount grows in order to manually input transactional data. There’s always a tipping point where companies start to look for a better solution.
Some ERP systems connect directly to banks and credit card companies and data is imported in batches daily. Often times, however, the data just sits in a G/L holding account and each transaction needs to be touched by a human again. Still, some automation is better than manual data entry.
Unlike most ERP systems, Excel provides a fast and easy way to edit multiple rows and columns of data. From drag’n drop formulas to Index and Vlookup, there is something to help everyone work at a rate of speed that is impossible to achieve in the web UI used by most ERP systems. Some Excel applications such as CloudExtend Excel for NetSuite can pre-validate data and then update NetSuite records directly from Excel.
In order to prepare for a successful (success = matching all transactions on both sides) bank reconciliation a user could export their bank statement to Excel and then add a filter to filter out all the data already in the ERP. These are typically coded as Checks or Deposits. With CloudExtend Excel for NetSuite the remaining transactions can easily be associated with the correct G/L account (hint-leverage Excel formulas such as INDEX or VLookup) and then uploaded together in one batch. In NetSuite this batch will be broken into separate transactions. The end results is that the accountant performing the bank rec will now have perfect data to work with and the number of mismatches will be so small that they might even get a game of golf in after work.
Like orphaned bank transactions most credit card transactions do not yet exist in the ERP. Some ERP systems even have a record type to record these transactions but in the end each transaction is simply a journal entry with a credit to one account and a debit to another. Knowing this it makes sense to bypass the transaction data and move right to a journal entry. Export your credit card statement to Excel (most every credit card company offers this option). Once in Excel you have a powerful number of tools available to you. Combine these with CloudExtend Excel for NetSuite and you can import a months worth of credit card transactions in less than 5 minutes (and yes, get that game of golf in). Keep a running a list of all the merchants and their associated G/L accounts (Sunoco = Auto Expense : Fuel) and then use a macro to get the data into a CloudExtend Journal Entry. Choose to have one Journal Entry created for each transaction or import the entire credit card statement as one Journal Entry.
Don’t settle for manual
Manual data entry is a complete and utter waste of time fraught with risk (like adding an extra 0). Find a tried and trusted solution to work faster and more accurately. Whether it’s a direct connection to your bank or a solution such as CloudExtend, you owe it to yourself to work faster and smarter.
CloudExtend also integrates NetSuite with Google G Suite, Microsoft Outlook, and Microsoft Excel. We also integrate Microsoft Excel with Salesforce.
- Google G Suite integration
- Microsoft Outlook integration
- Microsoft Excel <-> NetSuite integration
- Microsoft Excel <-> Salesforce integration