{"text_format":"3","workbook":"xlsx","n_sheets":24,"sheets":["Instructions","Point Grid","Navigator","1","2","3","4","5","6","7","8","9","10","11","12","13","14","15","16","17","18","19","20","21"],"not_rendered":{"charts":1,"images":2},"read":"whole","date_system":"1900"}

{"sheet":"Instructions","index":0,"n_rows":32,"n_cols":1,"rows_kept":32}
[]
[]
[]
[]
[]
["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":"Point Grid","index":1,"n_rows":32,"n_cols":19,"rows_kept":32,"merged":[[5,1,6,1]]}
[]
[]
[]
[]
["Practice Exam 2 – Exam 8"]
[null,"Total Point Value","Sub-Part of Question",{"$nulls":8},"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":5.75},{"v":2.5,"d":"2.50"},{"v":2,"d":"2.00"},{"v":0.5,"d":"0.50"},0.75,{"$nulls":3},{"$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":{"v":2.5,"d":"2.50"}},{"v":2,"d":"2.00"},{"v":0.5,"d":"0.50"},0.75,"","",""]
[{"$f":"A8+1","v":2},{"$f":"SUM(C9:I9)","v":2.75},{"v":1,"d":"1.00"},{"v":1,"d":"1.00"},0.75,{"$nulls":4},{"$f":"INDIRECT(\"'\"&A9&\"'!B1\")","v":"Finished"},null,{"$f":"A9","v":2},{"v":1,"d":"1.00"},{"v":1,"d":"1.00"},0.75,"","","",""]
[{"$f":"A9+1","v":3},{"v":2.5,"d":"2.50"},{"v":2,"d":"2.00"},{"v":0.5,"d":"0.50"},{"$nulls":5},"Finished",null,3,{"v":2,"d":"2.00"},{"v":0.5,"d":"0.50"},"","","","",""]
[4,{"v":2,"d":"2.00"},{"v":1.5,"d":"1.50"},{"v":0.5,"d":"0.50"},{"$nulls":5},"Finished",null,4,{"v":1.5,"d":"1.50"},{"v":0.5,"d":"0.50"},"","","","",""]
[5,4.25,0.75,{"v":1,"d":"1.00"},{"v":1,"d":"1.00"},{"v":0.5,"d":"0.50"},{"v":1,"d":"1.00"},null,null,"Finished",null,5,0.75,{"v":1,"d":"1.00"},{"v":1,"d":"1.00"},{"v":0.5,"d":"0.50"},{"v":1,"d":"1.00"},"",""]
[6,4.75,0.75,{"v":1,"d":"1.00"},{"v":0.5,"d":"0.50"},{"v":0.5,"d":"0.50"},1.25,0.75,null,"Finished",null,6,0.75,{"v":1,"d":"1.00"},{"v":0.5,"d":"0.50"},{"v":0.5,"d":"0.50"},1.25,0.75,""]
[7,2.75,0.75,{"v":2,"d":"2.00"},{"$nulls":5},"Finished",null,7,0.75,{"v":2,"d":"2.00"},"","","","",""]
[8,{"v":3.5,"d":"3.50"},{"v":3,"d":"3.00"},{"v":0.5,"d":"0.50"},{"$nulls":5},"Finished",null,8,{"v":3,"d":"3.00"},{"v":0.5,"d":"0.50"},"","","","",""]
[9,{"v":3.5,"d":"3.50"},{"v":1,"d":"1.00"},{"v":1,"d":"1.00"},{"v":1.5,"d":"1.50"},{"$nulls":4},"Finished",null,9,{"v":1,"d":"1.00"},{"v":1,"d":"1.00"},{"v":1.5,"d":"1.50"},"","","",""]
[10,{"v":5.5,"d":"5.50"},{"v":2.5,"d":"2.50"},{"v":2.5,"d":"2.50"},{"v":0.5,"d":"0.50"},{"$nulls":4},"Finished",null,10,{"v":2.5,"d":"2.50"},{"v":2.5,"d":"2.50"},{"v":0.5,"d":"0.50"},"","","",""]
[11,{"v":3.5,"d":"3.50"},{"v":2,"d":"2.00"},{"v":1,"d":"1.00"},{"v":0.5,"d":"0.50"},{"$nulls":4},"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":{"v":2,"d":"2.00"}},{"v":1,"d":"1.00"},{"v":0.5,"d":"0.50"},"","","",""]
[12,{"v":2,"d":"2.00"},{"v":2,"d":"2.00"},{"$nulls":6},"Finished",null,12,{"v":2,"d":"2.00"},"","","","","",""]
[13,{"v":4,"d":"4.00"},{"v":0.5,"d":"0.50"},{"v":1.5,"d":"1.50"},{"v":2,"d":"2.00"},{"$nulls":4},"Finished",null,13,{"v":0.5,"d":"0.50"},{"v":1.5,"d":"1.50"},{"v":2,"d":"2.00"},"","","",""]
[14,3.25,3.25,{"$nulls":6},"Finished",null,14,3.25,"","","","","",""]
[15,{"v":2,"d":"2.00"},{"v":1,"d":"1.00"},{"v":1,"d":"1.00"},{"$nulls":5},"Finished",null,15,{"v":1,"d":"1.00"},{"v":1,"d":"1.00"},"","","","",""]
[16,{"v":2,"d":"2.00"},{"v":2,"d":"2.00"},{"$nulls":6},"Finished",null,16,{"v":2,"d":"2.00"},"","","","","",""]
[17,{"v":1,"d":"1.00"},{"v":1,"d":"1.00"},{"$nulls":6},"Finished",null,17,{"v":1,"d":"1.00"},"","","","","",""]
[18,3.25,{"v":2,"d":"2.00"},{"v":1,"d":"1.00"},0.25,{"$nulls":4},"Finished",null,18,{"v":2,"d":"2.00"},{"v":1,"d":"1.00"},0.25,"","","",""]
[19,{"v":2.5,"d":"2.50"},{"v":1,"d":"1.00"},{"v":0.5,"d":"0.50"},{"v":1,"d":"1.00"},{"$nulls":4},"Finished",null,19,{"v":1,"d":"1.00"},{"v":0.5,"d":"0.50"},{"v":1,"d":"1.00"},"","","",""]
[20,2.75,0.75,{"v":0.5,"d":"0.50"},{"v":0.5,"d":"0.50"},{"v":1,"d":"1.00"},{"$nulls":3},"Finished",null,20,0.75,{"v":0.5,"d":"0.50"},{"v":0.5,"d":"0.50"},{"v":1,"d":"1.00"},"","",""]
[21,{"$f":"SUM(C28:I28)","v":{"v":4,"d":"4.00"}},{"v":2.5,"d":"2.50"},{"v":1,"d":"1.00"},{"v":0.5,"d":"0.50"},{"$nulls":4},{"$f":"INDIRECT(\"'\"&A28&\"'!B1\")","v":"Finished"},null,{"$f":"A28","v":21},{"v":2.5,"d":"2.50"},{"v":1,"d":"1.00"},{"v":0.5,"d":"0.50"},"","","",""]
[]
["Total",{"$f":"SUM(B8:B28)","v":{"v":67.5,"d":"67.50"}},{"$nulls":9},"TOTAL",{"$f":"IF(SUM(IF(M8:S28=\"\",0,1))=0,\"\",SUM(M8:S28))","v":{"v":67.5,"d":"67.50"}},null,"Percentage:",{"$f":"IF(M30=\"\",\"\",M30/B30)","v":{"v":1,"d":"100.0%"}}]
[]
[{"$nulls":11},"Passing Score:",{"$f":"MROUND(72%*B30,0.25)","v":{"v":48.5,"d":"48.50"}}]

{"sheet":"Navigator","index":2,"n_rows":29,"n_cols":8,"rows_kept":29,"merged":[[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],[19,0,19,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],[26,0,26,6],[27,0,27,6],[28,0,28,6]]}
[]
[]
[]
[]
[]
["Navigator – Select a question to go to it"]
[]
["Question #",{"$nulls":6},"Status"]
["Q1 -- Mahler -- Shifting Risk Parameters -- 5.75 points",{"$nulls":6},{"$f":"'Point Grid'!J8","v":"Finished"}]
["Q2 -- Goldburd (GLM) -- GLM offsets -- 2.75 points",{"$nulls":6},{"$f":"'Point Grid'!J9","v":"Finished"}]
["Q3 -- Goldburd (GLM) -- Number of Parameters within a GLM -- 2.50 points",{"$nulls":6},{"$f":"'Point Grid'!J10","v":"Finished"}]
["Q4 -- Goldburd (GLM) -- Odds ratio -- 2.00 points",{"$nulls":6},{"$f":"'Point Grid'!J11","v":"Finished"}]
["Q5 -- Goldburd (GLM) -- Calculating with interactions -- 4.25 points",{"$nulls":6},{"$f":"'Point Grid'!J12","v":"Finished"}]
["Q6 -- Couret & Venter -- Estimate variances, credibilities, and expected claims -- 4.75 points",{"$nulls":6},{"$f":"'Point Grid'!J13","v":"Finished"}]
["Q7 -- ISO Rating -- Company Subject Loss Cost -- 2.75 points",{"$nulls":6},{"$f":"'Point Grid'!J14","v":"Finished"}]
["Q8 -- Fisher -- Quintiles & Efficiency Tests -- 3.50 points",{"$nulls":6},{"$f":"'Point Grid'!J15","v":"Finished"}]
["Q9 -- Fisher -- Experience & Schedule Rating -- 3.50 points",{"$nulls":6},{"$f":"'Point Grid'!J16","v":"Finished"}]
["Q10 -- NCCI Experience Rating -- Calculate experience modifications -- 5.50 points",{"$nulls":6},{"$f":"'Point Grid'!J17","v":"Finished"}]
["Q11 -- Bailey & Simon -- Single car-year credibility -- 3.50 points",{"$nulls":6},{"$f":"'Point Grid'!J18","v":"Finished"}]
["Q12 -- Fisher -- Balance Equations -- 2.00 points",{"$nulls":6},{"$f":"'Point Grid'!J19","v":"Finished"}]
["Q13 -- Fisher -- Tables M and L -- 4.00 points",{"$nulls":6},{"$f":"'Point Grid'!J20","v":"Finished"}]
["Q14 -- Fisher -- Large Dollar Deductible Premium -- 3.25 points",{"$nulls":6},{"$f":"'Point Grid'!J21","v":"Finished"}]
["Q15 -- Fisher -- Tables M and L -- 2.00 points",{"$nulls":6},{"$f":"'Point Grid'!J22","v":"Finished"}]
["Q16 -- Bahnemann -- Risk Loaded ILFs -- 2.00 points",{"$nulls":6},{"$f":"'Point Grid'!J23","v":"Finished"}]
["Q17 -- Bahnemann -- Excess Layer Inflation -- 1.00 point",{"$nulls":6},{"$f":"'Point Grid'!J24","v":"Finished"}]
["Q18 -- NCCI Retrospective Rating -- Preparation for Panjer's Recursive Algorithm -- 3.25 points",{"$nulls":6},{"$f":"'Point Grid'!J25","v":"Finished"}]
["Q19 -- Bahnemann -- Loss Elimination Ratios -- 2.50 points",{"$nulls":6},{"$f":"'Point Grid'!J26","v":"Finished"}]
["Q20 -- Bahnemann -- Inflation and the Pareto Distribution -- 2.75 points",{"$nulls":6},{"$f":"'Point Grid'!J27","v":"Finished"}]
["Q21 -- Holmes -- Interpreting a Lasso Credibility Model -- 4.00 points",{"$nulls":6},{"$f":"'Point Grid'!J28","v":"Finished"}]

{"sheet":"1","index":3,"n_rows":70,"n_cols":14,"rows_kept":70,"merged":[[0,6,0,7]]}
[{"$f":"VALUE(MID(CELL(\"filename\",A1),FIND(\"]\",CELL(\"filename\",A1))+1,LEN(CELL(\"filename\",A1))-FIND(\"]\",CELL(\"filename\",A1))+1))","v":1},"Finished",{"$nulls":4},"Navigator",null,null,"Solution"]
[{"$nulls":8},"a.)","Observe the book of business has grown rapidly over time. We need to account for this by working on a per-exposure basis."]
["Points","A State Regulator is reviewing a Homeowners rate filing made by XYZ Insuance Company (XYZ)."]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('1'!A1,'Point Grid'!A:A,0))","v":5.75},"XYZ provided 15 years of historical data as of 12/31/2022 in support of their catastrophe provision:",{"$nulls":7},"First calculate the ratio of catastrophe losses to non-catastrophe losses"]
[{"$nulls":9},"Year","NC / EE","Actual Cat/EE","Expected Cat/EE","(A-E)^2/E"]
[null,"Accident","Earned","Case Incurred Loss and DCC ($000)",{"$nulls":5},{"$f":"B8","v":2007},{"$f":"E8/C8","v":{"v":5.4209919261822375,"d":"5.421"}},{"$f":"D8/C8","v":{"v":0,"d":"0.000"}},{"$f":"K6*($B$26-1)","v":{"v":4.708939436518076,"d":"4.709"}},{"$f":"(L6-M6)^2/M6","v":{"v":4.708939436518076,"d":"4.709"}}]
[null,"Year","Exposures","Catastrophic","Non-Catastrophic",{"$nulls":4},2008,{"$f":"E9/C9","v":{"v":3.5056608921020325,"d":"3.506"}},{"$f":"D9/C9","v":{"v":0,"d":"0.000"}},{"$f":"K7*($B$26-1)","v":{"v":3.0451889710716116,"d":"3.045"}},{"$f":"(L7-M7)^2/M7","v":{"v":3.0451889710716116,"d":"3.045"}}]
[null,2007,{"v":2601,"d":"2,601"},0,{"v":14100,"d":"14,100"},{"$nulls":4},2009,{"v":2.0935101186322402,"d":"2.094"},{"$f":"D10/C10","v":{"v":0.6995115143056525,"d":"0.700"}},{"$f":"K8*($B$26-1)","v":{"v":1.8185255563218834,"d":"1.819"}},{"$f":"(L8-M8)^2/M8","v":{"v":0.6885756550829918,"d":"0.689"}}]
[null,2008,{"v":7331,"d":"7,331"},0,{"v":25700,"d":"25,700"},{"$nulls":4},2010,{"v":1.6123358348968104,"d":"1.612"},{"v":0,"d":"0.000"},{"v":1.4005539763280686,"d":"1.401"},{"v":1.4005539763280686,"d":"1.401"}]
[null,2009,{"v":14330,"d":"14,330"},{"v":10024,"d":"10,024"},{"v":30000,"d":"30,000"},{"$nulls":4},2011,{"v":1.660431507200328,"d":"1.660"},{"v":0.5581304771178188,"d":"0.558"},{"v":1.4423322359380921,"d":"1.442"},{"v":0.5420476162292622,"d":"0.542"}]
[null,2010,{"v":17056,"d":"17,056"},0,{"v":27500,"d":"27,500"},{"$nulls":4},2012,{"v":1.9700658191326301,"d":"1.970"},{"v":0,"d":"0.000"},{"v":1.7112957839771679,"d":"1.711"},{"v":1.7112957839771679,"d":"1.711"}]
[null,2011,{"v":19513,"d":"19,513"},{"v":10890.8,"d":"10,891"},{"v":32400,"d":"32,400"},{"$nulls":4},2013,{"v":1.8670535680195488,"d":"1.867"},{"v":2.399068382268718,"d":"2.399"},{"v":1.6218142908636906,"d":"1.622"},{"v":0.3724988280157104,"d":"0.372"}]
[null,2012,{"v":22182,"d":"22,182"},0,{"v":43700,"d":"43,700"},{"$nulls":4},2014,{"v":1.5633199834277034,"d":"1.563"},{"v":0,"d":"0.000"},{"v":1.3579764039685498,"d":"1.358"},{"v":1.3579764039685498,"d":"1.358"}]
[null,2013,{"v":26191,"d":"26,191"},{"v":62834,"d":"62,834"},{"v":48900,"d":"48,900"},{"$nulls":4},2015,{"v":1.5673473792117403,"d":"1.567"},{"v":1.2210553292508566,"d":"1.221"},{"v":1.3614747974530195,"d":"1.361"},{"v":0.014482550163297189,"d":"0.014"}]
[null,2014,{"v":36205,"d":"36,205"},0,{"v":56600,"d":"56,600"},{"$nulls":4},2016,{"v":2.0812529563252222,"d":"2.081"},{"v":2.2961686025122194,"d":"2.296"},{"v":1.8078783840417416,"d":"1.808"},{"v":0.1318823984835265,"d":"0.132"}]
[null,2015,{"v":37069,"d":"37,069"},{"v":45263.3,"d":"45,263"},{"v":58100,"d":"58,100"},{"$nulls":4},2017,{"v":2.1295725441841347,"d":"2.130"},{"v":1.5545879572544183,"d":"1.555"},{"v":1.849851147684165,"d":"1.850"},{"v":0.04712830636772815,"d":"0.047"}]
[null,2016,{"v":38054,"d":"38,054"},{"v":87378.4,"d":"87,378"},{"v":79200,"d":"79,200"},{"$nulls":4},2018,{"v":2.1031679416772584,"d":"2.103"},{"v":0.30957576920309837,"d":"0.310"},{"v":1.8269148150455405,"d":"1.827"},{"v":1.2602217471101198,"d":"1.260"}]
[null,2017,{"v":38928,"d":"38,928"},{"v":60517,"d":"60,517"},{"v":82900,"d":"82,900"},{"$nulls":4},2019,{"v":2.0845147095515686,"d":"2.085"},{"v":4.2014357974005945,"d":"4.201"},{"v":1.8107117028529263,"d":"1.811"},{"v":3.15652772732701,"d":"3.157"}]
[null,2018,{"v":41699,"d":"41,699"},{"v":12909,"d":"12,909"},{"v":87700,"d":"87,700"},{"$nulls":4},2020,{"v":2.0807480943095196,"d":"2.081"},{"v":2.6019522247828397,"d":"2.602"},{"v":1.8074398361360973,"d":"1.807"},{"v":0.34925087026001583,"d":"0.349"}]
[null,2019,{"v":44087,"d":"44,087"},{"v":185228.7,"d":"185,229"},{"v":91900,"d":"91,900"},{"$nulls":4},2021,{"v":2.200172562553926,"d":"2.200"},{"$f":"D22/C22","v":{"v":4.078654466191363,"d":"4.079"}},{"v":1.9111777859170578,"d":"1.911"},{"v":2.4581465911496347,"d":"2.458"}]
[null,2020,{"v":45128,"d":"45,128"},{"v":117420.9,"d":"117,421"},{"v":93900,"d":"93,900"}]
[null,2021,{"v":44042,"d":"44,042"},{"v":179632.1,"d":"179,632"},{"v":96900,"d":"96,900"},{"$nulls":4},"Total",{"$nulls":3},{"$f":"SUM(N6:N20)","v":{"v":21.24471686205277,"d":"21.245"}}]
[]
[null,{"v":1.6556371763675823,"d":"1.656"},"15-year straight average factor",{"$nulls":6},"Degrees of Freedom:",null,{"$f":"15-1","v":14},"Since we estimated the 15-year weighted average."]
[null,{"v":1.9164288509711265,"d":"1.916"},"10-year straight average factor"]
[null,{"v":1.8686490407364196,"d":"1.869"},"15-year exposure weighted average factor",{"$nulls":6},"Chi-squared statistic with 14 degrees of freedom and α = 0.1"]
[null,{"v":1.9797178502892394,"d":"1.980"},"10-year exposure weighted average factor",{"$nulls":6},{"$f":"_xlfn.CHISQ.INV(1-0.1,14)","v":{"v":21.06414421299706,"d":"21.064"}}]
[]
[null,"• XYZ selected the 10-year exposure weighted average catastrophe provision, citing a recent ",{"$nulls":7},"Since the test statistic is 21.245 which is greater than the chi-squared statistic of 21.064, we reject the null hypothesis"]
[null,"upward trend in the ratio of catastrophe to non-catastrophe claims.",{"$nulls":7},"that the risk parameters are not changing over time."]
[{"$nulls":9},"We agree with XYZ's statement."]
["(a)","Apply Mahler's Chi-squared test with α = 0.1 to evaluate whether the catastrophe provision "]
[{"$f":"INDEX('Point Grid'!$C$8:$I$27,MATCH($A$1,'Point Grid'!$A$8:$A$27,0),MATCH(A32,'Point Grid'!$C$7:$I$7,0))","v":2.5},"is changing over time. Use the 15-year weighted average factor to estimate the expected",{"$nulls":7},"Note: If you didn't control for the exposure growth you would incorrectly not reject the null hypothesis."]
[2.5,"catastrophe losses. Explain whether you agree with XYZ's statement."]
[{"$nulls":8},"b.)","Year","Cat/Non-Cat","Estimate","(X_Act - X_Est)^2"]
[null,"The actuary at the regulator wonders if the selected provision is too high.",{"$nulls":7},{"$f":"B13","v":2012},{"$f":"D13/E13","v":{"v":0,"d":"0.000"}},"NA","NA"]
[null,"The latest available industry catastrophe provision (applied to non-CAT claims) is:",{"$nulls":4},{"v":1.85,"d":"1.850"},null,null,{"$f":"B14","v":2013},{"$f":"D14/E14","v":{"v":1.2849488752556237,"d":"1.285"}},"NA","NA"]
[{"$nulls":9},2014,{"v":0,"d":"0.000"},"NA","NA"]
[null,"The actuary uses the latest 10 years of data provided by XYZ and estimates the catastrophe to",{"$nulls":7},2015,{"$f":"D16/E16","v":{"v":0.7790585197934596,"d":"0.779"}},{"$f":"$K$47/3*(K36+K37+K38)+(1-$K$47)*($G$37-1)","v":{"v":0.4283162917518746,"d":"0.428"}},{"$f":"(L39-K39)^2","v":{"v":0.12302011053157526,"d":"0.1230"}}]
[null,"non-catastrophe claims ratio by giving weight Z/3 to each of the preceding three years with the ",{"$nulls":7},2016,{"v":1.1032626262626262,"d":"1.103"},{"v":0.6880024650163611,"d":"0.688"},{"$f":"(L40-K40)^2","v":{"v":0.17244100151827407,"d":"0.1724"}}]
[null,"complement being the industry ratio.",{"$nulls":7},2017,{"v":0.73,"d":"0.730"},{"v":0.6274403820186952,"d":"0.627"},{"v":0.010518475240471168,"d":"0.0105"}]
[{"$nulls":9},2018,{"v":0.14719498289623717,"d":"0.147"},{"v":0.8707737153520286,"d":"0.871"},{"v":0.5235661820623297,"d":"0.5236"}]
["(b)","Calculate the optimal credibility to the nearest 10% using the mean squared error criterion.",{"$nulls":7},2019,{"v":2.0155462459194777,"d":"2.016"},{"v":0.6601525363862877,"d":"0.660"},{"v":1.8370921078421416,"d":"1.8371"}]
[{"$f":"INDEX('Point Grid'!$C$8:$I$27,MATCH($A$1,'Point Grid'!$A$8:$A$27,0),MATCH(A43,'Point Grid'!$C$7:$I$7,0))","v":2},{"$nulls":8},2020,{"v":1.2504888178913738,"d":"1.250"},{"v":0.9642470762719049,"d":"0.964"},{"v":0.0819343346453468,"d":"0.0819"}]
[2,{"$nulls":8},2021,{"v":1.8537884416924666,"d":"1.854"},{"v":1.1377433489023627,"d":"1.138"},{"v":0.5127205749087884,"d":"0.5127"}]
[]
["(c)","Using the method from part (b) above, estimate the future catastrophe provision which would",{"$nulls":7},"Z =",{"v":1,"d":"100%"}]
[{"$f":"INDEX('Point Grid'!$C$8:$I$27,MATCH($A$1,'Point Grid'!$A$8:$A$27,0),MATCH(A47,'Point Grid'!$C$7:$I$7,0))","v":0.5},"apply to non-catastrophic losses and opine on the reasonableness of XYZ's proposed ",{"$nulls":7},"MSE",{"$f":"SUM(M39:M45)/COUNT(M39:M45)","v":{"v":0.46589896953556104,"d":"0.4659"}}]
[0.5,"catastrophe provision."]
[{"$nulls":9},"Once the above is set up, you iterate through the values of Z in increments of 10% to complete the table below."]
["(d)","The regulatory analyst's manager contends that insufficient consideration is given to older ",{"$nulls":7},"Z","MSE","Z","MSE"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$27,MATCH($A$1,'Point Grid'!$A$8:$A$27,0),MATCH(A51,'Point Grid'!$C$7:$I$7,0))","v":0.75},"historical data under the analysis used in part b.) above. ",{"$nulls":7},{"v":0,"d":"0%"},{"v":0.44342716054999504,"d":"0.4434"},{"v":0.6,"d":"60%"},{"v":0.44367886180431876,"d":"0.4437"}]
[0.75,"Identify what would likely happen if the analysis was repeated using more years of data in the ",{"$nulls":7},{"v":0.1,"d":"10%"},{"v":0.44071257239717065,"d":"0.4407"},{"v":0.7,"d":"70%"},{"v":0.4475799657200024,"d":"0.4476"}]
[null,"catastrophe provision (not more years of data when calculating the optimal credibility) and",{"$nulls":7},{"v":0.2,"d":"20%"},{"v":0.43910059958909775,"d":"0.4391"},{"v":0.8,"d":"80%"},{"v":0.45258368498043733,"d":"0.4526"}]
[null,"briefly explain why.",{"$nulls":7},{"v":0.3,"d":"30%"},{"v":0.43859124212577605,"d":"0.4386"},{"v":0.9,"d":"90%"},{"v":0.4586900195856235,"d":"0.4587"}]
[{"$nulls":9},{"v":0.4,"d":"40%"},{"v":0.4391845000072056,"d":"0.4392"},{"v":1,"d":"100%"},{"v":0.46589896953556104,"d":"0.4659"}]
[{"$nulls":9},{"v":0.5,"d":"50%"},{"v":0.4408803732333865,"d":"0.4409"}]
[]
[{"$nulls":9},"From the table above, the optimal credibility to assign to the data is 30% (10% to each of the last three years)."]
[]
[{"$nulls":8},"c.)","Using Z =",{"v":0.3,"d":"30%"}]
[{"$nulls":9},"Catastrophe provision =",null,{"$f":"$K$61/3*(K45+K44+K43)+(1-$K$61)*($G$37-1)+1","v":{"v":2.106982350550332,"d":"2.107"}}]
[]
[{"$nulls":9},"XYZ selected a catastrophe provision of 1.980 which is higher than the industry provision but lower than that"]
[{"$nulls":9},"indicated by the optimal mean squared error process. As such, their provision is reasonable and reflects the"]
[{"$nulls":9},"shifting risk parameters."]
[]
[{"$nulls":8},"d.)","The analysis could be repeated using say 4 or 5 years instead of 3. Since the risk parameters are"]
[{"$nulls":9},"shifting over time, we should expect the optimal credibility to decline. This is because the older years are less"]
[{"$nulls":9},"reflective of the future in a changing environment."]

{"sheet":"2","index":4,"n_rows":34,"n_cols":14,"rows_kept":34,"merged":[[0,6,0,7]]}
[{"$f":"VALUE(MID(CELL(\"filename\",A1),FIND(\"]\",CELL(\"filename\",A1))+1,LEN(CELL(\"filename\",A1))-FIND(\"]\",CELL(\"filename\",A1))+1))","v":2},"Finished",{"$nulls":4},"Navigator",null,null,"Solution"]
[{"$nulls":8},"a.)","The analyst should separately estimate the relativities for the territories and deductibles and then"]
["Points","An analyst is building a generalized linear model for a rating plan. The new plan",{"$nulls":7},"include them in the GLM via offset terms."]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('2'!A1,'Point Grid'!A:A,0))","v":2.75},"will include the territory and deductible relativities from their existing  rating plan.",{"$nulls":7},"The analyst should do this for territory because there may be too many territories to realistically consider"]
[{"$nulls":9},"the territory data to be fully credible which is a core assumption when modeling with GLMs."]
["(a)","Briefly describe how the analyst should incorporate territory and deductible into",{"$nulls":7},"The analyst should do this for deductibles because GLMs often produce counterintuitive results when modeling"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$27,MATCH($A$1,'Point Grid'!$A$8:$A$27,0),MATCH(A6,'Point Grid'!$C$7:$I$7,0))","v":1},"the GLM and explain why they should do this for each.",{"$nulls":7},"limits/deductibles. This can be due to business decisions such as requiring high risk policies to have high deductibles."]
[1]
[{"$nulls":8},"b.)","Territory","ln(Relativity)",null,"Deductible","ln(Relativity)"]
["(b)","The analyst is building a pure premium model using a log-link function.",{"$nulls":7},{"$f":"B21","v":"A"},{"$f":"LN(C21)","v":{"v":0,"d":"0.0000"}},null,{"$f":"E21","v":{"v":500,"d":"$500"}},{"$f":"LN(F21)","v":{"v":0.09531017980432493,"d":"0.0953"}}]
[{"$f":"INDEX('Point Grid'!$C$8:$I$27,MATCH($A$1,'Point Grid'!$A$8:$A$27,0),MATCH(A10,'Point Grid'!$C$7:$I$7,0))","v":1},"They want to verify their model is correctly incorporating the territory and deductible",{"$nulls":7},{"$f":"B22","v":"B"},{"$f":"LN(C22)","v":{"v":0.22314355131420976,"d":"0.2231"}},null,{"$f":"E22","v":{"v":1000,"d":"$1,000"}},{"$f":"LN(F22)","v":{"v":0,"d":"0.0000"}}]
[1,"relativities which are shown below:",{"$nulls":7},{"$f":"B23","v":"C"},{"$f":"LN(C23)","v":{"v":-0.2231435513142097,"d":"-0.2231"}},null,{"$f":"E23","v":{"v":2000,"d":"$2,000"}},{"$f":"LN(F23)","v":{"v":-0.2876820724517809,"d":"-0.2877"}}]
[]
[null,"Record ID","Territory","Deductible",{"$nulls":5},"Record","Offset"]
[null,1,"A",{"v":500,"d":"$500"},{"$nulls":5},1,{"$f":"INDEX($K$10:$K$12,MATCH(C15,$J$10:$J$12,0)) + INDEX($N$10:$N$12,MATCH(D15,$M$10:$M$12,0))","v":{"v":0.09531017980432493,"d":"0.0953"}}]
[null,2,"B",{"v":1000,"d":"$1,000"},{"$nulls":5},2,{"$f":"INDEX($K$10:$K$12,MATCH(C16,$J$10:$J$12,0)) + INDEX($N$10:$N$12,MATCH(D16,$M$10:$M$12,0))","v":{"v":0.22314355131420976,"d":"0.2231"}}]
[null,3,"C",{"v":2000,"d":"$2,000"},{"$nulls":5},3,{"$f":"INDEX($K$10:$K$12,MATCH(C17,$J$10:$J$12,0)) + INDEX($N$10:$N$12,MATCH(D17,$M$10:$M$12,0))","v":{"v":-0.5108256237659906,"d":"-0.5108"}}]
[null,4,"B",{"v":2000,"d":"$2,000"},{"$nulls":5},4,{"$f":"INDEX($K$10:$K$12,MATCH(C18,$J$10:$J$12,0)) + INDEX($N$10:$N$12,MATCH(D18,$M$10:$M$12,0))","v":{"v":-0.06453852113757114,"d":"-0.0645"}}]
[]
[null,"Territory","Relativity",null,"Deductible","Relativity",null,null,"c.)","i.) TRUE"]
[null,"A",{"v":1,"d":"1.000"},null,{"v":500,"d":"$500"},{"v":1.1,"d":"1.100"},{"$nulls":3},"ii.) FALSE"]
[null,"B",{"v":1.25,"d":"1.250"},null,{"v":1000,"d":"$1,000"},{"v":1,"d":"1.000"},{"$nulls":3},"iii.) TRUE"]
[null,"C",{"v":0.8,"d":"0.800"},null,{"v":2000,"d":"$2,000"},{"v":0.75,"d":"0.750"},{"$nulls":3},"iv.) TRUE"]
[{"$nulls":9},"v.) FALSE"]
[null,"Calculate the value for each record using the method you described in part (a) above"]
[null,"which will allow the analyst to verify if the GLM is correctly set up."]
[]
["(c)","Identify which of the following are key things to consider when the actuary is "]
[{"$f":"INDEX('Point Grid'!$C$8:$I$27,MATCH($A$1,'Point Grid'!$A$8:$A$27,0),MATCH(A28,'Point Grid'!$C$7:$I$7,0))","v":0.75},"designing the risk classes for use in the GLM."]
[0.75,"i.)   Practicality – can you place a risk in the correct class accurately and quickly?"]
[null,"ii.)  Acceptability to Sales Agents – will the variable reduce their bonuses?"]
[null,"iii.) Risk of adverse selection  – are the classes sufficiently homogeneous?"]
[null,"iv.) Credibility – is there sufficient data in each class?"]
[null,"v.) Speed to Market – can they bring the product to market quicker than expected?"]

{"sheet":"3","index":5,"n_rows":21,"n_cols":12,"rows_kept":21,"merged":[[0,7,0,8]]}
[{"$f":"VALUE(MID(CELL(\"filename\",A1),FIND(\"]\",CELL(\"filename\",A1))+1,LEN(CELL(\"filename\",A1))-FIND(\"]\",CELL(\"filename\",A1))+1))","v":3},"Finished",{"$nulls":5},"Navigator",null,null,"Solution"]
[{"$nulls":9},"a.)","AIC = -2*log-likelihood + 2p where p is the number of parameters."]
["Points","You are given the following table for model selection:",{"$nulls":8},"The intercept is a single parameter and so the Age variable contributes 4 parameters."]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('3'!A1,'Point Grid'!A:A,0))","v":2.5},{"$nulls":9},"Similarly, there are 10 parameters for the Vehicle Body variable."]
[{"$nulls":6},"Number of"]
[null,"Model",{"$nulls":3},"-2*ll","Parameters","AIC",null,null,"Using the first line we get"]
[null,"Intercept + Age",{"$nulls":3},"A",5,435,null,null,"435 = A + 2*5 which yields"]
[null,"Intercept + Vehicle Body",{"$nulls":3},392,11,414,null,null,"A =",{"$f":"H7-2*G7","v":425}]
[null,"Intercept + Age + Vehicle Value",{"$nulls":3},392,"X",446]
[null,"Intercept + Age + Vehicle Body + Vehicle Value",{"$nulls":3},"B","Y",501,null,null,"Using the third line we get"]
[{"$nulls":10},"446 = 392 + 2X which yields"]
["(a)","Calculate A, B, X, and Y.",{"$nulls":8},"X =",{"$f":"(H9-F9)/2","v":27}]
[{"$f":"INDEX('Point Grid'!$C$8:$I$27,MATCH($A$1,'Point Grid'!$A$8:$A$27,0),MATCH(A12,'Point Grid'!$C$7:$I$7,0))","v":2}]
[2,{"$nulls":9},"The number of parameters found in the fourth line is then Y = X + 10."]
[{"$nulls":10},"So Y =",{"$f":"L12+10","v":37}]
["(b)","Briefly explain which model performs best according to the AIC measure."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$27,MATCH($A$1,'Point Grid'!$A$8:$A$27,0),MATCH(A16,'Point Grid'!$C$7:$I$7,0))","v":0.5},{"$nulls":9},"Using the fourth line we get"]
[0.5,{"$nulls":9},"501 = B + 2Y which yields"]
[{"$nulls":10},"B =",{"$f":"H10-2*L15","v":427}]
[]
[{"$nulls":9},"b.)","The Intercept + Vehicle Body model performs best as it has the lowest AIC score."]

{"sheet":"4","index":6,"n_rows":37,"n_cols":12,"rows_kept":37,"merged":[[0,6,0,7]]}
[{"$f":"VALUE(MID(CELL(\"filename\",A1),FIND(\"]\",CELL(\"filename\",A1))+1,LEN(CELL(\"filename\",A1))-FIND(\"]\",CELL(\"filename\",A1))+1))","v":4},"Finished",{"$nulls":4},"Navigator",null,null,"Solution"]
[{"$nulls":8},"a.)"]
["Points","You are given the following information for a fitted GLM:"]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('4'!A1,'Point Grid'!A:A,0))","v":2}]
[null,"Response variable","Occurrence of Accidents"]
[null,"Response distribution","Binomial"]
[null,"Link","Logit",{"$nulls":6},"Using the Logit link function we have ln(u/(1-u)) = x + 0.064 + 0 + -0.371 where u = 0.22."]
[{"$nulls":9},"Therefore, x =",null,{"$f":"LN(B28/(1-B28))-D15-D21-D25","v":{"v":-0.9586663733312759,"d":"-0.9587"}}]
[null,null,"Degrees of"]
[null,"Parameter"," Freedom",{"$nulls":6},"Applying the Logit link function again, this time to the parameters specified in part (a)"]
[null,"Intercept",1,"x",{"$nulls":5},"ln(u/(1-u)) =",null,{"$f":"L8+D16+D20+D24","v":{"v":0.23033362666872403,"d":"0.2303"}}]
[]
[null,"Driver's Age",2,{"$nulls":6},"So the odds ratio is ",null,{"$f":"EXP(L11)","v":{"v":1.2590199825111779,"d":"1.259"}}]
[null,1,1,0.288]
[null,2,1,0.064,{"$nulls":4},"b.)","The odds ratio measures the strength of association; in this case between the claim frequency and driver description."]
[null,3,0,0,{"$nulls":5},"The further away the odds ratio is from 1.000 (in either direction), the stronger the association. "]
[{"$nulls":9},"In this case, the odds ratio of 1.259 indicates a positive relationship between claim frequency and this type of driver."]
[null,"Area",2]
[null,"A",1,-0.036]
[null,"B",1,0.053]
[null,"C",0,0]
[]
[null,"Vehicle Body",2]
[null,"Truck",1,1.136]
[null,"Other",1,-0.371]
[null,"Sedan",0,0]
[]
[null,0.22,"The probability of a driver in age group 2, from area C "]
[null,null,"and with vehicle body type Other, having an accident."]
[]
["(a)","Calculate the odds ratio of a driver in age group 3, from area B, and with vehicle body"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$27,MATCH($A$1,'Point Grid'!$A$8:$A$27,0),MATCH(A31,'Point Grid'!$C$7:$I$7,0))","v":1.5},"type Truck having an accident."]
[1.5]
[]
["(b)","Briefly interpret the odds ratio derived in part (a) above."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$27,MATCH($A$1,'Point Grid'!$A$8:$A$27,0),MATCH(A35,'Point Grid'!$C$7:$I$7,0))","v":0.5}]
[0.5]

{"sheet":"5","index":7,"n_rows":44,"n_cols":14,"rows_kept":44,"merged":[[0,5,0,6]]}
[{"$f":"VALUE(MID(CELL(\"filename\",A1),FIND(\"]\",CELL(\"filename\",A1))+1,LEN(CELL(\"filename\",A1))-FIND(\"]\",CELL(\"filename\",A1))+1))","v":5},"Finished",{"$nulls":3},"Navigator",null,null,"Solution"]
[{"$nulls":7},"a.)","Estimated claim severity for electric vehicles that are trucks and drive 30 miles per day:"]
["Points","An actuary is working on a usage based insurance (UBI) program for a state's Private Passenger",{"$nulls":6},"ln(u) =",{"$f":"D17+D18+D20+LN(D27/20)*D21+LN(D27/20)*D22","v":9.472424822554073}]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('5'!A1,'Point Grid'!A:A,0))","v":4.25},"Automobile market. They create a generalized linear model (GLM) to predict claim severity",{"$nulls":6},"u =",{"$f":"EXP(J3)","v":{"v":12996.36292831051,"d":"12,996"}}]
[null,"by Daily Miles Driven (DMD), Vehicle Type, and Electric Vehicle Ind (EVInd)."]
[{"$nulls":8},"Estimated claim severity for non-electric vehicles that are trucks and drive 30 miles per day:"]
[null,"• Policies are effective for 6-months and the actuary uses data from 2017 through 2019.",{"$nulls":6},"ln(u) =",{"$f":"D17+D18+0*D20+LN(D27/20)*D21+0*D22","v":9.586551403640563}]
[null,"• Daily Miles Driven is a continuous variable with a base level of 20 miles per day.",{"$nulls":6},"u =",{"$f":"EXP(J7)","v":{"v":14567.545226670618,"d":"14,568"}}]
[null,"• Vehicle Type is a categorical variable with levels: Car, Truck, and Van."]
[null,"• Electric Vehicle Ind is a binary variable which classifies whether a vehicle is counted as",{"$nulls":6},"Ratio of electric to non-electric for trucks driven 30 miles per day:",{"$nulls":4},{"$f":"J4/J8","v":{"v":0.8921450200488447,"d":"0.892"}}]
[null,"   an electric vehicle or not."]
[null,"• The log-link function is chosen.",{"$nulls":5},"b.)","Typical means average, so drives the median miles per day and all other variables are at their base level as the base level"]
[null,"• An interaction variable is included as ln(DMD/20) for EVInd.",{"$nulls":6},"(should) have the highest volume of exposures for a given rating variable."]
[null,"• The model results are as follows:",{"$nulls":6},"ln(u) =",{"$f":"D17+0+0+LN(20/20)*D21+0","v":9.1198}]
[{"$nulls":8},"u =",{"$f":"EXP(J14)","v":{"v":9134.374558813251,"d":"9,134"}}]
[null,null,"Parameter","Coefficient"]
[null,null,"Intercept",9.1198,{"$nulls":3},"c.)","The actuary can use a perspective plot."]
[null,null,"Vehicle Type: Truck",0.3012,{"$nulls":4},"Hold all variables equal except for ln(DMD/20) and ln(YDE). Model with and without the interaction."]
[null,null,"Vehicle Type: Van",0.7879,{"$nulls":4},"Build perspective plots for with and without the interaction."]
[null,null,"EVInd",-0.0988,{"$nulls":4},"If the perspective plots look significantly different then the interaction is likely material and should be included."]
[null,null,"ln(DMD/20)",0.4083]
[null,null,"EVInd: Yes, ln(DMD/20)",-0.0378,{"$nulls":3},"d.)","1. GLMs assume the data is fully credible."]
[{"$nulls":8},"2. GLMs assume the randomness of outcomes is uncorrelated."]
["(a)","Calculate the ratio of the estimated claim severity for an Electric Vehicle to that of a non-Electric "]
[{"$f":"INDEX('Point Grid'!$C$8:$I$27,MATCH($A$1,'Point Grid'!$A$8:$A$27,0),MATCH(A24,'Point Grid'!$C$7:$I$7,0))","v":0.75},"Vehicle for the following characteristics:",{"$nulls":5},"e.)","1. Renewals are present in the dataset because policies are effective for 6-months and we have two years of data."]
[0.75,{"$nulls":7},"An insured likely has similar (correlated) outcomes across policy periods. I.e. a bad driver is usually a persistently bad driver."]
[null,null,"Daily Miles Driven",30,{"$nulls":4},"2. Electric vehicles are relatively new in 2017 – 2019; there may be insufficient data to be considered fully credible."]
[null,null,"Vehicle Type","Truck"]
[]
["(b)","Suppose the median Daily Miles Driven for your book of business is estimated as 20 miles per day."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$27,MATCH($A$1,'Point Grid'!$A$8:$A$27,0),MATCH(A30,'Point Grid'!$C$7:$I$7,0))","v":1},"Calculate the intercept term for a \"typical\" policyholder and briefly explain its meaning."]
[1]
[]
["(c)","The actuary is considering adding Years of Driving Experience (YDE) to the model. "]
[{"$f":"INDEX('Point Grid'!$C$8:$I$27,MATCH($A$1,'Point Grid'!$A$8:$A$27,0),MATCH(A34,'Point Grid'!$C$7:$I$7,0))","v":1},"Describe how they can determine whether the interaction between ln(YDE) and ln(DMD/20)"]
[1,"is significant."]
[]
["(d)","Identify two limitations of Generalized Linear Models."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$27,MATCH($A$1,'Point Grid'!$A$8:$A$27,0),MATCH(A38,'Point Grid'!$C$7:$I$7,0))","v":0.5}]
[0.5]
[]
["(e)","Briefly explain why each of the limitations in part b.) above may be present in the actuary's work."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$27,MATCH($A$1,'Point Grid'!$A$8:$A$27,0),MATCH(A42,'Point Grid'!$C$7:$I$7,0))","v":1}]
[1]

{"sheet":"6","index":8,"n_rows":80,"n_cols":21,"rows_kept":80,"merged":[[0,11,0,12]]}
[{"$f":"VALUE(MID(CELL(\"filename\",A1),FIND(\"]\",CELL(\"filename\",A1))+1,LEN(CELL(\"filename\",A1))-FIND(\"]\",CELL(\"filename\",A1))+1))","v":6},"Finished",{"$nulls":9},"Navigator",null,null,"Solution"]
[{"$nulls":13},"a.)","First convert the given table into ratios of claims to TT claims."]
["Points","An actuary has collected the following claim count data for Workers' Compensation claims.",{"$nulls":13},"Year 1",null,"Year 2",null,"Total"]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('6'!A1,'Point Grid'!A:A,0))","v":4.75},"The only injury types are Fatal, Major, and Temporary Total (TT).",{"$nulls":12},"Class","Fatal","Major","Fatal","Major","Fatal","Major"]
[null,"The classes below are the only classes the company writes in Hazard Group F.",{"$nulls":12},1,{"$f":"C9/$E9","v":{"v":2.4096385542168677E-3,"d":"0.0024"}},{"v":0.03132530120481928,"d":"0.0313"},{"$f":"F9/$H9","v":{"v":0.047619047619047616,"d":"0.0476"}},{"v":0.16071428571428573,"d":"0.1607"},{"$f":"I9/$K9","v":{"v":0.015437392795883362,"d":"0.0154"}},{"v":0.0686106346483705,"d":"0.0686"}]
[{"$nulls":14},2,{"v":0.024489795918367346,"d":"0.0245"},{"v":0.07346938775510205,"d":"0.0735"},{"v":0.04390243902439024,"d":"0.0439"},{"v":0.07317073170731707,"d":"0.0732"},{"v":0.03333333333333333,"d":"0.0333"},{"v":0.07333333333333333,"d":"0.0733"}]
[null,null,"Year 1",null,null,"Year 2",null,null,"Total",{"$nulls":5},3,{"v":0.012024048096192385,"d":"0.0120"},{"v":0.050100200400801605,"d":"0.0501"},{"v":2.2988505747126436E-3,"d":"0.0023"},{"v":0.06206896551724138,"d":"0.0621"},{"v":0.007494646680942184,"d":"0.0075"},{"v":0.055674518201284794,"d":"0.0557"}]
[null,"Class","Fatal","Major","TT","Fatal","Major","TT","Fatal","Major","TT",{"$nulls":3},"Hazard Group F",{"v":0.011216566005176877,"d":"0.0112"},{"v":0.04831751509922347,"d":"0.0483"},{"v":0.022277227722772276,"d":"0.0223"},{"v":0.0853960396039604,"d":"0.0854"},{"v":0.0157600406710727,"d":"0.0158"},{"v":0.06354855109303507,"d":"0.0635"}]
[null,1,1,13,415,8,27,168,{"$f":"C9+F9","v":9},40,583]
[null,2,6,18,245,9,15,205,15,33,450,{"$nulls":3},"Apply the following formula to estimate the expected process variance (EPV)."]
[null,3,6,25,499,1,27,435,7,52,934,{"$nulls":7},"R is the number of classes in the hazard group"]
[null,"Hazard Group F",{"$f":"SUM(C9:C11)","v":13},{"$f":"SUM(D9:D11)","v":56},{"v":1159,"d":"1,159"},18,69,808,31,125,{"v":1967,"d":"1,967"},{"$nulls":7},"N is the number of years of data available. We need at least two years to be able "]
[{"$nulls":18},"to estimate variances."]
[null,"Let V denote the ratio of Fatal claims to TT claims.",{"$nulls":16},"mit is the number of TT claims for class i in year t."]
[null,"Let X denote the ratio of Major claims to TT claims.",{"$nulls":12},"R =",3]
[null,"Answer the following using Couret & Venter's multi-dimensional credibility technique.",{"$nulls":12},"N =",2]
[{"$nulls":14},"Calculating (Xit - Xi)2",null,"Year"]
["(a)","Calculate the estimated Expected Process Variance for X for the hazard group.",{"$nulls":13},"Class",1,2]
[{"$f":"INDEX('Point Grid'!$C$8:$I$27,MATCH($A$1,'Point Grid'!$A$8:$A$27,0),MATCH(A18,'Point Grid'!$C$7:$I$7,0))","v":0.75},{"$nulls":14},1,{"$f":"(Q5-U5)^2","v":{"v":0.001390196089996799,"d":"0.001390"}},{"$f":"(S5-U5)^2","v":{"v":0.008483082539671867,"d":"0.008483"}}]
[0.75,{"$nulls":14},2,{"$f":"(Q6-U6)^2","v":{"v":1.8510805682819047E-8,"d":"0.000000"}},{"$f":"(S6-U6)^2","v":{"v":2.6439288783133286E-8,"d":"0.000000"}}]
[{"$nulls":15},3,{"$f":"(Q7-U7)^2","v":{"v":3.1073018940783734E-5,"d":"0.000031"}},{"$f":"(S7-U7)^2","v":{"v":4.0888956476544369E-5,"d":"0.000041"}}]
["(b)","Calculate the estimated Variance of Hypothetical Means for X for the hazard group."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$27,MATCH($A$1,'Point Grid'!$A$8:$A$27,0),MATCH(A22,'Point Grid'!$C$7:$I$7,0))","v":1},{"$nulls":13},"EPV X = ",{"$f":"(SUMPRODUCT(E9:E11,Q19:Q21)+SUMPRODUCT(H9:H11,R19:R21))/(P15*(P16-1))","v":{"v":0.6784637772446286,"d":"0.678464"}}]
[1]
[{"$nulls":13},"b.)","Apply the following formula to estimate the variance of hypothetical means (VHM). "]
["(c)","Calculate the estimated covariance between V and X for the hazard group.",{"$nulls":12},"Notice this depends on your answer to part (a) but you'd get credit for using part (a) even if your answer was wrong."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$27,MATCH($A$1,'Point Grid'!$A$8:$A$27,0),MATCH(A26,'Point Grid'!$C$7:$I$7,0))","v":0.5}]
[0.5]
[]
[null,"Given the following:"]
[null,"EPV V",{"v":0.10282227448621183,"d":"0.1028"},{"$nulls":11},"Class","(Xi - Xh)2",null,"VHM X =",{"$f":"(SUMPRODUCT(K9:K11,P32:P34)-(P15-1)*P23)/(K12-1/K12*SUMSQ(K9:K11))","v":-9.945791404327854E-4},"VHM is negative, per Couret & Venter this means we should set it equal to 0."]
[null,"VHM V",{"v":0,"d":"0.0000"},{"$nulls":11},1,{"$f":"(U5-$U$8)^2","v":{"v":2.5624689921197342E-5,"d":"0.000026"}},null,"VHM X =",{"$f":"MAX(0,S31)","v":{"v":0,"d":"0.000000"}}]
[{"$nulls":14},2,{"$f":"(U6-$U$8)^2","v":{"v":9.5741963490056215E-5,"d":"0.000096"}}]
["(d)","Calculate the Variance of V and the Variance of X for Class 3.",{"$nulls":12},3,{"$f":"(U7-$U$8)^2","v":{"v":6.2000393980365289E-5,"d":"0.000062"}}]
[{"$f":"INDEX('Point Grid'!$C$8:$I$27,MATCH($A$1,'Point Grid'!$A$8:$A$27,0),MATCH(A34,'Point Grid'!$C$7:$I$7,0))","v":0.5}]
[0.5,{"$nulls":12},"c.)","Apply the following formula to estimate the covariance between V and X."]
[]
["(e)","Calculate the credibilities used by Class 3 to estimate X for its population."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$27,MATCH($A$1,'Point Grid'!$A$8:$A$27,0),MATCH(A38,'Point Grid'!$C$7:$I$7,0))","v":1.25}]
[1.25,{"$nulls":13},"Class","(Vi - Vh)","(Xi - Xh)"]
[{"$nulls":14},1,{"$f":"T5-T$8","v":{"v":-3.2264787518933823E-4,"d":"-0.0003"}},{"v":0.005062083555335425,"d":"0.0051"}]
["(f)","Calculate the expected number of Major claims for Class 3.",{"$nulls":12},2,{"v":0.017573292662260633,"d":"0.0176"},{"v":0.00978478224029826,"d":"0.0098"}]
[{"$f":"INDEX('Point Grid'!$C$8:$I$27,MATCH($A$1,'Point Grid'!$A$8:$A$27,0),MATCH(A42,'Point Grid'!$C$7:$I$7,0))","v":0.75},{"$nulls":13},3,{"v":-0.008265393990130515,"d":"-0.0083"},{"v":-0.00787403289175028,"d":"-0.0079"}]
[0.75]
[{"$nulls":14},"Cov(Vi, Xi) =",{"$f":"SUMPRODUCT(P41:P43,Q41:Q43,K9:K11)/K12","v":{"v":6.9757120162806615E-5,"d":"0.000070"}}]
[]
[{"$nulls":13},"d.)","Apply the following formula to estimate the variance for Class i."]
[]
[]
[]
[{"$nulls":14},"Variance of X for Class 3 =",null,{"$f":"P23/K11+S32","v":{"v":7.26406613752279E-4,"d":"0.000726"}}]
[{"$nulls":14},"Variance of V for Class 3 =",null,{"$f":"C31/K11+C32","v":{"v":1.1008808831500195E-4,"d":"0.000110"}}]
[]
[{"$nulls":13},"e.)","Use the following credibility equation"]
[]
[]
[]
[{"$nulls":14},"LHS",null,"RHS 2 by 2 matrix"]
[{"$nulls":14},{"$f":"P45","v":{"v":6.9757120162806615E-5,"d":"0.000070"}},null,{"$f":"Q52","v":{"v":1.1008808831500195E-4,"d":"0.000110"}},{"$f":"P45","v":{"v":6.9757120162806615E-5,"d":"0.000070"}}]
[{"$nulls":14},{"$f":"S32","v":{"v":0,"d":"0.000000"}},null,{"$f":"P45","v":{"v":6.9757120162806615E-5,"d":"0.000070"}},{"$f":"Q51","v":{"v":7.26406613752279E-4,"d":"0.000726"}}]
[]
[{"$nulls":14},"Alice: \"Note Cov(X3, x3) = VHMX because the covariance of an observed variable, x3, with its unobserved mean, X3, is the variance of its hypothetical means.\""]
[]
[{"$nulls":14},"Now apply the MINVERSE function to invert the matrix. You'll need to use an array formula to do this."]
[{"$nulls":14}," (Both the MINVERSE function and array formulas are currently available in the Pearson environment)"]
[]
[{"$nulls":14},"2 by 2 matrix inverse"]
[{"$nulls":14},{"$f":"MINVERSE(Q59:R60)","v":9672.182280797258},-928.8235663397091]
[{"$nulls":14},-928.823566339709,1465.8347500816171]
[]
[{"$nulls":14},"Solve the matrix equation to calculate the credibilities used by Class 3."]
[{"$nulls":15},{"$f":"SUMPRODUCT(O68:P68,TRANSPOSE(O59:O60))","v":{"v":0.6747035815981434,"d":"0.674704"}}]
[{"$nulls":15},{"$f":"SUMPRODUCT(O69:P69,TRANSPOSE(O59:O60))","v":{"v":-0.06479205712720566,"d":"-0.064792"}}]
[]
[{"$nulls":13},"f.)","Apply the following multidimensional credibility formula"]
[]
[]
[{"$nulls":14},"E[X3] =",{"$f":"U8+P72*(T7-T8)+P73*(U7-U8)","v":0.058482034953518024},"This is the ratio of Major claims to TT claims. "]
[]
[{"$nulls":14},{"$f":"P78*K11","v":{"v":54.62222064658583,"d":"54.6"}},"Major claims expected in Class 3"]

{"sheet":"7","index":9,"n_rows":48,"n_cols":25,"rows_kept":48,"merged":[[0,10,0,11],[42,2,43,2],[44,2,45,2],[46,2,47,2]]}
[{"$f":"VALUE(MID(CELL(\"filename\",A1),FIND(\"]\",CELL(\"filename\",A1))+1,LEN(CELL(\"filename\",A1))-FIND(\"]\",CELL(\"filename\",A1))+1))","v":7},"Finished",{"$nulls":8},"Navigator",null,null,"Solution"]
[{"$nulls":12},"a.)","The company has experienced rapid growth in gross annual sales. Therefore, there has been a dramatic shift"]
["Points","The following policy is being rated using the ISO CGL rating plan.",{"$nulls":11},"in exposures so it is not appropriate to use the standard approach."]
[{"$f":"INDEX('Point Grid'!B:B,MATCH(A1,'Point Grid'!A:A,0))","v":2.75},{"$nulls":5},"Current Company Rates per Exposure",{"$nulls":6},"We cannot use the Present Average Company Rates approach because we lack information about the"]
[null,{"$f":"DATE(YEAR(TODAY()),12,1)","v":"2025-12-01"},"Effective Date",{"$nulls":3},"Policy Type","Sub-line","Rate",{"$nulls":4},"company's annual basic limits premium."]
[null,"Claims-Made (CM)","Policy Type",{"$nulls":3},"3rd-yr CM","Prem/Ops",7.7]
[null,{"v":0.72,"d":"72%"},"Expected Loss Ratio (ELR)",{"$nulls":3},"3rd-yr CM","Products",8.9]
[{"$nulls":6},"2nd-yr CM","Prem/Ops",3.3,{"$nulls":3},"b.)","The experience period is the latest three full years ending at least six months prior to the policy effective date."]
[{"$nulls":6},"2nd-yr CM","Products",4.2,{"$nulls":4},{"$f":"\"Thus, we are looking at years \"&N20&\" – \"&N15&\".\"","v":"Thus, we are looking at years 2021 – 2023."}]
[{"$nulls":6},"1st-yr CM","Prem/Ops",1.8,{"$nulls":4},{"$f":"\"Since \"&YEAR(B19)&\" is a 2nd-year claims made policy, we are pricing a 3rd-year claims made policy.\"","v":"Since 2024 is a 2nd-year claims made policy, we are pricing a 3rd-year claims made policy."}]
[{"$nulls":6},"1st-yr CM","Products",4.1]
[{"$nulls":6},"Ocurrence","Prem/Ops",7.9,{"$nulls":15},"Annual Basic"]
[{"$nulls":6},"Ocurrence","Products",5.4,{"$nulls":4},"Policy",null,"Policy","Historical","Basic",{"$nulls":6},"Limits Company"]
[{"$nulls":13},"Year","Sub-line","Type","Exposures","Rate","ILF","ELR","BLEL","PAF 13B","PAF 13C","Detrend","Loss Cost"]
[null,"Information about previous policy years",{"$nulls":11},{"$f":"YEAR(B20)","v":2023},"Prem/Ops","1st yr CM",{"$f":"F20","v":{"v":72000,"d":"$72,000"}},1.8,{"$f":"J19","v":{"v":1.63,"d":"1.630"}},{"$f":"$B$7","v":{"v":0.72,"d":"72%"}},{"$f":"PRODUCT(Q15:T15)","v":{"v":152098.56,"d":"152,099"}},{"v":1,"d":"1.000"},{"v":1,"d":"1.000"},0.926,{"$f":"PRODUCT(U15:X15)","v":{"v":140843.26656000002,"d":"140,843"}}]
[{"$nulls":4},"Policy","Gross",null,"Current Increased Limits Factors",{"$nulls":5},{"$f":"YEAR(B20)","v":2023},"Products","1st yr CM",{"$f":"F20","v":{"v":72000,"d":"$72,000"}},4.1,{"$f":"J19","v":{"v":1.63,"d":"1.630"}},{"v":0.72,"d":"72%"},{"v":346446.72,"d":"346,447"},{"v":1,"d":"1.000"},{"v":1,"d":"1.000"},0.901,{"v":312148.49471999996,"d":"312,148"}]
[{"$nulls":4},"Limits","Annual",null,"Aggregate Limit",{"$nulls":5},{"$f":"YEAR(B21)","v":2022},"Prem/Ops","Occurrence",{"$f":"F21","v":{"v":33000,"d":"$33,000"}},7.9,{"$f":"I19","v":{"v":1.35,"d":"1.350"}},{"v":0.72,"d":"72%"},{"v":253400.4,"d":"253,400"},{"v":1,"d":"1.000"},{"v":1,"d":"1.000"},0.892,{"v":226033.1568,"d":"226,033"}]
[null,"Effective Date","Policy Type",null,"(occ/agg)","Sales",null,"200k","250k","500k",{"$nulls":3},{"$f":"YEAR(B21)","v":2022},"Products","Occurrence",{"$f":"F21","v":{"v":33000,"d":"$33,000"}},5.4,{"$f":"I19","v":{"v":1.35,"d":"1.350"}},{"v":0.72,"d":"72%"},{"v":173210.40000000002,"d":"173,210"},{"v":1,"d":"1.000"},{"v":1,"d":"1.000"},0.854,{"v":147921.6816,"d":"147,922"}]
[null,{"$f":"DATE(YEAR(TODAY())-1,12,1)","v":"2024-12-01"},"2nd-year Claims-Made",null,"250k/500k",{"v":108000,"d":"$108,000"},"100k",{"v":1,"d":"1.000"},{"v":1.35,"d":"1.350"},{"v":1.63,"d":"1.630"},{"$nulls":3},{"$f":"YEAR(B22)","v":2021},"Prem/Ops","Occurrence",{"$f":"F22","v":{"v":16000,"d":"$16,000"}},7.9,{"$f":"H19","v":{"v":1,"d":"1.000"}},{"v":0.72,"d":"72%"},{"v":91008,"d":"91,008"},{"v":1,"d":"1.000"},{"v":1,"d":"1.000"},0.858,{"v":78084.864,"d":"78,085"}]
[null,{"$f":"DATE(YEAR(TODAY())-2,12,1)","v":"2023-12-01"},"1st-year Claims-Made",null,"150k/500k",{"v":72000,"d":"$72,000"},"150k",{"v":1.15,"d":"1.150"},{"v":1.29,"d":"1.290"},{"v":1.81,"d":"1.810"},{"$nulls":3},{"$f":"YEAR(B22)","v":2021},"Products","Occurrence",{"$f":"F22","v":{"v":16000,"d":"$16,000"}},5.4,{"$f":"H19","v":{"v":1,"d":"1.000"}},{"v":0.72,"d":"72%"},{"v":62208,"d":"62,208"},{"v":1,"d":"1.000"},{"v":1,"d":"1.000"},{"v":0.81,"d":"0.810"},{"v":50388.48,"d":"50,388"}]
[null,{"$f":"DATE(YEAR(TODAY())-3,12,1)","v":"2022-12-01"},"Occurrence",null,"100k/250k",{"v":33000,"d":"$33,000"},"250k",{"v":1.33,"d":"1.330"},{"v":1.46,"d":"1.460"},{"v":1.95,"d":"1.950"},{"$nulls":13},"CSLC =",{"$f":"SUM(Y15:Y20)","v":{"v":955419.94368,"d":"955,420"}}]
[null,{"$f":"DATE(YEAR(TODAY())-4,12,1)","v":"2021-12-01"},"Occurrence",null,"100k/200k",{"v":16000,"d":"$16,000"}]
[]
[]
["(a)","Briefly explain why the Company Subject Loss Cost should be calculated using the Historical Exposures at Present Company Rates "]
[{"$f":"INDEX('Point Grid'!$C$8:$I$27,MATCH($A$1,'Point Grid'!$A$8:$A$27,0),MATCH(A25,'Point Grid'!$C$7:$I$7,0))","v":0.75},"approach instead of the standard approach or the Present Average Company Rates approach."]
[0.75]
[]
["(b)","Calculate the Company Subject Loss Cost using the Historical Exposures at Present Company Rates approach using the information"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$27,MATCH($A$1,'Point Grid'!$A$8:$A$27,0),MATCH(A29,'Point Grid'!$C$7:$I$7,0))","v":2}," provided below."]
[2,null,"Table 13B"]
[null,null,"Sub-line","Occurrence","4th-yr CM","3rd-yr CM","2nd-yr CM","1st-yr CM"]
[null,null,"Prem/Ops",{"v":1,"d":"1.00"},1.14,{"v":1.2,"d":"1.20"},1.32,1.62]
[null,null,"Products",{"v":1,"d":"1.00"},1.51,1.59,2.03,2.39]
[]
[null,null,"Table 13C"]
[null,null,"Sub-line","Occurrence","4th-yr CM","3rd-yr CM","2nd-yr CM","1st-yr CM"]
[null,null,"Prem/Ops",{"v":1,"d":"1.00"},0.84,0.78,0.67,0.47]
[null,null,"Products",{"v":1,"d":"1.00"},0.49,0.43,0.35,0.22]
[]
[null,null,"Table 14"]
[null,null,"Year of Experience Period","Sub-line","Rule 5B","Rule 5C"]
[null,null,"Latest Year","Prem/Ops",0.907,0.926]
[{"$nulls":3},"Products",0.882,0.901]
[null,null,"2nd Latest Year","Prem/Ops",0.864,0.892]
[{"$nulls":3},"Products",0.828,0.854]
[null,null,"3rd Latest Year","Prem/Ops",0.823,0.858]
[{"$nulls":3},"Products",0.777,{"v":0.81,"d":"0.810"}]

{"sheet":"8","index":10,"n_rows":35,"n_cols":18,"rows_kept":35,"merged":[[0,5,0,6]]}
[{"$f":"VALUE(MID(CELL(\"filename\",A1),FIND(\"]\",CELL(\"filename\",A1))+1,LEN(CELL(\"filename\",A1))-FIND(\"]\",CELL(\"filename\",A1))+1))","v":8},"Finished",{"$nulls":3},"Navigator",null,null,"Solution"]
[{"$nulls":7},"a.)","Apply the quintiles test. Group the insureds into five groups, ordered by increasing experience mod."]
["Points","The following information was taken from two different experience rating plans:",{"$nulls":6},"Use weighted manual and standard loss ratios."]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('8'!A1,'Point Grid'!A:A,0))","v":3.5},null,"Experience Mod",null,"Experience Data",{"$nulls":8},"Weighted Average Mods",null,null,"Standard Loss Ratios"]
[null,"Insured","Plan A","Plan B","Manual Premium ($)","Losses ($)",null,null,"Quintile","Contains",null,"Manual Premium","Losses","Plan A","Plan B","Manual LR","Plan A","Plan B"]
[null,1,0.62,0.66,{"v":100000,"d":"100,000"},{"v":65000,"d":"65,000"},null,null,"A",1,2,{"$f":"SUMIFS($E$6:$E$15,$B$6:$B$15,J6)+SUMIFS($E$6:$E$15,$B$6:$B$15,K6)","v":{"v":180000,"d":"180,000"}},{"$f":"SUMIFS($F$6:$F$15,$B$6:$B$15,J6)+SUMIFS($F$6:$F$15,$B$6:$B$15,K6)","v":{"v":145000,"d":"145,000"}},{"$f":"(SUMIFS($E$6:$E$15,$B$6:$B$15,J6)*SUMIFS($C$6:$C$15,$B$6:$B$15,J6)+SUMIFS($E$6:$E$15,$B$6:$B$15,K6)*SUMIFS($C$6:$C$15,$B$6:$B$15,K6))/(SUMIFS($E$6:$E$15,$B$6:$B$15,J6)+SUMIFS($E$6:$E$15,$B$6:$B$15,K6))","v":{"v":0.6866666666666666,"d":"0.687"}},{"$f":"(SUMIFS($E$6:$E$15,$B$6:$B$15,J6)*SUMIFS($D$6:$D$15,$B$6:$B$15,J6)+SUMIFS($E$6:$E$15,$B$6:$B$15,K6)*SUMIFS($D$6:$D$15,$B$6:$B$15,K6))/(SUMIFS($E$6:$E$15,$B$6:$B$15,J6)+SUMIFS($E$6:$E$15,$B$6:$B$15,K6))","v":{"v":0.7266666666666667,"d":"0.727"}},{"$f":"M6/L6","v":{"v":0.8055555555555556,"d":"0.806"}},{"$f":"M6/(N6*L6)","v":{"v":1.1731391585760518,"d":"1.173"}},{"$f":"M6/(O6*L6)","v":{"v":1.1085626911314985,"d":"1.109"}}]
[null,2,0.77,0.81,{"v":80000,"d":"80,000"},{"v":80000,"d":"80,000"},null,null,"B",3,4,{"$f":"SUMIFS($E$6:$E$15,$B$6:$B$15,J7)+SUMIFS($E$6:$E$15,$B$6:$B$15,K7)","v":{"v":180000,"d":"180,000"}},{"$f":"SUMIFS($F$6:$F$15,$B$6:$B$15,J7)+SUMIFS($F$6:$F$15,$B$6:$B$15,K7)","v":{"v":191000,"d":"191,000"}},{"$f":"(SUMIFS($E$6:$E$15,$B$6:$B$15,J7)*SUMIFS($C$6:$C$15,$B$6:$B$15,J7)+SUMIFS($E$6:$E$15,$B$6:$B$15,K7)*SUMIFS($C$6:$C$15,$B$6:$B$15,K7))/(SUMIFS($E$6:$E$15,$B$6:$B$15,J7)+SUMIFS($E$6:$E$15,$B$6:$B$15,K7))","v":{"v":0.9377777777777778,"d":"0.938"}},{"$f":"(SUMIFS($E$6:$E$15,$B$6:$B$15,J7)*SUMIFS($D$6:$D$15,$B$6:$B$15,J7)+SUMIFS($E$6:$E$15,$B$6:$B$15,K7)*SUMIFS($D$6:$D$15,$B$6:$B$15,K7))/(SUMIFS($E$6:$E$15,$B$6:$B$15,J7)+SUMIFS($E$6:$E$15,$B$6:$B$15,K7))","v":{"v":0.96,"d":"0.960"}},{"$f":"M7/L7","v":{"v":1.0611111111111111,"d":"1.061"}},{"$f":"M7/(N7*L7)","v":{"v":1.1315165876777251,"d":"1.132"}},{"$f":"M7/(O7*L7)","v":{"v":1.1053240740740742,"d":"1.105"}}]
[null,3,0.92,0.96,{"v":100000,"d":"100,000"},{"v":95000,"d":"95,000"},null,null,"C",5,6,{"$f":"SUMIFS($E$6:$E$15,$B$6:$B$15,J8)+SUMIFS($E$6:$E$15,$B$6:$B$15,K8)","v":{"v":180000,"d":"180,000"}},{"$f":"SUMIFS($F$6:$F$15,$B$6:$B$15,J8)+SUMIFS($F$6:$F$15,$B$6:$B$15,K8)","v":{"v":199000,"d":"199,000"}},{"$f":"(SUMIFS($E$6:$E$15,$B$6:$B$15,J8)*SUMIFS($C$6:$C$15,$B$6:$B$15,J8)+SUMIFS($E$6:$E$15,$B$6:$B$15,K8)*SUMIFS($C$6:$C$15,$B$6:$B$15,K8))/(SUMIFS($E$6:$E$15,$B$6:$B$15,J8)+SUMIFS($E$6:$E$15,$B$6:$B$15,K8))","v":{"v":1.0133333333333334,"d":"1.013"}},{"$f":"(SUMIFS($E$6:$E$15,$B$6:$B$15,J8)*SUMIFS($D$6:$D$15,$B$6:$B$15,J8)+SUMIFS($E$6:$E$15,$B$6:$B$15,K8)*SUMIFS($D$6:$D$15,$B$6:$B$15,K8))/(SUMIFS($E$6:$E$15,$B$6:$B$15,J8)+SUMIFS($E$6:$E$15,$B$6:$B$15,K8))","v":{"v":0.9922222222222222,"d":"0.992"}},{"$f":"M8/L8","v":{"v":1.1055555555555556,"d":"1.106"}},{"$f":"M8/(N8*L8)","v":{"v":1.0910087719298245,"d":"1.091"}},{"$f":"M8/(O8*L8)","v":{"v":1.1142217245240762,"d":"1.114"}}]
[null,4,0.96,0.96,{"v":80000,"d":"80,000"},{"v":96000,"d":"96,000"},null,null,"D",7,8,{"$f":"SUMIFS($E$6:$E$15,$B$6:$B$15,J9)+SUMIFS($E$6:$E$15,$B$6:$B$15,K9)","v":{"v":180000,"d":"180,000"}},{"$f":"SUMIFS($F$6:$F$15,$B$6:$B$15,J9)+SUMIFS($F$6:$F$15,$B$6:$B$15,K9)","v":{"v":215500,"d":"215,500"}},{"$f":"(SUMIFS($E$6:$E$15,$B$6:$B$15,J9)*SUMIFS($C$6:$C$15,$B$6:$B$15,J9)+SUMIFS($E$6:$E$15,$B$6:$B$15,K9)*SUMIFS($C$6:$C$15,$B$6:$B$15,K9))/(SUMIFS($E$6:$E$15,$B$6:$B$15,J9)+SUMIFS($E$6:$E$15,$B$6:$B$15,K9))","v":{"v":1.0777777777777777,"d":"1.078"}},{"$f":"(SUMIFS($E$6:$E$15,$B$6:$B$15,J9)*SUMIFS($D$6:$D$15,$B$6:$B$15,J9)+SUMIFS($E$6:$E$15,$B$6:$B$15,K9)*SUMIFS($D$6:$D$15,$B$6:$B$15,K9))/(SUMIFS($E$6:$E$15,$B$6:$B$15,J9)+SUMIFS($E$6:$E$15,$B$6:$B$15,K9))","v":{"v":1.0644444444444445,"d":"1.064"}},{"$f":"M9/L9","v":{"v":1.1972222222222222,"d":"1.197"}},{"$f":"M9/(N9*L9)","v":{"v":1.1108247422680413,"d":"1.111"}},{"$f":"M9/(O9*L9)","v":{"v":1.1247390396659707,"d":"1.125"}}]
[null,5,{"v":1,"d":"1.00"},0.97,{"v":100000,"d":"100,000"},{"v":97000,"d":"97,000"},null,null,"E",9,10,{"$f":"SUMIFS($E$6:$E$15,$B$6:$B$15,J10)+SUMIFS($E$6:$E$15,$B$6:$B$15,K10)","v":{"v":180000,"d":"180,000"}},{"$f":"SUMIFS($F$6:$F$15,$B$6:$B$15,J10)+SUMIFS($F$6:$F$15,$B$6:$B$15,K10)","v":{"v":225000,"d":"225,000"}},{"$f":"(SUMIFS($E$6:$E$15,$B$6:$B$15,J10)*SUMIFS($C$6:$C$15,$B$6:$B$15,J10)+SUMIFS($E$6:$E$15,$B$6:$B$15,K10)*SUMIFS($C$6:$C$15,$B$6:$B$15,K10))/(SUMIFS($E$6:$E$15,$B$6:$B$15,J10)+SUMIFS($E$6:$E$15,$B$6:$B$15,K10))","v":{"v":1.1622222222222223,"d":"1.162"}},{"$f":"(SUMIFS($E$6:$E$15,$B$6:$B$15,J10)*SUMIFS($D$6:$D$15,$B$6:$B$15,J10)+SUMIFS($E$6:$E$15,$B$6:$B$15,K10)*SUMIFS($D$6:$D$15,$B$6:$B$15,K10))/(SUMIFS($E$6:$E$15,$B$6:$B$15,J10)+SUMIFS($E$6:$E$15,$B$6:$B$15,K10))","v":{"v":1.0977777777777777,"d":"1.098"}},{"$f":"M10/L10","v":{"v":1.25,"d":"1.250"}},{"$f":"M10/(N10*L10)","v":{"v":1.0755258126195029,"d":"1.076"}},{"$f":"M10/(O10*L10)","v":{"v":1.138663967611336,"d":"1.139"}}]
[null,6,1.03,1.02,{"v":80000,"d":"80,000"},{"v":102000,"d":"102,000"}]
[null,7,1.06,1.06,{"v":100000,"d":"100,000"},{"v":107000,"d":"107,000"},null,null,"Manual Premium Weighted Averages",{"$nulls":6},{"$f":"SUMPRODUCT(P6:P10,L6:L10)/SUM(L6:L10)","v":{"v":1.083888888888889,"d":"1.084"}},{"$f":"SUMPRODUCT(Q6:Q10,L6:L10)/SUM(L6:L10)","v":{"v":1.116403014614229,"d":"1.116"}},{"$f":"SUMPRODUCT(R6:R10,L6:L10)/SUM(L6:L10)","v":{"v":1.1183022994013911,"d":"1.118"}}]
[null,8,{"v":1.1,"d":"1.10"},1.07,{"v":80000,"d":"80,000"},{"v":108500,"d":"108,500"}]
[null,9,1.14,1.08,{"v":100000,"d":"100,000"},{"v":110000,"d":"110,000"},null,null,"Calculating Sample Variances"]
[null,10,1.19,1.12,{"v":80000,"d":"80,000"},{"v":115000,"d":"115,000"},null,null,"Quintile","(x-xavg)2","(y - yavg)2","(z- zavg)2",null,"Alice: \"In this case you could have used Excel's VAR.S() function"]
[{"$nulls":8},"A",{"$f":"(P6-$P$12)^2","v":{"v":0.07746944444444444,"d":"0.077"}},{"$f":"(Q6-$Q$12)^2","v":{"v":0.003218990031656682,"d":"0.003"}},{"$f":"(R6-$R$12)^2","v":{"v":9.48599692509607e-05,"d":"0.000"}},null,"since all quintiles have the same manual premium volume.\""]
[null,"All insureds are in the same industry. However, some are smaller firms than others.",{"$nulls":6},"B",{"$f":"(P7-$P$12)^2","v":{"v":5.1882716049382756E-4,"d":"0.001"}},{"$f":"(Q7-$Q$12)^2","v":{"v":2.2842009074563622E-4,"d":"0.000"}},{"$f":"(R7-$R$12)^2","v":{"v":1.6843433264661145E-4,"d":"0.000"}}]
[{"$nulls":8},"C",{"$f":"(P8-$P$12)^2","v":{"v":4.6944444444444686E-4,"d":"0.000"}},{"$f":"(Q8-$Q$12)^2","v":{"v":6.448675615144309E-4,"d":"0.001"}},{"$f":"(R8-$R$12)^2","v":{"v":1.665109132937389E-5,"d":"0.000"}}]
["(a)","Demonstrate whether Plan A or Plan B appears to be more equitable according to:",{"$nulls":6},"D",{"$f":"(P9-$P$12)^2","v":{"v":0.012844444444444434,"d":"0.013"}},{"$f":"(Q9-$Q$12)^2","v":{"v":3.1117122368242255E-5,"d":"0.000"}},{"$f":"(R9-$R$12)^2","v":{"v":4.1431625233659249E-5,"d":"0.000"}}]
[{"$f":"INDEX('Point Grid'!$C$8:$I$27,MATCH($A$1,'Point Grid'!$A$8:$A$27,0),MATCH(A19,'Point Grid'!$C$7:$I$7,0))","v":3},"   (i) the quintiles test",{"$nulls":6},"E",{"$f":"(P10-$P$12)^2","v":{"v":0.027592901234567896,"d":"0.028"}},{"$f":"(Q10-$Q$12)^2","v":{"v":1.6709456429176402E-3,"d":"0.002"}},{"$f":"(R10-$R$12)^2","v":{"v":4.14597532291876E-4,"d":"0.000"}}]
[3,"   (ii) the efficiency test"]
[{"$nulls":9},"Manual LR","Std LR A","Std LR B"]
["(b)","Briefly explain whether Plan A or Plan B gives too much credit for good experience.",{"$nulls":6},"Sample Variance",{"$f":"SUM(J16:J20)/(COUNTA(I16:I20)-1)","v":{"v":0.029723765432098762,"d":"0.030"}},{"$f":"SUM(K16:K20)/(COUNTA(I16:I20)-1)","v":{"v":1.448585112300658E-3,"d":"0.001"}},{"$f":"SUM(L16:L20)/(COUNTA(I16:I20)-1)","v":{"v":1.8399363768812032E-4,"d":"0.000"}}]
[{"$f":"INDEX('Point Grid'!$C$8:$I$27,MATCH($A$1,'Point Grid'!$A$8:$A$27,0),MATCH(A23,'Point Grid'!$C$7:$I$7,0))","v":0.5}]
[0.5,{"$nulls":8},"Plan A","Plan B"]
[{"$nulls":8},"Efficiency Stat",{"$f":"K23/J23","v":{"v":0.04873491266137929,"d":"0.049"}},{"$f":"L23/J23","v":{"v":0.006190118748865686,"d":"0.006"}}]
[{"$nulls":13},"Plan A","Plan B"]
[{"$nulls":8},"Difference between largest and smallest standard loss ratios",{"$nulls":4},{"$f":"MAX(Q6:Q10)-MIN(Q6:Q10)","v":{"v":0.09761334595654891,"d":"0.098"}},{"$f":"MAX(R6:R10)-MIN(R6:R10)","v":{"v":0.033339893537261744,"d":"0.033"}}]
[]
[{"$nulls":8},"Since Plan B has the smallest difference between the largest and smallest standard loss ratios it performs better than Plan A according"]
[{"$nulls":8},"to the quintiles test."]
[]
[{"$nulls":8},"Further, Plan B has the lowest efficiency test statistic which indicates it performs better than Plan A."]
[]
[{"$nulls":7},"b.)","Plan A gives too much credit for good experience because its standard loss ratios have a downward trend."]

{"sheet":"9","index":11,"n_rows":29,"n_cols":9,"rows_kept":29,"merged":[[0,5,0,6]]}
[{"$f":"VALUE(MID(CELL(\"filename\",A1),FIND(\"]\",CELL(\"filename\",A1))+1,LEN(CELL(\"filename\",A1))-FIND(\"]\",CELL(\"filename\",A1))+1))","v":9},"Finished",{"$nulls":3},"Navigator",null,null,"Solution"]
[{"$nulls":7},"a.)","Two goals of experience rating are:"]
["Points",{"$nulls":7},"1. Provide an incentive for safety improvements."]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('9'!A1,'Point Grid'!A:A,0))","v":3.5},{"$nulls":7},"2. Increase rating equitability by charging rates which better reflect risk."]
[]
["(a)","Identify and explain two goals of experience rating.",{"$nulls":5},"b.)","The experience period is January 1, 2017 through January 1, 2019."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$27,MATCH($A$1,'Point Grid'!$A$8:$A$27,0),MATCH(A6,'Point Grid'!$C$7:$I$7,0))","v":1},{"$nulls":7},"The construction company has had increasing loss experience but their manual premiums have grown materially."]
[1,{"$nulls":7},"Losses are growing slower than manual premiums which suggests the experience rating is providing an incentive for safety improvements."]
[{"$nulls":8},"A debit mod is indicated because losses are consistently greater than manual premiums."]
["(b)","Given the following Workers' Compensation information for a Construction company:",{"$nulls":6},"The debit mod improves rating equity because it allows more (standard) premium to be collected on this risk. This reduces the"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$27,MATCH($A$1,'Point Grid'!$A$8:$A$27,0),MATCH(A10,'Point Grid'!$C$7:$I$7,0))","v":1},{"$nulls":7},"need for the insurer to subsidize it using other risks with better experience. "]
[1,null,"Policy Year","Loss Experience","Manual Premium"]
[null,null,2017,{"v":1000000,"d":"1,000,000"},{"v":500000,"d":"500,000"},null,null,"c.)","i.) Assuming the company does not already have some form of on-site medical team in the experience period (2018 – 2020)."]
[null,null,2018,{"v":1100000,"d":"1,100,000"},{"v":800000,"d":"800,000"},{"$nulls":3},"There is no impact to the experience modification for 2022 because the experience period is not affected by this change."]
[null,null,2019,{"v":1400000,"d":"1,400,000"},{"v":1200000,"d":"1,200,000"}]
[{"$nulls":8},"ii.) Since the on-site medical team is new in 2022 and should improve loss experience through reducing the severity of injuries for example,"]
[null,"Assume:",{"$nulls":6},"a schedule credit modification is suitable for 2022."]
[null,"   • The policy period being rated is January 1, 2021 to January 1, 2022."]
[{"$nulls":8},"iii.) The experience mod for 2024 considers experience within 2020 – 2022."]
[null,{"v":1100000,"d":"1,100,000"},"2021 indicated manual premium",{"$nulls":5},"Thus, one year of the experience period will reflect the safety improvements of having an on-site medical team."]
[null,1.25,"2021 experience modification factor",{"$nulls":5},"We expect the experience modification factor to reduce, all else being equal."]
[]
[null,"Discuss whether each of the two goals identified in part (a) above are fulfilled."]
[]
["(c)","The Construction company plans to add an on-site medical team in 2022. "]
[{"$f":"INDEX('Point Grid'!$C$8:$I$27,MATCH($A$1,'Point Grid'!$A$8:$A$27,0),MATCH(A25,'Point Grid'!$C$7:$I$7,0))","v":1.5},"Briefly discuss the potential impact of this to:"]
[1.5,"   (i) the experience modification for 2022"]
[null,"   (ii) the schedule modification for 2022"]
[null,"   (iii) the experience modification for 2024."]

{"sheet":"10","index":12,"n_rows":66,"n_cols":15,"rows_kept":66,"merged":[[0,6,0,7]]}
[{"$f":"VALUE(MID(CELL(\"filename\",A1),FIND(\"]\",CELL(\"filename\",A1))+1,LEN(CELL(\"filename\",A1))-FIND(\"]\",CELL(\"filename\",A1))+1))","v":10},"Finished",{"$nulls":4},"Navigator",null,null,"Solution"]
[{"$nulls":8},"a.)","The experience period is January 1, 2019 – January 1, 2021."]
["Points","Both Employer A and Employer B want to purchase Workers' Compensation insurance in Alabama.",{"$nulls":7},"We need either the sum of the latest two years in the experience period to exceed the Column A value, or"]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('10'!A1,'Point Grid'!A:A,0))","v":5.5},"The effective date is January 1, 2023 for each policy. The following information applies:",{"$nulls":7},"the average of the three years to exceed the Column B value."]
[]
[null,"Policy Effective Date","Months of Data","Subject Premium",{"$nulls":5},"Employer","Latest 2","3-yr Avg"]
[{"$nulls":3},"Employer A","Employer B",{"$nulls":4},"A",{"$f":"SUM(D9:D10)","v":{"v":11500,"d":"11,500"}},{"$f":"AVERAGE(D9:D11)","v":{"v":5333.333333333333,"d":"5,333"}}]
[null,"2022-01-01",12,{"v":4500,"d":"4,500"},{"v":3000,"d":"3,000"},{"$nulls":4},"B",{"$f":"SUM(E9:E10)","v":{"v":10000,"d":"10,000"}},{"$f":"AVERAGE(E9:E11)","v":{"v":5666.666666666667,"d":"5,667"}}]
[null,"2021-01-01",12,{"v":6000,"d":"6,000"},{"v":5000,"d":"5,000"}]
[null,"2020-01-01",12,{"v":5500,"d":"5,500"},{"v":5000,"d":"5,000"},{"$nulls":4},"Employer A qualifies for experience rating because the sum of their latest two years in the experience period is at least"]
[null,"2019-01-01",12,{"v":4500,"d":"4,500"},{"v":7000,"d":"7,000"},{"$nulls":4},"the Column A value."]
[null,"2018-01-01",12,{"v":10000,"d":"10,000"},{"v":10000,"d":"10,000"}]
[{"$nulls":9},"Employer B qualifies for experience rating because they met the second condition, the average of the three years in the "]
[null,"Expected Loss",null,{"v":19600,"d":"19,600"},{"v":20500,"d":"20,500"},{"$nulls":4},"experience period is at least the Column B value."]
[null,"Expected Excess Loss",null,{"v":16072,"d":"16,072"},{"v":15744,"d":"15,744"}]
[{"$nulls":8},"b.)","Employer A – Only the first two claims are in the experience period."]
[null,"Historical Claim Data",{"$nulls":7},"Claim Amount","Ap","Ae","Notes"]
[null,"Employer","Accident Date","Claim Amount","Claim Description",{"$nulls":4},{"$f":"D19","v":{"v":8000,"d":"8,000"}},{"$f":"30%*MIN(J18,$C$40)","v":{"v":1575,"d":"1,575"}},{"$f":"30%*J18-K18","v":825},"Med only so reduce by 70%"]
[null,"A","2020-07-01",{"v":8000,"d":"8,000"},"Med only",{"$nulls":4},{"$f":"D20","v":{"v":160000,"d":"160,000"}},{"$f":"MIN(J19,$C$40)","v":{"v":5250,"d":"5,250"}},{"$f":"J19-K19","v":{"v":154750,"d":"154,750"}},"Below state single accident threshold so no capping."]
[null,"A","2019-05-23",{"v":160000,"d":"160,000"},"Wage loss",{"$nulls":4},"Total",{"$f":"SUM(K18:K19)","v":{"v":6825,"d":"6,825"}},{"$f":"SUM(L18:L19)","v":{"v":155575,"d":"155,575"}}]
[null,"A","2018-03-28",{"v":400000,"d":"400,000"}," 3 claims from one occurrence - wage loss"]
[null,"B","2022-02-10",{"v":10000,"d":"10,000"},"Med only",{"$nulls":4},"Expected Primary Loss",null,{"$f":"D14-D15","v":{"v":3528,"d":"3,528"}}]
[null,"B","2021-03-20",{"v":175000,"d":"175,000"},"Disease loss",{"$nulls":4},"Expected Excess Loss",null,{"$f":"D15","v":{"v":16072,"d":"16,072"}}]
[null,"B","2020-09-01",{"v":428000,"d":"428,000"},"4 claims from one occurrence - wage loss"]
[null,"B","2019-06-05",{"v":170000,"d":"170,000"},"Med only",{"$nulls":4},"B =",{"$f":"D48","v":{"v":17500,"d":"17,500"}},null,"Look up based on E =",null,{"$f":"D14","v":{"v":19600,"d":"19,600"}}]
[{"$nulls":9},"W =",{"$f":"D44","v":0.08}]
[null,"Use the NCCI's Experience Rating Plan Manual for Workers' Compensation and Employers Liability Insurance"]
[null,"to answer the following questions:"]
[]
["(a)","Demonstrate whether each employer qualifies for experience rating.",{"$nulls":7},"Initial Mod =",{"$f":"ROUND((K20+(1-K26)*L23+K25+K26*L20)/(L22+(1-K26)*L23+K25+K26*L23),2)","v":{"v":1.39,"d":"1.390"}},"Make sure to round to two decimal places"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$27,MATCH($A$1,'Point Grid'!$A$8:$A$27,0),MATCH(A30,'Point Grid'!$C$7:$I$7,0))","v":2.5}]
[2.5]
[]
["(b)","For the employer(s) that qualify for experience rating, calculate the experience rating modification(s).",{"$nulls":7},"Max. Mod =",{"$f":"1.1+0.0004*(O25/E51)","v":{"v":2.22,"d":"2.220"}}]
[{"$f":"INDEX('Point Grid'!$C$8:$I$27,MATCH($A$1,'Point Grid'!$A$8:$A$27,0),MATCH(A34,'Point Grid'!$C$7:$I$7,0))","v":2.5},"You may use the following tables:",{"$nulls":7},"Final Mod =",{"$f":"MIN(K34,K30)","v":{"v":1.39,"d":"1.390"}}]
[2.5,null,"State","Column A","Column B"]
[null,null,"AL",{"v":11000,"d":"11,000"},{"v":5500,"d":"5,500"},{"$nulls":4},"Employer B – only the last three claims are in the experience period."]
[null,null,"State table of Subject Premium Eligibility Amounts",{"$nulls":6},"Expected Primary Loss",null,{"$f":"E14-E15","v":{"v":4756,"d":"4,756"}}]
[{"$nulls":9},"Expected Excess Loss",null,{"$f":"E15","v":{"v":15744,"d":"15,744"}}]
[null,null,{"v":5250,"d":"$5,250"},"AL Primary/Excess Split Point"]
[{"$nulls":9},"B =",{"$f":"D48","v":{"v":17500,"d":"17,500"}},null,"Look up based on E =",null,{"$f":"E14","v":{"v":20500,"d":"20,500"}}]
[null,null,"Expected Losses","Weighting Value",{"$nulls":5},"W =",{"$f":"D45","v":0.09}]
[null,null,"10,482 – 15,135",0.07]
[null,null,"15,136 – 19,890",0.08,{"$nulls":5},"Claim Amount","Ap","Ae","Notes"]
[null,null,"19,891 – 33,268",0.09,{"$nulls":5},{"$f":"D23","v":{"v":175000,"d":"175,000"}},{"$f":"MIN(J45,$C$40)","v":{"v":5250,"d":"5,250"}},{"$f":"J45-K45","v":{"v":169750,"d":"169,750"}},"Disease loss, no capping required (see side calculation)"]
[{"$nulls":9},{"$f":"D24","v":{"v":428000,"d":"428,000"}},{"$f":"MIN(J46,2*$C$40)","v":{"v":10500,"d":"10,500"}},{"$f":"MIN(J46,E53)-K46","v":{"v":340500,"d":"340,500"}},"Capped at state multiple claim limit, actual primary loss is doubled"]
[null,null,"Expected Losses","Ballast Value",{"$nulls":5},{"$f":"D25","v":{"v":170000,"d":"170,000"}},{"$f":"30%*MIN(J47,$C$40)","v":{"v":1575,"d":"1,575"}},{"$f":"30%*J47-K47","v":{"v":49425,"d":"49,425"}},"Med only so reduce by 70%"]
[null,null,"0 – 37,651",{"v":17500,"d":"17,500"},{"$nulls":5},"Total",{"$f":"SUM(K45:K47)","v":{"v":17325,"d":"17,325"}},{"$f":"SUM(L45:L47)","v":{"v":559675,"d":"559,675"}}]
[null,null,"37,652 – 64,802",{"v":21000,"d":"21,000"}]
[{"$nulls":9},"Disease losses"]
[null,null,"G",null,7,{"$nulls":4},"Each disease loss is capped at the applicable state per claim and multiple claim limits first."]
[null,null,"State Per Claim Accident Limit",null,{"v":175500,"d":"175,500"},{"$nulls":4},"In this case, neither cap applies."]
[null,null,"State Multiple Claim Accident Limit",null,{"v":351000,"d":"351,000"},{"$nulls":4},"Then, the sum of all capped disease losses is capped at: "]
[{"$nulls":9},"3*(State Per Claim Accident Limit) + 120% * (Expected Loss for experience period)"]
["(c)","Given the payroll information below for Employer A and your result from part b.) above, determine the",{"$nulls":7},"which in this case is:"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$27,MATCH($A$1,'Point Grid'!$A$8:$A$27,0),MATCH(A55,'Point Grid'!$C$7:$I$7,0))","v":0.5},"standard premium for Employer A's policy effective January 1, 2023.",{"$nulls":7},{"$f":"3*E52+120%*O41","v":{"v":551100,"d":"551,100"}}]
[0.5,{"$nulls":8},"So in this case, no disease loss capping is required."]
[null,{"v":7,"d":"$7.00"},"Employer A Manual Rate per $100 Payroll"]
[{"$nulls":9},"Initial Mod =",{"$f":"ROUND((K48+(1-K42)*L39+K41+K42*L48)/(L38+(1-K42)*L39+K41+K42*L39),2)","v":{"v":2.62,"d":"2.620"}},"Make sure to round to two decimal places"]
[null,"Policy Effective Date","Audited Payroll",{"$nulls":6},"Max. Mod =",{"$f":"ROUND(1.1+0.0004*(O41/E51),2)","v":{"v":2.27,"d":"2.270"}}]
[null,"2023-01-01",{"v":426000,"d":"426,000"},"(Estimated)",{"$nulls":5},"Final Mod =",{"$f":"MIN(K59,K60)","v":{"v":2.27,"d":"2.270"}}]
[null,"2022-01-01",{"v":419000,"d":"419,000"},"(Estimated)"]
[null,"2021-01-01",{"v":361000,"d":"361,000"},{"$nulls":5},"c.)","Standard Premium = Estimated Policy Period Payroll / 100 * Rate per $100 Payroll * Experience Mod"]
[null,"2020-01-01",{"v":330000,"d":"330,000"},{"$nulls":6},{"$f":"C61/100*B58*K35","v":{"v":41449.799999999996,"d":"$41,450"}}]
[null,"2019-01-01",{"v":264000,"d":"264,000"}]
[null,"2018-01-01",{"v":184000,"d":"184,000"}]

{"sheet":"11","index":13,"n_rows":31,"n_cols":13,"rows_kept":31,"merged":[[0,5,0,6]]}
[{"$f":"VALUE(MID(CELL(\"filename\",A1),FIND(\"]\",CELL(\"filename\",A1))+1,LEN(CELL(\"filename\",A1))-FIND(\"]\",CELL(\"filename\",A1))+1))","v":11},"Finished",{"$nulls":3},"Navigator",null,null,"Solution"]
[{"$nulls":7},"a.)","We'll assume Group A is really 3 or more years accident free."]
["Points","The following data were compiled from ABC Insurance Company's portfolio of",{"$nulls":6},"First calculate the claim frequency for each group using the earned premium as the exposure base as this is the only option."]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('11'!A1,'Point Grid'!A:A,0))","v":3.5},"Private Passenger Auto policies.",{"$nulls":6},"Then calculate the relative claim frequency for each group to the total claim frequency and set the experience modification "]
[{"$nulls":8},"equal to the relative claim frequency"]
[null,"Group","Number of accident ","Earned Premium at ","Number of"]
[null,null,"free years","Present Group D","Claims ",{"$nulls":3},"Years",null,"Relative"]
[{"$nulls":3},"Rates","Incurred",{"$nulls":3},"Claim Free","Frequency","Frequency","Mod","Credibility"]
[null,"A",3,{"v":100000000,"d":"100,000,000"},{"v":120000,"d":"120,000"},{"$nulls":3},"3+",{"$f":"SUM(E9:$E$9)/SUM(D9:$D$9)","v":0.0012},{"$f":"J9/$J$12","v":{"v":0.7306666666666666,"d":"0.7307"}},{"$f":"K9","v":{"v":0.7306666666666666,"d":"0.7307"}},{"$f":"1-L9","v":{"v":0.2693333333333334,"d":"0.2693"}}]
[null,"B",2,{"v":10000000,"d":"10,000,000"},{"v":25000,"d":"25,000"},{"$nulls":3},"2+",{"$f":"SUM(E$9:$E10)/SUM(D$9:$D10)","v":{"v":1.3181818181818182E-3,"d":"0.0013"}},{"$f":"J10/$J$12","v":{"v":0.8026262626262626,"d":"0.8026"}},{"$f":"K10","v":{"v":0.8026262626262626,"d":"0.8026"}},{"$f":"1-L10","v":{"v":0.19737373737373742,"d":"0.1974"}}]
[null,"C",1,{"v":17000000,"d":"17,000,000"},{"v":44000,"d":"44,000"},{"$nulls":3},"1+",{"$f":"SUM(E$9:$E11)/SUM(D$9:$D11)","v":{"v":1.4881889763779528E-3,"d":"0.0015"}},{"$f":"J11/$J$12","v":{"v":0.9061417322834645,"d":"0.9061"}},{"$f":"K11","v":{"v":0.9061417322834645,"d":"0.9061"}},{"$f":"1-L11","v":{"v":0.09385826771653549,"d":"0.0939"}}]
[null,"D",0,{"v":10000000,"d":"10,000,000"},{"v":36000,"d":"36,000"},{"$nulls":3},"Total",{"$f":"SUM(E$9:$E12)/SUM(D$9:$D12)","v":{"v":1.6423357664233577E-3,"d":"0.0016"}}]
[]
["(a)","Calculate the credibility of a single car for each of the following ranges of accident-free years:",{"$nulls":6},"Since we know the k+ group had 0 claims in the past k years for k = 1, 2, 3, the experience modification calculation,"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$27,MATCH($A$1,'Point Grid'!$A$8:$A$27,0),MATCH(A14,'Point Grid'!$C$7:$I$7,0))","v":2},"   i.) 1 or more",{"$nulls":6},"Mod = ZR + (1 - Z) reduces to Mod = 1 - Z"]
[2,"   ii.) 2 or more"]
[null,"   iii.) 3 or more",{"$nulls":5},"b.)","Relative credibility for",null,"ABC","XYZ"]
[{"$nulls":10},{"$f":"M9/$M$11","v":{"v":2.8695749440715876,"d":"2.870"}},{"$f":"D21/$D$23","v":{"v":2.3333333333333335,"d":"2.333"}}]
["(b)","The following table provides the single car credibility for the XYZ automobile insurance portfolio:",{"$nulls":8},{"$f":"M10/$M$11","v":{"v":2.1028913294013956,"d":"2.103"}},{"$f":"D22/$D$23","v":{"v":1.6666666666666667,"d":"1.667"}}]
[{"$f":"INDEX('Point Grid'!$C$8:$I$27,MATCH($A$1,'Point Grid'!$A$8:$A$27,0),MATCH(A19,'Point Grid'!$C$7:$I$7,0))","v":1},null,"Accident-Free Years","Single Car Credibility",{"$nulls":6},{"$f":"M11/$M$11","v":{"v":1,"d":"1.000"}},{"$f":"D23/$D$23","v":{"v":1,"d":"1.000"}}]
[1,null,"3 or more",{"v":0.14,"d":"0.140"}]
[null,null,"2 or more",{"v":0.1,"d":"0.100"},{"$nulls":4},"ABC's relative credibilities are closer to 1, 2, 3 than XYZ's. We expect them to be close to 1, 2, 3 if the credibility"]
[null,null,"1 or more",{"v":0.06,"d":"0.060"},{"$nulls":4},"is proportional to the number of years accident free. "]
[{"$nulls":8},"We can conclude either:"]
[null,"Discuss two conclusions which can be drawn from the different credibility results of the ABC",{"$nulls":6},"1. XYZ has risks entering and leaving its classes whereas ABC's classes are relatively stable over time, or"]
[null,"and XYZ portfolios.",{"$nulls":6},"2. XYZ has changing risk characteristics over time within its classes and ABC does not."]
[]
["(c)","Explain why analysis of the two portfolios with different classification plans could assign ",{"$nulls":5},"c.)","If a classification plan is highly refined then there is less unexplained variation between risks within the same class."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$27,MATCH($A$1,'Point Grid'!$A$8:$A$27,0),MATCH(A28,'Point Grid'!$C$7:$I$7,0))","v":0.5},"different values to the credibility of the experience of a single car.",{"$nulls":6},"This means experience rating is less useful and consequently we get lower credibilities."]
[0.5,{"$nulls":7},"Whereas a less refined rating plan has a lot of unexplained variation between risks within the same class, so"]
[{"$nulls":8},"experience rating is more useful and we get higher credibilities."]

{"sheet":"12","index":14,"n_rows":33,"n_cols":13,"rows_kept":33,"merged":[[0,4,0,5]]}
[{"$f":"VALUE(MID(CELL(\"filename\",A1),FIND(\"]\",CELL(\"filename\",A1))+1,LEN(CELL(\"filename\",A1))-FIND(\"]\",CELL(\"filename\",A1))+1))","v":12},"Finished",null,null,"Navigator",null,null,"Solution"]
[{"$nulls":6},"a.)","Balance Equations"]
["Points","Consider two policies:",{"$nulls":8},"and "]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('12'!A1,'Point Grid'!A:A,0))","v":2},"   • A retrospectively rated policy with no loss limit,"]
[null,"   • A retrospectively rated policy with a $500,000 loss limit."]
[{"$nulls":7},"When there is a loss limitation, replace the denominators with "]
[null,"Both policies are subject to the following parameters:"]
[{"$nulls":7},"Maximum Premium Factor = G / (Standard Premium) =",{"$nulls":4},{"$f":"B9","v":{"v":1,"d":"1.00"}}]
[null,{"v":1,"d":"1.00"},"Maximum Premium Factor",{"$nulls":4},"Minimum Premium Factor = H / (Standard Premium) =",{"$nulls":4},{"$f":"B10","v":{"v":0.4,"d":"0.40"}}]
[null,{"v":0.4,"d":"0.40"},"Minimum Premium Factor",{"$nulls":4},"Expected Loss & ALAE Ratio = E[A] / (Standard Premium) =",{"$nulls":4},{"$f":"B12","v":{"v":0.6,"d":"0.60"}}]
[null,{"v":1.2,"d":"1.20"},"Loss Conversion Factor",{"$nulls":4},"Expense and Profit Provision = e / (Standard Premium) =",{"$nulls":4},{"$f":"B14","v":0.35}]
[null,{"v":0.6,"d":"0.60"},"Expected Loss & ALAE Ratio"]
[null,1.05,"Tax Multiplier",{"$nulls":4},"Re-express the balance equations in terms of ratios to standard premium (SP)"]
[null,0.35,"Expense and Profit Provision"]
[null,{"v":0.1,"d":"0.10"},"Loss Elimination Ratio at $500,000",{"$nulls":7},"and"]
[]
["(a)","Calculate the difference between the balance equations for the two policies."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$27,MATCH($A$1,'Point Grid'!$A$8:$A$27,0),MATCH(A17,'Point Grid'!$C$7:$I$7,0))","v":2},{"$nulls":6},"No Loss Limit"]
[2,{"$nulls":6},"phi(r_H) - phi(r_G) =",null,{"$f":"((M11+M10)*B13-M9)/(B11*M10*B13)","v":{"v":0.7903439153439152,"d":"0.79034"}}]
[{"$nulls":7},"r_G - r_H =",null,{"$f":"(M8-M9)/(B11*M10*B13)","v":{"v":0.7936507936507936,"d":"0.79365"}}]
[]
[{"$nulls":7},"$500,000 Loss Limit"]
[{"$nulls":7},"First calculate E[A_D] / (Standard Premium) using"]
[]
[{"$nulls":7},"E[A_D] / SP =",{"$f":"(1-B15)*M10","v":0.54},"Again, we switch to working in terms of ratios to standard premium."]
[]
[{"$nulls":7},"Apply the balance equations with E[A_D] in the denominator instead."]
[]
[{"$nulls":7},"phi(r_H) - phi(r_G) =",null,{"$f":"((M11+M10)*B13-M9)/(B11*I25*B13)","v":{"v":0.8781599059376836,"d":"0.87816"}},null,"Alice: \"It's fine to type out mathematical symbols, use _ to denote"]
[{"$nulls":7},"r_G - r_H =",null,{"$f":"(M8-M9)/(B11*I25*B13)","v":{"v":0.8818342151675485,"d":"0.88183"}},null,"subscripts, etc. in the exam to save time rather than messing around"]
[{"$nulls":11},"with the symbol feature in the Pearson VUE environment.\""]
[{"$nulls":7},"1st Equation Difference =",null,{"$f":"J29-J19","v":{"v":0.08781599059376832,"d":"0.08782"}}]
[{"$nulls":7},"2nd Equation Difference =",null,{"$f":"J30-J20","v":{"v":0.08818342151675485,"d":"0.08818"}}]

{"sheet":"13","index":15,"n_rows":68,"n_cols":13,"rows_kept":68,"merged":[[0,4,0,5]]}
[{"$f":"VALUE(MID(CELL(\"filename\",A1),FIND(\"]\",CELL(\"filename\",A1))+1,LEN(CELL(\"filename\",A1))-FIND(\"]\",CELL(\"filename\",A1))+1))","v":13},"Finished",null,null,"Navigator",null,null,"Solution"]
[{"$nulls":6},"a.)","E[A] =",{"$f":"AVERAGE(C7:C21)","v":{"v":0.6093333333333334,"d":"60.9%"}},"We can use a straight average since we're told all risks are approximately equal in size."]
["Points","The unlimited and limited loss ratios for 15 risks of ",{"$nulls":5},"E[A_D] =",{"$f":"AVERAGE(D7:D21)","v":{"v":0.5186666666666667,"d":"51.9%"}}]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('13'!A1,'Point Grid'!A:A,0))","v":4},"approximately equal size are as follows:"]
[null,null,"Loss Ratio"]
[null,"Risk Number","Unlimited","Limited"]
[null,3,{"v":0.29,"d":"29%"},{"v":0.06,"d":"6%"},{"$nulls":3},"LER =",{"$f":"(I2-I3)/I2","v":{"v":0.1487964989059081,"d":"0.149"}}]
[null,4,{"v":0.35,"d":"35%"},{"v":0.06,"d":"6%"}]
[null,1,{"v":0.14,"d":"14%"},{"v":0.14,"d":"14%"},null,null,"b.)","Notice the risks are ordered by increasing unlimited loss ratio. "]
[null,2,{"v":0.25,"d":"25%"},{"v":0.25,"d":"25%"},{"$nulls":3},"To build a Table M we need an entry for each desired entry ratio plus one for each actual entry ratio"]
[null,5,{"v":0.41,"d":"41%"},{"v":0.41,"d":"41%"},{"$nulls":3},"There's no easy way to get a list of distinct entries in the Pearson VUE environment that we're aware of for now."]
[null,8,{"v":0.63,"d":"63%"},{"v":0.41,"d":"41%"}]
[null,6,{"v":0.5,"d":"50%"},{"v":0.45,"d":"45%"},{"$nulls":3},"Table M: Bold figures are the desired increments."]
[null,7,{"v":0.5,"d":"50%"},{"v":0.5,"d":"50%"},{"$nulls":3},"Loss Ratio","Entry Ratio, r","# risks","# risks above","% risks above","phi(r)"]
[null,9,{"v":0.66,"d":"66%"},{"v":0.66,"d":"66%"},{"$nulls":3},{"v":0,"d":"0%"},{"$f":"H15/$I$2","v":{"v":0,"d":"0.000"}},{"$f":"COUNTIF($C$7:$C$21,H15)","v":0},{"$f":"SUM(J16:$J$36)","v":15},{"$f":"K15/$J$38","v":{"v":1,"d":"100.0%"}},{"$f":"M16+L15*(I16-I15)","v":{"v":0.9999999999999997,"d":"1.000"}}]
[null,13,{"v":0.99,"d":"99%"},{"v":0.67,"d":"67%"},{"$nulls":3},{"v":0.1,"d":"10%"},{"$f":"H16/$I$2","v":{"v":0.16411378555798686,"d":"0.164"}},{"$f":"COUNTIF($C$7:$C$21,H16)","v":0},{"$f":"SUM(J17:$J$36)","v":15},{"$f":"K16/$J$38","v":{"v":1,"d":"100.0%"}},{"v":0.8358862144420128,"d":"0.836"}]
[null,10,{"v":0.71,"d":"71%"},{"v":0.71,"d":"71%"},{"$nulls":3},{"v":0.14,"d":"14%"},{"v":0.22975929978118162,"d":"0.230"},1,{"$f":"SUM(J18:$J$36)","v":14},{"v":0.9333333333333333,"d":"93.3%"},{"v":0.7702407002188181,"d":"0.770"}]
[null,11,{"v":0.75,"d":"75%"},{"v":0.75,"d":"75%"},{"$nulls":3},{"v":0.2,"d":"20%"},{"v":0.3282275711159737,"d":"0.328"},0,{"$f":"SUM(J19:$J$36)","v":14},{"v":0.9333333333333333,"d":"93.3%"},{"v":0.6783369803063455,"d":"0.678"}]
[null,14,{"v":1,"d":"100%"},{"v":0.75,"d":"75%"},{"$nulls":3},{"v":0.25,"d":"25%"},{"v":0.41028446389496714,"d":"0.410"},1,{"$f":"SUM(J20:$J$36)","v":13},{"v":0.8666666666666667,"d":"86.7%"},{"v":0.6017505470459517,"d":"0.602"}]
[null,12,{"v":0.96,"d":"96%"},{"v":0.96,"d":"96%"},{"$nulls":3},{"v":0.29,"d":"29%"},{"v":0.47592997811816184,"d":"0.476"},1,{"$f":"SUM(J21:$J$36)","v":12},{"v":0.8,"d":"80.0%"},{"v":0.5448577680525163,"d":"0.545"}]
[null,15,{"v":1,"d":"100%"},{"v":1,"d":"100%"},{"$nulls":3},{"v":0.3,"d":"30%"},{"v":0.49234135667396056,"d":"0.492"},0,{"$f":"SUM(J22:$J$36)","v":12},{"v":0.8,"d":"80.0%"},{"v":0.5317286652078773,"d":"0.532"}]
[{"$nulls":7},{"v":0.35,"d":"35%"},{"v":0.5743982494529539,"d":"0.574"},1,{"$f":"SUM(J23:$J$36)","v":11},{"v":0.7333333333333333,"d":"73.3%"},{"v":0.46608315098468267,"d":"0.466"}]
["(a)","Calculate the loss elimination ratio.",{"$nulls":5},{"v":0.4,"d":"40%"},{"v":0.6564551422319475,"d":"0.656"},0,{"$f":"SUM(J24:$J$36)","v":11},{"v":0.7333333333333333,"d":"73.3%"},{"v":0.40590809628008745,"d":"0.406"}]
[{"$f":"INDEX('Point Grid'!$C$8:$I$27,MATCH($A$1,'Point Grid'!$A$8:$A$27,0),MATCH(A23,'Point Grid'!$C$7:$I$7,0))","v":0.5},{"$nulls":6},{"v":0.41,"d":"41%"},{"v":0.6728665207877461,"d":"0.673"},1,{"$f":"SUM(J25:$J$36)","v":10},{"v":0.6666666666666666,"d":"66.7%"},{"v":0.3938730853391684,"d":"0.394"}]
[0.5,{"$nulls":6},{"v":0.5,"d":"50%"},{"v":0.8205689277899343,"d":"0.821"},2,{"$f":"SUM(J26:$J$36)","v":8},{"v":0.5333333333333333,"d":"53.3%"},{"v":0.29540481400437635,"d":"0.295"}]
[{"$nulls":7},{"v":0.6,"d":"60%"},{"v":0.9846827133479211,"d":"0.985"},0,{"$f":"SUM(J27:$J$36)","v":8},{"v":0.5333333333333333,"d":"53.3%"},{"v":0.20787746170678334,"d":"0.208"}]
["(b)","Calculate the Table M charges at loss ratios from 0% to 100%",{"$nulls":5},{"v":0.63,"d":"63%"},{"v":1.0339168490153172,"d":"1.034"},1,{"$f":"SUM(J28:$J$36)","v":7},{"v":0.4666666666666667,"d":"46.7%"},{"v":0.18161925601750542,"d":"0.182"}]
[{"$f":"INDEX('Point Grid'!$C$8:$I$27,MATCH($A$1,'Point Grid'!$A$8:$A$27,0),MATCH(A27,'Point Grid'!$C$7:$I$7,0))","v":1.5},"using increments of 10%.",{"$nulls":5},{"v":0.66,"d":"66%"},{"v":1.0831509846827132,"d":"1.083"},1,{"$f":"SUM(J29:$J$36)","v":6},{"v":0.4,"d":"40.0%"},{"v":0.1586433260393873,"d":"0.159"}]
[1.5,{"$nulls":6},{"v":0.7,"d":"70%"},{"v":1.1487964989059078,"d":"1.149"},0,{"$f":"SUM(J30:$J$36)","v":6},{"v":0.4,"d":"40.0%"},{"v":0.13238512035010944,"d":"0.132"}]
[{"$nulls":7},{"v":0.71,"d":"71%"},{"v":1.1652078774617065,"d":"1.165"},1,{"$f":"SUM(J31:$J$36)","v":5},{"v":0.3333333333333333,"d":"33.3%"},{"v":0.12582056892778998,"d":"0.126"}]
["(c)","Calculate the Table L charges at loss ratios from 0% to 100% ",{"$nulls":5},{"v":0.75,"d":"75%"},{"v":1.2308533916849014,"d":"1.231"},1,{"$f":"SUM(J32:$J$36)","v":4},{"v":0.26666666666666666,"d":"26.7%"},{"v":0.1039387308533917,"d":"0.104"}]
[{"$f":"INDEX('Point Grid'!$C$8:$I$27,MATCH($A$1,'Point Grid'!$A$8:$A$27,0),MATCH(A31,'Point Grid'!$C$7:$I$7,0))","v":2},"using increments of 10%.",{"$nulls":5},{"v":0.8,"d":"80%"},{"v":1.312910284463895,"d":"1.313"},0,{"$f":"SUM(J33:$J$36)","v":4},{"v":0.26666666666666666,"d":"26.7%"},{"v":0.08205689277899342,"d":"0.082"}]
[2,{"$nulls":6},{"v":0.9,"d":"90%"},{"v":1.4770240700218817,"d":"1.477"},0,{"$f":"SUM(J34:$J$36)","v":4},{"v":0.26666666666666666,"d":"26.7%"},{"v":0.03829321663019692,"d":"0.038"}]
[{"$nulls":7},{"v":0.96,"d":"96%"},{"v":1.5754923413566737,"d":"1.575"},1,{"$f":"SUM(J35:$J$36)","v":3},{"v":0.2,"d":"20.0%"},{"v":0.012035010940919064,"d":"0.012"}]
[{"$nulls":7},{"v":0.99,"d":"99%"},{"v":1.62472647702407,"d":"1.625"},1,{"$f":"SUM(J36:$J$36)","v":2},{"v":0.13333333333333333,"d":"13.3%"},{"$f":"M36+L35*(I36-I35)","v":{"v":2.1881838074398214E-3,"d":"0.002"}}]
[{"$nulls":7},{"v":1,"d":"100%"},{"v":1.6411378555798686,"d":"1.641"},2,0,{"v":0,"d":"0.0%"},{"v":0,"d":"0.000"}]
[]
[{"$nulls":7},"Total",null,{"$f":"SUM(J15:J36)","v":15},"Use this to check you got all of the given loss ratios"]
[]
[{"$nulls":6},"c.)","To build the Table L we follow the same approach but use the limited loss ratios instead and add on the loss elimination ratio."]
[{"$nulls":7},"Notice the entry ratio is still computed as a ratio to E[A] when building a Table L."]
[]
[{"$nulls":7},"Table L: Bold figures are the desired increments."]
[{"$nulls":7},"Loss Ratio","Entry Ratio","# risks","# risks above","% risks above","phi*_D(r)"]
[{"$nulls":7},{"v":0,"d":"0%"},{"$f":"H45/$I$2","v":{"v":0,"d":"0.000"}},{"$f":"COUNTIF($D$7:$D$21,H45)","v":0},{"$f":"SUM(J46:$J$66)","v":15},{"$f":"K45/$J$68","v":{"v":1,"d":"100.0%"}},{"$f":"M46+L45*(I46-I45)","v":{"v":1.0000000000000002,"d":"1.000"}}]
[{"$nulls":7},{"v":0.06,"d":"6%"},{"$f":"H46/$I$2","v":{"v":0.0984682713347921,"d":"0.098"}},{"$f":"COUNTIF($D$7:$D$21,H46)","v":2},{"$f":"SUM(J47:$J$66)","v":13},{"v":0.8666666666666667,"d":"86.7%"},{"v":0.901531728665208,"d":"0.902"}]
[{"$nulls":7},{"v":0.1,"d":"10%"},{"v":0.16411378555798686,"d":"0.164"},0,{"$f":"SUM(J48:$J$66)","v":13},{"v":0.8666666666666667,"d":"86.7%"},{"v":0.8446389496717726,"d":"0.845"}]
[{"$nulls":7},{"v":0.14,"d":"14%"},{"v":0.22975929978118162,"d":"0.230"},1,{"$f":"SUM(J49:$J$66)","v":12},{"v":0.8,"d":"80.0%"},{"v":0.7877461706783371,"d":"0.788"}]
[{"$nulls":7},{"v":0.2,"d":"20%"},{"v":0.3282275711159737,"d":"0.328"},0,{"$f":"SUM(J50:$J$66)","v":12},{"v":0.8,"d":"80.0%"},{"v":0.7089715536105033,"d":"0.709"}]
[{"$nulls":7},{"v":0.25,"d":"25%"},{"v":0.41028446389496714,"d":"0.410"},1,{"$f":"SUM(J51:$J$66)","v":11},{"v":0.7333333333333333,"d":"73.3%"},{"v":0.6433260393873086,"d":"0.643"}]
[{"$nulls":7},{"v":0.3,"d":"30%"},{"v":0.49234135667396056,"d":"0.492"},0,{"$f":"SUM(J52:$J$66)","v":11},{"v":0.7333333333333333,"d":"73.3%"},{"v":0.5831509846827134,"d":"0.583"}]
[{"$nulls":7},{"v":0.35,"d":"35%"},{"v":0.5743982494529539,"d":"0.574"},0,{"$f":"SUM(J53:$J$66)","v":11},{"v":0.7333333333333333,"d":"73.3%"},{"v":0.5229759299781183,"d":"0.523"}]
[{"$nulls":7},{"v":0.4,"d":"40%"},{"v":0.6564551422319475,"d":"0.656"},0,{"$f":"SUM(J54:$J$66)","v":11},{"v":0.7333333333333333,"d":"73.3%"},{"v":0.462800875273523,"d":"0.463"}]
[{"$nulls":7},{"v":0.41,"d":"41%"},{"v":0.6728665207877461,"d":"0.673"},2,{"$f":"SUM(J55:$J$66)","v":9},{"v":0.6,"d":"60.0%"},{"v":0.45076586433260396,"d":"0.451"}]
[{"$nulls":7},{"v":0.45,"d":"45%"},{"v":0.7385120350109409,"d":"0.739"},1,{"$f":"SUM(J56:$J$66)","v":8},{"v":0.5333333333333333,"d":"53.3%"},{"v":0.4113785557986871,"d":"0.411"}]
[{"$nulls":7},{"v":0.5,"d":"50%"},{"v":0.8205689277899343,"d":"0.821"},1,{"$f":"SUM(J57:$J$66)","v":7},{"v":0.4666666666666667,"d":"46.7%"},{"v":0.3676148796498906,"d":"0.368"}]
[{"$nulls":7},{"v":0.6,"d":"60%"},{"v":0.9846827133479211,"d":"0.985"},0,{"$f":"SUM(J58:$J$66)","v":7},{"v":0.4666666666666667,"d":"46.7%"},{"v":0.2910284463894967,"d":"0.291"}]
[{"$nulls":7},{"v":0.66,"d":"66%"},{"v":1.0831509846827132,"d":"1.083"},1,{"$f":"SUM(J59:$J$66)","v":6},{"v":0.4,"d":"40.0%"},{"v":0.24507658643326038,"d":"0.245"}]
[{"$nulls":7},{"v":0.67,"d":"67%"},{"v":1.099562363238512,"d":"1.100"},1,{"$f":"SUM(J60:$J$66)","v":5},{"v":0.3333333333333333,"d":"33.3%"},{"v":0.23851203501094084,"d":"0.239"}]
[{"$nulls":7},{"v":0.7,"d":"70%"},{"v":1.1487964989059078,"d":"1.149"},0,{"$f":"SUM(J61:$J$66)","v":5},{"v":0.3333333333333333,"d":"33.3%"},{"v":0.22210065645514226,"d":"0.222"}]
[{"$nulls":7},{"v":0.71,"d":"71%"},{"v":1.1652078774617065,"d":"1.165"},1,{"$f":"SUM(J62:$J$66)","v":4},{"v":0.26666666666666666,"d":"26.7%"},{"v":0.2166301969365427,"d":"0.217"}]
[{"$nulls":7},{"v":0.75,"d":"75%"},{"v":1.2308533916849014,"d":"1.231"},2,{"$f":"SUM(J63:$J$66)","v":2},{"v":0.13333333333333333,"d":"13.3%"},{"v":0.19912472647702406,"d":"0.199"}]
[{"$nulls":7},{"v":0.8,"d":"80%"},{"v":1.312910284463895,"d":"1.313"},0,{"$f":"SUM(J64:$J$66)","v":2},{"v":0.13333333333333333,"d":"13.3%"},{"v":0.18818380743982493,"d":"0.188"}]
[{"$nulls":7},{"v":0.9,"d":"90%"},{"v":1.4770240700218817,"d":"1.477"},0,{"$f":"SUM(J65:$J$66)","v":2},{"v":0.13333333333333333,"d":"13.3%"},{"v":0.16630196936542668,"d":"0.166"}]
[{"$nulls":7},{"v":0.96,"d":"96%"},{"v":1.5754923413566737,"d":"1.575"},1,{"$f":"SUM(J66:$J$66)","v":1},{"v":0.06666666666666667,"d":"6.7%"},{"$f":"M66+L65*(I66-I65)","v":{"v":0.15317286652078776,"d":"0.153"}}]
[{"$nulls":7},{"v":1,"d":"100%"},{"v":1.6411378555798686,"d":"1.641"},1,0,{"$f":"K66/$J$68","v":{"v":0,"d":"0.0%"}},{"$f":"I7","v":{"v":0.1487964989059081,"d":"0.149"}}]
[]
[{"$nulls":7},"Total",null,{"$f":"SUM(J45:J66)","v":15}]

{"sheet":"14","index":16,"n_rows":28,"n_cols":15,"rows_kept":28,"merged":[[0,7,0,8]]}
[{"$f":"VALUE(MID(CELL(\"filename\",A1),FIND(\"]\",CELL(\"filename\",A1))+1,LEN(CELL(\"filename\",A1))-FIND(\"]\",CELL(\"filename\",A1))+1))","v":14},"Finished",{"$nulls":5},"Navigator",null,null,"Solution"]
[{"$nulls":9},"a.)","Calculate the deductible layer losses, given a claim occurred"]
["Points","Given the following information:",{"$nulls":8},"% of Claims","Deductible"]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('14'!A1,'Point Grid'!A:A,0))","v":3.25},null,null,"Ground-up",{"$nulls":6},{"$f":"C6","v":{"v":0.45,"d":"45%"}},{"$f":"MIN(D6,$B$21)","v":{"v":22000,"d":"22,000"}}]
[null,null,"Percent of Claims","Loss per Claim",{"$nulls":6},{"$f":"C7","v":{"v":0.2,"d":"20%"}},{"$f":"MIN(D7,$B$21)","v":{"v":35000,"d":"35,000"}}]
[null,null,{"v":0.45,"d":"45%"},{"v":22000,"d":"22,000"},{"$nulls":6},{"$f":"C8","v":{"v":0.15,"d":"15%"}},{"$f":"MIN(D8,$B$21)","v":{"v":100000,"d":"100,000"}}]
[null,null,{"v":0.2,"d":"20%"},{"v":35000,"d":"35,000"},{"$nulls":6},{"$f":"C9","v":{"v":0.15,"d":"15%"}},{"$f":"MIN(D9,$B$21)","v":{"v":100000,"d":"100,000"}}]
[null,null,{"v":0.15,"d":"15%"},{"v":150000,"d":"150,000"},{"$nulls":6},{"$f":"C10","v":{"v":0.05,"d":"5%"}},{"$f":"MIN(D10,$B$21)","v":{"v":100000,"d":"100,000"}}]
[null,null,{"v":0.15,"d":"15%"},{"v":250000,"d":"250,000"}]
[null,null,{"v":0.05,"d":"5%"},{"v":1000000,"d":"1,000,000"},{"$nulls":6},"Expected deductible losses, E[A_D] =",null,null,{"$f":"SUMPRODUCT(K4:K8,L4:L8)","v":{"v":51900,"d":"51,900"}}]
[]
[null,{"v":24793,"d":"$24,793"},"Full Coverage Premium",{"$nulls":7},"Expected unlimited losses, E[A] =",null,null,{"$f":"SUMPRODUCT(C6:C10,D6:D10)","v":{"v":126900,"d":"126,900"}}]
[null,{"v":0.59,"d":"59%"},"Expected Ground-up Loss Ratio"]
[null,{"v":0.1,"d":"10%"},"ALAE as a % of losses (assume the deductible doesn't apply to ALAE)",{"$nulls":7},"Expected % Deductible Loss =",null,null,{"$f":"N10/N12","v":{"v":0.408983451536643,"d":"0.4090"}},"This is the portion of losses we eliminate by introducing the deductible"]
[null,{"v":0.04,"d":"4%"},"Incremental Fixed Expenses for processing a deductible (% of deductible losses)"]
[null,{"v":0.01,"d":"1%"},"Load for uncollectible deductible payments (% of deductible losses)"]
[null,{"v":0.08,"d":"8%"},"Profit Provision",{"$nulls":7},"Expected Ground-up loss",null,null,{"$f":"B12*B13","v":{"v":14627.869999999999,"d":"$14,627.87"}}]
[null,{"v":0.05,"d":"5%"},"Additional Risk Load (% of excess losses)"]
[null,{"v":0.1,"d":"10%"},"Commission",{"$nulls":7},"Expected Deductible Loss",null,null,{"$f":"N17*N14","v":{"v":5982.556761229313,"d":"$5,982.56"}}]
[null,{"v":0.05,"d":"5%"},"Other Variable Expenses"]
[null,{"v":100000,"d":"$100,000"},"Deductible",{"$nulls":7},"ALAE",null,null,{"$f":"B14*N17","v":{"v":1462.787,"d":"$1,462.79"}},"Remember, ALAE is calculated on all losses, not just the portion in excess of the deductible."]
[{"$nulls":10},"Fixed Expense",null,null,{"$f":"B15*N19","v":{"v":239.30227044917254,"d":"$239.30"}}]
["(a)","Calculate the final premium for a policy with a $100,000 deductible.",{"$nulls":8},"Credit Risk",null,null,{"$f":"B16*N19","v":{"v":59.825567612293135,"d":"$59.83"}}]
[{"$f":"INDEX('Point Grid'!$C$8:$I$27,MATCH($A$1,'Point Grid'!$A$8:$A$27,0),MATCH(A23,'Point Grid'!$C$7:$I$7,0))","v":3.25},{"$nulls":9},"Additional Risk Load",null,null,{"$f":"B18*(N17-N19)","v":{"v":432.2656619385343,"d":"$432.27"}}]
[3.25]
[{"$nulls":10},"Now apply the standard pricing formula (loss + ALAE + fixed expense) / (1 - profit provision - variable expense - commission)"]
[]
[{"$nulls":10},"Final Premium",null,null,{"$f":"((N17-N19)+N21+N22+N23+N24)/(1-B17-B19-B20)","v":{"v":14077.264595806086,"d":"$14,077.26"}}]

{"sheet":"15","index":17,"n_rows":39,"n_cols":12,"rows_kept":39,"merged":[[0,5,0,6]]}
[{"$f":"VALUE(MID(CELL(\"filename\",A1),FIND(\"]\",CELL(\"filename\",A1))+1,LEN(CELL(\"filename\",A1))-FIND(\"]\",CELL(\"filename\",A1))+1))","v":15},"Finished",{"$nulls":3},"Navigator",null,null,"Solution"]
[{"$nulls":7},"a.)","(i): H + I"]
["Points","The diagram below exhibits cumulative distribution functions:",{"$nulls":6},"(ii): B + E + H + I"]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('15'!A1,'Point Grid'!A:A,0))","v":2},"   • F(y) represents aggregate loss ratios with no loss limit, and",{"$nulls":6},"(iii): A + B"]
[null,"   • F*(y) represents aggregate loss ratios adjusted for a per-occurrence loss limit.",{"$nulls":6},"(iv): (B + E + H) / (B + C + E + F + H + I) = B + E + H"]
[{"$nulls":8},"Alice: \"You should remember the area under F equals 1 so the denominator is 1.\""]
[null,"The diagram is also partitioned into nine areas labeled with the letters from A to I."]
[{"$nulls":7},"b.)","Left Hand Side of Equation"]
[{"$nulls":8},"Table L Charge - Table M Charge =",null,null,"(B + E + H + I) - (H + I) = B + E"]
[]
[{"$nulls":8},"Right Hand Side of Equation"]
[{"$nulls":8},"Table L Savings - Table M Savings =",null,null,"(A + B + D + E) - (A + D) = B + E"]
[]
[{"$nulls":8},"LHS = RHS so the equation is valid."]
{"$rows_empty":15}
["(a)","Using the labels provided, give an algebraic expression for each of the following quantities:"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$27,MATCH($A$1,'Point Grid'!$A$8:$A$27,0),MATCH(A30,'Point Grid'!$C$7:$I$7,0))","v":1},"   (i) The Table M Charge at rG"]
[1,"   (ii) The Table L Charge at rG"]
[null,"   (iii) The Table L Savings at rH"]
[null,"   (iv) The Loss Elimination Ratio"]
[]
["(b)","Using the labels provided and the entry ratio rG, algebraically demonstrate whether the"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$27,MATCH($A$1,'Point Grid'!$A$8:$A$27,0),MATCH(A36,'Point Grid'!$C$7:$I$7,0))","v":1},"following equation is valid."]
[1]
[null,"Table L Charge - Table M Charge = Table L Savings - Table M Savings"]

{"sheet":"16","index":18,"n_rows":19,"n_cols":13,"rows_kept":19,"merged":[[0,5,0,6]]}
[{"$f":"VALUE(MID(CELL(\"filename\",A1),FIND(\"]\",CELL(\"filename\",A1))+1,LEN(CELL(\"filename\",A1))-FIND(\"]\",CELL(\"filename\",A1))+1))","v":16},"Finished",{"$nulls":3},"Navigator",null,null,"Solution"]
[{"$nulls":7},"a.)","We need the capped pure premium distributions and then to calculate the variance and standard deviation."]
["Points","You believe the standard deviation of pure premiums is an appropriate measure of risk."]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('16'!A1,'Point Grid'!A:A,0))","v":2},"You are given the following pure premium distribution:",{"$nulls":6},"Cap at $2,000",null,null,"Cap at $5,000"]
[{"$nulls":8},"Capped Amount","Probability",null,"Capped Amount","Probability"]
[null,"Loss Amount","Probability",{"$nulls":5},{"$f":"MIN(B7,$B$12)","v":{"v":0,"d":"$0"}},{"$f":"C7","v":{"v":0.9,"d":"90%"}},null,{"$f":"MIN(B7,$B$13)","v":{"v":0,"d":"$0"}},{"$f":"C7","v":{"v":0.9,"d":"90%"}}]
[null,{"v":0,"d":"$0"},{"v":0.9,"d":"90%"},{"$nulls":5},{"$f":"MIN(B8,$B$12)","v":{"v":1000,"d":"$1,000"}},{"$f":"C8","v":{"v":0.09,"d":"9%"}},null,{"$f":"MIN(B8,$B$13)","v":{"v":1000,"d":"$1,000"}},{"$f":"C8","v":{"v":0.09,"d":"9%"}}]
[null,{"v":1000,"d":"$1,000"},{"v":0.09,"d":"9%"},{"$nulls":5},{"$f":"MIN(B9,$B$12)","v":{"v":2000,"d":"$2,000"}},{"$f":"C9","v":{"v":0.01,"d":"1%"}},null,{"$f":"MIN(B9,$B$13)","v":{"v":5000,"d":"$5,000"}},{"$f":"C9","v":{"v":0.01,"d":"1%"}}]
[null,{"v":10000,"d":"$10,000"},{"v":0.01,"d":"1%"}]
[{"$nulls":8},"E[S] =",{"$f":"SUMPRODUCT(I6:I8,J6:J8)","v":110},null,"E[S] =",{"$f":"SUMPRODUCT(L6:L8,M6:M8)","v":140}]
[null,{"v":0.1,"d":"10%"},"% of standard deviation of pure premium chosen as risk load",{"$nulls":5},"E[S^2] =",{"$f":"SUMPRODUCT(I6:I8,I6:I8,J6:J8)","v":130000},null,"E[S^2] =",{"$f":"SUMPRODUCT(L6:L8,L6:L8,M6:M8)","v":340000}]
[null,{"v":2000,"d":"$2,000"},"Basic aggregate limit",{"$nulls":5},"Var(S) =",{"$f":"J11-J10^2","v":117900},null,"Var(S) =",{"$f":"M11-M10^2","v":320400}]
[null,{"v":5000,"d":"$5,000"},"Aggregate limit on policy being priced",{"$nulls":5},"Std Dev S =",{"$f":"SQRT(J12)","v":343.3656942677879},null,"Std Dev S =",{"$f":"SQRT(M12)","v":566.0388679233962}]
[{"$nulls":8},"Risk Load =",{"$f":"B11*J13","v":{"v":34.33656942677879,"d":"34.34"}},null,"Risk Load =",{"$f":"B11*M13","v":{"v":56.60388679233962,"d":"56.60"}}]
[null,"You may assume there are no expenses."]
[{"$nulls":8},"Risk Loaded ILF at $5,000",null,{"$f":"(M10+M14)/(J10+J14)","v":{"v":1.3621211005162193,"d":"1.362"}}]
["(a)","Calculate the increased limit factor with risk load for a $5,000 aggregate policy limit."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$27,MATCH($A$1,'Point Grid'!$A$8:$A$27,0),MATCH(A17,'Point Grid'!$C$7:$I$7,0))","v":2}]
[2]

{"sheet":"17","index":19,"n_rows":33,"n_cols":13,"rows_kept":33,"merged":[[0,4,0,5]]}
[{"$f":"VALUE(MID(CELL(\"filename\",A1),FIND(\"]\",CELL(\"filename\",A1))+1,LEN(CELL(\"filename\",A1))-FIND(\"]\",CELL(\"filename\",A1))+1))","v":17},"Finished",null,null,"Navigator",null,null,"Solution"]
[{"$nulls":6},"a.)","Initial Loss in Layer",null,null,"Trend claims by one year"]
["Points","An insurance company purchases Workers' Compensation excess of loss ",{"$nulls":5},"Loss Number","Loss in Layer",null,"Loss Number","Trended Loss","Loss in Layer"]
[{"$f":"INDEX('Point Grid'!B:B,MATCH(VALUE('17'!A1),'Point Grid'!A:A,0))","v":1},"coverage of $500,000 per claim. Losses in the current year are as follows:",{"$nulls":5},{"$f":"B7","v":1},{"$f":"MIN($B$14,MAX(0,C7-$B$13))","v":0},null,{"$f":"B7","v":1},{"$f":"C7*(1+$B$15)","v":{"v":545000,"d":"545,000"}},{"$f":"MIN($B$14,MAX(0,L4-$B$13))","v":{"v":45000,"d":"45,000"}}]
[{"$nulls":7},{"$f":"B8","v":2},{"$f":"MIN($B$14,MAX(0,C8-$B$13))","v":{"v":100000,"d":"100,000"}},null,{"$f":"B8","v":2},{"$f":"C8*(1+$B$15)","v":{"v":654000,"d":"654,000"}},{"$f":"MIN($B$14,MAX(0,L5-$B$13))","v":{"v":154000,"d":"154,000"}}]
[null,"Loss Number","Incurred Loss",{"$nulls":4},{"$f":"B9","v":3},{"$f":"MIN($B$14,MAX(0,C9-$B$13))","v":0},null,{"$f":"B9","v":3},{"$f":"C9*(1+$B$15)","v":{"v":381500,"d":"381,500"}},{"$f":"MIN($B$14,MAX(0,L6-$B$13))","v":0}]
[null,1,{"v":500000,"d":"500,000"},{"$nulls":4},{"$f":"B10","v":4},{"$f":"MIN($B$14,MAX(0,C10-$B$13))","v":{"v":500000,"d":"500,000"}},null,{"$f":"B10","v":4},{"$f":"C10*(1+$B$15)","v":{"v":1635000.0000000002,"d":"1,635,000"}},{"$f":"MIN($B$14,MAX(0,L7-$B$13))","v":{"v":500000,"d":"500,000"}}]
[null,2,{"v":600000,"d":"600,000"},{"$nulls":4},{"$f":"B11","v":5},{"$f":"MIN($B$14,MAX(0,C11-$B$13))","v":{"v":450000,"d":"450,000"}},null,{"$f":"B11","v":5},{"$f":"C11*(1+$B$15)","v":{"v":1035500.0000000001,"d":"1,035,500"}},{"$f":"MIN($B$14,MAX(0,L8-$B$13))","v":{"v":500000,"d":"500,000"}}]
[null,3,{"v":350000,"d":"350,000"}]
[null,4,{"v":1500000,"d":"1,500,000"},{"$nulls":4},"Total",{"$f":"SUM(I4:I8)","v":{"v":1050000,"d":"1,050,000"}},null,"Total",null,{"$f":"SUM(M4:M8)","v":{"v":1199000,"d":"1,199,000"}}]
[null,5,{"v":950000,"d":"950,000"}]
[{"$nulls":7},"Inflation rate in layer",null,{"$f":"M10/I10-1","v":{"v":0.14190476190476198,"d":"14.2%"}}]
[null,{"v":500000,"d":"500,000"},"Retention"]
[null,{"v":500000,"d":"500,000"},"Limit",{"$nulls":4},"Alice: \"Here's a bonus - another way of approaching this problem. Rather than trending the claims,"]
[null,{"v":0.09,"d":"9%"},"Inflation rate for Workers' Compensation claims",{"$nulls":4},"you can detrend the future limits to today instead.\""]
[]
["(a)","Calculate the inflation rate in the $500,000 excess of $500,000 layer.",{"$nulls":5},"Detrended Retention (lower bound)",{"$nulls":3},{"$f":"B13/(1+B15)","v":{"v":458715.5963302752,"d":"458,716"}}]
[{"$f":"INDEX('Point Grid'!$C$8:$I$27,MATCH($A$1,'Point Grid'!$A$8:$A$27,0),MATCH(A17,'Point Grid'!$C$7:$I$7,0))","v":1},{"$nulls":6},"Detrended Retention + Limit (upper bound)",{"$nulls":3},{"$f":"(B13+B14)/(1+B15)","v":{"v":917431.1926605504,"d":"917,431"}}]
[1,{"$nulls":6},"Layer size",{"$nulls":3},{"$f":"L18-L17","v":{"v":458715.5963302752,"d":"458,716"}}]
[]
[{"$nulls":7},"Loss in layer"]
[{"$nulls":7},"Loss Number","Loss in Layer"]
[{"$nulls":7},{"$f":"B7","v":1},{"$f":"MIN($L$19,MAX(0,C7-$L$17))","v":{"v":41284.40366972482,"d":"41,284"}}]
[{"$nulls":7},{"$f":"B8","v":2},{"$f":"MIN($L$19,MAX(0,C8-$L$17))","v":{"v":141284.40366972482,"d":"141,284"}}]
[{"$nulls":7},{"$f":"B9","v":3},{"$f":"MIN($L$19,MAX(0,C9-$L$17))","v":0}]
[{"$nulls":7},{"$f":"B10","v":4},{"$f":"MIN($L$19,MAX(0,C10-$L$17))","v":{"v":458715.5963302752,"d":"458,716"}}]
[{"$nulls":7},{"$f":"B11","v":5},{"$f":"MIN($L$19,MAX(0,C11-$L$17))","v":{"v":458715.5963302752,"d":"458,716"}}]
[]
[{"$nulls":7},"Total",{"$f":"SUM(I23:I27)","v":{"v":1100000,"d":"1,100,000"}}]
[]
[{"$nulls":7},"Change in level today:",null,{"$f":"I29/I10-1","v":{"v":0.04761904761904767,"d":"4.8%"}}]
[{"$nulls":7},"Change in level after 1 year:",null,{"$f":"(1+J31)*(1+B15)-1","v":{"v":0.14190476190476198,"d":"14.2%"}},"We get the same result provided we remember to match the point in time where"]
[{"$nulls":10},"we make the inflation measurement."]

{"sheet":"18","index":20,"n_rows":28,"n_cols":16,"rows_kept":28,"merged":[[0,6,0,7]]}
[{"$f":"VALUE(MID(CELL(\"filename\",A1),FIND(\"]\",CELL(\"filename\",A1))+1,LEN(CELL(\"filename\",A1))-FIND(\"]\",CELL(\"filename\",A1))+1))","v":18},"Finished",{"$nulls":4},"Navigator",null,null,"Solution"]
[{"$nulls":8},"a.)","For a uniform distribution on [0, b] we have:"]
["Points","Per-claim accident severity is modeled using a uniform distribution on the"]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('18'!A1,'Point Grid'!A:A,0))","v":3.25},"interval [0, 18,000]."]
[{"$nulls":9},{"$f":"AVERAGE(0,18000)","v":{"v":9000,"d":"9,000"}},"Expected Severity"]
["(a)","Using the equally spaced evaluation points of $3,000 calculate the probability density "]
[{"$f":"INDEX('Point Grid'!$C$8:$I$27,MATCH($A$1,'Point Grid'!$A$8:$A$27,0),MATCH(A6,'Point Grid'!$C$7:$I$7,0))","v":2},"function for a discrete version of this distribution for use in Panjer's recursive",{"$nulls":7},"xi","XS(xi)","LEVi","LILi","CDF","PDF","PDF Panjer"]
[2," algorithm.",{"$nulls":7},0,{"$f":"(1-J8/18000)^2","v":{"v":1,"d":"1.000"}},{"$f":"(1-K8)*$J$5","v":0},0,{"$f":"1-M9/(J9-J8)","v":{"v":0.08333333333333359,"d":"0.083"}},{"$f":"N8","v":{"v":0.08333333333333359,"d":"0.083"}},{"v":0,"d":"0.000"}]
[{"$nulls":9},{"v":3000,"d":"3,000"},{"$f":"(1-J9/18000)^2","v":{"v":0.6944444444444445,"d":"0.694"}},{"$f":"(1-K9)*$J$5","v":{"v":2749.999999999999,"d":"2,750"}},{"$f":"L9-L8","v":{"v":2749.999999999999,"d":"2,750"}},{"v":0.25,"d":"0.250"},{"$f":"N9-N8","v":{"v":0.1666666666666664,"d":"0.167"}},{"$f":"O9/(1-$O$8)","v":{"v":0.18181818181818157,"d":"0.182"}}]
[null,"A multi-state, multi-hazard group policy is being priced using the following information:",{"$nulls":7},{"v":6000,"d":"6,000"},{"v":0.44444444444444453,"d":"0.444"},{"v":4999.999999999999,"d":"5,000"},{"v":2250,"d":"2,250"},{"v":0.4166666666666664,"d":"0.417"},{"v":0.1666666666666664,"d":"0.167"},{"v":0.18181818181818157,"d":"0.182"}]
[null,"Claim counts are modeled using a negative binomial distribution.",{"$nulls":7},{"v":9000,"d":"9,000"},{"v":0.25,"d":"0.250"},{"v":6750,"d":"6,750"},{"v":1750.000000000001,"d":"1,750"},{"v":0.5833333333333333,"d":"0.583"},{"v":0.16666666666666685,"d":"0.167"},{"v":0.18181818181818207,"d":"0.182"}]
[{"$nulls":9},{"v":12000,"d":"12,000"},{"v":0.11111111111111113,"d":"0.111"},{"v":8000,"d":"8,000"},{"v":1250,"d":"1,250"},{"v":0.75,"d":"0.750"},{"v":0.16666666666666674,"d":"0.167"},{"v":0.18181818181818196,"d":"0.182"}]
[null,null,"Hazard","Expected","Average ",{"$nulls":4},{"v":15000,"d":"15,000"},{"v":0.027777777777777766,"d":"0.028"},{"v":8750,"d":"8,750"},750,{"v":0.9166666666666666,"d":"0.917"},{"v":0.16666666666666663,"d":"0.167"},{"v":0.18181818181818182,"d":"0.182"}]
[null,"State","Group","Loss","Claim Size",{"$nulls":4},{"v":18000,"d":"18,000"},{"v":0,"d":"0.000"},{"v":9000,"d":"9,000"},250,{"v":1,"d":"1.000"},{"v":0.08333333333333337,"d":"0.083"},{"v":0.09090909090909098,"d":"0.091"}]
[null,"AK","E",{"v":240000,"d":"240,000"},{"v":15000,"d":"15,000"}]
[null,"AL","G",{"v":171000,"d":"171,000"},{"v":9000,"d":"9,000"},{"$nulls":3},"b.)","E[N]PC =",{"$f":"D15/E15+D16/E16","v":35}]
[{"$nulls":9},"VtMPC =",{"$f":"1.5*K16^0.75","v":21.584514887277287}]
[null,"Variance-to-Mean relationships for claim count distributions:",{"$nulls":7},"Var(N)PC =",{"$f":"K17*K16","v":755.458021054705}]
[null,"VtMPC = 1 + 0.811265 * E[N]PC if E[N]PC ≤ 3.697927"]
[null,"VtMPC = 1.5 * (E[N]PC)0.75 if E[N]PC > 3.697927",{"$nulls":7},"Adjustment Factor AF0 =",null,{"$f":"1-O8","v":{"v":0.9166666666666664,"d":"0.917"}}]
[]
["(b)","Calculate the mean and variance-to-mean function for the expected claim counts on",{"$nulls":7},"E[N]Panjer =",{"$f":"K16*L20","v":32.08333333333332}]
[{"$f":"INDEX('Point Grid'!$C$8:$I$27,MATCH($A$1,'Point Grid'!$A$8:$A$27,0),MATCH(A22,'Point Grid'!$C$7:$I$7,0))","v":1},"a per-claim basis for use in Panjer's recursive algorithm.",{"$nulls":7},"VtMPanjer =",{"$f":"K17*L20","v":19.78580531333751}]
[1]
[{"$nulls":8},"c.)","By directly calculating the limited aggregate loss distribution there is no need for an adjustment to "]
["(c)","Briefly describe an advantage of the NCCI Circular approach to produce the ",{"$nulls":7},"remove the overlap between the loss limit and the aggregate loss limit."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$27,MATCH($A$1,'Point Grid'!$A$8:$A$27,0),MATCH(A26,'Point Grid'!$C$7:$I$7,0))","v":0.25},"Aggregate Loss Factors on Demand."]
[0.25]

{"sheet":"19","index":21,"n_rows":36,"n_cols":14,"rows_kept":36,"merged":[[0,7,0,8]]}
[{"$f":"VALUE(MID(CELL(\"filename\",A1),FIND(\"]\",CELL(\"filename\",A1))+1,LEN(CELL(\"filename\",A1))-FIND(\"]\",CELL(\"filename\",A1))+1))","v":19},"Finished",{"$nulls":5},"Navigator",null,null,"Solution"]
[{"$nulls":9},"a.)","This diminishing deductible eliminates all losses below $500 and eliminates proportionally less until the ground-up"]
["Points","An actuary is pricing a diminishing deductible which starts at $500 and ends ",{"$nulls":8},"claim size reaches $1,500 at which point the deductible does not eliminate any loss."]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('19'!A1,'Point Grid'!A:A,0))","v":2.5},"at $1,500 using the following ground-up claims data:",{"$nulls":9},"Size of Deductible"]
[{"$nulls":10},"Claim","Diminishing","Franchise","Straight"]
[null,null,"Claim #","Loss Size",null,"Claim #","Loss Size",{"$nulls":3},1,{"$f":"IF(D7<500,D7,IF(D7>1500,0,500/(1500-500)*(1500-D7)))","v":325},{"$f":"IF(D7<=1500,D7,0)","v":850},{"$f":"MIN(D7,500)","v":500}]
[null,null,1,850,null,9,400,{"$nulls":3},2,{"$f":"IF(D8<500,D8,IF(D8>1500,0,500/(1500-500)*(1500-D8)))","v":0},{"$f":"IF(D8<=1500,D8,0)","v":0},{"$f":"MIN(D8,500)","v":500}]
[null,null,2,{"v":2250,"d":"2,250"},null,10,{"v":5000,"d":"5,000"},{"$nulls":3},3,125,{"v":1250,"d":"1,250"},500]
[null,null,3,{"v":1250,"d":"1,250"},null,11,{"v":1500,"d":"1,500"},{"$nulls":3},4,250,{"v":1000,"d":"1,000"},500]
[null,null,4,{"v":1000,"d":"1,000"},null,12,{"v":2000,"d":"2,000"},{"$nulls":3},5,0,0,500]
[null,null,5,{"v":2750,"d":"2,750"},null,13,100,{"$nulls":3},6,500,500,500]
[null,null,6,500,null,14,900,{"$nulls":3},7,250,250,250]
[null,null,7,250,null,15,{"v":1750,"d":"1,750"},{"$nulls":3},8,0,0,500]
[null,null,8,{"v":3500,"d":"3,500"},{"$nulls":6},9,{"$f":"IF(G7<500,G7,IF(G7>1500,0,500/(1500-500)*(1500-G7)))","v":400},{"$f":"IF(G7<=1500,G7,0)","v":400},{"$f":"MIN(G7,500)","v":400}]
[{"$nulls":10},10,{"$f":"IF(G8<500,G8,IF(G8>1500,0,500/(1500-500)*(1500-G8)))","v":0},{"$f":"IF(G8<=1500,G8,0)","v":0},{"$f":"MIN(G8,500)","v":500}]
["(a)","Calculate the loss elimination ratio which results from this diminishing",{"$nulls":8},11,0,{"v":1500,"d":"1,500"},500]
[{"$f":"INDEX('Point Grid'!$C$8:$I$27,MATCH($A$1,'Point Grid'!$A$8:$A$27,0),MATCH(A16,'Point Grid'!$C$7:$I$7,0))","v":1},"deductible.",{"$nulls":8},12,0,0,500]
[1,{"$nulls":9},13,100,100,100]
[{"$nulls":10},14,300,900,500]
[null,"The underwriter responsible for this policy believes it is better for the",{"$nulls":8},15,0,0,500]
[null,"insurer to require a $1,500 franchise deductible than a $500 straight ",{"$nulls":8},"Total",{"$f":"SUM(L6:L20)","v":{"v":2250,"d":"2,250"}},{"$f":"SUM(M6:M20)","v":{"v":6750,"d":"6,750"}},{"$f":"SUM(N6:N20)","v":{"v":6750,"d":"6,750"}}]
[null,"deductible as the higher franchise deductible will eliminate more losses "]
[null,"than the straight deductible.",{"$nulls":8},"Total loss",{"$f":"SUM(D7:D14,G7:G13)","v":{"v":24000,"d":"24,000"}}]
[]
["(b)","Briefly explain why the underwriter is incorrect.",{"$nulls":8},"LER =",{"$f":"L21/L23","v":{"v":0.09375,"d":"9.4%"}},{"$f":"M21/L23","v":{"v":0.28125,"d":"28.1%"}},{"$f":"N21/L23","v":{"v":0.28125,"d":"28.1%"}}]
[{"$f":"INDEX('Point Grid'!$C$8:$I$27,MATCH($A$1,'Point Grid'!$A$8:$A$27,0),MATCH(A25,'Point Grid'!$C$7:$I$7,0))","v":0.5}]
[0.5,{"$nulls":8},"b.)","Using the above table we see the franchise deductible and straight deductible have the same LER."]
[{"$nulls":10},"Therefore, the insurer's expected loss is the same under each."]
["(c)","Give one reason in support of the franchise deductible and another "]
[{"$f":"INDEX('Point Grid'!$C$8:$I$27,MATCH($A$1,'Point Grid'!$A$8:$A$27,0),MATCH(A29,'Point Grid'!$C$7:$I$7,0))","v":1},"reason in support of the straight deductible.",{"$nulls":7},"c.)","For Franchise Deductible:"]
[1,{"$nulls":9},"The franchise deductible will eliminate more small value nuisance claims than the straight deductible. "]
[{"$nulls":10},"This could reduce the insurer's loss adjustment expenses."]
[]
[{"$nulls":10},"For Straight Deductible:"]
[{"$nulls":10},"If the insurer pays all losses and seeks reimbursement for the deductible then the lower straight deductible"]
[{"$nulls":10},"reduces the credit risk associated with any individual claim (the expected overall credit risk is the same)."]

{"sheet":"20","index":22,"n_rows":44,"n_cols":16,"rows_kept":44}
[{"$f":"VALUE(MID(CELL(\"filename\",A1),FIND(\"]\",CELL(\"filename\",A1))+1,LEN(CELL(\"filename\",A1))-FIND(\"]\",CELL(\"filename\",A1))+1))","v":20},"Finished",{"$nulls":5},"Navigator",null,"Solution"]
[{"$nulls":8},"a.)","For a Pareto distribution we have: "]
["Points","An actuary is estimating the impact of inflation on their book of business."]
[{"$f":"INDEX('Point Grid'!B:B,MATCH(A1,'Point Grid'!A:A,0))","v":2.75},{"$nulls":8},"E[X; 9,000] =",null,{"$f":"C7/(C6-1)*(1-(C7/(9000+C7))^(C6-1))","v":{"v":2250,"d":"2,250"}}]
[null,"They model claim-size using a Pareto distribution with parameters:",{"$nulls":7},"E[X; 5,000] =",null,{"$f":"C7/(C6-1)*(1-(C7/(5000+C7))^(C6-1))","v":{"v":1875,"d":"1,875"}}]
[null,"α =",2]
[null,"β =",{"v":3000,"d":"3,000"},{"$nulls":6},"Excess layer expected severity ="]
[]
[null,"Estimated Inflation",{"v":0.1,"d":"10%"},{"$nulls":6},"F(5,000) =",{"$f":"1-(C7/(5000+C7))^C6","v":0.859375}]
[null,"Exposure Growth",{"v":0.05,"d":"5%"}]
[{"$nulls":9},"Excess layer expected severity =",null,null,{"$f":"(L4-L5)/(1-K9)","v":{"v":2666.6666666666665,"d":"$2,666.67"}}]
["(a)","Calculate the expected excess severity for the layer 4,000 excess of 5,000."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$27,MATCH($A$1,'Point Grid'!$A$8:$A$27,0),MATCH(A12,'Point Grid'!$C$7:$I$7,0))","v":0.75},{"$nulls":7},"b.)","We detrend the layer before applying the approach used in part a.)"]
[0.75]
[{"$nulls":9},"Before","After","F(x)","E[X; L]"]
["(b)","Calculate the annual trend rate for claims in the layer 4,000 excess of 5,000.",{"$nulls":7},{"v":9000,"d":"9,000"},{"$f":"J16/(1+$C$9)","v":{"v":8181.818181818181,"d":"8,182"}},{"$f":"1-(C7/(K16+C7))^C6","v":{"v":0.9280190362879238,"d":"0.9280"}},{"$f":"C7/(C6-1)*(1-(C7/(K16+C7))^(C6-1))","v":{"v":2195.121951219512,"d":"2,195"}}]
[{"$f":"INDEX('Point Grid'!$C$8:$I$27,MATCH($A$1,'Point Grid'!$A$8:$A$27,0),MATCH(A16,'Point Grid'!$C$7:$I$7,0))","v":0.5},{"$nulls":8},{"v":5000,"d":"5,000"},{"$f":"J17/(1+$C$9)","v":{"v":4545.454545454545,"d":"4,545"}},{"$f":"1-(C7/(K17+C7))^C6","v":{"v":0.8419219044854115,"d":"0.8419"}},{"$f":"C7/(C6-1)*(1-(C7/(K17+C7))^(C6-1))","v":{"v":1807.2289156626505,"d":"1,807"}}]
[0.5]
[{"$nulls":9},"Excess layer expected severity =",null,null,{"$f":"(1+C9)*(M16-M17)/(1-L17)","v":{"v":2699.1869918699185,"d":"$2,699.19"}}]
["(c)","Briefly describe why the annual trend rate is lower in part b.) above than the"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$27,MATCH($A$1,'Point Grid'!$A$8:$A$27,0),MATCH(A20,'Point Grid'!$C$7:$I$7,0))","v":0.5},"estimated inflation chosen by the actuary.",{"$nulls":7},"Annual Trend Rate =",null,{"$f":"M19/M11-1","v":{"v":0.012195121951219523,"d":"1.22%"}}]
[0.5]
[{"$nulls":8},"c.) ","A uniform inflationary trend does not cause all claims below the attachment point to then exceed it."]
["(d)","Calculate the annual change in:",{"$nulls":7},"Further, the upper limit serves to reduce the impact of inflation further because once a claim exceeds this limit"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$27,MATCH($A$1,'Point Grid'!$A$8:$A$27,0),MATCH(A24,'Point Grid'!$C$7:$I$7,0))","v":1},"i.) Aggregate Loss",{"$nulls":7},"inflation no longer impacts the layer."]
[1,"ii.) Aggregate Loss, due to claim-size inflation only"]
[{"$nulls":8},"d.)","Claim count annual trend due to inflation = "]
[]
[{"$nulls":9},"We are interested in the layer 4,000 excess of 5,000 so L = 5,000."]
[{"$nulls":9},"S(5,000) =",{"$f":"(C7/(5000+C7))^C6","v":0.140625}]
[{"$nulls":9},"S(4,545) =",{"$f":"1-L17","v":{"v":0.1580780955145885,"d":"0.1581"}}]
[]
[{"$nulls":9},"Claim count annual trend due to inflation",{"$nulls":3},{"$f":"K31/K30-1","v":{"v":0.12411090143707382,"d":"12.4%"}}]
[{"$nulls":9},"However, claim counts are also increasing due to exposure growth. "]
[{"$nulls":9},"We assume the new exposures follow the same claim size distribution and loss frequency."]
[]
[{"$nulls":9},"Claim count annual trend to inflation and exposure growth",{"$nulls":5},{"$f":"(1+N33)*(1+C10)-1","v":{"v":0.18031644650892753,"d":"18.0%"}}]
[]
[{"$nulls":9},"In part b.) above, we calculated the claim size annual trend as",{"$nulls":5},{"$f":"L21","v":{"v":0.012195121951219523,"d":"1.22%"}}]
[]
[{"$nulls":8},"i.)","So the annual aggregate loss trend is",{"$nulls":3},{"$f":"(1+P37)*(1+P39)-1","v":{"v":0.194710549515134,"d":"19.47%"}}]
[]
[{"$nulls":8},"ii.)","We can now back out the exposure growth."]
[{"$nulls":9},"Annual aggregate loss trend due to claim-size inflation only",{"$nulls":5},{"$f":"(1+N41)/(1+C10)-1","v":{"v":0.1378195709667942,"d":"13.78%"}}]

{"sheet":"21","index":23,"n_rows":46,"n_cols":10,"rows_kept":46}
[{"$f":"VALUE(MID(CELL(\"filename\",A1),FIND(\"]\",CELL(\"filename\",A1))+1,LEN(CELL(\"filename\",A1))-FIND(\"]\",CELL(\"filename\",A1))+1))","v":21},"Finished",{"$nulls":5},"Navigator",null,"Solution"]
[{"$nulls":8},"a.)","Categories A and D are assigned partial credibility because they deviate away from the GLM relativity towards their complement."]
["Points","An actuary has built a GLM and a lasso credibility model and is evaluating the",{"$nulls":7},"Category B is assigned full credibility because even though the complement and lasso credibility relativities are the same, this category has a lot of exposures."]
[{"$f":"INDEX('Point Grid'!B:B,MATCH(A1,'Point Grid'!A:A,0))","v":4},"model output for a categorical variable with five levels.",{"$nulls":7},"Category C is assigned zero credibility because the lasso credibility relativity is the same as the complement relativity and the exposure volume is low."]
[{"$nulls":9},"Category E is assigned full credibility because the GLM relativity and lasso credibility are the same and there are ample exposures."]
[null,"The complement of credibility for the lasso credibility model is the current"]
[null,"rating plan relativities which were previously produced by a GLM.",{"$nulls":7},"The result for category A is intuitive because there is a small deviation from the complement and both the complement and lasso credibility relativity indicate a surcharge."]
[{"$nulls":9},"The result for category D is not intuitive because there is a large deviation from the complement and a low exposure volume."]
["(a)","Using the table and graph below, briefly explain the broad level of credibility",{"$nulls":7},"The result for category E is intuitive because although the deviation is large, it is backed by a substantial volume of exposures."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$28,MATCH($A$1,'Point Grid'!$A$8:$A$28,0),MATCH(A9,'Point Grid'!$C$7:$I$7,0))","v":2.5},"assigned to each category by the lasso credibility model and explain"]
[2.5,"whether the results for categories A, D, and E are intuitive.",{"$nulls":6},"b.)","1.) The complement of credibility may be inappropriate. They could use penalized lasso regression or a different complement."]
[{"$nulls":9},"2.) Lasso credibility with the existing complement may be appropriate but the λ-parameter should likely be increased to remove the counter-intuitive results."]
[]
[{"$nulls":8},"c.)","When building a lasso credibility model the data should be at least as granular as the complement. This allows the model to react to credible differences."]
[{"$nulls":9},"While the model can be run as is, splitting category D into two sub-categories with the same complement may help resolve the reversal seen between the"]
[{"$nulls":9},"current and prior GLMs."]
{"$rows_empty":11}
[null,"Category","Exposures","GLM","Lasso Credibility","Complement"]
[null,"A",{"v":55000,"d":"55,000"},0.772,1.036,1.095]
[null,"B",{"v":175000,"d":"175,000"},1.952,1.881,1.881]
[null,"C",{"v":10000,"d":"10,000"},1.184,1.138,1.138]
[null,"D",{"v":48000,"d":"48,000"},0.723,0.801,1.043]
[null,"E",{"v":169000,"d":"169,000"},0.707,0.707,0.952]
[]
["(b)","Briefly describe two ways in which the actuary could address the "]
[{"$f":"INDEX('Point Grid'!$C$8:$I$28,MATCH($A$1,'Point Grid'!$A$8:$A$28,0),MATCH(A35,'Point Grid'!$C$7:$I$7,0))","v":1},"counter-intuitive results identified in part a.) above."]
[1]
[]
[null,"The actuary recently learned that the Data Enablement group who prepared the "]
[null,"data has grouped two sub-categories to form category D because "]
[null,"the prior model had shown these sub-categories were insignificant by "]
[null,"themselves. "]
[]
["(c)","Briefly explain whether or not this presents a problem when modeling"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$28,MATCH($A$1,'Point Grid'!$A$8:$A$28,0),MATCH(A44,'Point Grid'!$C$7:$I$7,0))","v":0.5},"using lasso credibility."]
[0.5]