This article is relevant if you have customer payment information in Excel and you want to apply cash to NetSuite accounts receivable.

Background

In a recent conversation with a client who was wondering about different methods to apply cash in NetSuite, I discussed how my firm developed a tool that helps data entry clerks apply cash directly from Excel.

I recognized that my 2015 article on this topic, Use Excel to Update NetSuite without CSV, ODBC or Web Services, did not help quickly illustrate the tool’s capacities.  Thus, this supporting article helps listeners learn how this license-free tool works.

Cash Receipts and Lockbox Information

The lockbox concept is an industry practice to trust a third party to receive cash on your behalf and act with it in a responsible manner. Thus, it is common for organizations that have a large number of terms-based invoices to outsource their cash receipt operations to others for efficiency reasons. In this lockbox operation, organizations may work with a bank or other third party to process incoming cash receipts from customers. The cash information is collected, deposited into the bank, and summarized into an electronic data file. The data file represents collection efforts and the daily bank deposit.

In this information collected summary, the following data elements may be supplied:

1. Amount Collected
2. Customer [account name also known as entityid]
3. Invoice Number
4. Collection Date

Information in this pattern, where the invoice number is supplied, suggests that the amounts collected pertain to a single invoice. Thus, we can see a one-to-one (1:1) relationship between funds and the invoice.

With the data now in hand, the goal becomes how to apply this collected cash to each respective invoice.

Using Excel to Apply Cash in NetSuite

If data is supplied in the manner discussed above, then it is straightforward to directly apply cash from Excel without logging into NetSuite. Here is how it works.

Excel is enhanced with a Macro that understands how to communicate behind the scenes with NetSuite. A special Excel formula, “NSLinkPayment”, is used to reference the cash receipt data elements. When Excel recalculates, the information is sent to NetSuite. If the invoice information matches, the cash is automatically applied. Visually, the result is a hyperlink that, if clicked, will take you right into the payment record created in NetSuite. However, if the information does not match, a message, “Not Found” is offered to inform the user to research the issue for resolution.

This is a simple and easy-to-use application. Click the image to see the pattern.

Alternative to NetSuite CSV Upload Pattern

The power of this approach is that we don’t have to prepare the data to go into a CSV import into NetSuite. That data preparation effort can oftentimes be challenging due to the way data elements must be enhanced to get information to match the invoice information in NetSuite’s database.  Here, we use the most basic elements provided to go right to work.

Get the Netsuite Excel Cash Receipt Tool

Similar to all the work we do in our firm, we provide our tools and applications to our clients license-free. Our goal is to make sure you are up and running right getting the value you expect out of the NetSuite platform.

If you found this article relevant, feel free to sign up for notifications to new articles as I post them. If you would like to tackle your NetSuite cash receipt challenge, and you believe this tool is applicable, let’s have a conversation.

Marty Zigman

Holding all three official certifications, Marty is regarded as the top NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 30 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to set up a conversation.

More Posts - Website - Twitter - Facebook - LinkedIn - YouTube