Expert guide
Month-End Close Checklist in Excel
Every task with an owner, a due day and evidence, and a clear view of what slows your close
Call us at +1 (385) 386-3860
Short answer: list every close task in an Excel Table with area, owner, reviewer, due working day, dependency, status, completed date and evidence link. Calculate actual due dates from period end with WORKDAY, flag overdue and blocked tasks, and show progress by area. Keep a separate reconciliation register for every balance sheet account. Copy the checklist each month as the audit trail, and track days taken per task to pick automation targets. Data gathering is usually the first thing to automate with Excel data consolidation.
A slow month-end close is rarely about hard accounting. It is about waiting: for the bank export, for a manager to approve an accrual, for someone to send the payroll file, for the one person who knows how the intercompany schedule works. A checklist makes the waiting visible, with an owner and a due day on every task, and that alone usually takes days off the close. It also shows exactly which steps are worth automating.
The Checklist Structure
| Column | Example |
|---|---|
| TaskID, Area | C-14, Cash |
| Task | Reconcile main operating account to bank statement |
| Owner, Reviewer | AJ, MR |
| DueWD | 2 (second working day after period end) |
| DependsOn | C-02 (bank feed imported) |
| Status | Not started / In progress / Blocked / Done / Reviewed |
| CompletedOn, ReviewedOn | Dates |
| Evidence | Link to the reconciliation file |
| Minutes | Time actually spent, for automation decisions |
A Typical Task List by Area
Cut-off (WD0–1)
All sales invoiced, purchase invoices entered, expense claims processed, payroll posted.
Cash (WD1–2)
Bank and credit card reconciliations for every account.
Receivables and payables (WD2)
Sub-ledger to control account reconciliations; aged debt review; unmatched receipts.
Accruals and prepayments (WD2–3)
Expected but uninvoiced costs, prepayment release schedule.
Fixed assets (WD3)
Additions, disposals, depreciation run and register reconciliation.
Inventory and cost of sales (WD3)
Stock valuation, adjustments, cost of sales review.
Payroll and tax (WD3)
Payroll liabilities, tax accounts reconciled to filings.
Review (WD4)
Trial balance review, flux analysis against last month and budget, adjustments.
Reporting (WD5)
Management pack, commentary, sign-off, period lock.
Formulas That Run the Close
Period end in Settings!PeriodEnd, holidays in tblHolidays[Date].
Due date:
=WORKDAY(Settings!PeriodEnd, [@DueWD], tblHolidays[Date])
Blocked by dependency:
=IF([@DependsOn] = "", FALSE,
XLOOKUP([@DependsOn], [TaskID], [Status]) <> "Done")
Overdue:
=AND([@Status] <> "Done", [@Status] <> "Reviewed", TODAY() > [@[Due date]])
Days late (completed tasks):
=IF([@CompletedOn] = "", "", NETWORKDAYS([@[Due date]], [@CompletedOn], tblHolidays[Date]) - 1)
Progress by area (area in A2):
=COUNTIFS(tblClose[Area], A2, tblClose[Status], "Reviewed") / COUNTIFS(tblClose[Area], A2)Conditional formatting turns Overdue red and Blocked amber. A status board of areas against working days, coloured by progress, gives the controller a one-glance view during the close.
The Reconciliation Register
| Column | Purpose |
|---|---|
| Account, Description, Risk (High/Med/Low) | Every balance sheet account, with risk driving frequency |
| GL balance | From the trial balance export |
| Supporting balance | From the bank, sub-ledger or schedule |
| Difference | =GL − Supporting |
| Explained / Unexplained | Reconciling items with notes and expected clear dates |
| Preparer, Reviewer, Dates, Evidence link | Control evidence |
Pull GL balances automatically from the trial balance export with XLOOKUP on account number. The register then shows which accounts are unreconciled before anyone opens a file.
Measuring and Shortening the Close
Close duration: last task's reviewed date minus period end, in working days, per month.
Late tasks by owner and area: shows bottlenecks and overloaded people.
Minutes per task: the long, repetitive ones are your automation list.
Unexplained reconciliation differences carried forward: a control warning sign.
| Common slow task | Automation |
|---|---|
| Downloading and formatting bank and card statements | Bank feeds or Power Query imports from a folder |
| Matching transactions | Rule-based matching plus AI for leftovers |
| Building accrual schedules | Template driven by open purchase orders and recurring costs |
| Consolidating entities or branches | Automated consolidation workbook |
| Writing variance commentary | AI draft from a variance table, reviewed by the controller |
Transaction categorisation is covered in categorising bank transactions with AI; commentary in automating report commentary; and multi-entity data in Excel data consolidation.
Running It Month After Month
Keep a master checklist; each month, copy it to a new sheet or append rows with a Period column.
Review the master quarterly: add tasks for new accounts, remove obsolete ones.
Lock completed months; corrections are new adjusting entries, not edits to history.
Share the board in the team's weekly meeting during the close.
For accounting firms closing books for many clients, the same structure scales with a client column; see the client document tracker for the intake side. A monthly management dashboard on top of the closed numbers is an Excel dashboard project.
FAQ
Month-end close checklist FAQ
Tasks, reconciliations and automation