Revenue Assurance Analysis · NetSuite TD3016323

The Revenue Triangle:
Billings · Recognition · Cash

One view tying what was invoiced, what hit the P&L, and what landed in the bank — because the divergence between the three is where surprises live. Twelve months of activity, reconciled to the penny where it should be, and interrogated where it shouldn't.

Window Sep 2025 – Aug 2026 Run date 2026-08-21 Account TD3016323 · Production · OneWorld Currency USD (single-currency) Source Live SuiteQL · 7 queries Prepared by Sonar AI

Billings (12 mo)

$1.99M

764 invoices · 542 cash sales · 3 credit memos, net of credits

Recognized revenue

$12.24M

85% from 24 journals — only $1.84M traces to billing documents

Cash collected

$1.19M

$799K billed but never collected — the real divergence

Open A/R · DSO

$928K · 172d

39 open invoices · 51% of balance is 90+ days past due

§ 01

Executive summary

Three legs, two stories. The Recognition leg looks alarming — GL revenue runs ~6× billings — but it is a cosmetic divergence: $10.4M of the $12.2M recognized comes from monthly "Beg Balance" seed journals with no billing or cash counterpart. Strip those and billings vs. recognition reconciles within normal timing noise. The Cash leg is the opposite: an unglamorous but genuine problem. Over 12 months the business invoiced $799K more than it collected, that gap matches today's past-due A/R to within 0.07%, and half the open balance has been overdue for more than 90 days. Collections capacity is flat (~$73–117K/month) no matter how much is billed — heavy invoice months simply pile onto the backlog.

§ 02

The triangle, month by month

Bars are billings and cash; the line is recognized revenue. Use the toggle to switch between total GL revenue (including seed journals) and revenue traceable to billing documents — the difference between the two views is Finding 1.

Billings Cash collected Recognized revenue

Gap sign convention: Bill−Rec negative = recognition exceeds billings · Bill−Cash positive = billed but not yet collected (builds A/R). All figures USD, rounded to cents in source data.

§ 03

Findings

FINDING 1 · COSMETIC — BUT MUST BE EXCLUDED FROM ANY REVENUE ANALYSIS

85% of recognized revenue is seed journals, not sales

Of $12.24M posted to Income accounts, $10.41M comes from just 24 journal entries — a matched monthly series (JE123–125, JE147–149, …) posting ~$830–930K/month to 4210 Sales : Revenue - Products and 4310 Sales : Revenue - Services, every one memoed "Beg Balance Entries – Sub 1 / Sub 2". They have no billing document, no A/R entry, and no cash counterpart: demo-data seeding. Any P&L, margin, or revenue-trend analysis on this account that fails to exclude them reports ~$1M/month of phantom revenue. In the billing-doc view of the chart above, recognition ($1.84M) tracks billings ($1.99M) closely — the residual is shipping/tax components inside invoice totals plus minor timing.

FINDING 2 · OPERATIONAL — THE REAL DIVERGENCE

$799K billed-not-collected, and it reconciles exactly to past-due A/R

Cumulative billings exceed cumulative cash by $798,688. Today's A/R shows $799,240 past due — a 99.93% match, confirming the gap is real receivables, not a measurement artifact. The gap builds in the heavy invoice months (Nov +$179K, Jun +$137K, Feb +$132K) and never unwinds: monthly collections sit in a flat $73–117K band regardless of billing volume. Implied DSO is ~172 days, and 50.9% of the open balance is 90+ days overdue — eight invoices alone (Mercury Co. at 460 days, Haskell Associates at 327, Global Information at 262…) hold over $640K.

A/R aging — $928,247 across 39 invoices

Top open balances · red = past due

FINDING 3 · CONTROL CONFIRMATION

The cash-sale channel converges perfectly — a built-in control group

Retail cash sales ($96K/yr across 542 transactions) bill, recognize, and collect on the same document — so that slice of the triangle closes by construction. More telling: in months with no large invoices (Sep, Dec, Jan) the Bill−Cash gap is exactly $0.00 — payments applied that month exactly equal that month's invoicing, a same-month batch-application pattern typical of demo data. This zero-noise floor is what makes the $799K structural gap unambiguous.

§ 04

Data integrity checks

Cross-validations run before publishing — each ties two independent query paths together.

✓Flow gap ↔ balance-sheet position

Cumulative Bill−Cash flow gap $798,688 vs. past-due open A/R $799,240 (independent point-in-time query): Δ 0.07% — explained by the small current-but-unpaid slice of recent invoices.

✓Journal attribution is exhaustive

Recognized-by-type query: Journal $10,406,458 + billing docs $1,837,344 + micro items (ItemRcpt/ItemShip/CustCred/CashRfnd, net −$984) = $12,243,802 total. No unattributed source.

✓Zero-gap months behave

Sep, Dec, Jan show Bill−Cash of exactly $0.00 — same-month payment application confirmed; the measurement pipeline introduces no noise of its own.

✓Aging buckets sum to control total

$129,007 + $6,191 + $180,705 + $139,431 + $472,913 = $928,247 = open A/R control query. Invoice counts also tie (12+4+8+2+13 = 39).

§ 05

Methodology & assumptions

Billings: foreigntotal of CustInvc + CashSale, net of CustCred, by transaction date. Includes shipping & tax (it's what the customer owes).
Recognized revenue: GL postings to accounts of type Income / OthIncome (−transactionaccountingline.amount, since revenue posts as credits), on posting transactions, split by source transaction type.
Cash collected: CustPymt + CustDep − CustRfnd, plus CashSale totals (cash sales collect at point of sale). Measured by payment date, not application date.

Assumptions & scope decisions

Elimination subsidiary excluded. All queries filter transactionline.subsidiary <> 4 (xElim) per house convention — and because transaction.subsidiary is NOT_EXPOSED to SuiteQL in this account.
Transaction date, not posting period. Months are bucketed by trandate. In this account periods align with calendar months, so the distinction is immaterial; in accounts with late-posted entries the two can differ.
foreign* amount columns used throughout. Single-currency USD account, so transaction-currency = base-currency; the foreign* family is used because base-currency variants are frequently NOT_EXPOSED.
Billings include tax & shipping; GL income does not. This creates a small structural residual (~$150K/yr) between billings and billing-doc recognition. It is expected, not an anomaly.
Cash measured gross of application. A payment counts when received. Customer deposits ($150) counted as cash-in; refunds netted out.
Open A/R snapshot as of run date (2026-08-21), invoices with status='A' (single-letter code — the prefixed form silently returns zero rows) and foreignamountunpaid > 0.
Seed journals identified by memo pattern ("Beg Balance Entries") and posting cadence (1st of month, matched Sub 1 / Sub 2 pairs), verified against the 6 most recent months in Query Q4.
Unbilled backlog not scoped. A fourth leg (open Sales Orders → future billings) exists but was out of scope; noted under next steps.

§ 06

Appendix — source queries

Every number in this document derives from these seven SuiteQL queries, run live against TD3016323 on 2026-08-21. They are reproducible as-is in the SuiteQL Query Tool.

Q1 — Monthly billings by document type transaction · transactionline

Header-level totals; mainline='T' join used purely to reach the exposed line-level subsidiary.

SELECT TO_CHAR(t.trandate,'YYYY-MM') AS month, t.type,
       ROUND(SUM(t.foreigntotal),2) AS total, COUNT(*) AS n
FROM transaction t
JOIN transactionline tl ON tl.transaction = t.id AND tl.mainline = 'T'
WHERE t.type IN ('CustInvc','CashSale','CustCred')
  AND tl.subsidiary <> 4   -- exclude xElim
  AND t.trandate >= TO_DATE('2025-09-01','YYYY-MM-DD')
GROUP BY TO_CHAR(t.trandate,'YYYY-MM'), t.type
ORDER BY TO_CHAR(t.trandate,'YYYY-MM'), t.type

Q2 — Monthly recognized revenue, GL income postings transactionaccountingline

Revenue posts as credits, hence −tal.amount. Posting flag lives on the transaction header, not the accounting line.

SELECT TO_CHAR(t.trandate,'YYYY-MM') AS month, ROUND(SUM(-tal.amount),2) AS recognized
FROM transactionaccountingline tal
JOIN transaction t  ON t.id = tal.transaction
JOIN transactionline tl ON tl.transaction = tal.transaction AND tl.id = tal.transactionline
JOIN account a ON a.id = tal.account
WHERE t.posting = 'T'
  AND a.accttype IN ('Income','OthIncome')
  AND tl.subsidiary <> 4
  AND t.trandate >= TO_DATE('2025-09-01','YYYY-MM-DD')
GROUP BY TO_CHAR(t.trandate,'YYYY-MM')
ORDER BY TO_CHAR(t.trandate,'YYYY-MM')

Q3 — Monthly cash collections by type CustPymt · CustDep · CustRfnd

SELECT TO_CHAR(t.trandate,'YYYY-MM') AS month, t.type,
       ROUND(SUM(t.foreigntotal),2) AS total, COUNT(*) AS n
FROM transaction t
JOIN transactionline tl ON tl.transaction = t.id AND tl.mainline = 'T'
WHERE t.type IN ('CustPymt','CustDep','CustRfnd')
  AND tl.subsidiary <> 4
  AND t.trandate >= TO_DATE('2025-09-01','YYYY-MM-DD')
GROUP BY TO_CHAR(t.trandate,'YYYY-MM'), t.type
ORDER BY TO_CHAR(t.trandate,'YYYY-MM'), t.type

Q4 — Recognized revenue attributed by source type + journal identification 2 queries

First: group Q2 by t.type instead of month (yields Journal $10.4M / CustInvc $1.75M / CashSale $87K / micro items). Second: drill into the journals. Note account.acctname is NOT_EXPOSED in this account — use fullname.

SELECT t.tranid, TO_CHAR(t.trandate,'YYYY-MM-DD') AS trandate,
       a.acctnumber, a.fullname,
       ROUND(SUM(-tal.amount),2) AS income_amt, MAX(t.memo) AS memo
FROM transactionaccountingline tal
JOIN transaction t  ON t.id = tal.transaction
JOIN transactionline tl ON tl.transaction = tal.transaction AND tl.id = tal.transactionline
JOIN account a ON a.id = tal.account
WHERE t.posting = 'T' AND t.type = 'Journal'
  AND a.accttype IN ('Income','OthIncome')
  AND tl.subsidiary <> 4
  AND t.trandate >= TO_DATE('2026-06-01','YYYY-MM-DD')
GROUP BY t.id, t.tranid, t.trandate, a.acctnumber, a.fullname
ORDER BY t.trandate DESC, SUM(-tal.amount) DESC

Q5 — Open A/R control totals status='A' single-letter code

SELECT COUNT(*) AS open_invoices,
       ROUND(SUM(t.foreignamountunpaid),2) AS open_ar,
       ROUND(SUM(CASE WHEN t.duedate < TRUNC(SYSDATE) THEN t.foreignamountunpaid ELSE 0 END),2) AS overdue_ar
FROM transaction t
WHERE t.type = 'CustInvc' AND t.status = 'A'
  AND t.foreignamountunpaid > 0

Q6 — A/R aging buckets as of 2026-08-21

SELECT CASE
    WHEN t.duedate >= TRUNC(SYSDATE) THEN 'current'
    WHEN TRUNC(SYSDATE)-TRUNC(t.duedate) <= 30 THEN '1-30'
    WHEN TRUNC(SYSDATE)-TRUNC(t.duedate) <= 60 THEN '31-60'
    WHEN TRUNC(SYSDATE)-TRUNC(t.duedate) <= 90 THEN '61-90'
    ELSE '90+' END AS bucket,
  COUNT(*) AS invoices, ROUND(SUM(t.foreignamountunpaid),2) AS balance
FROM transaction t
WHERE t.type = 'CustInvc' AND t.status = 'A' AND t.foreignamountunpaid > 0
GROUP BY /* same CASE */

Q7 — Top customers by open balance customer join

SELECT c.entityid AS customer, COUNT(t.id) AS open_invoices,
       ROUND(SUM(t.foreignamountunpaid),2) AS open_balance,
       ROUND(SUM(CASE WHEN t.duedate < TRUNC(SYSDATE) THEN t.foreignamountunpaid ELSE 0 END),2) AS overdue_balance,
       MAX(TRUNC(SYSDATE)-TRUNC(t.duedate)) AS max_days_overdue
FROM transaction t
JOIN customer c ON c.id = t.entity
WHERE t.type = 'CustInvc' AND t.status = 'A' AND t.foreignamountunpaid > 0
GROUP BY c.id, c.entityid
ORDER BY SUM(t.foreignamountunpaid) DESC
FETCH FIRST 12 ROWS ONLY

§ 07

Recommended next steps

Collections sprint on the 90+ bucket. Eight invoices hold $473K; the top four (Global Information, Red Rivers, Mercury Co., Gotter inc.) are each >150 days overdue. Dunning or write-off decisions are overdue in both senses.
Tag or reverse the seed journals ("Beg Balance Entries") — or standardize an exclusion filter — so P&L reporting stops carrying ~$1M/month of phantom revenue.
Add the fourth leg: unbilled backlog. Extending the triangle to open Sales Orders (booked → billed → recognized → collected) would complete the revenue waterfall.
Recurring monitor. This triangle is a natural monthly artifact — it could run as part of the monthly-management-reporting process or as a Fathom workbench tab.