Insurance cost estimating and rating tool for construction projects
Excelling for Turner
When Dave Lawton was hired as the IT director for the risk management department at Turner Construction, he soon met Ira Skop, the SIR partner who had been hired by Turner to help automate their insurance cost-estimating process. It was Ira who had built the Excel-based insurance underwriting tool used to estimate the cost of insurance on Turner construction projects.
“I don’t think I’d ever seen any Excel workbook as complex as what Ira built,” says Dave. “Just the amount of information that was behind the scenes for use in pricing insurance, it was by far the most impressive Excel application that I’d ever seen, and it still holds true today.”
SIR first created the workbook for Turner back in 2001 for its Contractor Controlled Insurance Program (CCIP). Turner’s CCIP is a wrap-up program for all on-site workers’ compensation and general liability insurance coverage for both Turner and its subcontractors. Turner needed a tool that would be used by its business development departments to estimate what insurance would cost on a job and work that back into the total cost of the project.
With Turner’s staff of up to 6,000, the SIR application was ultimately used by approximately 600 people in the company, figures Dave, from entry-level estimators entering numbers and more experienced staff viewing the documents up to business development staff and department managers.
“We have jobs that range from a couple of hundred thousand dollars to hundreds of millions of dollars and they would all use this same tool to estimate their insurance costs,” says Lawton. “Mega jobs would need third and fourth sets of eyes on the numbers.”
For ten years, the SIR Excel application, known as the Insurance Cost Calculator (ICC), was the first step of calculating the carrier-based insurance rates on a Turner project. As each project would progress, the data would also be used to calculate the insurance carrier premium, and track claims.
From the users’ perspective, the ICC Form was deceptively simple. Any user could view the fields needed for entering information, such as contract numbers and contract values. But behind the scenes of the ICC were dozens of tables hidden from the user, with formulas and calculations “happening magically” on screens behind the worksheets.
“Each individual spreadsheet is almost like its own program, a self-contained piece,” explains Dave. “It could proliferate and be all over the place. It also has the ability to consolidate the data; it would automatically be emailed to the insurance department where it would be detached from the email and the data then imported into a database that consolidated all project estimates. From there, the data were also fed into other systems.”
Another positive aspect about the SIR application is its flexibility. With an insurance program that was constantly growing and changing, Turner benefitted from SIR’s ability to swiftly and succinctly understand any problem in the system and turn it around quickly. At the time the project began, the Internet was newer, slower and less reliable.
The fact that the workbook was stand-alone and self-contained meant one didn’t have to be online to open it, read it or work on it.
At the same time, improvements in the speed and reliability of the Internet eventually allowed Turner to consider investing in a new solution that would allow for sharing data across its departments, and plug in with other systems.
“We have matured to the level where we know how the program is going to work,” says Lawton. “It’s not like it used to be, we wanted something a bit more structured, since data isn’t really changing from year to year as it once had.”
As a result, Turner took the entire SIR tool, and migrated all the logic into a new application. Given the fact that the SIR application had worked so well, many of the tables in the new application look the same, as well as the forms themselves, making it easier for the business units to continue their work without any major technical
“The SIR worksheet was of such critical importance, it was a tool we had to have,” says Lawton. “We wouldn’t be where we are today without those calculations. We could quickly turn around insurance estimates to owners or business units without going to insurance carriers. It sped up the process.”
Working with SIR, says Lawton, has always been a breeze. The team is made up of insurance experts who speak insurance language, never requiring any translation. That, combined with their technical language, creates a “very boutique sort of firm,” he adds. “I haven’t seen anyone else who really can do this work.”