Red Oak Strategic
  • Home
  • About Us
  • Services
  • Amazon Web Services
    • Database Engineering
    • Machine Learning and AI
  • Resources
    • Blog
    • Case Studies
Contact Us
  • Home
  • About Us
  • Services
  • Amazon Web Services
    • Database Engineering
    • Machine Learning and AI
  • Resources
    • Blog
    • Case Studies
Patrick Stewart
Thursday, 19 August 2021 / Published in Data, Analytics, Data Science, Data Visualization, Case Studies, Private Equity

Business Intelligence Across a Private Equity Portfolio

Background

Recognizing an opportunity to expand their customer base, Red Oak Strategic’s client, a global CPG manufacturer, formed a private equity arm to help develop FinTech platforms in developing economies. This involved buying or building various applications to serve specific needs for distribution partners across several markets. The ventures were spread across multiple continents with headquarters in the United States.

Challenge

While this strategy was successful, the management team within the investment arm lacked a holistic view into the performance of their ventures. The lack of insight into daily, MoM and YoY performance severely limited their reporting capabilities to the C-Suite and left the team with a gaping blind spot. Furthermore, the existing reports they did have were manually cobbled together on a day-to-day basis, facilitating a bottleneck where mistakes were common and requiring unnecessary manpower to produce. Additionally, any changes required a full rebuild on the Excel based reports due to a rigid design. This further compounded their reporting challenges by providing stale data points and limiting drill-down capabilities when questions arose from the C-Suite.

Solution

Red Oak Strategic was initially engaged to recreate the existing static PDF reports within Microsoft’s Power BI platform. This was the business intelligence tool of choice as the client had a standing enterprise relationship with Microsoft, which our team was happy to work with. Unfortunately, as we began our review of the client’s existing reports, it became apparent that the underlying data was stored in a heavily formatted Excel document that was manually updated on a daily basis. Our team immediately recognized that this would not be suitable for Power BI and a better data management solution was required.

Effective business intelligence platforms rely on well-designed underlying data. This involves both the actual format of the data source as well as how that data is continuously updated, with the goal being to reduced manual intervention as much as possible. Understanding this, the Red Oak Strategic team shifted our focus to first improving the client-provided data that would drive our Power BI reports. While we could not completely avoid manual data entry in this engagement, our engineers were able to build a process that reduced 80% of the daily manual data entry by automating calculations and focusing only on core KPIs that other metrics could be derived from. Furthermore, the Red Oak Strategic team developed an ETL process to extract data from the heavily formatted Excel file and place it into a flat format that was consumable by Power BI.

Once the underlying data was cleaned up, we could then focus on building our visualizations. This required marrying multiple data sources for daily, monthly and year over year metrics in our Power BI data model. As we began building though, we quickly learned that due to the reliance on manual data entry, there was no “single source of truth” for the client’s KPIs. In fact, multiple values for various KPIs existed across different Excel documents, making it extremely difficult to determine what the true value was. To remedy this, our team worked with the client to set business rules and delineated exactly which files each KPI would be sourced from. Once these were defined, we were able to construct a streamlined data model and rebuild the PDF reports as interactive Power BI dashboards.

As with most visualization projects though, once the initial reports were built, the client realized just how powerful having their data at their fingertips was. Typically, this results in new requests to better leverage the data that is now so readily available, and this client was no different as they began to request additional KPIs and visualizations. Furthermore, in this particular engagement, we had to address unique situations in their datasets that were previously handled by manual data entry but were thrown off once automated calculations were introduced. In the end, our team was able to not only automate the existing PDF reports into Power BI dashboards, but also add new insights for our client to use at weekly stakeholder meetings. This allowed the reporting team to update leadership on the standing of each unique venture, but also answer off-the-cuff questions on the fly using Power BI’s drilldown capabilities. In fact, the reports were so effective, the client now displays them in the common areas of their headquarters!

  • Tweet
Tagged under: Data Analytics Data Science Data Visualization Case Studies Private Equity

What you can read next

6 Strategies for Migrating Applications to Cloud
Data Visualization: Empowering Decision Makers
Tracking Coronavirus: Building Parameterized Reports to Analyze Changing Data Sources

Leave a reply

    Recent Posts

    • 6 Strategies for Migrating Applications to Cloud

      At Red Oak Strategic, we understand that...
    • Business Intelligence Across a Private Equity Portfolio

      Background Recognizing an opportunity to expand...
    • Data Visualization: Empowering Decision Makers

      Time and again, across Red Oak Strategic’s...
    • Tracking Coronavirus: Building Parameterized Reports to Analyze Changing Data Sources

      The pace of our modern world, and the impressive...
    • Draw Rotatable 3D Charts in R Shiny with Highcharts and JQuery

      While it might be tempting to liven up a report...

    Categories

    • 2016 Election (6)
    • Analytics (11)
    • Apache Spark (1)
    • Blockchain (1)
    • Business Intelligence (1)
    • Case Studies (3)
    • Cloud (1)
    • Code (12)
    • Data (15)
    • Data Processing (2)
    • Data Science (18)
    • Data Visualization (8)
    • Databases (2)
    • Donald Trump (1)
    • Excel (1)
    • Exploratory Data Science (1)
    • Financial Analytics (1)
    • Forecasting (1)
    • ggplot2 (1)
    • h2o (1)
    • Highcharts (1)
    • Hillary Clinton (1)
    • JavaScript (3)
    • JQuery (1)
    • Machine Learning (3)
    • Maps (1)
    • Political Analytics (3)
    • Politics (7)
    • Polling (3)
    • Predictive Analytics (3)
    • Private Equity (1)
    • Python (2)
    • Python 3 (1)
    • R (10)
    • R Shiny (4)
    • RegEx (1)
    • RShiny (2)
    • Sparkling Water (1)
    • Time Series (1)
    • Tutorial (1)
    • Tutorials (8)
    • Uber (1)
    see all topics

    © 2022 Red Oak Strategic

    KEEP UPDATED

    Receive our updates, best practices and latest news straight to your inbox