The Background 

The client is a US-based integrated engineering & construction services enterprise with expertise in the design, installation, management, service, and maintenance of HVAC, mechanical, electrical, plumbing and control systems. Pricing and quotation management of their projects were becoming too complicated to manage manually, resulting in higher lead time for estimations & error-prone and inefficient control over design changes and adjustments. In addition, they lacked the tools necessary to ease these functions, running the risk of losing customers due to the ever-rising estimation delays.  

 

The Challenge   

The mode of operation employed by the client for estimation was marred by several inefficiencies that compromised many quality parameters. Their method involved a cumbersome three-part process. First, data from a third-party application had to be exported to the cloud due to its sheer volume before it could be moved to Excel spreadsheets for analysis and estimation. The Excel sheets had many macros and couldn’t handle the volume of data coming in.  Each step of this task took very long and, many a time, caused the systems to crash. This also meant the company had zero control over their design data, with data leaks and loss becoming an overhanging threat.     

Another grave challenge lay in enabling multiple user collaboration to safely work on the same platform since the various disciplines in construction entail various engineers working on the same spreadsheet simultaneously. This meant large volumes of idle time, inexcusable in large projects such as the customer engaged with. In addition, the platform was limited by its unreliable security systems. It was not conducive to collaboration, as multiple versions of the estimates weren’t possible either, as their estimate approval process wasn’t streamlined.  

   

The Solution   

Team Experion started by extensively studying the client’s current operations to identify their pain points and the root causes of the issues. Next, the team analyzed the spreadsheet templates that they use to understand the business rules and extract the logic in the Excel macros. The spreadsheets proved difficult to even look at, let alone work on; it was clear that transferring such volumes of data from endless matrices time and again would not work, and a paradigm shift was the need of the hour.   

It was the determined effort of our expert programmers which materialized as a one-of-its-kind plugin that would later prove to be the cornerstone of our success. The plugin would serve as a bypass to the cloud, automating data export from the third-party software to the quoting system with no intermediary storage involved. In addition, to tackle the major issue of low speed and inefficiency due to the sheer size of data, they designed an ETL process that automatically triggers when new data arrives and performs concurrent transformations using Serverless functions (AWS Lambda).  

To ensure end-user acceptance and retention, the client insisted on maintaining the look and feel of the Excel sheets – they wanted their engineers to easily begin using the new application without any time wasted on adjusting to a different user experience. The team was able to deliver on this aspect as well, using a customized version of SpreadJS. 

To allow multi-user collaboration, where partners can see each other’s updates and work of various parts of the same project, our developers implemented a token-based locking system where the holder of the token (which can be passed onto others by the token holder) can edit while the others view the proceedings; we also implanted tools to ensure that all manual modifications are reflected in multiple sheets and estimation sheets real-time.   

As a result, the customer could reduce estimation turnaround time through the application, which imports multi-trade design and project data from the third-party software and generates project estimations without the need for manually managing data in sheets. In addition, the automated estimation tool provided increased accuracy in the estimation process through project estimation version support, approval workflows, import and manual adjustments at both granular and overall project levels.   

 

The Payoff   

  • Easy conversion of individual trade specific design data from the third-party software to consolidated project estimations  
  • Easy to create business analytics reports (comparative analysis, etc.) in comparison to the software used previously  
  • Automated data import process reduced manual effort and errors  
  • Better manageability by replacing the traditional model of estimation preparation through multiple files with a centralized application  
  • Multi-user collaboration space with data security checks  
  • User interface design that resembles spreadsheet kind of data presentation to which existing users can easily adapt  
  • Error reduction in multi-trade consolidated estimates