Shared Phone Cluster in No-Fault Medical Claims
An analyst at a no-fault auto insurer is reviewing 2,300 personal injury protection claims paid in 2023. The Excel workbook has one row per claim with CLAIM_NO, INSURED_NAME, DATE_OF_LOSS, PROVIDER_NAME, PROVIDER_ADDRESS, PROVIDER_PHONE, ATTORNEY_NAME, ATTORNEY_PHONE, PAID_AMOUNT, and ADJUSTER_NOTE. The phone field is free text, and some rows contain punctuation or a leading 1. A pivot shows 38 claims tied to phone 555-0142 and nine distinct insured names; three provider addresses appear 12, 9, and 7 times. One address is a mailbox store, one is a law office, and one is a large medical clinic with 14 providers. Six claims share the same date of loss and the same clinic, and four of those have paid amounts within $3 of each other. The special investigations manager asks whether this is a staged-accident ring.
1. List the Excel transformations and formulas you would use to test the phone-number cluster, and explain what each test rules in or out.
Read the worked answer
I would make the cleanup repeatable in Power Query and keep an audit column. Normalize phone numbers with nested SUBSTITUTE functions or a TEXT formula that removes parentheses, hyphens, and a leading 1. Then add a COUNTIF on the normalized phone to count all claims per phone, and a COUNTIFS that counts other insured names sharing the phone while excluding the current row. Pivot by phone, provider name, address, and date of loss. Use XLOOKUP to match the phone against a provider master and an attorney master; if the number belongs to a clinic or law office, the cluster is expected. Check distinct claim numbers and paid totals. Split the phone cluster by address and date. A shared phone among nine claimants is meaningful only if the phone is not a business line and the claimants have no household or referral link. I would also inspect the raw claim forms because phone fields are often defaulted.
2. The six same-date claims at one clinic: what alternative explanations must you exclude before calling it a staged event?
Read the worked answer
Same-date claims at one clinic can be a legitimate multi-vehicle crash, a clinic batch-entering dates, a default date in the claim system, or a marketing event where several people were injured. I would check police reports, vehicle VINs, occupants, tow records, injury patterns, and whether the claimants knew each other. I would compare the clinic's same-date volume with its own baseline and with peer clinics. Exact paid amounts within $3 may simply reflect a fee schedule, not staging. Before calling it a ring, I would want independent evidence of a common organizer, fabricated treatment, or unrelated claimants recruited to the same clinic. A cluster is a reason to look, not a conclusion.
3. How would you present the cluster to the SIU manager without overstating it?
Read the worked answer
Present three sections: observations, alternative explanations, and recommended next steps. Observations should be counts and rates, for example 38 claims, nine insured names, three addresses, and the clinic's share of total paid dollars. State that the phone and address links were normalized and checked against provider and attorney records. Then state what the data cannot show: intent, whether treatment was unnecessary, or who organized anything. Recommend a targeted review of the six same-date claims and a sample of the phone cluster, with requests for claim forms and police reports. Use 'consistent with' and 'requires verification' rather than 'fraud ring'. Give the manager a prioritized list, not a verdict, and remind him of need-to-know handling.