How to import open AR transactions into NetSuite

Introduction

Importing open accounts receivable (AR) transactions during a NetSuite implementation is required to begin receiving payments for outstanding invoices in NetSuite. This post will discuss preparing, loading, and validating your open balances from your legacy detailed AR aging report.  

You should complete these items before starting:

1. Loading your historical monthly financial statements - This article will show you how to load monthly financial statements if you aren't sure where to start.

2. Clean the AR aging report - Here are a few items to look for:

1. Apply any customer payments, credits, and journal entries against open invoices. Journal entries are often involved with contra-AR balances.

2. Add the entity to any journal entries missing a name. If the entries net to zero and impact multiple vendors, exclude the journals from the import altogether. Otherwise, you will have a 'No Customer' line on the AP aging that users cannot remove.

3. Create a suspense account - This account will offset the GL impact of the open AR transactions in NetSuite. Name this account OPENAR and configure it as an equity account type.

4. Create a For Sale, Non-inventory Item - invoice and customer credits require using an item. Set this up as a for-sale, non-inventory item configured to impact the OPENAR GL account discussed in bullet three above.

Let's get started!

Why do we use a suspense account?

We imported our monthly financial statements with a journal entry. This journal entry recorded the original general ledger (GL) impact for all transactions posted in the legacy system, including the open AR transactions. After completing this step, your legacy system and NetSuite financial statements should match.

However, this step excludes the details required to receive payment for our unpaid invoices. We still need to create these invoices. NetSuite automatically sets the debit as AR when creating an invoice, and the user defines the credit account. But, these invoices will have a GL impact. And that impact will create a variance in our financial statement tieout.

The solution is to use a suspense account to identify the GL impact of the open transactions. This will prevent us from double-counting our revenue balances. Don't worry. The suspense account will have a zero-dollar balance at the end of this process. Keep reading to find out how.

How to import open AR transactions

1. Prepare and format the report from the legacy system.

First, navigate to the AR detailed aging report. In QuickBooks Online, the out-of-the-box list should include all the needed data. If you have customers with non-USD currencies, add the currency and open balance foreign currency columns to the report.

Export the report to Excel. Remove the headers, footers, subtotal and total lines. Feel free to remove any blank columns. Finally, remove the amount column because we are loading the outstanding balances. Check out the video below for a tutorial on how to do this in QuickBooks Online.

Last, validate that the total open balance in the Excel spreadsheet matches the AR balance in NetSuite at the go-live date.

2. Map the legacy system values to NetSuite

Next, we must map the legacy system values to the new NetSuite values. Users will need to:

• Map transaction types - Transactions impacting AR are just about a 1:1 match with NetSuite. Load invoices as invoices, payments as payments, and credits as customer credits. You cannot import deposit transactions into NetSuite. Either enter these manually or load the deposits as journal entries. Remember, each NetSuite transaction type will need a separate CSV upload template.

• Map the customers - The customers with an existing balance should exist in QuickBooks and NetSuite. See my article on the segment map file for more details.

• Map the account - The open AR transactions should only involve two general ledger accounts, the OPENAR suspense account, and the AR account. You must use the OPEANAR item record instead of the GL account for the invoice and customer credit uploads.

I recommend mapping the NetSuite values based on the internal ID instead of the name. The internal ID is easier to track. Also, there is a lower likelihood of a typo. Check out bullet one in this article for instructions on adding internal IDs to all lists in NetSuite. You can also find this on the URL of any record in NetSuite:

3. Prepare and load the CSV upload files by transaction type

Once you have added the NetSuite segment values to your file, you are ready to prepare the CSV upload files. Remember, you must prepare a separate file for each transaction type. To navigate to the upload page, go to Setup -> Import/Export -> Import CSV records.

Here are a few tips when working with CSV upload templates:

• Your implementation partner should have the transaction type upload templates. You can also download my guide here.

• I recommend loading a single transaction first to validate the upload worked. Even if the transaction loads successfully, make sure the transaction isn't stuck in an approval workflow.

• If you assign values based on the internal ID, don't forget to adjust the upload file on the Field Mapping page of the upload process.

4. Validate that the customer balances are correct

After successfully uploading all the transactions, run the AR summary aging report from NetSuite and the legacy system. Compare the balances using a SUMIF formula. Expect to see a variance in NetSuite for the fictitious AR customer. We will correct that in the next step.

5. Reverse the suspense account balance

The final step is to reverse the AR balance and OPENAR suspense balance with a journal entry. Again, include the fictitious AR customer name on the AR side. See an example journal entry below. After posting this entry, the AR balance should be correct, and the OPENAR balance should be zero.

6. Deactivate the appropriate records

Once completed, you can deactivate the fictitious customer account, the OPENAR item, and the OPENAR GL account. Before deactivating the plug customer account, navigate to the Accept Customer Payment page, select the fictitious customer, and net the journal entries against each other. This action should result in a zero-dollar transaction with no GL impact.

Final reminders for loading your open AR transactions

Here are a few final tips & tricks to get your transactions loaded correctly:

• Set the posting period to the current period on the uploaded files. This trick will ensure that the GL impact of the OPENAR account impacts just one accounting period.

• If the OPENAR account is not zero, something is off. Ensure you didn't use the amount column in the QuickBooks file by accident. Always use the outstanding balance amount.

• Train your staff not to change the OPENAR account.

• Your NetSuite implementation partner should be able to offer guidance on how to complete this task for you. There are also several other resources available.

Closing

This article discussed how loading your open AR transactions is essential to start applying customer payments in NetSuite on day 1. Check out our other helpful articles on loading open accounts payable transactions and open bank transactions. Finally, our implementation resource page includes other tips & tricks for preparing for and successfully executing your NetSuite implementation.