{"text_format":"1","workbook":"xlsx","n_sheets":24,"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"],"not_rendered":{"charts":6,"images":6,"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":31,"n_cols":19,"rows_kept":31,"cut":false,"hidden":false,"merged":[[5,1,6,1]]}
[]
[]
[]
[]
["2017 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":8.5},1.5,2,1.5,1.5,1,1,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},2,1.5,1.5,1,1,""]
[2,{"$f":"SUM(C9:I9)","v":2},1.75,0.25,null,null,null,null,null,{"$f":"INDIRECT(\"'\"&A9&\"'!B1\")","v":"Finished"},null,{"$f":"A9","v":2},1.75,0.25,"","","","",""]
[3,1.5,0.75,0.75,null,null,null,null,null,"Finished",null,3,0.75,0.75,"","","","",""]
[4,1.75,0.5,0.75,0.5,null,null,null,null,"Finished",null,4,0.5,0.75,0.5,"","","",""]
[5,1.75,1.75,null,null,null,null,null,null,"Finished",null,5,1.75,"","","","","",""]
[6,3.5,1,1.5,0.5,0.5,null,null,null,"Finished",null,6,1,1.5,0.5,0.5,"","",""]
[7,1.75,1,0.75,null,null,null,null,null,"Finished",null,7,1,0.75,"","","","",""]
[8,1.75,0.75,1,null,null,null,null,null,"Finished",null,8,0.75,1,"","","","",""]
[9,3.25,3.25,null,null,null,null,null,null,"Finished",null,9,3.25,"","","","","",""]
[10,1,1,null,null,null,null,null,null,"Finished",null,10,1,"","","","","",""]
[11,2.5,1.5,1,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":1.5},1,"","","","",""]
[12,3.25,1,0.5,0.5,1.25,null,null,null,"Finished",null,12,1,0.5,0.5,1.25,"","",""]
[13,2.5,2,0.5,null,null,null,null,null,"Finished",null,13,2,0.5,"","","","",""]
[14,3,1.5,1.5,null,null,null,null,null,"Finished",null,14,1.5,1.5,"","","","",""]
[15,3.25,1.75,1,0.5,null,null,null,null,"Finished",null,15,1.75,1,0.5,"","","",""]
[16,1.75,0.5,1.25,null,null,null,null,null,"Finished",null,16,0.5,1.25,"","","","",""]
[17,2,1.5,0.5,null,null,null,null,null,"Finished",null,17,1.5,0.5,"","","","",""]
[18,{"$f":"SUM(C25:I25)","v":3.25},2,0.75,0.5,null,null,null,null,"Finished",null,{"$f":"A25","v":18},2,0.75,0.5,"","","",""]
[19,3,1.5,0.5,1,null,null,null,null,"Finished",null,19,1.5,0.5,1,"","","",""]
[20,2.5,0.75,1.75,null,null,null,null,null,"Finished",null,20,0.75,1.75,"","","","",""]
[]
["Total",{"$f":"SUM(B8:B27)","v":53.75},null,null,null,null,null,null,null,null,null,"TOTAL",{"$f":"IF(SUM(IF(M8:S27=\"\",0,1))=0,\"\",SUM(M8:S27))","v":53.75},null,"Percentage:",{"$f":"IF(M29=\"\",\"\",M29/B29)","v":1}]
[]
[null,null,null,null,null,null,null,null,null,null,null,"Passing Score:",37.5]

{"sheet":"Navigator","index":3,"n_rows":28,"n_cols":8,"rows_kept":28,"cut":false,"hidden":false,"merged":[[19,0,19,6],[8,0,8,6],[9,0,9,6],[10,0,10,6],[11,0,11,6],[12,0,12,6],[13,0,13,6],[14,0,14,6],[15,0,15,6],[16,0,16,6],[17,0,17,6],[18,0,18,6],[26,0,26,6],[27,0,27,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]]}
[]
[]
[]
[]
[]
["Navigator – Select a question to go to it"]
[]
["Question #",null,null,null,null,null,null,"Status"]
["Q1 -- Goldburd (GLM) -- Variable Selection (IQ) -- 8.50 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J8","v":"Finished"}]
["Q2 -- Robertson -- Weighted k-means -- 2.00 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J9","v":"Finished"}]
["Q3 -- Bailey & Simon -- Relative Credibility -- 1.50 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J10","v":"Finished"}]
["Q4 -- Goldburd (GLM) -- Model Selection -- 1.75 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J11","v":"Finished"}]
["Q5 -- Goldburd (GLM) -- Null, Saturated & Practical Models -- 1.75 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J12","v":"Finished"}]
["Q6 -- Goldburd (GLM) -- Logistic Regression --3.50 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J13","v":"Finished"}]
["Q7 -- Bahnemann -- Risk Loaded ILFs -- 1.75 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J14","v":"Finished"}]
["Q8 -- Bahnemann -- Layer Inflation -- 1.75 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J15","v":"Finished"}]
["Q9 -- ISO CGL -- Experience Modified Premium -- 3.25 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J16","v":"Finished"}]
["Q10 -- ISO CGL -- Schedule Rating -- 1.00 point",null,null,null,null,null,null,{"$f":"'Point Grid'!J17","v":"Finished"}]
["Q11 -- Fisher -- Efficiency Test -- 2.50 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J18","v":"Finished"}]
["Q12 -- Fisher -- Lee Diagrams -- 3.25 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J19","v":"Finished"}]
["Q13 -- Fisher -- Retrospective Premium -- 2.50 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J20","v":"Finished"}]
["Q14 -- Bahnemann -- Excess Severity -- 3.00 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J21","v":"Finished"}]
["Q15 -- Fisher -- Retrospective Rating Cashflow -- 3.25 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J22","v":"Finished"}]
["Q16 -- Clark -- Modeling Approaches -- 1.75 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J23","v":"Finished"}]
["Q17 -- Fisher -- ICRLL Adjustment -- 2.00 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J24","v":"Finished"}]
["Q18 -- Bernegger -- Exposure Curves -- 3.25 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J25","v":"Finished"}]
["Q19 -- Clark -- Swing Plans -- 3.00 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J26","v":"Finished"}]
["Q20 -- Grossi -- Risk Loaded Premiums -- 3.00 points",null,null,null,null,null,null,{"$f":"'Point Grid'!J27","v":"Finished"}]

{"sheet":"1","index":4,"n_rows":82,"n_cols":17,"rows_kept":82,"cut":false,"hidden":false,"merged":[[0,7,0,8]]}
[1,"Finished",null,null,null,null,null,"Navigator",null,null,"Solution"]
[null,null,null,null,null,null,null,null,null,"(a)","Industry:","This has a low p-value which implies the variable is statistically significant, i.e. a good predictor of future loss."]
["Points","An actuary has constructed a pure premium model using the Tweedie distribution with parameter",null,null,null,null,null,null,null,null,null,"Industry is a variable that is easy and practical to collect and verify."]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('1'!A1,'Point Grid'!A:A,0))","v":8.5},"1 < p < 2 to determine manual rates for a Workers' Compensation book of business. The output of the model "]
[null,"is pure premium per $100 of payroll.",null,null,null,null,null,null,null,null,"Employee Tenure:",null,"The low p-value implies it is significant and a good predictor of future loss."]
[null,null,null,null,null,null,null,null,null,null,null,null,"Employee tenure is directly related to future loss activity as newer employees"]
[null,"The following variables were considered for inclusion in the model:",null,null,null,null,null,null,null,null,null,null,"may not be as experienced with machinary for example."]
[null,"Variable",null,"Description or Source of Data",null,null,"p-value"]
[null,"Industry",null,"Construction, Manufacturing, All Other",null,null,2E-3,null,null,null,"Return to Work Program:"]
[null,"Return to Work Program",null,"Yes or No",null,null,8.0000000000000002E-3,null,null,null,"This has a low p-value so it is significant and a good predictor of future loss activity."]
[null,"Employee Age",null,"Average Age in Years",null,null,3.0000000000000001E-3,null,null,null,"There is a clear relationship between the length of time off of work and the cost occurred"]
[null,"Employee Tenure",null,"Average Number of Years of Employment",null,null,5.0000000000000001E-3,null,null,null,"by the employer. It is a binary variable that is easy to obtain and verify."]
[null,"Location",null,"State of Jurisdiction",null,null,0.08]
[null,"Employee Morale",null,"Based on Results of Annual Company Survey",null,null,0.15,null,null,"(b)","Employee Age:",null,"It has a low p-value implying it is significant and a predictor of future loss."]
[null,"Number of Back Injuries",null,"Supplied by Employer",null,null,0.01,null,null,null,null,null,"However, it may be against the law (discriminatory practice) to use age."]
[]
[null,"The actuary has decided to use the following variables in the model: Industry, Employee Tenure, and",null,null,null,null,null,null,null,null,"Location:","The p-value is high which suggests this variable is not likely to be a significant predictor of future loss activity."]
[null,"Return to Work Program.",null,null,null,null,null,null,null,null,null,"The variable makes intuitive sense as some states have more generous laws for workers' compensation"]
[null,null,null,null,null,null,null,null,null,null,null,"which favor the worker but increase costs for the employer."]
["(a)","Discuss the statistical and non-statistical considerations of including each of the three variables (Industry,"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$29,MATCH($A$1,'Point Grid'!$A$8:$A$29,0),MATCH(A20,'Point Grid'!$C$7:$I$7,0))","v":1.5},"Employee Tenure, and Return to Work Program) in the model.",null,null,null,null,null,null,null,null,"Employee Moral:",null,"The p-value suggests this is not a signficant variable (not a good predictor of future loss)."]
[1.5,null,null,null,null,null,null,null,null,null,null,null,"This variable is highly subjective and time dependent. It would be difficult to objectively collect and verify."]
[]
["(b)","Discuss the statistical and non-statistical considerations of excluding each of the remaining four variables",null,null,null,null,null,null,null,null,"Number of Back Injury Claims:"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$29,MATCH($A$1,'Point Grid'!$A$8:$A$29,0),MATCH(A24,'Point Grid'!$C$7:$I$7,0))","v":2},"(Employee Age, Location, Employee Morale, and Number of Back Injuries) from the model.",null,null,null,null,null,null,null,null,"The p-value is quite small, indicating it is significant and may be a fair"]
[2,null,null,null,null,null,null,null,null,null,"predictor of future loss."]
[null,null,null,null,null,null,null,null,null,null,"The information is supplied by the employer which may make it difficult to "]
["(c)","The actuary fits the log link GLM model using the three selected variables. Given the fitted model parameters",null,null,null,null,null,null,null,null,"verify or open to gaming by the insured to achieve lower rates."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$29,MATCH($A$1,'Point Grid'!$A$8:$A$29,0),MATCH(A28,'Point Grid'!$C$7:$I$7,0))","v":1.5},"below and the following information for a Manufacturing Workers' Compensation risk, calculate the "]
[1.5,"standard premium for this risk for an annual policy period.",null,null,null,null,null,null,null,"(c)","I will treat a manufacturing risk as an \"All Other\" industry type.",null,null,null,null,null,"← Alice: \"The other option is to assume Manufacturing risks are the base class."]
[null,null,null,null,null,null,null,null,null,null,"Treat employee tenure as a discrete variable.",null,null,null,null,null,"since the base class always has 1.000 for its coefficient, it isn't listed.\""]
[null,null,"Parameter",null,"Coefficient"]
[null,null,"Intercept",null,-0.63100000000000001,null,null,null,null,null,"Apply GLM"]
[null,null,"Employee Tenure",null,-0.04,null,null,null,null,null,"ln(u) = -0.631 + -0.04*5 + -0.2*0 + -0.55*1"]
[null,null,"Return to Work Program: Yes",null,-0.2,null,null,null,null,null,"u = ",{"$f":"EXP(E33+E34*5+E35*0+E37*1)","v":0.25132710025405403}]
[null,null,"Industry Type: Construction",null,0.35,null,null,null,null,null,{"$f":"\"i.e. \"&TEXT(L35,\"$0.00\")&\" per $100 of payroll\"","v":"i.e. $0.25 per $100 of payroll"}]
[null,null,"Industry Type: All Other",null,-0.55000000000000004]
[null,null,null,null,null,null,null,null,null,null,"Expected Loss = u * Payroll/$100"]
[null,null,"Data for Manufacturing Risk",null,null,"Value",null,null,null,null,{"$f":"L35*F40/100","v":2513.2710025405404}]
[null,null,"Payroll",null,null,1000000]
[null,null,"Employee Tenure",null,null,"5 Years",null,null,null,null,"Standard Premium = Loss + Fixed Expense + Variable Expense"]
[null,null,"Return to Work Program",null,null,"No",null,null,null,null,"P = Expected Loss + 1500 + 0.2P"]
[null,null,"Actual Losses for Experience Rating",null,null,12500,null,null,null,null,"P = ",{"$f":"(K39+F44)/(1-F45)","v":5016.5887531756753},null,"← Alice: \"This is the manual premium.\""]
[null,null,"Fixed Expenses",null,null,1500]
[null,null,"Variable Expenses (as % of premium)",null,null,0.2,null,null,null,null,"Experience Mod = (Actual ratable loss + K) / (Expected ratable loss + K)"]
[null,null,"Experience rating Constant (K)",null,null,10000,null,null,null,null,{"$f":"(F43+F46)/(K39+F46)","v":1.7980910023791443}]
[]
[null,"The actuary has graphed actual vs. modeled expected loss ratios by size of risk for the company's entire",null,null,null,null,null,null,null,null,"Standard Premium = Manual Premium * Mod"]
[null,"Workers' Compensation book, which is shown below.",null,null,null,null,null,null,null,null,{"$f":"L43*K46","v":9020.2830997215915}]
[]
["(d)","In order to improve the model fit for larger risks, the actuary is considering incorporating the variable",null,null,null,null,null,null,null,"(d)","• The historical loss experience already impacts the premium through experience rating."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$29,MATCH($A$1,'Point Grid'!$A$8:$A$29,0),MATCH(A51,'Point Grid'!$C$7:$I$7,0))","v":1.5},"\"Latest 3 Year Historical Losses\" into the model. Explain three reasons against doing so.",null,null,null,null,null,null,null,null,"Inclusion here would be double counting."]
[1.5,null,null,null,null,null,null,null,null,null,"• Small risks have volatile experience, so incorporating the latest 3 years of historical "]
[null,null,null,null,null,null,null,null,null,null,"losses will cause their premiums to fluctuate."]
[null,null,null,null,null,null,null,null,null,null,"• The latest 3 year losses will not be fully developed. If the reporting lag varies by company"]
[null,null,null,null,null,null,null,null,null,null,"then the loss experience may be over or under predicted."]
[]
[null,null,null,null,null,null,null,null,null,"(e)","• Since the risk is new there is no prior loss history (not even from a previous insurer) to draw on."]
[null,null,null,null,null,null,null,null,null,null,"This makes it difficult to know whether the manual premium is adequate or not when "]
[null,null,null,null,null,null,null,null,null,null,"experience rating."]
[null,null,null,null,null,null,null,null,null,null,"• The high payroll amount puts the risk in a high manual premium range where"]
[null,null,null,null,null,null,null,null,null,null,"the model is less good at predicting the actual losses."]
[]
[null,null,null,null,null,null,null,null,null,"(f)","Use a retrospective rating plan. This would allow premium adjustments as"]
[null,null,null,null,null,null,null,null,null,null,"losses are incurred (or paid), allowing the insured some of the benefits similar to "]
[null,null,null,null,null,null,null,null,null,null,"experience rating. Further, the insured may benefit from a cash flow advantage."]
[null,null,null,null,null,null,null,null,null,null,"The insurer will not need to hold as much capital under this type of plan so they can "]
[null,null,null,null,null,null,null,null,null,null,"write more business and diversify risk further."]
[]
[]
[]
[]
[null,"The actuary is now developing a quote for a new Construction risk with $50,000,000 of payroll using this "]
[null,"rating plan."]
[]
["(e)","Describe two potential issues in developing a premium for this risk under this rating plan."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$29,MATCH($A$1,'Point Grid'!$A$8:$A$29,0),MATCH(A76,'Point Grid'!$C$7:$I$7,0))","v":1}]
[1]
[]
["(f)","Provide an alternative rating approach for this risk and briefly discuss the advantages of this alternative for"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$29,MATCH($A$1,'Point Grid'!$A$8:$A$29,0),MATCH(A80,'Point Grid'!$C$7:$I$7,0))","v":1},"the insured as well as the insurance company."]
[1]

{"sheet":"2","index":5,"n_rows":25,"n_cols":14,"rows_kept":25,"cut":false,"hidden":false,"merged":[[6,1,8,1],[0,7,0,8],[6,2,8,2],[6,3,8,3],[6,4,8,4],[6,5,8,5]]}
[2,"Finished",null,null,null,null,null,"Navigator",null,null,"Solution"]
[null,null,null,null,null,null,null,null,null,"(a)","Since we have two limits, the centroid of each cluster has two coordinates. The first"]
["Points","An actuary wants to cluster five Workers' Compensation classes based on excess ratios at",null,null,null,null,null,null,null,null,"corresponds to the weighted average excess ratio for the 500,000 limit for classes in the cluster,"]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('2'!A1,'Point Grid'!A:A,0))","v":2},"two limits: 500,000 and 1,000,000. The actuary decides to use a weighted k-means",null,null,null,null,null,null,null,null,"while the second corresponds to the weighted average excess ratio for the 1,000,000 limit."]
[null,"algorithm with two clusters. Given the following:"]
[null,null,null,null,null,null,null,null,null,null,"Centroids","Limit"]
[null,"Class","On-Leveled Earned Premium ($000)","Normalized Excess Ratio at 500,000 Limit","Normalized Excess Ratio at 1,000,000 Limit","Initial Cluster",null,null,null,null,"Cluster",500000,1000000]
[null,null,null,null,null,null,null,null,null,null,"A",{"$f":"(C10*D10+C11*D11+C12*D12+C13*D13)/SUM(C10:C13)","v":0.24216216216216216},{"$f":"(C10*E10+C11*E11+C12*E12+C13*E13)/SUM(C10:C13)","v":0.10432432432432433}]
[null,null,null,null,null,null,null,null,null,null,"B",{"$f":"C14*D14/C14","v":0.18},{"$f":"C14*E14/C14","v":7.0000000000000007E-2}]
[null,1,6500,0.24,0.08,"A"]
[null,2,5000,0.35,0.2,"A",null,null,null,null,"We're told to use the Euclidean norm."]
[null,3,4000,0.21,0.08,"A"]
[null,4,3000,0.11,0.03,"A",null,null,null,null,"Class","Dist to A","Dist to B","New Cluster"]
[null,5,5000,0.18,7.0000000000000007E-2,"B",null,null,null,null,1,{"$f":"SQRT((D10-$L$8)^2+(E10-$M$8)^2)","v":2.4420231347192473E-2},{"$f":"SQRT((D10-$L$9)^2+(E10-$M$9)^2)","v":6.0827625302982198E-2},{"$f":"IF(L14<M14,\"A\",\"B\")","v":"A"}]
[null,null,null,null,null,null,null,null,null,null,2,{"$f":"SQRT((D11-$L$8)^2+(E11-$M$8)^2)","v":0.14416252698096299},{"$f":"SQRT((D11-$L$9)^2+(E11-$M$9)^2)","v":0.21400934559032694},{"$f":"IF(L15<M15,\"A\",\"B\")","v":"A"}]
[null,"• Distance will be measured using the L2 (Euclidean) norm.",null,null,null,null,null,null,null,null,3,{"$f":"SQRT((D12-$L$8)^2+(E12-$M$8)^2)","v":4.0324650386334808E-2},{"$f":"SQRT((D12-$L$9)^2+(E12-$M$9)^2)","v":3.1622776601683791E-2},"B"]
[null,"• At the start of the algorithm, the actuary randomly assigns each class to a cluster.",null,null,null,null,null,null,null,null,4,0.15162764356688063,8.06225774829855E-2,"B"]
[null,null,null,null,null,null,null,null,null,null,5,7.1009109591631606E-2,0,"B"]
["(a)","Determine the cluster for each class after the first iteration of the weighted k-means algorithm."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$29,MATCH($A$1,'Point Grid'!$A$8:$A$29,0),MATCH(A19,'Point Grid'!$C$7:$I$7,0))","v":1.75},null,null,null,null,null,null,null,null,"(b)","The L1 measure penalizes many small errors the same amount as one large error."]
[1.75,null,null,null,null,null,null,null,null,null,"This results in outliers having less of an impact on the result."]
[]
["(b)","Briefly describe one advantage of using the L1 measure rather than L2 when computing clusters."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$29,MATCH($A$1,'Point Grid'!$A$8:$A$29,0),MATCH(A23,'Point Grid'!$C$7:$I$7,0))","v":0.25}]
[0.25]

{"sheet":"3","index":6,"n_rows":35,"n_cols":12,"rows_kept":35,"cut":false,"hidden":false,"merged":[[5,1,6,1],[5,2,6,2],[5,3,6,3],[5,4,6,4]]}
[3,"Finished",null,null,null,"Navigator",null,"Solution"]
[null,null,null,null,null,null,"(a)","Using earned premium as the exposure base will only correct for maldistribution due to correlation"]
["Points","The following data shows the experience of a merit rating plan for private passenger ",null,null,null,null,null,"between frequency and territory when:"]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('3'!A1,'Point Grid'!A:A,0))","v":1.5},"vehicles. The merit rating plan uses multiple rating variables, including territory.",null,null,null,null,null,"1. Territory differentials are proper, i.e. all territories have the same loss ratio, and"]
[null,null,null,null,null,null,null,"2. High frequency territories are also high premium territories."]
[null,"Number of Accident-Free Years","Earned Car Years (000s)","Earned Premium ($000s)","Number of Incurred Claims"]
[null,null,null,null,null,null,null,"Here we see Territory C has the highest frequency but not the highest average premium. So 2. doesn't hold."]
[null,"5 or More",250,500000,15000,null,null,"Therefore, it is better to use earned car years as the exposure base."]
[null,"3 and 4",100,90000,13500]
[null,"1 and 2",80,60000,8000,null,"(b)","3+ Claim Frequency:",null,{"$f":"SUM(E8:E9)/(SUM(C8:C9)*1000)","v":8.1428571428571433E-2}]
[null,0,70,50000,10500,null,null,"Overall Claim Freq:",null,{"$f":"E12/(C12*1000)","v":9.4E-2}]
[null,"Total",{"$f":"SUM(C8:C11)","v":500},{"$f":"SUM(D8:D11)","v":700000},{"$f":"SUM(E8:E11)","v":47000},null,null,"Relative Frequency:",null,{"$f":"J10/J11","v":0.86626139817629189}]
[null,null,null,null,null,null,null,"Set Z = 1 - Rel Freq"]
[null,"Territory","Frequency","Average Premium",null,null,null,"Z =",{"$f":"1-J12","v":0.13373860182370811}]
[null,"A",0.05,1500]
[null,"B",0.1,2000,null,null,null,"The question is ambiguous regarding what we should calculate the credibility of the 3+ group against."]
[null,"C",0.15,1250,null,null,null,"I will assume they meant relative to the 1+ years claim free group."]
[null,null,null,null,null,null,null,"1+ Claim Frequency:",null,{"$f":"SUM(E8:E10)/(SUM(C8:C10)*1000)","v":8.4883720930232553E-2}]
["(a)","Recommend and justify an exposure base for this merit rating plan.",null,null,null,null,null,"Relative Frequency:",null,{"$f":"J18/J11","v":0.90301830776843139}]
[{"$f":"INDEX('Point Grid'!$C$8:$I$29,MATCH($A$1,'Point Grid'!$A$8:$A$29,0),MATCH(A19,'Point Grid'!$C$7:$I$7,0))","v":0.75},null,null,null,null,null,null,"Set Z = 1 - Rel Freq"]
[0.75,null,null,null,null,null,null,"Z =",{"$f":"1-J19","v":9.6981692231568606E-2}]
[]
["(b)","Calculate the relative credibility of an exposure that has been three or more years ",null,null,null,null,null,"Relative credibility of 3+ years claims free group to 1+ years claims free group"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$29,MATCH($A$1,'Point Grid'!$A$8:$A$29,0),MATCH(A23,'Point Grid'!$C$7:$I$7,0))","v":0.75},"accident-free using the exposure base from part (a) above.",null,null,null,null,null,{"$f":"I14/I21","v":1.379008746355683}]
[0.75]
[null,null,null,null,null,null,null,"Alice: \"You could have calculated the credibility for the 0 years accident free group"]
[null,null,null,null,null,null,null,"and used that instead when calculating the relativity. Here's how to get there.\""]
[]
[null,null,null,null,null,null,null,"Assume claim frequency has a Poisson distribution. Then mean frequency (for any group is)"]
[null,null,null,null,null,null,null,"λ =",{"$f":"E12/(C12*1000)","v":9.4E-2}]
[null,null,null,null,null,null,null,"The 0 years claims free group has relative claim frequency given by "]
[null,null,null,null,null,null,null,"So R =",{"$f":"1/(1-EXP(-1*I30))","v":11.146130052327516}]
[null,null,null,null,null,null,null,"The experience mod for the group is",null,null,null,{"$f":"(E11/(C11*1000))/(E12/(C12*1000))","v":1.5957446808510638}]
[null,null,null,null,null,null,null,"Using"]
[null,null,null,null,null,null,null,"Z = ",{"$f":"(L33-1)/(I32-1)","v":5.8716444376188566E-2}]

{"sheet":"4","index":7,"n_rows":36,"n_cols":9,"rows_kept":36,"cut":false,"hidden":false,"merged":[]}
[4,"Finished",null,null,null,null,"Navigator",null,"Solution"]
[null,null,null,null,null,null,null,"(a)","• To prevent overfitting. As more parameters are added to the model, the fit on the training"]
["Points","An actuary has split data into training and test groups for a model. The chart below shows",null,null,null,null,null,null,"data set always improves. The test data set may not improve, indicating the model is being"]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('4'!A1,'Point Grid'!A:A,0))","v":1.75},"the relationship between model performance and model complexity. Model performance",null,null,null,null,null,null,"fit to noise in the training data set instead."]
[null,"is represented by model error and model complexity is represented by degrees of freedom.",null,null,null,null,null,null,"• To measure the predictive power of the model. If we try to assess the model on the training"]
[null,null,null,null,null,null,null,null,"data set it will be too optimistic of its performance."]
[]
[null,null,null,null,null,null,null,"(b)","   i. The model isn't optimal because the model error is relatively high for both the train and test"]
[null,null,null,null,null,null,null,null,"       datasets, and we can increase the degrees of freedom in the model and have both model errors decrease."]
[null,null,null,null,null,null,null,null,"  ii. The model is optimal because the model error on the test data set is at its lowest."]
[null,null,null,null,null,null,null,null," iii. The model is overfitted to the training data set because although the model error is lowest"]
[null,null,null,null,null,null,null," ","       for the train data set, the model error on the test data set has increased again."]
[]
[null,null,null,null,null,null,null,"(c)","When the data is impacted by a large weather event (such as a hurricane). Splitting the data"]
[null,null,null,null,null,null,null,null,"by time keeps the event in either the train or the test data set. This prevents producing an"]
[null,null,null,null,null,null,null,null,"overly optimistic view of the model using the test set when losses from the same underlying"]
[null,null,null,null,null,null,null,null,"cause are present in both the train and test data sets."]
[]
[]
[]
[]
[]
[]
["(a)","Briefly describe two reasons for splitting modeling data into training and test groups."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$29,MATCH($A$1,'Point Grid'!$A$8:$A$29,0),MATCH(A24,'Point Grid'!$C$7:$I$7,0))","v":0.5}]
[0.5]
[]
["(b)","Briefly describe whether each of the following model iterations has an optimal balance of"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$29,MATCH($A$1,'Point Grid'!$A$8:$A$29,0),MATCH(A28,'Point Grid'!$C$7:$I$7,0))","v":0.75},"complexity and performance."]
[0.75,"   i. Model iteration 1: 10 degrees of freedom"]
[null,"  ii. Model iteration 2: 60 degrees of freedom"]
[null," iii. Model iteration 3: 100 degrees of freedom"]
[]
["(c)","Identify and briefly describe one situation where it is an advantage to split the data by time"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$29,MATCH($A$1,'Point Grid'!$A$8:$A$29,0),MATCH(A34,'Point Grid'!$C$7:$I$7,0))","v":0.5},"rather than by random assignment."]
[0.5]

{"sheet":"5","index":8,"n_rows":47,"n_cols":11,"rows_kept":47,"cut":false,"hidden":false,"merged":[]}
[5,"Finished",null,null,null,null,"Navigator",null,"Solution"]
[null,null,null,null,null,null,null,"(a)","A saturated model perfectly predicts every result."]
["Points","An analyst has fit several different variations of a logistic GLM to a data set containing 1,000",null,null,null,null,null,"(i.)","We have 10,000 claim records which are perfectly predicted."]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('5'!A1,'Point Grid'!A:A,0))","v":1.75},"records of fraudulent claims and 9,000 records of legitimate claims. For each model variation",null,null,null,null,null,null,"Ordering the claim records by prediction (0 = no fraud, 1 = fraud) from smallest to largest "]
[null,"listed below, draw a quintile plot based on the training data. Label the axes and identify each",null,null,null,null,null,null,"and dividing into 5 quintiles, we see that each quintile has 2,000 claims in it. "]
[null,"data series.",null,null,null,null,null,null,"So the first 4 quintiles consist of all 0 predictions and 0 fraud."]
[null,null,null,null,null,null,null,null,"The last quintile has 1,000 fraud predictions and 1,000 fraud claims out of 2,000."]
["(a)","   i. A saturated model",null,null,null,null,null,null,"This means the % fraud is 50% in the last quintile."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$29,MATCH($A$1,'Point Grid'!$A$8:$A$29,0),MATCH(A8,'Point Grid'!$C$7:$I$7,0))","v":1.75},"  ii. A null model",null,null,null,null,null,null,"Quintile","Expected Fraud","Actual Fraud"]
[1.75," iii. A model that could be used in practice",null,null,null,null,null,null,1,0,0]
[null,null,null,null,null,null,null,null,2,0,0]
[null,null,null,null,null,null,null,null,3,0,0]
[null,null,null,null,null,null,null,null,4,0,0]
[null,null,null,null,null,null,null,null,5,0.5,0.5]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,null,"(ii.)","There is a 10% chance overall of a claim being fraudulent. The null model plots the overall mean."]
[null,null,null,null,null,null,null,null,"So each quintile is predicted to contain 10% fraudulent claims."]
[null,null,null,null,null,null,null,null,"Quintile","Expected Fraud","Actual Fraud"]
[null,null,null,null,null,null,"Alice: \"Notice no quintile can have more than 50% actual fraud because of the data given. ",null,1,0.1,0.23849999999999999]
[null,null,null,null,null,null,"The CAS penalized poor axis values…\"",null,2,0.1,1.55E-2]
[null,null,null,null,null,null,null,null,3,0.1,3.5999999999999997E-2]
[null,null,null,null,null,null,null,null,4,0.1,3.6499999999999998E-2]
[null,null,null,null,null,null,null,null,5,0.1,0.17349999999999999]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,null,"(iii.)","A practical model will accurately predict a large number of the fraudulent claims but not all."]
[null,null,null,null,null,null,null,null,"Quintile","Expected Fraud","Actual Fraud"]
[null,null,null,null,null,null,null,null,1,6.0499999999999998E-2,8.5999999999999993E-2]
[null,null,null,null,null,null,null,null,2,6.5500000000000003E-2,5.6000000000000001E-2]
[null,null,null,null,null,null,null,null,3,0.1075,9.2499999999999999E-2]
[null,null,null,null,null,null,null,null,4,0.1225,0.14899999999999999]
[null,null,null,null,null,null,null,null,5,0.14399999999999999,0.11650000000000001]

{"sheet":"6","index":9,"n_rows":41,"n_cols":15,"rows_kept":41,"cut":false,"hidden":false,"merged":[[5,4,6,4],[5,3,6,3],[5,2,6,2],[15,8,16,8],[15,12,16,12]]}
[6,"Finished",null,null,null,null,"Navigator",null,"Solution",null,"Predicted Above"]
[null,null,null,null,null,null,null,"(a)","Claim","Actual",0.5,0.25]
["Points","A logistic model was built to predict the probability of a claim being fraudulent. Consider the",null,null,null,null,null,null,1,{"$f":"D8","v":"Y"},{"$f":"IF(E8>50%,\"Y\",\"N\")","v":"N"},{"$f":"IF(E8>25%,\"Y\",\"N\")","v":"N"}]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('6'!A1,'Point Grid'!A:A,0))","v":3.5},"predicted probabilities for the 10 claims below to be a representative sample of the total ",null,null,null,null,null,null,2,{"$f":"D9","v":"N"},{"$f":"IF(E9>50%,\"Y\",\"N\")","v":"N"},{"$f":"IF(E9>25%,\"Y\",\"N\")","v":"N"}]
[null,"model.",null,null,null,null,null,null,3,"N","N","N"]
[null,null,"Claim Number","Actual Fraud Indicator","Predicted Probability of Fraud",null,null,null,4,"N","Y","Y"]
[null,null,null,null,null,null,null,null,5,"Y","Y","Y"]
[null,null,1,"Y",0.11,null,null,null,6,"Y","N","Y"]
[null,null,2,"N",0.23,null,null,null,7,"N","N","N"]
[null,null,3,"N",0.15,null,null,null,8,"Y","Y","Y"]
[null,null,4,"N",0.7,null,null,null,9,"N","Y","Y"]
[null,null,5,"Y",0.91,null,null,null,10,"N","N","Y"]
[null,null,6,"Y",0.3]
[null,null,7,"N",0.11,null,null,null,"Confusion Matrix at 0.50",null,null,null,"Confusion Matrix at 0.25"]
[null,null,8,"Y",0.75,null,null,null,null,"Predicted",null,null,null,"Predicted"]
[null,null,9,"N",0.57999999999999996,null,null,null,"Actual",{"$f":"COUNTIFS($K$3:$K$12,\"Y\",$J$3:$J$12,\"Y\")","v":2},{"$f":"COUNTIFS($K$3:$K$12,\"N\",$J$3:$J$12,\"Y\")","v":2},null,"Actual",{"$f":"COUNTIFS($L$3:$L$12,\"Y\",$J$3:$J$12,\"Y\")","v":3},{"$f":"COUNTIFS($L$3:$L$12,\"N\",$J$3:$J$12,\"Y\")","v":1}]
[null,null,10,"N",0.27,null,null,null,null,{"$f":"COUNTIFS($K$3:$K$12,\"Y\",$J$3:$J$12,\"N\")","v":2},{"$f":"COUNTIFS($K$3:$K$12,\"N\",$J$3:$J$12,\"N\")","v":4},null,null,{"$f":"COUNTIFS($L$3:$L$12,\"Y\",$J$3:$J$12,\"N\")","v":3},{"$f":"COUNTIFS($L$3:$L$12,\"N\",$J$3:$J$12,\"N\")","v":3}]
[]
["(a)","Construct confusion matrices for discrimination thresholds of 0.50 and 0.25.",null,null,null,null,null,"(b)","The ROC curve has the sensitivity on the y-axis and the false positive rate on the x-axis."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$29,MATCH($A$1,'Point Grid'!$A$8:$A$29,0),MATCH(A19,'Point Grid'!$C$7:$I$7,0))","v":1},null,null,null,null,null,null,null,"Sensitivity = TP / (TP + FN)"]
[1,null,null,null,null,null,null,null,"Specificity = TN / (TN + FP)"]
[null,null,null,null,null,null,null,null,"False Positive Rate = 1 - Specificity"]
["(b)","Plot the Receiver Operating Characteristic (ROC) curve with the discrimination thresholds of 0.50"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$29,MATCH($A$1,'Point Grid'!$A$8:$A$29,0),MATCH(A23,'Point Grid'!$C$7:$I$7,0))","v":1.5},"and 0.25. Label each axis and the coordinates and discrimination threshold of each point on the",null,null,null,null,null,null,"Threshold","x-coord","y-coord","Line of Equality"]
[1.5,"curve.",null,null,null,null,null,null,0,1,1,{"$f":"I25","v":0}]
[null,null,null,null,null,null,null,null,0.25,{"$f":"1-O17/SUM(N17:O17)","v":0.5},{"$f":"N16/SUM(N16:O16)","v":0.75},{"$f":"I26","v":0.25}]
["(c)","Describe an advantage and a disadvantage of selecting a discrimination threshold of 0.25 instead",null,null,null,null,null,null,0.5,{"$f":"1-K17/SUM(J17:K17)","v":0.33333333333333337},{"$f":"J16/SUM(J16:K16)","v":0.5},0.5]
[{"$f":"INDEX('Point Grid'!$C$8:$I$29,MATCH($A$1,'Point Grid'!$A$8:$A$29,0),MATCH(A27,'Point Grid'!$C$7:$I$7,0))","v":0.5},"of 0.50.",null,null,null,null,null,null,1,0,0,1]
[0.5]
[]
["(d)","Describe whether a discrimination threshold of 0.25 or 0.50 is more appropriate for a line of"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$29,MATCH($A$1,'Point Grid'!$A$8:$A$29,0),MATCH(A31,'Point Grid'!$C$7:$I$7,0))","v":0.5},"business with low frequency and high severity."]
[0.5]
[null,null,null,null,null,null,null,"(c)","An advantage of a lower threshold is we correctly identify more of the fraudulent claims."]
[null,null,null,null,null,null,null,null,"A disadvantage of a lower threshold is we will flag more non-fraudulent claims as potentially fraudulent,"]
[null,null,null,null,null,null,null,null,"so more time will be spent investigating."]
[]
[null,null,null,null,null,null,null,"(d)","A threshold of 0.25 is more appropriate because the high severity means the cost of not"]
[null,null,null,null,null,null,null,null,"investigating a fraudulent claim is high. The low frequency means there will not be"]
[null,null,null,null,null,null,null,null,"many claims to begin with, so flagging more claims as potentially fraudulent will not"]
[null,null,null,null,null,null,null,null,"drastically increase the workload yet will result in correctly identifying more fraud."]

{"sheet":"7","index":10,"n_rows":18,"n_cols":14,"rows_kept":18,"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)","Risk Load"]
["Points","Given the following information:"]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('7'!A1,'Point Grid'!A:A,0))","v":1.75},"• Allocated loss adjustment expense is 15% of the indemnity amount.",null,null,null,null,null,null,null,"L = 1,000","Risk Load",{"$f":"0.000064*E9","v":50.567871999999994}]
[null,"• The variance method has been selected to include a risk load in the",null,null,null,null,null,null,null,"L = 5,000","Risk Load",{"$f":"0.000064*E10","v":605.91718400000002}]
[null,"   Increased Limits Factors (ILFs) with k = 0.000064 and δ = 0."]
[null,null,null,null,null,null,null,null,null,"ILF without risk load",null,{"$f":"D10*(1+15%)/(D9*(1+15%))","v":2.9583333333333335}]
[null,null,"Limit, L","E[X; L]","E[X2; L]",null,null,null,null,"ILF with risk load",null,{"$f":"(D10*(1+15%)+L5)/(D9*(1+15%)+L4)","v":3.4072168513309071}]
[null,null,1000,840,790123]
[null,null,5000,2485,9467456,null,null,null,"(b)","Without risk loads",null,{"$f":"0.75*1+0.25*L7","v":1.4895833333333335}]
[null,null,null,null,null,null,null,null,null,"With risk loads",null,{"$f":"0.75*1+0.25*L8","v":1.6018042128327268}]
["(a)","Calculate the following:",null,null,null,null,null,null,null,"Impact to premium from using risk loads",null,null,null,{"$f":"L11/L10-1","v":7.5337093929662702E-2}]
[{"$f":"INDEX('Point Grid'!$C$8:$I$29,MATCH($A$1,'Point Grid'!$A$8:$A$29,0),MATCH(A12,'Point Grid'!$C$7:$I$7,0))","v":1},"• The risk loads for each limit."]
[1,"• The ILF with and without risk load for the 5,000 limit."]
[]
["(b)","Assuming portfolio weights of 75% for a 1,000 limit and 25% for a 5,000 limit,"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$29,MATCH($A$1,'Point Grid'!$A$8:$A$29,0),MATCH(A16,'Point Grid'!$C$7:$I$7,0))","v":0.75},"determine the overall impact on premium by using the ILFs with the risk loads"]
[0.75,"instead of the ILFs without risk loads."]

{"sheet":"8","index":11,"n_rows":23,"n_cols":14,"rows_kept":23,"cut":false,"hidden":false,"merged":[]}
[8,"Finished",null,null,null,null,"Navigator",null,"Solution"]
[null,null,null,null,null,null,null,"(a)","Current Layer Frequency = F(10,000) - F(5,000) =",null,null,null,null,{"$f":"D11-D8","v":8.7999999999999967E-2}]
["Points","An insurer sells coverage with an attachment point of 5,000 and a layer limit of 5,000."]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('8'!A1,'Point Grid'!A:A,0))","v":1.75},"The following table represents the expected cumulative severity distribution and limited",null,null,null,null,null,null,"Due to the severity increase, a claim that was 10,000 this year is now 11,000."]
[null,"expected severity for the current year at various limits.",null,null,null,null,null,null,"This means the layer 5,000 to 10,000 next year corresponds to the layer"]
[null,null,"L","F(L)","E[X; L]",null,null,null,"5,000/1.1 to 10,000/1.1 this year."]
[null,null,4545,0.84199999999999997,1807,null,null,null,"Hence"]
[null,null,5000,0.85899999999999999,1875,null,null,null,"Future layer frequency = F(9,091) - F(4,545) =",null,null,null,null,{"$f":"D10-D7","v":9.5999999999999974E-2}]
[null,null,5500,0.875,1941]
[null,null,9091,0.93799999999999994,2256,null,null,null,"So the change in layer frequency is",null,null,null,{"$f":"N8/N2-1","v":9.090909090909105E-2}]
[null,null,10000,0.94699999999999995,2308]
[null,null,11000,0.95399999999999996,2357,null,null,"(b)","Current Layer Severity = "]
[]
[null,"The actuary expects a 10% severity increase for all claim sizes next year.",null,null,null,null,null,null,null,null,{"$f":"(E11-E8)/(D11-D8)","v":4920.4545454545469}]
[]
["(a)","Calculate the percent change in frequency of claims in the layer.",null,null,null,null,null,null,"Future Layer Severity = "]
[{"$f":"INDEX('Point Grid'!$C$8:$I$29,MATCH($A$1,'Point Grid'!$A$8:$A$29,0),MATCH(A16,'Point Grid'!$C$7:$I$7,0))","v":0.75}]
[0.75,null,null,null,null,null,null,null,{"$f":"1.1*(E10-E7)/(D10-D7)","v":5144.7916666666688}]
[]
["(b)","Calculate the percent change in pure premiums in the layer.",null,null,null,null,null,null,"Change in layer severity =",null,null,{"$f":"I18/K14-1","v":4.5592763664357383E-2}]
[{"$f":"INDEX('Point Grid'!$C$8:$I$29,MATCH($A$1,'Point Grid'!$A$8:$A$29,0),MATCH(A20,'Point Grid'!$C$7:$I$7,0))","v":1}]
[1,null,null,null,null,null,null,null,"Change in layer pure premium = (1 + layer change in frequency)*(1+ layer change in severity)-1"]
[null,null,null,null,null,null,null,null,{"$f":"(1+M10)*(1+L20)-1","v":0.14064665127020826}]

{"sheet":"9","index":12,"n_rows":43,"n_cols":19,"rows_kept":43,"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 experience period is the three years prior to 1/1/17, lagged by one year. Namely,"]
["Points","Consider the following claims-made commercial general liability policy:",null,null,null,null,null,null,null,null,"1/1/13 – 12/31/15."]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('9'!A1,'Point Grid'!A:A,0))","v":3.25},"• The insurance contract was originally written January 1, 2011, and has been ",null,null,null,null,null,null,null,null,"First we need the CSLC (Company Subject Loss Cost). "]
[null,"   renewed annually as a claims-made policy.",null,null,null,null,null,null,null,null,"Notice there's only one type of policy, Prem/Ops, and since it started in 2011, the 2017 policy is mature."]
[null,"• The annual Premises/Operations basic limits manual premium is $200,000 and",null,null,null,null,null,null,null,null,"Also, observe there's no mention of a special underwriting basis so use the standard approach with Table 14, Rule 5B."]
[null,"   there is no products exposure."]
[null,"• The expected loss ratio is 70%.",null,null,null,null,null,null,null,null,"Year","Subline","Premium","ELR","PAF 13B","PAF 13C","Detrend","Loss Cost","Comment"]
[null,"• Loss experience is evaluated as of June 30, 2016.",null,null,null,null,null,null,null,null,2015,"Prem/Ops",200000,0.7,{"$f":"$D$22","v":1.03},{"$f":"D26","v":0.94},{"$f":"D31","v":0.90700000000000003},{"$f":"PRODUCT(M9:Q9)","v":122942.03600000001},"5th year"]
[null,null,null,null,null,null,null,null,null,null,2014,"Prem/Ops",200000,0.7,{"$f":"$D$22","v":1.03},{"$f":"E26","v":0.91},{"$f":"D32","v":0.86399999999999999},{"$f":"PRODUCT(M10:Q10)","v":113375.808},"4th year"]
[null,"Claim Number","Policy Year","Indemnity","ALAE",null,null,null,null,null,2013,"Prem/Ops",200000,0.7,{"$f":"$D$22","v":1.03},{"$f":"F26","v":0.88},{"$f":"D33","v":0.82299999999999995},104435.408,"3rd year"]
[null,1,2011,5000,5000,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(R9:R11)","v":340753.25199999998},"CSLC"]
[null,2,2012,15000,25000]
[null,3,2013,58000,0,null,null,null,null,null,"Next, look up the credibility, EER and MSL based on the CSLC using Table 16"]
[null,4,2013,20000,85000,null,null,null,null,null,"Z =",{"$f":"C38","v":0.54}]
[null,5,2014,118000,82000,null,null,null,null,null,"EER = ",{"$f":"D38","v":0.94}]
[null,6,2015,8000,5000,null,null,null,null,null,"MSL =",{"$f":"E38","v":173150}]
[]
[null,"You may use the information in the following tables:",null,null,null,null,null,null,null,null,"Now use the claim history to calculate the Actual Experience Ratio"]
[null,"Table 13.B","Occurrence","Claims-Made Year in Program",null,null,null,null,null,null,"Claim","Basic Limits","ALAE","Basic + ALAE","Cap by MSL","Comment"]
[null,"Subline","Policy","Mature","Fourth","Third","Second","First",null,null,1,0,0,{"$f":"M21+L21","v":0},{"$f":"MIN(N21,$L$17)","v":0},"Not in experience period"]
[null,"Prem/Ops",1,1.03,1.1399999999999999,1.2,1.32,1.62,null,null,2,0,0,{"$f":"M22+L22","v":0},{"$f":"MIN(N22,$L$17)","v":0},"Not in experience period"]
[null,null,null,null,null,null,null,null,null,null,3,{"$f":"MIN(D14,100000)","v":58000},{"$f":"E14","v":0},58000,58000]
[null,"Table 13.C","Occurrence","Claims-Made Year in Program",null,null,null,null,null,null,4,{"$f":"MIN(D15,100000)","v":20000},{"$f":"E15","v":85000},105000,105000]
[null,"Subline","Policy","Mature","Fourth","Third","Second","First",null,null,5,100000,82000,182000,173150]
[null,"Prem/Ops",1,0.94,0.91,0.88,0.78,0.67,null,null,6,8000,5000,13000,13000]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(O21:O26)","v":349150},"Limited Loss History"]
[null,"Table 14."]
[null,"Experience",null,"Factors For",null,null,null,null,null,null,"Recall Claims-Made policies have no expected development."]
[null,"Period Year","Subline","Rule 5.B","Rule 5.C"]
[null,"Latest","Prem/Ops",0.90700000000000003,0.92600000000000005]
[null,"2nd Latest","Prem/Ops",0.86399999999999999,0.89200000000000002]
[null,"3rd Latest","Prem/Ops",0.82299999999999995,0.85799999999999998,null,null,null,null,null,"AER =",{"$f":"O27/R12","v":1.0246417252094193}]
[]
[null,"Table 16."]
[null,"CSLC","Credibility","EER","MSL"]
[null,"318,537 – 331,584",0.53,0.93700000000000006,170200,null,null,null,null,null,"Mod =",{"$f":"L15*(L33-L16)/L16","v":4.8623969801155818E-2}]
[null,"331,585 – 345,206",0.54,0.94,173150]
[null,"345,207 – 359,440",0.55000000000000004,0.94399999999999995,178700,null,null,null,null,null,"Experience Modified Premium = 200,000 * Modification Factor =",null,null,null,null,null,{"$f":"(1+L37)*200000","v":209724.79396023118}]
[]
["(a)","Calculate the experience modified premium for the policy effective January 1, 2017."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$29,MATCH($A$1,'Point Grid'!$A$8:$A$29,0),MATCH(A41,'Point Grid'!$C$7:$I$7,0))","v":3.25}]
[3.25]

{"sheet":"10","index":13,"n_rows":14,"n_cols":16,"rows_kept":14,"cut":false,"hidden":false,"merged":[[0,6,0,7]]}
[10,"Finished",null,null,null,null,"Navigator",null,null,"Solution"]
[null,null,null,null,null,null,null,null,"(a)","Since the most recent prior year is not included in the experience period, only the first year"]
["Points","An actuary is evaluating a risk to determine the appropriateness of applying any schedule",null,null,null,null,null,null,null,"of the safety program being removed is included in the three years of experience."]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('10'!A1,'Point Grid'!A:A,0))","v":1},"credits or debits. In order to reduce expenses the insured removed a safety program ",null,null,null,null,null,null,null,"Therefore, apply a debit of 2/3 of the maximum debit for removing the safety program."]
[null,"two years ago and in the next month will be reducing its staff.",null,null,null,null,null,null,null,"Safety Program",null,{"$f":"10%*2/3","v":6.6666666666666666E-2},"debit"]
[]
[null,null,null,"Range of Modifications",null,null,null,null,null,"Since the staff are being reduced, it is reasonable to expect that even if the most experienced"]
[null,"Risk Characteristic","Description","Credit",null,"Debit",null,null,null,"staff remain, they will be overworked and so more likely to be accident prone. Further, there"]
[null,"Cooperation","Safety Program",0.1,"to",0.1,null,null,null,"would be less supervision. Recommend a"]
[null,"Employees","Selection, training, supervision, experience",0.05,"to",0.05,null,null,null,0.02,"debit for employees."]
[]
["(a)","Recommend and defend an appropriate schedule modification.",null,null,null,null,null,null,null,"These combine to give a schedule modification of ",null,null,null,null,{"$f":"L5+J10","v":8.666666666666667E-2},"debit"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$29,MATCH($A$1,'Point Grid'!$A$8:$A$29,0),MATCH(A12,'Point Grid'!$C$7:$I$7,0))","v":1}]
[1]

{"sheet":"11","index":14,"n_rows":37,"n_cols":14,"rows_kept":37,"cut":false,"hidden":false,"merged":[[0,6,0,7],[9,2,10,2],[9,1,10,1],[19,6,20,6],[9,6,10,6],[9,5,10,5],[9,4,10,4],[9,3,10,3],[19,1,20,1],[19,2,20,2],[19,3,20,3],[19,4,20,4],[19,5,20,5]]}
[11,"Finished",null,null,null,null,"Navigator",null,null,"Solution"]
[null,null,null,null,null,null,null,null,"(a)","Since the values are given in thousands throughout the tables, we can ignore the thousands for now."]
["Points","A Workers' Compensation insurer is facing an increasingly competitive market. "]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('11'!A1,'Point Grid'!A:A,0))","v":2.5},"Its management is concerned about customer retention, premium growth, and loss ratio",null,null,null,null,null,null,null,"Efficiency Statistic = Sample Variance of Standard Loss Ratios / Sample Variance of Manual Loss Ratios"]
[null,"deterioration. The insurer's actuaries have proposed an updated experience rating plan."]
[null,"After grouping insureds for the purposes of an efficiency test, the projected impact of",null,null,null,null,null,null,null,"Current Plan"]
[null,"this proposal is below (values in thousands):",null,null,null,null,null,null,null,"Sample Variance of Standard LRs",null,null,null,{"$f":"((G12-G17)^2+(G13-G17)^2+(G14-G17)^2+(G15-G17)^2+(G16-G17)^2)/(COUNT(G12:G16)-1)","v":1.6500000000000002E-3}]
[null,null,null,null,null,null,null,null,null,"Sample Variance of Manual LRs",null,null,null,{"$f":"((F12-F17)^2+(F13-F17)^2+(F14-F17)^2+(F15-F17)^2+(F16-F17)^2)/(COUNT(F12:F16)-1)","v":2.1725000000000005E-2}]
[null,"Current Plan"]
[null,"Quintile","Manual Premium","Loss","Standard Premium","Loss Ratio to Manual","Loss Ratio to Standard",null,null,"Efficiency Statistic",null,{"$f":"N7/N8","v":7.5949367088607583E-2}]
[]
[null,"A",4750,1978,3278,0.42,0.6,null,null,"Proposed Plan"]
[null,"B",4825,2824,4005,0.59,0.71,null,null,"Sample Variance of Standard LRs",null,null,null,{"$f":"((G22-G27)^2+(G23-G27)^2+(G25-G27)^2+(G24-G27)^2+(G26-G27)^2)/(COUNT(G23:G27)-1)","v":1.7500000000000005E-3}]
[null,"C",4450,2915,4450,0.66,0.66,null,null,"Sample Variance of Manual LRs",null,null,null,{"$f":"((F22-F27)^2+(F23-F27)^2+(F25-F27)^2+(F24-F27)^2+(F26-F27)^2)/(COUNT(F23:F27)-1)","v":3.0800000000000004E-2}]
[null,"D",4845,3608,5378,0.74,0.67]
[null,"E",4400,3520,5500,0.8,0.64,null,null,"Efficiency Statistic",null,{"$f":"N13/N14","v":5.6818181818181823E-2}]
[null,"Total",{"$f":"SUM(C12:C16)","v":23270},{"$f":"SUM(D12:D16)","v":14845},{"$f":"SUM(E12:E16)","v":22611},{"$f":"ROUND(D17/C17,2)","v":0.64},{"$f":"ROUND(D17/E17,2)","v":0.66}]
[null,null,null,null,null,null,null,null,null,"The proposed plan has a lower efficiency statistic than the current plan so the "]
[null,"Proposed Plan",null,null,null,null,null,null,null,"proposed plan performs better."]
[null,"Quintile","Manual Premium","Loss","Standard Premium","Loss Ratio to Manual","Loss Ratio to Standard"]
[null,null,null,null,null,null,null,null,null,"Alice: \"You might be tempted to use the Excel function Var.S(). "]
[null,"A",4220,1494,2068,0.35,0.72,null,null,"However, that would give the wrong answer because it automatically calculates the mean "]
[null,"B",5100,2922,4233,0.56999999999999995,0.69,null,null,"of the range of data you give it using the arithmetic mean."]
[null,"C",4150,3088,4109,0.74,0.75,null,null,"Whereas, we want to use the weighted averages from the Total rows when "]
[null,"D",4950,3689,5346,0.75,0.69,null,null,"calculating the variance.\""]
[null,"E",4850,3652,5723,0.75,0.64]
[null,"Total",{"$f":"SUM(C22:C26)","v":23270},{"$f":"SUM(D22:D26)","v":14845},{"$f":"SUM(E22:E26)","v":21479},{"$f":"ROUND(D27/C27,2)","v":0.64},{"$f":"ROUND(D27/E27,2)","v":0.69},null,"(b)","The proposed plan is more efficient which should help reduce adverse selection via better"]
[null,null,null,null,null,null,null,null,null,"matching of rate to risk. This should result in less loss ratio deterioration."]
[null,"It is estimated that the upfront cost to adopt the new rating plan will be $500,000.",null,null,null,null,null,null,null,"The cost of adopting the proposed plan is approximately 2% of the premium, which is low."]
[null,null,null,null,null,null,null,null,null,"There is a risk that adopting the new plan may cause customers to experience large rate swings."]
["(a)","Perform an efficiency test and evaluate the proposed experience rating plan relative to",null,null,null,null,null,null,null,"This may hurt retention in the short term."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$29,MATCH($A$1,'Point Grid'!$A$8:$A$29,0),MATCH(A31,'Point Grid'!$C$7:$I$7,0))","v":1.5},"the current plan.",null,null,null,null,null,null,null,"Overall, recommend adopting the proposed plan as it addresses almost all of the concerns."]
[1.5]
[]
["(b)","In light of management's concerns, evaluate the merits of adopting the new rating plan"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$29,MATCH($A$1,'Point Grid'!$A$8:$A$29,0),MATCH(A35,'Point Grid'!$C$7:$I$7,0))","v":1},"versus keeping the current plan, and provide a recommendation."]
[1]

{"sheet":"12","index":15,"n_rows":51,"n_cols":9,"rows_kept":51,"cut":false,"hidden":false,"merged":[]}
[12,"Finished",null,null,null,null,"Navigator",null,"Solution"]
[null,null,null,null,null,null,null,"(a)","Alice: \"This isn't really a fair question for the new Computer Based Testing environment"]
["Points","A policy has a flat dollar deductible of M and a maximum payout on a loss by the insurer of N.",null,null,null,null,null,null,"Nevertheless, it's an important topic to understand so you should make sure you know"]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('12'!A1,'Point Grid'!A:A,0))","v":3.25},null,null,null,null,null,null,null,"how to do it.\""]
[]
["(a)","Draw a Lee diagram representing the expected amount of loss incurred by this policy."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$29,MATCH($A$1,'Point Grid'!$A$8:$A$29,0),MATCH(A6,'Point Grid'!$C$7:$I$7,0))","v":1},"Label the following:"]
[1,"   i. The axes"]
[null,"  ii. The deductible amount"]
[null," iii. The policy limit"]
[null," iv. The expected insured loss."]
[]
["(b)","Assume cumulative losses follow a distribution F(x). Write the formula for covered losses"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$29,MATCH($A$1,'Point Grid'!$A$8:$A$29,0),MATCH(A13,'Point Grid'!$C$7:$I$7,0))","v":0.5},"for this policy using:"]
[0.5,"   i. The layer method"]
[null,"  ii. The size method."]
[]
["(c)","Briefly describe when the layer method may be preferred and when the size method may"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$29,MATCH($A$1,'Point Grid'!$A$8:$A$29,0),MATCH(A18,'Point Grid'!$C$7:$I$7,0))","v":0.5},"be preferred."]
[0.5,null,null,null,null,null,null,null,"The expected insured loss is the shaded area denoted by B."]
[]
["(d)","Use a Lee diagram to demonstrate the consistency test of ILFs.",null,null,null,null,null,"(b)"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$29,MATCH($A$1,'Point Grid'!$A$8:$A$29,0),MATCH(A22,'Point Grid'!$C$7:$I$7,0))","v":1.25},null,null,null,null,null,null,"i."]
[1.25]
[null,null,null,null,null,null,null,"ii."]
[]
[]
[null,null,null,null,null,null,null,"(c)","The layer method is preferred when the survival function, S(x), is easy to integrate."]
[null,null,null,null,null,null,null,null,"The size method is preferred when you are calculating expected losses at a single limit"]
[null,null,null,null,null,null,null,null,"because it is more intuitive to explain."]
[]
[null,null,null,null,null,null,null,"(d)"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,"The consistency test of ILFs states the premium calculated from the layer formula"]
[null,null,null,null,null,null,null,null,"applied to successive excess layers of constant width is a decreasing function"]
[null,null,null,null,null,null,null,null,"of the attachment point limit."]
[null,null,null,null,null,null,null,null,"We have Area A > Area B > Area C and so the premium for layer L to 2L is greater than"]
[null,null,null,null,null,null,null,null,"the premium for layer 2L to 3L, which is greater than the premium for layer 3L to 4L."]
[null,null,null,null,null,null,null,null,"Hence the consistency test is satisfied."]

{"sheet":"13","index":16,"n_rows":42,"n_cols":14,"rows_kept":42,"cut":false,"hidden":false,"merged":[[10,2,11,2],[10,4,11,4],[10,3,11,3],[10,1,11,1],[24,2,25,2],[24,1,25,1]]}
[13,"Finished",null,null,null,null,"Navigator",null,"Solution"]
[null,null,null,null,null,null,null,"(a)","The maximum premium that can be charged is given by B + c*LG, where B is the basic premium"]
["Points","A risk is written using a balanced retrospective rating plan with the following characteristics:",null,null,null,null,null,null,"c, is the loss conversion factor, and LG is the maximum ratable loss."]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('13'!A1,'Point Grid'!A:A,0))","v":2.5},"• Losses at the minimum premium = $50,000"]
[null,"• Losses at the maximum premium = $300,000",null,null,null,null,null,null,"We need to find B = e - (c-1)*E + cI"]
[null,"• Loss conversion factor = 1.05",null,null,null,null,null,null,"where E is the expected aggregate loss and I is the net insurance charge."]
[null,"• e = $10,000"]
[null,null,null,null,null,null,null,null,"E = ",{"$f":"SUM(C13:C17,E13:E17)/D17","v":200000}]
[null,"The following table shows actual experience from a representative sample of risks that are"]
[null,"similar to the risk in question:",null,null,null,null,null,null,"Entry ratio at minimum premium , rH =",null,null,null,{"$f":"50000/J8","v":0.25}]
[null,"Risk","Actual Aggregate Loss","Risk","Actual Aggregate Loss",null,null,null,"Entry ratio at maximum premium, rG =",null,null,null,{"$f":"300000/J8","v":1.5}]
[]
[null,1,25000,6,175000,null,null,null,"I = E*(φ(1.5) - ψ(0.25))"]
[null,2,50000,7,200000]
[null,3,100000,8,300000,null,null,null,"Now it's implicit we need to use the actual experience given to calculate the (Table M)"]
[null,4,100000,9,350000,null,null,null,"insurance charges."]
[null,5,150000,10,550000]
[null,null,null,null,null,null,null,null,"Actual","Entry Ratio","# Risk","# Risks > r","% Risks > r","φ(r)"]
["(a)","Determine the maximum premium that the insured can be charged.",null,null,null,null,null,null,0,{"$f":"I19/$J$8","v":0},{"$f":"COUNTIF($C$13:$C$17,J19*$J$8)+COUNTIF($E$13:$E$17,J19*$J$8)","v":0},{"$f":"COUNTIF($C$13:$C$17,\">\"&J19*$J$8)+COUNTIF($E$13:$E$17,\">\"&J19*$J$8)","v":10},{"$f":"L19/$D$17","v":1},{"$f":"N20+M19*(J20-J19)","v":1}]
[{"$f":"INDEX('Point Grid'!$C$8:$I$29,MATCH($A$1,'Point Grid'!$A$8:$A$29,0),MATCH(A19,'Point Grid'!$C$7:$I$7,0))","v":2},null,null,null,null,null,null,null,{"$f":"C13","v":25000},{"$f":"I20/$J$8","v":0.125},{"$f":"COUNTIF($C$13:$C$17,J20*$J$8)+COUNTIF($E$13:$E$17,J20*$J$8)","v":1},9,{"$f":"L20/$D$17","v":0.9},0.875]
[2,null,null,null,null,null,null,null,{"$f":"C14","v":50000},{"$f":"I21/$J$8","v":0.25},1,8,0.8,0.76249999999999996]
[null,null,null,null,null,null,null,null,{"$f":"C15","v":100000},0.5,2,6,0.6,0.5625]
[null,"An actuary has acquired actual aggregate data from a new book consisting of risks within the",null,null,null,null,null,null,{"$f":"C17","v":150000},{"$f":"I23/$J$8","v":0.75},1,5,0.5,0.41250000000000003]
[null,"same industry. The loss experience of five representative risks from this book is shown below:",null,null,null,null,null,null,{"$f":"E13","v":175000},0.875,1,4,0.4,0.35000000000000003]
[null,"Risk","Actual Aggregate Loss",null,null,null,null,null,{"$f":"E14","v":200000},1,1,3,0.3,0.30000000000000004]
[null,null,null,null,null,null,null,null,{"$f":"E15","v":300000},1.5,1,2,0.2,0.15000000000000002]
[null,1,275000,null,null,null,null,null,{"$f":"E16","v":350000},1.75,1,1,0.1,{"$f":"N28+M27*(J28-J27)","v":0.1}]
[null,2,300000,null,null,null,null,null,{"$f":"E17","v":550000},2.75,1,0,0,0]
[null,3,500000,null,null,null,null,null,"Alice: \"Notice there are no duplicate rows.\""]
[null,4,700000]
[null,5,800000,null,null,null,null,null,"φ(1.5) =",{"$f":"N26","v":0.15000000000000002}]
[null,null,null,null,null,null,null,null,"ψ(0.25) =",{"$f":"N21+M10-1","v":1.2499999999999956E-2}]
["(b)","The actuary proposes to combine the data from the two books, stating that the combined data",null,null,null,null,null,null,"So I =",{"$f":"J8*(J31-J32)","v":27500.000000000015}]
[{"$f":"INDEX('Point Grid'!$C$8:$I$29,MATCH($A$1,'Point Grid'!$A$8:$A$29,0),MATCH(A33,'Point Grid'!$C$7:$I$7,0))","v":0.5},"will result in a more accurate calculation of the insured's retrospective premium.",null,null,null,null,null,null,"Then B =",{"$f":"10000-(1.05-1)*J8+1.05*J33","v":28875.000000000007}]
[0.5,"Assess the validity of the actuary's statement."]
[null,null,null,null,null,null,null,null,"Maximum Premium that can be charged =",null,null,null,{"$f":"J34+1.05*300000","v":343875}]
[]
[null,null,null,null,null,null,null,"(b)","The expected aggregate loss for the new book is",null,null,null,null,{"$f":"AVERAGE(C27:C31)","v":515000}]
[null,null,null,null,null,null,null,null,"The actuary is incorrect, you should combine the losses only if the expected value of the"]
[null,null,null,null,null,null,null,null,"losses, the underlying distributions are the same and the risks are exposed to similar hazards."]
[null,null,null,null,null,null,null,null,"In this case the expected value of the new book is significantly higher than the old book."]
[null,null,null,null,null,null,null,null,"Hence, a separate Table M should be used."]

{"sheet":"14","index":17,"n_rows":38,"n_cols":13,"rows_kept":38,"cut":false,"hidden":false,"merged":[[4,2,5,2],[4,1,5,1],[4,3,5,3]]}
[14,"Finished",null,null,null,null,"Navigator",null,"Solution"]
[null,null,null,null,null,null,null,"(a)"]
["Points","A reinsurer has been supplied the following information from a large insurance company:"]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('14'!A1,'Point Grid'!A:A,0))","v":3},null,null,null,null,null,null,null,"E[X] =",{"$f":"D12*1000/C12","v":500000}]
[null,"Claim Size Range","Expected Number of Claims","Expected Ultimate Losses ($000)",null,null,null,null,"E[X; 1m] =",{"$f":"(D7*1000+SUM(C8:C11)*10^6)/C12","v":387525},null,"e(1m) =",{"$f":"($J$4-J5)/(1-SUM(C7:$C$7)/$C$12)","v":2249499.9999999981}]
[null,null,null,null,null,null,null,null,"E[X; 2m] =",{"$f":"(SUM(D7:D8)*1000+SUM(C9:C11)*2*10^6)/C12","v":427890},null,"e(2m) =",{"$f":"($J$4-J6)/(1-SUM(C$7:$C8)/$C$12)","v":2249921.9968798738}]
[null,"$0 to $1,000,000",19000,6750500,null,null,null,null,"E[X; 3m] =",{"$f":"(SUM(D7:D9)*1000+SUM(C10:C11)*3*10^6)/C12","v":453765},null,"e(3m) =",{"$f":"($J$4-J7)/(1-SUM(C$7:$C9)/$C$12)","v":2249878.3454987882}]
[null,"$1,000,001 to $2,000,000",359,525300,null,null,null,null,"E[X; 4m] =",{"$f":"(SUM(D7:D10)*1000+SUM(C11)*4*10^6)/C12","v":470300},null,"e(4m) =",{"$f":"($J$4-J8)/(1-SUM(C$7:$C10)/$C$12)","v":2250000.0000000019}]
[null,"$2,000,001 to $3,000,000",230,566500]
[null,"$3,000,001 to $4,000,000",147,507700]
[null,"above $4,000,001",264,1650000]
[null,"Total",{"$f":"SUM(C7:C11)","v":20000},{"$f":"SUM(D7:D11)","v":10000000}]
[]
[null,"The reinsurer is entering into an excess of loss contract with the primary insurance company."]
[null,"The reinsurer will pay all losses above a $5,000,000 per claim retention."]
[]
["(a)","Construct a graph of the excess severity function for claim sizes of $1,000,000, $2,000,000,"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$29,MATCH($A$1,'Point Grid'!$A$8:$A$29,0),MATCH(A17,'Point Grid'!$C$7:$I$7,0))","v":1.5},"$3,000,000, and $4,000,000."]
[1.5]
[]
["(b)","Calculate the reinsurer's expected losses under the proposed contract."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$29,MATCH($A$1,'Point Grid'!$A$8:$A$29,0),MATCH(A21,'Point Grid'!$C$7:$I$7,0))","v":1.5}]
[1.5]
[null,null,null,null,null,null,null,"(b)","From part (a) the excess severity distribution is flat which means losses follow an"]
[null,null,null,null,null,null,null,null,"exponential distribution above $1 million, with mean 2,250,000."]
[null,null,null,null,null,null,null,null,"This means e(5m) = e(4m) = … = e(1m) = 2,250,000."]
[null,null,null,null,null,null,null,null,"The reinsurer pays all losses above $5,000,000, so we need the expected number of claims over $5M."]
[null,null,null,null,null,null,null,null,"In part (a) we found information for losses in excess of 1,000,000 so"]
[null,null,null,null,null,null,null,null,"E[N|X>5M] = P(X>5M | X>1M) * E[N|X>1M]"]
[null,null,null,null,null,null,null,null,"P(X>5M | X>1M) = S(5M) / S(1M) where S is the survival function for the exponential distribution."]
[]
[]
[null,null,null,null,null,null,null,null,"So P(X>5M | X> 1M) =",null,{"$f":"EXP(-5*10^6/2250000)/EXP(-1*10^6/2250000)","v":0.16901331540606604}]
[null,null,null,null,null,null,null,null,"E[N|X>1M] =",null,{"$f":"(C12-C7)","v":1000}]
[]
[null,null,null,null,null,null,null,null,"So E[N|X>5M] =",null,{"$f":"K34*K33","v":169.01331540606603}]
[null,null,null,null,null,null,null,null,"Multiplying this by the expected excess severity gives the reinsurer's expected loss"]
[null,null,null,null,null,null,null,null,{"$f":"K36*2250000","v":380279959.66364855}]

{"sheet":"15","index":18,"n_rows":41,"n_cols":14,"rows_kept":41,"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)","Tax multiplier = ",null,{"$f":"1/(1-E10)","v":1.0309278350515465}]
["Points","The following applies to an incurred loss retrospectively rated policy effective January 1, 2017.",null,null,null,null,null,null,null,null,"Loss conversion factor = ",null,null,{"$f":"1+E7","v":1.08}]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('15'!A1,'Point Grid'!A:A,0))","v":3.25}]
[null,"Per-occurrence limit",null,null,150000,null,null,null,null,null,"Retrospective Premium = (B + cL)*T"]
[null,"Maximum ratable loss",null,null,600000]
[null,"ULAE as a percentage of loss & ALAE",null,null,0.08,null,null,null,null,null,"Time 0:"]
[null,"Expected Loss & ALAE limited to $150,000",null,null,400000,null,null,null,null,null,"Ratable Loss =",null,{"$f":"MIN(E8,E6)","v":400000},"← Alice: \"At time 0 we only know the expected ratable loss.\""]
[null,"Basic Premium",null,null,375000,null,null,null,null,null,"Retro Premium =",null,{"$f":"(E9+N3*M8)*M2","v":831958.76288659801}]
[null,"Tax Rate",null,null,0.03,null,null,null,null,null,{"$f":"\"The insurer pays the full initial retro premium of \"&TEXT(M9,\"$0,000.00\")","v":"The insurer pays the full initial retro premium of $831,958.76"}]
[]
[null,"The expected loss & ALAE limited to $150,000 is used to determine the initial premium.",null,null,null,null,null,null,null,null,"The first premium adjustment is at 18 months"]
[null,"Retrospective rating adjustments start at 18 months and occur every twelve months thereafter."]
[null,null,null,null,null,null,null,null,null,null,"Time 18 months:"]
[null,"The following loss experience occurs during the policy period. All claims are closed at",null,null,null,null,null,null,null,null,"Ratable Loss = ",null,{"$f":"MIN(E20,E6)","v":375000},"← Alice: \"Don't forget to show you considered the maximum ratable loss.\""]
[null,"42 months.",null,null,null,null,null,null,null,null,"Retro Premium =",null,{"$f":"(E9+N3*M15)*M2","v":804123.71134020633}]
[null,null,null,null,"Maturity (months)",null,null,null,null,null,{"$f":"\"The insurer pays \"&TEXT(M16-M9,\"$0,000.00\")","v":"The insurer pays -$27,835.05"}]
[null,null,null,null,18,30,42,null,null,null,"i.e. the insurer receives a premium refund."]
[null,"Unlimited Incurred Loss & ALAE",null,null,425000,650000,950000]
[null,"Incurred Loss & ALAE Limited to $150,000",null,null,375000,475000,700000,null,null,null,"Subsequent adjustments occur every 12 months until 42 months have elapse"]
[null,"Incurred Loss & ALAE Excess of $150,000",null,null,50000,175000,250000]
[null,null,null,null,null,null,null,null,null,null,"Time 30 months:"]
["(a)","Determine the amount and timing of each incremental cash flow payment made by the insured",null,null,null,null,null,null,null,null,"Ratable Loss = ",null,{"$f":"MIN(F20,E6)","v":475000}]
[{"$f":"INDEX('Point Grid'!$C$8:$I$29,MATCH($A$1,'Point Grid'!$A$8:$A$29,0),MATCH(A23,'Point Grid'!$C$7:$I$7,0))","v":1.75},"for this policy beginning with time 0.",null,null,null,null,null,null,null,null,"Retro Premium =",null,{"$f":"(E9+N3*M23)*M2","v":915463.91752577329}]
[1.75,null,null,null,null,null,null,null,null,null,{"$f":"\"The insurer pays \"&TEXT(M24-M16,\"$0,000.00\")","v":"The insurer pays $111,340.21"}]
[]
["(b)","Propose and justify an alternative policy that would increase the insured's cash flow benefit",null,null,null,null,null,null,null,null,"Time 42 months:"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$29,MATCH($A$1,'Point Grid'!$A$8:$A$29,0),MATCH(A27,'Point Grid'!$C$7:$I$7,0))","v":1},"without the insured retaining any additional excess loss risk.",null,null,null,null,null,null,null,null,"Ratable Loss = ",null,{"$f":"MIN(G20,E6)","v":600000}]
[1,null,null,null,null,null,null,null,null,null,"Retro Premium =",null,{"$f":"(E9+N3*M28)*M2","v":1054639.1752577322}]
[null,null,null,null,null,null,null,null,null,null,{"$f":"\"The insurer pays \"&TEXT(M29-M24,\"$0,000.00\")","v":"The insurer pays $139,175.26"}]
["(c)","Identify and briefly describe one disadvantage to the insurer of the proposed plan in part (b)"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$29,MATCH($A$1,'Point Grid'!$A$8:$A$29,0),MATCH(A31,'Point Grid'!$C$7:$I$7,0))","v":0.5},"above compared to the existing retrospectively rated plan.",null,null,null,null,null,null,null,"(b)","A large deductible policy with a deductible of $150,000 and an aggregate deductible of"]
[0.5,null,null,null,null,null,null,null,null,null,"$600,000 exposes the insured to the same loss potential. However, the insured pays a lower"]
[null,null,null,null,null,null,null,null,null,null,"premium to cover only the excess losses and reimburses the reinsurer for deductible losses later."]
[null,null,null,null,null,null,null,null,null,null,"This creates a cashflow advantage for the insured as they can invest their money for longer."]
[]
[null,null,null,null,null,null,null,null,null,"(c)","A large deductible plan poses credit risk for the insurer because they pay all losses upfront"]
[null,null,null,null,null,null,null,null,null,null,"and then seek reimbursement for deductible losses from the insured. There is a risk the"]
[null,null,null,null,null,null,null,null,null,null,"insured may not be able to pay. "]
[null,null,null,null,null,null,null,null,null,null,"A retrospective rating plan also has credit risk but it is smaller in magnitude because the"]
[null,null,null,null,null,null,null,null,null,null,"additional premium payments are usually much smaller than deductible recoveries."]

{"sheet":"16","index":19,"n_rows":15,"n_cols":9,"rows_kept":15,"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)","1. If there is insufficient data then the resulting distribution may have significant bias and not"]
["Points","An insurance company generally uses two different methods to price excess layer",null,null,null,null,null,null,"     reflect reality."]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('16'!A1,'Point Grid'!A:A,0))","v":1.75},"insurance contracts:",null,null,null,null,null,null,"2. Continuous approximation models require intensive calculation which can be time consuming and costly."]
[null,"   • Empirical construction of Table M; or"]
[null,"   • Approximating the distribution of aggregate losses with a continuous approximation ",null,null,null,null,null,"(b)","1. Divide the available data into homogeneous risk groups based on similar size policies for example."]
[null,"      model.",null,null,null,null,null,null,"2. Calculate the average expected aggregate loss across all policies in the risk group."]
[null,null,null,null,null,null,null,null,"3. For each policy, compute its entry ratio by dividing its aggregate loss by the expected aggregate loss."]
["(a)","Briefly describe two potential disadvantages of using a continuous approximation model.",null,null,null,null,null,null,"4. Starting at 0, order the unique entry ratios in ascending order."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$29,MATCH($A$1,'Point Grid'!$A$8:$A$29,0),MATCH(A9,'Point Grid'!$C$7:$I$7,0))","v":0.5},null,null,null,null,null,null,null,"5. Determine the percentage of risks above each entry ratio."]
[0.5,null,null,null,null,null,null,null,"6. Calculate the Table M insurance charge for each entry ratio, starting at the bottom with 0 and"]
[null,null,null,null,null,null,null,null,"    then working upward by adding (difference between entry ratios) * % risks above r to the result."]
["(b)","Fully describe the process of constructing an empirical Table M and estimating the ",null,null,null,null,null,null,"7. Assuming the excess layer has no upper bound. Calculate the entry ratio for the excess layer by dividing the excess layer bound by the expected aggregate loss."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$29,MATCH($A$1,'Point Grid'!$A$8:$A$29,0),MATCH(A13,'Point Grid'!$C$7:$I$7,0))","v":1.25},"aggregate loss cost of an excess layer using the table.",null,null,null,null,null,null,"8. Calculate the insurance charge by multiplying the expected aggregate loss by the Table M insurance charge for the excess layer entry ratio."]
[1.25]

{"sheet":"17","index":20,"n_rows":35,"n_cols":15,"rows_kept":35,"cut":false,"hidden":false,"merged":[[0,7,0,8]]}
[17,"Finished",null,null,null,null,null,"Navigator",null,null,"Solution"]
[null,null,null,null,null,null,null,null,null,"(a)"]
["Points","A Workers' Compensation policy has the following limits and expected losses:"]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('17'!A1,'Point Grid'!A:A,0))","v":2},null,null,null,null,null,null,null,null,null,"k = ",{"$f":"(E6-E7)/E6","v":0.2857142857142857}]
[null,"Per-Occurrence Limit",null,null,300000]
[null,"Expected Unlimited Aggregate Losses",null,null,700000]
[null,"Expected Limited Aggregate losses",null,null,500000]
[null,"Aggregate Deductible Limit",null,null,800000,null,null,null,null,null,"ICRLL =",{"$f":"(1+0.8*L4)/(1-L4)","v":1.72}]
[null,"State Hazard Group Differential",null,null,0.95]
[null,null,null,null,null,null,null,null,null,null,"Adjusted Expected Unlimited Loss =",null,null,null,{"$f":"E6*E9*L8","v":1143800}]
[null,"Table of Expected Loss Groups",null,null,null,null,null,null,null,null,"Implies expected loss group 28"]
[null,"Expected Loss Group","Range of Values"]
[null,31,"630,000 – 720,000",null,null,null,null,null,null,null,"(Limited) Entry Ratio = Aggregate Deductible / Expected Limited Aggregate Loss"]
[null,30,"720,001 – 830,000",null,null,null,null,null,null,null,{"$f":"E8/E7","v":1.6}]
[null,29,"830,001 – 990,000"]
[null,28,"990,001 – 1,180,000",null,null,null,null,null,null,null,"Look up Table M insurance charge based on entry ratio and expected loss group"]
[null,27,"1,180,001 – 1,415,000",null,null,null,null,null,null,null,{"$f":"F26","v":0.13220000000000001}]
[null,26,"1,415,001 – 1,744,000"]
[null,null,null,null,null,null,null,null,null,null,"Expected loss cost = Expected per-occurrence excess loss + (Expected Limited Agg Losses)*(Table M insurance charge)"]
[null,"Table of Insurance Charges",null,null,null,null,null,null,null,null,{"$f":"(E6-E7)+E7*K17","v":266100}]
[null,null,"Expected Loss Group"]
[null,"Entry Ratio",31,30,29,28,27,26,null,"(b)","The per-occurrence limit reduces the variance of the underlying severity distribution. So "]
[null,0.43,0.5675,0.56259999999999999,0.55840000000000001,0.55430000000000001,0.54969999999999997,0.54559999999999997,null,null,"the variance of the aggregate distribution is reduced as well. The ICRLL procedure approximates"]
[null,0.6,0.48649999999999999,0.47989999999999999,0.47370000000000001,0.46760000000000002,0.46129999999999999,0.45529999999999998,null,null,"a Limited Table M by increasing the expected unlimited losses used to find the expected loss group"]
[null,1.1399999999999999,0.26579999999999998,0.25519999999999998,0.24479999999999999,0.2341,0.22339999999999999,0.21279999999999999,null,null,"because policies with larger expected losses have lower variance in their aggregate loss distribution."]
[null,1.6,0.16439999999999999,0.1537,0.13669999999999999,0.13220000000000001,0.1215,0.11070000000000001,null,null,"The ICRLL procedure also adjusts the expected loss for differences in risk and location via the"]
[null,2.67,8.2199999999999995E-2,7.6899999999999996E-2,6.8400000000000002E-2,6.6100000000000006E-2,6.08E-2,5.5399999999999998E-2,null,null,"state/hazard group multiplier to further align the variance of the loss distribution"]
[null,null,null,null,null,null,null,null,null,null,"with the variances found in the table m's."]
["(a)","Calculate the total expected loss cost for this policy using the Insurance Charge Reflecting"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$29,MATCH($A$1,'Point Grid'!$A$8:$A$29,0),MATCH(A29,'Point Grid'!$C$7:$I$7,0))","v":1.5},"Loss Limitation (ICRLL) adjustment procedure."]
[1.5]
[]
["(b)","Describe how the ICRLL procedure is used to adjust expected losses for a Workers'"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$29,MATCH($A$1,'Point Grid'!$A$8:$A$29,0),MATCH(A33,'Point Grid'!$C$7:$I$7,0))","v":0.5},"Compensation policy."]
[0.5]

{"sheet":"18","index":21,"n_rows":54,"n_cols":18,"rows_kept":54,"cut":false,"hidden":false,"merged":[[0,9,0,11],[8,2,8,5],[9,2,9,5],[3,1,4,1],[3,2,4,5],[5,2,5,5],[6,2,6,5],[7,2,7,5]]}
[18,"Finished",null,null,null,null,null,null,null,"Navigator",null,null,null,"Solution"]
[null,null,null,null,null,null,null,null,null,null,null,null,"(a)",null,"where L is the limited expected value function."]
["Points","A homogeneous group of property risks exhibit the following risk profile:"]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('18'!A1,'Point Grid'!A:A,0))","v":3.25},"Exposure Distribution","Losses as a percent of the Maximum Possible Loss (MPL)",null,null,null,null,null,null,null,null,null,null,"Loss (% of MPL)","Probabililty","(given a loss occurred)"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,0.25,{"$f":"B7/(1-$B$6)","v":0.30000000000000004}]
[null,0.8,0,null,null,null,null,null,null,null,null,null,null,0.5,{"$f":"B8/(1-$B$6)","v":0.40000000000000008}]
[null,0.06,0.25,null,null,null,null,null,null,null,null,null,null,0.75,0.20000000000000004]
[null,0.08,0.5,null,null,null,null,null,null,null,null,null,null,1,0.10000000000000002]
[null,0.04,0.75]
[null,0.02,1,null,null,null,null,null,null,null,null,null,null,"Expected Loss",{"$f":"SUMPRODUCT(N5:N8,O5:O8)","v":0.52500000000000002}]
[]
["(a)","Plot the exposure curve, G(x). Label the axes and the points on the curve.",null,null,null,null,null,null,null,null,null,null,null,"x (% of MPL)","G(x)"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$29,MATCH($A$1,'Point Grid'!$A$8:$A$29,0),MATCH(A12,'Point Grid'!$C$7:$I$7,0))","v":2},null,null,null,null,null,null,null,null,null,null,null,null,0,0]
[2,null,null,null,null,null,null,null,null,null,null,null,null,0.25,{"$f":"(MIN(N14,$N$5)*$O$5+MIN(N14,$N$6)*$O$6+MIN(N14,$N$7)*$O$7+MIN(N14,$N$8)*$O$8)/$O$10","v":0.47619047619047628}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,0.5,{"$f":"(MIN(N15,$N$5)*$O$5+MIN(N15,$N$6)*$O$6+MIN(N15,$N$7)*$O$7+MIN(N15,$N$8)*$O$8)/$O$10","v":0.80952380952380953}]
["(b)","Determine the parameters b and g for the 2-parameter MBBEFD distribution that fits this ",null,null,null,null,null,null,null,null,null,null,null,0.75,0.95238095238095233]
[{"$f":"INDEX('Point Grid'!$C$8:$I$29,MATCH($A$1,'Point Grid'!$A$8:$A$29,0),MATCH(A16,'Point Grid'!$C$7:$I$7,0))","v":0.75},"exposure curve using the following information:",null,null,null,null,null,null,null,null,null,null,null,1,1]
[0.75]
[null,"Calculated value of parameter b"]
[null,null,"μ"]
[null,"Parameter g",0.4,0.42499999999999999,0.45,0.47499999999999998,0.5,0.52500000000000002,0.55000000000000004,0.57499999999999996,0.6]
[null,50,2.3E-3,1.6999999999999999E-3,1.2999999999999999E-3,8.9999999999999998E-4,5.0000000000000001E-4,2.9999999999999997E-4,2.0000000000000001E-4,1E-4,1E-4]
[null,25,9.5999999999999992E-3,7.3000000000000001E-3,5.4999999999999997E-3,4.1000000000000003E-3,2.2000000000000001E-3,1.5E-3,1E-3,5.9999999999999995E-4,4.0000000000000002E-4]
[null,16.7,2.3900000000000001E-2,1.8100000000000002E-2,1.37E-2,1.03E-2,5.7000000000000002E-3,3.8999999999999998E-3,2.5999999999999999E-3,1.6999999999999999E-3,1E-3]
[null,12.5,4.8300000000000003E-2,3.6499999999999998E-2,2.76E-2,2.0899999999999998E-2,1.18E-2,8.0999999999999996E-3,5.4999999999999997E-3,3.5999999999999999E-3,2.3E-3]
[null,10,8.77E-2,6.59E-2,4.9700000000000001E-2,3.7499999999999999E-2,2.12E-2,1.47E-2,0.01,6.7000000000000002E-3,4.4000000000000003E-3]
[null,8.3000000000000007,0.14979999999999999,0.1116,8.3599999999999994E-2,6.2799999999999995E-2,3.5400000000000001E-2,2.4500000000000001E-2,1.6799999999999999E-2,1.1299999999999999E-2,7.4999999999999997E-3]
[null,7.1,0.247,0.1817,0.13469999999999999,0.1004,5.6099999999999997E-2,3.8899999999999997E-2,2.6700000000000002E-2,1.8100000000000002E-2,1.21E-2]
[null,6.3,0.4,0.28920000000000001,0.21160000000000001,0.15609999999999999,8.6099999999999996E-2,5.9400000000000001E-2,4.0800000000000003E-2,2.7699999999999999E-2,1.8499999999999999E-2]
[null,5.6,6.4460000000000003E-2,0.45569999999999999,0.32769999999999999,0.23849999999999999,0.12909999999999999,8.8499999999999995E-2,6.0499999999999998E-2,4.1099999999999998E-2,2.76E-2]
[null,5,1.0468999999999999,0.71899999999999997,0.55049999999999999,0.36159999999999998,0.191,0.12970000000000001,8.8200000000000001E-2,5.9700000000000003E-2,0.04]
[]
["(c)","A reinsurer uses this exposure curve to price a property excess of loss treaty where the "]
[{"$f":"INDEX('Point Grid'!$C$8:$I$29,MATCH($A$1,'Point Grid'!$A$8:$A$29,0),MATCH(A33,'Point Grid'!$C$7:$I$7,0))","v":0.5},"underlying limit is $100 million. Calculate the proportion of total losses in the layer"]
[0.5,"$25 million excess of $25 million.",null,null,null,null,null,null,null,null,null,null,"(b)"]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"p is the probability of a total loss, given a loss occurred."]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"p =",{"$f":"B10/(1-B6)","v":0.10000000000000002},"← Alice: \"You could have used linear interpolation to estimate G'(1)\""]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"g = ",{"$f":"1/O38","v":9.9999999999999982}]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"Use linear interpolation to estimate G'(0)"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"G'(0) =",{"$f":"(O14-O13)/(N14-N13)","v":1.9047619047619051}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"μ =",{"$f":"1/O42","v":0.52499999999999991}]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"Look up b using the table and calculated values of g and μ"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"b = ",{"$f":"H26","v":1.47E-2}]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,"(c)"]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"MPL = ",{"$f":"100*10^6","v":100000000}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"Limit =",{"$f":"25*10^6","v":25000000}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"Retention =",{"$f":"25*10^6","v":25000000}]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"Exposure =",{"$f":"\"G(\"&(O51+O52)/O50&\") - G(\"&O52/O50&\")\"","v":"G(0.5) - G(0.25)"},"=",{"$f":"O15-O14","v":0.33333333333333326}," using the table in part (a) above."]

{"sheet":"19","index":22,"n_rows":29,"n_cols":16,"rows_kept":29,"cut":false,"hidden":false,"merged":[[0,6,0,7],[10,6,11,6],[10,2,11,2],[10,1,11,1],[10,3,11,3],[10,4,11,4],[10,5,11,5]]}
[19,"Finished",null,null,null,null,"Navigator",null,null,"Solution"]
[null,null,null,null,null,null,null,null,"(a)","Retention",500000,null,"Limit",500000]
["Points","A primary insurer wants to institute a swing plan with the following terms:"]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('19'!A1,'Point Grid'!A:A,0))","v":3},"   • Layer: $500,000 xs $500,000 per claim",null,null,null,null,null,null,null,"Year","Layer Loss","Premium","Loss Ratio"]
[null,"   • Premium = Layer Losses * 1.3",null,null,null,null,null,null,null,2012,{"$f":"MAX(0,MIN($N$2,D13-$K$2))+MAX(0,MIN($N$2,E13-$K$2))+MAX(0,MIN($N$2,F13-$K$2))+MAX(0,MIN($N$2,G13-$K$2))","v":12017},{"$f":"IF(K5*1.3<100000,100000,IF(K5*1.3>325000,325000,K5*1.3))","v":100000},{"$f":"K5/L5","v":0.12017}]
[null,"   •  Minimum Premium = $100,000",null,null,null,null,null,null,null,2013,{"$f":"MAX(0,MIN($N$2,D14-$K$2))+MAX(0,MIN($N$2,E14-$K$2))+MAX(0,MIN($N$2,F14-$K$2))+MAX(0,MIN($N$2,G14-$K$2))","v":0},{"$f":"IF(K6*1.3<100000,100000,IF(K6*1.3>325000,325000,K6*1.3))","v":100000},{"$f":"K6/L6","v":0}]
[null,"   • Maximum Premium = $325,000",null,null,null,null,null,null,null,2014,204895,266363.5,0.76923076923076927]
[null,null,null,null,null,null,null,null,null,2015,500000,325000,1.5384615384615385]
[null,"The reinsurer has analyzed the insurer's experience. All claims are closed and trend has",null,null,null,null,null,null,null,2016,154158,200405.4,0.76923076923076927]
[null,"been applied."]
[null,"Accident Year","On-Level Premium","Claim #1","Claim #2","Claim #3","Claim #4",null,null,"Expected reinsurer loss ratio using equal weights for each year",null,null,null,null,null,{"$f":"SUM(M5:M9)/COUNT(M5:M9)","v":0.63941861538461542}]
[]
[null,2012,400000,465899,230567,512017,null,null,null,"Alice: \"The 2nd CAS sample solution to part (a) is technically incorrect because it is the weighted average loss ratio\""]
[null,2013,420000,240785,125766,80154,470666,null,null,"which doesn't give each year equal weight. To see this, note the denominator in each average used"]
[null,2014,1200000,704895,106528,null,null,null,null,"in the sample solution will cancel out, leaving the weighted average formula. However, the"]
[null,2015,2000000,45988,349145,376011,1246852,null,null,"examiner's report does say the CAS accepted the weighted average answer.\""]
[null,2016,2200000,437100,654158,250000,156750]
[null,null,null,null,null,null,null,null,"(b)","An empirical distribution model is not appropriate because there are not many claims within"]
["(a)","Calculate the expected loss ratio for the reinsurer based on the empirical distribution",null,null,null,null,null,null,null,"the layer, so the data isn't credible. That is, we don't know it will reflect all possible outcomes."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$29,MATCH($A$1,'Point Grid'!$A$8:$A$29,0),MATCH(A19,'Point Grid'!$C$7:$I$7,0))","v":1.5},"model using equal weights for each year."]
[1.5,null,null,null,null,null,null,null,"(c)","1. A collective risk model assumes frequency and severity are independent and that all "]
[null,null,null,null,null,null,null,null,null,"    observations are independent. These assumptions may not hold."]
["(b)","Determine if an empirical distribution model is appropriate for this account and give one",null,null,null,null,null,null,null,"2. Collective risk models involve complex calculations and can give a false impression of accuracy."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$29,MATCH($A$1,'Point Grid'!$A$8:$A$29,0),MATCH(A23,'Point Grid'!$C$7:$I$7,0))","v":0.5},"reason in support of your answer.",null,null,null,null,null,null,null,"     You need to check the reasonableness of the results."]
[0.5]
[]
["(c)","The use of a collective risk model is an alternative approach. Explain two concerns"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$29,MATCH($A$1,'Point Grid'!$A$8:$A$29,0),MATCH(A27,'Point Grid'!$C$7:$I$7,0))","v":1},"when using this type of model."]
[1]

{"sheet":"20","index":23,"n_rows":35,"n_cols":12,"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)","A is the damage ratio at each intensity (the damage ratio curve)."]
["Points","An earthquake model produces the following damage function for a $1,000,000 home in",null,null,null,null,null,null,"B is the uncertainty in the damage ratio at the given intensities."]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('20'!A1,'Point Grid'!A:A,0))","v":2.5},"California:",null,null,null,null,null,null,"C is the probability distribution for the earthquake intensity felt by the home given "]
[null,null,null,null,null,null,null,null,"a particular earthquake has occurred."]
[]
[null,null,null,null,null,null,null,"(b)","Reading the damage ratios from the given chart."]
[null,null,null,null,null,null,null,null,"EQ Intensity","Probability","Damage Ratio","Expected Damage"]
[null,null,null,null,null,null,null,null,{"$f":"C25","v":3.5},0.1,0.2,{"$f":"K9*10^6","v":200000}]
[null,null,null,null,null,null,null,null,6,0.05,0.45,{"$f":"K10*10^6","v":450000}]
[null,null,null,null,null,null,null,null,8.5,0.02,0.8,800000]
[]
[null,null,null,null,null,null,null,null,"Expected Loss",{"$f":"SUMPRODUCT(J9:J11,L9:L11)","v":58500}]
[]
[null,null,null,null,null,null,null,null,"Expected Loss Squared =",null,{"$f":"J9*L9^2+J10*L10^2+J11*L11^2","v":26925000000}]
[null,null,null,null,null,null,null,null,"Std Dev =",{"$f":"SQRT(K15-J13^2)","v":153306.06641617283}]
[]
[null,null,null,null,null,null,null,null,"Risk Load =",{"$f":"8%*J16","v":12264.485313293828}]
[]
[null,null,null,null,null,null,null,null,"Premium = (Expected Loss + Risk Load)/(1 - Expense Load)"]
[null,null,null,null,null,null,null,null,{"$f":"(J13+J18)/(1-20%)","v":88455.606641617283}]
[]
[null,"The probability of an event occurring with a given intensity is:"]
[null,null,"Intensity","Probability"]
[null,null,3.5,0.1]
[null,null,6,0.05]
[null,null,8.5,0.02]
[]
["(a)","Identify what A, B, and C represent in the above graph."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$29,MATCH($A$1,'Point Grid'!$A$8:$A$29,0),MATCH(A29,'Point Grid'!$C$7:$I$7,0))","v":0.75}]
[0.75]
[]
["(b)","Determine the premium for this home given the following information:"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$29,MATCH($A$1,'Point Grid'!$A$8:$A$29,0),MATCH(A33,'Point Grid'!$C$7:$I$7,0))","v":1.75},"• The insurer's expense load is 20% of premium"]
[1.75,"• The risk load is set to 8% of the standard deviation of the loss."]