Private equity roll-up financial reporting
This healthcare services partnership backed by a major private equity firm was conducting a roll-up strategy, acquiring independent endodontics practices and consolidating them under an umbrella brand. The private equity group’s finance team was looking for help in developing and implementing a streamlined and scalable financial reporting process to reduce the time and complexity involved each time they wanted a report.
Overview
THE CHALLENGE
A private equity firm with a focus on healthcare practices needed streamlined and scalable financial reporting.
THE SOLUTION
The Ollion team implemented a modern data warehouse to facilitate data consolidation and user-friendly dashboarding.
THE OUTCOME
Using Ollion’s solution, the client was able to consolidate data from newly acquired practices and incorporate them into their reporting in a short time period.
The challenge
Private equity firms need the ability to quickly and easily analyze the financial performance of their portfolio companies. Slow and inconsistent reports can lead to slow and inconsistent decisions. This was the challenge our private equity client was facing as they rapidly acquired endodontics practices across the United States.
Prior to the engagement with Ollion, a team of four to six people from our client’s finance group were spending as many as 15 days to produce practice-specific, consolidated monthly financial reports. With a continuous stream of new practice acquisitions, our client realized their Excel-based approach was not practical or scalable. They wanted to automate financial reporting, alleviating the strain of the manual effort behind this recurrent process and providing more immediate, tailored reporting capabilities.
The solution needed to achieve the following goals:
- Provide a data model that reflects pre-acquisition data, post-acquisition data, budget and production/collection data.
- Consolidate QuickBooks, Excel budget sheets and data from various practices into a single modern data warehouse.
- Provide a tailored reporting solution to track KPIs relevant for management reporting and for individual practice reporting.
The solution
Ollion developed a scalable financial reporting solution for our private equity client by implementing a modern data warehouse to facilitate data consolidation and user-friendly dashboarding in Power BI. The solution was implemented in four steps.
Step 1: Data restructuring
The Ollion team began by creating a dimensional model able to incorporate our client’s various data sources, restructuring their data into a format that supports accurate reporting of date ranges pre- and post-acquisition. We then built the model and implemented complicated business logic using stored procedures to manipulate data between database schemas.
Step 2: Data consolidation
Once the data was restructured, it was brought into Microsoft Azure using a combination of Azure Pipelines and PowerShell scripts. To ingest QuickBooks data, Ollion leveraged an ODBC driver (QODBC) to establish connections within a PowerShell script. The script would then process more than 20 QuickBooks files, creating CSV files to port to Azure storage for processing. All other data sources were loaded directly using native Azure Data Factory connections. Ollion also created custom mapping tables to aid in financial consolidation and reporting.
Step 3: Custom dashboard creation
In addition to building out a data management platform for our private equity client, Ollion focused on the analytics environment for reporting across all practices and the management team. We created Power BI dashboards based on business user requirements, incorporating support for rolled-up practices and allowing for customization of drill-in fields and KPI measures.
Ollion developed both a management workbook exported into a PowerPoint presentation for distribution ease and a financial workbook to provide a more in-depth and interactive view of the data for our client’s financial advisors.
Step 4: Ongoing data management process development
To accommodate our client’s continuous yearly acquisitions, Ollion developed a streamlined process to add new practices to the data management platform. This encapsulated the full process of formatting pre-acquisition and budget data, ingesting QuickBooks data, and incorporating each new practice into the Power BI workbooks.
The outcome
Ollion produced a solution that provides multiple dashboards with drill-down capability for the private equity firm’s financial management, executive management and practice-level users. Business users are able to take actionable steps by clearly visualizing their performance against budgeted goals and previous years.
The financial business intelligence solution we developed significantly reduced manual effort required to produce timely financial reporting. Furthermore, by providing QuickBooks transaction validation, Ollion facilitated increased reporting accuracy and reduced validation time. Finally, our solution allowed for quick and seamless integration of our client’s new acquisitions, creating a scalable financial reporting system that effectively serves all stakeholders’ needs.
Project outcome insights
- Ollion’s solution greatly increased the number of practices the client can acquire and report on each month. At the project’s conclusion, they were able to acquire five endodontics practices in one month and incorporate them into their monthly reporting workbook that same month.
- Data collection and consolidation previously required a team of four to six people. Now, Ollion’s system gathers and models the data and only two resources are required to validate data and maintain the pipelines.
- Practice-specific and consolidated financial reporting previously took nearly a month to complete and now requires only a few days.
