This article is relevant if you need to clean up and close NetSuite transactions and/or bring the customer outstanding balance to zero.

Background

As a NetSuite Systems Integrator, my firm may be asked to help transition a NetSuite implementation that did not go as well as had hoped. Usually, this means we are asked to assess the overall situation, develop a plan of action to get things right and then help our clients with the remediation efforts.

In a recent client situation, we are were asked to “reset the customer balance situation”.  A study of that request revealed that there were Sales Orders and Return Authorization records that needed to be closed. Furthermore, there were Invoices, Customers Deposits and Credit Memos comprising the customer balance which needed to be set to zero.

In this article, I provide a basic strategy to address this kind of write off or cleanup concern.  It can be used to help you consider a general approach for writing off old balances.

Develop the Customer Balance Clean Up Action Plan

The first thing that should be done is to come up with an understanding of the scope of the problem. In our client’s situation, they wanted to preserve information and they had two major concerns:

1. Customer Balance Should Be Zero: a large number of customer balances should be set to zero.
2. Orders will not be Fulfilled: a large number of Sales Orders and Return Authorizations are not going to be fulfilled.

Since some of the work warranted the use of light scripting, we worked in our client’s recently refreshed sandbox to test our algorithms (discussed below).

Create a Customer Record Clean Up Flag

In our client’s situation, some of the customer balances and order situations were indeed real balances but others were not. So the first thing we did is add a custom field to act as a flag at the Customer level that allowed our client to mark which customer records are in the cleanup scope. Our client performed analysis of information in spreadsheets and we used standard CSV import tools to update the flag.

Produce Summaries of the Situation

With our flag in hand, we developed NetSuite Summary Saved Searches that would help quantify the challenge. In this case, we counted the number of transactions and summarized the nature of the transactions that comprised the customer balance. We could distinguish invoices, credit memos, and customer deposits.

The trick with creating these reports is to learn how to use the general ledger account be your key, use the posting flag, and not to distinguish on the record type. Readers may be interested in this related article Learn How To Craft Better NetSuite Financial Saved Searches.

Close Non-Posting Sales Orders and Return Authorizations

The first set of actions was to close Sales Orders and Return Authorizations. The key to close these transactions is to loop through all the transaction lines and mark the “closed” flag to be on. I have written about this before and provide workflow and script patterns that can help. See article Learn the NetSuite Code Pattern to Create a Custom Workflow to Close a Sales Order.

Close Open Financial Transactions Comprising the Customer Balance

After closing the Sales Orders and Return Authorization records, we could move on to the more challenging financial transactions. The general consideration is that the accounting books are closed and financials were previously expressed.  Since the cleanup is going to have some sort of “Write Off” required, it’s important to understand where that balance is going to land. In our client’s situation, they elected to push the balances into the revenue section of the income statement.

Also, the idea of using a journal entry to clean this up customer balance situation does not really help. The Accounts Receivable aging is focused on financial transactions being applied to each other to distinguish and age.  To get things clean requires working the NetSuite system so it sets the record references right.

Since the balances were both on the Accounts Receivable and Customer Deposit accounts, we focused on Receivables first. The approach is to create a clearing credit memo or invoice depending on the situation. Thus, we created a mass update script that received a customer record as input. Here is the basic algorithm:

1. Distinguish Net Credit from Net Debit Situations: an accounts receivable balance may be comprised of both invoices and credit memos. The first step is to apply all open credit memos to any open invoices. Readers may be interested in this SuiteScript pattern to assist: Get SuiteScript 2.0 to Apply NetSuite Credit Memos to Invoices.
2. Clean up Open Invoices for Net Debits: with credit memos applied in the previous step, if the remaining Customer Accounts Receivable balance is a debit, it should be comprised of all invoices. To clean these up, craft a Credit Memo for the open balance.  Craft an item for the purpose of this clearing work which routes to the right GL account.  In our client’s case, they wanted it to route to Sales Write-Offs on the income statement. Once you have the credit memo, use the same algorithm in the previous step to apply the credit memo to remaining open invoices. At this point, you should have a net debit (negative revenue) on the income statement.
3. Clean up Open Credit Memos for Net Credits: The remaining balances were customers with a negative Accounts Receivable situation.  Just like the work to craft credit memos, you now can create invoices of similar shape for the value of the negative Accounts Receivable. Once again, this new summary invoice can then be targetted for the application of the open credit memos.
4. Clean Up Open Customer Deposits: Closing the sales orders in the earlier step “freed” the linking of the customer deposits that were linked. With the sales order binding now freed, the customer deposit balance can be applied to any invoice. We take a similar approach to the previous step.  Craft invoices for the balance of the customer deposits.  Then, a Deposit Application record needs to be produced to apply them to the open invoice. While I discuss an approach in my article, Converting NetSuite Customer Deposits to Credit Memos, this article, Learn the NetSuite SuiteScript 2.0 Pattern to Apply Customer Deposits to Invoices, provides the code pattern.

Once the work is complete, your customer balances should all be at zero and the net situation should be sitting at the target general ledger account as designed.

Work with a NetSuite Systems Integrator with Accounting and Technology Leadership

Navigating the challenges in a cleanup situation demands thoughtfulness and care. Planning the attack is essential. Having some extra utilities to automate the transaction application is essential if the volume of records is significant.

If you found this information valuable, sign up to receive notifications of new articles. If you have a special NetSuite cleanup situation that demands strong thinking and care, 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