Executive summary
This project packaged a repeatable month-end close workflow into a standardized close workbook powered by Power Query. The approach reduced manual trial balance handling, improved reconciliation visibility, and produced a refreshable management reporting pack that could be rebuilt on demand (with controlled inputs and documented ownership).
Design principles were finance-led: keep the model auditable, keep responsibilities clear, and automate only where controls and review remain practical.
Client profile
- Type: Mid-sized, multi-entity services company
- Structure: 8 legal entities
- Close target: 5 business days
- Reporting: Management pack built monthly for leadership review
The challenge
Close package assembly was highly manual and dependent on individual file handling. Key pain points included:
- Trial balance exports copied and reshaped each month (entity-by-entity)
- Accrual schedules maintained in separate files with inconsistent refresh practices
- Reconciliations tracked across spreadsheets without a single status view
- Management pack assembled manually, with limited “one-click rebuild” capability
- Inconsistent file versions and unclear “source of truth” during review
- Late review caused by rework, missing sign-offs, and dependency on a few power users
Solution overview
The solution centered on a standardized close workbook with controlled inputs, import automation, and a status layer that supports finance review without obscuring exceptions.
Core components
- Standardized close workbook (structure, naming, tabs, control fields)
- Power Query import for trial balance and supporting extracts
- Reconciliation status dashboard (owner, due date, status, aging)
- Automated variance checks (thresholds, rollforwards, reason codes)
Close package outputs
- Controlled accrual schedules (inputs, approvals, reversals, documentation links)
- Refreshable management reporting pack (KPIs, variance bridges, entity views)
- Repeatable refresh workflow (import → checks → review → publish)
- Defined file governance to reduce parallel versions and improve audit trail
Six-step implementation process
- Close workflow mapping: documented current close steps, dependencies, and review points (including where “exceptions live”).
- Data and export standardization: aligned export formats and naming for trial balance and supporting extracts across entities.
- Workbook architecture: designed a controlled template (inputs, calculations, checks, outputs) with consistent sign-off fields.
- Power Query build: created refreshable queries, transformations, and parameters to load entity data consistently.
- Controls layer: implemented variance checks, completeness checks, reconciliation status tracking, and required owner notes for exceptions.
- Training and handover: defined owners per module, delivered usage guidance, and set a “month 1 support” cadence to stabilize behavior.
The intent was not to eliminate finance judgment—it was to reduce mechanical effort and give reviewers a clearer view of what changed, what needs explanation, and what is complete.
Controls and ownership
- Named owners for each close module (TB import, accruals, key reconciliations, reporting outputs)
- Clear sign-offs with dates and reviewer fields
- Locked structure where practical (to reduce accidental breakage) while keeping inputs editable
- Source documentation links embedded for material accruals and reconciliations
Exceptions and human review
Automation was designed to surface issues—not hide them. The workflow explicitly supported:
- Exception queues (items failing checks or missing documentation)
- Reason codes and short commentary for key variances
- Materiality thresholds aligned with internal review expectations
- Manual override with traceability (who changed what and why)
Practical Excel + Power Query stack
This engagement used a pragmatic tooling stack that most finance teams can support and maintain:
Excel (model + controls)
- Structured input sheets with validation and required fields
- Rollforwards and controlled accrual schedules
- Variance checks and reconciliation completeness checks
- Management pack outputs (tables, pivots, charts where appropriate)
Power Query (import + transformation)
- Consistent trial balance ingestion across entities
- Standard transformations (mapping, reshaping, data typing)
- Parameters to support multi-entity refresh patterns
- Refresh workflow aligned with close timing and reviewer needs
Outcomes (illustrative estimates)
Estimates below are representative of the workflow design and team adoption patterns. Results vary based on entity complexity, data quality, and review cycles.
Preparation time
Close package build reduced
From 24–30 hours to 7–9 hours per month by standardizing inputs, automating imports, and reducing manual pack assembly.
Speed to reporting
Reporting available earlier
Management reporting was typically available about 1.5 days earlier due to faster refresh cycles and clearer exception handling.
Reliability
Fewer version errors
Reduced rework from competing file versions through controlled templates, refresh rules, and clearer ownership during review.
Deliverables
- Standardized close workbook (inputs, checks, outputs)
- Power Query import layer for trial balance and supporting extracts
- Reconciliation status dashboard and review tracker
- Accrual schedules with controlled templates and documentation fields
- Refreshable management reporting pack
- Ownership matrix and refresh/review runbook
Lessons learned
- File governance matters: the fastest model still fails if people can’t identify the source of truth.
- Automate checks, not judgment: reviewers need clear exceptions, not a black box.
- Design for adoption: keep steps repeatable for the whole team, not just power users.
- Close is a system: improvements stick when ownership, deadlines, and reviews are explicit.
Want to apply this to your close?
If your team is spending significant time assembling the close package, dealing with multiple file versions, or rebuilding reporting manually, we can help you design a controlled, refreshable workflow that fits your existing tools and governance.
Important: This case study is representative and anonymized. Time savings and timing improvements are illustrative estimates; actual outcomes vary based on systems, data quality, entity complexity, and review/approval requirements.