The problem
You need to close your books and it's time-consuming to enter credit card transactions one by one. Leverage Excel formulas and ExtendInsights to make this a 30-second process every month. See it in action in the video below and refer to the steps below for the formulas used.
The video above shows this in more detail. We recommend that you create a re-usable Excel template that has all the data necessary to validate your Journal Entry prior to upload.
Template Layout
💡 Tip: It is best to check out the forms and fields and identify the header, column, and required fields in NetSuite that you may need to build the template.
Custom Form
Tran ID
Tran Date
Credit Card Type (This is a custom field, permission is needed for your NetSuite Role)
Memo
Line List: Account
Line List: Entity (Name in NetSuite)
Line List: Class (Optional)
Line List: Debit
Line List: Credit
Line List: Memo
Configure Excel Workbook
Create a new Excel workbook and save it as an Excel template so it can be re-used by yourself or your users.
💡Note: The formulas below may refer to table names that are different than yours. Adjust accordingly. |
Create your lookup tables as individual worksheets in the Excel template file
Create a worksheet named GL Accounts Download and use ExtendInsights to download a list of your chart of accounts
Create a worksheet named Vendor List Download and use ExtendInsights to download a list of your vendors
Create a worksheet named Lookup Table with the following 5 columns. This will be used to populate the G/L accounts and entity names to assign to each line of your Journal Entry. You'll want to keep the master template up to date with all the merchants that you receive charges for.
Doing Business As - This should be a name that appears on each line of your credit card statement, for example with American Express it is typically called Doing Business As.
Associated G/L account - This is the name of the G/L account that you would typically assign to this merchant. It should match the name of the account from your GL Accounts Download.
GL Internal ID - This is a formula field that will append the NetSuite internal ID to your account name. When ExtendInsights sees
[[ ]]it ignores everything but the values in[[ ]]and treats those values as the NetSuite internal ID. This allows for faster uploads.
=[@[Associated G/L Account]]&" "&"[["&XLOOKUP([@[Associated G/L Account]],GLAccounts[[#All],[Acct Name]],GLAccounts[[#All],[Internal Id]],"No Match")&"]]"Vendor Name - For those transactions that you want to track by entity you should enter the name of the vendor to associate them within NetSuite. It should match the name of the account from your Vendor List Download.
Vendor Internal ID - Like (3) this is a formula field to look up the NetSuite internal ID.
=IFERROR([@[Vendor Name]]&" "&"[["&XLOOKUP([@[Vendor Name]],Table5[[#All],[Company Name]],Table5[[#All],[Internal Id]],)&"]]","")
Add three new worksheets. We recommend naming them with numbers to help guide your users.
Step 1: Download Credit Card Statement
Download CC Stmt - this will be where you copy your credit card statement details that were previously downloaded to
Step 2: Paste the data from the credit card statement
Paste CC Stmt Here - this will be where you copy the lines from your credit card statement that you wish to upload
Step 3: Review ExtendInsights Journal Entry Template
This will contain formulas that will perform the lookups from the downloaded credit card statement, this is your ExtendInsights template.
Now, load your ExtendInsights template to the worksheet named
3. Review Journal Entryand use formulas to refer back to the2. Paste CC Stmt Hereworksheet. These formulas will populate your ExtendInsights template with the desired data from your credit card statement.
In order to upload as a single Journal entry the date must be the same on every row. You'll probably want to manually enter the date for your Journal entry, however, consider the formula below which will return the max date from all the transactions.
=IF(MAX('2. Paste CC Stmt Here'!$A$8:$A$62)=0,"",MAX('2. Paste CC Stmt Here'!$A$8:$A$62))This is optional, however, we recommend creating a custom transaction body (list) field called Credit Card Type and applying it to Journal Entries. This will allow you to generate reports based on the type of credit card used.
For the 1st row, you would manually enter the account used for credit cards payable. Then, for subsequent rows, you will enter a formula that looks up the merchant and assigns the proper account.
=XLOOKUP([@[Line List: Memo]],Table3[[#All],[Doing Business As]],Table3[[#All],[GL InternalID]])For the first row, you would add the credit card vendor manually. Doing so will allow you to later use the write check functionality in NetSuite to make a payment to the credit card company as well as track the transactions. Then, for subsequent rows, use the formula below that looks up the entity in NetSuite to assign the line-level transaction to.
=XLOOKUP([@[Line List: Memo]],Table3[[#All],[Doing Business As]],Table3[[#All],[Vendor InternalID]])Leave the debit empty in row 1 and in all subsequent rows use a formula that looks up the charge from the credit card company.
='2. Paste CC Stmt Here'!E8For the Line List Credit field you only need a formula in row 1 which will sum all of the debits for you to help ensure that your entry is in balance.
=SUM(Table4[[#All],[Line List: Debit]])For the line list memo field on row 1 manually enter the desired text such as "Amex Bill". Then, in subsequent rows use a formula that looks up the name of the payee on each line of the credit card statement.
='2.Paste CC Stmt Here'!G8
Now, save your Excel file as an Excel template
Step 4: Add missing Vendors to the Look-Up Table
If you have any missing vendors, make sure to add this to the vendor list download and associate it with the proper Chart of Account. Download the company name with the internal ID associated with it. This will be used in the Journal Entry upload.
Step 5: (Optional) Upload missing vendors to NetSuite (if desired)
You can create a ExtendInsights Vendor Upload template. With the following layout:
Company Name
Entity ID (automatically generated by NetSuite)
Is Person (TRUE or FALSE)
Step 6: Upload the New Journal Entry
Click Upload to NetSuite then hit the Upload Records button.
💡 Tip! Header values are repeated to let the system know this is one record upload. Different header values will create separate records.