Why Exposure Data Quality Comes Before Portfolio Summaries
Catastrophe model results depend on the exposures supplied to the model. A location assigned to the wrong county, an omitted coverage value, or a duplicated record can distort a portfolio summary before any event or loss calculation begins. Data-quality checks therefore have two purposes: to identify records that need attention and to make clear which records are included in each summary.
An exposure record should be interpreted at its stated level of detail. It might represent one insured location with several coverage amounts, or one location-and-coverage combination in a table with a separate row for each coverage. The record definition determines which fields should be unique and which values may be summed. In either format, a location count should count distinct location identifiers, not merely table rows.
For example, a blank insured value is missing, while a negative insured value may be invalid if the field is defined as a nonnegative amount. A reported total that does not equal its building and contents components is inconsistent. A very large value for a small structure may be implausible, but it could reflect a valid high-value risk or a unit-entry error. Treating every unusual value as invalid can discard real exposure; treating every populated field as correct can contaminate the summary.
A Practical Quality-Control Sequence
Start by specifying the record grain, the required fields, permitted values, and the inclusion rules. For example, if one row represents one insured location, the location identifier should identify that location uniquely. If one row represents one location-and-coverage combination, the combination of location identifier and coverage type should be unique. Define whether an amount is reported in dollars or thousands of dollars, whether zero is permitted, and how a reported total relates to its components.
Identify missing required identifiers, geography, coverage fields, and insured values. Distinguish a missing value from a genuine zero; zero should not be used as a substitute for unknown.
Apply defined rules such as nonnegative insured values, recognized coverage codes, and unique keys at the stated record grain. A duplicate key needs investigation before it is counted or summed.
Compare fields that should agree. If total insured value is defined as the sum of building, contents, and business interruption values, verify that identity for each record with complete components.
Use documented thresholds or reasonableness checks to select records for review. Correct, retain, hold, or exclude a record only after applying a stated rule or obtaining confirmation.
State the population included, group records by the intended characteristics, and verify that grouped counts and values reconcile to the eligible underlying records.
A useful audit trail records the original value, the flag, the resolution, and the reason. A corrected amount should not silently overwrite the source value. Likewise, held and excluded records should remain visible in the quality report so that a user can distinguish “not included because unresolved” from “confirmed to have no covered exposure.”
Worked Examples of Quality Review and Summarization
Worked Example: Resolving Flags Before Summarizing
A hypothetical insurer reviews six location-level records. All insured values are in thousands of dollars. Total insured value (TIV) is defined as building plus contents. After checking source documents, the reviewer confirms the stated resolutions below. Determine which locations enter the summary, then calculate eligible location counts and TIV by county.
| Location | County | Building | Contents | Reported TIV | Review finding |
|---|---|---|---|---|---|
| A1 | North | 300 | 60 | 360 | Consistent |
| A2 | South | 500 | 100 | 600 | Consistent |
| A3 | South | 240 | Missing | Missing | Hold pending contents value |
| A4 | West | -20 | 30 | 10 | Negative building value confirmed as an error; reject record |
| A5 | North | 450 | 50 | 520 | Source confirms components; correct reported TIV |
| A6 | West | 0 | 0 | 0 | Confirmed to have no covered exposure; exclude |
Method. Apply the confirmed quality disposition first, then sum the component values for each eligible location. A1 and A2 are internally consistent. A3 is held because its TIV cannot be completed from the available component values. A4 is rejected because its building amount is invalid. A6 is excluded from the covered portfolio after confirmation that its zero values mean no covered exposure.
For A5, calculate the component total:
The source confirms the components, so use corrected TIV of 500 rather than the inconsistent reported amount of 520. The eligible locations are A1, A2, and A5. North has two locations and TIV \(360+500=860\). South has one eligible location and TIV \(600\). West has no eligible covered locations. The overall eligible total is \(860+600=1{,}460\) thousand dollars across three locations.
Check. Directly summing the eligible location values gives \(360+600+500=1{,}460\) thousand dollars, equal to the county totals. The wrong calculation would retain A5's reported 520 and report \(360+600+520=1{,}480\). That overstates the confirmed exposure by 20 thousand dollars because it treats a field known to be inconsistent as authoritative.
Worked Example: Summarizing by Geography and Coverage Characteristics
A hypothetical portfolio has four eligible location-level records. Values are in thousands of dollars. Building and contents are separately reported, and location TIV is their sum. Calculate location count and TIV by county, the average TIV by county, occupancy totals, and the proportion of locations with positive contents coverage.
| Location | County | Occupancy | Building | Contents |
|---|---|---|---|---|
| P1 | Pine | Residential | 400 | 80 |
| P2 | Pine | Commercial | 900 | 100 |
| L1 | Lake | Residential | 300 | 60 |
| L2 | Lake | Residential | 500 | 0 |
Step 1: Calculate TIV by location. P1 has \(400+80=480\); P2 has \(900+100=1{,}000\); L1 has \(300+60=360\); and L2 has \(500+0=500\), all in thousands of dollars.
Step 2: Aggregate by county. Pine has two locations and TIV \(480+1{,}000=1{,}480\). Lake has two locations and TIV \(360+500=860\). The average TIV per location is \(1{,}480/2=740\) for Pine and \(860/2=430\) for Lake.
Step 3: Aggregate by occupancy. Residential TIV is \(480+360+500=1{,}340\) across three locations. Commercial TIV is \(1{,}000\) across one location. Total TIV is \(1{,}340+1{,}000=2{,}340\) thousand dollars.
Step 4: Summarize the coverage characteristic. Three locations have positive contents values: P1, P2, and L1. The proportion is \(3/4=0.75\), or 75%. L2's contents value of zero is a recorded zero, not a missing amount, under the stated data definition.
Check. County TIV reconciles to the occupancy totals: \(1{,}480+860=2{,}340\), and \(1{,}340+1{,}000=2{,}340\). Each location is counted once within a location-level summary, even though it contributes to both building and contents amounts.
Worked Example: Counting Locations in Coverage-Level Records
A hypothetical exposure file has one row per location-and-coverage combination, with amounts in thousands of dollars. The six rows are: Elm location E1, building 300; E1, contents 50; Elm location E2, building 200; E2, contents 0; Oak location O1, building 250; and O1, contents 40. Find the number of locations, building and contents totals, TIV by county, the average TIV per location, and the proportion of locations with positive contents coverage.
Method. Treat each row as one coverage component, not one location. The distinct location identifiers are E1, E2, and O1, so there are three locations. The building total is \(300+200+250=750\) thousand dollars. The contents total is \(50+0+40=90\) thousand dollars. Thus overall TIV is \(750+90=840\) thousand dollars.
For the county summaries, Elm contains E1 and E2, with TIV \((300+50)+(200+0)=550\) thousand dollars across two locations. Oak contains O1, with TIV \(250+40=290\) thousand dollars across one location. The average TIV per location is \(840/3=280\) thousand dollars.
E1 and O1 have positive contents amounts, while E2 has zero contents. Therefore, two of the three distinct locations have positive contents coverage, a proportion of \(2/3=0.6667\), or 66.67% when expressed as a percentage.
Check and common mistake. The county totals reconcile: \(550+290=840\), matching the building-plus-contents total. Counting the six rows as six locations is wrong because each location appears once for each of two coverage types. The correct location count is three distinct identifiers; the coverage rows are summed only for their corresponding amounts.
Why Grouped Totals Reconcile
A portfolio summary should not create or lose exposure merely because the records have been grouped. Suppose the eligible location set is divided into mutually exclusive groups \(G_1,\ldots,G_m\), such as counties, and each location \(j\) has TIV \(T_j\). Every eligible location belongs to exactly one group. Therefore, the sum of group totals is the sum of the underlying location values:
This follows by rearranging the addition: the left side adds each eligible location's TIV once in the one group to which it belongs. If a location is assigned to two counties, omitted from all counties, or duplicated within a county, this equality can fail. A reconciliation check is therefore a basic test of both the group assignment and the aggregation process.
Counts require a related but different rule. For location-level data, count eligible location identifiers. For coverage-level data, first identify distinct locations for a location count; do not substitute the number of rows. If the desired statistic is the number of locations with a particular coverage, define the condition at the location level, such as “contents amount greater than zero,” and count each qualifying location once.
Common Traps and Reporting Standards
- Replacing missing with zero. A missing amount means it is unknown. Zero means the amount is known to be zero. Conflating them can understate totals and change coverage statistics.
- Automatically deleting an unusual value. An implausible value is a review flag, not proof of error. Confirm it, document the decision, and then retain, correct, hold, or exclude it according to a stated rule.
- Summing totals and components together. If TIV already includes building and contents, adding TIV to those components counts the same exposure twice. Decide which fields define the total before aggregating.
- Counting rows as locations. This is especially damaging when a location has several coverage rows. Use distinct location identifiers for location counts.
- Reporting only a cleaned total. State the eligible population and report unresolved or excluded records separately when they matter. Otherwise, two summaries may appear different simply because they used different inclusion rules.
Check Your Understanding
For each hypothetical portfolio, apply the stated record definitions and inclusion rules. Values are in thousands of dollars unless noted.
- A location-level record reports building 180, contents 20, and TIV 210. The source confirms the component amounts. Identify the data-quality issue and state the corrected TIV. (Answer: Inconsistent total; corrected TIV is 200 thousand dollars.)
- Three eligible locations in Cedar County have TIVs of 240, 360, and 600. Two locations in Birch County have TIVs of 150 and 450. Find total TIV, the location count in each county, and average TIV per location in each county. (Answer: Total TIV is 1,800 thousand dollars; Cedar has 3 locations and an average of 400 thousand dollars; Birch has 2 locations and an average of 300 thousand dollars.)
- A coverage-level file has rows: K1 building 400, K1 contents 60, K2 building 300, K2 contents 0, and K3 building 500, K3 contents 40. Find the number of distinct locations, total TIV, and the proportion with positive contents. (Answer: 3 locations; total TIV is 1,300 thousand dollars; 2/3, or 0.6667, have positive contents.)
- A portfolio summary reports 12,000 thousand dollars of eligible TIV. County A totals 4,800, County B totals 3,700, and County C totals 3,200. Determine whether the summary reconciles and, if not, the difference. (Answer: It does not reconcile; county totals equal 11,700 thousand dollars, which is 300 thousand dollars below the portfolio total.)
- A location-level file has 80 eligible locations. Of these, 12 have a missing contents amount, 8 have a confirmed zero contents amount, and the remaining records have positive contents amounts. Find the number of locations with known contents amounts, and the proportion of known-contents locations with positive contents. (Answer: 68 have known contents amounts; 60/68 = 0.8824, or 88.24%, have positive contents.)