When I joined the business, the reporting landscape was remarkably thin. Our primary "truth" was a standard P&L report pulled directly from the finance system, which offered basic financial figures but little operational context. While our internal systems held years of rich data covering everything from invoicing to payroll. It was a black box just waiting to be opened. Our budgeting methodology was equally problematic, relying on a simple average spend per client multiplied by the desired number of clients. This failed to account for the nuances of the calendar, leading to "disaster" months like February, where fewer days and weekends made the business look like it was underperforming when the reality was not quite correct. We lacked a clear understanding of por past performance, margins, revenue drivers, and which client types truly were valuable the business and where we found them.

The objective was to move beyond basic hindsight and create a more forward looking engine to help define our strategies that would ultimately help us meet our objective. I needed to overhaul our financial structure to define our KPIs, identify profitable client segments, and align our financial planning model with the company's core strategic goals, such as improving service quality and business development.

I took a phased approach to turn data into a strategic roadmap:

• Structural Reorganization: I reconfigured the Chart of Accounts and introduced logical groupings to the P&L and Balance Sheet. Giving us the ability to provide leaders with meaningful summarised data, while managers could still interigate the detail. By establishing clear cost centers and departments (HR, Finance, etc.), we could finally see exactly where revenue was generated and where costs were absorbed - this did not reveal anything we did not know but it was needed to create the framework for the next phases.
• Strategic Insight: We performed a forensic analysis of five years of operational data, uncovering the KPI's of the business, specifically Customer Lifetime Value (LTV), average length of stay, and Cost of Acquisition (CAC). By grounding our strategy in these historical truths, we built a roadmap the team could deliver and the PE partners approved of he methodology and our planned growth. More importantly, it transformed our reporting from a backward looking into a forward facing business navigation tool.
• Defining the Goal: With the data in hand, we established our primary business objective and few specific goals, such as elevating service quality to improve customer retention and our reputation. We identified the exact metrics required to measure these strategies.
• Driver-Based Planning: When creating the new budget, I used many of those same success metrics as drivers and measures. This ensured that our financial plan wasn't just a list of numbers, but a direct reflection of our strategy. With this it would help us identify successes or setbacks in the 'actuals' early enough to act. Driving initiatives that either course-corrected issues or accelerating our biggest wins.

The transformation turned our finance department from a simple reporting function into a strategic guide. By building the budget around the same KPIs we used for actual reporting, we created a "closed-loop" system:

• Instant Diagnostics: We can now see exactly where we are performing well and where we need to focus attention. If average spend or client numbers dip, we know exactly which lever to pull.
• Operational Clarity: We now track client churn, retention, and hours serviced against the budget, allowing the Business Development team to pivot their strategy based on real evidence that was previously hidden in vast rows of disconnected meaningless data.
• Strategic Alignment: For the first time, the business had a clear direction with the team aligned behind our objective and goals and the strategies of how we were going to make it happen. We were no longer just measuring money. We measured the success of our most important strategies.

The goal was to move away from manual copy-paste accounting and build a resilient, automated data pipeline. Here is the high level technical architecture behind the transformation.

To make this a reality, I used Microsoft Excel, Power Query, and Power BI, utilising Zebra BI for professional, IBCS-standard reporting that we as the leadership team could easily understand and actually use to define startegies to drive improvement.

The ETL Process (Extract, Transform, Load)
Instead of relying on slow, manual refreshes by copying data into various excel templates, I established a folder based ETL workflow using Power BI’s "Combine Files" feature to automatically grab and append the latest month’s data without any manual intervention (other than saving the required reports into their specific folder).

Advanced Data Modeling with Excel and Power Query
To ensure the data was "machine-readable," I performed several critical transformations:

• The Unpivot: The budget was created in Excel with months running horizontally (human-readable). To align this with transactional "actuals," I used Power Query to unpivot the months. This turned 12 columns into a single "Date" attribute, allowing for a seamless join between budget and actuals on a single timeline.
• Attributes: Every data row was assigned additional attributes like "Scenario" (e.g. Actuals, Plan A, Plan B) and "Measure" (e.g. Value, Client Count, Spend per Day etc) fields . This enabled high-speed comparisons between actual results and the various plans. What If analysis could now be achieved across the our FP&A data model.
• Driver Integration: I merged financial data with operational drivers (such as client count) within the same model. This allowed us to report on KPI's like revenue per client directly alongside the typical financial reports.

Visualization with Zebra BI
To ensure reports were consistent and easy to understand, I utilized Zebra BI visuals within Power BI. Zebra BI conforms to IBCS reporting standards a visual approach to make graphs and charts understandable at a glance:

• Standardised Scaling: Zebra BI ensures that the scales within the charts are consistent, preventing the distortion of data and ensuring that a variance has the same scale as the rest of the numbers shown - this clearly depicts the impact of the variance.
• Waterfall Charts: I used these specifically for the P&L to visualize how individual numbers impacted the bottom line, with clear red/green color-coding both in an absolute and % based measures for instant variance recognition.
• Consistent Logic: By following the rule that "Time always runs left-to-right," we created a reporting language that the leadership team could understand at a glance, regardless of the metric being viewed.

• Time Savings: We reduced the month-end close process from 5 working days down to no more than 2 days allowing time to analyse and refine our forward looking plans on real evidence and new initiatives.
• Accuracy: By automating the journal imports from the operational system and removing manual data entry, we virtually eliminated human error from our reporting cycle.