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 | 30 September 2026 | Date |
| Invoice A gross | 5000 | USD |
| A due: 10 September 2026 | 10 September 2026 | Date |
| Invoice B gross | 4000 | USD |
| B due: 25 September 2026 | 25 September 2026 | Date |
| B credit note | 500 | USD |
| Invoice C gross | 3000 | USD |
| C due: 10 October 2026 | 10 October 2026 | Date |
| Invoice D disputed | 2000 | USD |
| D due: 15 August 2026 | 15 August 2026 | Date |
| Invoice E gross | 1000 | USD |
| E due: 30 September 2026 | 30 September 2026 | Date |
| A week 1 promise | 3000 | USD |
Required deliverables
Produce invoice-level aging, a weekly collection plan and an exception log. Use conditional aging formulas so buckets recalculate when the as-of date or due dates change. 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 notes
Invoice aging - USD
| Invoice | Net AR | Due date | Days overdue |
|---|---|---|---|
| A | 5,000.00 | 10 Sep 2026 | 20 |
| B | 3,500.00 | 25 Sep 2026 | 5 |
| C | 3,000.00 | 10 Oct 2026 | 0 |
| D | 2,000.00 | 15 Aug 2026 | 46 |
| E | 1,000.00 | 30 Sep 2026 | 0 |
Collection calendar - USD
| Invoice | 1-7 Oct | 8-14 Oct | 15-21 Oct | Outside plan |
|---|---|---|---|---|
| A | 3,000.00 | 0.00 | 2,000.00 | 0.00 |
| B | 0.00 | 3,500.00 | 0.00 | 0.00 |
| C | 0.00 | 0.00 | 3,000.00 | 0.00 |
| D | 0.00 | 0.00 | 0.00 | 2,000.00 |
| E | 1,000.00 | 0.00 | 0.00 | 0.00 |
| Total | 4,000.00 | 3,500.00 | 5,000.00 | 2,000.00 |
Aging summary - USD
| Bucket | Amount |
|---|---|
| Current | 4,000.00 |
| 1–30 days | 8,500.00 |
| 31–60 days | 2,000.00 |
| Over 60 days | 0.00 |
| Aging reconciliation | 0.00 |
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. The total duration of 45 minutes is a planning estimate, not a tested completion time. Allocate class time according to prior knowledge and reserve time for discussion of the decision and evidence.
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.
Decision
Confirm the promised collections and resolve invoice D with the account owner; keep disputed cash outside the committed plan.
Evidence and reasoning
Aging measures lateness, not the probability or date of collection. A credit note reduces the collectible balance, while a dispute requires evidence rather than an assumed write-off.
What would change the conclusion
A revised customer promise, approved credit or resolved dispute changes the collection calendar. Date changes must also recalculate the aging buckets.
Related learning
Version 1.1. Synthetic teaching case. Completion time is an estimate.