Civil Contractor Performance Reporting System Through Power BI
Executive Summary
One of our clients in the UK deals in the Civil Engineering domain as one of the biggest contractors that have operated in Scotland for nearly 60 years. ABC operates across Scotland and beyond, working on Marine projects, Water Management, Infrastructure, and Energy.
Problems faced by ABC
ABC is a civil contracting company that takes the project from various clients. ABC is required to track all projects and their tasks, like when & what tasks are assigned to their engineers, whether they are on track or not, and whether the assigned task will be completed on time. Any roadblocks the engineer faces due to the delay in completion will occur, and many more crucial informative items like this. Also, the column in the report must display whether the task was Compliance or Non- Compliance (If “Compliance,” then the job was completed within 60 Days, and Non-compliance if more than 60 days). ABC was using spreadsheets to monitor their project tasks completion process, which was time-consuming, prone to errors, and lacked real-time insights.
Our client ABC receives an Excel E1 from their client monthly, which holds information like (project Id, project description, contractor, regional Hub like the south or west regional hub, contract Reference id, its completion date is essential, Draft submission date is the first date when this project started, duration compliance to FDA, Etc.
Another Excel E2, which our client ABC maintains monthly internally for the same projects to track progress, had data columns like Auto Code Number, Contract number, Contract Name, pricing, Contract manager, site agent, contract engineer, etc.
ABC is required to manually co-relate the information each month between two excel E1 and excel E2 and figure out the status of the projects, their task, and the status of the tasks assigned to their engineers. Also, color shades are applied to excel data according to these conditions red color for less than 75%, orange color for between 75% to 90%, and green for above 90%.
So, this activity required the number of employees each month working to generate the monthly composite report by merging the information from both excel E1 and E2. It was also prone to human error as the information was handled manually and was required to pass from desk to desk. Hence, each employee involved in this process provided their input to the monthly composite report required.
Solution ABC Wanted
ABC organization wanted some solution by which they could overcome the manual excel merging requirement as it was prone to human error and took a lot of time for their employee to prepare it. They wanted this whole process to be fully automated and the reports they were creating manually over excel by applying numerous formulae to it.
ABC used to go through the same process each month to create several different sheets in excel to show the project its tasks and the Engineer progress assigned to the task’s month-wise, along with the data on whether the task was falling under Compliance or Non- Compliance. In other words, ABC wanted completely automated and restructured reporting to give enhanced business insights.
Our Solution
Here XRM Labs advised ABC to keep their requirement in mind to go with PowerBI reporting services that will cover all their requirements to drill down information through reporting services.
To automate the system entirely. Firstly, XRM is required to deal with the two excel our client ABC receives monthly. So, to overcome this, we decided to finalize a location over “One Drive” and created a folder named “Excel Dropbox” in which staff personnel will paste the latest excel files monthly. Our PowerBI reporting system will further read to extract data from it and to generate different required reports further.
We redesigned the existing reports that they required and provided more enhanced drill-down reporting features. PowerBI helped us a lot to connect to the two files excel spreadsheet and read data sources from cloud storage One Drive, allowing us to build reports that suit our client-specific needs quickly.
The following features are implemented to overcome their issues:
• Automated manually extracting the information and handling the two excel which clients must create reports.
• Automatic reading of two excel by the system and generated required reports in Power BI with enhanced features like drilling down to any selected record to get further insights.
• Producing composite drill-down reports with user and Period filters like User Specific measure report, which displays the info as per given requirements like which user on which month worked on which project’s task along with its min percentge compliance towards its assigned tasks.
• Provides the feature of viewing records, updating & delete records depending on provided permissions to users.
• The system can produce a variety of reports to evaluate how well account managers and the sales team are doing at pitching services against lines of business.
• Display per user wise the average percentage of compliance per period.
• Count of compliance per user as selected period-wise.
• Project Agreement report displaying data period-wise, regional hub-wise, no of agreement with period completed and completion percentage achieved, not achieved as per the given time scale.
• Drilled down reporting feature showing compliance users-wise, like Minimum compliance reached by users and its total count.
• Gave the ability to users to see their monthly statements with drill-down details of the compliance components.
• Many values analysis reports are created so each user can show his % compliance towards each project task assigned to him.
Technologies Used
• Power BI
• Web Services
• HTML, CSS, JavaScript
Client Benefits-their improved statistics (Facts and Figures)
•With the help of our system, the client observed that their managers could more accurately monitor their team's performance, and task tracking also improved.
•Our client experienced a lot of satisfaction as all their requirements have been catered to and in a much more attractive and easy way, the complete information was presented.
•Increased the revenue and our client confidence through the Power BI reporting as it eliminated human error in the provided information.
•Timely delivery of projects and billing cycle of our client streamlined as the accurate project completion information was made available.
•The new reporting is much more user-friendly and drastically reduced the time taken off the manual excel report generation tasks.
•Better client customer satisfaction as the tasks were much more accurately tracked and further can be drilled down.
•Reduced our client staff time by almost 100%, which was used to create excel reports.
•ABC employee workload was reduced due to the automation for the month report generation.
•Through the Power BI reporting tool, drilled-down reports were implemented, which helped many high-level managers get better insights into the business.