📌 Stage 5 · Collections & Follow-up · Invoicing & AR · Dunning Cadence · 40–50 min

From friendly reminder to escalation, on schedule

Learning goals

  • Lay out a five-step escalation ladder anchored to the due date: day 0, +7, +14, +30, +60
  • Turn the ladder into nested IFs that name the next action, its date, and the email template
  • Describe the four email templates and the tone discipline that keeps dunning professional

Concepts

Anchor everything to the due date

Dunning - the scheduled chasing of unpaid invoices - fails in one of two ways: too timid (one polite email, then silence) or too personal (anger at whoever answers the phone). The fix is a cadence anchored to the due date, not to your mood.

Under NET 30 the first reminder goes out ON the due date - the day the promise breaks, not a week after. Then the ladder: a second email at +7 days, a phone call to accounts payable at +14, a formal final-notice letter at +30 (this is where Cedar & Co.'s 1.5%-per-month late fee gets mentioned), and an escalation review at +60: owner decision among a payment plan, a collections agency, or a write-off.

Because every step counts from the due date, the cadence is automatic and identical for every customer - which is also what keeps it defensible.

💡 Tips:

  • Reminder on the due date is standard US practice for NET 30 terms; waiting a 'grace week' quietly adds seven days to your DSO.
  • Log every contact outside this sheet (email sent-date, who you spoke to) - the planner says what to do, not what you did.

The ladder as nested IFs

The Dunning sheet is a worklist: one row per open invoice (nine at this close), invoice number typed, everything else pulled from the register by VLOOKUP. Days Overdue is recomputed locally so the ladder has its input, then two nested IFs walk the same thresholds - one to name the action, one to compute its calendar date - and a third picks the email template.

The action date adds the ladder's offset to the due date: +0, +7, +14, +30, +60. A not-yet-due invoice reads 'Monitor - not yet due' with the due date itself as its action date - the day the first reminder would go out.

=IF($F5<0,"Monitor - not yet due",IF($F5<7,"Reminder 1 - email",IF($F5<14,"Reminder 2 - email",IF($F5<30,"Phone AP contact",IF($F5<60,"Final notice - letter","Escalate to collections")))))

$F5 is days overdue. The IFs test the ladder thresholds in order: below zero means not due; under 7 means the day-0 reminder is the current step; under 14 the second email; under 30 the phone call; under 60 the final notice; anything else escalates. First true branch wins, so the order of the tests is the ladder.

=IF($F5<0,$E5,IF($F5<7,$E5+0,IF($F5<14,$E5+7,IF($F5<30,$E5+14,IF($F5<60,$E5+30,$E5+60)))))

The same ladder for dates: the due date ($E5) plus the step's offset. For INV-1082 (due Jun 25, 2026, 97 days late) the escalation step's date is Jun 25 + 60 = Aug 24, 2026 - the day that decision should have been made.

Templates T1-T4

The Email Template column points at four reusable messages - write them once, keep them in a snippets folder, and personalize only the facts.

T1 - friendly reminder (steps 1 and 2). Subject: 'Invoice INV-1116 - friendly reminder'. Body: invoice number, amount, due date, payment link, one warm line. Assumes forgetfulness, because it usually is.

T2 - firm reminder (phone-prep, step 3). Subject: 'Invoice INV-1108 - payment overdue'. Restates the terms, asks for a specific payment date, offers to walk through any issue.

T3 - final notice (step 4). Subject: 'Final notice - Invoice INV-1101'. States the late fee (1.5% per month after 60 days), pauses further work per the credit policy, gives a deadline.

T4 - escalation (step 5). Internal, not to the client: the owner chooses among a payment plan, a collections agency, or a write-off, and documents the choice.

Every message states the same four facts - number, amount, due date, how to pay - and never speculates about why the client is late.

💡 Tips:

  • Send to the billing contact from the Customers sheet; CC the owner on T2 and later.
  • The workbook deliberately computes the cadence but does not send email - no macros, by design; your mail client's templates do that half.
  • Rebuild this worklist at each close: list the open invoice numbers, then copy the formula columns down.

Practice

Open on Dunning. Cell G5 (Next Action for INV-1082) is blank - type the ladder, then walk all nine rows like a Monday collection plan.

  1. Type the ladder formula — Cell G5 is blank. Type =IF($F5<0,"Monitor - not yet due",IF($F5<7,"Reminder 1 - email",IF($F5<14,"Reminder 2 - email",IF($F5<30,"Phone AP contact",IF($F5<60,"Final notice - letter","Escalate to collections"))))) and press Enter. G5 returns Escalate to collections - 97 days is past every softer step.
  2. Check the action date — H5 reads Aug 24, 2026: the Jun 25 due date plus the +60 escalation offset. That date being six weeks past is itself information - this escalation is overdue twice over.
  3. Walk the working set — INV-1101 (48 days): final notice, Sep 12. INV-1108 (25 days): phone AP contact, Sep 19. INV-1112 (10 days) and INV-1116 (7 days): Reminder 2 emails, Sep 27 and Sep 30 - note INV-1116 already missed its day-0 reminder, short NET 15 terms move fast.
  4. Read the bottom of the list — Rows 11-13 (INV-1117, INV-1118, INV-1119) read Monitor - not yet due with action dates in October. The full list is nine rows: two escalations, one final notice, one call, two emails, three monitors.

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 nested-IF ladder into G5 and got Escalate to collections with action date Aug 24, 2026
  • ☐ Walked all nine rows: 2 escalations, 1 final notice, 1 phone contact, 2 reminder emails, 3 monitors
  • ☐ Mapped each step to its template: T1 for reminders, T2 for phone prep, T3 final notice, T4 escalation
  • ☐ Noted the baseline: the dunning worklist holds 9 open invoices worth $36,600.00, including $4,800.00 already at 97 days