This article is relevant if you would like to calculate Days Sales Outstanding (DSO) using NetSuite Saved Search technology.

Background

Since my background is both in accounting and software technology, I often think about all the ways that NetSuite produces financial information and how this can be reproduced with NetSuite saved searches. The key to solving these questions is to understand the transactional general ledger system and the way NetSuite exposes underlying Oracle functions. Given some previous work I produced to create saved search trial balances and solving for the explanation behind the inventory received not billed account, I was asked by a member of the NetSuite community to help with calculating Days Sales Outstanding.

What is Days Sales Outstanding (DSO)

Days sales outstanding (DSO) is a measure, in days, between the average time that a sale is generated to the time it is paid. The idea is that an accounts receivable credit collections department can watch the pattern for a customer payment history relative to the credit terms offered. Those with longer days outstanding than the extended number of days offered by terms may be deemed a greater credit and financial risk.

Thus, focusing on the larger receivables and the DSO can help organize the priorities of the credit collection efforts.

Crafting NetSuite Days Sales Outstanding

I took the following approach to help other community members think about how they can refine the algorithm for their own purposes:

1. Current AR: Determine current AR as of now; that tends to be what the credit department is focusing on.
2. Rolling Sales: look back 12 months from today’s date and summarize sales. This works if the invoice date is indeed reliable.
3. Include Customer Deposits: given some organizations work with customer deposits, including those in the AR balance.
4. Rank by AR Balance: given the largest accounts may be the most worrisome, rank the output by the highest outstanding balance.
5. Calculate Oldest Invoice: determine the oldest invoice to contrast to the DSO number. This may indicate a trend or illustrate trouble in cash receipt collections.

The Saved Search NetSuite DSO Formula

Here is the key saved search formula that drives the DSO calculation. I also offer the full saved search definition below as an accelerator bundle.

SUM(CASE 
	WHEN {accounttype} = 'Accounts Receivable' OR {accounttype} = 'Other Current Liability'
	THEN NVL({debitamount},0)-NVL({creditamount},0)
	ELSE 0 
END) *365

/

SUM(
NULLIF(
CASE 
	WHEN {trandate} > ADD_MONTHS(SYSDATE,-12) THEN 
		CASE 
			WHEN {accounttype} = 'Income' 
			THEN NVL({debitamount},0)-NVL({creditamount},0)*-1 
			ELSE 0 
		END
	ELSE 0
END,
0))

Build on the Saved Search for Financial Transactions

The community member who made the request actually wanted to calculate the position at the end of the year. It’s actually not difficult to do this by adding some criteria to the saved search definition and making some refinements to the column formula.  I wanted to make sure this article worked for the most common use case before tackling a more specific requirement.

If you are a NetSuite end customer and would like to obtain the NetSuite Days Sales Outstanding (DSO) search, send me a note and supply me with your NetSuite Account ID. If you found this article meaningful, feel free to receive notifications of new articles. If you would like to tackle other financial saved search challenges, 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