📌 Stage 5 · Collections & Follow-up · Invoicing & AR · DSO and Metrics · 35–45 min

One number the owner tracks every month

Learning goals

  • Compute days sales outstanding (DSO) and read it against a target
  • Build the supporting metrics: invoiced, collected, open, overdue, counts
  • Explain what actually moves DSO - and how not to game it

Concepts

DSO - days sales outstanding

DSO answers the owner's question 'how long does a dollar of billing take to come back?' The classic month-end formula: open receivables divided by the period's credit sales, scaled to the number of days in the period. September invoiced $20,300.00 and left $36,600.00 open: 36,600 / 20,300 × 30 = 54.1 days.

Read against Cedar & Co.'s 45-day target, that is a 9.1-day gap - roughly a week and a half of extra waiting on every dollar billed. DSO is a trend instrument, not a verdict: one month means little, three rising months mean the terms, the invoicing speed, or the follow-up is slipping.

=IF($C$5=0,"",ROUND($C$7/$C$5*30,1))

Open receivables ($C$7) divided by invoiced-in-period ($C$5), times 30 days, rounded to one decimal. The IF guard shows blank in a month with no invoicing instead of #DIV/0! - a fresh January or a sabbatical month should not break the report.

The supporting metrics

DSO never stands alone; the rows around it explain it. Invoiced in period ($20,300.00, accrual view: five September invoices) and Collected in period ($12,950.00, cash view: six September receipts) are the same SUMIFS shape you built in Lessons 6 and 8, one over each journal's date column. Open receivables ($36,600.00) and Overdue receivables ($22,000.00) come straight off the register.

Two derived rows add nuance: overdue share of open AR (22,000 / 36,600 = 60.1%) says most of the book's risk is already late, and the DSO gap (9.1 days) turns the target into a to-do. Three COUNTIFS rows count activity: 5 invoices raised, 4 fully settled, 6 receipts applied, plus the 2 customers on credit hold carried over from Lesson 11.

=SUMIFS(Invoices!$H$5:$H$1004,Invoices!$C$5:$C$1004,">="&Settings!$C$5,Invoices!$C$5:$C$1004,"<"&(Settings!$C$6+1))

Invoiced in period: sum invoice amounts (H) dated inside the Settings window. The criteria concatenate the comparison onto the Settings cell reference - the standard trick for date-window SUMIFS.

=IF($C$7=0,"",$C$8/$C$7)

Overdue share of open AR, guarded like DSO. The cell formats as a percentage, so 22000/36600 displays as 60.1%.

What actually moves DSO

Four levers, in order of honesty. Invoice faster - the clock starts at the invoice date, and 'we bill at month end' silently adds weeks. Get the PO right the first time - unmatched invoices sit in AP purgatory. Run the dunning cadence - Lesson 12 exists because day-60 silence is a DSO problem wearing a disguise. Take deposits on large projects - money received before invoicing never enters the receivable at all.

One lever to refuse: stopping invoicing to shrink the denominator makes DSO look worse, not better, and shrinks revenue besides. Metrics are for steering, not for decorating.

💡 Tips:

  • A 90-day rolling DSO (open AR divided by trailing 3-month average daily sales) smooths lumpy months - build it later by widening the SUMIFS window.
  • Compare DSO to your terms: with NET 30 terms, a DSO near 30 means clients pay on time; 54.1 means the average dollar waits a NET 60 lifetime.

Practice

Open on Collections. Cell C10 (DSO) is blank - type the formula, then read the twelve rows as one monthly story.

  1. Type the DSO formula — Cell C10 is blank. Type =IF($C$5=0,"",ROUND($C$7/$C$5*30,1)) and press Enter. C10 returns 54.1 - days sales outstanding for September 2026.
  2. Read it against target — C11 holds the 45-day policy target and C12 the gap: 54.1 - 45 = 9.1 days late versus goal. Positive gap means cash is arriving slower than the owner wants.
  3. Audit the four dollar rows — C5 invoiced 20,300.00 (five September invoices), C6 collected 12,950.00 (six September receipts), C7 open 36,600.00, C8 overdue 22,000.00. Each traces to a SUMIFS or SUM you have already built.
  4. Read the ratios and counts — C9 overdue share 60.1% - three-fifths of the open book is late. C13-C16: 5 invoices raised, 4 settled, 6 receipts applied, 2 customers on hold.

Online exercise

The spreadsheet below is editable — follow the practice steps right on this page. Steps that need ribbon menus should be done in your own Excel with the downloadable practice file.

表格加载中…

Checklist

Work through each item; when every box passes, this lesson is done:

  • ☐ Typed the guarded DSO formula into C10 and got 54.1 days for September 2026
  • ☐ Verified the gap row reads 9.1 days against the 45-day target
  • ☐ Audited the dollar rows: invoiced 20,300.00, collected 12,950.00, open 36,600.00, overdue 22,000.00 (60.1% of open)
  • ☐ Noted the baseline: counts run 5 raised / 4 settled / 6 receipts / 2 credit holds