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? These include credit card fees, automatic payments miscellaneous service charges. Depending on your business model, they could also be a large number of important business transactions. What if there was a better way to manage transactional data?
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 the solution of choice simply because it’s what they started with. As companies grow the volume of transactions grows. 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.
Automation of Credit Card Transactions
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.
Excel for Managing Transactional Data
Unlike most ERP systems, Excel provides a fast and easy way to edit multiple rows and columns of data. From drag and drop formulas to Index and Vlookup, there is something to help everyone work. It happens 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. It can 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. Then they can 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). Once that takes place they can be uploaded together in one batch. In NetSuite, this batch will be broken into separate transactions. The end result is that the accountant performing the bank rec will now have perfect data to work with. The number of mismatches will be so small that they might even get a game of golf in after work.
Credit Card Transactions
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 month’s 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). 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 management of transactional data
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
- Outlook integration
- Microsoft Excel <-> NetSuite integration
- Microsoft Excel <-> Salesforce integration