Ravindra BagaleCourses & study guides मराठी Track your progress

27. 20 Industry Projects

27.6 Insurance: Premium, Claims and Loss Ratio

By Last updated:

Example first. Salman looks after a fictional insurance agency's policies and wants to know how much of the premium went out as claims.

Insurance project — in 30 seconds

What: A Insurance tracker: one Table tblPolicies, the calculated column LossRatio, KPI cells, a PivotTable by City, a chart and a one-page dashboard.

Why: This is the shape of a real Insurance report: Total Premium, Total Claims, Loss Ratio, Policies with a Claim are the numbers a manager reads first.

Tiny example: From 6 rows: Total Premium = ₹91,200, Total Claims = ₹39,900, Loss Ratio = 43.8%, Policies with a Claim = 3.

First look at the data (before → after):

Insurance project: before and after tables, fictional Blinkit Maharashtra data

Expected result — Calculated column

PolicyID LossRatio
POL-801 69.4%
POL-802 0.0%
POL-803 0.0%
POL-804 131.2%
POL-805 78.0%
POL-806 0.0%

Expected result — KPI cells (expected results)

KPI Formula Result
Total Premium =SUM(tblPolicies[Premium]) ₹91,200
Total Claims =SUM(tblPolicies[ClaimPaid]) ₹39,900
Loss Ratio =SUM(tblPolicies[ClaimPaid])/SUM(tblPolicies[Premium]) 43.8%
Policies with a Claim =COUNTIF(tblPolicies[ClaimPaid],">0") 3
Before table and formula as text

Before — Table tblPolicies (fictional sample rows)

PolicyID City Type Premium ClaimPaid
POL-801 Pune Health 18,000 12,500
POL-802 Nashik Motor 9,500 0
POL-803 Pune Life 24,000 0
POL-804 Nagpur Health 16,000 21,000
POL-805 Solapur Motor 8,200 6,400
POL-806 Kolhapur Health 15,500 0

Formula / click path

LossRatio: =[@ClaimPaid]/[@Premium]
Total Premium: =SUM(tblPolicies[Premium])
Total Claims: =SUM(tblPolicies[ClaimPaid])
Loss Ratio: =SUM(tblPolicies[ClaimPaid])/SUM(tblPolicies[Premium])
Policies with a Claim: =COUNTIF(tblPolicies[ClaimPaid],">0")

Do it step by step:

  1. Type the data and press Ctrl+T; name the Table tblPolicies in Table Design › Properties › Table Name.
  2. Add the column LossRatio with =[@ClaimPaid]/[@Premium]; the Table fills it down.
  3. On a KPI sheet type =SUM(tblPolicies[Premium]) and the other KPI formulas from the table below.
  4. Click Insert › PivotTable › From Table/Range, put City in Rows and Premium in Values (Sum).
  5. Click PivotTable Analyze › PivotChart › Clustered Column, then PivotTable Analyze › Insert Slicer › Type.
  6. Move the KPI cells, the chart and the slicer onto a sheet named Dashboard.

The brief. Build a claims sheet: loss ratio per policy, total premium and claims, the overall loss ratio (claims ÷ premium), the number of policies with a claim, premium by city, a chart and a Type slicer.

Calculated column formulas:

Column Formula
LossRatio =[@ClaimPaid]/[@Premium]

KPI cells (check your numbers):

KPI Formula Result
Total Premium =SUM(tblPolicies[Premium]) ₹91,200
Total Claims =SUM(tblPolicies[ClaimPaid]) ₹39,900
Loss Ratio =SUM(tblPolicies[ClaimPaid])/SUM(tblPolicies[Premium]) 43.8%
Policies with a Claim =COUNTIF(tblPolicies[ClaimPaid],">0") 3

PivotTable (expected):

City Sum of Premium
Kolhapur ₹15,500
Nagpur ₹16,000
Nashik ₹9,500
Pune ₹42,000
Solapur ₹8,200
Grand Total ₹91,200

Chart and dashboard (one under the other):

  1. Click inside the PivotTable, then PivotTable Analyze › PivotChart › Clustered Column › OK.
  2. Add a chart title and data labels with Chart Design › Add Chart Element.
  3. Click PivotTable Analyze › Insert Slicer, tick Type and click OK.
  4. Insert a new sheet named Dashboard. In it, link a card cell to each KPI (for example =KPI!B2) and format it large and bold.
  5. Cut and paste the chart and slicer onto the Dashboard sheet.
  6. On the View tab, untick Gridlines in the Show group. Select the dashboard area and click Page Layout › Print Area › Set Print Area.

Template and guide: download the practice template Insurance_Template.xlsx and read the step-by-step template guide. The template's sample rows may differ from the rows above; the method is the same.

Practice task (try it before you open the answer):

  1. Which city has the highest sum of Premium?
  2. Filter the Table on City = Pune (Data › Filter): what is Total Premium for Pune alone?
  3. Add the Clustered Column PivotChart and the Type slicer to your Dashboard sheet.
Answer

Pune (₹42,000). For Pune only (2 rows): Total Premium = ₹42,000. The chart shows 5 bars, one per city.

Ravindra Bagale's Tip

Show Total Claims next to the rupee total. A percentage or ratio without the size behind it can make a small city look like a big problem.

Loading practice tasks...

  1. Monthly rate (Easy): Yearly rate B3 divided by 12.
  2. Months (Easy): B4 times 12.
  3. Monthly EMI (Easy): EMI on amount B2 at B3 for B4 years, as a positive number, rounded to 2 decimals.
  4. Total repaid (Medium): Total paid over the loan, rounded to 0 decimals.
  5. Total interest (Medium): Total paid minus B2, rounded to 0 decimals.
  6. Processing fee (Medium): B2 times B6.
  7. Savings value (Medium): Value after B4 years of saving B5 each month at B3, positive, rounded to 0 decimals.
  8. Interest earned (Hard): Savings value minus B5 times months, rounded to 0 decimals.
  9. Months to repay at double EMI (Hard): Months to clear B2 at B3 paying twice the EMI, rounded up.
  10. Loan you can afford (Hard): Loan size for an EMI equal to B5 over B4 years at B3, positive, rounded to 0 decimals.
  11. Interest share (Hard): Total interest ÷ total paid, rounded to 3 decimals.

See all practice tasks in the Task Hub →