SPK comes to the aid of a major medical device maker to solve the mystery of why project-management and employee-utilization metrics couldn’t be trusted—and then fixed the problems, and created powerful dashboards for the CFO and other execs
The Client In The Crunch
The new CFO of this respected medical-device manufacturer was being frustrated by the very data she relied on to run the business. Specifically, things didn’t match. They didn’t line up. Crucial information was missing, wrong, or both.
This wasn’t just a hunch or a bad feeling. Manually reconciling information from the company’s own enterprise resource planning (ERP) system—in this case, Microsoft Dynamics—against basic input data such as time sheets from employees and contractors, simply didn’t work. Even their own Power BI installation—which should’ve provided easy-to-read management dashboards of essential KPIs—wasn’t trustworthy. The CFO’s own analysts were forced to manually generate reports in Excel, to try and get to the truth about expenses, budgeting, and employee utilization.
Even the manually-generated reports were suspect, because the data from the management portal wasn’t
The stakes were high. Cost overruns; the ability to properly budget employees and contractors; the power to “see” who was performing well, or not; the ability to simply forecast profitability from month to month… none of this was available to the CFO who so desperately needed it. So she turned to the experts at SPK and Associates.
KPI's available on demand
Data presented with actionable insights
Ability to track overdelivery on projects
Digging To The Bottom Of The Mystery
Before getting to any dashboards, the nitty gritty work of data engineering comes first. This company’s Dynamics ERP system was running on an on-premises server. Their data was spread across numerous different systems and data warehouses. SPK (based in California) set up a data gateway to work on the project remotely.
What we found was eye-opening.
There were two sources of problems. The first was related to their on-prem ERP system. After a lot of digging, we discovered that the company’s reporting system had actually been created by a third-party consulting firm; it wasn’t part of the original, internal Dynamics software. Thus it was applying undocumented filters to the company’s own data, and users didn’t realize that it was being manipulated! For example, if a new labor class of employee was created after the reports were set up, those employees would be excluded from any reports. That’s just one frightening example of many we discovered.
The other source of the incorrect reporting, we discovered, was their own Power BI setup. They used Power BI to import data from several different sources (such as Dynamics ERP and Salesforce) into the above-mentioned data warehouses, which had their own undocumented filters on them. Thus the data was being manipulated, incorrectly and under the radar, upstream of Power BI—rather than simply being imported directly into it.
For example, their purchase order (PO) budget forecasting report failed to indicate when additional POs were issued; it only provided totals for all POs combined. Thus they couldn’t see if they were going over-budget at any point in a project and might need to request an additional PO.
To make matters even worse for the beleaguered CFO, the Power BI reports themselves weren’t even documented or configured to her needs.
As noted above, SPK set up a data gateway from their preexisting ERP system and imported the data into Microsoft Power BI. We then analyzed their data and generated dashboards that allowed them to view their business metrics straight from their raw data—which is what they’d wanted all along. The undocumented filters (and, incidentally, their internal IT person who had installed them on Power BI) were history.
Finally, the company had the KPIs it craved, including:
- Employee billable utilization percentage
- Contractor billable utilization percentage
- Utilization percentage compared to target
- Non billable time allocation
- PO budget forecasting totals—indicating when PO budgets will be exhausted compared to resource forecasting
And of course, since this is a real-world story, it’s not quite as simple as that. Because shortly after we did all this work for the client, they migrated to an entirely new ERP system—Oracle NetSuite—and we helped to seamlessly migrate all of the Power BI dashboards to it. To accomplish all of the above, SPK provided a team including a Senior Engineer, the Director of Finance, a Senior Application Integration Engineer, an Applications Services Engineer, and a Mechanical CAD Engineer. The Senior Engineer served as project manager; the Director of Finance was the business consultant and Power BI designer; the other engineers provided the technical assistance with the migration and gateway.
On the client side, our sponsor at the company was the CFO. We also worked with one of their Senior Business Analysts, the Director of Systems and Technology, and a Senior Revenue Accountant.
The initial analysis of the data and the project design took two months. The implementation took about twice that long.
Once the data engineering was complete, the data was clean and immediately available in easy-to-navigate dashboards, the CFO was able to, finally, grasp some painful—yet empowering—information. For example:
- They had been manually calculating billable hours divided by a flat 173 hours per month. But the new logic developed by SPK factored in real-world details such as the actual number of work-days each month, along with holidays and changes in employment, such as mid-month hire/fire dates or approved leaves. We also provided them with the same information for contractors, which had previously been unavailable.
Previously, the company had been unable to recognize which employees were being under-utilized and whether they needed to be assigned to additional projects. With the new dashboards in place, the company saw that:
- About 20 percent of employees were working—and getting paid—for previously-unrecognized “beach time.”
- Around eight percent should be considered for termination due to underperformance.
Those were lagging indicators. On the leading-indicators side, they could (finally) see that, with their current resource forecasting, they would exceed their PO budget—with about 22 percent of projects on track to be over-delivered.
Needless to say, armed with this information, the CFO was able to take immediate action.
This was a complete game changer for us. We were making decisions based on bad data, when we were even able to get the data. SPK discovered the problems, cleaned up the data, and then put together effective and beautiful visualizations. We can finally make decisions based on something real!
Get a no-obligation initial assessment
Whether you’re trying to solve technical problems, implement new technology tools and strategies, or improve efficiency, SPK and Associates can be a key part of your strategy. We can provide a free, no-obligation initial assessment on almost any technology or engineering situation. Contact us today at (888) 310-4540 or firstname.lastname@example.org.