Modernizing Financial Data Collection with Power Platform

Executive Summary 

One of the core challenges in portfolio oversight and value creation is gaining timely, consistent insight into how portfolio companies are performing. At a leading private equity firm, this was compounded by a highly manual process of ingesting financial data from portfolio companies operating on different systems, with varying data formats and metric definitions. The result was fragmented, Excel-based workflows that required significant manual effort and limited scalability. 

To address this challenge, Lantern partnered with the firm to modernize and streamline financial metric data collection through a standardized, user-friendly experience. The objective was to simplify data submission, improve consistency, and materially reduce manual processing. 

The resulting solution was a Microsoft Power Platform-based application that centralized and automated financial data collection across the firm’s portfolio. Built using Power Apps, Power Automate, Azure SQL, and Power BI, the platform replaced disparate manual workflows with a single, automated, and auditable system – enabling more efficient data ingestion, improved data quality, and faster insights to support portfolio oversight and value creation. 

Customer Challenge

The firm’s FP&A team and Portfolio Company Associates were relying on manual, piecemeal Excel-based workflows to collect and submit financial metric data across multiple portfolio companies. This approach lacked automation, auditability, and centralized control, resulting in inefficiencies and data accuracy challenges. 

Associates had to gather data from a variety of sources in a fragmented manner, making it difficult to ensure timely, accurate, and consistent financial reporting. 

Solution

The solution was built using Microsoft Power Apps and Power Automate for user experience and automation, with Azure SQL as the data platform to ensure scalability, performance, and complex data validation. 

The solution supports two user personas; Associates (responsible for data collection and submission) and Administrators (responsible for managing company, metric, period, and user access). Associates were provided three flexible data input modalities: 

• Direct in-app data entry 
• Excel template generation for offline collection and upload 
• SharePoint document library for batch ingestion on a twice-daily cadence 

The solution was further enhanced with a Power BI integration delivering a summary visibility dashboard. Application Lifecycle Management was managed via Power Platform Pipelines with automated DEV/TEST/PROD deployments, and the backlog was managed in Azure DevOps with two-week iterations.

Impact

The engagement delivered transformative impact across financial operations, data management, and strategic decision-making for the private equity firm: 

• Improved data accuracy through validation controls that prevent invalid financial metric entries across portfolio companies 
• Enhanced business efficiency by replacing manual, piecemeal Excel-based workflows with a centralized, automated Power Platform solution 
• Increased user adoption among FP&A Associates and Administrators through an intuitive, user-friendly application experience 
• Enhanced visibility via a Power BI summary dashboard for real-time financial reporting 
• Enabled the FP&A team to access consolidated, structured, and validated financial data in one place leading to better insights 
• Improved decision-making for firm leadership through timely and accurate portfolio company financial data 
• Streamlined administration with improved Admin screens for managing company, metric, period, and user access 
• Flexible data ingestion supporting three input modalities including batch processing via SharePoint