Color Coded Excel – Not the Right Solution

Laptops and spreadsheets

Every project requires insurance – we all know that. It’s how we track that insurance that is still managed in a manner that is outdated and most likely putting your company at risk. 

 

I’m talking about excel and no, conditional formatting is not an upgrade to excel nor does it do a better job of reducing risk transfer.  I have spent that last few months talking to many companies from all over the country and the majority of them have this same process for tracking insurance:

• Receive certificate 
• Manually verify it matches project requirements
• Enter the vendor name, policy number, and expiration dates into excel
• Repeat above for each policy (auto, workers comp, umbrella & GL)
• Repeat above for every vendor
• Track expiration dates to insure policies on projects don’t expire 

 

If you’re a sophisticated company then you may have macros built into excel or conditional formatting with color coding to “alert” you as to when a policy is going to expire. 

 

Red-Yellow-Green cannot be the answer to tracking insurance. This system is actually a simple database to house data related to documents that are not stored with the data.  Color-coded excel is NOT a proactive insurance tracking and management system.  This is how many companies get into trouble.

 

If you are managing only one construction project you (on average) will have to track 30 COI with 4 policies per COI.  That is 120 dates – if you aren’t tracking tiered subs and suppliers.  Its easy to see how this can get unwieldy. 

 

The problem is that this database method only works as a tracking tool to manage insurance if the person managing the sheet is actively (this means daily) reviewing and updating data, looking for soon to expire certificates or looking for the color coding to point them in the right direction.  

 

What’s the downside if the color coding is not accurate or if the spreadsheet is not being inspected daily? If a subcontractor’s policy expires and is permitted on site they are no longer insured for that project. 

 

If an accident were to happen with that subcontractor then all that risk would transfer to you – the Contractor.  Do you think the Superintendent, Compliance officer, Project Manager or Owner would be aware of this situation?  In most cases not until it’s too late. 

 

Like I said earlier there has to be a better way.  Why are we still doing it this way? How can we do it differently?  

 

Now you can – with billy.  billy is the first end to end insurance tracking platform for construction.  billy can request, verify, track, renew and communicate with the field automatically on all your projects.  Construction is successful when the entire team – office to field – are working together and billy makes that happen. How do I know?  I have been in the construction industry for over 25 years and was tired of conditional formatting “protecting” my company.  So I created billy!

The Importance of Tracking Insurance

Before I dive into the importance of insurance tracking I feel it’s important to understand my long history in construction since; I feel it’s extremely relevant to the topic of compliance.

 

I am a self-proclaimed construction nerd.  I love construction, the construction industry and most of all, the diverse group of people and companies that make up this amazing sector in our economy.  I caught the bug early on when I started framing housing while still going to high school. While that experience was fun and I was making good money it was not the career I wanted. 

 

Realizing I needed a degree in order to enter the large commercial market I traded my nail bags for a pencil (computers were not mainstream yet) and I earned a B.S. in Construction Management from Cal Poly San Luis Obispo and immediately went to work for one of the largest commercial general contractors in the nation. Over the next 10 years I graduated up the construction ladder from Project Engineer to a senior level Project Manager. 

 

While I loved the projects I was working on I understood that I wanted to spend less time on a freeway and more time at home so I made my way back up to San Luis Obispo where I was fortunate enough to work for a publicly traded home building company.  I was running all the land development and I was definitely not on a freeway very much at all.  After 6 years Wall Street decided our small division didn’t check all the boxes needed for a public company and we closed our successful branch.  So what is the next logical step for a well seasoned construction professional?  Start your own company. 

 

With a business partner we started our own general contracting company and grew it to a $35M/ year company in just 6 short years.  This is when I became passionate about technology in construction.  I started to question how and why we do everything we do on a day to day basis. 

 

I’m not saying we need to stop, but it seems like there has to be a better way. I knew I needed to find a way to help change this industry for the better.  

 

So why the back story? We are about to talk about insurance tracking in construction – exciting I know – but this is a process that affects many people and roles in a company. 

 

I have been fortunate to have been in all of those rolls which gives me an unique understanding of this important part of our industry as a whole but also how it impacts the day to day operations of individuals.  And let’s be honest.. Insurance verification and tracking is mission critical to the success of a company but for many companies it doesn’t seem to get the attention it deserves.  

 

Simply signing a contract is not enough to protect you.  Every project requires insurance and we know we must diligently track those certificates. 

 

But why do we painstakingly put all those names and policy expiration dates into a tracking system.  To minimize the transfer of risk.   But what exactly does that mean?  When a subcontractor or vendor issues you a COI – naming you (and others) as additionally insured – you are holding evidence that they have insurance to cover any costs arising from issues on a project. 

 

If this COI is not accurate or current all of the risk is transferred to your policy.  In simple terms, your insurance policy is now covering any costs related to accidents or defects on the project.  

 

Based on our research there are 2 pain points in this process.

• Verification.  Is the person reviewing the COI qualified or diligent enough to accurately verify it meets all the requirements of the project.

• Tracking. Once the COI is verified it has to be proactively tracked to ensure the same COI is issued annually prior to the policy expiring. 

 

Sadly in 2021 insurance is still managed in outdated systems that most likely are putting your company at risk.  Simply entering data into a spreadsheet or other system not specifically designed for COI tracking does not eliminate risk transfer. 

 

Conditional formatting with color codes or macros to provide alerts of expiring policies is not an upgrade nor does it do a better job of protecting your company.  I have spent that last few months talking to many companies from all over the country and the majority of them have this same process for tracking insurance:

• Receive certificate
• Manually verify it matches project requirements
• Enter the vendor name, policy number, and expiration dates into excel, accounting software or other non-insurance specific software
• Repeat above for each policy (auto, workers comp, umbrella & GL)
• Repeat above for every vendor
• Track expiration dates to insure policies on projects don’t expire

 

Let’s go back to those rolls and see how this process impacts their day to day: 

• Project Administrator/ Contracts Administrator: Responsible for receiving, validating and entering insurance certificates into that magic excel sheet. Communicates insurance status to the Field team via email or weekly meetings.

• Project Engineer: (Depending on company set up) Responsible for receiving, validating and entering insurance certificates into that magic excel sheet. Communicates insurance status to the Field team via email or weekly meetings. All other PE duties.

• Superintendent: Focused on project schedule and all site activities.  Superintendent must have confidence all vendors/ subcontractors are insured and permitted to be on site so as not to impact schedule. 

• Project Manager: Focused on project schedule, budget, compliance and company risk. Project manager must coordinate all owner responsibilities as well and insuring the project is being managed properly by the others on the Team.

• Compliance Officer: Needs to have confidence the vendors and subcontractors are compliant and insurance is being tracked properly.  In short they want to see that excel sheets are being filled out and updated regularly but proactive management is left to those entering data into the spreadsheets.

• Company Owner:  Manages the risk and growth of the company for the benefit of everyone.  Trusts in their teams to manage budgets, schedules, and risk in a manner that protects the company and in turn helps promote revenue growth.  Is watching long term strength and growth so the company can sustain itself into the future. 

 

If you look carefully at these rolls you start to understand that those individuals that are most concerned with the impacts of poorly tracked insurance are far removed from the actual tracking and verification of the certificates and policies. 

 

The executives and owners put all their confidence in the accuracy of the data entered by an admin or PE. Remember, just because data is in a software does not mean the job is done nor that the data is accurate.  In most instances these current solutions are simple databases and not designed as tracking software. 

 

It only works as a tracking tool to manage insurance if the person managing the sheet is actively (this means daily) reviewing and updating data, looking for soon to expire certificates or looking for the color coding to point them in the right direction – color coding set up by them in the first place.  If you start to imagine the depth of this you can quickly realize the potential for lapses in coverage and potential for risk transfer.  

For example: 

• 1 construction project = +/- 30 subcontractors
• 30 subcontractors x 4 policies per subcontractor
• 2 tiered subs on average per subcontractor
• 4 policies per tiered subcontractor 
• This equates to:

• 90 COI’s to verify
• 360 Policy dates to enter and track and renew

• Now imagine you have 5 projects per year (average for a SMB) – thats 

• 450 COI’s to verify
• 32,400 Policy dates to enter and track and renew

 

So what’s the downside of not tracking insurance? If a subcontractor’s policy expires and is permitted on site they are no longer insured for that project. 

 

If an accident were to happen with that subcontractor then all that risk would transfer to you – the Contractor. Out of that 32,400 policies to track it only takes 1 mistake to cost millions. I know – my company was sued for over $8M on a $1.5M project due to a certificate of insurance expiring and the sub was permitted on site. One simple error in managing an excel spreadsheet cost millions.

 

It’s tedious and time consuming but the downside can be huge.  All contractors need to not just track insurance but take the time to make sure they are using a software or system to track it properly. 

 

My advice in all this is to not try to have insurance tracking be a side benefit of another software solution, or try to use a spreadsheet that relies heavily on data entry and visual inspection to track something so important. 

How to Track Insurance Successfully

In construction, you always hear the same two keys to success – “On Time” and “On Budget”.  These mantras are great and hitting these metrics will make you successful.  But all the success in the industry will disappear in a split second if you are not actively managing insurance on your projects. 

 

I would be willing to bet that many company owners are not able to properly read and evaluate a Certificate of Insurance (COI) properly.  I’ll be honest, I wasn’t able to until I saw all that success pulled away from me due to a simple COI expiring on a project and all that risk didn’t transfer.  It stuck to me and my company and my bottom line suffered – despite that project coming in “On Time” and “On Budget”.  

 

So like anything that happens in our industry we must adapt and learn from our mistakes. After all – we can’t judge someone on the mistakes they make but on how they address that mistake. After spending some time to reflect I realized that I was not alone.  Most companies in our industry don’t have a good handle on tracking insurance and use archaic systems and in most cases have under qualified staff managing the process.  I boiled it down to 3 Keys to consider when evaluating your current system.  

 

MANUAL ENTRY.
Manually analyzing all of the data and flagging deficiencies, is a time-consuming process that doesn’t allow much room for error (after all, mistaking a single digit, can mean the difference between a $1,000,000 limit or a $2,000,000 limit…) The obvious requirement for a successful method to track & manage insurance certificates in-house is, therefore: 


TIME & KNOWLEDGE.

Time comes in the form of a dedicated, specialized, internal resource that spends an allocated portion of their 40-hour work week carefully reviewing the contents of the incoming certificates of insurance. We highlight “specialized” because this person must have a decent grasp of insurance concepts to be able to knowledgeably review the contents of the certificates. Once reviewed, this person must then be able flag any gaps in the vendors coverage. 

 

EXPERTISE.
This brings up the problem of insurance expertise. How well you’re able to protect your company from third party liability will depend on the reviewer’s grasp of insurance and their ability to interpret certificates of insurance. This is especially the case if you are also reviewing endorsement pages such as the Additional Insured, Waiver of Subrogation, Third Party over Action, etc. Here’s a quick test for the person reviewing the certificates, to assess how meticulously your vendor’s  insurance coverages are being reviewed: 

 

• Are you just looking at expiration dates and limits, or are you actually examining the COI and endorsements to make sure every aspect is compliant? 
• What’s the difference between a CG 20 10 and a CG 20 37 endorsement? 
• What’s the difference between limits “per project” and “per location”? 
• How does your risk change if the COI indicates coverage is ‘per occurrence’ vs. ‘claims-made’? 
• What’s the significance of having the umbrella/excess ‘follow form’? 

 

If you’re not able to answer these questions it could mean the difference between “tendering the claim” or having the fork out the policy limits from your company’s own pockets (or your insurance carrier). 

 

It’s important to remember that when evaluating any system you currently have implemented or plan to roll out you can’t forget your greatest resource – your team.  The people that work for you are your greatest asset – more than any customer – and  you need them to embrace your plans.  

 

If they are not on board you will fail before you start.  So consider what commitment your changes will require of them.  On the other hand, if you plan to improve a broken system and in-turn improve their day to day operations then they will be your biggest supporters.  Evaluate everything not only through the eyes of your CFO/ Controller but also your admin in charge of compliance or COI review. 

 

How to Select Software to Manage Your Certificates

Introduction

Tracking certificates of insurance has its challenges. It’s not exactly the most fun task to carry out, but at the same time it is mission critical to any construction business to make sure your third parties are properly insured in order to minimize risk transfer.

 

If you are attempting to handle the entire certificate of insurance tracking process in-house it is important to make sure you have the right tools and resources in place. Our team put together this guide that will help you select the most appropriate insurance and risk management solution to improve your company’s insurance tracking and compliance process.

 

Step 1: Track every vendor and their insurance.

The first step in creating a successful process for tracking your vendor insurance is to actually have criteria for tracking vendor insurance. Sadly, many companies never even get past this step.

 

The ideal solution should track:

• Your vendors – name & trade (at a minimum)
• Projects your vendors are working on.
• Insurance requirements for each project
• Coverage their certificate of insurance affords (policies & limits per trade)
• Insurance expiration dates for each policy (when will each policy expire)
• Compliance status (Who is compliant vs. non-compliant).

 

Step 2: Collect and store Certificates Of Insurance.

Tracking Certificates of Insurance (COI’s) is not just a one-and-done process.  It can be a very arduous task, but let’s face it, not tracking COI’s  has serious consequences. Vendors don’t often think about insurance after they sign their contract and you quickly find yourself chasing down vendors requesting their certificates.

 

The ideal solution should: 

• Initiate the COI request for with all your vendors at contract signing.
• Collect & sort certificates from all vendors on all jobs.
• Store all certificates in an organized and easily accessible manner.

 

Step 3: Review of the Insurance Coverage

At a basic level, most of the information you know about your vendor’s insurance coverage is contained on the standardized Acord 25 form. Interpreting this form and knowing where to find the information you need on the COI is critical to the success of your insurance tracking system. At the end of the day, it’s not about simply “tracking” coverage, it’s about tracking that they have the right coverage.

 

The ideal solution should: 

• Analyze and interpret data on insurance certificates
• Compare that data against project requirement and flag deficiencies
• Verify all required back up endorsements and waivers are included
• Create a clear and concise audit trail

 

Step 4: Follow-up and Corrective Action

Simply reviewing the information on a COI is not enough. If a COI does not accurately match the specific requirements for a project those deficiencies must be communicated back to the vendor or perhaps their broker as well. Remember, the goal is to track coverage and you can’t track the right coverage until you have the right coverage.

 

The ideal solution should: 

• Highlight all deficiencies where a COI does not match project requirements
• Separate deficiencies between coverage types
• Communicate with vendor and broker for correct all deficiencies
• Close the loop when a COI is deemed acceptable

 

Step 5: Expired Insurance Policy Alerts & Automating Renewal Requests

Unfortunately tracking insurance doesn’t stop once you have deemed a COI in compliance with project requirements.  Insurance policies renew annually and therefore this entire process continues – annually for every policy for every vendor.  Since coverage is considered lapsed on a project if a policy expires then it’s your responsibility to maintain continuous coverage for all vendors.  Yes, this means going back to Step 2.

 

The ideal solution should: 

• Maintain a log or dashboard showing overall compliance and soon to expire COI’s
• Alert you to expiring policies a minimum of 60 days in advance
• Communicate with vendors and brokers of expiring policies
• Request, receive, verify and validate renewed COI policies meet project requirements.

 

What is billy

billy checks all the boxes!  billy automates the request, receipt, data entry, verification and tracking of all the COI’s on all your projects.

 

billy is the time-saving tool that helps you verify that your vendors have the right insurance coverage.

 

billy automatically verifies all insurance certificates are correct and comply with project requirements – minimizing your risk.

 

We constantly monitor your certificates and notify you when a certificate has not been filed, changed, does not match a previous version, or is expiring.  You never have to worry about a vendor being on site that does not have proper insurance coverage.  billy has you covered.

 

Through our centralized dashboard, you receive complete visibility into:

• Who is on your projects
• What insurance they have
• When their policies expire
• Should they be on-site

 

At billy, we don’t believe you should ever buy a product that doesn’t deliver value.

 

Try us for free today and start properly transferring your risk immediately.