NetSuite does not have a direct connection to your bank or credit card company and it's time consuming to manually enter the transactions in NetSuite.
For most companies there are going to be entries in a bank statement that don't yet exist in NetSuite. This could be miscellaneous service charges, or, depending on your business model, a large number of important business transactions that need to be entered into NetSuite before you can reconcile your statement.
With CloudExtend Excel for NetSuite you can export your bank statement to Excel and filter out all the rows except those that you know do not yet exist in NetSuite. Now you can load this data into a Journal Entry template in CloudExtend and either create one Journal Entry encompassing all of the rows or 1 Journal entry for each row. Pro-tip: ensure that at least one header level field such as date or memo is unique in order to create multiple Journal Entries at once.
Credit Card Statements
The video above demonstrates how you can download a credit statement into Excel and transform it into a Journal Entry.
- Download the credit card statement
- Copy and paste the amount column to the debit column of your Journal Entry template (no need to worry if the amount is negative, CloudExtend will upload this as a credit).
- Copy/paste the merchant name to the line level memo field of your Journal Entry template
- Insert 1 row at the top and enter your Credit Card Payable Account in the Account field and then enter the sum of the debits column as the corresponding amount. Also, in the entity field of the same row, enter the name of the credit card vendor in NetSuite, for example, American Express.
- For each row, select the correct G/L account (note, CloudExtend provides an easy way to lookup the account and verify it, saving you time).
- When done, click the Update Action Item and you will now have the proper accounting of your credit card transaction
Use macros to speed up the entire process
Excel macros are great for speeding up repetitive tasks (see the 5:00 mark in the video above). The following steps will allow you to further speed up the transformation of your credit card statement into a Journal Entry.
- Create a saved search in NetSuite of all your G/L Expense Accounts. Use CloudExtend to download these into an Account template in a separate worksheet called GLExpense Accounts
- Create a separate tab on your worksheet called 'Merchant Info'. Add Merchant Name, GL Account, and GL InternalID. This sheet will hold all of the merchants from the credit card statements and will populate over time. The GL Account column will be a lookup to your GLExpense Account worksheet. Associate each merchant with a G/L Account name. For the GL InternalID use a vLookup to the GLExpense Account tab to retrieve it. This will be user later in the process.
- Take a deep breath, we're almost done. And remember, this is a one time process. All the time you put into it now will be saved many times over.
- Create (or record) a macro that copies and pastes the Amount and Merchant field from your credit card statement into your Journal Entry template. Use the same macro to insert a formula into the GL Account field on your Journal Entry template. This formula could be a VLookup or Index formula that scans the 'Merchant Info' tab to find the match for the Merchant on your Journal Entry and returns the internalID of the G/L account associate with them surrounded by [[ ]]. Below is a sample of the formula. Your may be different.
="[["&VLOOKUP([@[Line List: Memo]],Table1[#All],3,FALSE)&"]]"
- For each "N/A" value in the G/L Account column you may need to add the merchant to the end of your Merchant Info tab. Future looks ups will then auto resolve.
- Last, enter a row at the top of your table to handle your credit card payable debit and click Update to create your new Journal Entry.
NOTE - once you have gone through the trouble of building your related lookup tables and macro the total time to create this Journal Entry should be less than one minute.