This article is relevant if you have numerous small balances in your NetSuite general ledger, accounts receivable, or customer deposit accounts and need a license-free tool to write them off.

Background

Our firm works with many clients who use third-party eCommerce systems, the most popular being Shopify. We specialize in advanced integrations to ensure front-end business logic is properly coupled with NetSuite back-end systems.  Since calculations on the eCommerce front end, such as discounts and taxes, may differ slightly from those on the NetSuite back end, inconsistencies can arise.

When dealing with eCommerce-driven customer deposit accounting, especially for companies that take funds before delivering goods or services, we help our clients achieve proper revenue recognition (I am amazed at the number of prospective customers who come to us with eCommerce integrations that incorrectly go directly to cash sales). The goal is to recognize revenue via an invoice when goods have been shipped or services have been delivered. The earned invoice can be satisfied using the customer deposit funds we recorded during the order capture, which generally works well.

However, inconsistencies can lead to small balance issues, often called “penny problems.” For instance, one client experienced rounding differences due to slight variations in how the front end calculated discounts in bundling compared to NetSuite’s representation. This resulted in either insufficient customer deposit funds to satisfy the earned invoice or small leftover amounts that were not consumed. Over time, these small balances can grow write-off complexity on the customer account with subsequent orders.

Other clients have faced similar challenges due to differing sales tax engines. The eCommerce platform might calculate tax one way, while NetSuite calculates it differently, leading to small discrepancies. If transaction volumes are high, these small balance issues can become significant, increasing the reporting and administrative costs of managing the accounts receivable and customer deposit agings.

Note: readers may be interested in our tool, which allows you to see consolidated aging based on Accounts Receivable and Customer deposits. See my 2021 article, Get a NetSuite Accounts Receivable with Customer Deposit Aging.  In addition, in 2015, I wrote an article on how to write off invoices.

One client wanted to address this challenge proactively.  They had over 60,000 customers with small balances to write off and needed an easy-to-use tool to handle these balances regularly without turning them into a major project each time they sought to get it under control. We collaborated with them to develop a scalable solution tailored to their needs.

Review of a Prolecto Small Balance Write-Off Tool

As discussed, a common business need arises when clients have Customer Deposits and/or credit memos, often leaving net residual balances when applying Deposits or Credits across multiple Invoices. In other words, the total amount of invoices often does not quite match the total credit or deposit balances.

For example, a NetSuite-driven organization may sell a product in a 3-pack on their website for $70.00. The customer will prepay $70, yet when this order is recorded in NetSuite, each item is assigned a price of $23.33, totaling $69.99, leaving a credit balance of $0.01.

The application we created for the client, the Prolecto Small Balance Write-Off Tool, addresses this issue when customers overpay or underpay by user-defined immaterial amounts, allowing clients to write off these balances. This tool is particularly useful when multiple A/R accounts are involved, making it challenging to offset debits against credits natively since NetSuite does not allow a debit in one GL account to be applied against a credit in another.

The tool allows users to set a value-amount threshold for balances that should be written off.   The process involves identifying all customers in this situation and applying the appropriate logic for the write-off. The tool assesses a net balance by customer and subsidiary, calculated by netting all credits and debits across A/R accounts and the Customer Deposit liability account. If the absolute value of the net balance is within the threshold, custom financial transactions are created to offset the balances within each A/R account. A Deposit Application and/or zero-dollar customer payments are then generated to apply the custom transactions against the open A/R transactions, effectively writing off the balance. This approach properly cleans up the reports and agings.

Scalability and Logic for Small Balance Write-Offs

Writing off a small balance from accounts receivable aging requires different approaches depending on whether the net balance is a credit or a debit. Additionally, if configured as such, NetSuite’s use of multiple AR accounts adds complexity to the write-off process. There may also be small credit balances on the NetSuite system-generated customer deposit account and, occasionally, believe it or not, net debit balances on the customer deposit account.

Our tool uses a NetSuite plug-in logic to tailor the approach for each client. Beyond specifying the write-off general ledger account, we can customize specific transaction types as needed.

Calculating the net balance situation is demanding on the NetSuite database, especially with a large volume of transactions. Standard NetSuite saved searches can time out.  However, using the persistent search feature allows long-running queries to write their results to the NetSuite file system. The file system then provides our tool with the inputs for all customers in the targetted situation. To ensure accuracy, the write-off logic checks all customer assumptions when producing the actual remediation transactions, even if the customer file data grows stale.

Our approach involves crafting custom financial transactions that are more detailed than standard journal entries. These transactions are then integrated into cash payment application logic to ensure clean customer aging. These transactions appear within the standard NetSuite financial system, assuring auditors that proper write-off practices are followed.

Escheatment Considerations on Credit Write-Offs

Net credit balances by the customer on the balance sheet may indicate an obligation to the customer, even if the amounts are small or immaterial. These credit balances might be subject to state-based escheatment laws, which require organizations to transmit funds to the state if returning them to the customer is difficult or impossible. Therefore, organizations need a policy for writing off credits that may be subject to escheatment.

The good news is that we have complete control over determining which customer situations will be written off. This allows organizations to shape their write-off strategy to comply with escheatment laws while effectively managing small balance credits.

Small Balance Write-Off Video Demonstration (9:54)

In preparation for this client work, Jenni M., an analyst in our Accounting Practice, collaborated with our Technology Practice to develop the tool. I interviewed Jenni to gain a deeper understanding of its functionality.

Click here to watch the (9:54) video.

Get the Small Balance Write-Off Tool

Like all the intellectual property under our Prolecto LABs initiative, this tool is available to our clients without a license charge. The key to successful implementation involves a thorough discussion on the origin of the small balances, the remediation approach, and the practices for ongoing management. Where appropriate, our clients request our assistance to minimize small balance issues through refinements in integration logic.

While this tool is designed for handling small balances, it is also flexible enough to help clean up any balance sheet account with numerous transaction inputs.

If you found this article relevant, feel free to sign up for notifications to new articles as I post them. If you are ready to tackle your small balance write off concerns, 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