Construction Management

Using Tableau for construction management by tracking RFIs and project status

Picture5.png

Figure 1. Actual vs. Committed Dashboard for Clark Builders

Successful companies recognize the importance of managing projects through efficient and organized documentation management. An environment that promotes on-time and profitable projects is created by identifying, controlling, accessing and tracking documentation. Monitoring documents, such as RFIs – Request for Information – is important because late or no responses can lead to time delays, budget issues, as well as legal consequences.

 

RFIs are typically used to clarify uncertainties and gaps in information for specifications, plans, contracts or other documents. In a simple RFI, there may be only one main consultant interacting with a contractor for information; however, in complex RFIs, it may require coordination between multiple consultants and subcontractors, making documentation management increasingly important. According to a Navigant Construction Forum survey of 1,400 projects, there is an average of 9.9 RFIs for each $1 million of construction worldwide, and 25% of RFIs received no replies. When using data from Onware’s Contract Administration software, we could see Canadian large institutional and healthcare projects average around 30 RFIs.

Background

Based on the statistics from Navigant Construction Forum, documents, like RFIs, can have a considerable impact on project planning and budget. Consequently, lack of response to documents can be a catalyst for costly disputes, budget overruns and may hinder completion of a project on-time. Document management can be manual and tedious – many are tracking projects documentation through emails, spreadsheets, or even pen and paper. This is not only ineffective but can result in disputes if not managed properly. Thus, effectively and efficiently tracking and monitoring documents digitally is of paramount importance, in fact, Navigant Construction forum recommends using software to help monitor and track documents for every project.

Challenges and Objectives

Challenges

  • Three reports are required to monitor project costs: 

  • Job cost report, which summarizes the actual costs impacting each cost code (i.e. invoices from subcontractors, timecards, etc.); 

  • Project cost report, which provides an updated record of commitments, forecasts and expenditures to identify budget variances; and the 

  • Detailed job cost ledger, which lists itemized expenditures in the cost codes from the job cost report. 

  • On an average size project, this can result in approximately 200 – 300 pages of data for the Project Manager to read through; it is time consuming and often not done effectively: 

  • It can be difficult to cross reference cost codes to higher levels and identify any cost issues such as budget overruns. 

  • Black and white pages make it strenuous to find miscoded items because they are hard to distinguish from the rest of the data. 

  • Printed or exported reports are limited to cost information at a point of time. As a result, Project Managers are unable to identify cost issues and react in timely fashion. In addition, it was difficult to look at costs over time to identify trends or see patterns.  

Figure 2. 200-300 black and white pages of data

How We Helped

With Tableau, we connected directly to the financial system (eCMS by Computer Guidance Corporation) used by Clark Builders and created an immersive dashboard with all cost information. Moreover, we integrated visualizations and highlighted the relevant information for the Project Manager. This required intimate knowledge of the source databases, as well as, the calculations being performed for key pieces of information on the dashboard, such as the Estimated Cost at Completion (ECAC). As a result, the dashboard provided near-real time information for Project Managers and Executives to see cost issues on their projects, allowing them to perform their jobs more efficiently and effectively, instead of spending hours summarizing and compiling reports. 

"I see this software as an invaluable tool and benefit for reporting on financial metrics throughout our business." - Marc Timberman, CFO

Results

Making use of Tableau Desktop and Server tools and collaborating with Clark Builders, we were able to create a consolidated dashboard for the Project Managers and Executives. This provided a number of benefits. 

 

At a glance, Project Managers can see and compare the profitability on the project to the original budget. Furthermore, they have instant high-level visibility of the original contract value, change orders, the current contract value, and how much has been billed to their client. Project Managers can quickly identify the overall project budget, ECAC, and actual costs. Moreover, cost types (labour, materials, subcontract, and equipment) are visible at a high-level and can instantly evaluate the budget and cost. 

 

Even though the cost code summary provides a high-level of detail, Project Managers can effortlessly identify cost issues (highlighted in red) rather than seeing a sea of monotone numbers. The detailed actual cost area within the dashboard allows Project Managers to see transaction level detail without having to navigate to a different screen.   

Figure 3. Selecting a cost item on the left will display the detail list on the right for the cost items that make up the total for the cost code. 

We helped link the detailed actual cost records to scanned vendor invoices to provide even more information and detail. Furthermore, we also provided the ability to examine costs across the life of the project via two timelines: monthly actual costs, and cumulative costs. These help to identify outliers, trends, and issues. To save space on the dashboard, only one version of the timeline is displayed at once, but the view can easily be toggled between the monthly and cumulative views as desired. Since the entire dashboard is interactive, we gave the ability to filter by months, so by selecting one or more months, the detailed cost records are automatically filtered to the corresponding period.  

 

Finally, through searchable filters, a Project Manager can quickly locate records by a vendor name, transaction description, PO number, or cost code. 

See the full presentation on Clark Builders here.

Actual vs. Committed Gallery

Project location map for DIALOG

Figure 1. Project Map for DIALOG 

Background

Successful companies recognize the importance of managing projects through efficient and organized documentation management. An environment that promotes on-time and profitable projects is created by identifying, controlling, accessing and tracking documentation. Monitoring documents, such as RFIs – Request for Information – is important because late or no responses can lead to time delays, budget issues, as well as legal consequences.

 

RFIs are typically used to clarify uncertainties and gaps in information for specifications, plans, contracts or other documents. In a simple RFI, there may be only one main consultant interacting with a contractor for information; however, in complex RFIs, it may require coordination between multiple consultants and subcontractors, making documentation management increasingly important. According to a Navigant Construction Forum survey of 1,400 projects, there is an average of 9.9 RFIs for each $1 million of construction worldwide, and 25% of RFIs received no replies. When using data from Onware’s Contract Administration software, we could see Canadian large institutional and healthcare projects average around 30 RFIs.

DIALOG is a diverse team of architects, planners, designers, and engineers that design for urban vibrancy, health and wellness, transportation, education, arts and culture, residential, retail, and commercial, as well as mixed-use solutions which incorporate an increasing number of these.

 

They are located in Canada (Edmonton, Calgary, Vancouver, Toronto) and the US (San Francisco).

dialog.jpeg

Executive Summary

Based on the statistics from Navigant Construction Forum, documents, like RFIs, can have a considerable impact on project planning and budget. Consequently, lack of response to documents can be a catalyst for costly disputes, budget overruns and may hinder completion of a project on-time. Document management can be manual and tedious – many are tracking projects documentation through emails, spreadsheets, or even pen and paper. This is not only ineffective but can result in disputes if not managed properly. Thus, effectively and efficiently tracking and monitoring documents digitally is of paramount importance, in fact, Navigant Construction forum recommends using software to help monitor and track documents for every project.

Our client, DIALOG, is a multi-disciplinary collaborative team of architects, urban planners, interior designers, and engineers. Due to the size of its team and its range of services, there is increased complexity to track the amount of information within the organization. Consequently, DIALOG uses Onware’s collaborative construction management software to help centralize project data, and track and monitor important documents, such as RFIs.

 

Because DIALOG uses our construction management software, we have the benefit of understanding the data and the data environment.  We connected Tableau to our database and created dashboards with live data to show report cards for active items and aging on documents, contractor scorecards, submittal tracking, and project/contractor administration reports. Furthermore, we set up weekly automated emails to project executives and managers so they can have a quick glance of how their projects are doing week-to-week.

Challenges and Objectives

Challenges

  • Create dashboards to monitor active items/documents that require action.

  • Implement dashboards and reports to track time taken to respond to active items.

  • Provide Principals with an overview of projects they are responsible for.

Objectives

  • Create dashboards and reports that shows active items, aging for documents on projects – changes, submittals, progress claims, RFIs.

  • Implement drill down ability in visualizations and dashboards to view details of documents that require action, which project they are in, and who is responsible.

  • Provide Principals with weekly report card emailed directly to their inbox. This provides quick view of projects and determine whether actions are required if there are issues.

How We Helped

DIALOG uses Onware’s construction collaboration software, which is a cloud-based construction administration software solution that offers transparency and accountability in the document management process by creating, managing and distributing project documents across departments. Onware’s software has helped to track millions of construction and design documents for DIALOG. Our tool serves as an important central repository for users to view and action documentation anytime. By connecting Tableau with our Onware database, we created   several visualizations and dashboards with live data so project executives can get up-to-date information to see and understand their projects. We created five main modules to help DIALOG manage their projects.

 

  1. Active Items

 

The Active Items dashboard shows the number of active items by studio and by document type (based on change code), as well as, the active items by project. Users can drill down to look at the individual documents and even click through to view them in their web browser.

Active Items Drill Down

Figure 2. Active Items Drill Down

   2. Change Management

 

We created a series of Change Management dashboards to see the changes being made to the contracts and whether it was an internal or external change. These dashboards showed what kind of change was being made to the contract (e.g. scope change, design upgrade, etc.), the change as a percentage of the contract, the total change amount by code, and project change details.

Project Change Code Summary with Detail

Figure 3. Project Change Code Summary with Detail

In addition, there are dashboards that include turnaround times for changes – average days to price, review the price, issue the change order, and approve the change order – and details. Finally, we included a report card for turnaround times by studio and individual Contract Administrators.

Company Average Turnaround Time

Figure 4. Company Average Turnaround Times (values are blurred)

   3. Performance Report

 

We built a Performance Report module to be used as a scorecard to see the length of time the company and contractors/consultants is taking to respond to active items. These dashboards also allow for a comparison of contractors/consultants based on their average response times and types of documents they generated on past projects. When starting new projects, this information is useful to help make decisions in choosing consultants to be part of the project team, based on their past performance.

Company Average Response Times

Figure 5. Company Average Response Times (values are blurred)

   4. Project Stats

 

This dashboard shows Principals and other stakeholders statistics for weekly submittal and RFI activity. These dashboards are well suited to subscription e-mails, where the snapshot can be automatically e-mailed out on a schedule. The collaborative nature of Tableau allows the reporting to be available to any project stakeholders, including Owners, without having to rely on Contract Administrators to generate and export reports.

RFI Weekly Stats dashboard

Figure 6. RFI Weekly Stats

   5. Submittal Tracking

 

To help track shop drawings and other contractor submissions, we helped DIALOG create a submittal tracker so Document Controllers can view where documents are in the process, across all active projects. Ensuring documents are not delayed is very important because architects and engineers must approve plans that comply with the contract before the project can proceed.

Picture1.png

Figure 7. Open Submittals

In the past, DIALOG was running manual reports and adding data to excel in order to manage their projects. They were also going into individual projects and running reports separately to view the status and details of each project. By using Onware’s software and connecting the data to Tableau, we built project reports, visualizations and other dashboards that combined information into one area without having to open multiple projects and run them one at a time. As a result, we were able to help make contract and project administrators, as well as, project executives’ jobs more efficient.

Results

The dashboards allow project teams to quickly see what kind of changes are most common for outstanding documents, which projects they are from, and who is needed to respond to the outstanding item. In addition, executives are able to filter to see all projects and active document types associated with each studio. This not only can promote healthy internal competition between studios but also allows them to validate studios that have abnormally large values. For example, DIALOG discovered that some active items with large response times were due to gaps in process – some projects and documents were not closed after being completed for years, and some jobs were still assigned to Contract Administrators that had left the company.

 

In addition to displaying important information about active items and documents, many of our dashboards implement a drill down functionality. This allows for someone to find the details on the active item. Furthermore, we built tooltips showing the document number and title, clicking on an item will redirect the user to the actual electronic document in Onware. The idea is to have the ability to view the documentation side-by-side with the dashboard open to see what work needs to be done.

Figure 8. Link directly to the specific document from the Active Items Drill Down

We included an aging chart to indicate average response times to documentation. Those with significantly higher than average aging values may need to be investigated. Once again, clicking on the data points will redirect the user to the document on Onware.

Our performance report shows a consultant scorecard so we can see which projects the consultant has worked on and the average response time to active documents. This scorecard helps DIALOG choose consultants (e.g. electrical, structural, mechanical, etc.) that respond quickly, thus, reducing potential project delays and budget overruns. In addition, our dashboard allows DIALOG to view contractors they have worked with in previous projects and their performance – e.g. days it took to respond to documents. This also helps them see which contractors are good for specific type of projects (e.g. healthcare, commercial, public space, etc.).

Figure 9. Aging of Outstanding Items

Onware and other construction management software (e.g. Procore) typically has data that is siloed to particular projects. In other words, to find information you have to choose specific parameters – for example, you pick the company, choose the project, select the details - before the information will display. Onware Business Solutions is inexperienced in connecting to these construction management software to get the information out of the silos and obtain an overview of all the projects and details that go along with it. Furthermore, it becomes easy to go from a high level to a more granular level of detail.

Figure 10. Automated Report sent to Partners weekly

(some values are blurred)

Because Principals at DIALOG are busy, they typically want to see a brief overview of their projects. If there are problems, they can locate the Contract Administrator in charge of the specific job and find resolutions for issues. Therefore, on the Tableau Server every Friday at 4:00 PM, we run some custom code that connects to Tableau’s Application Programming Interface (API). It queries the Onware database to find active projects, the Principal in Charge, and the Contract Administrator. This process is automated, and the query combines four dashboards into a single page report card that is exported into PDF and emailed directly to the Principals and Contract Administrators.

Future Improvements

There is additional functionality that we can implement to improve data quality and process. For example, we can create data alerts to check projects that are active but do not have an studio assigned to it. This automatic data alert can be sent to an administrator to correct the data in the database.

 

We can build many different dashboards for construction management. What we talked about in this case study is only the tip of the iceberg. Individual project owners typically have custom reporting requirements and formats that they are looking for. Tools like Tableau, along with a certified data source, turn generating these custom reports into an easy drag-and-drop exercise. Onware Business Solutions are experts at connecting to data sources and building reliable data sources in Tableau. Contact us today and let us help you see and understand your data.

Questions?

Call us and speak to one of our data experts and we will answer any questions you may have. We want to help you unleash your data through visual analytics as well, we offer 'proof of concept' packages to connect your data to our set of starter dashboards.

 

Visit our website for more content including additional case studies, news and events, and tips and tricks to elevate your visual analytics.

Construction Management Gallery 

  • Facebook
  • Instagram
  • LinkedIn
  • Twitter
  • YouTube

© Onware