Credit and collections · NetSuite TD3016323

Customer Payment Behavior Analysis

Payment timing, DSO, aging concentration, and collections priority for the receivables portfolio, from live invoice and payment data.

Prepared 2026-09-23 · Scope: all subsidiaries, invoices dated after 2024-09-23 · Source: NetSuite via SuiteQL · Prompt: Customer Payment Behavior Analyzer, NetSuite AI Prompt Library v1 · Standard Review depth

Executive Summary

The receivables portfolio has two populations that behave nothing alike. Customers who pay, pay early: the 246 net-30 invoices settled in the last 24 months were paid an average of 28 days before their due date, 99.6% on time, with no partial payments. Customers who don't pay, don't pay at all: 14 customers hold $562,592 of open balances, none of them has a single settled invoice in 24 months, and the oldest item is 460 days past due.

That split is the finding. Open receivables total $927,459, and 85.4% of it is more than 30 days past due. Half of the open balance ($472,126) is past 90 days. Payment behavior among active payers gives no early warning, because the accounts at risk never entered the paying population. Collections effort should be concentrated on the 14 never-paid accounts, starting with the four above $80,000.

Payment Behavior Scorecard

Risk based on: open balance more than 60 days past due, presence of a payment history, and age of the oldest open item. Customers are sorted by open balance. "No paid invoices" means no invoice for that customer was settled in the 24-month window.

DSO per customer = open balance / trailing 12-month invoiced x 365, capped at 365 when the customer had no invoicing in the window.

DSO Analysis

Calculated using: open invoice balances on 2026-09-23 divided by invoices raised in the trailing twelve months, times 365. Portfolio DSO is 175 days against 30-day terms, which is well past the "concern" threshold of terms plus 30. But the number is misleading if read as typical customer behavior. Among customers with a settled invoice, days to pay averaged 2.1 days on net-30 terms. The portfolio DSO is driven almost entirely by balances from customers who have never paid.

The trend among paying customers is stable. Net-30 invoices were paid about 30 days early in every quarter through the first quarter of 2026, then about 23 days early in the second and third quarters. That is a shift of a week, still comfortably inside terms, and worth a note rather than an action.

Retail cash-terms invoices (480 due on receipt) were paid the same day in 92.3% of cases and within a day otherwise. They are excluded from the net-30 statistics above so that they don't flatter the B2B numbers.

Aging Analysis

Aging buckets: days between the invoice due date and 2026-09-23. Current means not yet due.

The aging is concentrated in a small number of accounts. The twelve largest open balances account for 83% of receivables. Red bars are customers with no payment history; blue bars are customers who have paid before.

Collections Priority

Priority score: past-due balance over 60 days weighted by the age of the oldest item. All customer-specific actions below require human review before any account hold, credit change, or write-off.

30 / 60 / 90 day framing

• Next 30 days. Contact the 14 never-paid accounts directly and establish whether the invoices were received, disputed, or belong to customers that no longer exist. Mercury Co. ($80,079, 460 days) and Haskell Associates ($43,941, 328 days) should be reviewed for bad-debt reserve, which is a human decision.
• 60 days. Review why every customer carries a $300,000 credit limit. Utilization is under 40% everywhere, so the limit is not doing any work as a control. A limit set relative to expected annual volume would have flagged the never-paid accounts sooner.
• 90 days. Put a monthly version of this scorecard in front of the credit manager. The paying population is healthy and needs no attention; the report exists to catch the next account that enters the non-paying population within its first 30 days, not its first 300.

Appendix: Data Lineage

Adaptations from the prompt's query templates, made to run in this account: the invoice-to-payment link is nexttransactionlinelink rather than createdfrom on the payment header, which is not populated for payments; the open balance column is foreignamountunpaid, since foreignamountremaining is not exposed to SuiteQL; and transaction.subsidiary is not exposed, so subsidiary filtering was not applied. Days to pay uses the date of the last payment application against each invoice.

SELECT inv.id, inv.tranid, inv.entity, inv.trandate, inv.duedate, inv.foreigntotal, inv.foreignamountunpaid,
       pay.id AS payment_id, pay.trandate AS pay_date, ntl.foreignamount AS applied
FROM transaction inv
JOIN nexttransactionlinelink ntl ON ntl.previousdoc = inv.id AND ntl.linktype = 'Payment'
JOIN transaction pay ON pay.id = ntl.nextdoc AND pay.type = 'CustPymt'
WHERE inv.type = 'CustInvc' AND inv.posting = 'T' AND inv.trandate >= ADD_MONTHS(SYSDATE, -24)

SELECT id, tranid, entity, trandate, duedate, foreigntotal, foreignamountunpaid, TRUNC(SYSDATE) - TRUNC(duedate) AS days_past_due
FROM transaction WHERE type = 'CustInvc' AND posting = 'T' AND foreignamountunpaid > 0

SELECT c.id, c.entityid, c.companyname, c.creditlimit, BUILTIN.DF(c.terms), t.daysuntilnetdue
FROM customer c LEFT JOIN term t ON t.id = c.terms WHERE c.isinactive = 'F'

SELECT entity, COUNT(*), SUM(foreigntotal) FROM transaction
WHERE type = 'CustInvc' AND posting = 'T' AND trandate >= ADD_MONTHS(SYSDATE, -12) GROUP BY entity

SELECT a.acctnumber, a.fullname, SUM(tal.amount) FROM transactionaccountingline tal
JOIN transaction t ON t.id = tal.transaction JOIN account a ON a.id = tal.account
WHERE tal.posting = 'T' AND a.accttype = 'AcctRec' GROUP BY a.acctnumber, a.fullname

Appendix: Assumptions and Verification

Confidence: 90% that net-30 customers pay about four weeks early (full payment history); 85% that the never-paid accounts represent collection risk rather than data entry gaps (no payment applications, but the GL discrepancy shows journals touch receivables and a manual review of those accounts is required).