A Multi-Entity Organisation Managing Financial Reporting Across 45 Entities
A Sydney-based healthcare organisation managing monthly financial reporting across 45 entities. The recurring reporting volume created a growing need for faster, more consistent financial statement preparation. reporting consistency, and formula reliability important operational concerns for the finance team.
A Repetitive Financial Statement Process That Had to Be Rebuilt for Every Entity
Monthly financial statement preparation required the finance team to work across multiple files, repeat the same manual steps, and carry out extensive checks for all 45 entities. The process was time-consuming, difficult to standardise, and heavily dependent on manual handling to keep reporting accurate and consistent.
Manual Trial Balance Preparation
The source Trial Balance contained numerous general ledger account tabs. Team members had to tidy, rename, and format those worksheets individually before using them in the reporting process. Repeating this work across the full portfolio added a significant amount of routine effort to every monthly close.
Separate Balance Sheet and Profit & Loss Processing
The Balance Sheet and Profit & Loss statement relied on separate macros or manual input. The team had to move between workbooks and manage each statement independently, increasing the number of steps required to complete one reporting package.
Account-by-Account General Ledger Linking
Each Balance Sheet and Profit & Loss line had to connect to the relevant general ledger worksheet. Manual linking or account lookups made the process slow and introduced the possibility of linking a reporting line to the wrong source value.
Repeated Margin Calculations and Formatting
Gross profit margin and EBITDA percentages required manually inserted formulas. Fonts, colours, and currency formats also had to be reapplied after each export. Variations between entities increased the risk of formula references or presentation formats becoming inconsistent.
Manual Workbook Consolidation
The team ultimately needed one complete workbook, but the reporting process began with three separate files. Staff copied worksheets into a consolidated file and then checked whether the links still worked, adding another layer of manual review before the statements could be delivered.
Bringing Speed and Structure to Financial Reporting
A fragmented monthly reporting process was brought into one controlled workflow, reducing repetitive preparation and producing faster, more consistent financial statements across all 45 entities. The approach reflects the growing role of AI in streamlining finance operations while maintaining accuracy and traceability.
- Trial balance tabs cleaned and remained individually
- Balance Sheet and Profit & Loss prepared separately
- Reporting lines linked to general ledger accounts manually
- Gross Profit Margin and EBITDA formulas inserted by hand
- Fonts, colour, and currency formats reapplied after each export
- Three files combined manually
- Limited visibility while the process was running
- 30-40 minutes of hands-on work per entity
- General ledger worksheets processed automatically
- Both statements coordinated through one launcher
- Account codes used to create live links automatically
- Margin formulas identified by label and added automatically
- Standard formatting applied consistently during every run
- One consolidated workbook created and saved automatically
- Live dashboard updates and a permanent timestamped log
- Approximately five minutes per entity
Make Month-End Reporting Faster and Simpler
Automate repetitive tasks, simplify financial statement preparation, and achieve consistent reporting across every entity.
A Five-Stage Approach to One-Click Financial Statement Preparation
The solution combined file-level macros with a central launcher to reduce manual handling without disconnecting the final statements from their supporting general ledger data. Each stage addressed a specific weakness in the original process while contributing to one repeatable monthly workflow.
Phase 1: Source File Preparation and Macro Configuration
- Converted the Trial Balance, Balance Sheet, and Profit & Loss workbooks into macro-enabled excel files.
- Installed the required processing macro permanently within each source workbook.
- Retained separate file-level macros to support stable use in excel, where VBA code cannot be copied reliably between open workbooks during runtime.
- Confirmed the workbook names, account-code columns, link columns, general ledger value columns, and margin labels used by the automation.
Phase 2: Automated Trial Balance Processing
- Cleaned and prepared the Trial Balance workbook across all general ledger account worksheets.
- Renamed each account tab with its account-code prefix so the automation could identify it reliably.
- Preserved the unique account code even where excel shortened longer worksheet descriptions to meet its tab-name limit.
- Prepared the general ledger tabs for use within both statements and the final consolidated workbook.
Phase 3: Financial Statement Population and General Ledger Linking
- Processed the Balance Sheet and Profit & Loss workbooks through their dedicated macros.
- Matched each reporting line to a general ledger tab using the account code as the primary identifier.
- Used the account name as a fallback when no code-based match was available.
- Selected the last bold value in each general ledger worksheet as the preferred closing balance, with the last numeric value available as a secondary fallback.
- Applied configurable sign handling for liability and equity sections to preserve the organisation's reporting convention.
Phase 4: Automated Metrics and Standardised Formatting
- Located gross profit and EBITDA rows by their labels instead of relying on fixed row positions.
- Inserted gross profit margin and EBITDA percentage formulas across every monthly column.
- Refreshed existing formulas in place when the process was run again, preventing duplicate metric rows.
- Applied Times New Roman at size 11, removed fill colours, retained plain black text, and formatted numeric amounts in AUD.
- Preserved dates, serial numbers, and account codes in their original form.
Phase 5: Consolidation, Audit Logging, and Controlled Output
- Combined the completed Profit & Loss statement, Balance Sheet, and all general ledger worksheets into one workbook.
- Rebuilt links so they remained internal to the consolidated file rather than pointing back to the three original workbooks.
- Saved the final deliverable as a clean, macro-free .xlsx file ready for review or distribution.
- Displayed live status updates throughout the run and created a permanent log of each activity and its elapsed time.
- Added clear error messages, performance controls, and non-fatal handling so minor formatting or margin issues did not unnecessarily stop the main workflow.
Faster Monthly Reporting with Consistent, Fully Linked Outputs
The launcher changed financial statement preparation from a repeated manual task into a controlled monthly process. The measurable benefit comes from reducing hands-on time across the complete 45-entity portfolio while also improving consistency and visibility.
Key Takeaway for Finance Leaders
Three Principles Behind Scalable Financial Statement Automation
Automate the Full Preparation Sequence
The largest time saving came from connecting trial balance preparation, statement population, account linking, calculations, formatting, and consolidation. Automating only one stage would still leave the finance team moving data and checking files manually elsewhere in the process.
Preserve the Link Between Statements and Supporting Data
Automation should not turn financial statements into a collection of pasted values. Internal formulas keep every reported amount connected to its supporting general ledger worksheet, making the final workbook easier to review and investigate.
Build Visibility and Error Handling Into the Workflow
A faster process still needs control. Live status updates, permanent logs, clear error messages, and dependable restoration of excel settings allow the finance team to see what happened during each run and respond when an exception requires attention.