Improving Visibility Through Complex Data Analysis
The Situation
A full-service, independent Pharmacy Benefits Manager (“PBM”) had recently been acquired by a leading global private equity firm. The sponsor hired a new CFO and among the key initiatives for the CFO was improving insight into customer profitability and implementing a robust monthly financial reporting package to provide actionable insights into business performance. The CFO engaged Accordion to implement tools – ultimately enabling a detailed, rolling profitability analysis of 1,300+ customers and streamlined reporting of monthly financial results.
Services
Actionable Business Analytics
Stakeholder Reporting & Strategic KPI Enhancement
Granular Margin & Growth Analysis
The Execution
Our Accordion team worked closely with the Company to do the following:
- Conducted detailed foundational reviews with the relevant team to identify the underlying source data required for analysis of customer performance; Discovered that no single source of data existed, and the required claims-level financial data was spread across various disparate databases and offline Excel files.
- Leveraged Microsoft PowerQuery and PowerPivot for data querying, structuring/analysis and modeling within Excel (given multiple SQL databases and various manual Excel spreadsheets containing the ~70 million claims); Created this reporting to produce an output that could be manipulated via standard Excel features and, despite back-end complexity, would remain user-friendly for the CFO and FP&A team.
- Developed a granular P&L template which segmented customer performance by channel (i.e., Retail, Mail, Specialty and Discount Cards) and bifurcated by brand and generic performance; Worked with the IT team to structure the back-end data sets and developed a process to query and aggregate the data into a master data set.
- Incorporated critical features into the model which included the ability to a) create summary gross margin reports by top clients (PBM, Outsourced and Discount Card), b) analyze rebates received (actual customer-level information unavailable) based on well-developed assumptions (acceptance rate and rate per rebate, c) filter performance firm-wide by various contract level metrics (Pricing Type, Rebate Arrangement, Rebate Basis, etc.)
- Developed a process and tool for monthly financial reporting to the sponsor; Designed the reporting package to provide insight into performance by channel as well as by PBM versus Outsourced verticals; Illuminated the key drivers of financial performance via relevant detail around fixed costs, performance (sales and claims) by top customers, as well as bridge analyses (versus Budget, Forecast and Actual) to the Gross Margin.
The Results
As a result of our work, Company Management and the Sponsor gained valuable insight into the performance of 1,300+ customers by key business segments on a rolling 24-month basis. Our collaboration also enabled timely monthly updates on corporate level financial performance, which are being utilized to support critical decision making.