How we helped a global Investment Holding corporation to build a financial statement reporting and operating model automation tool

The Context

The client, a global Investment Holding company, which manages various portfolio companies, used to manually update the company’s financial model’s tabs using the input files, which was time-consuming. Additionally, monthly data was evaluated in silos without leveraging historical data resulting in a considerable gap while obtaining deeper insights. The client wanted the TresVista Team to remove these impediments and create an interactive dashboard populated with historical data that uncovered deeper insights and allowed them to interact with the data the way they wanted.

The Objective

To automate the manual task of operating model update for each of the client’s portfolio companies and create an interactive dashboard populated with historical data that uncovers deeper insights.

The Approach

The TresVista Team executed the deliverable by dividing the task into three major components:

• A Python programmed tool to automate the data pipeline and update the operating model. The tool converted the client’s files into a structure that Tableau could use and updated the client’s operating model simultaneously, completing the task in approximately 3-4 minutes
• An interactive Tableau dashboard that depicted historical data and additional visualizations to aid the client’s decision making
• Automation of the monthly dashboard refresh by setting up Tableau Bridge at the client’s network to update the dashboard with the latest month’s data

The Challenges We Overcame

The major hurdles faced by the TresVista Team were:

• Cleaning and extraction of data in a format that could be fed to the Tableau dashboard in the appropriate format as the file types used as input were non-homogeneous, for example, PDF and MS-Excel files
• Standardization of the inconsistent monthly historical file structures to be used as the input in the automation code
• Stakeholder Management because of the concurrent requests from multiple portfolio managers who worked in silos. This led to confusion over prioritization of requests, parallel execution, and back-and-forth between the teams because of the iterative feedback on formatting

The TresVista Team overcame these hurdles by creating a custom data transformation pipeline for the different file types, enabling the Python automation tool to identify the file type and name of the input file and perform the necessary operations. After communicating with the client, the code was adjusted to accommodate the structural changes. The team maintained clear lines of communication with the portfolio managers to resolve the hurdle of stakeholder management.

Final Product (Sanitized)

The Value Add – Catalyzing the Client’s Impact

The TresVista Team created additional visualizations and KPIs which the client could use to generate insights like a year-on-year waterfall bridge, geospatial visualization, and trend analysis. The team incorporated a functionality of comments addition in the dashboard to allow the client to view historical comments, which they used to add to track line items of interest.