CDOT Engineer Saves Thousands Through Automation
By Alex Sheehan, Web Development Intern
May 28, 2019
“I want it to be simple to use,'' says CDOT Engineering Estimates and Market Analysis (EEMA) Manager, Stephen Bokros, about his innovation. In fact, simplicity is at the heart of his application, a program that calculates the Colorado Construction Cost Index (CCCI). The new application vastly improves the efficiency of a process the Division of Project Support completes every quarter. His innovation is saving tens of thousands of dollars for the Colorado Department of Transportation (CDOT) due to gains in automation.
The CCCI is extremely valuable for CDOT Project Managers and must be prepared every quarter for the Chief Engineer. The construction industry uses these market indicators to forecast trends in construction and material prices. Charting the index over time helps identify any changes in the cost of standard items used in CDOT projects for Project Managers and the Chief Engineer (see Figure 1 below). Bokros and Roy Pallman were able to identify the multistep process for calculation as a pain point for the team member who is performing the data crunching. It would take around two work weeks for a Professional Engineer 1 on the Project Support team to crunch the series of numbers for generating the Construction Cost Index (CCI).
Figure 1. CCI per Quarter 2012-2018.
The Division of Project Support helps facilitate every design or construction project advertised by CDOT. All projects are assigned engineering cost estimates that are the result of thoughtful judgments of historical data and current market conditions.
Before Bokros developed his innovation, he observed the numerous steps involved when doing statistical analysis in Excel sheets. He realized that he could automate these processes using Microsoft Access. Access allows for a database, with the main objects being tables, queries, forms, reports, macros, data macros, and modules.
Bokros and the EEMA team worked smarter, not harder to develop the automated solution with a tool that was better suited for running these calculations than Excel. By importing all the past CDOT project cost data into an Access database and creating conditional queries and relational tables, Bokros was able to automate the process. Now, the only thing needed to calculate the CCI is the quarter end date, and Access will run the calculations in under two minutes. The GIF image below shows this automated process.
Bokros’ solution to calculating the CCI more efficiently will save tens of thousands of dollars for CDOT by requiring fewer staff hours. Previously the process took approximately 80 hours of tedious manual statistical analysis, cross referencing different Excel sheets with all past data of material prices for every project recorded at CDOT, then splitting the data into five different categories: earthwork, hot mix asphalt, concrete pavement, structural concrete, and reinforcing steel. Following the data categorization, weighted averages and quantities were calculated after removing outliers. One reason the old process took so long is because Excel does very poorly with huge amounts of data. For each CDOT project, there would typically be hundreds of rows of data of price information from every bidder.
Key Benefits
A CDOT employee saw an opportunity to help himself and his team work more efficiently. As you can see from the above GIF, Bokros was successful in simplifying the long process of calculating the CCI from several weeks of work to just a few minutes. Bokros’ innovation is fascinating and truly is an idea of our time. We hope to continue seeing more software processes becoming simplified and automated, speeding up process efficiency and saving money. Thanks to the EEMA team: Kenny Auge, George Demos, Greg Jones, Sina Khavary and Roy Pallman for their hard work and knowledge to make this happen. Casey Hensely, Assistant Area Engineer, also assisted with this project.