Why Loss-Data Cleaning Matters
Internal operational loss records are usually collected for accounting, claims administration, or incident reporting—not initially designed as a modeling data set. One event may generate several accounting entries; a recovery may arrive months later; and a valid loss may be assigned the wrong risk category. If these records are used without careful preparation, the resulting amounts and classifications can misstate the experience the data are meant to represent.
Cleaning means resolving and documenting data issues while preserving the underlying loss experience. It does not mean deleting inconvenient observations or changing records until they produce a preferred result. The event unit and modeling boundary must already be defined; cleaning then determines which source records relate to each event, what each amount represents, and how it should be recorded consistently.
A useful record retains both the original source information and the cleaned values. Typical fields include the event identifier, source-system identifiers, event and accounting dates, original and corrected classifications, gross loss, recovery amount and date, currency, price-level adjustment, inclusion status, and the reason for each change. Store enough detail to reproduce the final modeling value from the original records.
Resolve Records Before Summing Losses
A duplicate is a repeated representation of the same underlying financial transaction, not merely another record associated with the same event. One event can legitimately contain multiple payments—for example, an initial repair invoice and a later legal expense. Conversely, a single payment can appear twice because of a data export, reposting, or overlapping source systems. Compare transaction identifiers, amounts, dates, counterparties, and supporting documents before deciding.
The safe order is to identify the event, reconcile its associated transactions, remove only confirmed duplicate representations, and then calculate its loss amount. Do not deduplicate solely because two records have the same amount or because they share an event identifier. Retain a cross-reference from every removed duplicate to the record that represents the transaction.
Worked Example: Separating a Duplicate Posting From a Second Cost
A hypothetical claims system contains three entries for one in-scope operational event. Entry A is a repair invoice for $8,400 with transaction ID R-17. Entry B is also $8,400 with transaction ID R-17 and is a repeated export of the same invoice. Entry C is a separate remediation invoice for $2,100 with transaction ID M-04. Determine the event's gross loss after cleaning.
Method. Reconcile records using transaction identifiers and source evidence before summing them. The shared ID and invoice evidence establish that A and B represent the same transaction; C has a different ID and is a separate cost.
Keep Entry A: $8,400. Remove Entry B as a duplicate representation, while preserving its source reference and the reason for removal. Keep Entry C: $2,100.
The cleaned gross event loss is therefore
Check. The source total before duplicate resolution is $8,400 + $8,400 + $2,100 = $18,900. Removing exactly one repeated $8,400 posting gives $18,900 - $8,400 = $10,500, consistent with the retained transactions. No probability distribution, interest rate, or mortality assumption is used: this is a record-reconciliation calculation.
Common mistake avoided. Removing every record after the first for an event would incorrectly discard the distinct $2,100 remediation cost. Deduplication applies to repeated transactions, not to all subsequent costs of an event.
Correct Classification Errors With Evidence
A coding correction changes how a valid loss is classified; it does not change the amount or turn an out-of-scope loss into an in-scope one. Use documented evidence—such as incident narratives, investigation findings, or process records—to support the corrected category. A consistent crosswalk should map source-system codes to the modeling taxonomy and explain how ambiguous or retired codes are handled. Retain both the original code and the corrected code so that the change can be reviewed.
When evidence does not support a reliable correction, flag the record for review rather than guessing. A classification can affect analysis by risk type, so arbitrary recoding can conceal concentrations or create artificial ones. The same decision rules should be applied across the full period being analyzed.
Worked Example: Correcting a Misclassified Loss
A hypothetical insurer has a verified $24,000 loss from a processing failure. The source system labels it “external fraud,” but the investigation states that a staff member entered a valid customer instruction into the wrong account and the error was not intentional. The modeling taxonomy distinguishes internal fraud from process execution errors. Determine the appropriate cleaning action.
Method. Use the documented event facts and the stated category definitions, not the initial source label alone. The investigation identifies an unintentional processing error, so the supported category is process execution error, not internal fraud or external fraud.
Keep the loss amount at $24,000 and change the cleaned classification to process execution error. Preserve “external fraud” as the original code, record the corrected code, and note the investigation as the evidence for the change. The amount remains $24,000 because recoding changes the category, not the financial transaction.
Check. The cleaned record still contributes $24,000 to the total across all categories. Its amount within the process execution error category increases by $24,000, while its amount within external fraud decreases by $24,000. The overall total is unchanged. No probability distribution, interest rate, or mortality assumption is needed.
Common mistake avoided. Treating the source-system label as conclusive would leave the event in the wrong category. Conversely, changing the category without retaining the original code and evidence would make the adjustment difficult to audit.
Keep Recoveries Distinct From Losses
A recovery is an amount received or expected from a source such as insurance, restitution, or a legal settlement in connection with a loss. Its treatment depends on the stated modeling basis. A gross-loss basis records the loss before recoveries and preserves recovery information separately. A net-loss basis subtracts eligible recoveries from the associated gross loss, using a clearly defined rule. Neither basis should be applied inconsistently from event to event.
Record the recovery amount, source, status, and relevant date, and link it to the underlying event where possible. A recovery is not a new negative loss event. Do not silently net an uncertain expected recovery against a confirmed loss, or count a recovery twice—for example, once as a reduction in the event amount and again as a separate negative record. If the recovery is not linked confidently, flag that limitation rather than assigning it arbitrarily.
Restate Amounts to a Common Price Level
Losses occurring in different years may be expressed in dollars with different purchasing power. When a consistent price-level basis is appropriate, restate amounts to a selected target year using an index suitable for the costs represented. Let \(L_t\) be a loss amount in year \(t\), \(I_t\) the chosen index for that year, and \(T\) the target year. The restated amount is
The justification is that the loss's value measured in units of the year-\(t\) price level is \(L_t/I_t\). Expressing that same purchasing-power amount at the target-year price level gives \((L_t/I_t)I_T\), which equals \(L_t(I_T/I_t)\). This adjustment assumes the chosen index is a reasonable proxy for changes in the costs relevant to the loss. It is not a forecast of future loss amounts and does not, by itself, account for changes in exposure, business mix, or operational processes.
Apply the adjustment consistently and retain the original amount, currency, date, index values, and calculation. If losses and recoveries are both restated for a net measure, adjust each using the index for the date applicable to that amount, then calculate the net. Do not automatically use the event date for a recovery paid later without first stating that convention.
Worked Example: Adjusting a Loss and a Later Recovery
A hypothetical 2021 event has a gross loss of $120,000. A recovery of $20,000 is received in 2022. For this illustration, the selected index is 250 for 2021, 260 for 2022, and 280 for target year 2024. Calculate both the 2024 gross-loss equivalent and the 2024 net-loss equivalent. Assume the recovery is eligible for netting and use its receipt year for its price-level adjustment.
Method. Restate the gross loss from 2021 to 2024 and the recovery from 2022 to 2024 separately. Subtract only after both amounts are on the same target-year price basis.
The gross loss restated to 2024 is
The recovery restated to 2024 is
Thus, the 2024 net-loss equivalent is $134,400 - $21,538.4615… = $112,861.5385…. Rounded only at the final step to the nearest cent, it is $112,861.54. On a gross-loss modeling basis, retain $134,400 as the loss amount and keep the recovery separately; on the stated net basis, the corresponding net amount is $112,861.54.
Check. The net amount is below the restated gross loss by exactly the restated recovery: $134,400 - $112,861.54 = $21,538.46 to the cent. No interest or mortality assumption is used; the only adjustment is the stated index ratio.
Common mistake avoided. Using the event year's index for the later recovery would give $20,000(280/250) = $22,400 and a net of $112,000. That calculation applies the wrong date convention: the problem specifies the recovery's 2022 receipt year.
Document Exclusions and Reconcile the Result
An exclusion is a record or amount deliberately left out of a defined data set. A defensible exclusion follows a stated scope rule, such as an event being outside the selected legal entity or outside the defined operational-risk boundary. Keep excluded records in an exclusion log with their source identifiers, amount, reason, decision date, and approval or supporting evidence. This makes it possible to distinguish a genuine scope decision from a missing record or an undocumented judgment.
Do not exclude a valid in-scope loss merely because it is unusual, inconvenient, or difficult to classify. In particular, applying an unrecorded size threshold or deleting a large event can change the experience being represented. Decisions about incomplete reporting, thresholds, or observation limits require their own explicit treatment; cleaning should not disguise them as ordinary exclusions. The modeling data set should also reconcile to source totals, with differences explained by duplicates, scope exclusions, corrections, recoveries, and other documented adjustments.
Worked Example: Applying Scope Rules and Reconciling Totals
A hypothetical review covers records from one legal entity and one defined operational-risk boundary. Its source extract has four lines: (1) a $12,000 in-scope processing loss; (2) a $7,000 in-scope loss initially coded to the wrong category but supported by investigation; (3) a $15,000 trading loss outside the defined operational-risk boundary; and (4) a second $12,000 line that is confirmed to be a duplicate of line 1. Find the cleaned in-scope gross total and identify the audit actions.
Method. Apply the scope rule, resolve confirmed duplicates, and correct the supported coding error. Do not exclude a valid loss just because its original code was wrong.
Keep the $12,000 processing loss. Keep the $7,000 loss and correct its category based on the investigation; it remains an in-scope gross loss. Exclude the $15,000 trading loss because it is outside the stated boundary, and log that scope reason. Remove the repeated $12,000 line as a duplicate, linking it to the retained transaction.
The cleaned in-scope gross total is
Check. The extract totals $12,000 + $7,000 + $15,000 + $12,000 = $46,000. Subtracting the $15,000 out-of-scope loss and the $12,000 duplicate representation gives $46,000 - $15,000 - $12,000 = $19,000. The coding correction does not change the total.
Common mistake avoided. Removing both the duplicate and the original $12,000 line would understate the cleaned total by $12,000. Likewise, removing the misclassified $7,000 record instead of correcting it would exclude a valid in-scope loss without a scope-based reason.
A Controlled Cleaning Workflow
State the event unit, operational boundary, period, currency, and whether the target measure is gross or net of recoveries.
Link entries to events and transactions; investigate suspected duplicates rather than deleting records based only on similar amounts or dates.
Apply a consistent classification crosswalk and preserve original values, corrected values, and evidence.
Retain recovery details and apply the declared gross or net convention consistently.
Use a stated index and date convention where price-level adjustment is required; log exclusions and reconcile totals.
These controls matter because a modeling result is only as interpretable as the records behind it. A transparent audit trail allows a reviewer to distinguish a true change in loss experience from an effect created by duplicate removal, recoding, recovery treatment, inflation adjustment, or scope decisions. The workflow is aligned with the CAS Exam 9 (Risk Management for Actuaries) syllabus.
Check Your Understanding
For each problem, state the cleaning decision as well as the amount where requested. Round money to the nearest cent only when needed.
- A hypothetical event has a $6,200 vendor invoice and a second record with the same transaction ID and invoice image. It also has a separate $900 assessment fee. What gross event amount should remain after cleaning? (Answer: $7,100)
- A $30,000 in-scope loss is labeled “external fraud,” but a completed investigation documents an unintentional staff posting error. What should be changed, and what should remain unchanged? (Answer: Correct the supported classification to the appropriate processing-error category; retain the $30,000 amount and preserve the original code and evidence.)
- A hypothetical gross loss of $50,000 occurred in 2020. The selected index is 200 for 2020 and 230 for target year 2023. What is the restated amount? (Answer: $57,500)
- A gross loss of $18,000 occurred in 2022; a $3,000 recovery was received in 2023. The relevant indices are 240, 250, and 270 for 2022, 2023, and 2024, respectively. Using the receipt year for the recovery, what is the 2024 net equivalent? (Answer: $20,250 - $3,240 = $17,010)
- A source extract totals $41,000: $10,000 is an eligible loss, $8,000 is another eligible loss with a supported coding error, $20,000 is outside the stated boundary, and $3,000 is a confirmed duplicate of part of the $10,000 record. What cleaned in-scope gross total remains, and what two amounts should be logged as excluded? (Answer: $18,000 remains; log the $20,000 out-of-scope amount and the $3,000 duplicate representation.)