Summary
This project integrated a recurring month-close workflow into standardized closed ledger based Power Query. This approach reduced manual trial balance processing, improved reconciliation visibility, and created a upgradable management reporting pack rebuildable on demand (with controlled inputs and documented ownership).
The design principles followed a finance-first approach: keep the model reviewable, keep ownership clear, and automate only where control and verification remain practical.
Customer Profile
- Type: A medium-sized company providing many organizations
- Structure: 8 legal entities
- Month-end close target: 5 working days
- Reporting: a management package was created monthly for management review
Tasks
Packaging was very manual and dependent on processing individual files. Key issues include:
- Trial balance exports, copying and changing the file structure every month, entity by entity
- Accrual schedules stored in separate files with inconsistent update practices
- Reconciliations tracked in spreadsheets without a single view of states
- Management pack hand built, with limited one-click rebuildability
- Mismatched version file and unclear "source of truth" when browsing
- Late review caused by rework, missing signatures, and dependency on multiple power users
Solution overview
The solution focused on a standardized month-end close workbook with controlled inputs, import automation, and a status layer that supports finance review without hiding exceptions.
Main components
- Standardized closing ledger (structure, naming, tabs, control fields)
- Power Query imports for trial balances and supporting schedules
- Reconciliation status dashboard (owner, execution date, status, aging)
- Automatic deviation checks (thresholds, transition, reason codes)
Close result package
- Controlled Accrual Schedules (Inputs, Approvals, Reversals, Documentation Links)
- Refreshable Management Reporting Package (KPIs, variance bridges, Entity Views)
- Repetitive update workflow (import → validate → view → publish)
- Defined file management to reduce parallel versions and improve the audit trail
Six steps of the implementation process
- Close workflow display: documented current closure stages, dependencies and review points (including where "exclusions live").
- Data and export standardization: consistent export and naming formats for the trial balance and supporting cross-entity extracts.
- Workbook architecture: developed a controlled template (inputs, calculations, validations, outputs) with consistent signature fields.
- Building Power Query: created updatable queries, transformations, and parameters for consistent loading of entity data.
- Management level: implemented validations deviations, completeness checks, compliance status tracking, and required owner notes for exceptions.
- Learning and Transfer: identified owners for each module, provided usage instructions, and established "1 month of support" to stabilize behavior.
The goal was not to to eliminate financial evaluation, but to reduce mechanical effort give reviewers a clearer picture of what has changed, what needs to be explained, and what has been completed.
Control and ownership
- Named owners for each closed module (TV import, accrual, key reconciliation, reporting results)
- Clear signatures with dates and reviewer fields
- Locked structure where possible (to reduce accidental breakage) while keeping input editable
- Transitional link documentation built-in for material accruals and reconciliations
Exceptions and human validation
Automation is designed to detect problems, not hide them. Workflow explicitly supported:
- Exception Queues (failed items or missing documents)
- Reason codes and brief commentary for key deviations
- Materiality thresholds aligned with internal review expectations
- Manual override with tracking (who changed what and why)
Practical Excel + Power Query Stack
This engagement used a pragmatic tool stack that most finance teams can and will support:
Excel (model + controls)
- Structured input sheets with validation and required fields
- Regressive and controlled accrual schedules
- Outlier checks and consistency checks
- Management pack output (tables, pivot charts if needed)
Power Query (import + transform)
- Sequential trial balance submissions across entities
- Standard transformations (display, shape change, data entry)
- Options to support multiple entity update patterns
- Update workflow coordinated with close time and need reviewer
Results (illustrative estimates)
The estimates below are representative of the team's workflow design and implementation models. Results vary based on object complexity, data quality, and review cycles.
Preparation Time
Closed Package Build Reduction
From 24-30 hours to 7-9 hours per month by standardizing input data, automating imports and reducing manual package assembly.
Speed of reporting
Reporting available earlier
Management reporting was typically available around 1.5 days earlier due to faster update cycles and clearer exception handling.
Reliability
Fewer version errors
Reduced revision of competing file versions thanks to controlled templates, update rules and clearer ownership during review.
Results
- Standardized workbook (inputs, checks, outputs)
- Power Query import level for trial balance and supporting statements
- Reconciliation status dashboard and view tracker
- Accrual charts with controlled templates and documentation fields
- Updated Management Reporting Package
- Ownership matrix and update/runbook review
Lessons learned
- File management matters: fastest model still doesn't work if people can't identify the source of truth.
- Automate reviews, not judgements: reviewers need clear exceptions, not a black box.
- Design for implementation: Keep steps repeatable for the whole team, not just power users.
- Close is a system: improve when ownership, deadlines and reviews are clear.
Want to apply this to your closing?
If your team spends a lot of time building a closed package, working with multiple versions of files, or manually rebuilding reports, we can help you develop a controlled, updatable workflow that fits your existing tools and controls.
Important: This case study is representative and anonymous. Saving time and improving time Illustrative Estimates; actual results depend on system, data quality, object complexity, and review/approval requirements.