{"text_format":"1","workbook":"xlsx","n_sheets":25,"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","20","21"],"not_rendered":{"charts":1,"images":5,"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":32,"n_cols":19,"rows_kept":32,"cut":false,"hidden":false,"merged":[[5,1,6,1]]}
[]
[]
[]
[]
["2016 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.75},2.75,null,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":2.75},"","","","","",""]
[2,{"$f":"SUM(C9:I9)","v":2},0.5,0.5,1,null,null,null,null,{"$f":"INDIRECT(\"'\"&A9&\"'!B1\")","v":"Finished"},null,{"$f":"A9","v":2},0.5,0.5,1,"","","",""]
[3,2,1,1,null,null,null,null,null,"Finished",null,3,1,1,"","","","",""]
[4,3,1.5,1,0.5,null,null,null,null,"Finished",null,4,1.5,1,0.5,"","","",""]
[5,2.25,1.75,0.5,null,null,null,null,null,"Finished",null,5,1.75,0.5,"","","","",""]
[6,2.5,0.75,0.75,1,null,null,null,null,"Finished",null,6,0.75,0.75,1,"","","",""]
[7,1.5,1,0.25,0.25,null,null,null,null,"Finished",null,7,1,0.25,0.25,"","","",""]
[8,2.75,1.25,1.5,null,null,null,null,null,"Finished",null,8,1.25,1.5,"","","","",""]
[9,1.5,0.5,0.5,0.5,null,null,null,null,"Finished",null,9,0.5,0.5,0.5,"","","",""]
[10,2.5,2.5,null,null,null,null,null,null,"Finished",null,10,2.5,"","","","","",""]
[11,3.75,3,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":3},0.75,"","","","",""]
[12,2.5,1.5,0.25,0.75,null,null,null,null,"Finished",null,12,1.5,0.25,0.75,"","","",""]
[13,2.5,1.25,1.25,null,null,null,null,null,"Finished",null,13,1.25,1.25,"","","","",""]
[14,1.75,0.5,0.5,0.75,null,null,null,null,"Finished",null,14,0.5,0.5,0.75,"","","",""]
[15,1.75,1.25,0.5,null,null,null,null,null,"Finished",null,15,1.25,0.5,"","","","",""]
[16,3.75,2.75,1,null,null,null,null,null,"Finished",null,16,2.75,1,"","","","",""]
[17,2.5,1,1,0.5,null,null,null,null,"Finished",null,17,1,1,0.5,"","","",""]
[18,{"$f":"SUM(C25:I25)","v":2},1,1,null,null,null,null,null,"Finished",null,{"$f":"A25","v":18},1,1,"","","","",""]
[19,3,3,null,null,null,null,null,null,"Finished",null,19,3,"","","","","",""]
[20,3,0.75,2.25,null,null,null,null,null,"Finished",null,20,0.75,2.25,"","","","",""]
[21,4,4,null,null,null,null,null,null,"Finished",null,21,4,"","","","","",""]
[]
["Total",{"$f":"SUM(B8:B28)","v":53.25},null,null,null,null,null,null,null,null,null,"TOTAL",{"$f":"IF(SUM(IF(M8:S28=\"\",0,1))=0,\"\",SUM(M8:S28))","v":53.25},null,"Percentage:",{"$f":"IF(M30=\"\",\"\",M30/B30)","v":1}]
[]
[null,null,null,null,null,null,null,null,null,null,null,"Passing Score:",37.25]

{"sheet":"Navigator","index":3,"n_rows":29,"n_cols":8,"rows_kept":29,"cut":false,"hidden":false,"merged":[[26,0,26,6],[27,0,27,6],[28,0,28,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 -- Bailey & Simon -- Individual Risk Variation -- 2.75 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J8","v":"Finished"}]
["Q2 -- Robertson -- Clustering Theory -- 2.00 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J9","v":"Finished"}]
["Q3 -- ASOP 12 -- Application -- 2.00 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J10","v":"Finished"}]
["Q4 -- Goldburd (GLM) -- Model Selection -- 3.00 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J11","v":"Finished"}]
["Q5 -- Goldburd (GLM) -- Lorenz Curves & Gini Coefficients -- 2.25 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J12","v":"Finished"}]
["Q6 -- Goldburd (GLM) -- Renewal Models --2.50 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J13","v":"Finished"}]
["Q7 -- Goldburd (GLM) -- AIC & BIC -- 1.50 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J14","v":"Finished"}]
["Q8 -- Bahnemann -- Adverse Selection in ILFs -- 2.75 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J15","v":"Finished"}]
["Q9 -- Fisher -- Experience Modifications -- 1.50 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J16","v":"Finished"}]
["Q10 -- NCCI 1 -- Experience Modification -- 2.50 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J17","v":"Finished"}]
["Q11 -- Fisher -- Quintiles & Efficiency Tests -- 3.75 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J18","v":"Finished"}]
["Q12 -- Fisher -- Table M -- 2.50 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J19","v":"Finished"}]
["Q13 -- Fisher -- Converted Insurance Charge -- 2.50 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J20","v":"Finished"}]
["Q14 -- Fisher -- Per-Occurrence and Aggregate Overlap -- 1.75 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J21","v":"Finished"}]
["Q15 -- Fisher -- Large Dollar Deductible Rating -- 1.75 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J22","v":"Finished"}]
["Q16 -- Fisher -- Large Dollar Deductible Rating -- 3.75 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J23","v":"Finished"}]
["Q17 -- Couret & Venter -- Multi-Dimensional Credibility -- 2.50 points ",null,null,null,null,null,null,{"$f":"'Point Grid'!J24","v":"Finished"}]
["Q18 -- Grossi -- Epistemic & Aleatory Risk -- 2.00 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J25","v":"Finished"}]
["Q19 -- Clark -- Reinsurance & Return on Capital -- 3.00 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J26","v":"Finished"}]
["Q20 -- Grossi -- Reinstatement Premiums -- 3.00 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J27","v":"Finished"}]
["Q21 -- Bernegger -- Expected Ceded Loss Ratio -- 4.00 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J28","v":"Finished"}]

{"sheet":"1","index":4,"n_rows":35,"n_cols":14,"rows_kept":35,"cut":false,"hidden":false,"merged":[[5,1,6,1],[0,6,0,7]]}
[1,"Finished",null,null,null,null,"Navigator",null,null,"Solution"]
[null,null,null,null,null,null,null,null,"(a)","From the Bailey and Simon paper we know there is limited variation in the"]
["Points","A group of insureds have different expected claim frequencies. The number of insureds claim-free",null,null,null,null,null,null,null,"group of insured's chance of an accident if the credibility is proportional to"]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('1'!A1,'Point Grid'!A:A,0))","v":2.75},"for the past t years is as follows:",null,null,null,null,null,null,null,"the number of years of experience."]
[]
[null,"Expected Claim Frequency",null,null,null,null,null,null,null,"Expected Claims"]
[null,null,"t = 0","t = 1","t = 2","t = 3",null,null,null,"Group","t = 0","t = 1","t = 2","t = 3"]
[null,0.05,50000,47500,45000,44000,null,null,null,0.05,{"$f":"$J8*C8","v":2500},{"$f":"$J8*D8","v":2375},2250,2200]
[null,0.1,50000,45000,43000,36000,null,null,null,0.1,{"$f":"$J9*C9","v":5000},4500,4300,3600]
[null,0.2,25000,20500,16500,14000,null,null,null,0.2,5000,4100,3300,2800]
[null,"Total",{"$f":"SUM(C8:C10)","v":125000},{"$f":"SUM(D8:D10)","v":113000},{"$f":"SUM(E8:E10)","v":104500},{"$f":"SUM(F8:F10)","v":94000},null,null,null,"Total",{"$f":"SUM(K8:K10)","v":12500},{"$f":"SUM(L8:L10)","v":10975},9850,8600]
[]
["(a)","Determine whether the variation of an individual insured's chance for an accident changes over time.",null,null,null,null,null,null,null,"Average","t = 0","t = 1","t = 2","t = 3"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$30,MATCH($A$1,'Point Grid'!$A$8:$A$30,0),MATCH(A13,'Point Grid'!$C$7:$I$7,0))","v":2.75},null,null,null,null,null,null,null,null,"Frequency",{"$f":"K11/C11","v":0.1},{"$f":"L11/D11","v":9.7123893805309738E-2},9.4258373205741625E-2,9.1489361702127653E-2]
[2.75,null,null,null,null,null,null,null,null,"at time t"]
[null,null,null,null,null,null,null,null,null,"(all groups)"]
[]
[null,null,null,null,null,null,null,null,null,"Frequency Relative to t = 0"]
[null,null,null,null,null,null,null,null,null,null,"t = 0","t = 1","t = 2","t = 3"]
[null,null,null,null,null,null,null,null,null,null,{"$f":"K14/$K$14","v":1},{"$f":"L14/$K$14","v":0.97123893805309736},0.94258373205741619,0.91489361702127647]
[]
[null,null,null,null,null,null,null,null,null,"We set the experience modification equal to the relative frequency."]
[null,null,null,null,null,null,null,null,null,"The question tells us the number of insured's that are claim-free for the past t-years."]
[null,null,null,null,null,null,null,null,null,"So we know R = 0 when t > 0."]
[null,null,null,null,null,null,null,null,null,"Experience Mod = Z*R + (1 - Z)*1 = 1 - Z when t > 0."]
[]
[null,null,null,null,null,null,null,null,null,"From here we solve for the credibility at each time t > 0."]
[null,null,null,null,null,null,null,null,null,"Credibility at time t."]
[null,null,null,null,null,null,null,null,null,null,null,"t = 1","t = 2","t = 3"]
[null,null,null,null,null,null,null,null,null,null,null,{"$f":"1-L20","v":2.8761061946902644E-2},5.741626794258381E-2,8.5106382978723527E-2]
[null,null,null,null,null,null,null,null,null,"Re-basing relative to t = 1."]
[null,null,null,null,null,null,null,null,null,null,null,"t = 1","t = 2","t = 3"]
[null,null,null,null,null,null,null,null,null,null,null,{"$f":"L30/$L$30","v":1},{"$f":"M30/$L$30","v":1.9963194700036839},2.9590834697217732]
[null,null,null,null,null,null,null,null,null,"Since these ratios are approximately 1, 2, 3 we conclude there is limited variation"]
[null,null,null,null,null,null,null,null,null,"in the insured's chance of an accident changing over time."]

{"sheet":"2","index":5,"n_rows":17,"n_cols":11,"rows_kept":17,"cut":false,"hidden":false,"merged":[[0,7,0,8]]}
[2,"Finished",null,null,null,null,null,"Navigator",null,null,"Solution"]
[null,null,null,null,null,null,null,null,null,"(a)","To group classes of insureds with similar risk characteristics and/or expected costs"]
["Points",null,null,null,null,null,null,null,null,null,"into larger homogeneous groups that have sufficient volume for credible ratemaking."]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('2'!A1,'Point Grid'!A:A,0))","v":2},null,null,null,null,null,null,null,null,null,"The clustering should maximize the variance between classes and minimize the "]
["(a)","Describe the purpose of clustering as it pertains to risk classification.",null,null,null,null,null,null,null,null,"variance within classes."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$30,MATCH($A$1,'Point Grid'!$A$8:$A$30,0),MATCH(A5,'Point Grid'!$C$7:$I$7,0))","v":0.5}]
[0.5,null,null,null,null,null,null,null,null,"(b)","Hierarchical clustering means new clusters are formed from a single originating"]
[null,null,null,null,null,null,null,null,null,null,"cluster, i.e. the clusters are nested."]
["(b)","Briefly contrast hierarchical and non-hierarchical clustering methods.",null,null,null,null,null,null,null,null,"Non-hierarchical clustering doesn't have this restriction, so new clusters can be"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$30,MATCH($A$1,'Point Grid'!$A$8:$A$30,0),MATCH(A9,'Point Grid'!$C$7:$I$7,0))","v":0.5},null,null,null,null,null,null,null,null,null,"formed by selecting classes from multiple distinct clusters."]
[0.5]
[null,null,null,null,null,null,null,null,null,"(c)","Credibility: As you increase the number of clusters, each one becomes smaller."]
["(c)","An insurer wants to increase the number of groupings for a particular class within their classification plan.",null,null,null,null,null,null,null,null,"This improves homogeneity but it's then possible the cluster will lack credibility"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$30,MATCH($A$1,'Point Grid'!$A$8:$A$30,0),MATCH(A13,'Point Grid'!$C$7:$I$7,0))","v":1},"According to Actuarial Standards of Practice No. 12, \"Risk Classification\", describe two considerations in",null,null,null,null,null,null,null,null,"to be useful."]
[1,"using hierarchical or non-hierarchical clustering methods to determine the groupings.",null,null,null,null,null,null,null,null,"Expense: The cost of increasing the number of groupings (e.g. upgrading IT systems,"]
[null,null,null,null,null,null,null,null,null,null,"analysis, training etc.) should not be more than the estimated benefit of"]
[null,null,null,null,null,null,null,null,null,null,"implementing the new groupings."]

{"sheet":"3","index":6,"n_rows":19,"n_cols":8,"rows_kept":19,"cut":false,"hidden":false,"merged":[]}
[3,"Finished",null,null,null,"Navigator",null,"Solution"]
[null,null,null,null,null,null,"(a)","Credibility: Since there are only two channels and an insured must come through one of "]
["Points","A private passenger automobile insurer sells policies through two distribution channels:",null,null,null,null,null,"them, it is likely that both channels are large enough to produce credible predictions."]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('3'!A1,'Point Grid'!A:A,0))","v":2},"independent insurance agents and directly to consumers via the internet.",null,null,null,null,null,"Absence of Ambiguity: Each channel is clearly defined and easy to determine which route"]
[null,null,null,null,null,null,null,"an insured came from. Further, the channels are mutually independent."]
[null,"The company's rates already incorporate expense differentials between the two channels but"]
[null,"the head of sales has asked their actuary to file different pure premium factors for the two",null,null,null,null,"(b)","Public Acceptability: It is unclear how the insured's choice of distribution channel "]
[null,"groups.",null,null,null,null,null,"is related to their loss potential."]
[null,null,null,null,null,null,null,"Manipulation: If an insured knows they can obtain a better rate through one channel or"]
[null,"The actuary wishes to evaluate the acceptability of the request against Actuarial Standard of ",null,null,null,null,null,"the other, they can change their channel to obtain preferred pricing."]
[null,"Practice No. 12 (Risk Classification)."]
[]
["(a)","Identify and briefly describe two considerations supporting inclusion of distribution channel in"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$30,MATCH($A$1,'Point Grid'!$A$8:$A$30,0),MATCH(A13,'Point Grid'!$C$7:$I$7,0))","v":1},"the pure premium factors."]
[1]
[]
["(b)","Identify and briefly describe two considerations against inclusion of distribution channel in the"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$30,MATCH($A$1,'Point Grid'!$A$8:$A$30,0),MATCH(A17,'Point Grid'!$C$7:$I$7,0))","v":1},"pure premium factors."]
[1]

{"sheet":"4","index":7,"n_rows":21,"n_cols":9,"rows_kept":21,"cut":false,"hidden":false,"merged":[]}
[4,"Finished",null,null,null,null,"Navigator",null,"Solution"]
[null,null,null,null,null,null,null,"(a)","   i. Including the limit of liability in a GLM can lead to unexpected results such as a "]
["Points","An actuary is conducting a generalized linear model (GLM) analysis on historical personal ",null,null,null,null,null,null,"       lower rate for more coverage. This can be because of favourable selection."]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('4'!A1,'Point Grid'!A:A,0))","v":3},"automobile data in order to develop a rating plan.",null,null,null,null,null,null,"  ii. The number of coverage changes during the current policy period will not be available"]
[null,null,null,null,null,null,null,null,"       at inception. Further, the number is likely to be different from year to year which"]
["(a)","Argue against the following factors being included as predictors in the actuary's GLM analysis:",null,null,null,null,null,null,"       will dilute its usefulness as a predictor."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$30,MATCH($A$1,'Point Grid'!$A$8:$A$30,0),MATCH(A6,'Point Grid'!$C$7:$I$7,0))","v":1.5},"   i. Limit of liability",null,null,null,null,null,null," iii. There are likely too many ZIP codes in the data set to have credible data. A GLM"]
[1.5,"  ii. Number of coverage changes during the current policy period.",null,null,null,null,null,null,"       assumes the underlying data is fully credible. Further, since there are a lot of ZIP"]
[null," iii. ZIP code of the garaging location of the automobile.",null,null,null,null,null,null,"       codes, the model will have too many degrees of freedom which will likely cause"]
[null,null,null,null,null,null,null,null,"       overfitting."]
["(b)","The actuary is modelling pure premium with a log-link function and a Tweedie error distribution"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$30,MATCH($A$1,'Point Grid'!$A$8:$A$30,0),MATCH(A11,'Point Grid'!$C$7:$I$7,0))","v":1},"(1 < p < 2). Provide two arguments against the inclusion of deductible as a predictor in the actuary's",null,null,null,null,null,"(b)","Deductibles should lower frequency by excluding small losses below the deductible."]
[1,"GLM analysis.",null,null,null,null,null,null,"As a result, they increase the average severity. This means frequency and severity do not"]
[null,null,null,null,null,null,null,null,"move in the same direction, which is an assumption of the Tweedie error model."]
["(c)","Other than including deductible as a predictor in the GLM, describe how to determine deductible",null,null,null,null,null,null,"Including deductibles in the GLM can produce counterintuitive results such as higher"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$30,MATCH($A$1,'Point Grid'!$A$8:$A$30,0),MATCH(A15,'Point Grid'!$C$7:$I$7,0))","v":0.5},"relativities and how such relativities can be incorporated in a GLM.",null,null,null,null,null,null,"relativities for higher deductibles. This can be because of reasons beyond loss"]
[0.5,null,null,null,null,null,null,null,"elimination such as underwriters requiring insureds perceived to be riskier to carry a"]
[null,null,null,null,null,null,null,null,"higher deductible."]
[]
[null,null,null,null,null,null,null,"(c)","Deductibles should be determined using a mixture of experience and exposure rating"]
[null,null,null,null,null,null,null,null,"and then included in the GLM model via an offset."]

{"sheet":"5","index":8,"n_rows":31,"n_cols":13,"rows_kept":31,"cut":false,"hidden":false,"merged":[]}
[5,"Finished",null,null,null,"Navigator",null,"Solution"]
[null,null,null,null,null,null,"(a)","Rank the risks by model predicted loss in increasing order."]
["Points","A GLM has been used to develop an insurance rating plan. The results are given below:",null,null,null,null,null,null,null,null,"Cumulative",null,"Cumulative"]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('5'!A1,'Point Grid'!A:A,0))","v":2.25},null,null,null,null,null,null,"Rank","Risk","Model","% Exposure","Actual","% Actual Loss"]
[null,"Risk","Model Predicted Loss","Actual Loss",null,null,null,0,0,0,0,0,0]
[null,1,2000,2050,null,null,null,1,5,{"$f":"INDEX($C$6:$C$10,MATCH(I6,$B$6:$B$10,0))","v":200},{"$f":"COUNT(I6:$I$6)/COUNT($I$6:$I$10)","v":0.2},{"$f":"INDEX($D$6:$D$10,MATCH(I6,$B$6:$B$10,0))","v":400},{"$f":"SUM(L6:$L$6)/$L$11","v":0.08}]
[null,2,500,220,null,null,null,2,2,{"$f":"INDEX($C$6:$C$10,MATCH(I7,$B$6:$B$10,0))","v":500},{"$f":"COUNT(I$6:$I7)/COUNT($I$6:$I$10)","v":0.4},{"$f":"INDEX($D$6:$D$10,MATCH(I7,$B$6:$B$10,0))","v":220},{"$f":"SUM(L$6:$L7)/$L$11","v":0.124}]
[null,3,1500,1480,null,null,null,3,4,800,{"$f":"COUNT(I$6:$I8)/COUNT($I$6:$I$10)","v":0.6},850,{"$f":"SUM(L$6:$L8)/$L$11","v":0.29399999999999998}]
[null,4,800,850,null,null,null,4,3,1500,{"$f":"COUNT(I$6:$I9)/COUNT($I$6:$I$10)","v":0.8},1480,{"$f":"SUM(L$6:$L9)/$L$11","v":0.59}]
[null,5,200,400,null,null,null,5,1,2000,{"$f":"COUNT(I$6:$I10)/COUNT($I$6:$I$10)","v":1},2050,{"$f":"SUM(L$6:$L10)/$L$11","v":1}]
[null,null,null,null,null,null,null,"Total",null,{"$f":"SUM(J6:J10)","v":5000},null,{"$f":"SUM(L6:L10)","v":5000}]
["(a)","Plot the Lorenz curve for this rating plan. Label each axis and the coordinates of each point"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$30,MATCH($A$1,'Point Grid'!$A$8:$A$30,0),MATCH(A12,'Point Grid'!$C$7:$I$7,0))","v":1.75},"on the curve."]
[1.75]
[]
["(b)","Briefly describe how the Gini index is calculated and what the Gini index measures when"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$30,MATCH($A$1,'Point Grid'!$A$8:$A$30,0),MATCH(A16,'Point Grid'!$C$7:$I$7,0))","v":0.5},"applied to an insurance rating program. Do not calculate the Gini index."]
[0.5]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,"(b)","The Gini index is twice the area between the line of equality and the Lorenz curve."]
[null,null,null,null,null,null,null,"The line of equality is a straight line from (0,0) to (1,1)."]
[null,null,null,null,null,null,null,"The Gini index measures the ability of the rating plan to differentiate between the best"]
[null,null,null,null,null,null,null,"and the worst risks. A higher Gini index says the plan has greater predictive power."]

{"sheet":"6","index":9,"n_rows":26,"n_cols":12,"rows_kept":26,"cut":false,"hidden":false,"merged":[]}
[6,"Finished",null,null,null,null,"Navigator",null,"Solution"]
[null,null,null,null,null,null,null,"(a)","ln(μ) = 0.91 + 0.013*4 + -0.187*ln(25000) + 0.062*8"]
["Points","An actuary has constructed a three-variable Tweedie GLM with a log-link function to estimate loss ",null,null,null,null,null,null,"ln(μ) = ",{"$f":" 0.91 + 0.013*4 + -0.187*LN(25000) + 0.062*8","v":-0.43568001642001308}]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('6'!A1,'Point Grid'!A:A,0))","v":2.5},"ratios for commercial property new business. The actuary wants to create a second model for renewal",null,null,null,null,null,null,"The modelled loss ratio, μ, is",null,null,{"$f":"EXP(J3)","v":0.64682466609722866}]
[null,"business that will include all of the variables from the new business model, plus a variable for the prior"]
[null,"year claim count. The actuary requires that the coefficients of the variables (Average Building Age,",null,null,null,null,null,"(b)","The renewal model may be written as"]
[null,"log(Manual Premium), and Location Count) are consistent between the new and renewal models.",null,null,null,null,null,null,"ln(μ) = β0 + 0.013*(age) + -0.187*(logprem) + 0.062*(loccnt) + β4*(priorYears)"]
[null,null,null,null,null,null,null,null,"To produce the renewal model we need to estimate the β4 parameter and "]
[null,"The fitted new business model parameters are as follows:",null,null,null,null,null,null,"re-estimate the intercept, β0."]
[null,"Variable",null,"Name","Estimate"]
[null,null,null,"Intercept",0.91,null,null,"(c)","Cross-validation: Split the data into k parts, run the model on (k-1) parts and then"]
[null,"Average Building Age (Years)",null,"age",1.2999999999999999E-2,null,null,null,"validate the result on the kth part. Iterate k times and compare how similar the"]
[null,"log(Manual Premium)",null,"logprem",-0.187,null,null,null,"estimates are across the k iterations to assess the variable stability."]
[null,"Location Count",null,"loccnt",6.2E-2,null,null,null,"Cook's Distance: Sort the observations in the data set according to their Cook's"]
[null,null,null,null,null,null,null,null,"Distance value (higher values have more influence on the model). Systematically"]
["(a)","Calculate the modeled loss ratio for a new business policy with a manual premium of $25,000, an",null,null,null,null,null,null,"remove some of the more influential observations and re-run the model to"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$30,MATCH($A$1,'Point Grid'!$A$8:$A$30,0),MATCH(A16,'Point Grid'!$C$7:$I$7,0))","v":0.75},"average building age of four years, and having eight locations.",null,null,null,null,null,null,"see if the estimated parameters change significantly."]
[0.75]
[]
["(b)","Briefly describe how to produce the renewal business model, and specify the resulting equation for"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$30,MATCH($A$1,'Point Grid'!$A$8:$A$30,0),MATCH(A20,'Point Grid'!$C$7:$I$7,0))","v":0.75},"the renewal business modeled loss ratio."]
[0.75]
[]
["(c)","Identify and briefly describe two techniques that the actuary can use to assess the stability of the new"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$30,MATCH($A$1,'Point Grid'!$A$8:$A$30,0),MATCH(A24,'Point Grid'!$C$7:$I$7,0))","v":1},"variable in the renewal business model."]
[1]

{"sheet":"7","index":10,"n_rows":22,"n_cols":14,"rows_kept":22,"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)","AIC = -2*log-likelihood + 2*(# parameters)"]
["Points","A company is considering modifying its rating plan to include factors by age",null,null,null,null,null,null,null,"BIC = -2*log-likelihood + p*ln(n)"]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('7'!A1,'Point Grid'!A:A,0))","v":1.5},"group. Below are statistics for the base model and for the new model."]
[null,null,null,null,null,null,null,null,null,"Base model"]
[null,"Statistic","Base Model","New Model",null,null,null,null,null,"AIC = -2*(-750) + 2*10 =",null,null,{"$f":"-2*C7+2*C9","v":1520}]
[null,"Log-likelihood",-750,-737.5,null,null,null,null,null,"BIC = -2*(-750) + 10*ln(1,000,000) = ",null,null,null,{"$f":"-2*C7+10*LN(C10)","v":1638.1551055796426}]
[null,"Deviance",500,475]
[null,"Parameters",10,15,null,null,null,null,null,"New Model"]
[null,"Data points",1000000,1000000,null,null,null,null,null,"AIC = -2*(-737.5) + 2*15 =",null,null,{"$f":"-2*D7+2*D9","v":1505}]
[null,null,null,null,null,null,null,null,null,"BIC = -2*(-737.5) + 15*ln(1,000,000) =",null,null,null,{"$f":"-2*D7+D9*LN(D10)","v":1682.2326583694642}]
["(a)","Calculate the Akaike Information Criterion (AIC) and the Bayesian Information"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$30,MATCH($A$1,'Point Grid'!$A$8:$A$30,0),MATCH(A12,'Point Grid'!$C$7:$I$7,0))","v":1},"Criterion (BIC) for both models.",null,null,null,null,null,null,"(b)","The BIC measure penalizes more when there are a large number of data points as is the case here."]
[1,null,null,null,null,null,null,null,null,"The AIC measure is generally preferred in this situation as BIC may cause predictive variables to be "]
[null,null,null,null,null,null,null,null,null,"excluded."]
["(b)","Explain whether AIC or BIC is a more reliable test statistic as an indicator of "]
[{"$f":"INDEX('Point Grid'!$C$8:$I$30,MATCH($A$1,'Point Grid'!$A$8:$A$30,0),MATCH(A16,'Point Grid'!$C$7:$I$7,0))","v":0.25},"whether to adopt the new model.",null,null,null,null,null,null,"(c)","The AIC for the new model is lower than the AIC for the base model. Therefore, adopt the new model."]
[0.25]
[]
["(c)","Recommend and briefly justify whether to adopt the new model."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$30,MATCH($A$1,'Point Grid'!$A$8:$A$30,0),MATCH(A20,'Point Grid'!$C$7:$I$7,0))","v":0.25}]
[0.25]

{"sheet":"8","index":11,"n_rows":22,"n_cols":16,"rows_kept":22,"cut":false,"hidden":false,"merged":[]}
[8,"Finished",null,null,null,null,"Navigator",null,"Solution"]
[null,null,null,null,null,null,null,"(a)","To determine whether anti-selection is present we can compare the ILFs for the individual policy"]
["Points","Given the following average severity, an actuary wants to validate if anti-selection ",null,null,null,null,null,null,"limits with those obtained using all available policy limits."]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('8'!A1,'Point Grid'!A:A,0))","v":2.75},"impacts its Increased Limits Factors for a given insurance coverage:"]
[null,null,null,"Policy Limit",null,null,null,null,"All Policy Limits ILFs",null,null,"$25k Policy Limit ILFs",null,null,"$50k Policy Limit ILFs"]
[null,null,"Severity limited to:",25000,50000,null,null,null,10000,{"$f":"SUM(D7:E7)/SUM($D$7:$E$7)","v":1},null,10000,{"$f":"D7/$D$7","v":1},null,10000,{"$f":"E7/$E$7","v":1}]
[null,null,10000,4000,6000,null,null,null,25000,{"$f":"SUM(D8:E8)/SUM($D$7:$E$7)","v":1.45},null,25000,{"$f":"D8/$D$7","v":1.625},null,25000,{"$f":"E8/$E$7","v":1.3333333333333333}]
[null,null,25000,6500,8000,null,null,null,50000,1.95,null,50000,2.25,null,50000,1.75]
[null,null,50000,9000,10500]
[null,null,null,null,null,null,null,null,"It is clear the ILFs under the two individual policy limits are significantly different than those"]
[null,"50% of the policies have a $25,000 policy limit and 50% of the policies have a $50,000",null,null,null,null,null,null,"obtained using all available data. This implies anti-selection is present as they should be"]
[null,"policy limit.",null,null,null,null,null,null,"(approximately) equal otherwise."]
[]
["(a)","Demonstrate if anti-selection impacts the ILFs.",null,null,null,null,null,"(b)","Adverse Selection:"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$30,MATCH($A$1,'Point Grid'!$A$8:$A$30,0),MATCH(A14,'Point Grid'!$C$7:$I$7,0))","v":1.25},null,null,null,null,null,null,null,"The higher limits generate higher ILFs because higher risk insureds choose higher policy limits."]
[1.25,null,null,null,null,null,null,null,"This could be because the more risky insureds are aware of their riskiness and choose high limits"]
[null,null,null,null,null,null,null,null,"to protect themselves."]
["(b)","Identify and briefly describe two possible forms of anti-selection for ILFs and give one"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$30,MATCH($A$1,'Point Grid'!$A$8:$A$30,0),MATCH(A18,'Point Grid'!$C$7:$I$7,0))","v":1.5},"example for each.",null,null,null,null,null,null,"Favourable Selection:"]
[1.5,null,null,null,null,null,null,null,"The higher limits generate lower ILFs. This can be because good risks with a large amount of "]
[null,null,null,null,null,null,null,null,"assets to protect will choose higher limits to get that protection, even though they are not"]
[null,null,null,null,null,null,null,null,"riskier than others."]

{"sheet":"9","index":12,"n_rows":21,"n_cols":11,"rows_kept":21,"cut":false,"hidden":false,"merged":[]}
[9,"Finished",null,null,null,null,null,null,"Navigator",null,"Solution"]
[null,null,null,null,null,null,null,null,null,"(a)","The underwriter should consider whether the risk is a good fit for the class it is in as an"]
["Points","An actuary and an underwriter are discussing a Workers' Compensation book of business.",null,null,null,null,null,null,null,null,"increased experience modifier doesn't necessarily imply a bad risk. Since the predominant"]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('9'!A1,'Point Grid'!A:A,0))","v":1.5},null,null,null,null,null,null,null,null,null,"risk in the class has had a decreasing modifier, the risk under consideration could receive an"]
[null,null,null,null,null,null,null,null,null,null,"increased experience modifier due to random fluctuations if it is not a good fit for the class."]
["(a)","The underwriter is considering not renewing a risk in the book because the experience",null,null,null,null,null,null,null,null,"The underwriter should check the standard loss ratios for the risks in the classes to see if"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$30,MATCH($A$1,'Point Grid'!$A$8:$A$30,0),MATCH(A6,'Point Grid'!$C$7:$I$7,0))","v":0.5},"modifier of the account has increased. The underwriter notes that the expected loss ratio",null,null,null,null,null,null,null,null,"the experience modifications are appropriate as the standard loss ratios should be similar"]
[0.5,"(ELR) for the predominant class code on the account has decreased over the last three years.",null,null,null,null,null,null,null,null,"for all credit and debit risks in the book of business. If this is true then the underwriter"]
[null,"Discuss whether the underwriter should non-renew the risk given the change in ELR.",null,null,null,null,null,null,null,null,"should renew the risk."]
[]
["(b)","A different account had large losses last year and now has a debit modification. ",null,null,null,null,null,null,null,"(b)","The underwriter's reasoning is poor. A debit mod is not supposed to help the current"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$30,MATCH($A$1,'Point Grid'!$A$8:$A$30,0),MATCH(A11,'Point Grid'!$C$7:$I$7,0))","v":0.5},"The underwriter wants to renew this account since the debit mod will help recoup losses.",null,null,null,null,null,null,null,null,"premium recoup prior losses. Instead it should reflect knowledge about the future"]
[0.5,"Evaluate the underwriter's reasoning to renew the account.",null,null,null,null,null,null,null,null,"expected losses in the policy period. The underwriter should renew the account"]
[null,null,null,null,null,null,null,null,null,null,"knowing that adequate rates are being charged for future losses."]
["(c)","The underwriter points out that the book of business has an overall experience modifier"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$30,MATCH($A$1,'Point Grid'!$A$8:$A$30,0),MATCH(A15,'Point Grid'!$C$7:$I$7,0))","v":0.5},"below 1.0 and believes this is due to superior risk selection. Provide two reasons why this",null,null,null,null,null,null,null,"(c)","• The book may have a number of small risks that do not qualify for experience rating or"]
[0.5,"may not be the case.",null,null,null,null,null,null,null,null,"receive limited credibility. This results in mods that are either 1 or close to 1 in the off-balance"]
[null,null,null,null,null,null,null,null,null,null,"calculation, which decreases the average experience mod for the book."]
[null,null,null,null,null,null,null,null,null,null,"• The experience mod is not a predictor of plan performance. After the mod is applied, all"]
[null,null,null,null,null,null,null,null,null,null,"risks should be equal. It is possible the manual rates were set too high and so the experience"]
[null,null,null,null,null,null,null,null,null,null,"mods being lower than 1 on average are correcting for this."]

{"sheet":"10","index":13,"n_rows":32,"n_cols":16,"rows_kept":32,"cut":false,"hidden":false,"merged":[[27,5,28,5]]}
[10,"Finished",null,null,null,null,null,"Navigator",null,"Solution"]
[null,null,null,null,null,null,null,null,"(a)","Look up the class code in the tables provided to get the ELR and D-ratio."]
["Points","An insured is subject to experience rating under the National Council on Compensation",null,null,null,null,null,null,null,"ELR =",{"$f":"C25","v":2.02}]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('10'!A1,'Point Grid'!A:A,0))","v":2.5},"Insurance (NCCI)'s Experience Rating Plan Manual for Workers' Compensation and",null,null,null,null,null,null,null,"D-Ratio =",{"$f":"D25","v":0.17}]
[null,"Employers Liability Insurance. The following information about the insured is given:"]
[null,null,null,null,null,null,null,null,null,"Expected Unlimited Loss = (Payroll/$100)*ELR =",null,null,null,null,{"$f":"B7/100*K3","v":101000}]
[null,5000000,"Payroll (Experience Period)",null,null,null,null,null,null,"Expected Excess Loss = Expected Unlimited Loss *(1 - D-ratio) =",null,null,null,null,null,{"$f":"O6*(1-K4)","v":83830}]
[null,"Alabama","State"]
[null,7705,"Class",null,null,null,null,null,null,"Look up the expected unlimited loss in the table to get the weight and ballast values."]
[null,null,null,null,null,null,null,null,null,"W = ",{"$f":"C32","v":0.14000000000000001}]
[null,"The following claims apply to the experience period. Each claim involves only one person,",null,null,null,null,null,null,null,"B =",{"$f":"F32","v":28000}]
[null,"and none are disease claims:"]
[null,"Claim Number","Type","Loss",null,null,null,null,null,"Compute the actual primary and actual excess loss experience."]
[null,1,"Indemnity",29000,null,null,null,null,null,"Claim","Primary","Excess"]
[null,2,"Medical",30500,null,null,null,null,null,1,{"$f":"IF(C14=\"Medical\",0.3,1)*MIN(D14,5000)","v":5000},{"$f":"IF(C14=\"Medical\",0.3,1)*MAX(0,D14-5000)","v":24000}]
[null,3,"Indemnity",90000,null,null,null,null,null,2,{"$f":"IF(C15=\"Medical\",0.3,1)*MIN(D15,5000)","v":1500},{"$f":"IF(C15=\"Medical\",0.3,1)*MAX(0,D15-5000)","v":7650}]
[null,4,"Indemnity",1500,null,null,null,null,null,3,5000,85000]
[null,5,"Medical",45000,null,null,null,null,null,4,1500,0]
[null,null,null,null,null,null,null,null,null,5,1500,12000]
["(a)","Calculate the experience modification for this insured.",null,null,null,null,null,null,null,"Total",{"$f":"SUM(K15:K19)","v":14500},{"$f":"SUM(L15:L19)","v":128650}]
[{"$f":"INDEX('Point Grid'!$C$8:$I$30,MATCH($A$1,'Point Grid'!$A$8:$A$30,0),MATCH(A20,'Point Grid'!$C$7:$I$7,0))","v":2.5},"You may use the information given in the tables below."]
[2.5]
[null,"Class Code","ELR","D Ratio"]
[null,7704,1.41,0.13,null,null,null,null,null,"Mod =",{"$f":"(K20+K10*L20+(1-K10)*P7+K11)/(O6+K11)","v":1.0279441860465115}]
[null,7705,2.02,0.17]
[null,7710,1.41,0.13]
[]
[null,"Expected","Weighting",null,"Expected","Ballast Value"]
[null,"Losses","Value",null,"Losses"]
[null,"63,979 – 78,055",0.12,null,"37,652 – 64,802",21000]
[null,"78,056 – 92,133",0.13,null,"64,803 – 95,998",24500]
[null,"92,134 – 106,385",0.14000000000000001,null,"95,999 – 128,908",28000]

{"sheet":"11","index":14,"n_rows":50,"n_cols":14,"rows_kept":50,"cut":false,"hidden":false,"merged":[[4,1,5,1],[4,4,5,4],[0,6,0,7],[4,6,5,6],[4,5,5,5],[4,3,5,3],[4,2,5,2]]}
[11,"Finished",null,null,null,null,"Navigator",null,null,"Solution"]
[null,null,null,null,null,null,null,null,"(a)","Perform a Quintiles Test. Sort the risks by order of increasing modification"]
["Points","Given the following information about 10 risks for a manufacturing class:",null,null,null,null,null,null,null,null,null,null,"Premiums"]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('11'!A1,'Point Grid'!A:A,0))","v":3.75},null,null,null,null,null,null,null,null,"Risk","Current Rank","Proposed Rank","Current Mod","Proposed Mod"]
[null,"Risk","Type of Manufacturing","Manual Premium","Losses","Current Modification","Proposed Modification",null,null,1,{"$f":"_xlfn.RANK.AVG(F7,$F$7:$F$16,1)","v":1},{"$f":"_xlfn.RANK.AVG(G7,$G$7:$G$16,1)","v":1},{"$f":"F7*D7","v":650},{"$f":"G7*D7","v":500}]
[null,null,null,null,null,null,null,null,null,2,{"$f":"_xlfn.RANK.AVG(F8,$F$7:$F$16,1)","v":3},{"$f":"_xlfn.RANK.AVG(G8,$G$7:$G$16,1)","v":2},{"$f":"F8*D8","v":750},{"$f":"G8*D8","v":550}]
[null,1,"Uses Robots",1000,500,0.65,0.5,null,null,3,2,3,700,700]
[null,2,"Uses Robots",1000,600,0.75,0.55000000000000004,null,null,4,5,4,1000,750]
[null,3,"Made by hand",1000,700,0.7,0.7,null,null,5,4,5,900,950]
[null,4,"Uses Robots",1000,800,1,0.75,null,null,6,7,6,1150,1050]
[null,5,"Uses Robots",1000,900,0.9,0.95,null,null,7,6,7,1100,1250]
[null,6,"Made by hand",1000,1100,1.1499999999999999,1.05,null,null,8,9,8,1250,1500]
[null,7,"Uses Robots",1000,1200,1.1000000000000001,1.25,null,null,9,8,9,1200,1750]
[null,8,"Made by hand",1000,1500,1.25,1.5,null,null,10,10,10,1300,2000]
[null,9,"Made by hand",1000,1600,1.2,1.75]
[null,10,"Made by hand",1000,1800,1.3,2,null,null,"Current Plan"]
[null,null,null,null,null,null,null,null,null,"Quintile","Risk A","Risk B","Manual LR","Standard LR"]
["(a)","Evaluate the proposed experience rating plan compared to the current plan.",null,null,null,null,null,null,null,1,{"$f":"INDEX($J$5:$J$14,MATCH(1,$K$5:$K$14,0))","v":1},{"$f":"INDEX($J$5:$J$14,MATCH(2,$K$5:$K$14,0))","v":3},{"$f":"(SUMIFS($E$7:$E$16,$B$7:$B$16,K18)+SUMIFS($E$7:$E$16,$B$7:$B$16,L18))/(SUMIFS($D$7:$D$16,$B$7:$B$16,K18)+SUMIFS($D$7:$D$16,$B$7:$B$16,L18))","v":0.6},{"$f":"(SUMIFS($E$7:$E$16,$B$7:$B$16,K18)+SUMIFS($E$7:$E$16,$B$7:$B$16,L18))/(SUMIFS($M$5:$M$14,$B$7:$B$16,K18)+SUMIFS($M$5:$M$14,$B$7:$B$16,L18))","v":0.88888888888888884}]
[{"$f":"INDEX('Point Grid'!$C$8:$I$30,MATCH($A$1,'Point Grid'!$A$8:$A$30,0),MATCH(A18,'Point Grid'!$C$7:$I$7,0))","v":3},null,null,null,null,null,null,null,null,2,{"$f":"INDEX($J$5:$J$14,MATCH(3,$K$5:$K$14,0))","v":2},{"$f":"INDEX($J$5:$J$14,MATCH(4,$K$5:$K$14,0))","v":5},{"$f":"(SUMIFS($E$7:$E$16,$B$7:$B$16,K19)+SUMIFS($E$7:$E$16,$B$7:$B$16,L19))/(SUMIFS($D$7:$D$16,$B$7:$B$16,K19)+SUMIFS($D$7:$D$16,$B$7:$B$16,L19))","v":0.75},{"$f":"(SUMIFS($E$7:$E$16,$B$7:$B$16,K19)+SUMIFS($E$7:$E$16,$B$7:$B$16,L19))/(SUMIFS($M$5:$M$14,$B$7:$B$16,K19)+SUMIFS($M$5:$M$14,$B$7:$B$16,L19))","v":0.90909090909090906}]
[3,null,null,null,null,null,null,null,null,3,{"$f":"INDEX($J$5:$J$14,MATCH(5,$K$5:$K$14,0))","v":4},{"$f":"INDEX($J$5:$J$14,MATCH(6,$K$5:$K$14,0))","v":7},1,0.95238095238095233]
[null,null,null,null,null,null,null,null,null,4,{"$f":"INDEX($J$5:$J$14,MATCH(7,$K$5:$K$14,0))","v":6},{"$f":"INDEX($J$5:$J$14,MATCH(8,$K$5:$K$14,0))","v":9},1.35,1.1489361702127661]
["(b)","Evaluate the classification plan for this class.",null,null,null,null,null,null,null,5,{"$f":"INDEX($J$5:$J$14,MATCH(9,$K$5:$K$14,0))","v":8},{"$f":"INDEX($J$5:$J$14,MATCH(10,$K$5:$K$14,0))","v":10},1.65,1.2941176470588236]
[{"$f":"INDEX('Point Grid'!$C$8:$I$30,MATCH($A$1,'Point Grid'!$A$8:$A$30,0),MATCH(A22,'Point Grid'!$C$7:$I$7,0))","v":0.75}]
[0.75,null,null,null,null,null,null,null,null,"Proposed Plan"]
[null,null,null,null,null,null,null,null,null,"Quintile","Risk A","Risk B","Manual LR","Standard LR"]
[null,null,null,null,null,null,null,null,null,1,{"$f":"INDEX($J$5:$J$14,MATCH(1,$L$5:$L$14,0))","v":1},{"$f":"INDEX($J$5:$J$14,MATCH(2,$L$5:$L$14,0))","v":2},{"$f":"(SUMIFS($E$7:$E$16,$B$7:$B$16,K26)+SUMIFS($E$7:$E$16,$B$7:$B$16,L26))/(SUMIFS($D$7:$D$16,$B$7:$B$16,K26)+SUMIFS($D$7:$D$16,$B$7:$B$16,L26))","v":0.55000000000000004},{"$f":"(SUMIFS($E$7:$E$16,$B$7:$B$16,K26)+SUMIFS($E$7:$E$16,$B$7:$B$16,L26))/(SUMIFS($N$5:$N$14,$B$7:$B$16,K26)+SUMIFS($N$5:$N$14,$B$7:$B$16,L26))","v":1.0476190476190477}]
[null,null,null,null,null,null,null,null,null,2,{"$f":"INDEX($J$5:$J$14,MATCH(3,$L$5:$L$14,0))","v":3},{"$f":"INDEX($J$5:$J$14,MATCH(4,$L$5:$L$14,0))","v":4},{"$f":"(SUMIFS($E$7:$E$16,$B$7:$B$16,K27)+SUMIFS($E$7:$E$16,$B$7:$B$16,L27))/(SUMIFS($D$7:$D$16,$B$7:$B$16,K27)+SUMIFS($D$7:$D$16,$B$7:$B$16,L27))","v":0.75},{"$f":"(SUMIFS($E$7:$E$16,$B$7:$B$16,K27)+SUMIFS($E$7:$E$16,$B$7:$B$16,L27))/(SUMIFS($N$5:$N$14,$B$7:$B$16,K27)+SUMIFS($N$5:$N$14,$B$7:$B$16,L27))","v":1.0344827586206897}]
[null,null,null,null,null,null,null,null,null,3,{"$f":"INDEX($J$5:$J$14,MATCH(5,$L$5:$L$14,0))","v":5},{"$f":"INDEX($J$5:$J$14,MATCH(6,$L$5:$L$14,0))","v":6},1,{"$f":"(SUMIFS($E$7:$E$16,$B$7:$B$16,K28)+SUMIFS($E$7:$E$16,$B$7:$B$16,L28))/(SUMIFS($N$5:$N$14,$B$7:$B$16,K28)+SUMIFS($N$5:$N$14,$B$7:$B$16,L28))","v":1}]
[null,null,null,null,null,null,null,null,null,4,{"$f":"INDEX($J$5:$J$14,MATCH(7,$L$5:$L$14,0))","v":7},{"$f":"INDEX($J$5:$J$14,MATCH(8,$L$5:$L$14,0))","v":8},1.35,{"$f":"(SUMIFS($E$7:$E$16,$B$7:$B$16,K29)+SUMIFS($E$7:$E$16,$B$7:$B$16,L29))/(SUMIFS($N$5:$N$14,$B$7:$B$16,K29)+SUMIFS($N$5:$N$14,$B$7:$B$16,L29))","v":0.98181818181818181}]
[null,null,null,null,null,null,null,null,null,5,{"$f":"INDEX($J$5:$J$14,MATCH(9,$L$5:$L$14,0))","v":9},{"$f":"INDEX($J$5:$J$14,MATCH(10,$L$5:$L$14,0))","v":10},1.7,{"$f":"(SUMIFS($E$7:$E$16,$B$7:$B$16,K30)+SUMIFS($E$7:$E$16,$B$7:$B$16,L30))/(SUMIFS($N$5:$N$14,$B$7:$B$16,K30)+SUMIFS($N$5:$N$14,$B$7:$B$16,L30))","v":0.90666666666666662}]
[]
[null,null,null,null,null,null,null,null,null,"The manual loss ratios under the proposed plan are more dispersed than those in the current plan."]
[null,null,null,null,null,null,null,null,null,"This means the proposed plan does a better job at identifying differences between risks."]
[null,null,null,null,null,null,null,null,null,"The standard loss ratios for the current plan show an increasing trend, which implies the current"]
[null,null,null,null,null,null,null,null,null,"plan does not place enough credibility on the actual experience. Further, the current "]
[null,null,null,null,null,null,null,null,null,"plan has a wide range of standard loss ratios. This says the current plan does a poor job of"]
[null,null,null,null,null,null,null,null,null,"correcting for differences between risks."]
[null,null,null,null,null,null,null,null,null,"Whereas the standard loss ratios for the proposed plan has a smaller range of standard loss"]
[null,null,null,null,null,null,null,null,null,"ratios. However, it does have a decreasing trend in the standard loss ratios which implies"]
[null,null,null,null,null,null,null,null,null,"the proposed plan places too much credibility on the actual experience."]
[null,null,null,null,null,null,null,null,null,"Overall, the proposed plan is better though."]
[]
[null,null,null,null,null,null,null,null,"(b)","Average Loss for \"Uses Robots\" risk",null,null,{"$f":"AVERAGEIF(C7:C16,\"Uses Robots\",E7:E16)","v":800}]
[]
[null,null,null,null,null,null,null,null,null,"Average Loss for \"Made by hand\" risk",null,null,{"$f":"AVERAGEIF(C7:C16,\"Made by hand\",E7:E16)","v":1340}]
[]
[null,null,null,null,null,null,null,null,null,"Since there is a significant difference between the average loss for the two types of "]
[null,null,null,null,null,null,null,null,null,"manufacturing, the classification plan is not doing a good job of differentiating the loss"]
[null,null,null,null,null,null,null,null,null,"potential between them. Should consider splitting the manufacturing type into two"]
[null,null,null,null,null,null,null,null,null,"separate classes if there would be sufficient credibility in each class."]

{"sheet":"12","index":15,"n_rows":48,"n_cols":14,"rows_kept":48,"cut":false,"hidden":false,"merged":[]}
[12,"Finished",null,null,null,null,"Navigator",null,"Solution"]
[null,null,null,null,null,null,null,"(a)","Since we're not given the expected loss we will use the average actual loss because"]
["Points","An actuary is given the following sample of experience from a grouping of five similarly-sized risks:",null,null,null,null,null,null,"the risks are similar sized."]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('12'!A1,'Point Grid'!A:A,0))","v":2.5}]
[null,null,"Risk","Actual Loss",null,null,null,null,"Expected Loss =",null,{"$f":"AVERAGE(D6:D10)","v":85000}]
[null,null,1,85000]
[null,null,2,127500,null,null,null,null,"Risk","Entry Ratio"]
[null,null,3,42500,null,null,null,null,1,{"$f":"D6/$K$5","v":1}]
[null,null,4,63750,null,null,null,null,2,{"$f":"D7/$K$5","v":1.5}]
[null,null,5,106250,null,null,null,null,3,0.5]
[null,null,null,null,null,null,null,null,4,0.75]
["(a)","Construct a Table M of insurance charges and savings at entry ratios of 0 to 1.50 in multiples of 0.25.",null,null,null,null,null,null,5,1.25]
[{"$f":"INDEX('Point Grid'!$C$8:$I$30,MATCH($A$1,'Point Grid'!$A$8:$A$30,0),MATCH(A12,'Point Grid'!$C$7:$I$7,0))","v":1.5}]
[1.5,null,null,null,null,null,null,null,"Now create a table with a row for each entry ratio associated with a risk, and for each"]
[null,null,null,null,null,null,null,null,"of the desired increments."]
["(b)","Briefly describe what the insurance charge at an entry ratio of 1.25 reflects."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$30,MATCH($A$1,'Point Grid'!$A$8:$A$30,0),MATCH(A16,'Point Grid'!$C$7:$I$7,0))","v":0.25},null,null,null,null,null,null,null,"Entry Ratio","# Risks","# Risks > r","% Risks > r","Ins. Charge","Ins. Savings"]
[0.25,null,null,null,null,null,null,null,0,{"$f":"COUNTIF($J$8:$J$12,I18)","v":0},{"$f":"COUNTIF($J$8:$J$12,\">\"&I18)","v":5},{"$f":"K18/COUNT($I$8:$I$12)","v":1},{"$f":"M19+L18*(I19-I18)","v":1},{"$f":"M18+I18-1","v":0}]
[null,null,null,null,null,null,null,null,0.25,{"$f":"COUNTIF($J$8:$J$12,I19)","v":0},{"$f":"COUNTIF($J$8:$J$12,\">\"&I19)","v":5},{"$f":"K19/COUNT($I$8:$I$12)","v":1},0.75,{"$f":"M19+I19-1","v":0}]
[null,"Suppose the Table M constructed above is used to price a book of Workers' Compensation ",null,null,null,null,null,null,0.5,1,4,0.8,0.5,0]
[null,"retrospectively rated business. The following table of actual losses reflects the experience of this",null,null,null,null,null,null,0.75,1,3,0.6,0.30000000000000004,5.0000000000000044E-2]
[null,"book:",null,null,null,null,null,null,1,1,2,0.4,0.15000000000000002,0.14999999999999991]
[null,null,"Risk","Actual Loss",null,null,null,null,1.25,1,1,0.2,{"$f":"M24+L23*(I24-I23)","v":0.05},0.30000000000000004]
[null,null,1,12000,null,null,null,null,1.5,1,0,0,0,0.5]
[null,null,2,42500]
[null,null,3,63750,null,null,null,null,"Here we used "]
[null,null,4,106250,null,null,null,null,"φ(1.5) = 0"]
[null,null,5,275000,null,null,null,null,"φ(ri) = φ(ri+1) + (ri+1 - ri)*(% > ri)"]
[null,null,null,null,null,null,null,null,"ψ(r) = φ(r) + r - 1"]
["(c)","Evaluate the appropriateness of using the Table M constructed in part (a) above. "]
[{"$f":"INDEX('Point Grid'!$C$8:$I$30,MATCH($A$1,'Point Grid'!$A$8:$A$30,0),MATCH(A30,'Point Grid'!$C$7:$I$7,0))","v":0.75},"Provide two reasons in support of the conclusion.",null,null,null,null,null,"(b)","The insurance charge at an entry ratio of 1.25 reflects the excess portion of expected losses"]
[0.75,null,null,null,null,null,null,null,"that are above 1.25 times the expected loss. In the above example it is"]
[null,null,null,null,null,null,null,null,"the amount of loss in excess of 1.25*85,000 = 106,250."]
[]
[null,null,null,null,null,null,null,"(c)","Average loss for book = ",null,{"$f":"AVERAGE(D24:D28)","v":99900}]
[null,null,null,null,null,null,null,null,"Risk","Entry Ratio"]
[null,null,null,null,null,null,null,null,1,{"$f":"D24/$K$35","v":0.12012012012012012}]
[null,null,null,null,null,null,null,null,2,{"$f":"D25/$K$35","v":0.42542542542542544}]
[null,null,null,null,null,null,null,null,3,0.63813813813813813]
[null,null,null,null,null,null,null,null,4,1.0635635635635636]
[null,null,null,null,null,null,null,null,5,2.7527527527527527]
[]
[null,null,null,null,null,null,null,null,"It is not appropriate to use the Table M calculated in part (a) because"]
[null,null,null,null,null,null,null,null,"the two books of business have significantly different expected losses and the"]
[null,null,null,null,null,null,null,null,"new book has only one entry ratio higher than 1.25, yet the initial book had two."]
[null,null,null,null,null,null,null,null,"The greater the expected loss, the less variation is expected in the loss. This "]
[null,null,null,null,null,null,null,null,"results in a flatter curve that gives smaller insurance charges. Using the original"]
[null,null,null,null,null,null,null,null,"Table M will likely overstate the insurance charge."]

{"sheet":"13","index":16,"n_rows":34,"n_cols":10,"rows_kept":34,"cut":false,"hidden":false,"merged":[]}
[13,"Finished",null,null,null,"Navigator",null,"Solution"]
[null,null,null,null,null,null,"(a)","The converted insurance charge is cI = c(φ(rmax) - ψ(rmin))"]
["Points","A risk is written using a retrospective rating plan with the following characteristics:",null,null,null,null,null,"c = ",{"$f":"B8","v":1.085}]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('13'!A1,'Point Grid'!A:A,0))","v":2.5},10000000,"Standard Premium",null,null,null,null,"Expected loss =",null,{"$f":"B4*B5","v":6000000}]
[null,0.6,"Expected Loss Ratio"]
[null,0.8,"Loss Ratio at Maximum Premium",null,null,null,null,"Notice the graph is given in terms of loss ratio rather than entry ratios. So φ(rmax) corresponds"]
[null,0.2,"Loss Ratio at Minimum Premium",null,null,null,null,"to the area above an 80% loss ratio divided by the expected loss ratio, and ψ(rmin) is the area "]
[null,1.085,"Loss Conversion Factor",null,null,null,null,"below the 20% loss ratio divided by the expected loss ratio."]
[null,0.97,"Provision for Losses and Total Expenses Exclusive of Taxes"]
[null,null,null,null,null,null,null,"φ(rmax) = ",{"$f":"((90%-70%)*(1-80%) + (100% - 90%)*(120% - 80%))/B5","v":0.13333333333333333}]
[null,"The following Lee Diagram depicts actual experience from a sample of similarly-sized risks",null,null,null,null,null,"ψ(rmin) = ",{"$f":"((20% - 0%)*(20%-0%))/B5","v":6.666666666666668E-2}]
[null,"and similar to the risk in question:"]
[null,null,null,null,null,null,null,"converted insurance charge =",null,{"$f":"I3*J4*(I10-I11)","v":433999.99999999988}]
[]
[null,null,null,null,null,null,"(b)","R = (B + cL)*T where T is the Tax multiplier, B is the Basic premium and L is the ratable loss."]
[]
[null,null,null,null,null,null,null,"Basic premium B = e - (c - 1)*E + cI"]
[null,null,null,null,null,null,null,"where e is the expense provision in dollars."]
[null,null,null,null,null,null,null,"e = (Provision for Loss and Expense - Expected Loss Ratio)*Standard Premium"]
[null,null,null,null,null,null,null,"e = ",{"$f":"(B9-B5)*B4","v":3700000}]
[]
[null,null,null,null,null,null,null,"B = ",{"$f":"I20-(I3-1)*J4+J13","v":3624000}]
[]
[null,null,null,null,null,null,null,"R =",12500000,"(given)"]
[]
[null,null,null,null,null,null,null,"Actual loss ratio = ultimate loss / standard premium"]
[null,null,null,null,null,null,null,{"$f":"8700000/B4","v":0.87}]
["(a)","Determine the converted insurance charge for this plan.",null,null,null,null,null,"This is greater than the loss ratio allowed at maximum premium, so cap ratable loss at 80%."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$30,MATCH($A$1,'Point Grid'!$A$8:$A$30,0),MATCH(A28,'Point Grid'!$C$7:$I$7,0))","v":1.25},null,null,null,null,null,null,"Therefore, L =",null,{"$f":"80%*B4","v":8000000}]
[1.25]
[null,null,null,null,null,null,null,"Solve for the Tax Multiplier"]
["(b)","The insured's actual ultimate losses are $8,700,000 and the final retrospective premium is",null,null,null,null,null,"T = R / (B + cL) =",null,{"$f":"I24/(I22+I3*J29)","v":1.0159297789336801}]
[{"$f":"INDEX('Point Grid'!$C$8:$I$30,MATCH($A$1,'Point Grid'!$A$8:$A$30,0),MATCH(A32,'Point Grid'!$C$7:$I$7,0))","v":1.25},"$12,500,000. Determine the tax multiplier that was used in the rating of this plan."]
[1.25]

{"sheet":"14","index":17,"n_rows":34,"n_cols":9,"rows_kept":34,"cut":false,"hidden":false,"merged":[]}
[14,"Finished",null,null,null,null,"Navigator",null,"Solution"]
[null,null,null,null,null,null,null,"(a)","Draw a Lee Diagram that shows the unlimited and limited loss curves as well as the"]
["Points","A retrospective rated policy has both a loss limitation and a maximum premium.",null,null,null,null,null,null,"entry ratio at maximum premium."]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('14'!A1,'Point Grid'!A:A,0))","v":1.75}]
[]
["(a)","Demonstrate how the charges for the loss limitation and the maximum premium overlap."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$30,MATCH($A$1,'Point Grid'!$A$8:$A$30,0),MATCH(A6,'Point Grid'!$C$7:$I$7,0))","v":0.5}]
[0.5]
[]
["(b)","Explain how the overlap is handled differently when using Table M versus Table L."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$30,MATCH($A$1,'Point Grid'!$A$8:$A$30,0),MATCH(A10,'Point Grid'!$C$7:$I$7,0))","v":0.5}]
[0.5]
[]
["(c)","An actuary is pricing a large Workers' Compensation policy with a fixed loss limit of $100,000."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$30,MATCH($A$1,'Point Grid'!$A$8:$A$30,0),MATCH(A14,'Point Grid'!$C$7:$I$7,0))","v":0.75},"Recommend a table of insurance charges, Table M or Table L, that the actuary should use and"]
[0.75,"provide two reasons supporting the recommendation."]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,"When there is an occurrence limit as well as an aggregate limit, the occurrence limit makes"]
[null,null,null,null,null,null,null,null,"it less likely the aggregate limit will be hit. So the charge for the aggregate limit should"]
[null,null,null,null,null,null,null,null,"be reduced to avoid overlap."]
[]
[null,null,null,null,null,null,null,"(b)","When using a Table M the approach involves adjusting the expected loss to be larger via"]
[null,null,null,null,null,null,null,null,"the ICRLL adjustment. This shifts the aggregate loss distribution to a flatter curve, reducing"]
[null,null,null,null,null,null,null,null,"the impact of the overlap."]
[null,null,null,null,null,null,null,null,"However, a Table L calculates a charge for each of the occurrence limit and aggregate limit."]
[]
[null,null,null,null,null,null,null,"(c)","Recommend using Table M because:"]
[null,null,null,null,null,null,null,null,"• A Table M is based on countrywide data whereas a Table L uses California taxes. We don't"]
[null,null,null,null,null,null,null,null,"   know where the policy is located."]
[null,null,null,null,null,null,null,null,"• A Table M can be updated easily to account for inflation by adjusting the mapping between"]
[null,null,null,null,null,null,null,null,"   expected losses and the expected loss groups."]

{"sheet":"15","index":18,"n_rows":38,"n_cols":15,"rows_kept":38,"cut":false,"hidden":false,"merged":[[0,7,0,8]]}
[15,"Finished",null,null,null,null,null,"Navigator",null,null,"Solution"]
[null,null,null,null,null,null,null,null,null,"(a)","The premium for an excess Workers' Compensation policy is given by"]
["Points","Given the following information about a Workers' Compensation book of business:"]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('15'!A1,'Point Grid'!A:A,0))","v":1.75}]
[]
[null,null,null,null,null,null,null,null,null,null,"Recall this type of policy only handles and pays out on claims above the deductible."]
[]
[null,null,null,null,null,null,null,null,null,null,"So at maximum premium we have"]
[null,null,null,null,null,null,null,null,null,null,"100,000 =","(Expected Excess Loss * 1.05 + 840,000*2%) / (1 - 5% - 3% - -8.8%)"]
[]
[null,null,null,null,null,null,null,null,null,null,"Solving for the expected excess loss at maximum premium yields"]
[null,null,null,null,null,null,null,null,null,null,"Expected Excess Loss at Max. Premium =",null,null,null,{"$f":"(100000*(1-B24-B25-B26)-B19*B22)/(1+B21)","v":80000}]
[]
[null,null,null,null,null,null,null,null,null,null,"Using the graph provided we can estimate the expected unlimited loss. "]
[null,null,null,null,null,null,null,null,null,null,"Notice here we're using \"loss\" to mean Loss + ALAE"]
[]
[null,null,null,null,null,null,null,null,null,null,"Expected Unlimited Loss & ALAE =",null,null,null,{"$f":"(100*(10%-0%)+200*(30%-10%)+300*(40%-30%)+500*(60%-40%)+600*(70%-60%)+800*(90%-70%)+1000*(100%-90%))*1000","v":500000}]
[]
[null,840000,"Standard Premium",null,null,null,null,null,null,null,"The ratio of expected excess loss at maximum premium to expected unlimited loss is"]
[null,0.02,"Loss based assessment factor",null,null,null,null,null,null,null,{"$f":"O12/O17","v":0.16}]
[null,0.05,"Ratio of ULAE to Loss"]
[null,0.02,"General overhead expense",null,null,null,null,null,null,null,"The excess ratio at deductible d is defined as the average excess loss above the deductible"]
[null,0.04,"Risk load for credit risk",null,null,null,null,null,null,null,"divided by the expected unlimited loss."]
[null,0.05,"Acquisition expense"]
[null,0.03,"Taxes ",null,null,null,null,null,null,null,"We need to find a deductible d that produces an excess ratio of 0.16"]
[null,-8.7999999999999995E-2,"Profit"]
[null,null,null,null,null,null,null,null,null,null,"Using trial and error we get when d = $600,000"]
["(a)","Calculate the deductible for an excess Workers' Compensation policy that minimizes the",null,null,null,null,null,null,null,null,"Excess ratio @ $600,000 = ",null,null,{"$f":"((800000-600000)*(90%-70%)+(1000000-600000)*(100%-90%))/O17","v":0.16}]
[{"$f":"INDEX('Point Grid'!$C$8:$I$30,MATCH($A$1,'Point Grid'!$A$8:$A$30,0),MATCH(A28,'Point Grid'!$C$7:$I$7,0))","v":1.25},"insured's loss retention with a maximum premium of $100,000."]
[1.25,null,null,null,null,null,null,null,null,null,"So the required deductible is $600,000."]
[]
["(b)","Contrast the profit load in a large dollar deductible (LDD) policy to the profit load in an ",null,null,null,null,null,null,null,"(b)","The profit load for a large dollar deductible plan is usually higher than that for an excess Workers'"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$30,MATCH($A$1,'Point Grid'!$A$8:$A$30,0),MATCH(A32,'Point Grid'!$C$7:$I$7,0))","v":0.5},"excess Workers' Compensation policy.",null,null,null,null,null,null,null,null,"Compensation plan because the LDD plan competes on both price and service while the excess"]
[0.5,null,null,null,null,null,null,null,null,null,"WC plan competes on price alone."]
[]
[null,null,null,null,null,null,null,null,null,null,"Alice: \"This was a tricky question because excess Workers' Compensation policies are "]
[null,null,null,null,null,null,null,null,null,null,"said to have a retention rather than a deductible. Make sure you state any assumptions"]
[null,null,null,null,null,null,null,null,null,null,"you make on the exam.\""]

{"sheet":"16","index":19,"n_rows":41,"n_cols":15,"rows_kept":41,"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)","Expiring Structure"]
["Points","An actuary is given the following expiring policy information for a Workers' Compensation"]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('16'!A1,'Point Grid'!A:A,0))","v":3.75},"Large Dollar Deductible policy:"]
[null,500000,"Expected Total Loss & ALAE"]
[null,100000,"Deductible",null,null,null,null,null,"Where LBA is the loss based assessment factor."]
[null,0.4,"Percentage of Loss & ALAE Excess of $100,000"]
[null,0.2,"Percentage of Loss & ALAE Excess of $200,000",null,null,null,null,null,"The expiring policy has a $100,000 deductible so the"]
[null,0.05,"ULAE",null,null,null,null,null,"Expected Excess Loss =",null,null,{"$f":"B7*B5","v":200000}]
[null,0.03,"Loss Based Assessment Factor"]
[null,0.17,"Profit and Variable Expenses",null,null,null,null,null,"Hence the expected limited loss =",null,null,null,{"$f":"B5-L9","v":300000}]
[null,15000,"Fixed Expense"]
[null,300000,"Aggregate Deductible",null,null,null,null,null,"Since we have an aggregate deductible and a per-occurrence deductible we need to calculate"]
[null,null,null,null,null,null,null,null,"a limited entry ratio = Aggregate Deductible / Expected Limited Loss"]
[null,"There are no changes to these expenses and profit provision. The table of Insurance "]
[null,"Charges is displayed below:",null,null,null,null,null,null,"Limited Entry Ratio = ",null,null,{"$f":"B13/M11","v":1}]
[null,"Modified Table M for Similarly Sized Policies"]
[null,null,"Deductible",null,null,null,null,null,"Using the Modified Table M to look up the limited entry ratio and deductible yields"]
[null,"Entry Ratio",100000,200000,300000,400000,null,null,"an insurance charge of",null,null,{"$f":"C21","v":0.33}]
[null,0.5,0.45,0.48,0.495,0.505]
[null,1,0.33,0.35,0.36499999999999999,0.37,null,null,"Expiring LDD Premium =",null,null,{"$f":"((L9+L19*M11)+B5*(B9+B10)+B12)/(1-B11)","v":426506.02409638558}]
[null,1.5,0.27,0.30299999999999999,0.32500000000000001,0.35]
[null,2,0.185,0.24,0.26,0.28599999999999998,null,null,"Option 1 Pricing"]
[null,null,null,null,null,null,null,null,"$200,000 deductible so the expected excess loss is",null,null,null,null,{"$f":"B8*B5","v":100000}]
[null,"The insured would like to retain more risk and requests the price of the following options:",null,null,null,null,null,null,"Expected Limited Loss =",null,null,{"$f":"B5-N24","v":400000}]
[null,"Option 1: Deductible of $200,000 with an aggregate deductible of $400,000."]
[null,null,null,null,null,null,null,null,"New aggregate deductible =",null,null,400000]
[null,"Option 2: Excess Policy with self-insured retention of $200,000 on a per-occurrence basis.",null,null,null,null,null,null,"Limited Entry Ratio =",null,null,{"$f":"L27/L25","v":1}]
[null,null,null,null,null,null,null,null,"Using the Modified Table M to look up the insurance charge of",null,null,null,null,null,{"$f":"D21","v":0.35}]
["(a)","Calculate the difference in price between Option 1 and the expiring structure."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$30,MATCH($A$1,'Point Grid'!$A$8:$A$30,0),MATCH(A30,'Point Grid'!$C$7:$I$7,0))","v":2.75},"Use linear interpolation as needed.",null,null,null,null,null,null,"Option 1 LDD Premium =",null,null,{"$f":"((N24+O29*L25)+B5*(B9+B10)+B12)/(1-B11)","v":355421.68674698798}]
[2.75]
[null,null,null,null,null,null,null,null,{"$f":"\"So Option 1 is \" &TEXT(L21-L31,\"$0,000.00\")&\" cheaper than the expiring policy.\"","v":"So Option 1 is $71,084.34 cheaper than the expiring policy."}]
["(b)","The insurer observed an upward trend of ground-up losses in the insured's industry for"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$30,MATCH($A$1,'Point Grid'!$A$8:$A$30,0),MATCH(A34,'Point Grid'!$C$7:$I$7,0))","v":1},"the most recent year, but is unsure if this trend will continue in the future. Based on this",null,null,null,null,null,"(b)","Recommend Option 1 from the insurer's perspective because an upward trend in the ground-up "]
[1,"observation, recommend one of the two options for the insurer and fully support the",null,null,null,null,null,null,"losses will be amplified in an excess layer. This is because claims already in the excess layer "]
[null,"recommendation.",null,null,null,null,null,null,"have the trend apply entirely in the excess layer, while claims just below the layer will be "]
[null,null,null,null,null,null,null,null,"pushed into the excess layer. The insurer will handle all claims under the LDD policy so has"]
[null,null,null,null,null,null,null,null,"an incentive to keep costs down and offset the observed trend."]
[null,null,null,null,null,null,null,null,"Further, since LDD policies compete on both price and service, they could support a more "]
[null,null,null,null,null,null,null,null,"conservative profit and expense load which would hedge against the observed trend."]

{"sheet":"17","index":20,"n_rows":37,"n_cols":13,"rows_kept":37,"cut":false,"hidden":false,"merged":[[0,7,0,8],[16,7,17,7],[16,3,17,3],[16,2,17,2],[16,4,17,4],[16,5,17,5],[16,6,17,6]]}
[17,"Finished",null,null,null,null,null,"Navigator",null,null,"Solution"]
[null,null,null,null,null,null,null,null,null,"(a)","E[PT] =",{"$f":"(G21+D21)/(H21+E21)","v":1.125E-2}]
["Points","The original 2016 Q17 is no longer on the Exam 8 Syllabus. It has been replaced with 2015 Q5 which has",null,null,null,null,null,null,null,null,"E[F] =",{"$f":"(C21+F21)/(E21+H21)","v":2E-3}]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('17'!A1,'Point Grid'!A:A,0))","v":2.5},"the same point value."]
[null,null,null,null,null,null,null,null,null,null,"E[Class 2 PT] = E[PT] + wF*(F2 - E[F]) + wPT*(PT2 - E[PT])"]
[null,"An actuary estimated the loss cost for Workers' Compensation insurance using a ",null,null,null,null,null,null,null,null,"Here F means Fatal claims, PT means Permanent Total claims, and a subscript 2"]
[null,"multi-dimensional credibility method.",null,null,null,null,null,null,null,null,"indicates the actual observed class 2 value"]
[]
[null,"Given the following:",null,null,null,null,null,null,null,null,"wF =",{"$f":"C25","v":0.2}]
[null,"   • There were 2 classes in Hazard Group X.",null,null,null,null,null,null,null,null,"wPT =",{"$f":"D25","v":0.3}]
[null,"   • There were no major or minor permanent partial losses."]
[null,"   • Premium information was not available.",null,null,null,null,null,null,null,null,"F2 = ",{"$f":"(F20+C20)/(E20+H20)","v":2.5000000000000001E-3}]
[null,"   • Holdout sample of odd years was used as a proxy of the true mean.",null,null,null,null,null,null,null,null,"PT2 =",{"$f":"(G20+D20)/(E20+H20)","v":1.15E-2}]
[]
[null,"Claim Count by Injury Type for Hazard Group X",null,null,null,null,null,null,null,null,"E[Class 2 PT] =",null,{"$f":"L2+L9*(L12-L3)+L10*(L13-L2)","v":1.1424999999999999E-2}]
[null,null,"Even Year 1",null,null,"Even Year 2"]
[null,null,"Fatal (F)","Permanent Total (PT)","Temporary Total (TT)","Fatal (F)","Permanent Total (PT)","Temporary Total (TT)",null,"(b)","Predict the frequency for each class using the credibility procedure."]
[null,"Class",null,null,null,null,null,null,null,null,"Rank the classes from smallest to largest by credibility relativity."]
[null,1,2,10,1000,1,12,1000,null,null,"Group the classes into quintiles."]
[null,2,3,10,1000,2,13,1000,null,null,"Compute the sum of squared errors using the holdout sample for each of"]
[null,"Total",{"$f":"SUM(C19:C20)","v":5},20,2000,3,25,2000,null,null,"• The credibility procedure"]
[null,null,null,null,null,null,null,null,null,null,"• The raw data"]
[null,"Optimal Weights for Estimation of Permanent Total Injury Ratio",null,null,null,null,null,null,null,null,"• The hazard group relativity (all class average)"]
[null,null,"Fatal","Permanent Total",null,null,null,null,null,null,"If the credibility procedure is accurate then the credibility procedure should"]
[null,null,0.2,0.3,null,null,null,null,null,null,"have the lowest sum of squared errors."]
[]
["(a)","Determine the ratio of permanent total injury to temporary total injury for Class 2 using a ",null,null,null,null,null,null,null,"(c)","Each class is relatively small compared to the hazard group and the results can"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$30,MATCH($A$1,'Point Grid'!$A$8:$A$30,0),MATCH(A27,'Point Grid'!$C$7:$I$7,0))","v":1},"multi-dimensional credibility method.",null,null,null,null,null,null,null,null,"be volatile from class to class. Grouping into quintiles allows for a more"]
[1,null,null,null,null,null,null,null,null,null,"credible evaluation of the results."]
[]
["(b)","Fully describe the steps involved in performing a quintile test to evaluate the actuary's work."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$30,MATCH($A$1,'Point Grid'!$A$8:$A$30,0),MATCH(A31,'Point Grid'!$C$7:$I$7,0))","v":1}]
[1]
[]
["(c)","Briefly describe one shortcoming of the individual class sum of squared errors test and briefly describe"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$30,MATCH($A$1,'Point Grid'!$A$8:$A$30,0),MATCH(A35,'Point Grid'!$C$7:$I$7,0))","v":0.5},"why the quintiles test is a better way to evaluate the actuary's work."]
[0.5]

{"sheet":"18","index":21,"n_rows":14,"n_cols":13,"rows_kept":14,"cut":false,"hidden":false,"merged":[[0,8,0,10]]}
[18,"Finished",null,null,null,null,null,null,"Navigator",null,null,null,"Solution"]
[null,null,null,null,null,null,null,null,null,null,null,"(a)","Hazard Module:"]
["Points","Catastrophe models were built to assist the insurance industry in quantifying the risk of",null,null,null,null,null,null,null,null,null,null,"Epistemic risk is uncertainty due to limited scientific knowledge about the occurrence and strength"]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('18'!A1,'Point Grid'!A:A,0))","v":2},"natural disasters.",null,null,null,null,null,null,null,null,null,null,"of earthquakes."]
[null,null,null,null,null,null,null,null,null,null,null,null,"Inventory Module:"]
["(a)","For any two of the four basic modules of a catastrophe model, provide an example of ",null,null,null,null,null,null,null,null,null,null,"Epistemic risk is uncertainty due to insufficient knowledge of individual building construction and"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$30,MATCH($A$1,'Point Grid'!$A$8:$A$30,0),MATCH(A6,'Point Grid'!$C$7:$I$7,0))","v":1},"epistemic risk.",null,null,null,null,null,null,null,null,null,null,"lack of accurate contents knowledge."]
[1]
[null,null,null,null,null,null,null,null,null,null,null,"(b)","Hazard Module:"]
["(b)","For any two of the four basic modules of a catastrophe model, provide an example of",null,null,null,null,null,null,null,null,null,null,"Aleatory risk is randomness in the process, such as an earthquake being much stronger or weaker"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$30,MATCH($A$1,'Point Grid'!$A$8:$A$30,0),MATCH(A10,'Point Grid'!$C$7:$I$7,0))","v":1},"aleatory risk.",null,null,null,null,null,null,null,null,null,null,"than expected."]
[1,null,null,null,null,null,null,null,null,null,null,null,"Loss Module:"]
[null,null,null,null,null,null,null,null,null,null,null,null,"The losses on a homeowners portfolio after an earthquake might be higher than expected"]
[null,null,null,null,null,null,null,null,null,null,null,null,"due to random chance rather than an underlying cause that wasn't included in the model."]

{"sheet":"19","index":22,"n_rows":43,"n_cols":16,"rows_kept":43,"cut":false,"hidden":false,"merged":[[0,6,0,7]]}
[19,"Finished",null,null,null,null,"Navigator",null,null,"Solution"]
[null,null,null,null,null,null,null,null,"(a)","Option 1"]
["Points","In order for an insurance company to increase its return on capital, two reinsurance ",null,null,null,null,null,null,null,"Ceded Loss",null,null,"Ceded ALAE = (Ceded Loss / Total Loss )*(Total ALAE)"]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('19'!A1,'Point Grid'!A:A,0))","v":3},"options are being considered:",null,null,null,null,null,null,null,"Claim 1",{"$f":"MIN(F18-5*10^6,5*10^6)","v":2500000},null,"Claim 1",{"$f":"K4/F18*G18","v":500000}]
[null,"   i. $5 million excess of $5 million per risk:",null,null,null,null,null,null,null,"Claim 2",{"$f":"MIN(F19-5*10^6,5*10^6)","v":5000000},null,"Claim 2",{"$f":"K5/F19*G19","v":250000}]
[null,"       • ALAE pro-rata",null,null,null,null,null,null,null,"Total",{"$f":"SUM(K4:K5)","v":7500000},null,"Total",{"$f":"SUM(N4:N5)","v":750000}]
[null,"       • Rate = 18% of premium"]
[null,null,null,null,null,null,null,null,null,"A 1-in-100 year probable maximum loss corresponds to a 1% aggregate exceedance probability."]
[null," ii. 20% Quota Share:",null,null,null,null,null,null,null,"Using the net of excess of loss AEP curve, this corresponds to a loss of at least $70 million."]
[null,"      • Ceding commission of 30%",null,null,null,null,null,null,null,"1-in-100 year loss",null,70000000]
[null,"      • Maximum ceded loss ratio of 150%"]
[null,null,null,null,null,null,null,null,null,"The ceded premium for the excess of loss treaty is",null,null,null,null,{"$f":"18%*B16","v":9000000}]
[null,"The insurance company must hold capital to support a 1-in-100 year Probable Maximum",null,null,null,null,null,null,null,"The retained premium for the excess of loss treaty is",null,null,null,null,null,{"$f":"B16-O12","v":41000000}]
[null,"Loss. Additionally, the following information regarding the insurance company's",null,null,null,null,null,null,null,"The retained Loss & ALAE is",null,null,{"$f":"B18-K6-N6","v":21750000}]
[null,"performance last year is given:"]
[null,50000000,"Premium",null,"Claims greater than $5,000,000",null,null,null,null,"Option 1 Profit =",null,{"$f":"P13-M14-B17","v":4250000}]
[null,15000000,"Expenses",null,null,"Loss","ALAE",null,null,"The Return on Equity for Option 1 is the profit divided by the capital the insurer must hold."]
[null,30000000,"Total Loss & ALAE",null,"Claim 1",7500000,1500000]
[null,0.05,"Return on Capital",null,"Claim 2",10000000,500000,null,null,"Option 1 ROE = ",null,{"$f":"L16/L10","v":6.0714285714285714E-2}]
[null,null,null,null,null,null,null,null,null,"Return on Capital =",null,{"$f":"B19","v":0.05}]
[null,"The Aggregate Exceedance Probability curve for the insurance company is shown below:",null,null,null,null,null,null,null,"Impact on ROE = ",null,{"$f":"L19-L20","v":1.0714285714285711E-2}]
[]
[null,null,null,null,null,null,null,null,null,"Option 2"]
[null,null,null,null,null,null,null,null,null,"Implicit ALAE is included in the quota share agreement."]
[null,null,null,null,null,null,null,null,null,"Ceded Premium =",null,null,{"$f":"20%*B16","v":10000000}]
[null,null,null,null,null,null,null,null,null,"Maximum Ceded Loss & ALAE =",null,null,{"$f":"150%*M25","v":15000000}]
[null,null,null,null,null,null,null,null,null,"Ceded Loss & ALAE =",null,null,{"$f":"MIN(M26,20%*B18)","v":6000000}]
[]
[null,null,null,null,null,null,null,null,null,"Retained Premium =",null,null,{"$f":"B16-M25","v":40000000}]
[null,null,null,null,null,null,null,null,null,"Retained Loss & ALAE =",null,null,{"$f":"B18-M27","v":24000000}]
[null,null,null,null,null,null,null,null,null,"Expenses =",null,null,{"$f":"B17","v":15000000}]
[null,null,null,null,null,null,null,null,null,"Ceding Commission =",null,null,{"$f":"30%*M25","v":3000000}]
[null,null,null,null,null,null,null,null,null,"Option 2 Profit =",null,null,{"$f":"M29-M30-M31+M32","v":4000000}]
[]
[null,null,null,null,null,null,null,null,null,"Using the graph provided we have"]
[null,null,null,null,null,null,null,null,null,"With no reinsurance and a 1-in-100 year PML requirement the insurer would experience a $100 million loss"]
[null,null,null,null,null,null,null,null,null,"With the quota share treaty in place, the insurer would experience a $100m - $15m = $85 million loss."]
["(a)","Determine the impact each reinsurance option would have had on last year's return on"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$30,MATCH($A$1,'Point Grid'!$A$8:$A$30,0),MATCH(A38,'Point Grid'!$C$7:$I$7,0))","v":3},"capital. Ignore investment income and taxes.",null,null,null,null,null,null,null,"Option 2 ROE = ",null,{"$f":"M33/85000000","v":4.7058823529411764E-2}]
[3,null,null,null,null,null,null,null,null,"Return on Capital",null,{"$f":"B19","v":0.05}]
[null,null,null,null,null,null,null,null,null,"Impact on ROE =",null,{"$f":"L39-L40","v":-2.9411764705882387E-3}]
[]
[null,null,null,null,null,null,null,null,null,"Hence Option 1 provides an increase to the return on equity whereas Option 2 provides a decrease."]

{"sheet":"20","index":23,"n_rows":35,"n_cols":14,"rows_kept":35,"cut":false,"hidden":false,"merged":[[0,5,0,6]]}
[20,"Finished",null,null,null,"Navigator",null,null,"Solution"]
[null,null,null,null,null,null,null,"(a)",null,"A","B","C","Primary"]
["Points","A primary insurer has entered into property catastrophe excess of loss treaties with three ",null,null,null,null,null,null,"Loss","100 xs 200","100 xs 300","100 xs 400","Insurer","Total"]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('20'!A1,'Point Grid'!A:A,0))","v":3},"reinsurers. The terms of the treaties are as follows:",null,null,null,null,null,null,1,{"$f":"MAX(0,MIN(100,D13-200))*100%","v":100},{"$f":"MAX(0,MIN(100,D13-300))*100%","v":80},{"$f":"MAX(0,MIN(100,D13-400))*100%","v":0},{"$f":"D13-SUM(J4:L4)","v":200},{"$f":"SUM(J4:M4)","v":380}]
[null,null,null,null,null,null,null,null,2,{"$f":"MAX(0,MIN(100,D14-200))*100%","v":60},{"$f":"MAX(0,MIN(100,D14-300))*100%","v":0},{"$f":"MAX(0,MIN(100,D14-400))*100%","v":0},{"$f":"D14-SUM(J5:L5)","v":200},{"$f":"SUM(J5:M5)","v":260}]
[null,"Reinsurer A:    100% of $100 excess of $200 written at a nominal rate on line of 10%.",null,null,null,null,null,null,3,{"$f":"MAX(0,MIN(100-J5,D15-200))*100%","v":40},{"$f":"MAX(0,MIN(100-K5,D15-300))*100%","v":100},{"$f":"MAX(0,MIN(100-L5,D15-400))*100%","v":100},360,600]
[null,"Reinsurer B:    100% of $100 excess of $300 written at a nominal rate on line of 7%."]
[null,"Reinsurer C:    100% of $100 excess of $400 written at a nominal rate on line of 4%.",null,null,null,null,null,null,"For Loss 3, there is no further reinstatement, so all that is available is what is left of the layer."]
[]
[null,"Each treaty specifies a single mandatory reinstatement that is 100% pro-rata as to amount.",null,null,null,null,null,"(b)","Premium = Rate on Line % * Limit"]
[null,"The primary insurer incurs three covered loss events during the contract period:",null,null,null,null,null,null,"Reinstatement Premium = Premium * Layer Coverage % * (Covered Loss / Limit)"]
[null,null,"Loss Number","Loss Amount",null,null,null,null,"Assume only one reinstatement is possible (the mandatory reinstatement)"]
[null,null,1,380,null,null,null,null,"Notice reinstatements are 100% pro-rata as to amount, a reinstatement fee is assessed after each"]
[null,null,2,260,null,null,null,null,"loss until the original treaty and its replacement have been exhausted."]
[null,null,3,600]
[null,null,null,null,null,null,null,null,"Reinsurer A"]
["(a)","For each loss event, calculate the amount of loss retained by the primary insurer.",null,null,null,null,null,null,"Premium =",null,{"$f":"10%*100","v":10}]
[{"$f":"INDEX('Point Grid'!$C$8:$I$30,MATCH($A$1,'Point Grid'!$A$8:$A$30,0),MATCH(A17,'Point Grid'!$C$7:$I$7,0))","v":0.75},null,null,null,null,null,null,null,"Event 1 reinstatement premium =",null,null,{"$f":"K17*100%*J4/100","v":10}]
[0.75,null,null,null,null,null,null,null,"Event 2, no reinstatement fee as coverage not exhausted"]
[null,null,null,null,null,null,null,null,"Event 3, no reinstatements left."]
["(b)","For each loss event, calculate the amount of reinstatement premium owed by the primary ",null,null,null,null,null,null,"Total Reinstatement Premium for Reinsurer A = ",null,null,null,null,{"$f":"L18","v":10}]
[{"$f":"INDEX('Point Grid'!$C$8:$I$30,MATCH($A$1,'Point Grid'!$A$8:$A$30,0),MATCH(A21,'Point Grid'!$C$7:$I$7,0))","v":2.25},"insurer to each of its reinsurers."]
[2.25,null,null,null,null,null,null,null,"Reinsurer B"]
[null,null,null,null,null,null,null,null,"Premium =",null,{"$f":"7%*100","v":7.0000000000000009}]
[null,null,null,null,null,null,null,null,"Event 1 reinstatement premium =",null,null,{"$f":"K24*K4/100*100%","v":5.6000000000000014},"Treaty not exhausted"]
[null,null,null,null,null,null,null,null,"Event 2 reinstatement premium =",null,null,0,"Loss below treaty"]
[null,null,null,null,null,null,null,null,"Event 3 reinstatement premium =",null,null,{"$f":"K24*(100-K4)/100*100%","v":1.4000000000000004},"Exhausts $20 from original treaty, and $80 from reinstatement"]
[null,null,null,null,null,null,null,null,"Total Reinstatement Premium for Reinsurer B = ",null,null,null,null,{"$f":"SUM(L25:L27)","v":7.0000000000000018}]
[]
[null,null,null,null,null,null,null,null,"Reinsurer C"]
[null,null,null,null,null,null,null,null,"Premium =",null,{"$f":"4%*100","v":4}]
[null,null,null,null,null,null,null,null,"Event 1 reinstatement premium =",null,null,0,"Loss below layer"]
[null,null,null,null,null,null,null,null,"Event 2 reinstatement premium =",null,null,0,"Loss below layer"]
[null,null,null,null,null,null,null,null,"Event 3 reinstatement premium =",null,null,{"$f":"K31*L6/100*100%","v":4}]
[null,null,null,null,null,null,null,null,"Total Reinstatement Premium for Reinsurer C = ",null,null,null,null,{"$f":"SUM(L32:L34)","v":4}]

{"sheet":"21","index":24,"n_rows":34,"n_cols":13,"rows_kept":34,"cut":false,"hidden":false,"merged":[[0,4,0,5],[7,3,8,3],[7,2,8,2],[7,4,8,4],[14,2,15,2],[14,3,15,3],[14,4,15,4]]}
[21,"Finished",null,null,"Navigator",null,null,"Solution"]
[null,null,null,null,null,null,"(a)","Insured Value Range",null,null,"Average in Range"]
["Points","An insurance company insures high value homes and plans to increase the maximum ",null,null,null,null,null,"1m to 4m",null,null,{"$f":"AVERAGE(1*10^6,4*10^6)","v":2500000}]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('21'!A1,'Point Grid'!A:A,0))","v":4},"property value they will insure next year. The company is considering purchasing a new"]
[null,"$4,000,000 excess of $4,000,000 reinsurance treaty. The reinsurer is given the following",null,null,null,null,null,"Percent of Cumulative Loss under $1 million"]
[null,"limit profile:",null,null,null,null,null,"E[X; 1million] / E[X] =",null,null,{"$f":"(E17+D18*1000000)/E19","v":0.8}]
[]
[null,null,"Insured Value Range","Experience Period On-Level Premium","Treaty Subject Premium",null,null,"Average Percent of Insured Value at $1 million"]
[null,null,null,null,null,null,null,{"$f":"10^6/K3","v":0.4}]
[null,null,"$1,000,000 to $4,000,000",100000000,25000000]
[null,null,"$4,000,001 to $8,000,000",0,5000000,null,null,"The above tells us that 40% of the insured value has 80% of the cumulative loss."]
[null,null,null,null,null,null,null,"This matches better with the Y4 curve (40% of the insured value has 82% of the cumulative"]
[null,"A reinsurance actuary has trended and developed the portfolio's historical losses for the",null,null,null,null,null,"loss) rather than the Y3 curve."]
[null,"Experience Period:"]
[null,null,"Loss Size","Number of Claims","Experience Period Ground-up Loss",null,null,"Use the Y4 curve, so c = 4. Then"]
[null,null,null,null,null,null,null,"b(4) =",{"$f":"EXP(3.1-0.15*4*(1+4))","v":1.1051709180756477}]
[null,null,"Less than $1,000,000",200,22000000,null,null,"g(4) =",{"$f":"EXP(4*(0.78+0.12*4))","v":154.47001502589086}]
[null,null,"Greater than $1,000,000",10,18000000]
[null,null,"Total",210,40000000,null,null,"We want to price the layer 4million to 8 million."]
[null,null,null,null,null,null,null,"Average insured value in range"]
[null,"The actuary is unsure if a Swiss Re Y3 or Y4 exposure curve is a better fit. The following",null,null,null,null,null,{"$f":"AVERAGE(4*10^6, 8*10^6)","v":6000000}]
[null,"MBBEFD exposure curve formulas are available along with the following information:"]
[null,null,null,null,null,null,null,"Maximum exposure is G(8million/6million) = G(1.333) = ",null,null,null,null,1]
[null,null,null,null,null,null,null,"Minimum exposure is G(4million/6million) = G(0.667) = ",null,null,null,null,{"$f":"LN(((I17-1)*I16+(1-I17*I16)*I16^0.6666667)/(1-I16))/LN(I17*I16)","v":0.91842620156587118}]
[]
[null,null,null,"% of Cumulative Loss",null,null,null,"Ground up loss ratio =",null,null,{"$f":"E19/SUM(D10:D11)","v":0.4}]
[null,null,"% of Insured Value","Y3","Y4"]
[null,null,0.25,0.6,0.73,null,null,"Losses covered by treaty =",null,null,{"$f":"(M23-M24)*E11*K26","v":163147.59686825765}]
[null,null,0.4,0.72,0.82]
[null,null,1,1,1,null,null,"Ceded Premium =",null,null,{"$f":"1%*SUM(E10:E11)","v":300000}]
[null,null,null,null,null,null,null,"Ceded Loss Ratio =",null,null,{"$f":"K28/K30","v":0.54382532289419216}]
["(a)","Given a proposed treaty rate of 1% of total subject premium, calculate the expected ceded"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$30,MATCH($A$1,'Point Grid'!$A$8:$A$30,0),MATCH(A32,'Point Grid'!$C$7:$I$7,0))","v":4},"loss ratio for the new treaty."]
[4]