How to Reduce DSO Using Data You Already Have in NetSuite

Aged debt gets reviewed monthly. Payment behaviour changes daily. That mismatch is most of why DSO is difficult to move: the artefact everyone works from is a snapshot of a slow variable, and it says nothing about the fast one.

Worse, the aged debt report actively misleads on priority. It puts the oldest invoices at the top, and the oldest invoices are frequently the least collectable — a disputed £4,000 sitting at 120 days will absorb an afternoon and yield nothing, while a £90,000 invoice that just tipped past terms with a customer who always pays at 45 days needs one email.

Calculating DSO properly

The standard formula:

-- Simple DSO for a period.
DSO = (Accounts Receivable ÷ Credit Sales) × Days in Period

That works when sales are stable and misleads badly when they aren't. If you had a strong final month, your AR balance is inflated relative to the quarter's average sales and DSO looks worse than reality. For seasonal businesses, use the countback method — work backwards month by month, subtracting each month's sales from the AR balance until it's exhausted, and count the days consumed.

-- Monthly AR balance and credit sales, for DSO by either method.
WITH sales AS (
  SELECT TRUNC(t.trandate, 'MM')      AS month,
         SUM(ABS(t.foreigntotal))     AS credit_sales
  FROM transaction t
  WHERE t.type = 'CustInvc' AND t.voided = 'F'
    AND t.trandate >= ADD_MONTHS(CURRENT_DATE, -13)
  GROUP BY TRUNC(t.trandate, 'MM')
),
open_ar AS (
  SELECT SUM(ABS(NVL(t.foreignamountunpaid, 0))) AS ar_balance
  FROM transaction t
  WHERE t.type = 'CustInvc' AND t.voided = 'F'
    AND NVL(t.foreignamountunpaid, 0) > 0
)
SELECT s.month, ROUND(s.credit_sales, 0) AS credit_sales,
       (SELECT ROUND(ar_balance, 0) FROM open_ar) AS ar_now
FROM sales s
ORDER BY s.month

Why the aged debt report isn't enough

Consider three overdue invoices, all at 45 days past due:

The aged debt report ranks these identically. But X is a reliable payer who is uncharacteristically late on a large sum — that's either an administrative problem you can fix with one call, or an early warning worth knowing about immediately. Y is behaving exactly as Y always behaves; chasing at day 45 achieves nothing except irritation, and the real issue is that terms were never enforced. Z is small, disputed and deteriorating — genuinely at risk, but the value doesn't justify front-of-queue.

Three completely different actions. One report that can't distinguish them.

Query 1 — payment behaviour by customer

This builds the behavioural profile the aged debt report lacks: how each customer actually pays, how consistently, and whether it's changing.

-- Payment behaviour profile by customer, last 24 months.
WITH paid AS (
  SELECT
    t.entity                                   AS cust,
    t.id,
    t.trandate,
    t.duedate,
    t.closedate,
    (t.closedate - t.trandate)                 AS days_to_pay,
    (t.closedate - t.duedate)                  AS days_beyond_terms,
    ABS(t.foreigntotal)                       AS invoice_value
  FROM transaction t
  WHERE t.type = 'CustInvc'
    AND t.voided = 'F'
    AND t.closedate IS NOT NULL
    AND t.trandate >= ADD_MONTHS(CURRENT_DATE, -24)
)
SELECT
  BUILTIN.DF(cust)                            AS customer,
  COUNT(*)                                    AS invoices_paid,
  ROUND(AVG(days_to_pay), 1)                 AS avg_days_to_pay,
  ROUND(MEDIAN(days_to_pay), 1)              AS median_days_to_pay,
  ROUND(STDDEV(days_to_pay), 1)              AS consistency,
  ROUND(AVG(days_beyond_terms), 1)           AS avg_beyond_terms,
  ROUND(AVG(CASE WHEN trandate >= ADD_MONTHS(CURRENT_DATE, -6)
                 THEN days_to_pay END), 1)          AS recent_6m,
  ROUND(AVG(CASE WHEN trandate <  ADD_MONTHS(CURRENT_DATE, -6)
                 THEN days_to_pay END), 1)          AS prior_18m,
  ROUND(SUM(invoice_value), 0)               AS value_paid
FROM paid
GROUP BY cust
HAVING COUNT(*) >= 6
ORDER BY (AVG(CASE WHEN trandate >= ADD_MONTHS(CURRENT_DATE, -6) THEN days_to_pay END)
        - AVG(CASE WHEN trandate < ADD_MONTHS(CURRENT_DATE, -6) THEN days_to_pay END)) DESC

Sorted by deterioration — the customers whose recent behaviour is worst relative to their own history appear first. That column is the early warning; a customer drifting from 38 to 55 days matters long before they show up in the 90-day bucket.

The consistency column (standard deviation) is underrated. A customer who always pays at exactly 60 days is entirely predictable and can be planned around. One averaging 45 days with a standard deviation of 30 is unpredictable, and unpredictability is what actually costs you, because it is what you have to hold working capital against.

Segmenting: four collection profiles

Only two of these four warrant chasing at all, and one of them isn't a collections activity. That's the practical payoff: a collections team of two working the right accounts will outperform a team of four working an aged debt report top to bottom.

Query 2 — a chase list ranked by value at risk

Combining current exposure with behavioural risk gives an actionable daily list.

-- Open invoices ranked by value at risk, not by age.
WITH behaviour AS (
  SELECT t.entity AS cust,
         AVG(t.closedate - t.trandate) AS typical_days
  FROM transaction t
  WHERE t.type = 'CustInvc' AND t.voided = 'F'
    AND t.closedate IS NOT NULL
    AND t.trandate >= ADD_MONTHS(CURRENT_DATE, -24)
  GROUP BY t.entity
  HAVING COUNT(*) >= 6
)
SELECT
  BUILTIN.DF(t.entity)                        AS customer,
  t.tranid                                     AS invoice,
  t.trandate, t.duedate,
  ROUND(ABS(t.foreignamountunpaid), 2)        AS outstanding,
  ROUND(CURRENT_DATE - t.duedate)            AS days_overdue,
  ROUND(b.typical_days, 0)                    AS usually_pays_in,
  ROUND((CURRENT_DATE - t.trandate) - b.typical_days) AS days_off_pattern,
  ROUND(ABS(t.foreignamountunpaid)
        * LEAST(((CURRENT_DATE - t.trandate) / NULLIF(b.typical_days, 0)), 3), 0)
                                               AS value_at_risk
FROM transaction t
JOIN behaviour b ON b.cust = t.entity
WHERE t.type = 'CustInvc'
  AND t.voided = 'F'
  AND NVL(t.foreignamountunpaid, 0) > 0
  AND CURRENT_DATE > t.duedate
ORDER BY value_at_risk DESC

value_at_risk weights the outstanding amount by how far past the customer's own normal payment point the invoice has travelled, capped at 3× so a single extreme case can't dominate. Crude, transparent, and a substantial improvement on sorting by age.

Give a collections team the top 20 rows of this each morning rather than a 400-line aged debt report, and the same effort collects materially more.

The half of DSO that isn't collections

Here's the part that gets missed. A meaningful share of DSO accrues before anyone is late, and no amount of chasing recovers it:

• Invoicing delay. If you despatch Monday and invoice in Friday's batch, you have added four days to every invoice you raise. Invoicing on despatch is often the single largest available reduction, and it requires no conversation with any customer.
• Dispute resolution time. A disputed invoice isn't late, it's stopped. Measure the average age of open disputes; in many businesses it's the largest single component of excess DSO.
• Wrong details. Missing PO reference, invoice sent to the wrong address, wrong entity named. The customer isn't refusing to pay — they can't process it. This shows up as the erratic profile above.
• Terms nobody agreed. Customers paying to their own standard terms rather than yours, uncontested for years.

Work out how many days sit in each of these before investing in more chasing capacity. The answer frequently reallocates the whole effort.

What to watch weekly

Four numbers, tracked as trends rather than levels:

1. Days Beyond Terms, not DSO — isolates what you control.
2. Count of customers in the deteriorating profile — the leading indicator; it moves months before aged debt does.
3. Average age of open disputes — usually the biggest hidden component.
4. Days from despatch to invoice — the one you can fix unilaterally this month.

None of these appear on a standard aged debt report, and all four come from data NetSuite is already recording. That's the recurring theme: the constraint is almost never data availability. It's that nobody has asked the ledger a question in this shape.