{"text_format":"1","workbook":"xlsx","n_sheets":21,"sheets":["Instructions","Formula Sheet","Point Grid","Navigator","1","2","3","4","5","6","7","8","9","10","11","12","13","14","15","16","17"],"not_rendered":{"charts":0,"images":2,"macros":1,"external_links":0,"defined_names":0,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Instructions","index":0,"n_rows":32,"n_cols":1,"rows_kept":32,"cut":false,"hidden":false,"merged":[]}
[]
[]
[]
[]
[]
["Instructions"]
[]
["1.) Find a quiet place and allow yourself the full four hour exam period to practice. Treat this just as you would the real exam."]
[]
["2.) Use the Navigator worksheet to see a brief description of each question, its point value and navigate between questions in your preferred order."]
[]
["3.) Answer each question either to the side or below the grey question area."]
[]
["4.) Use cell B1 on each question worksheet to mark whether you finished the question or not. You should use this feature to"]
["       reflect your confidence in your answer. This way you'll know if you're on the money or your guesses were lucky."]
[]
["5.) Grade each question harshly. It's better to put in extra reviewing time now than find the examiners aren't as generous as you hoped."]
["       Use increments of 0.25 when scoring; pay attention to the language used in the question and the point value. An \"identify\" question"]
["       is likely 0.25 per item while an \"explain\" or \"briefly justify\" gets 0.25 for the item and another 0.25 for the justification."]
[]
["6.) Enter your estimated score for each sub-part of a question in the box beneath the sub-part point value. They look like:"]
[]
["7.) Use the Point Grid to see your score and a breakdown of where you scored well and where you didn't."]
[]
["8.) Find the top five questions where you lost the most points. These are the topics you need to review the most intensely in the"]
["      next day or so."]
[]
["9.) On the BattleActs site → Custom Battles → BattleCards - Prior Exams → Select this exam and mark the cards you got right and wrong."]
["      This allows your Battle Readiness Quotient (BRQ) to track your strengths and weaknesses."]
[]
["10.) Use the Reset Exam button on the Point Grid worksheet to clear all of your work ready for a new attempt at this exam later."]
["slay the beast"]

{"sheet":"Formula Sheet","index":1,"n_rows":27,"n_cols":2,"rows_kept":27,"cut":false,"hidden":true,"merged":[]}
[]
[]
[]
[]
[]
["FORMULA SHEET"]
[]
["• For the negative binomial distribution (for use on question 3):"]
[]
[null,"○"]
[]
[]
[null,"○"]
[]
[]
["• For the shifted Pareto distribution (for use on questions 7, 13 & 15):"]
[]
[null,"○"]
[]
[]
[null,"○"]
[]
[]
[null,"○"]
[]
[]
[null,"○"]

{"sheet":"Point Grid","index":2,"n_rows":28,"n_cols":19,"rows_kept":28,"cut":false,"hidden":false,"merged":[[5,1,6,1]]}
[]
[]
[]
[]
["2018 Exam 8"]
[null,"Total Point Value","Sub-Part of Question",null,null,null,null,null,null,null,null,"Your Score","Sub-Part of Question"]
["Question",null,"(a)","(b)","(c)","(d)","(e)","(f)","(g)","Status",null,"Question","(a)","(b)","(c)","(d)","(e)","(f)","(g)"]
[1,{"$f":"SUM(C8:I8)","v":9},1.5,1.5,2.25,1.5,1,0.5,0.75,{"$f":"INDIRECT(\"'\"&A8&\"'!B1\")","v":"Finished"},null,{"$f":"A8","v":1},{"$f":"_xlfn.IFNA(IF(ISBLANK(INDEX(INDIRECT(\"'\"&$L8&\"'!$A:$A\"),MATCH(M$7,INDIRECT(\"'\"&$L8&\"'!$A:$A\"),0)+2)),\"\",INDEX(INDIRECT(\"'\"&$L8&\"'!$A:$A\"),MATCH(M$7,INDIRECT(\"'\"&$L8&\"'!$A:$A\"),0)+2)),\"\")","v":1.5},1.5,2.25,1.5,1,0.5,0.75]
[2,{"$f":"SUM(C9:I9)","v":9.5},1,1,1.75,1,1,1.25,2.5,{"$f":"INDIRECT(\"'\"&A9&\"'!B1\")","v":"Finished"},null,{"$f":"A9","v":2},1,1,1.75,1,1,1.25,2.5]
[3,2.75,1.5,0.75,0.5,null,null,null,null,"Finished",null,3,1.5,0.75,0.5,"","","",""]
[4,0.75,0.75,null,null,null,null,null,null,"Finished",null,4,0.75,"","","","","",""]
[5,1.5,1,0.5,null,null,null,null,null,"Finished",null,5,1,0.5,"","","","",""]
[6,2.5,2,0.5,null,null,null,null,null,"Finished",null,6,2,0.5,"","","","",""]
[7,2.5,0.5,0.75,0.5,0.25,0.5,null,null,"Finished",null,7,0.5,0.75,0.5,0.25,0.5,"",""]
[8,1.5,1.5,null,null,null,null,null,null,"Finished",null,8,1.5,"","","","","",""]
[9,4.25,2.75,1.5,null,null,null,null,null,"Finished",null,9,2.75,1.5,"","","","",""]
[10,2.25,1.25,1,null,null,null,null,null,"Finished",null,10,1.25,1,"","","","",""]
[11,1.25,0.5,0.75,null,null,null,null,null,"Finished",null,11,{"$f":"_xlfn.IFNA(IF(ISBLANK(INDEX(INDIRECT(\"'\"&$L18&\"'!$A:$A\"),MATCH(M$7,INDIRECT(\"'\"&$L18&\"'!$A:$A\"),0)+2)),\"\",INDEX(INDIRECT(\"'\"&$L18&\"'!$A:$A\"),MATCH(M$7,INDIRECT(\"'\"&$L18&\"'!$A:$A\"),0)+2)),\"\")","v":0.5},0.75,"","","","",""]
[12,3,1.5,0.5,1,null,null,null,null,"Finished",null,12,1.5,0.5,1,"","","",""]
[13,2.75,0.75,2,null,null,null,null,null,"Finished",null,13,0.75,2,"","","","",""]
[14,3,1.25,1.25,0.5,null,null,null,null,"Finished",null,14,1.25,1.25,0.5,"","","",""]
[15,1.75,1.75,null,null,null,null,null,null,"Finished",null,15,1.75,"","","","","",""]
[16,1.75,1,0.75,null,null,null,null,null,"Finished",null,16,1,0.75,"","","","",""]
[17,2,0.5,0.5,1,null,null,null,null,"Finished",null,17,0.5,0.5,1,"","","",""]
[]
["Total",{"$f":"SUM(B8:B24)","v":52},null,null,null,null,null,null,null,null,null,"TOTAL",{"$f":"IF(SUM(IF(M8:S24=\"\",0,1))=0,\"\",SUM(M8:S24))","v":52},null,"Percentage:",{"$f":"IF(M26=\"\",\"\",M26/B26)","v":1}]
[]
[null,null,null,null,null,null,null,null,null,null,null,"Passing Score:",33.75]

{"sheet":"Navigator","index":3,"n_rows":25,"n_cols":8,"rows_kept":25,"cut":false,"hidden":false,"merged":[[19,0,19,6],[8,0,8,6],[9,0,9,6],[10,0,10,6],[11,0,11,6],[12,0,12,6],[13,0,13,6],[14,0,14,6],[15,0,15,6],[16,0,16,6],[17,0,17,6],[18,0,18,6],[20,0,20,6],[21,0,21,6],[22,0,22,6],[23,0,23,6],[24,0,24,6]]}
[]
[]
[]
[]
[]
["Navigator – Select a question to go to it"]
[]
["Question #",null,null,null,null,null,null,"Status"]
["Q1 -- Mahler -- Chi-squared Test (IQ) -- 9.00 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J8","v":"Finished"}]
["Q2 -- Bahnemann -- Deductibles (IQ) -- 9.50 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J9","v":"Finished"}]
["Q3 -- Bailey & Simon -- Calculate Number of Claims -- 2.75 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J10","v":"Finished"}]
["Q4 -- ASOP 12 -- Risk Classification -- 0.75 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J11","v":"Finished"}]
["Q5 -- Goldburd (GLM) -- Residual Plots -- 1.50 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J12","v":"Finished"}]
["Q6 -- Goldburd (GLM) -- Nested Models --2.50 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J13","v":"Finished"}]
["Q7 -- Goldburd (GLM) -- Logistic Regression -- 2.50 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J14","v":"Finished"}]
["Q8 -- Bahnemann -- Risk Adjusted ILFs -- 1.50 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J15","v":"Finished"}]
["Q9 -- Fisher -- Efficiency Test -- 4.25 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J16","v":"Finished"}]
["Q10 -- Fisher -- Experience Modification -- 2.25 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J17","v":"Finished"}]
["Q11 -- ISO CGL -- Experience & Schedule Rating -- 1.25 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J18","v":"Finished"}]
["Q12 -- Fisher -- LDD & Retrospective Rating Plans -- 3.00 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J19","v":"Finished"}]
["Q13 -- Bahnemann -- Claim Count Inflation -- 2.75 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J20","v":"Finished"}]
["Q14 -- Fisher -- Insurance Charges -- 3.00 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J21","v":"Finished"}]
["Q15 -- Clark -- Loss Corridors -- 1.75 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J22","v":"Finished"}]
["Q16 -- Grossi -- Probable Maximum Loss -- 1.75 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J23","v":"Finished"}]
["Q17 -- Grossi -- Catastrophe Modules -- 2.00 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J24","v":"Finished"}]

{"sheet":"1","index":4,"n_rows":113,"n_cols":17,"rows_kept":113,"cut":false,"hidden":false,"merged":[[54,2,55,2],[0,7,0,8]]}
[1,"Finished",null,null,null,null,null,"Navigator",null,null,"Solution"]
[null,null,null,null,null,null,null,null,null,"(a)","Notice we're given claim counts rather than claim frequencies. Also, LOB 1"]
["Points","An insurance company is planning to expand into a new territory and has decided to review its historical",null,null,null,null,null,null,null,null,"has increasing premiums and claim counts. So we'll need to convert to claim"]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('1'!A1,'Point Grid'!A:A,0))","v":9},"loss experience in order to determine whether it will require additional capital to support the expansion.",null,null,null,null,null,null,null,null,"frequencies before we can address the question."]
[]
[null,"The insurance company has engaged an actuarial consultant to provide insights into a prospective loss ratio ",null,null,null,null,null,null,null,null,"We are given 5 periods for each line of business and need to estimate the"]
[null,"for the new territory. The following table outlines the insurance company's historical experience for two",null,null,null,null,null,null,null,null,"overall claim frequency for each. Therefore we have 5 - 1 = 4 degrees of freedom,"]
[null,"long-tailed lines of business (LOB):",null,null,null,null,null,null,null,null,"So the critical value (using the table given) is 7.779"]
[]
[null,null,"Earned Premiums",null,"Ultimate Losses",null,"Ultimate Claim Counts",null,null,null,"Average Frequencies (per $000):"]
[null,"Accident Years","LOB 1","LOB 2","LOB 1","LOB 2","LOB 1","LOB 2",null,null,"LOB 1",{"$f":"G17/(C17/1000)","v":1.4637914910342772E-2}]
[null,"1991 – 1995",12033000,1766000,2329000,1236000,170,170,null,null,"LOB 2",{"$f":"H17/(D17/1000)","v":0.10735176052299576}]
[null,"1996 – 2000",13812000,1819000,2762000,1273000,210,172]
[null,"2001 – 2005",13985000,1751000,2797000,1506000,210,201,null,null,null,"Actual","Expected","Actual","Expected"]
[null,"2006 – 2010",16444000,1710000,3288000,1471000,240,195,null,null,"AYs","LOB 1","LOB 1","LOB 2","LOB 2"]
[null,"2011 – 2015",17507000,1673000,3350000,1439000,250,198,null,null,"1991 – 1995",{"$f":"G12","v":170},{"$f":"(C12/1000)*$L$11","v":176.13803011615457},{"$f":"H12","v":170},{"$f":"(D12/1000)*$L$12","v":189.58320908361051}]
[null,"Total",{"$f":"SUM(C12:C16)","v":73781000},8719000,14526000,6925000,1080,936,null,null,"1996 – 2000",{"$f":"G13","v":210},{"$f":"(C13/1000)*$L$11","v":202.17888074165435},{"$f":"H13","v":172},{"$f":"(D13/1000)*$L$12","v":195.27285239132928}]
[null,null,null,null,null,null,null,null,null,null,"2001 – 2005",210,204.71124002114365,201,187.97293267576558]
["(a)","Conduct chi-square tests with an α value of 0.10 on actual vs. expected claim counts to confirm",null,null,null,null,null,null,null,null,"2006 – 2010",240,240.70587278567655,195,183.57151049432275]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A19,'Point Grid'!$C$7:$I$7,0))","v":1.5},"whether or not risk parameters have shifted over time. Use the following table of critical values:",null,null,null,null,null,null,null,null,"2011 – 2015",250,256.26597633537091,198,179.59949535497191]
[1.5,null,"Degrees of","Critical Value",null,null,null,null,null,null,"Chi-Squared",null,{"$f":"(L16-M16)^2/M16+(L17-M17)^2/M17+(L18-M18)^2/M18+(L19-M19)^2/M19+(L20-M20)^2/M20","v":0.80836655369678112},null,{"$f":"(N16-O16)^2/O16+(N17-O17)^2/O17+(N18-O18)^2/O18+(N19-O19)^2/O19+(N20-O20)^2/O20","v":8.296051776108138}]
[null,null,"Freedom","α = 0.10"]
[null,null,1,2.706,null,null,null,null,null,null,"Since 0.8084 < 7.779 we have no reason to reject the null hypothesis, i.e."]
[null,null,2,4.6050000000000004,null,null,null,null,null,null,"the risk parameters have not shifted over time for LOB 1."]
[null,null,3,6.2510000000000003]
[null,null,4,7.7789999999999999,null,null,null,null,null,null,"Since 8.2961 > 7.779 we reject the null hypothesis and conclude the risk parameters"]
[null,null,5,9.2360000000000007,null,null,null,null,null,null,"have shifted over time for LOB 2."]
[null,null,6,10.645]
[null,null,null,null,null,null,null,null,null,"(b)","We can calculate an expected future claim frequency for any period that has"]
["(b)","To select an expected future claim frequency for LOB 2, the actuarial consultant has decided to",null,null,null,null,null,null,null,null,"more than two periods preceding it. It's important to not use any unknown information"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A30,'Point Grid'!$C$7:$I$7,0))","v":1.5},"assign weight (Z/2) to each of the most recent two groups of accident years and the remaining",null,null,null,null,null,null,null,null,"when calculating the frequencies."]
[1.5,"weight (1 - Z) to the overall mean frequency.",null,null,null,null,null,null,null,null,"AYs","Actual Freq","1st Prior","2nd Prior","Overall","Expected Freq"]
[null,null,null,null,null,null,null,null,null,null,"2001 – 2005",{"$f":"H14/(D14/1000)","v":0.11479154768703598},{"$f":"H13/(D13/1000)","v":9.4557449147883457E-2},{"$f":"H12/(D12/1000)","v":9.6262740656851642E-2},{"$f":"SUM($H$12:H13)/(SUM($D$12:D13)/1000)","v":9.5397489539748956E-2},{"$f":"($C$37/2)*M33+($C$37/2)*N33+(1-$C$37)*O33","v":9.5398750076010813E-2}]
[null,"Calculate the expected future claim frequency for LOB 2 by first using the mean-squared-error",null,null,null,null,null,null,null,null,"2006 – 2010",{"$f":"H15/(D15/1000)","v":0.11403508771929824},{"$f":"H14/(D14/1000)","v":0.11479154768703598},{"$f":"H13/(D13/1000)","v":9.4557449147883457E-2},{"$f":"SUM($H$12:H14)/(SUM($D$12:D14)/1000)","v":0.1017616191904048},{"$f":"($C$37/2)*M34+($C$37/2)*N34+(1-$C$37)*O34","v":0.1020529071131103}]
[null,"(MSE) criterion to determine the optimal value for Z from the following three choices:",null,null,null,null,null,null,null,null,"2011 – 2015",0.11835026897788405,0.11403508771929824,0.11479154768703598,{"$f":"SUM($H$12:H15)/(SUM($D$12:D15)/1000)","v":0.10474027817201249},{"$f":"($C$37/2)*M35+($C$37/2)*N35+(1-$C$37)*O35","v":0.10570758212512796}]
[null,null,"Z value","MSE"]
[null,null,0.1,"Not provided"]
[null,null,0.5,1.9000000000000001E-4]
[null,null,0.9,1.64E-4,"Note: These figures were calculated using $000 omitted",null,null,null,null,null,"Mean Squared Error (MSE) @ Z = 0.1:",null,null,{"$f":"((L33-P33)^2+(L34-P34)^2+(L35-P35)^2)/3","v":2.2649692737278045E-4}]
[null,null,null,null,null,null,null,null,null,null,"Since Z = 0.90 has the lowest MSE, use Z = 0.90."]
[null,"(The following information relates to parts (c), (d), (e), (f), and (g) below)"]
[null,"The insurance company is planning to write $10,000,000 of new business for LOB 1 in the new territory.",null,null,null,null,null,null,null,null,"Expected frequency uses all of the information known to date:"]
[null,"The following reinsurance treaty options are available to the insurance company to support its expansion:",null,null,null,null,null,null,null,null,{"$f":"0.9/2*H16/(D16/1000)+0.9/2*H15/(D15/1000)+(1-0.9)*SUM(H12:H16)/(SUM(D12:D16)/1000)","v":0.1153085865660316},"claims per $1,000 earned premium."]
[]
[null,"Option 1: 2,000,000 xs 6,000,000 Aggregate Excess of Loss",null,null,null,null,null,null,null,"(c)","Rate on line = Ceded Premium / Covered Loss"]
[null,"   • Rate on Line = 12.5%"]
[null,null,null,null,null,null,null,null,null,null,"Option 1: Layer covers $2 million of loss. So the ceded premium is"]
[null,"Option 2: 25% Aggregate Quota Share",null,null,null,null,null,null,null,null,{"$f":"\"12.5% * $2 million = \" & TEXT(12.5%*2*10^6,\"$0,000\")","v":"12.5% * $2 million = $250,000"}]
[null,"   • Ceding Commission = 20%",null,null,null,null,null,null,null,null,null,null,"Ceded"]
[null,"   • Target ceded profit of 20%, which specifies that if ceded premiums less ceding commission and loss",null,null,null,null,null,null,null,null,"Scenario","Gross Loss","Premium","Loss","Profit"]
[null,"      exceeds 20% of ceded premiums, the excess is paid back to the cedent through a profit commission.",null,null,null,null,null,null,null,null,1,{"$f":"D57","v":5000000},{"$f":"12.5%*2*10^6","v":250000},{"$f":"MAX(0,MIN(2*10^6,L51-6*10^6))","v":0},{"$f":"M51-N51","v":250000}]
[null,"   • Aggregate Ceded Loss Ratio Cap = 220%",null,null,null,null,null,null,null,null,2,{"$f":"D58","v":10000000},{"$f":"12.5%*2*10^6","v":250000},{"$f":"MAX(0,MIN(2*10^6,L52-6*10^6))","v":2000000},{"$f":"M52-N52","v":-1750000}]
[null,null,null,null,null,null,null,null,null,null,3,20000000,250000,2000000,-1750000]
["(c)","Calculate the ceded profit for each reinsurance option for the following gross loss amount scenarios:",null,null,null,null,null,null,null,null,4,30000000,250000,2000000,-1750000]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A54,'Point Grid'!$C$7:$I$7,0))","v":2.25},null,"Scenario","LOB 1 Gross"]
[2.25,null,null,"Loss Amount",null,null,null,null,null,null,"Option 2:"]
[null,null,1,5000000,null,null,null,null,null,null,"Ceded Premium: 25% * $10 million = ",null,null,{"$f":"25%*10*10^6","v":2500000}]
[null,null,2,10000000,null,null,null,null,null,null,"Ceding Commission: 20% * ceded premium =",null,null,null,{"$f":"20%*N57","v":500000}]
[null,null,3,20000000,null,null,null,null,null,null,null,null,"Ceded",null,"Ceding","Profit","Ceded "]
[null,null,4,30000000,null,null,null,null,null,null,"Scenario","Gross Loss","Premium","Loss","Commission","Commission","Profit"]
[null,null,null,null,null,null,null,null,null,null,1,{"$f":"D57","v":5000000},{"$f":"$N$57","v":2500000},{"$f":"MIN(25%*L61,220%*M61)","v":1250000},{"$f":"20%*M61","v":500000},{"$f":"MAX(0,M61-O61-N61 - 20%*M61)","v":250000},{"$f":"M61-N61-O61-P61","v":500000}]
["(d)","The actuarial consultant has decided that a prospective loss ratio of 20% for LOB 1 is appropriate, but",null,null,null,null,null,null,null,null,2,{"$f":"D58","v":10000000},{"$f":"$N$57","v":2500000},{"$f":"MIN(25%*L62,220%*M62)","v":2500000},{"$f":"20%*M62","v":500000},{"$f":"MAX(0,M62-O62-N62 - 20%*M62)","v":0},{"$f":"M62-N62-O62-P62","v":-500000}]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A62,'Point Grid'!$C$7:$I$7,0))","v":1.5},"that the gross capital required to support the expansion is $8,000,000 based on the insurance ",null,null,null,null,null,null,null,null,3,20000000,2500000,5000000,500000,0,-3000000]
[1.5,"company's requirement to hold capital at a 1-in-100 year return period. Assume that aggregate LOB 1",null,null,null,null,null,null,null,null,4,30000000,2500000,5500000,500000,0,-3500000]
[null,"ground-up losses are lognormally distributed."]
[null,null,null,null,null,null,null,null,null,"(d)","$10 million of new business for LOB 1 and a 20% prospective loss ratio corresponds to a"]
[null,"The cumulative distribution function of a lognormal distribution is ",null,null,null,null,null,null,null,null,"$2 million expected loss."]
[null,"where φ is the standard normal CDF, and μ and σ are the mean and ",null,null,null,null,null,null,null,null,"This means "]
[null,"standard deviation of the associated normal distribution."]
[null,null,null,null,null,null,null,null,null,null,"A 1-in-100 year capital requirement means P(X > 8million) = 0.01"]
[null,"The mean and variance of a lognormal distribution are given below, as well as a table of values from ",null,null,null,null,null,null,null,null,"So 1 - F(8 million) = 0.01, i.e. F(8 million) = 0.99"]
[null,"the standard normal distribution function:",null,null,null,null,null,null,null,null,"Thus "]
[null,null,null,null,null,"x","φ(x)"]
[null,null,null,null,null,1.7509999999999999,0.96,null,null,null,"Rearranging the second equation to get an expression for μ and substituting"]
[null,null,null,null,null,1.8140000000000001,0.96519999999999995,null,null,null,"into the first gives"]
[null,null,null,null,null,1.881,0.97]
[null,null,null,null,null,1.9159999999999999,0.97230000000000005,null,null,null,"This yields σ = 0.7019 or 3.9501"]
[null,null,null,null,null,2.0539999999999998,0.98,null,null,null,"Hence μ = 14.2624 or 6.7071."]
[null,null,null,null,null,2.121,0.98309999999999997]
[null,null,null,null,null,2.3260000000000001,0.99,null,null,null,"Coefficient of Variation = Standard Deviation (X) / E[X]"]
[null,null,null,null,null,2.5310000000000001,0.99429999999999996,null,null,null,"Case 1:",null,"Case 2:"]
[null,"Determine the coefficient of variation for LOB 1, assuming it is less than 100%.",null,null,null,null,null,null,null,null,{"$f":"SQRT((EXP(0.7019^2)-1)*EXP(2*14.2624+0.7019^2))/EXP(14.2624+0.7019^2/2)","v":0.79791595384481695},null,{"$f":"SQRT(EXP(3.9501^2-1)*EXP(2*6.7071+3.9501^2))/EXP(6.7071+3.9501^2/2)","v":1482.7370323421019}]
[]
["(e)","Calculate the probability of each of the following for LOB 1 assuming aggregate losses are",null,null,null,null,null,null,null,null,"We're told the Coefficient of Variation is less than 100% so it must be Case 1."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A84,'Point Grid'!$C$7:$I$7,0))","v":1},"lognormally distributed:"]
[1,"   i. Attaching the 2,000,000 xs 6,000,000 Aggregate Excess of Loss treaty",null,null,null,null,null,null,null,"(e)","The treaty will attach if the loss exceeds $6 million"]
[null,"  ii. A negative ceded profit on the Aggregate Quota Share",null,null,null,null,null,null,null,"(i)","P(X > 6 million) = 1 - F(6 million)"]
[null,null,null,null,null,null,null,null,null,null,"F(6 million) = φ([ln(6,000,000) - 14.2624] / 0.7019 )",null,null,null,null,{"$f":"(LN(6*10^6)-14.2624)/0.7019","v":1.9160422099904979}]
["(f)","State one advantage and one disadvantage of the Aggregate Quota Share from the perspective of the ",null,null,null,null,null,null,null,null,"'= φ(1.916) = 97.23%"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A89,'Point Grid'!$C$7:$I$7,0))","v":0.5},"reinsurer.",null,null,null,null,null,null,null,null,"So P(X > 6 million) = 1 - 97.23% "]
[0.5,null,null,null,null,null,null,null,null,null,{"$f":"1-97.23%","v":2.7699999999999947E-2}]
[]
["(g)","Recommend and justify which reinsurance treaty option the insurance company should select from LOB 1.",null,null,null,null,null,null,null,"(ii)","A negative ceded profit occurs when the ceded premium less ceding commission equals the ceded loss. "]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A93,'Point Grid'!$C$7:$I$7,0))","v":0.75},"Assume both reinsurance treaty options provide the same amount of capital relief.",null,null,null,null,null,null,null,null,"There is no profit commission when you break even."]
[0.75,null,null,null,null,null,null,null,null,null,"Thus, 25% * $10 million - 20% * 25% * $10 million - 25% * Loss < 0."]
[null,null,null,null,null,null,null,null,null,null,"That is, Loss > $ 8 million"]
[null,null,null,null,null,null,null,null,null,null,"We're told $8 million is a 1-in-100 year loss so the probability of a negative ceded "]
[null,null,null,null,null,null,null,null,null,null,"profit is 0.01"]
[]
[null,null,null,null,null,null,null,null,null,"(f)","Advantage"]
[null,null,null,null,null,null,null,null,null,null,"Easy to administer because it is a fixed percentage of each loss."]
[]
[null,null,null,null,null,null,null,null,null,null,"Disadvantage"]
[null,null,null,null,null,null,null,null,null,null,"The profit commission limits the upside for the reinsurer."]
[]
[null,null,null,null,null,null,null,null,null,null,"(Alice: \"Make sure you read the examiners report for more options\")"]
[]
[null,null,null,null,null,null,null,null,null,"(g)","Recommend the Aggregate Quota Share treaty because"]
[null,null,null,null,null,null,null,null,null,null,"It protects the insurer from adverse loss scenarios up to a 220% loss ratio"]
[null,null,null,null,null,null,null,null,null,null,"whereas the aggregate excess of loss treaty only protects a small layer ($2million)"]
[null,null,null,null,null,null,null,null,null,null,"of adverse loss scenarios."]
[null,null,null,null,null,null,null,null,null,null,"Further, the profit commission will help the insurer benefit more from"]
[null,null,null,null,null,null,null,null,null,null,"favorable loss experience."]

{"sheet":"2","index":5,"n_rows":90,"n_cols":18,"rows_kept":90,"cut":false,"hidden":false,"merged":[[0,10,0,11]]}
[2,"Finished",null,null,null,null,null,null,null,null,"Navigator",null,null,"Solution"]
[null,null,null,null,null,null,null,null,null,null,null,null,"(a)","E[N] =",{"$f":"SUMPRODUCT(C13:C15,D13:D15)","v":0.25}]
["Points","A certain golf course holds a series of tournaments throughout the year, during which prizes are awarded if a player hits a ",null,null,null,null,null,null,null,null,null,null,null,"E[X] =",{"$f":"SUMPRODUCT(C20:C23,D20:D23)","v":257500}]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('2'!A1,'Point Grid'!A:A,0))","v":9.5},"hole-in-one. The prize amounts vary based on the difficulty of the hole, but are consistent across tournaments.",null,null,null,null,null,null,null,null,null,null,null,"E[S] = E[N] * E[X] =",null,{"$f":"O3*O2","v":64375}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"This is the loss cost. Now load for expenses."]
[null,"The course has approached an insurer to design a policy which would protect the course from large prize payouts.",null,null,null,null,null,null,null,null,null,null,null,"Expense multiplier = 1/(1 - general expense - commission - taxes - underwriting)"]
[null,"The insurer would pay the full prize amount directly to the winning player, and seek recoveries of any applicable ",null,null,null,null,null,null,null,null,null,null,null,{"$f":"1/(1-D27-D28-D29-D30)","v":1.3888888888888888}]
[null,"deductible from the golf course."]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"Note there are no fixed expenses."]
[null,"A single annual policy will cover all of the tournaments throughout a given year."]
[null,"The total number of holes-in-one in a given year (n), across all tournaments, has the following probability distribution:",null,null,null,null,null,null,null,null,null,null,null,"Guaranteed Cost Premium =",null,null,{"$f":"P4*N7","v":89409.722222222219}]
[null,null,"n","P(n)"]
[null,null,0,0.8,null,null,null,null,null,null,null,null,"(b)","$100,000 per-occurrence deductible gives X"]
[null,null,1,0.15,null,null,null,null,null,null,null,null,null,"x","P(x)"]
[null,null,2,0.05,null,null,null,null,null,null,null,null,null,{"$f":"C20-100000","v":0},{"$f":"D20","v":0.4}]
[null,null,"3 or more",0,null,null,null,null,null,null,null,null,null,{"$f":"C21-100000","v":150000},{"$f":"D21","v":0.35}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,400000,0.24]
[null,"Given that a hole-in-one has occurred, the prize amount (x) has the following probability distribution:",null,null,null,null,null,null,null,null,null,null,null,900000,0.01]
[null,null,"x","P(x)"]
[null,null,100000,0.4,null,null,null,null,null,null,null,null,null,"E[X] =",{"$f":"SUMPRODUCT(N15:N18,O15:O18)","v":157500}]
[null,null,250000,0.35,null,null,null,null,null,null,null,null,null,"E[N] =",{"$f":"SUMPRODUCT(C13:C15,D13:D15)","v":0.25}]
[null,null,500000,0.24,null,null,null,null,null,null,null,null,null,"E[S] = ",{"$f":"O21*O20","v":39375}]
[null,null,1000000,0.01,null,null,null,null,null,null,null,null,null,"Then load for expenses"]
[]
[null,"The expenses and profit for this policy are:",null,null,null,null,null,null,null,null,null,null,null,"Deductible Premium = ",null,{"$f":"O22*N7","v":54687.5}]
[null,null,"Fixed Expenses",0,"None"]
[null,null,"General Expenses",0.08,"of premium",null,null,null,null,null,null,null,"(c)","$1,000,000 aggregate deductible, no per-occurrence deductible."]
[null,null,"Commission",0.1,"of premium",null,null,null,null,null,null,null,null,"To exceed the aggregate deductible we need at least one claim of "]
[null,null,"Taxes",0.05,"of premium",null,null,null,null,null,null,null,null,"$1,000,000 and one other claim."]
[null,null,"Underwriting Profit",0.05,"of premium",null,null,null,null,null,null,null,null,"1st Claim","2nd Claim","P(X)","# Ways","Payout"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,1000000,100000,{"$f":"D23*D20","v":4.0000000000000001E-3},2,{"$f":"O31","v":100000}]
[null,"The above assumptions apply to all program structures.",null,null,null,null,null,null,null,null,null,null,null,1000000,250000,{"$f":"D23*D21","v":3.4999999999999996E-3},2,{"$f":"O32","v":250000}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,1000000,500000,{"$f":"D23*D22","v":2.3999999999999998E-3},2,500000]
["(a)","Calculate the annual guaranteed cost premium for this policy.",null,null,null,null,null,null,null,null,null,null,null,1000000,1000000,{"$f":"D23*D23","v":1E-4},1,1000000]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A34,'Point Grid'!$C$7:$I$7,0))","v":1}]
[1,null,null,null,null,null,null,null,null,null,null,null,null,"E[X] =",{"$f":"SUMPRODUCT(P31:P34,Q31:Q34,R31:R34)","v":5050}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"P(2+ claims) =",null,{"$f":"D15","v":0.05}]
["(b)","Calculate the annual premium for this policy under a $100,000 per-occurrence deductible.",null,null,null,null,null,null,null,null,null,null,null,"E[S] =",{"$f":"O36*P37","v":252.5}]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A38,'Point Grid'!$C$7:$I$7,0))","v":1},null,null,null,null,null,null,null,null,null,null,null,null,"Load for expenses"]
[1]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"Deductible Premium =",null,{"$f":"O38*N7","v":350.69444444444446}]
["(c)","Calculate the annual premium for this policy under a $1,000,000 annual aggregate deductible and no per-occurrence "]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A42,'Point Grid'!$C$7:$I$7,0))","v":1.75},"deductible.",null,null,null,null,null,null,null,null,null,null,"(d)","$250,000 Franchise Deductible"]
[1.75,null,null,null,null,null,null,null,null,null,null,null,null,"Eliminates claims below $250,000 but pays out in full on those above."]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"Gives distribution"]
["(d)","Calculate the annual premium for this policy under a $250,000 franchise deductible.",null,null,null,null,null,null,null,null,null,null,null,"X","P(X)"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A46,'Point Grid'!$C$7:$I$7,0))","v":1},null,null,null,null,null,null,null,null,null,null,null,null,0,{"$f":"D20","v":0.4}]
[1,null,null,null,null,null,null,null,null,null,null,null,null,0,{"$f":"D21","v":0.35}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"C22","v":500000},{"$f":"D22","v":0.24}]
["(e)","Explain which of the deductible structures above would generate the greatest credit risk to the insurer and estimate",null,null,null,null,null,null,null,null,null,null,null,{"$f":"C23","v":1000000},{"$f":"D23","v":0.01}]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A50,'Point Grid'!$C$7:$I$7,0))","v":1},"the magnitude of that risk."]
[1,null,null,null,null,null,null,null,null,null,null,null,null,"E[X] =",{"$f":"SUMPRODUCT(N47:N50,O47:O50)","v":130000}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"E[N] =",{"$f":"SUMPRODUCT(C13:C15,D13:D15)","v":0.25}]
["(f)","The insurer has allocated $45,000 of capital to support the guaranteed cost policy in part (a) above.",null,null,null,null,null,null,null,null,null,null,null,"E[S] =",{"$f":"O52*O53","v":32500}]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A54,'Point Grid'!$C$7:$I$7,0))","v":1.25},"Recommend and justify which of the three options below would be the most appropriate capital requirement to ",null,null,null,null,null,null,null,null,null,null,null,"Then load for expenses"]
[1.25,"support the per-occurrence deductible policy in part (b) above:",null,null,null,null,null,null,null,null,null,null,null,"Deductible Premium =",null,{"$f":"O54*N7","v":45138.888888888891}]
[null,"     i. $45,000"]
[null,"    ii. $32,000",null,null,null,null,null,null,null,null,null,null,"(e)","The $1 million aggregate deductible generates the most credit risk"]
[null,"   iii. $19,000",null,null,null,null,null,null,null,null,null,null,null,"because the insurer payouts all claims and then seeks to recover the deductible from the insured."]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"The magnitude of the credit risk is the expected loss below the deductible,"]
["(g)","Based on the success of this program, the insurer wishes to expand this coverage to other golf courses.",null,null,null,null,null,null,null,null,null,null,null,"which is the difference between the guaranteed cost policy expected loss"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A61,'Point Grid'!$C$7:$I$7,0))","v":2.5},"The insurer will offer varying per-occurrence limits with no aggregate limits, the prize amounts would remain the same,",null,null,null,null,null,null,null,null,null,null,null,"and the aggregate deductible expected loss, i.e."]
[2.5,"and the same distribution assumptions would apply.",null,null,null,null,null,null,null,null,null,null,null,{"$f":"P4-O38","v":64122.5}]
[]
[null,"Using $100,000 as a basic limit, use the variance method to calculate the risk-loaded ILF for a $500,000 policy limit.",null,null,null,null,null,null,null,null,null,null,"(f)","The capital required under the policy with a per-occurrence deductible "]
[null,"Assume ρ(100,000) = 5,750 and use k = 0.0000005.",null,null,null,null,null,null,null,null,null,null,null,"is less than needed for the guaranteed cost policy because the insured covers part of the risk."]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"The ratio of expected loss for the per-occurrence deductible to  the guaranteed cost policy is"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"O22/P4","v":0.61165048543689315}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"Multiplying this by the $45,000 required for the guaranteed cost policy gives"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"N68*45000","v":27524.27184466019}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"So at least this amount but less than $45,000 is required, recommend option (ii) $32,000."]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,"(g)"]
[null,null,null,null,null,null,null,"X; 100,000","X2; 100,000",null,null,null,null,"Solving for δ yields δ =",null,{"$f":"(5750/0.0000005-I81)/H81^2","v":0.15}]
[null,null,null,null,null,null,null,{"$f":"MIN(C20,100000)","v":100000},{"$f":"MIN(C20,100000)^2","v":10000000000},null,null,null,null,"Now we calculate E[X2; 500,000] and E[X; 500,000] and apply the formula."]
[null,null,null,null,null,null,null,{"$f":"MIN(C21,100000)","v":100000},{"$f":"MIN(C21,100000)^2","v":10000000000}]
[null,null,null,null,null,null,null,{"$f":"MIN(C22,100000)","v":100000},10000000000,null,null,null,null,"ρ(500,000) = ",{"$f":"0.0000005*(I90+P74*H90^2)","v":48969.21875}]
[null,null,null,null,null,null,null,{"$f":"MIN(C23,100000)","v":100000},10000000000]
[]
[null,null,null,null,null,null,null,"E[X; 100,000]","E[X2; 100,000]"]
[null,null,null,null,null,null,null,{"$f":"SUMPRODUCT(H75:H78,D20:D23)","v":100000},{"$f":"SUMPRODUCT(I75:I78,D20:D23)","v":10000000000},null,null,null,null,"Here, l = 500,000 and b = 100,000"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"Risk Loaded ILF = ",null,{"$f":"(H90+O77)/(H81+5750)","v":2.8507727541371159}]
[null,null,null,null,null,null,null,"X; 500,000","X2; 500,000"]
[null,null,null,null,null,null,null,{"$f":"MIN(C20,500000)","v":100000},{"$f":"MIN(C20,500000)^2","v":10000000000}]
[null,null,null,null,null,null,null,{"$f":"MIN(C21,500000)","v":250000},{"$f":"MIN(C21,500000)^2","v":62500000000}]
[null,null,null,null,null,null,null,500000,250000000000]
[null,null,null,null,null,null,null,500000,250000000000]
[]
[null,null,null,null,null,null,null,"E[X; 500,000]","E[X2; 500,000]"]
[null,null,null,null,null,null,null,{"$f":"SUMPRODUCT(H84:H87,D20:D23)","v":252500},{"$f":"SUMPRODUCT(I84:I87,D20:D23)","v":88375000000}]

{"sheet":"3","index":6,"n_rows":38,"n_cols":13,"rows_kept":38,"cut":false,"hidden":false,"merged":[[4,2,6,2],[4,1,6,1],[4,3,6,3],[4,4,6,4],[4,5,6,5]]}
[3,"Finished",null,null,null,null,null,"Navigator",null,"Solution"]
[null,null,null,null,null,null,null,null,"(a)","Mod = Z*R + (1 - Z)*1"]
["Points","An insurance company has a private passenger auto book of business with the following",null,null,null,null,null,null,null,"Here, Z = 0.038 and we know insureds in Group B have at least 1 claim."]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('3'!A1,'Point Grid'!A:A,0))","v":2.75},"claims experience:",null,null,null,null,null,null,null,"P(at least 1 claim) = 1 - e-λ"]
[null,"Group","Number of Accident-Free Years","Earned Premiums","Current Merit Rating Factor","Number of Claims Incurred",null,null,null,"R = 1/P(at least 1 claim) = ",null,null,{"$f":"1/(1-EXP(-1*0.05))","v":20.504166493065892}]
[]
[null,null,null,null,null,null,null,null,null,"Hence Mod =",null,{"$f":"0.038*M5+(1-0.038)*1","v":1.7411583267365038}]
[null,"A","3 or more",216000000,0.6,25000,null,null,null,"Since the current merit rating factor is given and we know we need the earned premiums to be"]
[null,"X",2,135000000,0.75,18000,null,null,null,"comparable, i.e. not distorted by the merit rating factor, back out the merit rating factors."]
[null,"Y",1,63750000,0.85,20000,null,null,null,"Group","Earned Premiums at Group B present rates (Adjusted earned premiums)"]
[null,"B",0,200000000,1,"C",null,null,null,"A",{"$f":"D8/E8","v":360000000}]
[null,"Total",null,{"$f":"SUM(D8:D11)","v":614750000},null,"63,000 + C",null,null,null,"X",{"$f":"D9/E9","v":180000000}]
[null,null,null,null,null,null,null,null,null,"Y",75000000]
[null,"• Claim counts follow a Poisson distribution with parameter λ = 0.05.",null,null,null,null,null,null,null,"B",200000000]
[null,"• The credibility for the new policy period for an insured that has had no claim-free years",null,null,null,null,null,null,null,"Total",{"$f":"SUM(K11:K14)","v":815000000}]
[null,"   is equal to 0.038."]
[null,null,null,null,null,null,null,null,null,"We set the experience mod equal to the incurred claims relativity (using adjusted EP as the exposure base)"]
["(a)","Calculate C, the number of claims incurred for Group B.",null,null,null,null,null,null,null,"Group B Relativity"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A18,'Point Grid'!$C$7:$I$7,0))","v":1.5}]
[1.5]
[]
["(b)","Calculate the merit rating factor for an exposure that is accident-free for two or more years"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A22,'Point Grid'!$C$7:$I$7,0))","v":0.75},"for the new policy period.",null,null,null,null,null,null,null,"Solving for C yields C =",null,{"$f":"L7*200000000*63000/(815000000-L7*200000000)","v":47001.035177768528}]
[0.75,null,null,null,null,null,null,null,null,"Which rounds to C = 47,000"]
[]
["(c)","Briefly explain two circumstances under which using earned premium as the exposure",null,null,null,null,null,null,"(b)","Accident Free for 2 or more years corresponds to the group A + X"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A26,'Point Grid'!$C$7:$I$7,0))","v":0.5},"base would not correct for maldistribution.",null,null,null,null,null,null,null,"The experience mod is the relative claim frequency for A + X relative to the entire population."]
[0.5,null,null,null,null,null,null,null,null,"ModA+X =",{"$f":"(SUM(F8:F9)/SUM(K11:K12))/(SUM(F8:F10,47000)/SUM(K11:K14))","v":0.58998316498316505}]
[]
[null,null,null,null,null,null,null,null,null,"Since the merit rating factors listed are relative to Group B, we need the mod for Group B which is"]
[null,null,null,null,null,null,null,null,null,"ModB = ",{"$f":"L7","v":1.7411583267365038}]
[]
[null,null,null,null,null,null,null,null,null,"So the merit rating factor for Group A + X is the ratio of the mod for A+X to the mod for B."]
[null,null,null,null,null,null,null,null,null,{"$f":"K28/K31","v":0.33884521351311292}]
[]
[null,null,null,null,null,null,null,null,"(c)","1.) When territories which have high frequencies are not high premium territories as well."]
[null,null,null,null,null,null,null,null,null,"2.) When the territory differentials are not proper, i.e. the territory loss ratios are not approximately"]
[null,null,null,null,null,null,null,null,null,"       equal after applying the rate differentials."]

{"sheet":"4","index":7,"n_rows":15,"n_cols":9,"rows_kept":15,"cut":false,"hidden":false,"merged":[]}
[4,"Finished",null,null,null,null,"Navigator",null,"Solution"]
[null,null,null,null,null,null,null,"(a)","Risk of adverse selection"]
["Points","An insurance company is launching a new telematics program for their private passenger",null,null,null,null,null,null,"There is a low risk of adverse selection as drivers who know they are bad drivers are"]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('4'!A1,'Point Grid'!A:A,0))","v":0.75},"automobile book of business. Telematics devices record various attributes such as miles",null,null,null,null,null,null,"unlikely to join the program because they would receive higher rates. This supports offering a discount."]
[null,"driven and braking practices. Management decided to give a 5% discount to all customers"]
[null,"that participate in the program. The Department of Insurance questions the filing and ",null,null,null,null,null,null,"Relationship between risk and expected outcomes"]
[null,"wants the company to address the following potential concerns:",null,null,null,null,null,null,"Since drivers are likely to engage in less risky behavior if they know they are being monitored,"]
[null,null,null,null,null,null,null,null,"the loss experience should be lower for the telematics program than a traditional program. "]
[null,"   • Risk of adverse selection",null,null,null,null,null,null,"This cost savings should be passed along in the form of a discount for participating in the program."]
[null,"   • Relationship between risk and expected outcomes"]
[null,"   • Practicality of monitoring the discount's effectiveness",null,null,null,null,null,null,"Practicality of monitoring the discount's effectiveness"]
[null,null,null,null,null,null,null,null,"It is easy for the insurer to know who is participating in the telematics program and who is not."]
["(a)","Defend the use of the discount by briefly addressing each of the concerns in light of",null,null,null,null,null,null,"The loss experience can be monitored for those in the telematics program and those not and the"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A13,'Point Grid'!$C$7:$I$7,0))","v":0.75},"Actuarial Standard of Practice No. 12, Risk Classification.",null,null,null,null,null,null,"effectiveness of the discount assessed."]
[0.75]

{"sheet":"5","index":8,"n_rows":28,"n_cols":9,"rows_kept":28,"cut":false,"hidden":false,"merged":[]}
[5,"Finished",null,null,null,null,"Navigator",null,"Solution"]
[null,null,null,null,null,null,null,"(a)","Bin the Variable: We could create categorical variables by grouping together ages that have"]
["Points","An actuary is analyzing a partial residual plot of the driver age variable, which is shown below.",null,null,null,null,null,null,"similar residuals. For instance creating groups for \"15-25\", \"26-80\" and \"81+\" captures the broad"]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('5'!A1,'Point Grid'!A:A,0))","v":1.5},null,null,null,null,null,null,null,"pattern of the residuals without the individual variability."]
[]
[null,null,null,null,null,null,null,null,"Piecewise Linear Functions: We could use hinge functions to fit piecewise linear functions to the residuals."]
[null,null,null,null,null,null,null,null,"This captures the different slopes of the residuals and allows for interpolation to give greater"]
[null,null,null,null,null,null,null,null,"rating refinement. In this case we can use hinge points at 25 and 75 years old."]
[]
[null,null,null,null,null,null,null,"(b)","Not all bins may contain sufficient data to produce credible estimates of the coefficients or produce stable predictions."]
[]
[null,null,null,null,null,null,null,null,"The hinge points must be chosen by manual inspection. This is time consuming and subject to"]
[null,null,null,null,null,null,null,null,"judgment so may not be optimal."]
[]
[]
[]
[]
[]
[]
["(a)","Adding polynomial terms is one approach to address the non-linearity in the driver age"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A20,'Point Grid'!$C$7:$I$7,0))","v":1},"variable."]
[1]
[null,"Briefly describe two other alternative approaches and how they can be used to improve the"]
[null,"fit of the driver age variable shown above."]
[]
["(b)","Briefly describe a downside to each of the two alternative approachs recommended in "]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A26,'Point Grid'!$C$7:$I$7,0))","v":0.5},"part (a) above."]
[0.5]

{"sheet":"6","index":9,"n_rows":26,"n_cols":11,"rows_kept":26,"cut":false,"hidden":false,"merged":[[6,1,6,2],[7,1,7,2]]}
[6,"Finished",null,null,null,null,"Navigator",null,"Solution"]
[null,null,null,null,null,null,null,"(a)","F-Test","The F-statistic is"]
["Points","An actuary is comparing the output of two generalized linear models to develop a new "]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('6'!A1,'Point Grid'!A:A,0))","v":2.5},"rating plan for personal auto. Model statistics are shown below:",null,null,null,null,null,null,null,"is the estimated dispersion parameter for the larger model (model B)"]
[null,null,null,"Model",null,null,null,null,"D denotes the deviance of the model and is given by D = 2*(llsat - llmodel)"]
[null,null,null,"Saturated","A","B",null,null,"where ll stands for log-likelihood."]
[null,"Log-Likelihood",null,-1000,-1500,-1465]
[null,"Estimated Dispersion Parameter",null,1.75,1.75,1.75,null,null,"Model B adds the Driver Age variable which is a 2nd order polynomial. This adds 2 parameters"]
[null,null,null,null,null,null,null,null,"(the coefficient for x and the coefficient for x2)"]
[null,"• Model A is a nested model of Model B, where Model B has an additional variable for",null,null,null,null,null,null,null,{"$f":"(2*(D7-E7)-2*(D7-F7))/(2*F8)","v":20},"> 19.5 which is the critical value."]
[null,"   driver age.",null,null,null,null,null,null,"Here, F = "]
[null,"• Driver age is fit using a second-order polynomial.",null,null,null,null,null,null,"So we reject the null hypothesis and accept model B."]
[null,"• The critical value to be used from the F-distribution is 19.5."]
[null,null,null,null,null,null,null,null,"AIC","AIC = -2llmodel + 2p"]
["(a)","Using two statistical tests, recommend whether or not driver age should be included in the",null,null,null,null,null,null,null,"Here p is the number of parameters in the model, which is unknown."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A15,'Point Grid'!$C$7:$I$7,0))","v":2},"rating plan.",null,null,null,null,null,null,"We have AICA = -2(-1500) +2pS"]
[2,null,null,null,null,null,null,null,"and AICB = -2(-1465) +2(pS + 2)"]
[null,null,null,null,null,null,null,null,"because pB = pS + 2 for the 2nd degree polynomial added for Driver Age."]
["(b)","Describe why the deviance statistic alone should not be used to assess model fit."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A19,'Point Grid'!$C$7:$I$7,0))","v":0.5},null,null,null,null,null,null,null,"Thus, AICA - AICB = 66 > 0 which implies model B is better because it has the smaller AIC value."]
[0.5]
[null,null,null,null,null,null,null,null,"Both the F-Test and AIC test imply model B is the better model. Therefore, include driver age"]
[null,null,null,null,null,null,null,null,"in the rating plan."]
[]
[null,null,null,null,null,null,null,"(b)","The deviance statistic shouldn't be used by itself because adding more variables to the model"]
[null,null,null,null,null,null,null,null,"will always decrease the deviance even though the model may become overfit to the training data (lose predictiveness)."]

{"sheet":"7","index":10,"n_rows":43,"n_cols":15,"rows_kept":43,"cut":false,"hidden":false,"merged":[[0,6,0,7]]}
[7,"Finished",null,null,null,null,"Navigator",null,null,"Solution"]
[null,null,null,null,null,null,null,null,"(a)","Policy 1","ln( 1.3% / [1 - 1.3%]) =",null,{"$f":"LN(C17/(1-C17))","v":-4.3297206819719447}]
["Points","An actuary is planning to add a credit-based insurance score to a model that estimates the ",null,null,null,null,null,null,null,"Policy 2","ln( 20.3% / [1 - 20.3%]) =",null,{"$f":"LN(C18/(1-C18))","v":-1.3676486997484274}]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('7'!A1,'Point Grid'!A:A,0))","v":2.5},"probability of a policy having a claim. The actuary has decided to offset all of the current model ",null,null,null,null,null,null,null,"Policy 3","ln( 2.5% / [1 - 2.5%]) =",null,-3.6635616461296463]
[null,"variables before fitting the new variable."]
[null,null,null,null,null,null,null,null,"(b)"]
[null,"Given the following:"]
[null,"   • The current model (without the insurance score variable) is a logit-link binomial GLM",null,null,null,null,null,null,null,"Policy 1","ln( u / [1 -  u]) = -0.02 * 78 + 1.25 + -4.330 =",null,null,null,{"$f":"C23*D17+C22+M2","v":-4.6397206819719443}]
[null,"      (logistic regression).",null,null,null,null,null,null,null,null,"so u =",{"$f":"1/(1+EXP(-O8))","v":9.5679652442775959E-3}]
[null,"   • The logit link function is defined as"]
[null,null,null,null,null,null,null,null,null,"Policy 2","ln( u / [1 -  u]) = -0.02 * 92 + 1.25 + -1.368 =",null,null,null,{"$f":"C23*D18+C22+M3","v":-1.9576486997484275}]
[null,"   • The insurance score is a continuous variable having a value between 1 and 100.",null,null,null,null,null,null,null,null,"so u =",{"$f":"1/(1+EXP(-O11))","v":0.12372173755164198}]
[null,"   • The current fitted values and insurance score for three policies as well as regression results"]
[null,"       from the fit of the insurance score variable are given below:",null,null,null,null,null,null,null,"Policy 3","ln( u / [1 -  u]) = -0.02 * 35 + 1.25 + -3.664 =",null,null,null,{"$f":"C23*D19+C22+M4","v":-3.1135616461296465}]
[null,null,"Fitted Probability","Insurance",null,null,null,null,null,null,"so u =",{"$f":"1/(1+EXP(-O14))","v":4.2551303651272687E-2}]
[null,"Policy Number","Without Insurance Score","Score"]
[null,1,1.2999999999999999E-2,78,null,null,null,null,"(c)","Logit function has range (-ꚙ, ꚙ)"]
[null,2,0.20300000000000001,92]
[null,3,2.5000000000000001E-2,35,null,null,null,null,null,"Logistic function has range (0, 1)"]
[]
[null,"Variable","Parameter Estimate",null,null,null,null,null,"(d)","The logistic function is often used to model probabilities because it takes any number"]
[null,"Intercept",1.25,null,null,null,null,null,null,"between negative infinity and infinity, and maps it to a unique value between 0 and 1."]
[null,"Insurance Score",-0.02,null,null,null,null,null,null,"Since this is the same range as probabilities, this is a natural mapping."]
[]
["(a)","Calculate the offset term to be used in the regression for each of the three policies above.",null,null,null,null,null,null,"(e)","A GLM often produces illogical results when estimation relativities for deductibles or increased limits."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A25,'Point Grid'!$C$7:$I$7,0))","v":0.5},null,null,null,null,null,null,null,null,"This is due to selection effects, such as underwriters not offering higher limits or low deductibles"]
[0.5,null,null,null,null,null,null,null,null,"to riskier insureds."]
[null,null,null,null,null,null,null,null,null,"The deductible and ILF relativities should be calculated outside of the GLM and then brought"]
["(b)","Calculate the revised fitted probability of having a claim for each of the three policies above.",null,null,null,null,null,null,null,"into the GLM by offsetting the relativities."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A29,'Point Grid'!$C$7:$I$7,0))","v":0.75}]
[0.75]
[]
["(c)","Identify the range of:"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A33,'Point Grid'!$C$7:$I$7,0))","v":0.5},"     i. the logit function"]
[0.5,"    ii. the logistic function"]
[]
["(d)","Briefly explain why logistic regression is often used to model probabilities."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A37,'Point Grid'!$C$7:$I$7,0))","v":0.25}]
[0.25]
[]
["(e)","Identify and briefly describe one situation in which the use of an offset is preferable to"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A41,'Point Grid'!$C$7:$I$7,0))","v":0.5},"(re)fitting all variables."]
[0.5]

{"sheet":"8","index":11,"n_rows":11,"n_cols":9,"rows_kept":11,"cut":false,"hidden":false,"merged":[]}
[8,"Finished",null,null,null,null,"Navigator",null,"Solution"]
[null,null,null,null,null,null,null,"(a)","The risk adjusted ILFs are more accurate as they explicitly calculate how the risk load"]
["Points","An actuary has been using risk-adjusted increased limit factors to account for riskiness in pricing.",null,null,null,null,null,null,"varies as the limits increase. Varying the profit requires more judgment and so"]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('8'!A1,'Point Grid'!A:A,0))","v":1.5},"The actuary's coworker has suggested an alternate approach of using non-risk-adjusted",null,null,null,null,null,null,"cannot be guaranteed to be accurate."]
[null,"increased limit factors and, instead, varying the profit and contingency load with the "]
[null,"policy limit.",null,null,null,null,null,null,"The risk adjusted ILFs are an involved calculation which must be calibrated to the portfolio. "]
[null,null,null,null,null,null,null,null,"It would be easier to vary the profit and contingency loads."]
["(a)","Compare and contrast the two methods with respect to each of the following:"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A8,'Point Grid'!$C$7:$I$7,0))","v":1.5},"   • Accuracy",null,null,null,null,null,null,"The risk adjusted ILFs are clear to someone with a numeric training but varying the "]
[1.5,"   • Ease of calculation",null,null,null,null,null,null,"profit and contingency loads is more likely to make intuitive sense to someone without"]
[null,"   • Clarity",null,null,null,null,null,null,"a numeric background."]

{"sheet":"9","index":12,"n_rows":30,"n_cols":23,"rows_kept":30,"cut":false,"hidden":false,"merged":[[14,9,15,9],[7,1,8,1],[7,3,8,3],[7,4,8,4],[7,7,8,7],[14,8,15,8]]}
[9,"Finished",null,null,null,null,null,null,null,null,"Navigator",null,"Solution",null,null,null,null,null,"Manual ",null,"Standard","Manual","Standard"]
[null,null,null,null,null,null,null,null,null,null,null,"(a)","Risk","Rank",null,"Quintile","Risk A","Risk B","Premium","Loss","Premium","Loss Ratio","Loss Ratio"]
["Points","Three insurers are using experience rating to determine premiums for a specific class of business. The",null,null,null,null,null,null,null,null,null,null,1,{"$f":"_xlfn.RANK.AVG(E10,$E$10:$E$19,1)","v":8},null,1,{"$f":"INDEX($M$3:$M$12,MATCH(1,$N$3:$N$12,0))","v":2},{"$f":"INDEX($M$3:$M$12,MATCH(2,$N$3:$N$12,0))","v":8},{"$f":"SUMIFS($C$10:$C$19,$B$10:$B$19,Q3)+SUMIFS($C$10:$C$19,$B$10:$B$19,R3)","v":2200},{"$f":"SUMIFS($D$10:$D$19,$B$10:$B$19,Q3)+SUMIFS($D$10:$D$19,$B$10:$B$19,R3)","v":1290},{"$f":"SUMIFS($F$10:$F$19,$B$10:$B$19,Q3)+SUMIFS($F$10:$F$19,$B$10:$B$19,R3)","v":1548},{"$f":"T3/S3","v":0.58636363636363631},{"$f":"T3/U3","v":0.83333333333333337}]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('9'!A1,'Point Grid'!A:A,0))","v":4.25},"same ten risks were rated using the experience plan of each insurer. Information related to each rating",null,null,null,null,null,null,null,null,null,null,2,{"$f":"_xlfn.RANK.AVG(E11,$E$10:$E$19,1)","v":1},null,2,{"$f":"INDEX($M$3:$M$12,MATCH(3,$N$3:$N$12,0))","v":10},{"$f":"INDEX($M$3:$M$12,MATCH(4,$N$3:$N$12,0))","v":4},{"$f":"SUMIFS($C$10:$C$19,$B$10:$B$19,Q4)+SUMIFS($C$10:$C$19,$B$10:$B$19,R4)","v":2475},{"$f":"SUMIFS($D$10:$D$19,$B$10:$B$19,Q4)+SUMIFS($D$10:$D$19,$B$10:$B$19,R4)","v":1625},{"$f":"SUMIFS($F$10:$F$19,$B$10:$B$19,Q4)+SUMIFS($F$10:$F$19,$B$10:$B$19,R4)","v":1901},{"$f":"T4/S4","v":0.65656565656565657},{"$f":"T4/U4","v":0.85481325618095738}]
[null,"plan is given below:",null,null,null,null,null,null,null,null,null,null,3,10,null,3,{"$f":"INDEX($M$3:$M$12,MATCH(5,$N$3:$N$12,0))","v":5},{"$f":"INDEX($M$3:$M$12,MATCH(6,$N$3:$N$12,0))","v":9},2525,2025,{"$f":"SUMIFS($F$10:$F$19,$B$10:$B$19,Q5)+SUMIFS($F$10:$F$19,$B$10:$B$19,R5)","v":2251},{"$f":"T5/S5","v":0.80198019801980203},0.89960017769880052]
[null,null,null,null,null,null,null,null,null,null,null,null,4,4,null,4,{"$f":"INDEX($M$3:$M$12,MATCH(7,$N$3:$N$12,0))","v":6},{"$f":"INDEX($M$3:$M$12,MATCH(8,$N$3:$N$12,0))","v":1},1910,1750,{"$f":"SUMIFS($F$10:$F$19,$B$10:$B$19,Q6)+SUMIFS($F$10:$F$19,$B$10:$B$19,R6)","v":1842},{"$f":"T6/S6","v":0.91623036649214662},0.95005428881650378]
[null,"Insurer 1's Plan",null,null,null,null,null,"Insurer 2's Plan",null,null,null,null,5,5,null,5,{"$f":"INDEX($M$3:$M$12,MATCH(9,$N$3:$N$12,0))","v":7},{"$f":"INDEX($M$3:$M$12,MATCH(10,$N$3:$N$12,0))","v":3},2175,2375,{"$f":"SUMIFS($F$10:$F$19,$B$10:$B$19,Q7)+SUMIFS($F$10:$F$19,$B$10:$B$19,R7)","v":2373},{"$f":"T7/S7","v":1.0919540229885059},1.0008428150021071]
[null,"Risk #","Manual","Loss","Mod","Standard",null,"Quintile","Manual","Standard",null,null,6,7]
[null,null,"Premium",null,null,"Premium",null,null,"Loss Ratio","Loss Ratio",null,null,7,9,null,null,null,null,null,"Sample Variance",null,{"$f":"_xlfn.VAR.S(V3:V7)","v":4.1100171383665396E-2},{"$f":"_xlfn.VAR.S(W3:W7)","v":4.7156203830242445E-3}]
[null,1,810,750,0.97,786,null,1,0.58599999999999997,0.94499999999999995,null,null,8,2,null,null,null,null,"Plan 1","Efficiency Statistic",null,null,{"$f":"W9/V9","v":0.11473481068982579}]
[null,2,900,490,0.68,612,null,2,0.65700000000000003,0.9,null,null,9,6]
[null,3,950,1075,1.1299999999999999,1074,null,3,0.80200000000000005,0.85299999999999998,null,null,10,3]
[null,4,975,650,0.78,761,null,4,0.91600000000000004,0.79700000000000004]
[null,5,1075,850,0.88,946,null,5,1.0920000000000001,0.753,null,null,"Plan 2"]
[null,6,1100,1000,0.96,1056,null,"Sample",{"$f":"_xlfn.VAR.S(I10:I14)","v":4.1101800000000188E-2},{"$f":"_xlfn.VAR.S(J10:J14)","v":5.9377999999999966E-3},null,null,"Efficiency Test =",null,{"$f":"J15/I15","v":0.1444656925000844}]
[null,7,1225,1300,1.06,1299,null,"Variance"]
[null,8,1300,800,0.72,936,null,null,null,null,null,null,"Ordering the efficiency test statistics from lowest to highest gives the plans from best to worst."]
[null,9,1450,1175,0.9,1305,null,null,null,null,null,null,"Insurer 3, Insurer 1, Insurer 2 (most to least equitable)"]
[null,10,1500,975,0.76,1140]
[null,null,null,null,null,null,null,null,null,null,null,"(b)","Insurer 1's standard loss ratios have an upward trend. This suggests they do not place enough credibility on actual experience."]
[null,"Insurer 3's Plan",null,null,null,null,null,null,null,null,null,null,"They will undercharge high risks and overcharge low risks, so good risks will leave and bad risks will stay on."]
[null,"Efficiency Test Statistic",null,0,null,null,null,null,null,null,null,null,"They will face adverse selection."]
[]
["(a)","Rank each of the insurer's rating plans from most to least equitable using the Efficiency Test as decribed ",null,null,null,null,null,null,null,null,null,null,"Insurer 2's standard loss ratios have a downward trend. This means they place too much credibility on actual experience."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A24,'Point Grid'!$C$7:$I$7,0))","v":2.75},"by Fisher et al.",null,null,null,null,null,null,null,null,null,null,"They will overcharge high risks and undercharge low risks. Although bad risks will leave, the undercharging of good risks"]
[2.75,null,null,null,null,null,null,null,null,null,null,null,"will place a strain on them, causing their book to deteriorate. "]
[]
["(b)","Explain how adverse selection may affect each of the three insurers in a well-functioning insurance",null,null,null,null,null,null,null,null,null,null,"Insurer 3 is optimal, they charge an adequate premium to both good and bad risks so do not face adverse selection."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A28,'Point Grid'!$C$7:$I$7,0))","v":1.5},"market. Assume that no adjustments are made to the experience rating plans over time."]
[1.5]

{"sheet":"10","index":13,"n_rows":38,"n_cols":16,"rows_kept":38,"cut":false,"hidden":false,"merged":[[0,7,0,8],[7,14,9,14],[7,15,9,15],[24,4,25,4],[7,10,9,10],[7,11,9,11],[7,12,9,12],[7,13,9,13]]}
[10,"Finished",null,null,null,null,null,"Navigator",null,null,"Solution"]
[null,null,null,null,null,null,null,null,null,"(a)","Do not use policy year 2018 as this year is incomplete. Assume the LDFs are policy year LDFs."]
["Points","An actuary is analyzing an experience rating plan using three years of experience that has the"]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('10'!A1,'Point Grid'!A:A,0))","v":2.25},"following information:",null,null,null,null,null,null,null,null,"Excess ratio:"]
[]
[null,"Policy effective date",null,null,"2019-01-01",null,null,null,null,null,"k = ",{"$f":"(F30-F27)/F30","v":0.79533121257485029}]
[null,"Policy term",null,null,"One Year"]
[null,"Annual loss trend",null,null,0.02,null,null,null,null,null,"Year","Expected Ultimate Unlimited Loss","% Limited","Loss Trend","LDF","Expected Ultimate Limited Loss"]
[null,"Cap for individual claims",null,null,100000]
[null,"Credibility factor",null,null,0.6]
[null,"Expected ultimate loss before modification",null,null,500000,null,null,null,null,null,2017,{"$f":"$E$11","v":500000},{"$f":"1-$L$6","v":0.20466878742514971},{"$f":"(1+$E$8)^2","v":1.0404},{"$f":"C27","v":1.5},{"$f":"(L11*M11)/(N11*O11)","v":65573.749655629144}]
[null,null,null,null,null,null,null,null,null,null,2016,{"$f":"$E$11","v":500000},{"$f":"1-$L$6","v":0.20466878742514971},{"$f":"(1+$E$8)^3","v":1.0612079999999999},{"$f":"C28","v":1.23},{"$f":"(L12*M12)/(N12*O12)","v":78399.987632268239}]
[null,"Reported Losses on Individual Claims by Policy Year as of June 30, 2018",null,null,null,null,null,null,null,null,2015,500000,0.20466878742514971,{"$f":"(1+$E$8)^4","v":1.08243216},1.2,78784.30129713229]
[null,2015,2016,2017,2018,null,null,null,null,null,null,null,null,null,"Total",{"$f":"SUM(P11:P13)","v":222758.03858502966}]
[null,3450,2389,456,5694,null,null,null,null,null,"Trend from 2017 to 2019 is 2 years, and so on."]
[null,5000,345,126890,99832]
[null,234,1236806,2345,76532,null,null,null,null,null,"Expected reported loss is",null,{"$f":"P14","v":222758.03858502966}]
[null,98000,null,1874]
[null,324789,null,690,null,null,null,null,null,"(b)",2015,2016,2017]
[null,null,null,26986,null,null,null,null,null,null,{"$f":"MIN(B15,$E$9)","v":3450},{"$f":"MIN(C15,$E$9)","v":2389},456]
[null,null,null,null,null,null,null,null,null,null,{"$f":"MIN(B16,$E$9)","v":5000},{"$f":"MIN(C16,$E$9)","v":345},{"$f":"MIN(D16,$E$9)","v":100000}]
[null,"The actuary has determined limited loss development factors (LDFs) and limited expected values",null,null,null,null,null,null,null,null,234,100000,2345]
[null,"using aggregate data from similar policies as follows:",null,null,null,null,null,null,null,null,98000,null,1874]
[null,null,null,null,null,null,null,null,null,null,100000,null,690]
[null,"Maturity to","Limited",null,"Limit","Limited Expected",null,null,null,null,null,null,26986]
[null,"Ultimate","LDFs",null,null,"Value"]
[null,"18 months",1.5,null,100000,17500,null,null,null,null,"Historical Capped Losses",null,{"$f":"SUM(K20:M25)","v":441769}]
[null,"30 months",1.23,null,500000,28567]
[null,"42 months",1.2,null,1000000,43393]
[null,null,null,null,"Unlimited",85504]
[null,null,null,null,null,null,null,null,null,null,"Mod =",{"$f":"E10*(M27/M17)+(1-E10)*1","v":1.5899072270688115}]
["(a)","Calculate the expected reported losses for this plan for the three years of experience combined."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A32,'Point Grid'!$C$7:$I$7,0))","v":1.25}]
[1.25]
[]
["(b)","Calculate the experience modification factor for this policy."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A36,'Point Grid'!$C$7:$I$7,0))","v":1}]
[1]

{"sheet":"11","index":14,"n_rows":28,"n_cols":10,"rows_kept":28,"cut":false,"hidden":false,"merged":[]}
[11,"Finished",null,null,null,null,"Navigator",null,"Solution"]
[null,null,null,null,null,null,null,"(a)"]
["Points","Given the following information for a construction insured's general liability policy that is"]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('11'!A1,'Point Grid'!A:A,0))","v":1.25},"subject to the ISO Commercial General Liability Experience and Schedule Rating Plan:",null,null,null,null,null,null,"Lookup EER, Z and MSL based on CSLC value given"]
[null,null,null,null,null,null,null,null,"EER",{"$f":"D19","v":0.92900000000000005}]
[null,"Company Subject Loss Cost",null,270500,null,null,null,null,"Z",{"$f":"C19","v":0.48}]
[null,"Actual Experience Ratio",null,0.85,null,null,null,null,"MSL",{"$f":"E19","v":151450}]
[null,"Policy Effective Date",null,"2020-01-01"]
[null,"Policy Term",null,"Annual",null,null,null,null,"Mod =",{"$f":"J6*(D7-J5)/J5","v":-4.0818083961248683E-2}]
[null,null,null,null,null,null,null,null,"The mod is a 4.08% credit."]
[null,"All major changes in the last five years for this insured:"]
[null,"   • Upgraded all equipment in 2015",null,null,null,null,null,"(b)","The upgraded equipment is already reflected in the experience so should not be counted"]
[null,"   • Improved employee training in 2018",null,null,null,null,null,null,"for schedule rating."]
[null,"   • Implemented a new safety program in 2018",null,null,null,null,null,null,"Employee training is up to 6%"]
[null,null,null,null,null,null,null,null,"Safety program is up to 2%"]
[null,"You may use the information in the table below:",null,null,null,null,null,null,"Therefore, 10% is too high, the most they should get is 8%."]
[null,"CSLC","Credibility","EER","MSL"]
[null,"250,492 – 260,752",0.47,0.92600000000000005,148050]
[null,"260,753 – 271,411",0.48,0.92900000000000005,151450]
[null,"271,412 – 282,493",0.49,0.93200000000000005,154900]
[]
["(a)","Calculate the experience modification for this policy."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A22,'Point Grid'!$C$7:$I$7,0))","v":0.5}]
[0.5]
[]
["(b)","An underwriter has selected a credit of 10% for schedule rating. "]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A26,'Point Grid'!$C$7:$I$7,0))","v":0.75},"Assess the reasonability of this selection."]
[0.75]

{"sheet":"12","index":15,"n_rows":34,"n_cols":10,"rows_kept":34,"cut":false,"hidden":false,"merged":[]}
[12,"Finished",null,null,null,null,"Navigator",null,"Solution"]
[null,null,null,null,null,null,null,"(a)","Incurred Loss Retro"]
["Points","An insured is considering an incurred loss retrospective rating plan and a paid loss deductible plan",null,null,null,null,null,null,"Per-occurrence limit and maximum ratable loss implies an aggregate limit is also present."]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('12'!A1,'Point Grid'!A:A,0))","v":3},"with the following characteristics:",null,null,null,null,null,null,"Cost = [(Expected Excess Loss paid by insurer + Primary loss paid by insured)*(1+LAE%) + fixed expense]*(1 + tax rate%)"]
[null,null,null,null,null,null,null,null,"          = [(Expected Ultimate Loss)*(1+LAE%) + fixed expense]*(tax multiplier)"]
[null,"Incurred Loss Retro",null,null,null,null,null,null,{"$f":"(F15*(1+F21)+F19)*F22","v":603580}]
[null,"Per-Occurrence Limit",null,null,null,150000]
[null,"Maximum Ratable Loss",null,null,null,500000,null,null,"Paid Loss Deductible"]
[null,null,null,null,null,null,null,null,"Cost = Deductible Payments + Premium"]
[null,"Paid Loss Deductible",null,null,null,null,null,null,"Deductible Payments = Expected Ultimate Loss limited by occurrence and aggregate"]
[null,"Per-Occurrence Deductible",null,null,null,150000,null,null,{"$f":"F17","v":325000}]
[null,"Aggregate Deductible",null,null,null,500000]
[null,null,null,null,null,null,null,null,"Premium = [(Expected Ultimate Loss - Expected Ultimate Loss limited by occurrence and aggregate) + (Expected Ultimate Loss)*LAE% + fixed expense]*(Tax multiplier)"]
[null,"The following apply to both plans:",null,null,null,null,null,null,{"$f":"((F15-F17) +F15*F21+F19)*F22","v":268830}]
[null,"Expected Ultimate Losses",null,null,null,450000]
[null,"Expected Ultimate Losses limited by occurrence",null,null,null,350000,null,null,"Cost =",{"$f":"I11+I14","v":593830}]
[null,"Expected Ultimate Losses limited by occurrence and aggregate",null,null,null,325000]
[null,null,null,null,null,null,null,null,"The expected total cost of the paid loss deductible is lower than the incurred loss retro plan."]
[null,"Fixed underwriting expenses including commission & profit",null,null,null,100000]
[null,null,null,null,null,null,null,"(b)","Recommend the paid loss deductible plan because:"]
[null,"LAE as a percentage of Loss",null,null,null,0.08,null,null,"The total cost is lower than that of the incurred loss retro plan in part due to not paying taxes on the deductible payments."]
[null,"Tax Multiplier",null,null,null,1.03,null,null,"There is a cash flow benefit to the insured because they do not have to pay their deductible until the loss is paid and the insurer seeks reimbursement."]
[null,null,null,null,null,null,null,null,"They could generate investment income from the delayed payments."]
["(a)","Compare the expected total cost of insurance of the two plans, taking into account both premium"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A24,'Point Grid'!$C$7:$I$7,0))","v":1.5},"and deductible payments, from the perspective of the insured.",null,null,null,null,null,"(c)","Increase the per-occurrence deductible. This would lower the premium paid and increase the cash flow advantage for the insured."]
[1.5,null,null,null,null,null,null,null,"Change from a paid loss deductible plan to an excess loss with self-insured retention."]
[null,null,null,null,null,null,null,null,"The insured handles all claims below the deductible, saving on expense handling."]
["(b)","Recommend and briefly justify one of the two plans above from the perspective of the insured."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A28,'Point Grid'!$C$7:$I$7,0))","v":0.5}]
[0.5]
[]
["(c)","Propose two changes to the recommended plan in part (b) above and briefly explain why each "]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A32,'Point Grid'!$C$7:$I$7,0))","v":1},"would be beneficial for the insured."]
[1]

{"sheet":"13","index":16,"n_rows":25,"n_cols":14,"rows_kept":25,"cut":false,"hidden":false,"merged":[]}
[13,"Finished",null,null,null,null,"Navigator",null,"Solution"]
[null,null,null,null,null,null,null,"(a)","P(X > 10,000) = 1 - F(10,000)",null,null,null,null,{"$f":"(6000/(10000+6000))^2","v":0.140625}]
["Points","An insurance company observes the following ground-up claim experience for a book of business:"]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('13'!A1,'Point Grid'!A:A,0))","v":2.75},"   • Claim counts (N) follow a Poisson distribution with λ = 5,000",null,null,null,null,null,null,"E[N > $10,000] = E[N] * P(X > 10,000) =",null,null,null,{"$f":"5000*N2","v":703.125},"claims in excess of $10,000."]
[null,"   • Claim size (X) follows a Pareto distribution with α = 2 and β = 6,000",null,null,null,null,null,null,"Using N has a Poisson distribution with mean 5,000."]
[]
[null,"The insurance company pays claims in excess of $10,000.",null,null,null,null,null,"(b)","Initial expected excess loss, E[S] = E[N] * (E[X] - E[X; 10,000])"]
[null,null,null,null,null,null,null,null,"E[X] =",{"$f":"6000/(2-1)","v":6000}]
[null,"Given the following:",null,null,null,null,null,null,"E[X; 10,000] =",null,{"$f":"(6000/(2-1))*(1-(6000/(10000+6000))^(2-1))","v":3750}]
[null,"   • For a Poisson distribution:"]
[null,null,null,null,null,null,null,null,"E[S] = ",{"$f":"M4*(J8-K9)","v":1582031.25}]
[null,"   • For a Pareto distribution:"]
[null,null,null,null,null,null,null,null,"Let Y = (1 + 3%) * X"]
[null,null,null,null,null,null,null,null,"E[T] = E[N'] * (E[Y] - E[Y; 10,000])"]
[null,null,null,null,null,null,null,null,"where E[N'] is the new expected number of claims over $10,000."]
[]
[null,null,null,null,null,null,null,null,"E[Y] - E[Y; 10,000] = 1.03*(E[X] - E[X; 10,000/1.03])"]
[null,null,null,null,null,null,null,null,"E[X; 10,000/1.03] = ",null,{"$f":"(6000/(2-1))*(1-(6000/(10000/1.03+6000))^(2-1))","v":3708.2818294190361}]
["(a)","Calculate the expected number of claims in excess of $10,000."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A19,'Point Grid'!$C$7:$I$7,0))","v":0.75},null,null,null,null,null,null,null,"E[Y] = ",{"$f":"1.03*(J8-K18)","v":2360.469715698393}]
[0.75,null,null,null,null,null,null,null,"Want no change, i.e. E[T] = E[S]"]
[null,null,null,null,null,null,null,null,"This means E[N'] = E[S] / E[Y] =",null,null,{"$f":"J11/J20","v":670.21882953152988}]
["(b)","Assume claim size is subject to a uniform annual inflation rate of 3%."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A23,'Point Grid'!$C$7:$I$7,0))","v":2},"Calculate the rate at which ground-up claims must change in order for there to be no change in ",null,null,null,null,null,null,"Rate of change = E[N'] / E[N] -1 =",null,null,{"$f":"L22/M4 -1","v":-4.6799886888490794E-2}]
[2,"expected annual total aggregate excess losses."]

{"sheet":"14","index":17,"n_rows":43,"n_cols":14,"rows_kept":43,"cut":false,"hidden":false,"merged":[]}
[14,"Finished",null,null,null,null,"Navigator",null,"Solution"]
[null,null,null,null,null,null,null,"(a)","R = (B + c(E - I))*T"]
["Points","The balanced plan provision for a workers' compensation risk in the state of Alabama are given below:",null,null,null,null,null,null,"B is the basic premium, c is the loss conversion factor, E is the expected loss,"]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('14'!A1,'Point Grid'!A:A,0))","v":3},"Standard Premium",null,9000000,null,null,null,null,"I is the net insurance charge and T is the tax multiplier."]
[null,"Minimum Entry Ratio, rH",null,0.28000000000000003]
[null,"Maximum Entry Ratio, rG",null,2.5499999999999998,null,null,null,null,"E = Adjusted Expected Loss / State Hazard Group Differential"]
[null,"ϕ(rG)",null,3.7900000000000003E-2,null,null,null,null,"E = ",{"$f":"D12/D11","v":5400000}]
[null,"ϕ(rH)",null,0.72529999999999994]
[null,"Loss Conversion Factor",null,1.3,null,null,null,null,"I = [ϕ(rG) - ψ(rH)] * E"]
[null,"Tax Multiplier",null,1.05,null,null,null,null,"ψ(rH) = ϕ(rH) + r - 1 =",null,{"$f":"D8+D5-1","v":5.3000000000000824E-3}]
[null,"State Hazard Group Differential",null,0.9]
[null,"Adjusted Expected Loss",null,4860000,null,null,null,null,"I = ",{"$f":"(D7-K10)*J7","v":176039.99999999956}]
[]
[null,"• Using the 2008 NCCI table of expected loss range, the expected retrospective premium = $9,493,205.",null,null,null,null,null,null,"R =",9493205]
[null,"• There is no per-occurrence loss limit.",null,null,null,null,null,null,"Solve for B"]
[null,null,null,null,null,null,null,null,"B = ",{"$f":"(J14/D10)-D9*(J7-J12)","v":2249999.6190476194}]
["(a)","Calculate the basic premium ratio to standard premium."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A17,'Point Grid'!$C$7:$I$7,0))","v":1.25},null,null,null,null,null,null,null,"Ratio of basic premium to standard premium is",null,null,null,null,{"$f":"J16/D4","v":0.24999995767195771}]
[1.25]
[null,null,null,null,null,null,null,"(b)","Current Net Insurance Charge",null,null,{"$f":"J12","v":176039.99999999956}]
["(b)","The insured believes that the insurance charge embedded in the current basic premium is unfair and"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A21,'Point Grid'!$C$7:$I$7,0))","v":1.25},"cites the following five years of loss experience the insured had with a prior carrier:",null,null,null,null,null,null,"Convert the given table of loss ratios to a table of entry ratios."]
[1.25,null,"Year","Loss Ratio",null,null,null,null,"Year ","Loss Ratio","Entry Ratio"]
[null,null,1,0.4,null,null,null,null,1,{"$f":"D24","v":0.4},{"$f":"J24/$J$29","v":0.66666666666666674}]
[null,null,2,1.65,null,null,null,null,2,{"$f":"D25","v":1.65},{"$f":"J25/$J$29","v":2.75}]
[null,null,3,0.15,null,null,null,null,3,0.15,0.25]
[null,null,4,0.55000000000000004,null,null,null,null,4,0.55000000000000004,0.91666666666666674]
[null,null,5,0.25,null,null,null,null,5,0.25,0.41666666666666669]
[null,null,null,null,null,null,null,null,"Average",{"$f":"AVERAGE(J24:J28)","v":0.6},1]
[null,"The insured's exposure base has remained stable over time."]
[null,"Compare the net insurance charge in the current basic premium for this policy to the net insurance",null,null,null,null,null,null,"Estimated insurance charge = sum( max{ 0, entry ratio - rG})/ Years"]
[null,"charge based on the prior loss experience using the plan provisions given above.",null,null,null,null,null,null,{"$f":"(MAX(0,K25-$D$6)+MAX(0,K26-$D$6)+MAX(0,K27-$D$6)+MAX(0,K28-$D$6)+MAX(0,K24-$D$6))/5","v":4.0000000000000036E-2}]
[null,null,null,null,null,null,null,null,"Estimated insurance savings = sum( max{0, rH - entry ratio})/Years"]
["(c)","Discuss the appropriateness of using a basic premium derived from the prior loss experience.",null,null,null,null,null,null,{"$f":"(MAX(0,$D$5-K25)+MAX(0,$D$5-K26)+MAX(0,$D$5-K27)+MAX(0,$D$5-K28)+MAX(0,$D$5-K24))/5","v":6.0000000000000053E-3}]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A34,'Point Grid'!$C$7:$I$7,0))","v":0.5},null,null,null,null,null,null,null,"Estimated net insurance charge = ",null,null,{"$f":"J7*(I32-I34)","v":183600.00000000017}]
[0.5]
[null,null,null,null,null,null,null,null,"Using the data provided by the insured produces a net insurance charge that is slightly"]
[null,null,null,null,null,null,null,null,"higher than the one produced by the insurer."]
[]
[null,null,null,null,null,null,null,"(c)","It isn't appropriate to use prior loss experience with another carrier to determine the net insurance charge because"]
[null,null,null,null,null,null,null,null,"the prior carrier likely had a different mix of business so the aggregate distributions would not be the same."]
[null,null,null,null,null,null,null,null,"The prior carrier may also have used different minimum and maximum entry ratios as a result of the aggregate"]
[null,null,null,null,null,null,null,null,"distribution being different."]

{"sheet":"15","index":18,"n_rows":25,"n_cols":14,"rows_kept":25,"cut":false,"hidden":false,"merged":[[12,1,14,1],[0,6,0,7]]}
[15,"Finished",null,null,null,null,"Navigator",null,null,"Solution"]
[null,null,null,null,null,null,null,null,"(a)","Expected loss ratio before loss corridor",null,null,null,{"$f":"SUMPRODUCT(C16:C19,D16:D19)","v":0.67777000000000009}]
["Points","A reinsurer has priced a quota share treaty to achieve an expected combined ratio of 90%."]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('15'!A1,'Point Grid'!A:A,0))","v":1.75},"Expenses for the treaty are as follows:",null,null,null,null,null,null,null,"Combined Ratio = loss ratio + ceding commission + fees + expenses"]
[null,null,null,null,null,null,null,null,null,"Therefore, loss ratio (with loss corridor) =",null,null,null,{"$f":"90%-C6-C7-C8-C9","v":0.62999999999999989}]
[null,"Ceding Commission",0.2,null,null,null,null,null,null,"This is the reinsurer's loss ratio with the loss corridor"]
[null,"Brokerage Fees",0.05]
[null,"Administrative Expenses",0.01,null,null,null,null,null,null,"Let x% be the percent reassumed by the primary insurer."]
[null,"Unallocated Expenses",0.01,null,null,null,null,null,null,"So 63% = 37.3%*0.03 + 53.2%*0.21 + (60% +(66.1%-60%)*(1-x%))*0.55 + (60% + (80% - 60%)*(1-x%) + (91.1%- 80%))*0.21"]
[]
[null,"The following table represents the expected loss ratio distribution for the primary insurer",null,null,null,null,null,null,null,"Solving for x yields"]
[null,"under the treaty:",null,null,null,null,null,null,null,{"$f":"1-(63% - 37.3%*0.03 - 53.2%*0.21 - 60%*0.55 - 60%*0.21 - (91.1%-80%)*0.21)/((66.1% - 60%)*0.55 + (80% -60%)*0.21)","v":0.63229649238914698}]
[null,"Range of Loss Ratios","Average","Probability "]
[null,null,"Ratio","of being "]
[null,null,"in Range","in Range"]
[null,"0 – 40%",0.373,0.03]
[null,"40 – 60%",0.53200000000000003,0.21]
[null,"60 – 80%",0.66100000000000003,0.55000000000000004]
[null,"80% or above",0.91100000000000003,0.21]
[]
[null,"The treaty includes a loss corridor from 60% to 80% loss ratio."]
[]
["(a)","Calculate the percent of loss reassumed by the primary insurer in the loss corridor."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A23,'Point Grid'!$C$7:$I$7,0))","v":1.75}]
[1.75]

{"sheet":"16","index":19,"n_rows":33,"n_cols":13,"rows_kept":33,"cut":false,"hidden":false,"merged":[[0,5,0,6]]}
[16,"Finished",null,null,null,"Navigator",null,null,"Solution"]
[null,null,null,null,null,null,null,"(a)","Use linear interpolation between the exceedance probabilities for the 200 and 500 year return periods."]
["Points","A reinsurance company is evaluating whether or not to write a $50 million excess of"]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('16'!A1,'Point Grid'!A:A,0))","v":1.75},"$50 million catastrophe reinsurance contract with a primary insurer. The reinsurer is",null,null,null,null,null,null,"The primary insurer keeps the first $50million of catastrophe losses and any excess over the layer [50m, 100m]"]
[null,"currently holding $850 million of capital and is required to hold enough capital to survive",null,null,null,null,null,null,"($000,000)"]
[null,"a 1-in-250 event. Without the new contract, the reinsurance company has a 1-in-250",null,null,null,null,null,null,"Return","Exceedance","Gross","Ceded","Net"]
[null,"probable maximum loss (PML) of $825 million which is solely driven by the ",null,null,null,null,null,null,"Period","Probability","PML","PML","PML"]
[null,"hurricane peril.",null,null,null,null,null,null,500,{"$f":"1/I8","v":2E-3},{"$f":"C16","v":105},50,{"$f":"K8-L8","v":55}]
[null,null,null,null,null,null,null,null,200,{"$f":"1/I9","v":5.0000000000000001E-3},{"$f":"C17","v":95},50,{"$f":"K9-L9","v":45}]
[null,"Given the following:"]
[null,"   • The primary insurer's PMLs are driven by the hurricane and earthquake perils only.",null,null,null,null,null,null,"1-in-250 return period has an exceedance probability of"]
[null,"   • The primary insurer's aggregate annual PMLs by return period are as follows:",null,null,null,null,null,null,{"$f":"1/250","v":4.0000000000000001E-3}]
[]
[null,"Return Period (years)","PML ($000,000)",null,null,null,null,null,"Ceded PML =",null,{"$f":"L8*(J9-I12)/(J9-J8)+L9*(I12-J8)/(J9-J8)","v":50}]
[null,1000,125]
[null,500,105,null,null,null,null,null,"Net PML =",null,{"$f":"M8*(J9-I12)/(J9-J8)+M9*(I12-J8)/(J9-J8)","v":48.333333333333329}]
[null,200,95]
[null,100,70,null,null,null,null,"(b)","We cannot add PMLs. The reinsurer has $850-$825 = $25million in available capital."]
[null,50,50,null,null,null,null,null,"The primary exposure is driven by both hurricane and earthquake perils, of which the largest"]
[null,25,30,null,null,null,null,null,"hurricane exposure is below the retention. So it would take more than 1 hurricane event."]
[null,20,25,null,null,null,null,null,"Further, hurricanes and earthquakes are assumed to be independent events so unlikely"]
[null,10,20,null,null,null,null,null,"to have multiples of each event type in the return periods considered. "]
[null,5,15,null,null,null,null,null,"The reinsurer should accept the treaty as it offers diversification benefits."]
[]
[null,"   • The largest hurricane event in the primary insurer's event catalog is $45,000,000.",null,null,null,null,null,null,"Note: The question was not clear if this was an aggregate catastrophe treaty, or a per-occurrence"]
[null,null,null,null,null,null,null,null,"treaty. If each catastrophe could exposure a new $50m - $100m layer then we have"]
["(a)","Calculate the ceded, and net, 1-in-250 PMLs for this contract for the primary insurer.",null,null,null,null,null,null,"insufficient information to conclude whether the reinsurer has sufficient capital to accept the treaty."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A27,'Point Grid'!$C$7:$I$7,0))","v":1}]
[1]
[]
["(b)","Evaluate whether the reinsurer should participate in this treaty."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A31,'Point Grid'!$C$7:$I$7,0))","v":0.75}]
[0.75]

{"sheet":"17","index":20,"n_rows":19,"n_cols":9,"rows_kept":19,"cut":false,"hidden":false,"merged":[[0,5,0,6]]}
[17,"Finished",null,null,null,"Navigator",null,null,"Solution"]
[null,null,null,null,null,null,null,"(a)","The Vulnerability module needs to be modified because it quantifies the physical damage to exposed properties."]
["Points","A catastrophe modeler would like to incorporate a new construction technique into a ",null,null,null,null,null,null,"The Inventory module needs to be updated because it describes the property exposures."]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('17'!A1,'Point Grid'!A:A,0))","v":2},"catastrophe model. This new technique would theoretically reduce the amount of building"]
[null,"damage sustained during hurricane force winds. However, experts have not reached a ",null,null,null,null,null,"(b)","The uncertainty due to the new construction technique is epistemic."]
[null,"consensus on the effectiveness of the new construction technique because it has not been",null,null,null,null,null,null,"This is because we may not know enough about the technique and there is no historical"]
[null,"exposed to an actual hurricane.",null,null,null,null,null,null,"data available. This causes the uncertainty rather than inherent randomness in the process."]
[]
["(a)","Briefly describe which module(s) of the catastrophe model would need to be modified to",null,null,null,null,null,"(c)","Use simulation techniques to recreate the randomness of catastrophe events. This"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A9,'Point Grid'!$C$7:$I$7,0))","v":0.5},"account for the new information.",null,null,null,null,null,null,"addresses aleatory uncertainty. However, simulations require more assumptions, data and"]
[0.5,null,null,null,null,null,null,null,"can be hard to calculate and explain the outcome."]
[]
["(b)","Classify the uncertainty created with the new construction technique as either aleatory or",null,null,null,null,null,null,"Could use a logic tree to assign different weights (probabilities) to parameters. This "]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A13,'Point Grid'!$C$7:$I$7,0))","v":0.5},"epistemic and briefly justify the selection.",null,null,null,null,null,null,"addresses epistemic uncertainty. It can be hard to update as it relies on expert opinions"]
[0.5,null,null,null,null,null,null,null,"which may change/not be consistent over time. Logic trees are easier to explain though."]
[]
["(c)","Briefly describe and contrast two methods the modeler could use to incorporate uncertainty"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A17,'Point Grid'!$C$7:$I$7,0))","v":1},"in this catastrophe model."]
[1]