garbage in = garbage out? how data characteristics and details drive the results lizzie edelstein...

Garbage In = Garbage Out?How Data Characteristics and Details Drive the Results

Lizzie Edelstein & Brandon KatzOctober 9, 2014

THIS AREA IS FREE FOR GRAPHICS/IMAGES OR SOLID

COLOUR OR MIXTURE

Introduction

Data Requirements

Modeling Process

Impact on Loss Results

Q & A

Case Study Set-up

Agenda

© 2014 JLT Re. All rights reserved.

Framework of Catastrophe Modelling

Calculate Damage

Quantify Financial LossAssess HazardGenerate Stoch.

Events

Policy Conditions

DATA QUALITY

Portfolio

$$$$$$$$$$$$

Apply Vulnerability

Flo

od

H

eig

ht

% Damage

2nd floor

1st floor

Geo-locating BuildingCharacteristics


Data Quality - Components

Key Data to Capture

Portfolio Exposure Value by Location•Coverage Specific•Limits vs. Replacement Costs

Geographic Information

Primary & Secondary Building Characteristics

Policy Structure•Limits/Deductibles/Excess Layers•Wind/Flood/Quake Endorsements•Facultative Reinsurance

Model Output Required•Settings•Output Detail

$$6


Portfolio Exposure Value by Location (Coverage Specific Replacement Value)


Building (BLDG)Building (BLDG)

Contents (CNTS)Contents (CNTS)

Business Interruption (BI)Business Interruption (BI)

Typically Provided

Sometimes Provided•Often assumed as a percentage of BLDG

Sometimes Provided•Often assumed as a percentage of BLDG

621 Burr St., Melbourne, FL 32901

Geographic Information

Actual Building Interpolated (RMS)

621621


630630610610

Location with just street address

Location provided with Lat/Long

Exposure Data – Primary Building Characteristics


Occupancy

Construction

Number of Stories

Year Built

Square Footage

Example of ISO Fire mapping to RMS construction classes

Some other construction classes include:

– Manufactured/Mobile Home (With or Without Tie-downs)

– Automobiles (Personal or Dealers)

– Boats (Various options for length and power/sail)

– Inland Marine (Bridges, Towers, Cranes, Pipelines, etc.)

Construction – Examples


ISO Fire Class RMS Class

1 - Frame 1 - Wood

2 - Joisted Masonry 2 - Masonry

3 - Non-Combustible 4A - Steel Frame (3 - Reinforced Concrete for HU)

4 - Masonry Non-Combustible 3 - Reinforced Concrete

5 - Modified Fire Resistive 4A - Steel Frame (3 - Reinforced Concrete for HU)

6 - Fire Resistive 3 - Reinforced Concrete

7 - Heavy Timber Joisted Masonry 2 - Masonry

8 - Superior Non-Combustible 4A - Steel Frame (3 - Reinforced Concrete for HU)

9 - Superior Masonry Non-Combustible 3 - Reinforced Concrete

Occupancy – Examples


Exposure Data – Secondary Building Characteristics

Varies by Peril

Hurricane/SCS

– Roof Shape

– Construction Quality

– Cladding Type

– Window Protection

– Roof Age

– Roof Anchorage System

Earthquake

– Building Foundation

– Building Shape

– Construction Quality


Model Output


Earthquake Earthquake Severe Convective StormSevere Convective Storm

TerrorismTerrorism

HurricaneHurricane

FloodFlood

Data Requirements


Data Quality – Modeling Process

Cat Modelling Process – the Flow of Information

Aggregated and Scrubbed Data

Raw Data from Clients

Review/Reformat Data

Run AnalysesProvide and Discuss Results with Client


Usually sent as a .txt or .csv file

Raw Data


Data is Cleaned and Scanned

– Garbage In, Garbage Out

Geocoding Resolution

– County

– City

– Zip Code

– Street Address

– Lat/Long

Exposure Summaries are Compiled

– Make sure submitted data makes sense

Raw Data

Florida County 2011 TIV 2010 TIV Change Bay 175,537,949 183,731,471 -4.46%

Brevard 561,923,070 583,552,958 -3.71%

Broward 2,555,108,052 2,496,744,740 2.34%

Charlotte 171,919,482 181,171,364 -5.11%

Collier 319,851,669 320,187,406 -0.10%

Duval 674,587,492 674,646,496 -0.01%

Flagler 172,156,458 174,990,748 -1.62%

Hillsborough 819,204,210 1,080,769,404 -24.20%

Lee 1,078,505,133 1,055,253,295 2.20%

Manatee 384,743,099 374,663,996 2.69%

Miami-dade 2,077,115,268 2,177,799,603 -4.62%

Orange 212,911,303 222,431,251 -4.28%

Palm Beach 2,584,484,629 2,296,291,771 12.55%

Pasco 433,738,820 368,017,790 17.86%

Pinellas 1,971,114,618 2,111,487,128 -6.65%

Sarasota 508,757,527 505,893,726 0.57%

St. Johns 357,838,294 362,633,140 -1.32%

St. Lucie 302,740,301 300,064,037 0.89%

Volusia 561,286,665 573,038,352 -2.05%

Total 15,923,524,041 16,043,368,677 -0.75%


Deeper dive into the data?

Do they only write in certain states/counties/tiers?

Are all lines of business captured?

Do all risks have a city/state?

Does the zip code match with supplied city?

Do limits make sense for each risk?

Any exceptions for peril deductibles?

Occ/Const/Num Stories/Year Built?

Data Review Checklist


Review/Reformated Data


Prepare Data for Import

Final Data Checks/Comparisons

Maps

Import

Run Analyses


Select Appropriate Perils

Apply Treaties Correctly

Selecting Appropriate Options (Near/Long Term, Demand Surge, Storm Surge)

Run Analyses


Run Analyses (Example: NA Hurricane)


Data Quality – Impact on Loss Results

Data Quality – Impact on Loss Results

Characteristic Information AAL % Change

Zip Unknown

Street Address Unknown

Parcel Unknown

Occupancy Unknown

Construction Unknown

# Stories Unknown

Year Built Unknown

Area Unknown

Secondary Unknown

220.96 —


Zip 32901

Street Address Unknown

Parcel Unknown

Occupancy Unknown


# Stories Unknown

Year Built Unknown

Area Unknown

Secondary Unknown

232.52 5.2%


Zip 32901 220.96 —

Street Address 621 Burr Street, Melbourne, FL

Parcel Unknown

Occupancy Unknown


# Stories Unknown

Year Built Unknown

Area Unknown

Secondary Unknown

240.83 3.6%


Zip 32901 220.96 —

Street Address 621 Burr Street, Melbourne, FL 232.52 5.2%

Parcel 28.069065 -80.609726

Occupancy Unknown


# Stories Unknown

Year Built Unknown

Area Unknown

Secondary Unknown

222.34 -7.7%


Zip 32901 220.96 —


Parcel 28.069065 -80.609726 240.83 3.6%

Occupancy Single Family


# Stories Unknown

Year Built Unknown

Area Unknown

Secondary Unknown

245.99 10.6%


Zip 32901 220.96 —


Parcel 28.069065 -80.609726 240.83 3.6%

Occupancy Single Family 222.34 -7.7%

Construction Wood Frame

# Stories Unknown

Year Built Unknown

Area Unknown

Secondary Unknown

235.89 -4.1%


Zip 32901 220.96 —


Parcel 28.069065 -80.609726 240.83 3.6%


Construction Wood Frame 245.99 10.6%

# Stories 1

Year Built Unknown

Area Unknown

Secondary Unknown

282.04 19.6%


Zip 32901 220.96 —


Parcel 28.069065 -80.609726 240.83 3.6%



# Stories 1 235.89 -4.1%

Year Built 1987

Area Unknown

Secondary Unknown

317.43 12.5%


Zip 32901 220.96 —


Parcel 28.069065 -80.609726 240.83 3.6%



# Stories 1 235.89 -4.1%

Year Built 1987 282.04 19.6%

Area 1440

Secondary Unknown

354.36 11.6%


Zip 32901 220.96 —


Parcel 28.069065 -80.609726 240.83 3.6%



# Stories 1 235.89 -4.1%

Year Built 1987 282.04 19.6%

Area 1440 317.43 12.5%

Secondary Gable roof, unknown pitch


Zip 32901 220.96 —


Parcel 28.069065 -80.609726 240.83 3.6%



# Stories 1 235.89 -4.1%

Year Built 1987 282.04 19.6%

Area 1440 317.43 12.5%

Secondary Gable roof, unknown pitch 354.36 11.6%

Overall 354.36 60.4%

Homeowners Coverage A: 60,000 HO-3 Coverage B: 6,000

Coverage C: 30,000 Coverage D: 12,000


(Building Located in Palm Beach, FL – An Illustrative Example)

Data Input Sensitivity: RMS Case Study

Base Case: Industry Default SettingsUnknown Defaults to Industry Average

Source: RMS Legal disclaimer note from RMS: “The results quoted in this case study are for illustrative purposes only. Do not assume that they represent actual loss estimates for Palm Beach, FL or any other location.”

Example: RMS v11 Storm Surge

New York, NY

TIV $66.3M

Distance to Coast ~150 ft

Multi-Family Dwelling (Apartment/Condo)

AAL without Storm Surge $4,981

AAL with Storm Surge $119,830

Data Quality – Impact on Loss Results (Peril Options)


What if you didn’t have the correct address of the location….

Data Quality – Q & A

Data Quality Quiz

Which Occupancy type is better in a windstorm?


Mobile Home

Single Family Dwelling

Which Construction is better in a windstorm?


Masonry

Wood Frame

Which Construction is better in an earthquake?


Masonry

Wood Frame

Which # of Stories is better in a windstorm?


Low rise

High Rise

Which # of Stories is better in an earthquake?


Low rise

High Rise

Which Roof Shape is better in a windstorm?


Which Roof Anchors are better in a windstorm?


Clips

Double Wraps

Which Roof Coverings are better in a windstorm?


Wood Shakes

Clay Tiles

Data Quality – Case Study

Groups will receive one of three datasets

– Northeast Super Regional writing hurricane

– Midwest Mutual writing tornado hail

– National writing earthquake

Look through the data set and see if you can find questionable data entries

– Highlight the errors or possible errors

Results will be shown modeled as received and as corrected as a comparison

Case Study Introduction


Case Study Introduction – Exposure Concentrations


Case Study – ResultsRMS RiskLink v11


MW Mutual - Tornado/Hail

Return Period Ground-Up Loss Gross Loss Ground-Up Loss Gross Loss10 251,754 223,253 217,502 198,863 20 480,239 448,317 434,623 415,822 50 1,029,041 1,001,285 980,517 962,280

100 1,764,221 1,747,913 1,708,292 1,693,329 250 3,353,660 3,346,137 3,216,057 3,212,263 500 5,167,639 5,138,954 4,873,663 4,873,663

1,000 7,597,157 7,526,785 7,062,110 7,062,110 AAL 170,227 149,528 150,166 137,270

Analyzed Expos 300,011,158 281,836,993

Good Dataset Messy Dataset

National - Earthquake (Shake Only)

Return Period Ground-Up Loss Gross Loss Ground-Up Loss Gross Loss10 61,477 2 60,837 2 20 572,172 298 558,667 248 50 2,970,602 388,180 2,906,354 363,293

100 6,315,330 2,111,007 6,230,807 2,042,420 250 12,992,610 6,598,273 12,939,970 6,523,231 500 21,354,913 11,205,943 21,377,281 11,172,358

1,000 36,482,242 17,017,705 36,584,901 17,040,601 AAL 285,483 106,578 283,098 105,451

Analyzed Expos 1,157,091,793 1,155,749,725


Northeast Super Regional - Hurricane (Near Term with Storm Surge and Loss Amplification)

Return Period Ground-Up Loss Gross Loss Ground-Up Loss Gross Loss10 2 2 6 1 20 1,311 537 15,187 10,032 50 225,207 184,162 484,708 440,729

100 991,711 907,723 1,576,572 1,508,809 250 3,151,485 3,024,099 4,055,105 3,959,937 500 5,769,087 5,611,842 6,768,455 6,647,259

1,000 9,211,656 9,029,395 10,191,740 10,046,458 AAL 49,539 47,242 64,805 62,570

Analyzed Expos 395,635,422 395,535,422


garbage in = garbage out? how data characteristics and details drive the results lizzie edelstein...

Documents

mixture slide

latlong slide

data characteristics

percentage of bldg slide

square footage slide

construction examples

data quality components

key data