🚀🚀 Case study - How to Analyse 5 Years of Loss History in Seconds

🎯 Why this matters:

Loss runs are messy. Key patterns are buried in rows of data.
This prompt pulls out the trends, red flags, and top losses instantly and accurately.

💡 Best Practice:

Run this prompt as soon as you receive the loss history, especially for large accounts or renewals with losses.
It reveals frequency, severity, open reserves, and loss types - a quick sanity check that flags gaps before you dig through the file.

✅ What You’ll Need:

• Loss history file opened in Excel format

• Microsoft Excel (with Copilot enabled)

• Our prompt (see below)

🚀 Step-by-Step:

1. Open the Broker submission in Microsoft Excel format

2. In the toolbar, click on the Copilot icon (usually top-right in Word).

3. A sidebar will appear.

4. Paste the following prompt into Copilot

   Ensure the tab names in your Excel file match what’s in the prompt, or Copilot won’t be able to find the data.

   Then hit Enter.

Act as a Senior Underwriter analyzing 5 years of loss history from a large insurance account.
Use the following tabs only:
- "Loss History – 5 Years" (primary data)
- "Claim Details" (for descriptions and cause analysis)
- Ignore any tabs labeled "Summary", "Pivot", or "Charts"


The spreadsheet includes these columns:
- Date of Loss
- Type of Loss (e.g., fire, theft, liability, weather, etc.)
- Description
- Location
- Amount Paid
- Amount Outstanding

Please do the following:

1. Data Preparation

Ensure dates are properly formatted and consistent.

2. Annual Loss Summary

For each year: calculate total incurred (Paid + Outstanding), claim count (frequency), and average claim size (severity).

Present this in a clean table.

3. Identify High/Low Activity Years

Flag years with significantly higher or lower losses than average.

Include possible causes based on loss types, amounts, or descriptions.

4. Top 5 Losses

List the five largest total incurred losses.

For each: include date, type of loss, location, cause (from description), and total amount.

5. Losses by Type

Break down all claims by type of loss (e.g., fire, liability, etc.).

Show total incurred and % share of each type relative to the overall loss.

6. Large Open Claims

List all claims with outstanding reserves > £100,000.

Include date, location, type, and brief description.

7. Trend Assessment

Based on frequency, severity, and total losses:
→ Is the account's loss profile improving, stable, or deteriorating?
→ Use numbers to justify.

8. Underwriting Impact

In 2–3 bullet points, recommend how this loss trend might affect pricing, deductibles, limits, or coverage terms.

✅ Use tables and bullet points
✅ Flag any missing or inconsistent data
✅ Assume this will be reviewed by a CUO—clarity and accuracy are critical
✅ If the tabs are not found, return the message: tab not found. Please confirm the sheet name or provide the correct tab.”

🏁 Results

• Copilot will generate a clear, structured breakdown of loss trends, frequency, severity, top claims, and unusual patterns in the Excel sidebar or a new worksheet.

• Copy it or save it directly for your internal file.

📎 Pro Tips:

• Use this on any account with more than 3 years of loss history - it saves hours and reveals trends you might miss.

• Let Copilot flag gaps or high-risk years before you go into manual review.

• Make sure the tab name matches the prompt exactly: