Creating an Executive Dashboard

Context

Our client was a manufacturing company in the US with annual revenue of around $120M, being the top global supplier of a niche product used in the commercial construction industry.

The company had traditionally relied on manual Excel reporting, primarily the monthly financial reports. Departments also reported their own KPIs at varying frequencies and levels of sophistication. The newly appointed CEO and CFO sought timely visibility into the company's overall health across key functions, similar to their previous company.

Objectives

We were asked to create an Executive Dashboard that contained KPIs for most of the main business drivers, including bookings, revenue, gross margin, EBITDA, throughput, labor productivity, and quality — all in one place and refreshed daily

Project delivery

We leveraged the data warehouse that had been set up for the client in a previous project (see this case study), which contained most of the raw ERP data including the general ledger, bookings, invoices, production jobs, standard costs, time tracking, inventory, and customer complaints. These datasets were sufficient for almost all of the KPIs requested by the client. The commercial and financial KPIs like bookings, revenue, gross margins, and EBITDA were straighforward to develop. For the operational KPIs we first aligned with the VP of Operations on the exact definition throughput, productivity, and quality.

Outcome

We published the dashboard to the client's internal PowerBI portal. We created an Executive-only workspace on PowerBI and limited access to VP and above, as there were already other PowerBI reports in the portal which we had created for the managers.

The dashboard is fully automated, except the EBITDA adjustments which are calculated by the finance team during the monthly closing and tracked in spreadsheets rather than the ERP general ledger.

‍