The Month-End Excel Routine: A Checklist You Can Hand Over
A close is not a list of reports. It is a sequence with dependencies, and the dependencies are where it goes wrong — because they live in one person's head rather than on a sheet.
Short answer
Why month-end goes wrong
- The sequence is in somebody's head. They know that the bank reconciliation has to finish before the vendor reconciliation makes sense. Nobody wrote it down, so nobody else can start.
- There is no cut-off. Data keeps arriving. The file is refreshed at 4pm, then again at 6pm, and the two versions do not agree.
- The files have no naming convention. Three versions of the register, one of them called “final” and one of them called “final (2)”.
- Exceptions accumulate. A small unresolved item each month is a large one after twelve months, and by then nobody can trace it.
- Closed periods get reopened. A figure is changed after the pack has gone out, with nothing recording that it happened or why.
Stage 0: freeze the period
| Decision | What to write down |
|---|---|
| Cut-off | The date and time after which nothing further is included — and, explicitly, who is allowed to make an exception |
| Folder | One folder per period, named to a convention, with the source files and the working file inside it |
| File naming | One pattern, applied to every file — period, purpose, version |
| Read-only copy | A copy of each source file taken at cut-off, so the reconciliation is against a fixed input |
Copy the source files at cut-off
Stage 1: collect
The collection stage produces two things: the data, and a record of what was expected.
| Expected input | Source | Owner | Received |
|---|---|---|---|
| Bank statements — all accounts | Net banking download | Accounts | Yes |
| Sales register | Billing system export | Billing | Yes |
| Purchase register | Accounts payable export | AP | Yes |
| Vendor statements — top 10 by value | Requested from vendors | AP | 8 of 10 |
| GSTR-2B for the period | GST portal | Tax | Yes |
| TDS challans and return data | Challan status and return preparation file | Tax | Yes |
| Stock and inventory summary | Stores | Stores | Pending |
| Payroll cost summary | Payroll system | HR | Yes |
Two of those lines are not received, and the table is the reason you know that. A close that slips is usually a close where nobody tracked what was outstanding — the work simply stopped, silently, waiting for something that never appeared.
Stage 2: load and standardise
Point the queries at the new period's files
Check row counts before you use the data
Apply the standard clean-up
Keep the source file name on every row
Stage 3: reconcile in dependency order
The order matters because later reconciliations depend on earlier ones being right. Running them in a different order means redoing work.
| # | Reconciliation | Why it is in this position | The proof that it is finished |
|---|---|---|---|
| 1 | Bank | Establishes what actually moved; every other balance is affected by it | Statement balance plus or minus listed items equals the book balance |
| 2 | Vendor and customer balances | Feed directly into receivables and payables | The difference column totals to the gap between the two sides |
| 3 | GST — 2B against the purchase register | Depends on the purchase data being complete | Every exception carries a category and the totals on both sides are stated |
| 4 | TDS — ledger, challans and return | Depends on the payment data being complete | Section-wise totals agree across books, return and challans |
| 5 | Control accounts and intercompany | Depends on all of the above | Each control account agrees to its supporting sub-ledger |
| 6 | Trial balance | Depends on everything | It balances, and each of the above reconciliations ties into it |
Every reconciliation needs an arithmetic proof
Stage 4: review the exceptions
| Exception | What to decide |
|---|---|
| Timing difference | Leave it, and note when it is expected to clear |
| Requires a correction | Raise it; do not adjust the reconciliation to make it balance |
| Requires chasing a third party | Who chases it, and by when |
| Requires writing off | Who approves write-offs, and at what value |
| Carried forward from an earlier period | Why it is still open — the reason nobody has resolved it is itself a finding |
The last line is worth reading twice. An item that has appeared on four consecutive reconciliations is not a reconciliation issue; it is a process failure that the reconciliation is faithfully reporting every month.
Stage 5: report
The reporting pack should be built from the reconciled figures, not assembled separately and checked afterwards. If the same number exists in two places in the pack, it should be a reference rather than a retyped value.
Stage 6: lock and archive
- Save a read-only copy. The working file stays editable; the archived copy does not. Future changes go in the next period with an explanation.
- Keep the checklist with the file. The control tab for this period should record who ran each stage and when. That record is what turns a spreadsheet into evidence.
- Note the exceptions that were accepted. Not just the ones that were resolved. Accepted differences are the ones that get questioned a year later.
The control tab
Put one sheet at the front of the month-end workbook. It carries the checklist, the status of each stage, and the totals that must agree.
| Control | This period (₹) | Supporting sheet | Agrees? |
|---|---|---|---|
| Bank balance per books | 4,18,206 | Bank recon | Yes |
| Bank balance per statement | 3,68,930 | Bank recon | 49,276 explained |
| Vendor payables per books | 18,42,910 | Vendor recon | Yes |
| Vendor payables per statements | 17,96,400 | Vendor recon | 46,510 explained |
| Net GST position | 2,14,380 | GST recon | Yes |
| TDS payable after set-off | 0 | TDS control | Yes |
| Trial balance total | 2,84,17,002 | Trial balance | Balanced |
Anyone can read that sheet in fifteen seconds and know whether the period is closed. That is the whole point of it — the alternative is a folder of working papers and a conversation.
The handover test
The only test that matters
What breaks the routine
- Late data with no record of what is outstanding. The collection table from Stage 1 is the fix, and it costs five minutes.
- A large exception list. It is large because it was not worked last month. The monthly cadence is what keeps it small.
- Manual corrections outside the process. If somebody adjusts a figure in the pack without going through the reconciliation, the control tab will not agree and the reason will not be recorded. Every adjustment should route through the working paper that supports it.
- Reopening a locked period. Allow it, but require the reason to be written on the control tab. An unexplained reopen is indistinguishable from an error.
- The routine being rebuilt every month. If the imports, the cleaning and the matching are all done by hand each period, the close time is whatever the volume dictates. That is the case for automating the mechanical stages — which is what automating repetitive Excel tasks is about.
Where automation fits in the close
Stages 0, 1, 4, 5 and 6 are judgement and record-keeping, and they should stay manual — they are where an accountant adds value. Stage 2 and Stage 3 are largely mechanical: loading files, cleaning them, matching two sets of records and categorising the differences.
That mechanical half is what Piloteq Automate is for — saved match rules applied the same way every period, so the exception list you review is short and each item already has a reason recorded against it. The judgement stays with you; the rebuild disappears.
If you would rather not build this by hand
Take the matching step out of the close
Piloteq Automate handles the repetitive matching inside a reconciliation — applying the same key, tolerance and group rules every period and writing a reason against each result, so the exception list you review is short and explained.
- ✓The same saved rules applied every period
- ✓Group matching for one payment across many invoices
- ✓Missing records reported for each side
- ✓An exception list with the reason recorded
Frequently asked questions
What should a month-end Excel routine include?+
Six stages: freeze the period and set a cut-off, collect the data and record what has arrived, load and standardise it, run the reconciliations in dependency order, review the exceptions, then report and lock the file. The stage people leave out is the last one — archiving the period as a read-only copy — which is what stops a closed month being changed after it has been reported.
In what order should month-end reconciliations be done?+
In dependency order, not in the order they appear on a list. Bank first, because it establishes what actually moved. Vendor and customer next, because those balances feed the receivables and payables figures. Then the statutory reconciliations — GST and TDS — because they depend on the transaction data being complete. Then the control account reconciliations, and finally the reporting pack, which is built from everything above it.
How do I stop month-end work depending on one person?+
Write the sequence down as a checklist that names the input, the output and the check for each step, and keep it in the workbook itself on a control tab. Then have somebody else run it once while the person who normally does it watches. The test is not whether it can be understood by reading — it is whether somebody else can produce the same output.
What is a control tab in a month-end workbook?+
A single sheet at the front of the workbook listing the steps, their status, and the key totals that must agree — the bank balance, the vendor balance, the net GST position, the TDS position and the trial balance total. It is the first place a reviewer looks, and it turns the close from a set of separate tasks into one statement that can be checked at a glance.
Why does my month-end close keep slipping?+
Usually one of three things: data arrives late and nothing was recorded about when it was expected, the exception list is large because it has not been worked monthly, or the same manual corrections are made every month outside the process instead of being fixed at the source. The third is the one that never improves on its own.
Should I automate my month-end process?+
Automate the mechanical steps — combining files, cleaning exports, refreshing the standard reports — and leave the review and the judgement manual. The value an accountant adds in a close is deciding what a difference means; the value of automation is removing the repeatable work that sits in front of that decision.