I need to finish by excel. So, please show the calculation how can i get it. 3)
ID: 3208508 • Letter: I
Question
I need to finish by excel. So, please show the calculation how can i get it.
3) You are given the following table:
Range of Loss Amount
Midpoint Dollar Amount of Loss
Number of Losses
Total $ Amt. of Losses
$1-2,000
$1,000
300
$300,000
$2,000-10,000
$6,000
20
$120,000
Greater than $12,000
NA
0
0
a. Use the information in the table to find the average severity per claim
b. Use the information in the table to estimate a probability distribution for the loss severity per claim.
c. Using your answers from question 3, part (a) and question 2, part( b), use convolution to find the average loss.
Range of Loss Amount
Midpoint Dollar Amount of Loss
Number of Losses
Total $ Amt. of Losses
$1-2,000
$1,000
300
$300,000
$2,000-10,000
$6,000
20
$120,000
Greater than $12,000
NA
0
0
Explanation / Answer
1.Average severity per claim = Total amount of loss/Number of losses
=(300,000 + 120,000)/ (300 + 20)
= 420,000/ 320
= $1312.5
2.Probability distribution
Range of Loss amount
Total amount of loss
Probability
$1-2000
$300,000
300/320 = 0.9375
$2000-10000
$120,000
20/320 = 0.0625
Greater than $12,000
0
0
3.Convolutions
probability distribution will be:
So,
Average loss = 0.9375(1000) + 0.0625(6000) + 0 = $1312.5
Range of Loss amount
Total amount of loss
Probability
$1-2000
$300,000
300/320 = 0.9375
$2000-10000
$120,000
20/320 = 0.0625
Greater than $12,000
0
0