📌 Stage 5 · Collections & Follow-up · Invoicing & AR · Monthly Close and Rollover · 35–45 min
Running the close, rolling to October, and knowing the limits
Learning goals
- Run the month-end close routine in order: post, set, rebuild, review, reconcile, act
- Roll the workbook to a new month by changing three Settings cells
- State the model's boundaries and when to graduate to dedicated accounting software
Concepts
The close routine
A collection report is only as good as the routine behind it. Cedar & Co.'s takes fifteen minutes on the first business day of the month, always in the same order. One: post every receipt that landed - including stragglers dated after the period end. Two: confirm Settings - period Sep 1-30, as-of Sep 30, 2026. Three: rebuild the Dunning worklist for the still-open invoices. Four: review the Aging grid for concentrations and anything climbing past 60 days. Five: reconcile - the aging grand total must equal open receivables ($36,600.00 = $36,600.00). Six: act on the report - escalate the two 60+ accounts, lift or apply holds, and file the numbers.
The reconciliation step is the routine's spine. Two independently computed totals agreeing means the register, the buckets, and the formulas all still tell one story.
=SUM(Invoices!$K$5:$K$1004)Open receivables straight off the register - the number the Aging grid's grand total must reproduce. If Aging!I11 ever disagrees with this cell, an invoice lost its customer code or a Days Overdue cell turned into text.
Rolling to October
The workbook has no archive ritual and no reset button because it needs neither. At the October close, change three cells: period start to Oct 1, 2026, period end to Oct 31, 2026, as-of to Oct 31, 2026. Everything re-derives. September's invoices that were 'not yet due' cross their due dates and start aging; INV-1117 (due Oct 14) becomes overdue; October invoices and receipts flow into the period rows; DSO recomputes against October's invoicing.
Keep every old row - paid invoices are the audit trail, and deleting them to 'clean up' also deletes the history behind past reports. Capacity is the only maintenance: formulas aggregate rows 5 through 1004, and the downloadable template pre-fills formula columns to row 204, so when a journal approaches that line, select the last formula row, fill down, and extend the 5:1004 ranges to match.
💡 Tips:
- File a PDF of each month's Collections sheet next to the workbook - the filed copy never re-derives, which is the point.
- October is also the natural moment to re-check credit limits against actual payment behavior, not just balances.
Boundaries and graduation
This workbook does one job extremely well: it tells a single user who owes what, how late, and what to do next. It does not do sales tax (amounts exclude it; confirm your state's treatment with your state Department of Revenue), formal credit notes, chargebacks, customer prepayments, multi-currency, multi-user permissions, approval workflows, audit logs, or automatic backups. Disputes and payment plans live in your accounting system; this sheet tracks the balance while they resolve.
Graduate to dedicated accounting or AR software when any of these bite: two or more people need to post simultaneously, you need GAAP-basis reports for a lender, volume passes a few hundred invoices a month, or you find yourself wanting audit trails more than formulas. Until then, a disciplined workbook beats an unused app.
The finished system is downloadable below this lesson in the last-lesson card: the case workbook with every live formula, and a blank template with formula columns pre-filled and guarded down to row 204. Both are plain .xlsx with no macros of any kind.
💡 Tips:
- Every formula in the course is classic (SUMIFS, VLOOKUP, IF, ROUND) - the files open and recalculate in any Excel since 2010, LibreOffice Calc, and Google Sheets.
- Take the template, replace six customers with your own, and your first real close is an hour away.
Practice
Open on Collections - the September 2026 collection report, twelve rows, one story. The exercise ends by re-timing the whole model to October.
- Read the report top to bottom — Rows 5-16: invoiced 20,300.00, collected 12,950.00, open 36,600.00, overdue 22,000.00 (60.1%), DSO 54.1 vs target 45 (gap 9.1), then the counts - 5 raised, 4 settled, 6 receipts, 2 holds.
- Run the reconciliation — Switch to Aging and confirm I11 reads 36,600.00, then to Invoices and add the nine open balances - the same 36,600.00. Three sheets, three routes, one number: the close is provably consistent.
- Simulate the October close — On Settings, set C5 to Oct 1, 2026, C6 to Oct 31, 2026, C7 to Oct 31, 2026. On Invoices, INV-1117 (due Oct 14) is now Overdue by 17 days; on Dunning it has climbed to Phone AP contact; Collections' period rows now measure October - the September report you just read is gone until you roll back.
- Roll back and finish — Click Reset Data to restore the September close, then use the download card below to take the case workbook and the blank template with you.
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.
Practice files: en-invoicing-ar-followup-case.xlsx, en-invoicing-ar-followup-template.xlsx (see the attachments section at the bottom of this page).
Checklist
Work through each item; when every box passes, this lesson is done:
- ☐ Read all twelve report rows and traced each to a formula built earlier in the course
- ☐ Reconciled the close: aging grand total 36,600.00 equals open receivables on both the register and the report
- ☐ Rolled the model to October 2026 and watched INV-1117 flip to Overdue, then restored the September close
- ☐ Noted the final baseline: collected 12,950.00 of 20,300.00 invoiced, 36,600.00 open, 22,000.00 overdue, DSO 54.1, 2 credit holds
- ☐ Downloaded (or noted the location of) the case and template .xlsx workbooks