Private Equity Roll-Up

Overview

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.

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.

Just the Headlines

Prior to the engagement with 2nd Watch, a team of four to six people from our client’s finance group were spending as many as 15 days to produce practice-specific and 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 practice 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

2nd Watch 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 2nd Watch 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 Azure using a combination of Azure Pipelines and PowerShell scripts. To ingest QuickBooks data, 2nd Watch 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. 2nd Watch 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, 2nd Watch 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.

2nd Watch 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, 2nd Watch 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

2nd Watch 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, 2nd Watch 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

• 2nd Watch’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, 2nd Watch’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.