Companies from all over the world that want to set up manufacturing and distribution operations in Mexico turn to Grupo Prodensa to help define and manage the details. The company tracks dozens of key performance indicators (KPIs) for each client project with a series of Balanced Score Cards to help managers keep projects on time and on budget. As Grupo Prodensa’s client volume grew, managing the spreadsheet-based score cards became extremely labor-intensive. The company turned to Microsoft and local partner Lagash to transform the manual score card process to an automated solution using Microsoft Power BI and Microsoft Azure SQL Database. Now Grupo Prodensa managers can see KPIs in real time, any time, just by opening a dashboard. This will help streamline project management by centralizing information, automating reporting, providing easier access to analytics tools, and scaling to demand.
The move toward economic globalization over the last two decades has produced enormous growth in the Mexican economy as companies in a range of industries have set up manufacturing and assembly operations in Mexico. To streamline the process and access the expertise needed to properly set up these operations, many Fortune 500 companies and small and medium-sized businesses from around the world turn to Grupo Prodensa for help with all the details, from the initial concept to full implementation and operation. The firm provides its customers with a full range of services including market intelligence, site selection, construction management, human resources, finance, environmental health and safety, and import and export management.
Grupo Prodensa started in 1985 with the goal of attracting foreign investment in Mexico. “We take care of the administrative side of things, so our clients can concentrate on what they know, which is creating their products,” says Hector Soto, IT Director for Grupo Prodensa.
To track its projects, the company uses “Balanced Score Cards,” which originally were Microsoft Excel spreadsheets created to track KPIs for its six business areas: operations; import and export management; human resources; accounting and finance; environmental, health, and safety; and quality. For example, operations KPIs include metrics like project management direct cost income, and productivity based on percentage of cost vs. export. HR KPIs include details such as headcount, voluntary rotation, and absences. By 2017, the company was tracking nearly 50 KPIs across more than 60 companies operating in Mexico.
Grupo Prodensa project managers manually entered the data used for these score cards—averaging several thousand entries each month—into the spreadsheets. Since the spreadsheet files weren’t connected to each other, managers had to spend an hour or more every month combining all their KPIs from multiple score cards into a Microsoft PowerPoint presentation for Prodensa directors. At the end of the month, each client received a .pdf file of this data in an executive report to show how their specific projects were progressing. “We wanted to find a way to simplify that reporting process while also providing both our executives and clients with a near-real-time view of KPIs throughout the month,” says Soto.
Looking for scalability
In early 2016, Prodensa implemented Microsoft SharePoint Online, part of Microsoft Office 365, to create a cloud-based collaborative space where its workers, located all over Mexico, could enter score card data. Later that year, Microsoft and solution partner Lagash presented a workshop to Prodensa’s IT staff outlining how easily they could move workloads to the cloud by way of Microsoft Azure infrastructure as a service (IaaS). That presentation was quite convincing and not long after the workshop, Prodensa started moving key workloads to Azure, including the database for the system that controls all of its clients’ import and export transactions.
Also, during that workshop Microsoft and Lagash learned about Prodensa’s need to automate its Balanced Score Cards solution. Prodensa had tried to create a web-based Balanced Score Cards solution in PHP, but it didn’t prove scalable enough to handle the data volume. “Since we were entering data into our score cards through Office 365, which is in the cloud, I just kept thinking there must be a solution related to that, which could help us automate our score cards,” says Soto.
He was right. Microsoft and Lagash recommended turning the spreadsheet-based score cards into Microsoft Power BI dashboards. This would allow project managers to easily share all KPIs with Grupo Prodensa directors in real time, and it would virtually eliminate the time required to create reports and PowerPoint presentations.
It took a four-member team from Lagash and Microsoft less than three weeks to develop the proof of concept (POC) using the Microsoft Entity Framework and ASP.NET MVC 5 project templates. The Balanced Score Cards run as a Web Role on Microsoft Azure using Azure SQL Database, Power BI Desktop, and the Power BI service. Lagash created a dashboard for each of the six Balanced Score Cards and included about half of the KPIs in the initial POC release. Lagash’s technical testing team, Grupo Prodensa’s IT staff, and a few business users tested the POC.
“It was pretty easy for Grupo Prodensa to use the new dashboards because Power BI is part of Office 365; so if you know how to use Office applications, you can easily learn to use Power BI,” says Diego Vazquez, Sales Manager at Lagash. “We also did some workshops for the business users to show them how to modify the dashboards or create their own.”
After testing the POC dashboards for about a month, Prodensa added the remaining KPIs to the dashboards and released the new Balanced Score Cards to the individual project management groups in June 2017.
Comparing apples to apples
The new Power BI–based score cards have transformed KPI tracking and reporting for Grupo Prodensa. Management is able to get more information quicker and knows that the data is accurate. Before implementing Power BI, Prodensa project managers could change details, such as dates and goals, in the spreadsheets. With Power BI, the IT department defines the goals and milestones in the dashboards at the beginning of the project and those details cannot be changed by project managers.
This will allow Grupo Prodensa to better control content so that executives can be sure they’re seeing consistent KPIs based on data from the same time period. “In the past, we could not control the dates that the project managers entered, so we didn’t know if they had changed the information. That made it more difficult to manage our projects and resources efficiently,” says Soto.
KPI tracking on demand
The automated reporting in Power BI saves each business unit manager about a day’s worth of labor each month by eliminating the need to aggregate KPI data from multiple score cards into a PowerPoint presentation. Because the reporting process is automated, it opens the door to including more KPIs for better project and resource management.
“We can use Power BI not only for client KPI score cards, but also internally to manage our own people,” says Soto. “In the future, these KPIs will grow and Power BI will provide the flexibility to create new KPIs as needed to manage all the projects in minutes. The whole operations team will eventually be able to type in all the KPI data into their smartphones, tablets, or PCs, and the directors and managers will be able to see how the project is going in real time on Power BI dashboards.”
In the future, these KPIs will grow and Power BI will provide the flexibility to create new KPIs as needed to manage all the projects in minutes.
Source: Article by Microsoft