Automated Executive Dashboard for Performance KPIs

Context

Our client was a manufacturing company in the US with annual revenue of around $120M, with four international subsidiaries and is one of the top 3 global suppliers of a niche product used in the commercial construction industry.

The company had traditionally relied on manual Excel workbooks for performance reporting, primarily the monthly financial statements produced by the FP&A team. Operational KPIs were published by departments at varying frequencies and levels of sophistication; the two primary metrics were bookings and backlog. Manufacturing and supply chain metrics were scant, and the only recurring report they had here was the total unit produced each week.

The client needed timely visibility into the company's overall health across key functions.

Objectives

We were engaged to create an automated Executive Dashboard that would show the performance of the key business drivers, including commercial metrics like bookings and backlog; financial metrics like revenue, gross margin, and EBITDA; and operational metrics like throughput, labor productivity, OTD, and quality.

The refresh frequency was to be daily for commercial and operational metrics, and monthly for the financial metrics.

Project Delivery

We spent the initial 2 weeks of the project scoping out the set of KPIs and their calculation methodologies. This was done through workshops with the executives and analyzing any ad hoc reports that were produced by the departments. Once the body of KPIs were defined, we worked with finance and IT to identify the sources of data that were required to produce them.

The set of KPIs implemented were:

• Financial metrics
  
  1. Gross Revenue: Actual vs. AOP and prior year
  2. Gross Margin ($ and %): Actual vs. AOP and prior year
  3. OPEX ($ and %): Actual vs. AOP and prior year
  4. GAAP EBITDA ($ and %): Actual vs. AOP and prior year
• Commercial metrics
  
  1. Bookings: By subsidiary and sales office, actual vs. plan and prior year
  2. Bookings plan attainment % by sales office
  3. Backlog: by subsidiary, actual vs. prior year
  4. 21-day rolling average bookings
• Operational metrics
  
  1. Actual vs. planned units produced
  2. Labor hours
     
  3. Units produced per assembly labor hour
  4. Customer claim %
  5. On-time delivery
     

We were able to leverage the data warehouse that had been set up for the client in a previous project (see this case study), in which we consolidated a significant amount of the client's raw ERP data from all of its global subsidiaries, including the general ledger, bookings, invoices, jobs, standard costs, timesheets, inventory, and customer claims.

Within the data warehouse, we created a set of stored procedures that would run nightly. These stored procedures performed the calculations and data transformations needed to turn the raw ERP data into the metrics. For example, mapping the general ledger accounts to financial statement lines, linking sales offices to bookings, matching production jobs to timesheet entries based on work station and time, etc. Additionally, since the format of the raw data was different for each global subsidiary (as they all use different ERPs), another critical step done in the data warehouse was to normalize all the subsidiaries' data into a standardized set of fields and aggregation levels.

The stored procedures would refresh a set of reporting tables every night over the course of 3 hours. These reporting tables fed into the PowerBI visuals that we then created for the dashboard. Because all the data is preprocessed nightly, the PowerBI visuals on the dashboard loaded extremely quickly.

Once completed, we published the dashboard to the client's PowerBI portal. We created an ELT-only workspace and limited access to VPs and above. For two of the metrics (21-day rolling average bookings, actual vs. units produced), we addtionally used Azure Logic Apps to create automated emails to be sent to specific recipients when they fall below a certain threshold as to require manual attention.

Outcome

The dashboard delivered the client's ELT an unprecedented level of visibility into their business, as none of the commercial and operational KPIs in it had ever been produced at a daily frequency before this project (some commercial KPIs like backlog were produced weekly, and operational KPIs were generally produced on an ad-hoc basis by the Ops VP's team manually in Excel). The FP&A team simply did not have the bandwidth to manually create something like this even at a monthly frequency.

After this dashboard was published and became part of the ELT's workflows, we were asked for a follow-on project by the VP of Operations to create an in-depth Operational Dashboard.