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
© 2014 JLT Re. All rights reserved.
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
© 2014 JLT Re. All rights reserved.
Portfolio Exposure Value by Location (Coverage Specific Replacement Value)
© 2014 JLT Re. All rights reserved.
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
© 2014 JLT Re. All rights reserved.
630630610610
Location with just street address
Location provided with Lat/Long
Exposure Data – Primary Building Characteristics
© 2014 JLT Re. All rights reserved.
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
© 2014 JLT Re. All rights reserved.
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
© 2014 JLT Re. All rights reserved.
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
© 2014 JLT Re. All rights reserved.
Model Output
© 2014 JLT Re. All rights reserved.
Earthquake Earthquake Severe Convective StormSevere Convective Storm
TerrorismTerrorism
HurricaneHurricane
FloodFlood
Data Requirements
© 2014 JLT Re. All rights reserved.
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
© 2014 JLT Re. All rights reserved.
Usually sent as a .txt or .csv file
Raw Data
© 2014 JLT Re. All rights reserved.
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%
© 2014 JLT Re. All rights reserved.
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
© 2014 JLT Re. All rights reserved.
Review/Reformated Data
© 2014 JLT Re. All rights reserved.
Prepare Data for Import
Final Data Checks/Comparisons
Maps
Import
Run Analyses
© 2014 JLT Re. All rights reserved.
Select Appropriate Perils
Apply Treaties Correctly
Selecting Appropriate Options (Near/Long Term, Demand Surge, Storm Surge)
Run Analyses
© 2014 JLT Re. All rights reserved.
Run Analyses (Example: NA Hurricane)
© 2014 JLT Re. All rights reserved.
Run Analyses (Example: NA Hurricane)
© 2014 JLT Re. All rights reserved.
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 —
Characteristic Information AAL % Change
Zip 32901
Street Address Unknown
Parcel Unknown
Occupancy Unknown
Construction Unknown
# Stories Unknown
Year Built Unknown
Area Unknown
Secondary Unknown
232.52 5.2%
Characteristic Information AAL % Change
Zip 32901 220.96 —
Street Address 621 Burr Street, Melbourne, FL
Parcel Unknown
Occupancy Unknown
Construction Unknown
# Stories Unknown
Year Built Unknown
Area Unknown
Secondary Unknown
240.83 3.6%
Characteristic Information AAL % Change
Zip 32901 220.96 —
Street Address 621 Burr Street, Melbourne, FL 232.52 5.2%
Parcel 28.069065 -80.609726
Occupancy Unknown
Construction Unknown
# Stories Unknown
Year Built Unknown
Area Unknown
Secondary Unknown
222.34 -7.7%
Characteristic Information AAL % Change
Zip 32901 220.96 —
Street Address 621 Burr Street, Melbourne, FL 232.52 5.2%
Parcel 28.069065 -80.609726 240.83 3.6%
Occupancy Single Family
Construction Unknown
# Stories Unknown
Year Built Unknown
Area Unknown
Secondary Unknown
245.99 10.6%
Characteristic Information AAL % Change
Zip 32901 220.96 —
Street Address 621 Burr Street, Melbourne, FL 232.52 5.2%
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%
Characteristic Information AAL % Change
Zip 32901 220.96 —
Street Address 621 Burr Street, Melbourne, FL 232.52 5.2%
Parcel 28.069065 -80.609726 240.83 3.6%
Occupancy Single Family 222.34 -7.7%
Construction Wood Frame 245.99 10.6%
# Stories 1
Year Built Unknown
Area Unknown
Secondary Unknown
282.04 19.6%
Characteristic Information AAL % Change
Zip 32901 220.96 —
Street Address 621 Burr Street, Melbourne, FL 232.52 5.2%
Parcel 28.069065 -80.609726 240.83 3.6%
Occupancy Single Family 222.34 -7.7%
Construction Wood Frame 245.99 10.6%
# Stories 1 235.89 -4.1%
Year Built 1987
Area Unknown
Secondary Unknown
317.43 12.5%
Characteristic Information AAL % Change
Zip 32901 220.96 —
Street Address 621 Burr Street, Melbourne, FL 232.52 5.2%
Parcel 28.069065 -80.609726 240.83 3.6%
Occupancy Single Family 222.34 -7.7%
Construction Wood Frame 245.99 10.6%
# Stories 1 235.89 -4.1%
Year Built 1987 282.04 19.6%
Area 1440
Secondary Unknown
354.36 11.6%
Characteristic Information AAL % Change
Zip 32901 220.96 —
Street Address 621 Burr Street, Melbourne, FL 232.52 5.2%
Parcel 28.069065 -80.609726 240.83 3.6%
Occupancy Single Family 222.34 -7.7%
Construction Wood Frame 245.99 10.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
Characteristic Information AAL % Change
Zip 32901 220.96 —
Street Address 621 Burr Street, Melbourne, FL 232.52 5.2%
Parcel 28.069065 -80.609726 240.83 3.6%
Occupancy Single Family 222.34 -7.7%
Construction Wood Frame 245.99 10.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
© 2014 JLT Re. All rights reserved.
(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)
© 2014 JLT Re. All rights reserved.
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?
© 2014 JLT Re. All rights reserved.
Mobile Home
Single Family Dwelling
Which Construction is better in a windstorm?
© 2014 JLT Re. All rights reserved.
Masonry
Wood Frame
Which Construction is better in an earthquake?
© 2014 JLT Re. All rights reserved.
Masonry
Wood Frame
Which # of Stories is better in a windstorm?
© 2014 JLT Re. All rights reserved.
Low rise
High Rise
Which # of Stories is better in an earthquake?
© 2014 JLT Re. All rights reserved.
Low rise
High Rise
Which Roof Shape is better in a windstorm?
© 2014 JLT Re. All rights reserved.
Which Roof Anchors are better in a windstorm?
© 2014 JLT Re. All rights reserved.
Clips
Double Wraps
Which Roof Coverings are better in a windstorm?
© 2014 JLT Re. All rights reserved.
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
© 2014 JLT Re. All rights reserved.
Case Study Introduction – Exposure Concentrations
© 2014 JLT Re. All rights reserved.
Case Study – ResultsRMS RiskLink v11
© 2014 JLT Re. All rights reserved.
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
Good Dataset Messy Dataset
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
Good Dataset Messy Dataset