Learning goals

Trace a partial payment from the journal through the register to the reports
Explain why the original invoice amount is never edited after the fact
Distinguish ledger truth (who owes what, ever) from period truth (what happened in September)

Concepts

How application works

Application is just addition with a key. INV-1094 (Pinewood Legal Group LLC, $6,200.00, due Jul 22, 2026) received $2,000.00 on Aug 14 and $1,200.00 on Sep 25. The register's SUMIFS found both rows by invoice number and summed them: $3,200.00 applied, $3,000.00 still open, status still Overdue - now 70 days past due.

INV-1108 (Kestrel Outdoor Gear, $9,600.00) tells the same story at larger scale: one $4,000.00 ACH on Sep 18, $5,600.00 open. And INV-1119 (Two Rivers Coffee) shows the friendly version - $1,000.00 paid against a $2,400.00 invoice that is not even due yet, so its status is Partial, not Overdue.

No special 'partial payment' feature exists anywhere in the workbook. That is the point: a payment is a payment, and the arithmetic absorbs any number of them.

=SUMIFS('Cash Receipts'!$F$5:$F$1004,'Cash Receipts'!$C$5:$C$1004,">="&Settings!$C$5,'Cash Receipts'!$C$5:$C$1004,"<"&(Settings!$C$6+1))

The period version of the SUMIFS from Lesson 6: sum receipt amounts (F) whose date (C) falls on or after the period start and strictly before the day after the period end - a half-open window that includes Sep 30 itself. This is exactly the Collections report's 'Collected in period' cell: 12,950.

Never edit the original invoice

When a client underpays, the tempting move is to change the invoice amount to what they sent. Never do it. The register's Amount column is the historical fact both sides agreed to; editing it destroys the audit trail, silently forgives the difference, and breaks every report that already went out.

The correct responses all live outside the amount column: record the partial payment and keep the status honest (what this workbook does); negotiate and document a payment plan in your accounting system; or issue a formal credit note if you genuinely are reducing the bill. The workbook keeps the original intact so the story stays checkable.

💡 Tips:

If a client disputes a line, resolve the dispute - do not let the register become the negotiation.

Part-paid and past due is the most common real-world state for troubled accounts; the ladder naming it Overdue (not Partial) is what makes the phone get dialed.

Ledger truth vs period truth

Two different true statements coexist in this workbook. Ledger truth: INV-1115 (Summit Ridge Dental, $2,800.00) is Paid - the money arrived Oct 2 and the register says so forever after. Period truth: September's collections were $12,950.00, because the Oct 2 receipt falls outside the September window and the period formulas exclude it.

The mirror case: PMT-4021 ($2,000.00, Aug 14) counted toward August's collections, yet it still reduces INV-1094's open balance today. Payments keep working forever (ledger) but each belongs to exactly one reporting month (period). Confusing the two is how double-counting happens; keeping them separate is what makes the September report defensible.

💡 Tips:

The period window uses < (period end + 1) rather than <= period end so it survives date-only values; the effect is identical here.

When a client pays an invoice two days after your close, nothing is wrong - it will simply headline next month's collections.

Practice

Open on Invoices, with the Cash Receipts journal one tab away. Three invoices carry the partial-payment story; the period formulas decide what September saw.

Trace INV-1094 end to end — On Cash Receipts read PMT-4021 and PMT-4029. On Invoices row 6, confirm Paid Amount 3,200.00, Open Balance 3,000.00, Status Overdue, Days Overdue 70.
Meet the Partial state — Invoices row 17 (INV-1119): 1,000.00 of 2,400.00 applied, due Oct 24, 2026, status Partial. Compare with row 9 (INV-1108): also part-paid, but past due, so Overdue wins.
Watch the period filter work — On Collections, row 6 (Collected in period) reads 12,950.00 - the six September receipts only. The Aug 14 and Oct 2 rows exist in the journal yet are invisible to this number. That is period truth doing its job.
Reconcile the ledger — Add every receipt in the journal: 17,750.00 total applied across all time. Subtract it from 54,350.00 total invoiced and you land on 36,600.00 - the open receivables the report carries.

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:

☐ Traced INV-1094 from two journal rows to a 3,200.00 paid / 3,000.00 open / Overdue register row
☐ Identified INV-1119 as the live Partial (1,000.00 of 2,400.00, not yet due) and INV-1108 as Overdue-despite-partial
☐ Confirmed September collections read $12,950.00 while the Oct 2 receipt still settles INV-1115 in the ledger
☐ Noted the baseline: total invoiced 54,350.00 minus total applied 17,750.00 equals open receivables 36,600.00

🧾 Lesson 08 · Partial payments and application