📌 Stage 1 · Set Up the System · Invoicing & AR · Settings and the As-Of Date · 30–40 min

Why no formula in this workbook says TODAY()

Learning goals

  • Explain the difference between the reporting period and the AR as-of date
  • Describe why TODAY() makes reports unreproducible and what to use instead
  • Change the as-of date and predict how invoice statuses will shift

Concepts

One place for every date

The Settings sheet holds three dates that steer the whole model. Period start (C5, Sep 1, 2026) and period end (C6, Sep 30, 2026) frame the monthly report: only invoices and receipts dated inside that window count as September activity. The AR as-of date (C7, also Sep 30, 2026) is different: it is the moment at which 'overdue' and every aging bucket are measured.

The two ideas are easy to blur. The period answers 'what happened in September?' The as-of date answers 'standing here, who is late right now?' At a month-end close they coincide - which is exactly why the close is the natural time to run the whole report.

Below the dates sit the policy parameters: NET 30 standard terms, the 45-day DSO target, and the 1.5%-per-month late fee referenced in final notices.

=IF($K5<=0,"Paid",IF($I5<Settings!$C$7,"Overdue",IF($J5>0,"Partial","Open")))

A preview of Lesson 6: the invoice status ladder. The comparison that matters here is $I5<Settings!$C$7 - the invoice's due date measured against the as-of date cell, not against the calendar on your wall.

TODAY() is a moving target

It is tempting to write =TODAY()-due_date for days overdue. Resist it. A report built on TODAY() shows different numbers every day it is reopened; the September report you filed would quietly rewrite itself in October. Nobody can audit a number that changes by itself, and the dunning planner would keep escalating reminders that were already sent.

The fix is the as-of date. Every lateness measure in this workbook reads Settings!$C$7. When Dana reopens the file on Oct 15, 2026, the September close still says September - and rolling the model forward is a deliberate act: change the three Settings cells, and the entire workbook re-derives (Lesson 14).

💡 Tips:

  • Rule of thumb: TODAY() belongs in scratch pads, never in anything you would file, print, or email.
  • If you want a live view AND a filed view, keep two files - or change the as-of cell deliberately, print, then change it back.

What changing the as-of date does

Because statuses, day counts, aging buckets, and dunning steps all read Settings!$C$7, moving that one cell re-times the whole system. Move it later and more invoices flip from Open to Overdue, day counts grow, invoices climb the aging buckets, and the dunning ladder advances a step. Move it earlier and the model forgives.

Two invoices are worth watching: INV-1117 (Kestrel Outdoor Gear, $7,400.00, due Oct 14, 2026) is safely Open at the September as-of date but becomes Overdue with an as-of of Oct 15. And INV-1115 (Summit Ridge Dental, paid Oct 2) stays Paid at any as-of date on or after Oct 2 - settlement is history, not a moving judgment.

💡 Tips:

  • The workbook never deletes history: rolling forward changes interpretations, not records.
  • When you try this in the exercise, use Reset Data afterward to restore the September close.

Practice

Open on Settings. Three dates steer everything; the exercise finishes by re-timing the whole model from one cell.

  1. Verify the close calendar — Confirm C5 is Sep 1, 2026, C6 is Sep 30, 2026, and C7 (AR as-of date) is Sep 30, 2026. Read each Notes cell - it states in one sentence what the parameter controls.
  2. Read the policy block — Note standard terms NET 30, DSO target 45 days, and the late fee of 1.5% per month after 60 days. Lesson 12's final-notice template quotes that fee.
  3. Re-time the model — Type Oct 15, 2026 into C7. Switch to Invoices: INV-1117 (due Oct 14) flips from Open to Overdue, INV-1118 is now due in three days, and every Days Overdue count grew by 15.
  4. Restore the close — Click Reset Data above the spreadsheet (or retype Sep 30, 2026 into C7) so the exercise matches the filed September numbers again.

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:

  • ☐ Verified period start Sep 1, 2026, period end Sep 30, 2026, and AR as-of date Sep 30, 2026 in Settings C5, C6, C7
  • ☐ Can explain in one sentence why the workbook uses an as-of cell instead of TODAY()
  • ☐ Moved the as-of date to Oct 15, 2026 and watched INV-1117 flip to Overdue, then restored the September close
  • ☐ Noted the baseline: at the Sep 30, 2026 as-of date, six invoices are Overdue and $22,000.00 is past due