01 / Learn the method
Learning outcomes
Turn aging into a collection plan without treating disputed invoices as cash. Calculate the result, reconcile it and communicate its limitations.
Method
Days past due = max(as-of date minus due date, 0). An invoice due today is current under this case convention. Apply valid credit notes before aging. A collection plan combines aging with dispute status and specific customer commitments; age alone does not guarantee payment.
Smaller worked example
At 30 September, a $1,000 invoice due 20 September is 10 days overdue. A valid $200 credit reduces collectible AR to $800. A promise for 10 October belongs in a future weekly collection bucket.
02 / Put it to work
Business context and rules
Fictional Maple Consulting, as of 30 September 2026. A: gross 5,000, due 10 Sep; B: gross 4,000, due 25 Sep, valid credit 500; C: 3,000 due 10 Oct; D: 2,000 due 15 Aug and fully disputed; E: 1,000 due 30 Sep. Collections: A promises 3,000 in 1-7 Oct and 2,000 in 15-21 Oct; B promises net amount in 8-14 Oct; C promises full amount in 15-21 Oct; E promises full amount in 1-7 Oct; D has no committed date. No other credits, receipts or invoices. Dates are represented by Excel date serials in Inputs and formatted as dates in the workbook.
Original synthetic inputs
| Input | Value | Unit |
|---|---|---|
| As-of date: 30 September 2026 | 46295 | Excel date |
| Invoice A gross | 5000 | USD |
| A due: 10 September 2026 | 46275 | Excel date |
| Invoice B gross | 4000 | USD |
| B due: 25 September 2026 | 46290 | Excel date |
| B credit note | 500 | USD |
| Invoice C gross | 3000 | USD |
| C due: 10 October 2026 | 46305 | Excel date |
| Invoice D disputed | 2000 | USD |
| D due: 15 August 2026 | 46249 | Excel date |
| Invoice E gross | 1000 | USD |
| E due: 30 September 2026 | 46295 | Excel date |
| A week 1 promise | 3000 | USD |
Required deliverables
Produce invoice-level aging, a weekly collection plan and an exception log. The supplied aggregate bucket formulas are specific to these due dates; replace them with conditional aging formulas if changing dates. Assign the disputed invoice to an owner and state evidence required before forecasting its cash.
Use formulas for derived amounts and preserve source data. Put narrative deliverables in the workbook response area; expand it as needed. Compare amounts within 0.01 of the stated unit and percentages within 0.1 percentage point. No unsupported balancing plugs.
03 / Review your work
Try the assignment before opening the answer.
Open the worked answer and teaching notes
Worked numerical schedule
| Measure | Value | Unit |
|---|---|---|
| A net AR | 5,000.00 | USD |
| B net AR | 3,500.00 | USD |
| A days past due | 20.00 | days |
| B days past due | 5.00 | days |
| C days past due | 0.00 | days |
| D days past due | 46.00 | days |
| E days past due | 0.00 | days |
| Current AR for supplied due dates | 4,000.00 | USD |
| 1-30 days overdue for supplied due dates | 8,500.00 | USD |
| 31-60 days overdue for supplied due dates | 2,000.00 | USD |
| Total net AR | 14,500.00 | USD |
| Expected collections 1-7 October | 4,000.00 | USD |
| Expected collections 8-14 October | 3,500.00 | USD |
| Expected collections 15-21 October | 5,000.00 | USD |
| AR outside committed plan | 2,000.00 | USD |
| Aging reconciliation | 0.00 | USD |
Interpretation and recommended actions
Net AR is 14,500: current 4,000, 1-30 days overdue 8,500 and 31-60 days 2,000. Expected weekly collections are 4,000 / 3,500 / 5,000; 2,000 disputed remains outside the committed plan. Prioritize confirming A’s split promise and resolving D’s dispute with the account owner. Do not write off D or assign a receipt date merely because it is old. Promises are assumptions requiring follow-up.
Scoring rubric - 100 points
| Dimension | Points | Awarding guidance |
|---|---|---|
| Calculation | 40 | Invoice aging 15; net AR and bucket totals 10; weekly collections 15. |
| Interpretation | 25 | Correct application of the case rules 10; explain the business decision 10; identify evidence or limitations 5. |
| Controls / audit trail | 20 | Traceable formulas 8; independent reconciliation 8; explicit units and signs 4. |
| Communication | 15 | Decision and numerical headline 5; actions with owners and evidence 5; concise response covering all required deliverables 5. |
Award method credit after an isolated arithmetic error rather than repeatedly deducting for it. Equivalent account labels and well-supported alternative recommendations are acceptable. Numerical tolerance is 0.01 in the stated units; no universal passing score is prescribed.
Common mistakes
Aging gross B before its credit; calling E overdue on its due date; putting D in week one solely because it is oldest.
Staged hints
Net the credit first. Compare each due date to 30 September. Reconcile committed receipts plus unresolved AR to net AR.
Instructor notes
Prerequisites: Invoices, terms and basic date arithmetic. Suggested use of the estimated 45 minutes: spend roughly 15% on the lesson and smaller example, 55% on the independent task, 20% on comparing approaches and 10% on the decision discussion. Timing is untested. Ask learners to explain why the numerical check is necessary but not sufficient.
For a simpler class, provide the model structure and work through one driver. For an extension, change one operational assumption and require a new reconciliation and recommendation. Verify the new key before distributing any variant. Open files in the intended spreadsheet application before class. Solutions are learning resources, not secure hiring examinations. Expert review remains pending.
Continue this learning path
CFT-02 · Build a 13-Week Cash Forecast
CFT-03 · Stress-Test Liquidity Before the Shortfall
AI-assisted synthetic teaching case. Expert review and native Excel / Sheets testing pending.