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!
