{"text_format":"1","workbook":"xlsx","n_sheets":23,"sheets":["Instructions","Formula Sheet","Point Grid","Navigator","1","2","3","4","5","6","7","8","9","10","11","12","13","14","15","16","17","18","19"],"not_rendered":{"charts":2,"images":10,"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":false,"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":30,"n_cols":19,"rows_kept":30,"cut":false,"hidden":false,"merged":[[5,1,6,1]]}
[]
[]
[]
[]
["2019 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":2.25},1.5,0.75,null,null,null,null,null,{"$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},0.75,"","","","",""]
[2,{"$f":"SUM(C9:I9)","v":2.75},1,0.75,1,null,null,null,null,{"$f":"INDIRECT(\"'\"&A9&\"'!B1\")","v":"Finished"},null,{"$f":"A9","v":2},1,0.75,1,"","","",""]
[3,1.75,1.25,0.5,null,null,null,null,null,"Finished",null,3,1.25,0.5,"","","","",""]
[4,3,3,null,null,null,null,null,null,"Finished",null,4,3,"","","","","",""]
[5,2.75,0.5,0.5,1.5,0.25,null,null,null,"Finished",null,5,0.5,0.5,1.5,0.25,"","",""]
[6,2,0.75,0.75,0.5,null,null,null,null,"Finished",null,6,0.75,0.75,0.5,"","","",""]
[7,7.5,2.25,1.5,0.25,0.5,1.5,1,0.5,"Finished",null,7,2.25,1.5,0.25,0.5,1.5,1,0.5]
[8,1.5,1.5,null,null,null,null,null,null,"Finished",null,8,1.5,"","","","","",""]
[9,1,1,null,null,null,null,null,null,"Finished",null,9,1,"","","","","",""]
[10,1.5,0.5,0.5,0.5,null,null,null,null,"Finished",null,10,0.5,0.5,0.5,"","","",""]
[11,3.75,0.5,0.5,2.75,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.5,2.75,"","","",""]
[12,2,0.5,1,0.5,null,null,null,null,"Finished",null,12,0.5,1,0.5,"","","",""]
[13,2,1.5,0.5,null,null,null,null,null,"Finished",null,13,1.5,0.5,"","","","",""]
[14,2,1,1,null,null,null,null,null,"Finished",null,14,1,1,"","","","",""]
[15,3.5,2,1,0.5,null,null,null,null,"Finished",null,15,2,1,0.5,"","","",""]
[16,3,1.5,1.5,null,null,null,null,null,"Finished",null,16,1.5,1.5,"","","","",""]
[17,3,3,null,null,null,null,null,null,"Finished",null,17,3,"","","","","",""]
[18,5.5,0.5,1,0.5,2,1,0.5,null,"Finished",null,18,0.5,1,0.5,2,1,0.5,""]
[19,1.75,0.5,0.75,0.5,null,null,null,null,"Finished",null,19,0.5,0.75,0.5,"","","",""]
[]
["Total",{"$f":"SUM(B8:B26)","v":52.5},null,null,null,null,null,null,null,null,null,"TOTAL",{"$f":"IF(SUM(IF(M8:S26=\"\",0,1))=0,\"\",SUM(M8:S26))","v":52.5},null,"Percentage:",{"$f":"IF(M28=\"\",\"\",M28/B28)","v":1}]
[]
[null,null,null,null,null,null,null,null,null,null,null,"Passing Score:",37]

{"sheet":"Navigator","index":3,"n_rows":27,"n_cols":8,"rows_kept":27,"cut":false,"hidden":false,"merged":[[26,0,26,6],[20,0,20,6],[21,0,21,6],[22,0,22,6],[23,0,23,6],[24,0,24,6],[25,0,25,6],[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]]}
[]
[]
[]
[]
[]
["Navigator – Select a question to go to it"]
[]
["Question #",null,null,null,null,null,null,"Status"]
["Q1 -- Couret & Venter -- Multi-Dimensional Credibility -- 2.25 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J8","v":"Finished"}]
["Q2 -- Goldburd (GLM) -- Model Validation -- 2.75 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J9","v":"Finished"}]
["Q3 -- Bailey & Simon -- Experience Modification -- 1.75 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J10","v":"Finished"}]
["Q4 -- Robertson -- Weighted k-means -- 3.00 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J11","v":"Finished"}]
["Q5 -- Goldburd (GLM) -- Logistic Models -- 2.75 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J12","v":"Finished"}]
["Q6 -- Goldburd (GLM) -- Interaction Terms --2.00 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J13","v":"Finished"}]
["Q7 -- Bahnemann -- Expected Ground Up Loss  (IQ) -- 7.50 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J14","v":"Finished"}]
["Q8 -- Bahnemann -- Excess Layer Premium -- 1.50 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J15","v":"Finished"}]
["Q9 -- Mahler -- When to Use Experience Rating -- 1.00 point",null,null,null,null,null,null,{"$f":"'Point Grid'!J16","v":"Finished"}]
["Q10 -- Fisher -- Quintiles & Efficiency Tests -- 1.50 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J17","v":"Finished"}]
["Q11 -- Fisher -- Experience Modification & Efficiency Test -- 3.75 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J18","v":"Finished"}]
["Q12 -- Fisher -- Table M -- 2.00 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J19","v":"Finished"}]
["Q13 -- Bahnemann -- Aggregate Loss in Layer -- 2.00 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J20","v":"Finished"}]
["Q14 -- Fisher -- Retrospective Rating -- 2.00 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J21","v":"Finished"}]
["Q15 -- Bahnemann -- Excess Severity -- 3.50 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J22","v":"Finished"}]
["Q16 -- Fisher -- Limited Table M -- 3.00 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J23","v":"Finished"}]
["Q17 -- Clark -- Technical Ratio -- 3.00 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J24","v":"Finished"}]
["Q18 -- Clark -- Property Per Risk Treaty (IQ) -- 5.50 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J25","v":"Finished"}]
["Q19 -- Grossi -- Quota Share Reinsurance -- 1.75 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J26","v":"Finished"}]

{"sheet":"1","index":4,"n_rows":29,"n_cols":13,"rows_kept":29,"cut":false,"hidden":false,"merged":[[17,3,17,4],[17,5,17,6],[19,3,19,4],[20,3,20,4],[21,3,21,4],[19,5,19,6],[20,5,20,6],[21,5,21,6]]}
[1,"Finished",null,null,null,null,null,"Navigator",null,"Solution"]
[null,null,null,null,null,null,null,null,"(a)","Multi-Dimensional Credibility Formula"]
["Points","An insurer uses five geographical Storm Zones (A, B, C, D, and E) in their rating plan."]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('1'!A1,'Point Grid'!A:A,0))","v":2.25},"The insurer refines their rating plan using the following approach:"]
[null,"• Divide each Storm Zone into numerous geographical sectors.",null,null,null,null,null,null,null,"Here, a subscript S refers to the storm sector which is analogous to the class within a "]
[null,"• For each geographical sector and storm type, calculate the observed storm frequency",null,null,null,null,null,null,null,"hazard group. A subscript Z refers to the storm zone which plays the role of a hazard group."]
[null,"   as number of storms per year."]
[null,"   ○ There are three storm types defined as Moderate, Strong, and Severe storms.",null,null,null,null,null,null,null,"Let V represent severe storms, W strong storms, and X moderate storms. These take on the"]
[null,null,null,null,null,null,null,null,null,"role of the injury types."]
[null,"The insurer performs two credibility procedures to produce estimates of the respective"]
[null,"storm frequencies in each sector. The first is a multi-dimensional credibility procedure",null,null,null,null,null,null,null,"Applying the formula:"]
[null,"and the second is a single-dimensional credibility procedure (utilizing only a single",null,null,null,null,null,null,null,{"$f":"\"= \"&D22&\" + \"&C22&\"(\"&F22&\" - \"&D22&\") + \"&C21&\"(\"&F21&\" - \"&D21&\") + \"&C20& \"(\"&F20&\" - \"&D20&\")\"","v":"= 0.73 + 0.2(0.69 - 0.73) + 0.12(2.4 - 1.4) + 0.05(7.1 - 5.3)"}]
[null,"storm type).",null,null,null,null,null,null,null,{"$f":"D22+C22*(F22-D22)+C21*(F21-D21)+C20*(F20-D20)","v":0.93199999999999994}]
[]
["(a)","The multi-dimensional credibility results for a particular sector in Storm Zone C for ",null,null,null,null,null,null,null,"Single Dimensional Credibility Formula"]
[{"$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":1.5},"Severe storms are given in the table below. The single-dimensional credibility factor",null,null,null,null,null,null,null,null,null,null,"Z = 0.30 (from text)"]
[1.5,"is 0.30."]
[null,"Storm Type","Coefficient","Storm Zone Mean Frequency",null,"Sector Mean Frequency",null,null,null,"Applying the formula:"]
[null,"Intercept","not provided",null,null,null,null,null,null,{"$f":"\"= 0.3 * \"&F22&\" + (1 - 0.3) * \"&D22","v":"= 0.3 * 0.69 + (1 - 0.3) * 0.73"}]
[null,"Moderate",0.05,5.3,null,7.1,null,null,null,{"$f":"0.3*F22+(1-0.3)*D22","v":0.71799999999999997}]
[null,"Strong",0.12,1.4,null,2.4]
[null,"Severe",0.2,0.73,null,0.69,null,null,"(b)","Multi-dimensional credibility is based on the idea that the claim frequency for different"]
[null,null,null,null,null,null,null,null,null,"storm types are related. It assigns weights based on the relative mean frequencies of the "]
[null,"Calculate the mean frequency estimate of Severe storms for each of the two",null,null,null,null,null,null,null,"sector to the zone. There are more strong and moderate storms than severe storms"]
[null,"credibility procedures.",null,null,null,null,null,null,null,"within the sectors than the zone so those storm types have higher frequencies which increases "]
[null,null,null,null,null,null,null,null,null,"the multi-dimensional credibility. Whereas the single dimensional credibility only relies on the "]
["(b)","Fully explain why the single-dimensional credibility estimate in part (a) above is",null,null,null,null,null,null,null,"difference between the zone and sector for severe storms."]
[{"$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":0.75},"lower than the multi-dimensional credibility estimate."]
[0.75]

{"sheet":"2","index":5,"n_rows":51,"n_cols":15,"rows_kept":51,"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)","Sort the data by modeled loss cost in ascending order."]
["Points","An actuary has built two generalized linear models to predict loss costs. Management has requested a series of model validation",null,null,null,null,null,null,null,null,null,null,null,"Note both models have the same order so the plots"]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('2'!A1,'Point Grid'!A:A,0))","v":2.75},"plots to demonstrate the appropriateness of each of the new models. Output for each model, simple quantile plots, and a",null,null,null,null,null,null,null,null,null,null,null,"are identical."]
[null,"double lift plot are shown below:",null,null,null,null,null,null,null,null,null,null,null,"Obs","Actual Loss Ratio"]
[null,null,null,"Observation","Actual Loss Cost","Model A Loss Cost","Model B Loss Cost","Earned Premium",null,null,null,null,null,3,{"$f":"E9/H9","v":0}]
[null,null,null,1,1500,825,900,1800,null,null,null,null,null,2,{"$f":"E8/H8","v":0.46551724137931033}]
[null,null,null,2,675,765,800,1450,null,null,null,null,null,1,{"$f":"E7/H7","v":0.83333333333333337}]
[null,null,null,3,0,615,350,2375,null,null,null,null,null,4,{"$f":"E10/H10","v":0.8571428571428571}]
[null,null,null,4,2250,900,3000,2625,null,null,null,null,null,5,{"$f":"E11/H11","v":1.0256410256410255}]
[null,null,null,5,5000,1050,3700,4875]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"Since only 5 observations, each observation is 1 quintile"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,"(b)","• Loss Ratio plot only assesses how well the GLM distinguishes"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"between risks, not if the predictions are accurate."]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"• Double lift charts can be difficult to explain to "]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"non-technical people (management)"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"• Quantile plots are on two separate charts which makes"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"it hard to compare them."]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,"(c)","• Based on the quantile plots, model B is better at predicting"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"the actual loss cost than model A as the actual and model"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"lines are closer together."]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"• The double lift chart suggests model B is better as it "]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"tracks the actual loss cost more closely."]
[null,"Given the following:",null,null,null,null,null,null,null,null,null,null,null,"• The loss ratio plot has an upward trend for model B"]
[null,"• The actuary has already provided management with the simple quintile plots and the double lift chart shown above.",null,null,null,null,null,null,null,null,null,null,null,"this suggests the model differentiates well between"]
[null,"• The company has implemented several segmented rate changes over the last three years.",null,null,null,null,null,null,null,null,null,null,null,"risks."]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"• Recommend implementing model B"]
["(a)","For each model, provide a loss ratio plot that management can use to assess lift. Identify the basis of sorting the data."]
[{"$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":1}]
[1]
[]
["(b)","Briefly describe one drawback of each type of model validation plot that the actuary has provided to management,"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A45,'Point Grid'!$C$7:$I$7,0))","v":0.75},"including the plot produced in part (a) above."]
[0.75]
[]
["(c)","Using all three types of model validation plots provided to management, recommend which model should be "]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A49,'Point Grid'!$C$7:$I$7,0))","v":1},"implemented. Do not perform any calculations."]
[1]

{"sheet":"3","index":6,"n_rows":22,"n_cols":13,"rows_kept":22,"cut":false,"hidden":false,"merged":[[0,4,0,5]]}
[3,"Finished",null,null,"Formula Sheet",null,null,"Navigator",null,"Solution"]
[null,null,null,null,null,null,null,null,"(a)","Using ",null,null,"yields 0.101 = 10p / (1 - p)"]
["Points","An insurance company has a private passenger auto book of business with an experience"]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('3'!A1,'Point Grid'!A:A,0))","v":1.75},"modification factor in its rating plan.",null,null,null,null,null,null,null,"Solving this for p gives p = 0.01."]
[null,"Given the following:"]
[null,"   • Annual claims for an individual driver follow a negative binomial distribution with ",null,null,null,null,null,null,null,"Next, we need the probability of no claims, Pr(N = 0)"]
[null,"       r = 10."]
[null,"   • The expected claim frequency for the entire book of business is 0.101."]
[null,"   • The credibility for the group of risks that have had at least one accident in the last year"]
[null,"      is 0.02.",null,null,null,null,null,null,null,"Here x = 0, so"]
[]
["(a)","Calculate the experience modification factor for a policy that has had at least one"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A12,'Point Grid'!$C$7:$I$7,0))","v":1.25},"accident in the last year."]
[1.25,null,null,null,null,null,null,null,null,"Then "]
[]
["(b)","Describe why a class with a higher volume of claims and more exposures may have less",null,null,null,null,null,null,null,"Then"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A16,'Point Grid'!$C$7:$I$7,0))","v":0.5},"credibility than a class with fewer claims and exposures.",null,null,null,null,null,null,null,"So Mod =",{"$f":"\"0.02 * 10.458 + (1 - 0.02) * 1\"","v":"0.02 * 10.458 + (1 - 0.02) * 1"}]
[0.5,null,null,null,null,null,null,null,null,"=",{"$f":"0.02*10.458+(1-0.02)*1","v":1.18916}]
[]
[null,null,null,null,null,null,null,null,"(b)","Experience rating credibility depends not only on the volume of data but also the "]
[null,null,null,null,null,null,null,null,null,"variance within a class. Therefore, a class may receive more credibility than a class with"]
[null,null,null,null,null,null,null,null,null,"more volume if it has more variance within the class."]

{"sheet":"4","index":7,"n_rows":20,"n_cols":13,"rows_kept":20,"cut":false,"hidden":false,"merged":[]}
[4,"Finished",null,null,null,null,"Navigator",null,"Solution"]
[null,null,null,null,null,null,null,null,"Although class 5 looks to be an outlier at both limits, it also has the lowest earned premium"]
["Points","An actuary wants to cluster six workers compensation classes based on excess ratios at",null,null,null,null,null,null,"so more volatility is expected. Recommend the L2 measure as it isn't a sufficient outlier."]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('4'!A1,'Point Grid'!A:A,0))","v":3},"two limits: 750,000 and 1,500,000. The actuary decides to use a weighted k-means",null,null,null,null,null,null,null,"Centroid"]
[null,"algorithm with three clusters. Given the following:",null,null,null,null,null,null,"Cluster",750000,1500000]
[null,"Class","On-Leveled Earned Premium","Normalized Excess Ratio at 750,000 Limit","Normalized Excess Ratio at 1,500,000 Limit","Initial Cluster",null,null,"A",{"$f":"(C7*D7+C10*D10+C11*D11)/(C7+C10+C11)","v":0.26555555555555554},{"$f":"(C7*E7+C10*E10+C11*E11)/(C7+C10+C11)","v":0.11322222222222222}]
[null,1,7000000,0.25,9.5000000000000001E-2,"A",null,null,"B",{"$f":"(C8*D8+C12*D12)/(C8+C12)","v":0.22060606060606061},{"$f":"(C8*E8+C12*E12)/(C8+C12)","v":8.9272727272727267E-2}]
[null,2,8000000,0.2,0.08,"B",null,null,"C",{"$f":"D9","v":0.17},{"$f":"E9","v":6.2E-2}]
[null,3,7500000,0.17,6.2E-2,"C"]
[null,4,6000000,0.13,4.8000000000000001E-2,"A",null,null,"Class","Dist to A","Dist to B","Dist to C","New Cluster"]
[null,5,5000000,0.45,0.217,"A",null,null,1,{"$f":"SQRT((D7-$J$6)^2+(E7-$K$6)^2)","v":2.3958812394566313E-2},{"$f":"SQRT((D7-$J$7)^2+(E7-$K$7)^2)","v":2.9946708099341135E-2},{"$f":"SQRT((D7-$J$8)^2+(E7-$K$8)^2)","v":8.6539008545279725E-2},"A"]
[null,6,8500000,0.24,9.8000000000000004E-2,"B",null,null,2,{"$f":"SQRT((D8-$J$6)^2+(E8-$K$6)^2)","v":7.3493175966073507E-2},{"$f":"SQRT((D8-$J$7)^2+(E8-$K$7)^2)","v":2.2596309538838916E-2},{"$f":"SQRT((D8-$J$8)^2+(E8-$K$8)^2)","v":3.4985711369071804E-2},"B"]
[null,null,null,null,null,null,null,null,3,0.10841854198850664,5.7487172681884788E-2,0,"C"]
[null,"• At the start of the algorithm, the actuary randomly assigned each class to a cluster.",null,null,null,null,null,null,4,0.15043020612091257,9.956352863914604E-2,4.2379240200834188E-2,"C"]
[null,"• The actuary has observed that the 750,000 limit is selected by the insured 75% of the",null,null,null,null,null,null,5,0.21163548910074978,0.26255634753137747,0.32003906011610522,"A"]
[null,"    time, consistent across all six classes.",null,null,null,null,null,null,6,2.9745629412332199E-2,2.1267114860083685E-2,7.8714674616617686E-2,"B"]
[]
["(a)","Briefly justify which measure to use to calculate the distance between vectors and using"]
[{"$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":3},"that measure, determine the cluster for each class after the first iteration of the "]
[3,"weighted k-means algorithm."]

{"sheet":"5","index":8,"n_rows":35,"n_cols":23,"rows_kept":35,"cut":false,"hidden":false,"merged":[]}
[5,"Finished",null,null,null,null,"Navigator",null,"Solution"]
[null,null,null,null,null,null,null,"(a)","The logit link function should be used,",null,null,null,null,"where "]
["Points","The following confusion matrix shows the results from a claim fraud model with a "]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('5'!A1,'Point Grid'!A:A,0))","v":2.75},"discrimination threshold of 25%:",null,null,null,null,null,null,"This is appropriate because the inverse of the logit function is the logistic function,"]
[null,null,"Predicted",null,null,null,null,null,null,null,"which takes any real number and maps it to the interval [0, 1]."]
[null,"Actual","Yes","No",null,null,null,null,null,null,"By choosing a discriminant threshold we can turn this into a "]
[null,"Yes",72,162,null,null,null,null,null,null,"binary outcome."]
[null,"No",63,1203]
[null,null,null,null,null,null,null,"(b)","Sensitivity = TP / (TP + FN)",null,null,null,"TP = True Positive"]
["(a)","Identify a link function that can be used for a generalized linear model that has a binary",null,null,null,null,null,null,null,{"$f":" \"= \"&C7 &\" / (\" &C7&\" + \"&D7&\")\"","v":"= 72 / (72 + 162)"},null,null,"TN = True Negative"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A10,'Point Grid'!$C$7:$I$7,0))","v":0.5},"target variable and briefly explain why this link function is appropriate.",null,null,null,null,null,null,null,{"$f":"C7/(C7+D7)","v":0.30769230769230771},null,null,"FP = False Positive"]
[0.5,null,null,null,null,null,null,null,null,null,null,null,"FN = False Negative"]
[null,null,null,null,null,null,null,null,"Specificity = TN / (TN + FP)"]
["(b)","Calculate the sensitivity and specificity from the above data.",null,null,null,null,null,null,null,{"$f":" \"= \"&D8 &\" / (\" &D8&\" + \"&C8&\")\"","v":"= 1203 / (1203 + 63)"}]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A14,'Point Grid'!$C$7:$I$7,0))","v":0.5},null,null,null,null,null,null,null,null,{"$f":"D8/(D8+C8)","v":0.95023696682464454}]
[0.5,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Current Model",null,"No Pred Power",null,"Perfect"]
[null,null,null,null,null,null,null,"(c)","X-axis: False positive rate = 1 - Specificity.   Y-axis: Sensitivity (True positive rate)",null,null,null,null,null,null,null,"Threshold","x","y","x","y","x","y"]
["(c)","Plot the receiver operating characteristic (ROC) curve with the discrimination threshold",null,null,null,null,null,null,null,null,null,null,null,null,null,null,0,0,0,0,0,0,0]
[{"$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},"of 25%. Label each axis, the coordinates, and the discrimination thresholds of 100%, 25%",null,null,null,null,null,null,null,null,null,null,null,null,null,null,0.25,{"$f":"1-J15","v":4.9763033175355464E-2},{"$f":"J11","v":0.30769230769230771},0.25,0.25,0,1]
[1.5,"and 0% on the curve.",null,null,null,null,null,null,null,null,null,null,null,null,null,null,1,1,1,1,1,1,1]
[]
[null,"In addition, plot the ROC curve for each of the following two models:"]
[null,"    i. A model with no predictive power"]
[null,"  ii. A hypothetical \"perfect\" model"]
[]
["(d)","Briefly describe how the severity of claims will impact the selection of the model threshold."]
[{"$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.25}]
[0.25]
[]
[]
[]
[]
[null,null,null,null,null,null,null,"(d)","The more severe the claims, the lower the discrimination threshold should be because"]
[null,null,null,null,null,null,null,null,"the benefit of identifying fraudulent claims will outweigh the cost of investigating the"]
[null,null,null,null,null,null,null,null,"false positives (predicted fraud when no fraud actually occurred)."]

{"sheet":"6","index":9,"n_rows":33,"n_cols":9,"rows_kept":33,"cut":false,"hidden":false,"merged":[]}
[6,"Finished",null,null,null,null,"Navigator",null,"Solution"]
[null,null,null,null,null,null,null,"(a)","Sprinklered property, AOI = 200,000, Occupancy Class 2"]
["Points","An actuary creates a generalized linear model (GLM) to estimate commercial property claim",null,null,null,null,null,null,{"$f":"\"= exp(\"&E14&\" + \"& E15&\" + \"&E18&\" + \"&E19&\" * ln(200,000) + \"&E20&\" * ln(200,000))\"","v":"= exp(-8.4607 + 0.2714 + 0.7228 + 0.4311 * ln(200,000) + -0.096 * ln(200,000))"}]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('6'!A1,'Point Grid'!A:A,0))","v":2},"frequency by occupancy class and amount of insurance (AOI) for sprinklered and",null,null,null,null,null,null,{"$f":"EXP(E14+E15+E18+E19*LN(200000)+E20*LN(200000))","v":3.4175541332503639E-2}]
[null,"non-sprinklered risks. Given the following:"]
[null,"   • Occupancy class is a categorical variable with four levels: Class 1, 2, 3 and 4.",null,null,null,null,null,null,"Non-Sprinklered property, AOI = 200,000, Occupancy Class 2"]
[null,"   • Sprinklered status is a categorical variable with two levels: sprinklered and ",null,null,null,null,null,null,{"$f":"\"= exp(\"&E14&\" + \"& E15&\" + \"&E19&\" * ln(200,000))\"","v":"= exp(-8.4607 + 0.2714 + 0.4311 * ln(200,000))"}]
[null,"      non-sprinklered.",null,null,null,null,null,null,{"$f":"EXP(E14+E15+E19*LN(200000))","v":5.3543435149753148E-2}]
[null,"   • The natural log of AOI, ln(AOI), is a continuous variable."]
[null,"   • The log link function is selected.",null,null,null,null,null,null,{"$f":"\"Ratio = \"&TEXT(I4,\"0.000000\") & \" / \"&TEXT(I8,\"0.000000\")","v":"Ratio = 0.034176 / 0.053543"}]
[null,"   • An interaction variable is included as ln(AOI) for sprinklered and zero otherwise.",null,null,null,null,null,null,{"$f":"I4/I8","v":0.63827696592345362}]
[null,"   • The model results are as follows:"]
[null,null,"Parameter",null,"Coefficient",null,null,"(b)","When we rebase the model, the predicted value does not change, just the coefficients."]
[null,null,"Intercept",null,-8.4606999999999992,null,null,null,"By rebasing to AOI = 200,000 we have a ln (AOI / 200,000) term in the model."]
[null,null,"Occupancy Class 2",null,0.27139999999999997,null,null,null,"This is 0 when the AOI  equals 200,000"]
[null,null,"Occupancy Class 3",null,0.36199999999999999,null,null,null,"We can equate predictions to determine the new model coefficients."]
[null,null,"Occupancy Class 4",null,3.95E-2,null,null,null,"It's easiest to use the Non-Sprinklered property found in part (a) above and to ignore "]
[null,null,"Sprinklered",null,0.7228,null,null,null,"the exponential as it is on both sides."]
[null,null,"ln(AOI)",null,0.43109999999999998,null,null,null,"We have"]
[null,null,"Sprinklered: Yes, ln(AOI)",null,-9.6000000000000002E-2,null,null,null,{"$f":"E14&\" + \"&E15&\" + \"&E19&\" * ln(200,000) = b + \"&E15","v":"-8.4607 + 0.2714 + 0.4311 * ln(200,000) = b + 0.2714"}]
[null,null,null,null,null,null,null,null,"(initial model terms) = (new coefficient + unchanged occupancy class + no contribution from AOI)"]
["(a)","Calculate the ratio of the estimated model frequency of a sprinklered property to that of a",null,null,null,null,null,null,"b is the rebased intercept term"]
[{"$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},"non-sprinklered property for AOI = 200,000 and Occupancy Class 2.",null,null,null,null,null,null,"Solving for b yields"]
[0.75,null,null,null,null,null,null,null,{"$f":"E14+E19*LN(200000)","v":-3.1986620825119418}]
[]
["(b)","Calculate the intercept term if AOI is centered at the base level of 200,000.",null,null,null,null,null,"(c)","The transformed intercept is indicated at the base case"]
[{"$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},null,null,null,null,null,null,null,"• The intercept now represents all variables at their base case, making the model easier "]
[0.75,null,null,null,null,null,null,null,"   to interpret as now it's the average frequency at base levels."]
[]
["(c)","Briefly describe two advantages of centering variables of a GLM at their base levels.",null,null,null,null,null,null,"Sign of the Interacted Variable"]
[{"$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":0.5},null,null,null,null,null,null,null,"• When a variable isn't centered on its base level the coefficient of an interaction term"]
[0.5,null,null,null,null,null,null,null,"   may have the opposite sign to what is intuitively expected. By rebasing, we make the"]
[null,null,null,null,null,null,null,null,"   model easier to interpret relative to our intuition."]

{"sheet":"7","index":10,"n_rows":84,"n_cols":18,"rows_kept":84,"cut":false,"hidden":false,"merged":[[25,15,26,15],[25,16,26,16],[5,6,5,7],[6,6,6,7],[7,6,7,7],[8,6,8,7],[0,10,0,11],[0,5,0,6],[20,3,20,4],[21,3,21,4],[39,7,39,8],[12,3,12,4],[13,3,13,4],[14,3,14,4],[15,3,15,4],[19,3,19,4]]}
[7,"Finished",null,null,null,"Formula Sheet",null,null,null,null,"Navigator",null,null,"Solution"]
[null,null,null,null,null,null,null,null,null,null,null,null,"(a)","Loss Elimination Ratio (LER) =  E[X; 100,000] / E[X]"]
["Points","An actuary is pricing a workers compensation policy. Given the following:",null,null,null,null,null,null,null,null,null,null,null,"Using the formulas on the Formula Sheet we calculate"]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('7'!A1,'Point Grid'!A:A,0))","v":7.5},null,"Policy Effective Date",null,null,null,"2020-01-01"]
[null,null,"Policy Term",null,null,null,"One year",null,null,null,null,null,null,"LER = ",null,"=",{"$f":"1-(22800/122800)^0.3","v":0.39657827218165509}]
[null,null,"Annual Loss Trend",null,null,null,4.4999999999999998E-2]
[null,null,"Cap for individual claims",null,null,null,100000,null,null,null,null,null,null,"This yields the expected ultimate limited loss before modification"]
[null,null,"Credibility factor",null,null,null,0.4,null,null,null,null,null,null,"as",{"$f":"G9&\" * \"&TEXT(Q5,\"0.0000\")& \" = \"&TEXT(G9*Q5,\"0,000.00\")","v":"1064000 * 0.3966 = 421,959.28"}]
[null,null,"Expected ultimate loss before modification",null,null,null,1064000]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"Now de-trend and de-develop back to the years in the "]
[null,"As of June 30, 2019, ground up reported losses are:",null,null,null,null,null,null,null,null,null,null,null,"experience period."]
[null,null,"Policy Year","Total Reported Loss",null,"Individual Claims over $100,000",null,null,null,null,null,null,null,"The effective date is 1/1/20, since we lag the experience period"]
[null,null,2016,392457,null,128305,null,null,null,null,null,null,null,"by 1 year, the years in the period are 2018, 2017, and 2016"]
[null,null,2017,1013863,null,525626,152860]
[null,null,2018,459798,null,275865,null,null,null,null,null,null,null,null,"Expected",null,null,"Expected"]
[null,null,2019,181325,null,"None",null,null,null,null,null,null,null,"PY","Limited","Detrend","De-Develop","Reported"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,2018,{"$f":"$G$9*$Q$5","v":421959.28160128102},{"$f":"(1+$G$6)^-2","v":0.91572995123738021},{"$f":"1/D22","v":0.77760497667185069},{"$f":"PRODUCT(O17:Q17)","v":300467.14802869444}]
[null,"The following limited loss development factors (LDFs) apply to this policy:",null,null,null,null,null,null,null,null,null,null,null,2017,{"$f":"$G$9*$Q$5","v":421959.28160128102},{"$f":"(1+$G$6)^-3","v":0.87629660405490928},{"$f":"1/D21","v":0.91407678244972568},{"$f":"PRODUCT(O18:Q18)","v":337990.38895489182}]
[null,null,"Maturity to Ultimate","Limited LDF",null,null,null,null,null,null,null,null,null,2016,421959.28160128102,{"$f":"(1+$G$6)^-4","v":0.83856134359321488},{"$f":"1/D20","v":0.9505703422053231},336348.6141836482]
[null,null,"42 months",1.052,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(R17:R19)","v":974806.15116723441}]
[null,null,"30 months",1.0940000000000001]
[null,null,"18 months",1.286,null,null,null,null,null,null,null,null,null,"Note: The average future accident date is 7/1/20, so the trend length"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"is 2 years for 2018."]
[null,"Based on an internal study, the actuary believes that claim severity follows a shifted Pareto distribution"]
[null,"with α = 1.3 and β = 22,800.",null,null,null,null,null,null,null,null,null,null,"(b)","Limited Reported Losses"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Excess of cap","Total Capped"]
[null,"The following expenses apply to this policy:",null,null,null,null,null,null,null,null,null,null,null,"PY","Total"]
[null,null,"Loss Adjustment Expense (LAE)",null,"7.5% of loss",null,null,null,null,null,null,null,null,2018,{"$f":"D15","v":459798},{"$f":"F15-100000","v":175865},{"$f":"O28-P28","v":283933}]
[null,null,"Taxes and Fees",null,"3.5% of gross premium",null,null,null,null,null,null,null,null,2017,{"$f":"D14","v":1013863},{"$f":"(F14-100000)+(G14-100000)","v":478486},{"$f":"O29-P29","v":535377}]
[null,null,"Acquisition",null,"17% of gross premium",null,null,null,null,null,null,null,null,2016,{"$f":"D13","v":392457},{"$f":"F13-100000","v":28305},364152]
[null,null,"Profit and Contingencies",null,"0% of gross premium",null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(Q28:Q30)","v":1183462},"Total"]
[]
[null,"The insurer's management is concerned about capacity on risks of this type. To address these concerns,",null,null,null,null,null,null,null,null,null,null,null,"Experience Mod = (Actual / Expected) * Z + (1 - Z)*1"]
[null,"they are requiring facultative reinsurance to support this account. Under the treaty, the reinsurer would",null,null,null,null,null,null,null,null,null,null,null,{"$f":"\"(\"&Q31&\" / \"&R20&\") * \"&G8&\" + (1 - \"&G8&\") * 1\"","v":"(1183462 / 974806.151167234) * 0.4 + (1 - 0.4) * 1"}]
[null,"assume all aggregate losses between $2,000,000 and $4,000,000. The primary insurer will retain all LAE.",null,null,null,null,null,null,null,null,null,null,null,{"$f":"Q31/R20*G8+(1-G8)*1","v":1.0856194223160864}]
[]
[null,"The primary insurer's actuary believes the following Table M and the associated expected loss groupings",null,null,null,null,null,null,null,null,null,null,null,"Total modified ground up unlimited loss ="]
[null,"(ELGs) are appropriate for risks of this type:",null,null,null,null,null,null,null,null,null,null,null,"Experience Mod * Expected ultimate unlimited loss"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"N35*G9","v":1155099.065344316}]
[null,"ELG","Loss Range",null,null,null,null,"ELG"]
[null,31,"$730,000 – 820,000",null,"Entry Ratio",31,30,29,28,27,26,null,"(c)","• This approach gives equal weight to all years of experience"]
[null,30,"$820,001 – 930,000",null,1.5,0.18759999999999999,0.1764,0.16489999999999999,0.15290000000000001,0.14419999999999999,0.1343,null,null,"    while the method used in parts (a) and (b) gives more weight"]
[null,29,"$930,001 – 1,090,000",null,1.75,0.14899999999999999,0.1376,0.12570000000000001,0.11310000000000001,0.1048,9.6299999999999997E-2,null,null,"    to the older years."]
[null,28,"$1,090,001 – 1,280,000",null,2,0.1195,0.10829999999999999,9.64E-2,8.3799999999999999E-2,7.6200000000000004E-2,6.9199999999999998E-2]
[null,27,"$1,280,001 – 1,515,000",null,2.25,9.6799999999999997E-2,8.5900000000000004E-2,7.4499999999999997E-2,6.2300000000000001E-2,5.5500000000000001E-2,4.9700000000000001E-2,null,"(d)"]
[null,26,"$1,515,001 – 1,844,000",null,2.5,7.9100000000000004E-2,6.8699999999999997E-2,5.79E-2,4.65E-2,4.0399999999999998E-2,3.5700000000000003E-2]
[null,null,null,null,2.75,6.5199999999999994E-2,5.5399999999999998E-2,4.53E-2,3.4700000000000002E-2,2.9499999999999998E-2,2.5700000000000001E-2,null,null,{"$f":"\"= [ \"&TEXT(G9,\"0,000\") &\" * \"&TEXT(N35,\"0.000\")&\" * (1 + 7.5%) ] / ( 1 - 3.5% - 17% )\"","v":"= [ 1,064,000 * 1.086 * (1 + 7.5%) ] / ( 1 - 3.5% - 17% )"}]
[null,null,null,null,3,5.4100000000000002E-2,4.4999999999999998E-2,3.56E-2,2.5999999999999999E-2,2.1499999999999998E-2,1.8499999999999999E-2,null,null,{"$f":"(G9*N35*(1+7.5%))/(1-3.5%-17%)","v":1561926.4091133832}]
[null,null,null,null,3.25,4.5199999999999997E-2,3.6700000000000003E-2,2.8199999999999999E-2,1.9599999999999999E-2,1.5699999999999999E-2,1.3299999999999999E-2]
[null,null,null,null,3.5,3.7999999999999999E-2,3.0200000000000001E-2,2.24E-2,1.4800000000000001E-2,1.15E-2,9.5999999999999992E-3,null,"(e)","From part (b) the experience modified expected unlimited"]
[null,null,null,null,3.75,3.2099999999999997E-2,2.5000000000000001E-2,1.7899999999999999E-2,1.11E-2,8.3999999999999995E-3,6.8999999999999999E-3,null,null,{"$f":"\"loss is \"&TEXT(N39,\"#,###.00\")","v":"loss is 1,155,099.07"}]
[null,null,null,null,4,2.7300000000000001E-2,2.07E-2,1.44E-2,8.3999999999999995E-3,6.1999999999999998E-3,5.0000000000000001E-3,null,null,"This corresponds to Expected Loss Group 28"]
[]
["(a)","Calculate the expected ground up reported loss limited to $100,000 per claim for this insured for the three",null,null,null,null,null,null,null,null,null,null,null,"Now compute the entry ratios for the reinsurer's layer."]
[{"$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},"years of experience combined.",null,null,null,null,null,null,null,null,null,null,null,"Since there's only a per-occurrence cap, this is a Table M"]
[2.25,null,null,null,null,null,null,null,null,null,null,null,null,"entry ratio, i.e. Unlimited Actual Loss / Expected Unlimited Loss"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"At the lower bound the entry ratio is 2 million / part (b)"]
["(b)","Calculate the total modified ground up unlimited expected loss for this policy.",null,null,null,null,null,null,null,null,null,null,null,{"$f":"ROUND(2*10^6/N39,2)","v":1.73},"which rounds to 1.75"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A58,'Point Grid'!$C$7:$I$7,0))","v":1.5},null,null,null,null,null,null,null,null,null,null,null,null,"At the upper bound the entry ratio is 4 million / part (b)"]
[1.5,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"ROUND(4*10^6/N39,2)","v":3.46},"which rounds to 3.50"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"Looking these values up in the given table yields"]
["(c)","Alternatively, the actuary could have trended and developed reported losses to the cost level of the ",null,null,null,null,null,null,null,null,null,null,null,"φ(1.75) = 0.1131"]
[{"$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":0.25},"prospective policy period. Briefly explain why this approach would not produce an identical modification",null,null,null,null,null,null,null,null,null,null,null,"φ(3.50) = 0.0148"]
[0.25,"factor."]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"Hence, the loss in reinsurer layer is"]
["(d)","Calculate the guaranteed cost premium for this insured.",null,null,null,null,null,null,null,null,null,null,null,"(φ(1.75) - φ(3.50))* (part (b))"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A66,'Point Grid'!$C$7:$I$7,0))","v":0.5},null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"(I43-I50)*N39","v":113546.23812334625}]
[0.5]
[null,null,null,null,null,null,null,null,null,null,null,null,"(f)","The difference between the premium charged for the layer"]
["(e)","Calculate the loss expected to be ceded to the reinsurer under this treaty. Round to the nearest entry ratio",null,null,null,null,null,null,null,null,null,null,null,{"$f":"\"and the loss in the layer is $200,000 - \"&TEXT(N67,\"$0,000.00\")","v":"and the loss in the layer is $200,000 - $113,546.24"}]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A70,'Point Grid'!$C$7:$I$7,0))","v":1.5},"(i.e. do not interpolate).",null,null,null,null,null,null,null,null,null,null,null,"The primary insurer must load this for profit and variable expenses"]
[1.5,null,null,null,null,null,null,null,null,null,null,null,null,"and add it to the guaranteed cost premium found in part (d)."]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"Hence, the new premium is"]
["(f)","A reinsurer has quoted a premium of $200,000 for this treaty. Calculate the premium the primary insurer must charge in ",null,null,null,null,null,null,null,null,null,null,null,{"$f":"TEXT(N48,\"0,000.00\") &\" + \"&TEXT(200000-N67,\"0,000.00\")&\" / (1 - 3.5% - 17%)\"","v":"1,561,926.41 + 86,453.76 / (1 - 3.5% - 17%)"}]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A74,'Point Grid'!$C$7:$I$7,0))","v":1},"order to maintain the same underwriting profit.",null,null,null,null,null,null,null,null,null,null,null,{"$f":"N48+(200000-N67)/(1-3.5%-17%)","v":1670673.2793984823}]
[1]
[null,null,null,null,null,null,null,null,null,null,null,null,"(g)","• The choice of a Pareto distribution may not be appropriate"]
["(g)","Explain how the assumptions of the primary insurer's actuary may have resulted in an inequitable premium calculated in",null,null,null,null,null,null,null,null,null,null,null,"   as it heavily influences the tail of the distribution. This could"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A78,'Point Grid'!$C$7:$I$7,0))","v":0.5},"part (f) above.",null,null,null,null,null,null,null,null,null,null,null,"   make the insurance charges either under or overstate premiums,"]
[0.5,null,null,null,null,null,null,null,null,null,null,null,null,"   making them inequitable."]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"• The calculation in part (f) also doesn't account for the "]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"   primary insurer transferring a large part of its uncertain risk"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"   to the reinsurer. The primary insurer could lower its profit"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"   or risk load to compensate and make premiums equitable."]

{"sheet":"8","index":11,"n_rows":20,"n_cols":12,"rows_kept":20,"cut":false,"hidden":false,"merged":[]}
[8,"Finished",null,null,null,null,"Navigator",null,"Solution"]
[null,null,null,null,null,null,null,"(a)","ILF(500,000) = 156,250 / 58,750 =",null,null,{"$f":"D15/D13","v":2.6595744680851063}]
["Points","Given the following for a policy:",null,null,null,null,null,null,"ILF(1,000,000) = 218,750 / 58,750 = ",null,null,{"$f":"D16/D13","v":3.7234042553191489}]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('8'!A1,'Point Grid'!A:A,0))","v":1.5},"   • Basic Limit = 100,000"]
[null,"   • Expected Basic Limit Indemnity = 65,000",null,null,null,null,null,null,"Expected Losses = Expected Basic Limit Indemnity * (ILF (1m) - ILF (500k))"]
[null,"   • Allocated Loss Adjustment Expense (ALAE) = 20% of Indemnity",null,null,null,null,null,null,{"$f":"65000*(L3-L2)","v":69148.936170212764}]
[null,"   • Unallocated Loss Adjustment Expense = 1.5% of Indemnity and ALAE"]
[null,"   • All other expenses are variable and are 15% of the premium",null,null,null,null,null,null,"Premium = [Expected Losses *(1 + ALAE%)*(1 + ULAE%) ] / [1 - Var. Exp. - Profit %]"]
[null,"   • Risk load = 0%",null,null,null,null,null,null,{"$f":"(I6*(1+20%)*(1+1.5%))/(1-15%-2.5%)","v":102088.9748549323}]
[null,"   • Profit load = 2.5%"]
[null,"   • The following table shows Indemnity-only limited expected severity:"]
[null,null,"Limit","E[X; L]"]
[null,null,100000,58750]
[null,null,200000,100000]
[null,null,500000,156250]
[null,null,1000000,218750]
[]
["(a)","Calculate the premium for this policy with a 500,000 limit over a 500,000 deductible."]
[{"$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]

{"sheet":"9","index":12,"n_rows":16,"n_cols":9,"rows_kept":16,"cut":false,"hidden":false,"merged":[[8,3,9,3],[8,4,9,4],[10,3,11,3],[10,4,11,4],[8,1,9,2],[10,1,11,2]]}
[9,"Finished",null,null,null,null,"Navigator",null,"Solution"]
[null,null,null,null,null,null,null,"(a)","High VHM means there is significant variance between risks within the class, this"]
["Points","An insurer's risk classification system contains four classes (A, B, C, and D). The insurer is considering",null,null,null,null,null,null,"causes the credibility of the experience rating to increase, making experience rating"]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('9'!A1,'Point Grid'!A:A,0))","v":1},"using experience rating to rate all four classes.",null,null,null,null,null,null,"more useful."]
[null,"Given the following:",null,null,null,null,null,null,"High EVPV means there is a lot of volatility in loss experience that isn't explained"]
[null,null,null,"Low Variance of","High Variance of",null,null,null,"by differences between risks in the class. This causes the credibility of the experience"]
[null,null,null,"Hypothetical Means","Hypothetical Means",null,null,null,"rating to decrease, making it less useful."]
[null,null,null,"within Class","within Class",null,null,null,"Hence experience rating is most useful for Class B and least useful for Class C."]
[null,"Low Expected Value of \nProcess Variance",null,"Class A","Class B"]
[]
[null,"High Expected Value of\nProcess Variance",null,"Class C","Class D"]
[]
[]
["(a)","From the insurer's perspective, briefly explain the class for which experience rating is expected to be"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A14,'Point Grid'!$C$7:$I$7,0))","v":1},"the most useful and the class for which experience rating is expected to be the least useful."]
[1]

{"sheet":"10","index":13,"n_rows":51,"n_cols":9,"rows_kept":51,"cut":false,"hidden":false,"merged":[]}
[10,"Finished",null,null,null,null,"Navigator",null,"Solution"]
[null,null,null,null,null,null,null,"(a)","Line of business 1 has an increasing standard loss ratio. This indicates the experience"]
["Points","An actuary has created new models to calculate the experience modification factor for two lines of",null,null,null,null,null,null,"rating is identify differences between risks. Since there is no experience plan currently"]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('10'!A1,'Point Grid'!A:A,0))","v":1.5},"business. Given the following:",null,null,null,null,null,null,"in place, and the standard loss ratio of the proposal is flatter than the experience modifications proposed,"]
[null,"   • Line of business 1 (currently does not use experience rating)",null,null,null,null,null,null,"this suggests the model is also correcting for differences between risks."]
[null,null,null,null,null,null,null,null,"Therefore, I would implement the proposal."]
[]
[null,null,null,null,null,null,null,"(b)","Not enough credibility is given to the actual experience because the standard loss ratios"]
[null,null,null,null,null,null,null,null,"are increasing. Increase the credibility given to actual experience."]
[]
[null,null,null,null,null,null,null,"(c)","Do not use the proposed plan for line of business 2. The proposed standard loss ratios"]
[null,null,null,null,null,null,null,null,"are steeper than the current ones and the flatter the ratio the better the plan."]
[null,null,null,null,null,null,null,null,"Also, the current plan has a lower efficiency test statistic than the proposed plan,"]
[null,null,null,null,null,null,null,null,"which indicates the current plan performs better."]
[]
[]
[]
[]
[]
[null,"   • Line of business 2 (currently uses experience rating)"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,"Line of Business 2","Efficiency Test"]
[null,null,"Current Plan",3.4599999999999999E-2]
[null,null,"Proposed Plan",0.11840000000000001]
[]
[null,"For both lines of business, assume that the cost of implementation is negligible."]
[]
["(a)","For line of business 1, evaluate whether the new experience modification should be implemented."]
[{"$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}]
[0.5]
[]
["(b)","For line of business 1, describe a way to improve the results of the experience modification."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A45,'Point Grid'!$C$7:$I$7,0))","v":0.5}]
[0.5]
[]
["(c)","For line of business 2, evaluate whether the proposed plan should be used."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A49,'Point Grid'!$C$7:$I$7,0))","v":0.5}]
[0.5]

{"sheet":"11","index":14,"n_rows":38,"n_cols":14,"rows_kept":38,"cut":false,"hidden":false,"merged":[]}
[11,"Finished",null,null,null,null,"Navigator",null,"Solution"]
[null,null,null,null,null,null,null,"(a)",null,null,null,"Here, Z = 0.75, A = 37,100 and E = 32,000."]
["Points","Consider the following sample of five insurance risks which have been experience rated:"]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('11'!A1,'Point Grid'!A:A,0))","v":3.75},"Risk","Mod","Manual Loss Ratio","Standard Loss Ratio",null,null,null,"So X =",{"$f":"0.75*(D23/32000)+(1-0.75)*1","v":1.1195312500000001}]
[null,1,0.6,0.6,1]
[null,2,0.79,0.8,1.01,null,null,"(b)","The standard loss ratio for risk 3 is its manual loss ratio divided by its experience mod."]
[null,3,"X",0.96,"Y",null,null,null,"i.e. = 0.96 / 1.1195 = 0.8575."]
[null,4,1.1000000000000001,1.1000000000000001,1,null,null,null,"If the experience rating plan is good, it should be close to the other standard loss ratios,"]
[null,5,1.1399999999999999,1.1499999999999999,1.01,null,null,null,"i.e. near 1.00. Since it is materially lower, too much credibility is being placed on the "]
[null,null,null,null,null,null,null,null,"actual experience. Lower the credibility to improve the effectiveness for risk 3."]
[null,"The actual claims for Risk 3 in the experience period are as follows:"]
[null,null,"Claim #","Incurred Loss Amount",null,null,null,"(c)","I choose to place 25% credibility on the actual experience for risk 3."]
[null,null,"001",3200,null,null,null,null,"The new experience mod for Risk 3 is then"]
[null,null,"002",3000,null,null,null,null,"X = 0.25 * (37,100 / 32,000) + (1 - 0.25)*1"]
[null,null,"003",2800,null,null,null,null,{"$f":"0.25*(37100/32000)+(1-0.25)*1","v":1.03984375}]
[null,null,"004",2700]
[null,null,"005",3300,null,null,null,null,"Then the new standard loss ratio is 0.96 / 1.040 = 0.923"]
[null,null,"006",2900,null,null,null,null,"To compare the old and new plans I will use the efficiency test statistic"]
[null,null,"007",10000,null,null,null,null,"Note we assume no other risks are impacted by this recommendation."]
[null,null,"008",3100]
[null,null,"009",3000,null,null,null,null,"Old Plan",null,null,"New Plan"]
[null,null,"010",3100,null,null,null,null,"Manual LR","Standard LR",null,"Manual LR","Standard LR"]
[null,null,"Total",{"$f":"SUM(D13:D22)","v":37100},null,null,null,null,{"$f":"D5","v":0.6},{"$f":"E5","v":1},null,{"$f":"D5","v":0.6},{"$f":"E5","v":1}]
[null,null,null,null,null,null,null,null,{"$f":"D6","v":0.8},1.01,null,{"$f":"D6","v":0.8},1.01]
[null,"• Expected Losses for Risk 3 in the experience period = 32,000",null,null,null,null,null,null,0.96,{"$f":"D7/J4","v":0.85750174459176542},null,0.96,{"$f":"D7/I15","v":0.92321562734785878}]
[null,"• Credibility factor = 0.75",null,null,null,null,null,null,1.1000000000000001,1,null,1.1000000000000001,1]
[null,null,null,null,null,null,null,null,1.1499999999999999,1.01,null,1.1499999999999999,1.01]
["(a)","Calculate the modification factor, X, for Risk 3."]
[{"$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},null,null,null,null,null,null,null,{"$f":"_xlfn.VAR.S(I23:I27)","v":5.0920000000000298E-2},{"$f":"_xlfn.VAR.S(J23:J27)","v":4.3761470696945604E-3},null,{"$f":"_xlfn.VAR.S(L23:L27)","v":5.0920000000000298E-2},{"$f":"_xlfn.VAR.S(M23:M27)","v":1.3627367220208611E-3},"<- Sample variances"]
[0.5,null,null,null,null,null,null,null,"Efficiency:",{"$f":"J29/I29","v":8.5941615665642873E-2},null,"Efficiency:",{"$f":"M29/L29","v":2.6762307973700966E-2}]
[]
["(b)","Provide a recommendation to improve the effectiveness of experience rating for Risk 3 and ",null,null,null,null,null,null,"Efficiency Test = sample variance of Standard LR / sample variance of Manual LR"]
[{"$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":0.5},"briefly justify.",null,null,null,null,null,null,"Since the new plan has a lower efficiency statistic than the old plan, it is an improvement."]
[0.5]
[]
["(c)","Show, quantitatively, that the recommendation in part (b) above improves the plan. Assume no"]
[{"$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":2.75},"other risks are impacted by the recommendation."]
[2.75]

{"sheet":"12","index":15,"n_rows":47,"n_cols":9,"rows_kept":47,"cut":false,"hidden":false,"merged":[]}
[12,"Finished",null,null,null,null,"Navigator",null,"Solution"]
[null,null,null,null,null,null,null,"(a)","Subset B has a significantly different loss distribution than Subset A even though"]
["Points","An actuary is assessing the current Table M underlying the pricing of a book of business. All policies",null,null,null,null,null,null,"they have the same expected loss. The variance of B is greater than that of A which"]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('12'!A1,'Point Grid'!A:A,0))","v":2},"within the book of business have identical expected losses and are expected to have exactly one",null,null,null,null,null,null,"means B should have a higher insurance charge than A for the same entry ratio."]
[null,"claim during the policy period. The actuary divides the book of business into two subsets of ",null,null,null,null,null,null,"Combining A and B to create one table will underprice B and overprice A."]
[null,"policies, Subset A and Subset B, and finds the following distributions of aggregate losses:"]
[null,null,null,null,null,null,null,"(b)","φ(x) = average amount above x divided by the expected amount."]
[null,null,null,null,null,null,null,null,"The expected loss for subset B is (10,000 * 6 + 40,000 * 3 + 80,000 * 1) / 10 = 26,000"]
[]
[null,null,null,null,null,null,null,"(i)","φ(10,000) = [ (1 * (80,000 - 10,0000) + 3 * (40,000 - 10,000) )/10 ] / 26,000"]
[null,null,null,null,null,null,null,null,{"$f":"(1*(80000-10000)+3*(40000-10000))/10/26000","v":0.61538461538461542}]
[]
[null,null,null,null,null,null,null,"(ii)","φ(40,000) = [ (1 * (80,000 - 40,0000) )/10 ] / 26,000"]
[null,null,null,null,null,null,null,null,{"$f":"(80000-40000)/10/26000","v":0.15384615384615385}]
[]
[null,null,null,null,null,null,null,"(iii)","φ(80,000) = 0 as no claims exceed 80,000"]
[]
[null,null,null,null,null,null,null,"(c)","1.) Does the different line of business have the same expected risk size?"]
[null,null,null,null,null,null,null,null,"2.) The Table M calculated in part (b) only uses 10 risks so likely is not credible."]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
["(a)","Discuss why the current Table M may not be appropriate for this book of business."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A35,'Point Grid'!$C$7:$I$7,0))","v":0.5}]
[0.5]
[]
["(b)","Using the observations in Subset B, calculate the insurance charges associated with the following"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A39,'Point Grid'!$C$7:$I$7,0))","v":1},"claim sizes:"]
[1,"   i. 10,000"]
[null,"  ii. 40,000"]
[null," iii. 80,000"]
[]
["(c)","Briefly describe two considerations when using the Table M calculated in part (b) above to support"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A45,'Point Grid'!$C$7:$I$7,0))","v":0.5},"the creation of a Table M for a different line of insurance."]
[0.5]

{"sheet":"13","index":16,"n_rows":23,"n_cols":9,"rows_kept":23,"cut":false,"hidden":false,"merged":[]}
[13,"Finished",null,"Formula Sheet",null,null,"Navigator",null,"Solution"]
[null,null,null,null,null,null,null,"(a)","Let S be the aggregate loss variable. Then"]
["Points","For a given policy that provides first dollar coverage, there is a 50% probability that no claims will",null,null,null,null,null,null,"Use the formula sheet and given information to find the parameters of the shifted "]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('13'!A1,'Point Grid'!A:A,0))","v":2},"occur during the policy period. Given that a claim does occur, the frequency distribution is shown",null,null,null,null,null,null,"Pareto distribution."]
[null,"below:"]
[null,"Number of Claims","Probability"]
[null,1,0.7]
[null,3,0.2]
[null,5,0.1]
[null,null,null,null,null,null,null,null,"Substitute the second equation into the first to get"]
[null,"The claim-size variable X for this policy follows a shifted Pareto distribution with the following",null,null,null,null,null,null,"So α = 1.5"]
[null,"characteristics:",null,null,null,null,null,null,"Then β = 3000(α - 1) = 1,500"]
[null,"   • eX(6,000) = 15,000"]
[null,"   • E[X] = 3,000",null,null,null,null,null,null,"Then"]
[null,"   • E[X; 4,000] = 1,433.30"]
[null,null,null,null,null,null,null,null,"Next, E[N] = 0.5 * 0 + 0.5 * ( 0.7 * 1 + 0.2 * 2 + 0.1 * 5) = 0.9"]
["(a)","Calculate the expected aggregate loss in the layer 1,000 excess of 4,000 for this policy."]
[{"$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.5},null,null,null,null,null,null,null,"Lastly, plug the above into the equation for E[S] to get"]
[1.5,null,null,null,null,null,null,null,{"$f":"0.9*(1558.85-1433.3)","v":112.99499999999996}]
[]
["(b)","Explain one difficulty that can arise when trying to fit an aggregate loss distribution function to",null,null,null,null,null,"(b)","Excess losses are usually sparse and may not be sufficiently credible to consistently"]
[{"$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":0.5},"losses in an excess layer.",null,null,null,null,null,null,"fit a distribution without having some jump discontinuities."]
[0.5]

{"sheet":"14","index":17,"n_rows":22,"n_cols":9,"rows_kept":22,"cut":false,"hidden":false,"merged":[]}
[14,"Finished",null,null,null,null,"Navigator",null,"Solution"]
[null,null,null,null,null,null,null,"(a)"]
["Points","An insured is written under a retrospective rating plan with a minimum and a maximum premium",null,null,null,null,null,null,"Here, e = 20,000 and c = 1.07 and E[A] = $825,000"]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('14'!A1,'Point Grid'!A:A,0))","v":2},"with the following parameters:"]
[null,"Loss Conversion Factor",1.07,null,null,null,null,null,"I is the net insurance charge,"]
[null,"Expected Loss",825000]
[null,"Expenses",20000]
[null,"φ(rG)",0.4,null,null,null,null,null,"So ψ(0.5) = 0.8 + 0.5 - 1 = 0.3"]
[null,"φ(rH)",0.8,null,null,null,null,null,"Thus, I = (0.4 - 0.3) * 825,000 = 82,500"]
[null,"rG",1.21]
[null,"rH",0.5,null,null,null,null,null,"Putting this all together yields the basic premium as"]
[null,null,null,null,null,null,null,null,"B = 20,000 - 0.07 * 825,000 + 1.07 * 82,500"]
["(a)","Calculate the basic premium.",null,null,null,null,null,null,{"$f":"C7-(C5-1)*C6+C5*82500","v":50524.999999999949}]
[{"$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":1}]
[1,null,null,null,null,null,null,"(b)","• There is an overlap between the per-occurrence limit and the aggregate limit set"]
[null,null,null,null,null,null,null,null,"by the maximum premium, so it is not appropriate to load the full amount of"]
["(b)","Upon renewal, the insured requests that a per-occurrence claim limit of $200,000 be added to the ",null,null,null,null,null,null,"$400,000 into the premium. This results in overpricing."]
[{"$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},"current policy. The pricing actuary estimates that, on average, the insured will have $400,000 of losses",null,null,null,null,null,null,"• The actuary should use a limited Table M or a Table L instead."]
[1,"that exceed this per-occurrence limit during the next policy period. To calculate the expected increase"]
[null,"to the basic premium, the actuary loads the $400,000 for expenses and profit."]
[]
[null,"Fully discuss the appropriateness of the actuary's methodology."]

{"sheet":"15","index":18,"n_rows":39,"n_cols":10,"rows_kept":39,"cut":false,"hidden":false,"merged":[[0,5,0,6]]}
[15,"Finished",null,"Formula Sheet",null,"Navigator",null,null,"Solution"]
[null,null,null,null,null,null,null,"(a)","From the formula sheet, the excess severity function is"]
["Points","An actuary is pricing a retrospectively rated policy with a per-occurrence limit of 20,000 and no",null,null,null,null,null,null,"We equate this with the fitted regression line to"]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('15'!A1,'Point Grid'!A:A,0))","v":3.5},"minimum or maximum ratable loss.",null,null,null,null,null,null,"determine the shifted Pareto parameters."]
[]
[null,"Using claim data from a group of similar policies, the actuary fit the following regression line to the",null,null,null,null,null,null,"16,112 = β / (α - 1)"]
[null,"to the excess severity to estimate losses using the shifted Pareto distribution. The fitted regression",null,null,null,null,null,null,"0.4467 = 1 / (α - 1)"]
[null,"function is y = 0.4467x + 16112 with R2 = 0.9826.",null,null,null,null,null,null,"So α = 3.24 and β = 36,069"]
[]
[null,null,null,null,null,null,null,null,"Then the unlimited expected severity is E[A] = 16,112"]
[null,null,null,null,null,null,null,null,"The expected severity limited to 20,000 is E[AD] = 10,108 by the formula sheet."]
[null,null,null,null,null,null,null,null,"So the expected excess severity is 16,112 - 10,108 = 6,004"]
[]
[null,null,null,null,null,null,null,null,"Multiply this by the expected number of claims which is E[N] = 15."]
[null,null,null,null,null,null,null,null,"So the expected aggregate excess is 15 * 6,004 = 90,060"]
[]
[null,null,null,null,null,null,null,null,"Then the basic premium = fixed expenses + (expected excess)*(1 + LAE % + UW Profit %)"]
[null,null,null,null,null,null,null,null,{"$f":"E24+90060*(1+E21 +E25)","v":154469.6}]
[]
[null,"Given the following:",null,null,null,"Included in:",null,"(b)","The expected limited loss is the expected limited severity multiplied by the expected claim count."]
[null,"Loss adjustment expenses as a percentage of loss",null,null,0.1,"Loss conversion factor",null,null,"Using the result in part (a) this is 15 * 10,108 = 151,620"]
[null,"Commission as a percentage of retrospective premium",null,null,0.09,"Tax multiplier",null,null,"Actual loss",{"$f":"151620/2","v":75810}]
[null,"Premium tax as a percentage of retrospective premium",null,null,0.08,"Tax multiplier"]
[null,"Fixed overhead expenses",null,null,50000,"Basic premium",null,null,"Plug into the retrospective rating formula"]
[null,"Underwriting profit as a percentage of expected excess loss",null,null,0.06,"Basic premium",null,null,"Here L = 75,810 and B = 154,469.60 and c = 1.1"]
[null,null,null,null,null,null,null,null,"T is the tax multiplier, it equals 1 / (1 - 9% - 8%)"]
[null,"   • The number of claims for ground up losses is Poisson distributed with λ = 15."]
[null,null,null,null,null,null,null,null,"So the retrospective premium is"]
["(a)","Calculate the basic premium.",null,null,null,null,null,null,{"$f":"(I18+(1+E21)*J22)*(1/(1-E22-E23))","v":286579.03614457831}]
[{"$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":2}]
[2,null,null,null,null,null,null,"(c)","The R2 value is high and the fitted line is close to the observed values indicating a good fit."]
[null,null,null,null,null,null,null,null,"The regression line is linear and upward sloping which matches the usual shape of a"]
["(b)","Assuming that actual limited losses equal half the expected limited losses, calculate the ",null,null,null,null,null,null,"Pareto excess severity distribution."]
[{"$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":1},"retrospective premium.",null,null,null,null,null,null,"Note the slope of the regression line is sensitive to the size of the largest claims so"]
[1,null,null,null,null,null,null,null,"minor variations in the largest claims could significantly change the distribution parameters."]
[]
["(c)","Assess the actuary's decision to use the shifted Pareto distribution to estimate excess severity."]
[{"$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.5}]
[0.5]

{"sheet":"16","index":19,"n_rows":31,"n_cols":13,"rows_kept":31,"cut":false,"hidden":false,"merged":[[3,3,5,3],[3,4,5,4],[3,2,5,2],[0,5,0,6]]}
[16,"Finished",null,null,null,"Navigator",null,null,"Solution"]
[null,null,null,null,null,null,null,"(a)","The entry ratio for a limited Table M is the actual limited loss / expected limited loss."]
["Points","The loss experience of five similarly sized risks is shown in the table below:",null,null,null,null,null,null,"Risk","Entry Ratio",null,"For the expected limited loss, use the average over all risks"]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('16'!A1,'Point Grid'!A:A,0))","v":3},null,"Risk","Aggregate Unlimited Loss","Aggregate Limited Loss",null,null,null,1,{"$f":"E7/$L$4","v":0.25},null,{"$f":"E12/5","v":120000}]
[null,null,null,null,null,null,null,null,2,{"$f":"E8/$L$4","v":0.5}]
[null,null,null,null,null,null,null,null,3,0.75]
[null,null,1,30000,30000,null,null,null,4,0.5]
[null,null,2,95000,60000,null,null,null,5,3]
[null,null,3,110000,90000]
[null,null,4,160000,60000,null,null,null,"Limited Table M"]
[null,null,5,415000,360000,null,null,null,"Entry Ratio","# Risks","# Risks Over","% Risks Over","φ(r)"]
[null,null,"Total",{"$f":"SUM(D7:D11)","v":810000},{"$f":"SUM(E7:E11)","v":600000},null,null,null,0,{"$f":"COUNTIF($J$4:$J$8,\"=\"&I12)","v":0},{"$f":"COUNTIF($J$4:$J$8,\">\"&I12)","v":5},{"$f":"K12/COUNT($I$4:$I$8)","v":1},{"$f":"M13+(I13-I12)*L12","v":1}]
[null,"Limited losses have a per-occurrence limit of 50,000 applied.",null,null,null,null,null,null,0.25,{"$f":"COUNTIF($J$4:$J$8,\"=\"&I13)","v":1},{"$f":"COUNTIF($J$4:$J$8,\">\"&I13)","v":4},{"$f":"K13/COUNT($I$4:$I$8)","v":0.8},0.75]
[null,null,null,null,null,null,null,null,0.5,2,2,0.4,0.55000000000000004]
["(a)","Construct a Limited Loss Table M based on the experience above, displaying entry ratios",null,null,null,null,null,null,0.75,1,1,0.2,0.45]
[{"$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":1.5},"ranging from 0 to 3 in increments of 0.5 and the corresponding insurance charges.",null,null,null,null,null,null,1,0,1,0.2,0.4]
[1.5,null,null,null,null,null,null,null,1.5,0,1,0.2,0.30000000000000004]
[null,null,null,null,null,null,null,null,2,0,1,0.2,0.2]
["(b)","An insured similar to the risks above is evaluating three potential policies:",null,null,null,null,null,null,2.5,0,1,0.2,{"$f":"M20+(I20-I19)*L19","v":0.1}]
[{"$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},"  1. A guaranteed cost policy",null,null,null,null,null,null,3,1,0,0,0]
[1.5,"  2. A large dollar deductible policy with a 50,000 per-occurrence limit"]
[null,"  3. A retrospectively rated policy with an underlying 200,000 aggregate limit and no",null,null,null,null,null,null,"Make sure you add rows that correspond to observed entry ratios."]
[null,"       per-occurrence limit."]
[null,null,null,null,null,null,null,"(b)","1.) Advantage: Stable premium known at policy inception. "]
[null,"Given the following:",null,null,null,null,null,null,"      Disadvantage: No reduction in premium for good experience."]
[null,"   • The insured would like to minimize cost",null,null,null,null,null,null,"2.) Advantage: Lower cost compared to GCP since the insured is responsible for losses below the deductible"]
[null,"   • Maintaining a stable, predictable cash flow is a priority for the insured",null,null,null,null,null,null,"      Disadvantage: Potential to pay a lot if there are a lot of claims below the deductible."]
[null,"   • The insured has recently implement a successful claim severity reduction program.",null,null,null,null,null,null,"3.) Advantage: The reduced severity from their program will likely lead to lower actual losses which"]
[null,null,null,null,null,null,null,null,"     will decrease premiums."]
[null,"Briefly discuss one advantage and one disadvantage of each of the three policies from ",null,null,null,null,null,null,"     Disadvantage: Potential to pay a lot if their loss experience is poor."]
[null,"the perspective of this insured."]

{"sheet":"17","index":20,"n_rows":32,"n_cols":14,"rows_kept":32,"cut":false,"hidden":false,"merged":[[13,2,15,2],[13,3,15,3],[13,1,15,1],[0,5,0,6]]}
[17,"Finished",null,null,null,"Navigator",null,null,"Solution"]
[null,null,null,null,null,null,null,"(a)","Technical Ratio = Expected Loss Ratio + Expected Commission"]
["Points","An insurer is deciding on a proportional reinsurance strategy for the upcoming two years."]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('17'!A1,'Point Grid'!A:A,0))","v":3},"A reinsurer proposed the following sliding-scale commission with a carry-forward ",null,null,null,null,null,null,"Year 1"]
[null,"provision. The contract will be identical for the second year.",null,null,null,null,null,null,"Range","Loss Ratio","Commission","Probability"]
[null,"Minimum Commission:",null,"12.5% at an 80% loss ratio",null,null,null,null,"0% - 50%",{"$f":"C17","v":0.43},0.35,{"$f":"D17","v":0.15}]
[null,"Sliding 1:1 to",null,"27.5% at a 65% loss ratio",null,null,null,null,"50% - 60%",{"$f":"C18","v":0.56999999999999995},{"$f":"35%-0.5*(J7-50%)","v":0.315},{"$f":"D18","v":0.25}]
[null,"Sliding 0.5:1 to a Maximum",null,"35% at a 50% loss ratio",null,null,null,null,"60% - 80%",{"$f":"C19","v":0.68},{"$f":"27.5%-1*(J8-65%)","v":0.245},0.45]
[null,null,null,null,null,null,null,null,"Over 80%",0.85,0.125,0.15]
[null,"Assume the insurer places no value on the carry-forward provision after the second year, and"]
[null,"ignore the time value of money (i.e., assume a 0% interest rate).",null,null,null,null,null,null,"Year 1 Technical Ratio = Sum [(Loss Ratio + Commission)*Probability]"]
[null,null,null,null,null,null,null,null,{"$f":"SUMPRODUCT(J6:J9,L6:L9)+SUMPRODUCT(K6:K9,L6:L9)","v":0.90075000000000005}]
[null,"The following table details the expected loss distribution of the underlying business:"]
[null,"Range of Loss Ratios","Average Loss Ratio in Range","Probability of Being in Range",null,null,null,null,"Year 2"]
[null,null,null,null,null,null,null,null,"Range","Loss Ratio","Commission LR < 80%","New LR","Commission LR >= 80%","Probability"]
[null,null,null,null,null,null,null,null,"0% - 50%",{"$f":"C17","v":0.43},{"$f":"K6","v":0.35},{"$f":"J16+(85%-80%)","v":0.47999999999999993},0.35,{"$f":"D17","v":0.15}]
[null,"0% – 50%",0.43,0.15,null,null,null,null,"50% - 60%",{"$f":"C18","v":0.56999999999999995},{"$f":"K7","v":0.315},{"$f":"J17+(85%-80%)","v":0.61999999999999988},{"$f":"35% -0.5*(L17-50%)","v":0.29000000000000004},{"$f":"D18","v":0.25}]
[null,"50% – 60%",0.56999999999999995,0.25,null,null,null,null,"60% - 80%",0.68,0.245,0.73,{"$f":"27.5% - 1*(73%-65%)","v":0.19500000000000006},0.45]
[null,"60% – 80%",0.68,0.45,null,null,null,null,"Over 80%",0.85,0.125,0.89999999999999991,0.125,0.15]
[null,"80% or above",0.85,0.15]
[null,null,null,null,null,null,null,null,"The carryforward provision increases the loss ratio by the amount which the "]
["(a)","Calculate the reinsurer's expected technical ratio for each year if the insurer buys the ",null,null,null,null,null,null,"actual loss ratio exceeds the maximum (in this case 80%)."]
[{"$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":3},"sliding-scale commission contract for both years.",null,null,null,null,null,null,"Split into two cases, the first when the actual LR is less than 80%. This happens 85% of the time."]
[3,null,null,null,null,null,null,null,"The second when the actual LR is greater than 80%. This happens 15% of the time."]
[]
[null,null,null,null,null,null,null,null,"Expected Commission"]
[null,null,null,null,null,null,null,null,"1st Case",{"$f":"SUMPRODUCT(K16:K19,N16:N19)","v":0.26024999999999998}]
[null,null,null,null,null,null,null,null,"2nd Case",{"$f":"SUMPRODUCT(M16:M19,N16:N19)","v":0.23150000000000004}]
[null,null,null,null,null,null,null,null,"Wtd Avg",{"$f":"SUM(D17:D19)*J27+D20*J28","v":0.25593750000000004}]
[]
[null,null,null,null,null,null,null,null,"Year 2 Technical Ratio "]
[null,null,null,null,null,null,null,null,{"$f":"SUMPRODUCT(J16:J19,N16:N19)+J29","v":0.89643750000000011}]

{"sheet":"18","index":21,"n_rows":67,"n_cols":15,"rows_kept":67,"cut":false,"hidden":false,"merged":[[17,4,19,4],[17,1,19,1],[17,2,19,2],[17,3,19,3]]}
[18,"Finished",null,null,null,"Navigator",null,"Solution"]
[null,null,null,null,null,null,"(a)","Use a volume weighted average loss cost for the layer"]
["Points","A property insurer has an existing Quota Share reinsurance treaty in place. The insurer would",null,null,null,null,null,{"$f":"SUMPRODUCT(C21:C25,E21:E25)/SUM(C21:C25)","v":0.33399999999999996}]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('18'!A1,'Point Grid'!A:A,0))","v":5.5},"like to further reduce its net loss exposure by exploring a proposed Property Per Risk Excess"]
[null,"reinsurance treaty. Given the following:",null,null,null,null,"(b)","Loss cost = Exposure * (Pure Risk Premium)"]
[null,"Existing Quota Share Reinsurance Treaty",null,null,null,null,null,"Exposure = G(1,000,000/MPL) - G(500,000/MPL) where MPL is the maximum possible loss."]
[null,"   • 25% Quota Share"]
[null,"   • 15% Ceding Commission",null,null,null,null,null,"MPL = 1,000,000"]
[null,null,null,null,null,null,null,"So exposure = G(100%) - G(50%)."]
[null,"Proposed Property Per Risk Excess Reinsurance Treaty",null,null,null,null,null,"G(100%) = 1"]
[null,"   • Subject premium of $100 million net of the Quota Share reinsurance treaty",null,null,null,null,null,"G(50%) = (1 - 0.32428^0.5) / (1 - 0.32428) = ",null,null,null,{"$f":"(1-0.32428^0.5)/(1-0.32428)","v":0.63716349872140743}]
[null,"   • Cover net losses in excess of $500,000, up to a limit of $500,000"]
[null,"   • The Quota Share reinsurance treaty will inure to the benefit of this treaty",null,null,null,null,null,"Thus, exposure = ",null,{"$f":"1-L11","v":0.36283650127859257}]
[null,"   • Ceded Loss Ratio for this Excess layer is estimated to be 90%"]
[null,null,null,null,null,null,null,"Pure risk premium = premium weighted trended ultimate subject loss ratio"]
[null,"The table below illustrates the insurer's historical experience data net of the Quota Share that",null,null,null,null,null,{"$f":"SUMPRODUCT(D21:D25,C21:C25)/SUM(C21:C25)","v":0.8}]
[null,"is subject to the proposed Property Per Risk Excess layer:"]
[null,"Historical Accident Year","On Level Subject Earned Premium ($ millions)","On Level Trended Ultimate Subject Loss Ratio","On Level Trended Ultimate Layer Loss Cost",null,null,"Loss cost =",{"$f":"H16*J13","v":0.29026920102287407}]
[]
[null,null,null,null,null,null,"(c)","Loss cost = Z *(experience loss cost) + (1 - Z)*(exposure loss cost)"]
[null,2014,100,0.75,0.25,null,null,"Z = 80%"]
[null,2015,120,0.75,0.3]
[null,2016,150,0.9,0.45,null,null,"Experience loss cost = premium weighted layer loss cost"]
[null,2017,80,0.75,0.24,null,null,{"$f":"SUMPRODUCT(C21:C25,E21:E25)/SUM(C21:C25)","v":0.33399999999999996}]
[null,2018,100,0.8,0.36]
[null,null,null,null,null,null,null,"Exposure loss cost was found in part (b)"]
["(a)","Calculate the loss cost of the Property Per Risk Excess layer using all 5 years of historical"]
[{"$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":0.5},"experience data provided.",null,null,null,null,null,"credibility weighted loss cost =",null,null,{"$f":"0.8*H24+(1-0.8)*I18","v":0.32525384020457482}]
[0.5]
[null,null,null,null,null,null,"(d)","Expected Subject Loss Ratio = ",null,null,{"$f":"SUMPRODUCT(C21:C25,D21:D25)/SUM(C21:C25)","v":0.8}]
["(b)","Assume that the following exposure curve definition applies to the Property Per Risk Excess",null,null,null,null,null,"So the expected loss net of the quota share is 80 million."]
[{"$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":1},"layer:"]
[1,null,null,null,null,null,null,{"$f":"\"From part (c) the ceded loss cost is \"&TEXT(K28,\"#.00%\")","v":"From part (c) the ceded loss cost is 32.53%"}]
[null,"where G(x) represents the ratio of pure risk premiums retained by the insurer, and x represents",null,null,null,null,null,"Since the ceded loss ratio is 90% this means the ceded premium per $ of premium is"]
[null,"the ground up loss normalized to the maximum possible loss of $1 million.",null,null,null,null,null,{"$f":"TEXT(K28,\"0.00%\") &\" / 90% = \" &TEXT(K28/90%,\"0.00%\")","v":"32.53% / 90% = 36.14%"}]
[null,null,null,null,null,null,null,"So with an initial $100 million of subject premium the ceded premium is"]
[null,"Calculate the loss cost of the Property Per Risk Excess layer retained by the reinsurer based on",null,null,null,null,null,{"$f":"100*10^6*K28/0.9","v":36139315.578286089}]
[null,"the exposure curve.",null,null,null,null,null,"and the ceded loss is "]
[null,null,null,null,null,null,null,{"$f":"K28*100*10^6","v":32525384.02045748}]
["(c)","Calculate the loss cost of the Property Per Risk Excess layer using a blend of the experience"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A40,'Point Grid'!$C$7:$I$7,0))","v":0.5},"loss cost and the exposure loss cost based on a credibility weight of 80%.",null,null,null,null,null,"Thus Profit = subject premium - (expected loss - ceded loss) - ceded premium - expenses"]
[0.5,null,null,null,null,null,null,{"$f":"100*10^6 -(80*10^6 - H39) - H37 -15*10^6","v":1386068.4421713874}]
[]
["(d)","The insurer's historical gross expenses are estimated to be 15% of historical gross premiums.",null,null,null,null,"(e)","The subject premium prior to the quota share is net subject premium / 0.75"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A44,'Point Grid'!$C$7:$I$7,0))","v":2},"Calculate the insurer's expected net underwriting profit after application of both the existing",null,null,null,null,null,"The quota share ceded premium is 25% of the above."]
[2,"Quota Share reinsurance treaty and the proposed Property Per Risk Excess reinsurance treaty.",null,null,null,null,null,"Quota share ceded loss is the ultimate loss ratio multiplied by the ceded premium"]
[null,null,null,null,null,null,null,"Ceding Commission is 15% of the ceded premium"]
[null,"The insurer is also exploring the following proposed modifications to the Quota Share",null,null,null,null,null,"The profit commission is max(0, ceded premium - ceded loss - ceding commission - profit margin)"]
[null,"reinsurance treaty:",null,null,null,null,null,"Working in $ millions we have"]
[null,"   • Option 1: Profit Commission equal to 100% of reinsurer profit above a 5% reinsurer margin",null,null,null,null,null,null,"On-Level","Trd. Ult","Ceded","Ceded","Ceding","Profit","Profit"]
[null,"   • Option 2: 20% Ceding Commission",null,null,null,null,null,"AY","Subject EP","Subject LR","Premium","Loss","Commission","Margin","Commission"]
[null,null,null,null,null,null,null,2014,{"$f":"C21","v":100},{"$f":"D21","v":0.75},{"$f":"I52/0.75*0.25","v":33.333333333333336},{"$f":"I52/0.75*J52*0.25","v":25},{"$f":"15%*K52","v":5},{"$f":"5%*K52","v":1.666666666666667},{"$f":"MAX(0,K52-L52-M52-N52)","v":1.6666666666666687}]
["(e)","Calculate the 5-year weighted average ratio of profit commission to ceded premium for the ",null,null,null,null,null,2015,{"$f":"C22","v":120},{"$f":"D22","v":0.75},{"$f":"I53/0.75*0.25","v":40},{"$f":"I53/0.75*J53*0.25","v":30},{"$f":"15%*K53","v":6},{"$f":"5%*K53","v":2},{"$f":"MAX(0,K53-L53-M53-N53)","v":2}]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A53,'Point Grid'!$C$7:$I$7,0))","v":1},"Quota Share reinsurance treaty using the historical experience data provided.",null,null,null,null,null,2016,150,0.9,{"$f":"I54/0.75*0.25","v":50},45,7.5,2.5,0]
[1,null,null,null,null,null,null,2017,80,0.75,{"$f":"I55/0.75*0.25","v":26.666666666666668},20,4,1.3333333333333335,1.3333333333333344]
[null,null,null,null,null,null,null,2018,100,0.8,{"$f":"I56/0.75*0.25","v":33.333333333333336},26.666666666666671,5,1.666666666666667,0]
["(f)","Briefly describe one advantage and one disadvantage of implementing the proposed profit",null,null,null,null,null,"TOTAL",{"$f":"SUM(I52:I56)","v":550},null,{"$f":"SUM(K52:K56)","v":183.33333333333334},{"$f":"SUM(L52:L56)","v":146.66666666666669},{"$f":"SUM(M52:M56)","v":27.5},{"$f":"SUM(N52:N56)","v":9.1666666666666679},{"$f":"SUM(O52:O56)","v":5.0000000000000036}]
[{"$f":"INDEX('Point Grid'!$C$8:$I$26,MATCH($A$1,'Point Grid'!$A$8:$A$26,0),MATCH(A57,'Point Grid'!$C$7:$I$7,0))","v":0.5},"commission versus the higher ceding commission from the insurer's perspective."]
[0.5,null,null,null,null,null,null,"Ratio of Profit Commission to Ceded Premium = ",null,null,null,{"$f":"O57/K57","v":2.7272727272727292E-2}]
[]
[null,null,null,null,null,null,"(f)","Advantage: Proposed profit commission allows the insurer to benefit from favorable results"]
[null,null,null,null,null,null,null,"with good underwriting performance."]
[]
[null,null,null,null,null,null,null,"Disadvantage: The expected profit commission is 2.73% of ceded premium. "]
[null,null,null,null,null,null,null,"Adding this to the 15% ceding commission for Option 1 results in a lower figure than Option 2."]
[null,null,null,null,null,null,null,"Further, the ceding commission is guaranteed while the profit commission isn't, making Option 2 "]
[null,null,null,null,null,null,null,"more appealing."]

{"sheet":"19","index":22,"n_rows":48,"n_cols":9,"rows_kept":48,"cut":false,"hidden":false,"merged":[[0,5,0,6],[29,1,30,1],[32,5,33,5],[34,3,35,3],[34,4,35,4],[34,5,35,5],[29,3,30,3],[29,4,30,4],[29,5,30,5],[32,1,33,1],[34,1,35,1],[32,2,33,2],[34,2,35,2],[32,3,33,3],[32,4,33,4]]}
[19,"Finished",null,null,null,"Navigator",null,null,"Solution"]
[null,null,null,null,null,null,null,"(a)","Using the graph, 750 million corresponds to a return period of 25 years."]
["Points","An insurance company is deciding between three reinsurance treaty options from the same",null,null,null,null,null,null,"This corresponds to a 1/25 = 0.04 = 4% probability."]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('19'!A1,'Point Grid'!A:A,0))","v":1.75},"reinsurance company based on the following output from a catastrophe model:"]
[null,null,null,null,null,null,null,"(b)","Option 1 is the Quota Share because it has the same shape as the gross loss but"]
[null,null,null,null,null,null,null,null,"scaled by a percentage. It has the same coefficient of variation for both the"]
[null,null,null,null,null,null,null,null,"retained and ceded losses which is what we expect when a fixed percentage is "]
[null,null,null,null,null,null,null,null,"ceded."]
[]
[null,null,null,null,null,null,null,null,"The Quota Share treaty at 25 years is 675 million retained compared to 750 million"]
[null,null,null,null,null,null,null,null,"on a gross basis. Therefore the Quota Share percentage is (750 - 675) / 750 = 10%"]
[]
[null,null,null,null,null,null,null,"(c)","Option 3 cedes the more in the tail of the risk which has greater volatility."]
[null,null,null,null,null,null,null,null,"This means there is more uncertainty in the amounts covered by the reinsurance,"]
[null,null,null,null,null,null,null,null,"so the reinsurer will need more capital to support the potential losses. They"]
[null,null,null,null,null,null,null,null,"will charge a higher risk load or profit margin for retaining that capital, making "]
[null,null,null,null,null,null,null,null,"Option 3 more expensive."]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"Insurance Company","Gross","Option 1","Option 2","Option 3"]
[null,null,"(No Reinsurance)"]
[null,"Retained AAL","$500 million","$450 million","$486 million","$488 million"]
[null,"Coefficient of Variation of Retained Loss",0.25,0.25,0.24,0.22]
[]
[null,"Coefficient of Variation of Ceded Loss","N/A",0.25,0.76,1.69]
[]
[]
["(a)","Calculate the probability that the insurance company retains more than $750 million of loss on"]
[{"$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":0.5},"a gross basis."]
[0.5]
[]
["(b)","One of the options represents a Quota Share reinsurance treaty. Justify which of the three options "]
[{"$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":0.75},"it is, and state the Quota Share percentage."]
[0.75]
[]
["(c)","Describe a reason why the reinsurance premium for Option 3 may be higher compared to Option 1."]
[{"$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":0.5}]
[0.5]