AUTOMATION · PYTHON · SQL · Manufacturing group, five entities
CloseFlowMonth-End Close Automation
Nine working days and two late nights — that was month-end for a manufacturing group with five legal entities. Nothing was broken exactly: 61 individually reasonable manual steps, chained together with no way to run any of them twice safely. I rebuilt the pipeline so the close is a checklist with a status board rather than a relay race.
- 9 → 3 working days to close, across five entities
- −142h manual finance effort per close cycle
- 61 manual steps replaced by 9 idempotent jobs
01 — Context
The problem.
The close depended on 61 manual steps spread across five entities and four people. Intercompany balances were reconciled by exporting two ledgers to Excel and eyeballing them; the consolidation workbook was a 40-tab file whose formulas nobody was willing to touch; and if a step was run twice, it double-posted. That last property is why nobody dared restart anything, so a single error cost half a day.
The team had asked for more headcount. What they actually needed was for the repeatable 80% to stop consuming the judgement-based 20%.
02 — Method
The approach.
The build, in the order it happened.
-
Mapped and timed all 61 steps
Two weeks shadowing a live close, recording each step’s owner, duration, inputs and failure mode. Eleven steps turned out to be duplicated between entities and four produced output nobody read — those were deleted before a line of code was written.
-
Made every job idempotent
Each automated step writes to a staging table keyed on entity and period, then swaps atomically. Re-running a job is always safe, which is what finally made restarting a failed close a non-event instead of a crisis.
-
Automated reconciliation with a tolerance ledger
Intercompany and bank reconciliations run in Python against both ledgers, with materiality thresholds agreed with the controller. Matches clear silently; breaks land in a queue with both source rows attached, so the accountant starts from the exception rather than the export.
-
Replaced the 40-tab workbook
Consolidation logic moved into version-controlled SQL with a test per elimination rule. The workbook survives as a formatted output only — it no longer contains any logic, so no one has to be afraid of it.
03 — Outcome
What changed.
- Close time fell from 9 working days to 3 over six cycles, with no cycle regressing.
- Around 142 hours of manual finance effort removed per cycle, redeployed onto variance analysis and forecasting.
- Reconciliation breaks are now surfaced on day one rather than discovered on day six; average break count per close fell from 38 to 9 as upstream causes became visible.
- No late nights in any of the six cycles since go-live.
04 — The numbers
Working days to close, by cycle.
Tracked as working days, from Baseline through Cycle 6 — low 3, high 9.
05 — Screens
What it looks like in use.
The exception queue. Matched items clear silently; the accountant only ever sees the breaks, with both source rows attached.
Before and after. Eleven duplicated steps and four unread outputs were deleted rather than automated.
06 — Stack & role
Built with.
- Python (Pandas, openpyxl)
- SQL Server
- Excel
- Power Automate
- Git
- Role
- Consulting automation engineer — process mapping, build, handover
- Duration
- 12 weeks
- Client
- Manufacturing group, five entities
- Period
- 2025
07 — Questions
The questions I get asked about this one.
Yes — accruals, provisions and anything requiring judgement. The goal was to give those steps more room, not to pretend a script can make an estimate.
Every job logs its inputs, row counts and the period it wrote to, and the staging-then-swap pattern means each posted figure traces back to an immutable run. Auditors get a run log rather than a reconstruction.