{"text_format":"3","workbook":"xlsx","n_sheets":26,"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","22","23"],"not_rendered":{"images":1},"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":34,"n_cols":19,"rows_kept":34,"merged":[[5,1,6,1]]}
[]
[]
[]
[]
["Practice Exam 1 – 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":{"v":3.5,"d":"3.50"}},{"v":2.5,"d":"2.50"},{"v":1,"d":"1.00"},{"$nulls":5},{"$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":1,"d":"1.00"},"","","","",""]
[2,{"$f":"SUM(C9:I9)","v":{"v":2,"d":"2.00"}},{"v":2,"d":"2.00"},{"$nulls":6},{"$f":"INDIRECT(\"'\"&A9&\"'!B1\")","v":"Finished"},null,{"$f":"A9","v":2},{"v":2,"d":"2.00"},"","","","","",""]
[3,{"v":2,"d":"2.00"},{"v":2,"d":"2.00"},{"$nulls":6},"Finished",null,3,{"v":2,"d":"2.00"},"","","","","",""]
[4,{"v":1.5,"d":"1.50"},{"v":1.5,"d":"1.50"},{"$nulls":6},"Finished",null,4,{"v":1.5,"d":"1.50"},"","","","","",""]
[5,{"v":1.5,"d":"1.50"},{"v":1.5,"d":"1.50"},{"$nulls":6},"Finished",null,5,{"v":1.5,"d":"1.50"},"","","","","",""]
[6,2.75,{"v":1.5,"d":"1.50"},0.25,{"v":1,"d":"1.00"},{"$nulls":4},"Finished",null,6,{"v":1.5,"d":"1.50"},0.25,{"v":1,"d":"1.00"},"","","",""]
[7,{"v":3,"d":"3.00"},{"v":2.5,"d":"2.50"},{"v":0.5,"d":"0.50"},{"$nulls":5},"Finished",null,7,{"v":2.5,"d":"2.50"},{"v":0.5,"d":"0.50"},"","","","",""]
[8,{"v":3.5,"d":"3.50"},{"v":1.5,"d":"1.50"},{"v":2,"d":"2.00"},{"$nulls":5},"Finished",null,8,{"v":1.5,"d":"1.50"},{"v":2,"d":"2.00"},"","","","",""]
[9,{"v":4,"d":"4.00"},{"v":4,"d":"4.00"},{"$nulls":6},"Finished",null,9,{"v":4,"d":"4.00"},"","","","","",""]
[10,1.75,{"v":0.5,"d":"0.50"},0.25,0.25,0.75,{"$nulls":3},"Finished",null,10,{"v":0.5,"d":"0.50"},0.25,0.25,0.75,"","",""]
[11,2.75,{"v":0.5,"d":"0.50"},{"v":0.5,"d":"0.50"},{"v":0.5,"d":"0.50"},{"v":0.5,"d":"0.50"},0.75,null,null,"Finished",null,11,{"$f":"_xlfn.IFNA(IF(ISBLANK(INDEX(INDIRECT(\"'\"&$L18&\"'!$A:$A\"),MATCH(M$7,INDIRECT(\"'\"&$L18&\"'!$A:$A\"),0)+2)),\"\",INDEX(INDIRECT(\"'\"&$L18&\"'!$A:$A\"),MATCH(M$7,INDIRECT(\"'\"&$L18&\"'!$A:$A\"),0)+2)),\"\")","v":{"v":0.5,"d":"0.50"}},{"v":0.5,"d":"0.50"},{"v":0.5,"d":"0.50"},{"v":0.5,"d":"0.50"},0.75,"",""]
[12,{"v":3,"d":"3.00"},{"v":1,"d":"1.00"},{"v":1,"d":"1.00"},{"v":1,"d":"1.00"},{"$nulls":4},"Finished",null,12,{"v":1,"d":"1.00"},{"v":1,"d":"1.00"},{"v":1,"d":"1.00"},"","","",""]
[13,{"v":2,"d":"2.00"},{"v":0.5,"d":"0.50"},{"v":0.5,"d":"0.50"},{"v":0.5,"d":"0.50"},{"v":0.5,"d":"0.50"},{"$nulls":3},"Finished",null,13,{"v":0.5,"d":"0.50"},{"v":0.5,"d":"0.50"},{"v":0.5,"d":"0.50"},{"v":0.5,"d":"0.50"},"","",""]
[14,{"v":2,"d":"2.00"},{"v":2,"d":"2.00"},{"$nulls":6},"Finished",null,14,{"v":2,"d":"2.00"},"","","","","",""]
[15,{"v":4,"d":"4.00"},{"v":2,"d":"2.00"},{"v":2,"d":"2.00"},{"$nulls":5},"Finished",null,15,{"v":2,"d":"2.00"},{"v":2,"d":"2.00"},"","","","",""]
[16,{"v":4,"d":"4.00"},{"v":2,"d":"2.00"},{"v":2,"d":"2.00"},{"$nulls":5},"Finished",null,16,{"v":2,"d":"2.00"},{"v":2,"d":"2.00"},"","","","",""]
[17,{"v":1.5,"d":"1.50"},{"v":1.5,"d":"1.50"},{"$nulls":6},"Finished",null,17,{"v":1.5,"d":"1.50"},"","","","","",""]
[18,{"v":3,"d":"3.00"},{"v":2.5,"d":"2.50"},{"v":0.5,"d":"0.50"},{"$nulls":5},"Finished",null,18,{"v":2.5,"d":"2.50"},{"v":0.5,"d":"0.50"},"","","","",""]
[19,{"v":2.5,"d":"2.50"},{"v":1.5,"d":"1.50"},{"v":1,"d":"1.00"},{"$nulls":5},"Finished",null,19,{"v":1.5,"d":"1.50"},{"v":1,"d":"1.00"},"","","","",""]
[20,{"v":2.5,"d":"2.50"},{"v":0.5,"d":"0.50"},{"v":0.5,"d":"0.50"},0.75,0.75,{"$nulls":3},"Finished",null,20,{"v":0.5,"d":"0.50"},{"v":0.5,"d":"0.50"},0.75,0.75,"","",""]
[21,2.25,{"v":1,"d":"1.00"},{"v":1,"d":"1.00"},0.25,{"$nulls":4},"Finished",null,21,{"v":1,"d":"1.00"},{"v":1,"d":"1.00"},0.25,"","","",""]
[22,1.75,0.75,{"v":0.5,"d":"0.50"},{"v":0.5,"d":"0.50"},{"$nulls":4},"Finished",null,22,0.75,{"v":0.5,"d":"0.50"},{"v":0.5,"d":"0.50"},"","","",""]
[23,{"v":2,"d":"2.00"},{"v":0.5,"d":"0.50"},{"v":1,"d":"1.00"},{"v":0.5,"d":"0.50"},{"$nulls":4},{"$f":"INDIRECT(\"'\"&A30&\"'!B1\")","v":"Finished"},null,{"$f":"A30","v":23},{"v":0.5,"d":"0.50"},{"v":1,"d":"1.00"},{"v":0.5,"d":"0.50"},"","","",""]
[]
["Total",{"$f":"SUM(B8:B30)","v":58.75},{"$nulls":9},"TOTAL",{"$f":"IF(SUM(IF(M8:S30=\"\",0,1))=0,\"\",SUM(M8:S30))","v":58.75},null,"Percentage:",{"$f":"IF(M32=\"\",\"\",M32/B32)","v":{"v":1,"d":"100.0%"}}]
[]
[{"$nulls":11},"Passing Score:",{"$f":"MROUND(72%*B32,0.25)","v":42.25}]

{"sheet":"Navigator","index":2,"n_rows":31,"n_cols":8,"rows_kept":31,"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],[29,0,29,6],[30,0,30,6]]}
[]
[]
[]
[]
[]
["Navigator – Select a question to go to it"]
[]
["Question #",{"$nulls":6},"Status"]
["Q1 -- Bailey & Simon -- Relative Credibility -- 3.50 points",{"$nulls":6},{"$f":"'Point Grid'!J8","v":"Finished"}]
["Q2 -- GLM -- Estimate probability using GLM output -- 2.00 points",{"$nulls":6},{"$f":"'Point Grid'!J9","v":"Finished"}]
["Q3 -- GLM -- Number of Parameters within a GLM -- 2.00 points",{"$nulls":6},{"$f":"'Point Grid'!J10","v":"Finished"}]
["Q4 -- Mahler -- Criteria for Credibility -- 1.50 points",{"$nulls":6},{"$f":"'Point Grid'!J11","v":"Finished"}]
["Q5 -- Couret & Venter -- Determine best approach using SSE -- 1.50 points",{"$nulls":6},{"$f":"'Point Grid'!J12","v":"Finished"}]
["Q6 -- GLM -- Adding variables to a GLM or GLMM -- 2.75 points",{"$nulls":6},{"$f":"'Point Grid'!J13","v":"Finished"}]
["Q7 -- Fisher -- Rating Plan Equity -- 3.00 points",{"$nulls":6},{"$f":"'Point Grid'!J14","v":"Finished"}]
["Q8 -- Bahnemann -- Excess Layer Inflation -- 3.50 points",{"$nulls":6},{"$f":"'Point Grid'!J15","v":"Finished"}]
["Q9 -- ISO -- Calculate experience modification factor -- 4.00 points",{"$nulls":6},{"$f":"'Point Grid'!J16","v":"Finished"}]
["Q10 -- Fisher -- Split Modification Formula -- 1.75 points",{"$nulls":6},{"$f":"'Point Grid'!J17","v":"Finished"}]
["Q11 -- ISO -- Schedule Rating -- 2.75 points",{"$nulls":6},{"$f":"'Point Grid'!J18","v":"Finished"}]
["Q12 -- Fisher -- Credibility Functions -- 3.00 points",{"$nulls":6},{"$f":"'Point Grid'!J19","v":"Finished"}]
["Q13 -- Fisher -- Balanced Rating Plan -- 2.00 points",{"$nulls":6},{"$f":"'Point Grid'!J20","v":"Finished"}]
["Q14 -- Fisher -- Large Dollar Deductible Premium -- 2.00 points",{"$nulls":6},{"$f":"'Point Grid'!J21","v":"Finished"}]
["Q15 -- Fisher -- Table M and Large Dollar Deductible Premium -- 4.00 points",{"$nulls":6},{"$f":"'Point Grid'!J22","v":"Finished"}]
["Q16 -- Fisher -- Retrospective Premium and Savings -- 4.00 points",{"$nulls":6},{"$f":"'Point Grid'!J23","v":"Finished"}]
["Q17 -- Bahnemann -- Risk-adjusted ILFs -- 1.50 points",{"$nulls":6},{"$f":"'Point Grid'!J24","v":"Finished"}]
["Q18 -- NCCI Experience Rating-- Calculate experience mod -- 3.00 points",{"$nulls":6},{"$f":"'Point Grid'!J25","v":"Finished"}]
["Q19 -- Bahnemann -- Pricing with Straight Deductibles -- 2.50 points",{"$nulls":6},{"$f":"'Point Grid'!J26","v":"Finished"}]
["Q20 -- NCCI Circular -- Policy Excess Ratio and Expected Claims -- 2.50 points",{"$nulls":6},{"$f":"'Point Grid'!J27","v":"Finished"}]
["Q21 -- Bahnemann -- Mean Excess Claim Size -- 2.25 points",{"$nulls":6},{"$f":"'Point Grid'!J28","v":"Finished"}]
["Q22 -- GLM -- Model Refinement -- 1.75 points",{"$nulls":6},{"$f":"'Point Grid'!J29","v":"Finished"}]
["Q23 -- Holmes -- Lasso Credibility Coefficients -- 2.00 points",{"$nulls":6},{"$f":"'Point Grid'!J30","v":"Finished"}]

{"sheet":"1","index":3,"n_rows":34,"n_cols":14,"rows_kept":34}
[1,"Finished",{"$nulls":5},"Navigator",null,"Solution"]
[{"$nulls":8},"a.)","Since high frequency territories are also high average premium territories and the "]
["Points","The following information can be used to calculate the credibility assigned to the",{"$nulls":7},"territory differentials are proper, we use the premiums at present B rates as the exposure base."]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('1'!A1,'Point Grid'!A:A,0))","v":3.5},"experience of a single private passenger car."]
[{"$nulls":9},"Group B frequency",null,{"$f":"F10/E10","v":0.00036}]
[null,"Group","Last Accident","Earned Car Years","Premium at Present B Rates","Number of Claims",{"$nulls":3},"Total frequency",null,{"$f":"F11/E11","v":0.000128099173553719}]
[null,"A","3+",{"v":671000,"d":"671,000"},{"v":600000000,"d":"600,000,000"},{"v":60000,"d":"60,000"},{"$nulls":3},"Group B relative frequency",null,null,{"$f":"L5/L6","v":2.8103225806451615}]
[null,"X",2,{"v":553000,"d":"553,000"},{"v":225000000,"d":"225,000,000"},{"v":25000,"d":"25,000"}]
[null,"Y",1,{"v":120000,"d":"120,000"},{"v":93000000,"d":"93,000,000"},{"v":21000,"d":"21,000"},{"$nulls":3},"Set the experience modification equal to the Group B relative frequency."]
[null,"B",0,"M",{"v":50000000,"d":"50,000,000"},{"v":18000,"d":"18,000"}]
[null,"Total",null,{"$f":"TEXT(SUM(D7:D9),\"0,000\")&\" + M\"","v":"1,344,000 + M"},{"$f":"SUM(E7:E10)","v":{"v":968000000,"d":"968,000,000"}},{"$f":"SUM(F7:F10)","v":{"v":124000,"d":"124,000"}},{"$nulls":3},"Mod = Z*R + (1 - Z)",null,null,"Need to solve for R, we're given Z = 0.167 for group B"]
[{"$nulls":9},"R =",{"$f":"(M7-(1-0.167))/0.167","v":11.840254973923122}]
[null,"You may assume the following:"]
[null,"   • High frequency territories are also high average premium territories.",{"$nulls":7},"Now recall R = 1/ Probability of at least one claim"]
[null,"   • Territory differentials are proper.",{"$nulls":7},"Probability of at least one claim = 1 - Probability of no claims"]
[null,"   • The total claim frequency for the entire class does not change from year to year.",{"$nulls":7},"Therefore, probability of no claims =",{"$nulls":3},{"$f":"1-1/K12","v":0.9155423593324307}]
[]
["(a)","Calculate M, the earned car years for Group B, given that the credibility for an insured that has",{"$nulls":7},"Since we're told to use a Poisson distribution, P(no claims) = exp(-lambda)"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$28,MATCH($A$1,'Point Grid'!$A$8:$A$28,0),MATCH(A18,'Point Grid'!$C$7:$I$7,0))","v":2.5},"had no claim-free years is equal to 0.167.",{"$nulls":7},"Solving for lambda gives"]
[2.5,{"$nulls":8},"lambda = ",{"$f":"-LN(N16)","v":0.08823864686921287}]
[]
["(b)","Calculate the credibility for the group of risks that have been claim free for two or more years.",{"$nulls":7},"Since we have a Poisson distribution, the expected frequency is lambda."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$28,MATCH($A$1,'Point Grid'!$A$8:$A$28,0),MATCH(A22,'Point Grid'!$C$7:$I$7,0))","v":1},{"$nulls":8},"Since the total claim frequency for the entire class doesn't change from year to year we have"]
[1,{"$nulls":8},"lambda = Total number of claims / Total earned car years"]
[{"$nulls":9},"So we can solve for M"]
[{"$nulls":9},"M = ",{"$f":"F11/K20-SUM(D7:D9)","v":{"v":61279.935715611326,"d":"61,280"}}]
[]
[{"$nulls":8},"b.)","2+ years claim free frequency",null,null,{"$f":"SUM(F7:F8)/SUM(E7:E8)","v":{"v":1.0303030303030303E-4,"d":"0.00010303"}},"← Remember to continue working with your chosen exposure base"]
[{"$nulls":9},"Relative frequency",null,null,{"$f":"M28/L6","v":0.8043010752688172}]
[]
[{"$nulls":9},"Since 2+ years claims free we have R = 0."]
[{"$nulls":9},"Set the experience modification equal to the relative claim frequency."]
[{"$nulls":9},"Then mod = ZR + (1 - Z) = 1 - Z"]
[{"$nulls":9},"Z =",{"$f":"1-M29","v":{"v":0.19569892473118278,"d":"19.6%"}}]

{"sheet":"2","index":4,"n_rows":33,"n_cols":12,"rows_kept":33}
[2,"Finished",{"$nulls":5},"Navigator",null,"Solution"]
[{"$nulls":8},"a.)","The logit link function is given by:"]
["Points","You are given the following output from a GLM to estimate the probability of a claim:"]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('2'!A1,'Point Grid'!A:A,0))","v":2},"• The actuary selected a Binomial distribution.",{"$nulls":7},"Linear predictor = intercept + driver gender + vehicle body + territory"]
[null,"• The actuary selected the logit link function.",{"$nulls":7},"Intercept",{"$f":"C8","v":-1.485}]
[{"$nulls":9},"Gender",0,"← Since Female is not listed, it is the base class, so its parameter is 0.000 (no deviation needed)"]
[null,"Parameter","β",{"$nulls":6},"ln(Age)",{"$f":"C22*LN(25)","v":-0.16094379124341004},"← Remember to log the age before applying the coefficient"]
[null,"Intercept",-1.485,{"$nulls":6},"Vehicle Body",{"$f":"C13","v":-1.175}]
[{"$nulls":9},"Territory",{"$f":"C27","v":-0.101}]
[null,"Vehicle Body"]
[null,"Coupe",-0.881,{"$nulls":6},"Linear Predictor",null,{"$f":"SUM(K5:K9)","v":-2.92194379124341}]
[null,"Roadster",-1.047]
[null,"Sedan",-1.175,{"$nulls":6},"Solve for the claim probability, u, by inverting the link function"]
[null,"Station Wagon",-1.083,{"$nulls":6},"u =",{"$f":"1/(1+EXP(-L11))","v":{"v":0.05107940252207033,"d":"5.11%"}}]
[null,"Truck",-1.118]
[null,"Utility",{"v":-1.33,"d":"-1.330"}]
[]
[null,"Driver Gender"]
[null,"Male",-0.025]
[]
[null,"Driver Age"]
[null,"ln(age)",-0.05]
[]
[null,"Territory"]
[null,"B",0.094]
[null,"C",0.037]
[null,"D",-0.101]
[]
["(a)","Calculate the estimated probability of a claim for:"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$28,MATCH($A$1,'Point Grid'!$A$8:$A$28,0),MATCH(A29,'Point Grid'!$C$7:$I$7,0))","v":2},"   • Driver Gender: Female"]
[2,"   • Driver Age: 25"]
[null,"   • Vehicle Body: Sedan"]
[null,"   • Territory: D"]

{"sheet":"3","index":5,"n_rows":13,"n_cols":11,"rows_kept":13}
[3,"Finished",{"$nulls":5},"Navigator",null,"Solution"]
[{"$nulls":8},"a.)",4,"Vehicle price is a third order polynomial so has 4 variables including the intercept"]
["Points","You are given a GLM which models liability claim size using the following explanatory variables:",{"$nulls":7},1,"Average driver age is a first order polynomial (e.g. a + b*x) so has 2 variables. "]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('3'!A1,'Point Grid'!A:A,0))","v":2},{"$nulls":9},"However, we've already accounted for the intercept so only 1 new variable."]
[null,"• Vehicle price, which is a continuous variable model with a third order polynomial.",{"$nulls":7},3,"Number of drivers has 4 categories but we do not count the base level."]
[null,"• Average driver age, which is a continuous variable modeled with a first order polynomial.",{"$nulls":7},1,"Gender has 2 categories but we do not count the base level."]
[null,"• Number of drivers, which is a categorical variable with four levels.",{"$nulls":7},1,"Interaction between a two level categorical variable and a continuous variable "]
[null,"• Gender, which is a categorical variable with two levels.",{"$nulls":8},"adds a single variable."]
[null,"• There is only one interaction in the model, which is between gender and average driver age."]
[{"$nulls":9},{"$f":"SUM(J2:J7)","v":10},"Maximum number of β parameters"]
["(a)","Determine the maximum number of β parameters in this model.",{"$nulls":8},"(The actual number of parameters could be less if some of these are zero.)"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$28,MATCH($A$1,'Point Grid'!$A$8:$A$28,0),MATCH(A11,'Point Grid'!$C$7:$I$7,0))","v":2}]
[2]

{"sheet":"4","index":6,"n_rows":31,"n_cols":10,"rows_kept":31}
[4,"Finished",{"$nulls":4},"Navigator",null,"Solution"]
[{"$nulls":7},"a.)","Meyers/Dorweiler criterion does not penalize for the size of the error, instead it penalizes if there "]
["Points","Three experience rating plans have been developed and you are trying to evaluate",{"$nulls":6},"is a pattern to the errors."]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('4'!A1,'Point Grid'!A:A,0))","v":1.5},"which is optimal.  Each rating plan has been tested on four different risks.",{"$nulls":6},"Plan 3 has a downward trend in the error percentages so is not preferred by this measure."]
[]
[null,"The following tables summarize the indicated modifications and the resulting errors.",{"$nulls":6},"Compute the correlation between the predicted modification factors and the errors."]
[{"$nulls":8},"Plan ","Correlation"]
[null,"Plan 1",{"$nulls":6},1,{"$f":"CORREL(C10:C13,D10:D13)","v":{"v":0.9434563530497265,"d":"0.943"}}]
[null,"Risk #","Predicted Modification Factor","Error",{"$nulls":4},2,{"$f":"CORREL(C17:C20,D17:D20)","v":0}]
[null,1,1.2,{"v":0.5,"d":"50%"},{"$nulls":4},3,{"$f":"CORREL(C24:C27,D24:D27)","v":0.9594220862834358}]
[null,2,1.2,{"v":0.45,"d":"45%"}]
[null,3,0.7,{"v":0.2,"d":"20%"},{"$nulls":4},"Since plans 1 and 3 have high correlation and plan 2 has no correlation, using the Meyers/Dorweiler"]
[null,4,0.7,{"v":0.3,"d":"30%"},{"$nulls":4},"criterion we prefer plan 2."]
[]
[null,"Plan 2",{"$nulls":6},"Plan","MSE"]
[null,"Risk #","Predicted Modification Factor","Error",{"$nulls":4},1,{"$f":"SUMSQ(D10:D13)/COUNT(D10:D13)","v":{"v":0.145625,"d":"14.563%"}}]
[null,5,1.2,{"v":0.08,"d":"8%"},{"$nulls":4},2,{"$f":"SUMSQ(D17:D20)/COUNT(D17:D20)","v":{"v":0.0274,"d":"2.740%"}}]
[null,6,1.2,{"v":-0.08,"d":"-8%"},{"$nulls":4},3,{"$f":"SUMSQ(D24:D27)/COUNT(D24:D27)","v":{"v":9.7500000000000017E-4,"d":"0.098%"}}]
[null,7,0.6,{"v":-0.22,"d":"-22%"}]
[null,8,0.6,{"v":0.22,"d":"22%"},{"$nulls":4},"Since plan 3 has the lowest mean squared error, this is the preferred plan under the MSE criterion."]
[]
[null,"Plan 3"]
[null,"Risk #","Predicted Modification Factor","Error"]
[null,9,1.4,{"v":0.03,"d":"3%"}]
[null,10,1.3,{"v":0.01,"d":"1%"}]
[null,11,0.75,{"v":-0.02,"d":"-2%"}]
[null,12,0.65,{"v":-0.05,"d":"-5%"}]
[]
["(a)","Which is the preferred plan based on the Meyers/Dorweiler criterion?"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$28,MATCH($A$1,'Point Grid'!$A$8:$A$28,0),MATCH(A29,'Point Grid'!$C$7:$I$7,0))","v":1.5},"Which is the preferred plan based on the mean squared error criterion?"]
[1.5,"Explain your answer to each part above."]

{"sheet":"5","index":7,"n_rows":33,"n_cols":12,"rows_kept":33}
[5,"Finished",{"$nulls":4},"Navigator",null,"Solution"]
[{"$nulls":7},"a.)","Since the sample means are normalized within the hazard group, the total hazard group "]
["Points","A multi-dimensional credibility technique has been developed to predict claim frequencies",{"$nulls":6},"relativity is 1.000."]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('5'!A1,'Point Grid'!A:A,0))","v":1.5},"for permanent total (PT) claims."]
[null,"   • Seven years of data were collected.",{"$nulls":6},"Hazard group approach"]
[null,"   • The technique produced a raw predicted relativity based on the oldest five years.",{"$nulls":6},"Quintile","HG","Holdout","(HG - Holdout)^2"]
[null,"   • The most recent two years were used as the holdout sample.",{"$nulls":6},1,1,{"$f":"C11","v":0.55},{"$f":"(J7-K7)^2","v":0.20249999999999996}]
[null,"   • The sample means are normalized within a hazard group.",{"$nulls":6},2,1,{"$f":"C12","v":0.9},{"$f":"(J8-K8)^2","v":9.999999999999995E-3}]
[{"$nulls":8},3,1,1.1,0.010000000000000018]
[null,"Quintile","Holdout Sample Relativity","Prediction Based on Raw","Prediction Based on Credibility Procedure",{"$nulls":3},4,1,1.25,0.0625]
[null,1,0.55,0.4,0.45,{"$nulls":3},5,1,1.5,0.25]
[null,2,0.9,0.6,0.7,{"$nulls":3},"Total hazard group SSE",null,null,{"$f":"SUM(L7:L11)","v":{"v":0.5349999999999999,"d":"0.54"}}]
[null,3,1.1,1.05,1.15]
[null,4,1.25,1.5,1.3,{"$nulls":3},"Raw approach"]
[null,5,1.5,2.5,1.9,{"$nulls":3},"Quintile","Raw","Holdout","(HG - Holdout)^2"]
[{"$nulls":8},1,{"$f":"D11","v":0.4},{"$f":"C11","v":0.55},{"$f":"(J16-K16)^2","v":0.022500000000000006}]
["(a)","Using the sum of squared error, demonstrate whether the multi-dimensional credibility ",{"$nulls":6},2,{"$f":"D12","v":0.6},{"$f":"C12","v":0.9},{"$f":"(J17-K17)^2","v":0.09000000000000002}]
[{"$f":"INDEX('Point Grid'!$C$8:$I$28,MATCH($A$1,'Point Grid'!$A$8:$A$28,0),MATCH(A17,'Point Grid'!$C$7:$I$7,0))","v":1.5},"technique produces an improved estimate over the estimates produced using the raw relativities",{"$nulls":6},3,1.05,1.1,2.5000000000000044E-3]
[1.5,"and the total hazard group relativities.",{"$nulls":6},4,1.5,1.25,0.0625]
[{"$nulls":8},5,2.5,1.5,1]
[{"$nulls":8},"Raw SSE",null,null,{"$f":"SUM(L16:L20)","v":1.1775}]
[]
[{"$nulls":8},"Multi-dimensional approach"]
[{"$nulls":8},"Quintile","Multi-dim.","Holdout","(HG - Holdout)^2"]
[{"$nulls":8},1,{"$f":"E11","v":0.45},{"$f":"C11","v":0.55},{"$f":"(J25-K25)^2","v":0.010000000000000007}]
[{"$nulls":8},2,{"$f":"E12","v":0.7},{"$f":"C12","v":0.9},{"$f":"(J26-K26)^2","v":0.04000000000000003}]
[{"$nulls":8},3,1.15,1.1,2.4999999999999823E-3]
[{"$nulls":8},4,1.3,1.25,2.5000000000000044E-3]
[{"$nulls":8},5,1.9,1.5,0.15999999999999992]
[{"$nulls":8},"Multi-dimensional SSE",null,null,{"$f":"SUM(L25:L29)","v":{"v":0.21499999999999994,"d":"0.22"}}]
[]
[{"$nulls":8},"Since the multi-dimensional approach has the lowest SSE it is an improvement over the"]
[{"$nulls":8},"hazard group membership method and the raw method."]

{"sheet":"6","index":8,"n_rows":27,"n_cols":12,"rows_kept":27}
[6,"Finished",{"$nulls":6},"Navigator",null,"Solution"]
[{"$nulls":9},"a.)","AIC = -2*log-likelihood + 2p"]
["Points","You are testing the addition of a new categorical variable into an existing GLM, and are",{"$nulls":8},"BIC = -2*log-likelihood + p*ln(n)"]
[null,"given the following information:"]
[{"$nulls":10},"If there are p parameters in the old model then the new model has p+q parameters once the new variable is added."]
[null,"• A is the change in AIC and B is the change in BIC after adding the new variable."]
[null,"• B > A + 100",{"$nulls":8},"A = Change in AIC = AIC_new - AIC_old = (-2*log-likelihood_new + 2(p+q)) - (-2*log-likelihood_old + 2p) = -2*(ll_new - ll_old) + 2q"]
[]
[null,{"v":5000,"d":"5,000"},"Number of observations in the model",{"$nulls":7},"B = Change in BIC = BIC_new - BIC_old = -2*(ll_new - ll_old) +q*ln(n)"]
[]
["(a)","Calculate the minimum possible number of levels in the new categorical variable.",{"$nulls":8},"B - A > 100 implies q*ln(n) - 2q > 100"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$28,MATCH($A$1,'Point Grid'!$A$8:$A$28,0),MATCH(A11,'Point Grid'!$C$7:$I$7,0))","v":1.5},{"$nulls":9},"ln(n) =",{"$f":"LN(B9)","v":8.517193191416238}]
[1.5]
[{"$nulls":10},"so q > ",{"$f":"100/(L12-2)","v":15.344028796278357}]
[null,"Suppose instead the new categorical is added as a random effect, turning the GLM into a"]
[null,"GLMM.",{"$nulls":8},"This means q is at least 16."]
["(b)","Identify the minimum possible number of parameters added to the model when the new",{"$nulls":8},"Since the base category doesn't get its own parameter in a GLM there must be a minimum of 17 levels."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$28,MATCH($A$1,'Point Grid'!$A$8:$A$28,0),MATCH(A17,'Point Grid'!$C$7:$I$7,0))","v":0.25},"categorical variable is introduced."]
[0.25,{"$nulls":8},"b.)","Random effects in a GLMM do not include a base level. Each of the levels gets its own parameter for a minimum of 17 parameters added."]
[]
[null,"A colleague suggests the GLM approach is better than the GLMM approach because the",{"$nulls":7},"c.)","The GLM approach assumes the data is fully credible. The new categorical variable has at least 17 levels and we only have 5,000 observations"]
[null,"GLM coefficients are greater in magnitude than those produced by the GLMM for the",{"$nulls":8},"in the model. This means the data for any level is unlikely to be highly credible."]
[null,"new categorical variable. ",{"$nulls":8},"Using a GLMM is a way of introducing classical credibility into a GLM for multi-level categorical variables. As the data for a level"]
[{"$nulls":10},"becomes more credible, the associated GLMM coefficient moves away from the grand mean and approaches "]
["(c)","Briefly describe an issue presented by the GLM approach and explain how the ",{"$nulls":8},"the standard GLM coefficient. This reduces the chance of reacting to noise."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$28,MATCH($A$1,'Point Grid'!$A$8:$A$28,0),MATCH(A25,'Point Grid'!$C$7:$I$7,0))","v":1},"GLMM approach is an improvement."]
[1]

{"sheet":"7","index":9,"n_rows":33,"n_cols":19,"rows_kept":33}
[7,"Finished",{"$nulls":5},"Navigator",null,"Solution"]
[{"$nulls":8},"a.)","We need to compute the manual and standard loss ratios for each insured across each rating plan."]
["Points","The following information was taken from two different experience rating plans:",{"$nulls":7},"Make sure to order the risks by increasing experience modification. "]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('7'!A1,'Point Grid'!A:A,0))","v":3}]
[null,null,"Experience Modification",null,"Experience Data",{"$nulls":4},"Plan A"]
[null,"Insured","Plan A","Plan B","Manual Premium","Losses",{"$nulls":3},"Insured","Mod Rank",null,"Rank","Insured","Ranked Mod","Manual Premium","Losses","Manual LR","Standard LR"]
[null,1,0.8,1.03,{"v":75000,"d":"75,000"},{"v":60000,"d":"60,000"},{"$nulls":3},1,{"$f":"_xlfn.RANK.AVG(C7,$C$7:$C$11,1)","v":1},null,1,{"$f":"INDEX($J$7:$J$11,MATCH(M7,$K$7:$K$11,0))","v":1},{"$f":"INDEX($C$7:$C$11,MATCH(M7,$K$7:$K$11,0))","v":0.8},{"$f":"INDEX($E$7:$E$11,MATCH(M7,$K$7:$K$11,0))","v":{"v":75000,"d":"75,000"}},{"$f":"INDEX($F$7:$F$11,MATCH(M7,$K$7:$K$11,0))","v":{"v":60000,"d":"60,000"}},{"$f":"Q7/P7","v":{"v":0.8,"d":"80.0%"}},{"$f":"Q7/(P7*O7)","v":{"v":1,"d":"100.0%"}}]
[null,2,0.85,0.69,{"v":75000,"d":"75,000"},{"v":58000,"d":"58,000"},{"$nulls":3},2,{"$f":"_xlfn.RANK.AVG(C8,$C$7:$C$11,1)","v":2},null,2,{"$f":"INDEX($J$7:$J$11,MATCH(M8,$K$7:$K$11,0))","v":2},{"$f":"INDEX($C$7:$C$11,MATCH(M8,$K$7:$K$11,0))","v":0.85},{"$f":"INDEX($E$7:$E$11,MATCH(M8,$K$7:$K$11,0))","v":{"v":75000,"d":"75,000"}},{"$f":"INDEX($F$7:$F$11,MATCH(M8,$K$7:$K$11,0))","v":{"v":58000,"d":"58,000"}},{"$f":"Q8/P8","v":{"v":0.7733333333333333,"d":"77.3%"}},{"$f":"Q8/(P8*O8)","v":{"v":0.9098039215686274,"d":"91.0%"}}]
[null,3,{"v":0.9,"d":"0.90"},0.75,{"v":75000,"d":"75,000"},{"v":61000,"d":"61,000"},{"$nulls":3},3,3,null,3,3,0.9,{"v":75000,"d":"75,000"},{"v":61000,"d":"61,000"},{"v":0.8133333333333334,"d":"81.3%"},{"v":0.9037037037037037,"d":"90.4%"}]
[null,4,0.91,0.89,{"v":125000,"d":"125,000"},{"v":104000,"d":"104,000"},{"$nulls":3},4,4,null,4,4,0.91,{"v":125000,"d":"125,000"},{"v":104000,"d":"104,000"},{"v":0.832,"d":"83.2%"},{"v":0.9142857142857143,"d":"91.4%"}]
[null,5,0.96,0.87,{"v":125000,"d":"125,000"},{"v":116000,"d":"116,000"},{"$nulls":3},5,5,null,5,5,0.96,{"v":125000,"d":"125,000"},{"v":116000,"d":"116,000"},{"v":0.928,"d":"92.8%"},{"v":0.9666666666666667,"d":"96.7%"}]
[]
["(a)","Demonstrate whether Plan A or Plan B appears to be more equitable.",{"$nulls":7},"Plan B"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$28,MATCH($A$1,'Point Grid'!$A$8:$A$28,0),MATCH(A13,'Point Grid'!$C$7:$I$7,0))","v":2.5},{"$nulls":8},"Insured","Mod Rank",null,"Rank","Insured","Ranked Mod","Manual Premium","Losses","Manual LR","Standard LR"]
[2.5,{"$nulls":8},1,{"$f":"_xlfn.RANK.AVG(D7,$D$7:$D$11,1)","v":5},null,1,{"$f":"INDEX($J$15:$J$19,MATCH(M15,$K$15:$K$19,0))","v":2},{"$f":"INDEX($D$7:$D$11,MATCH(M15,$K$15:$K$19,0))","v":0.69},{"$f":"INDEX($E$7:$E$11,MATCH(M15,$K$15:$K$19,0))","v":{"v":75000,"d":"75,000"}},{"$f":"INDEX($F$7:$F$11,MATCH(M15,$K$15:$K$19,0))","v":{"v":58000,"d":"58,000"}},{"$f":"Q15/P15","v":{"v":0.7733333333333333,"d":"77.3%"}},{"$f":"Q15/(P15*O15)","v":{"v":1.1207729468599035,"d":"112.1%"}}]
[{"$nulls":9},2,{"$f":"_xlfn.RANK.AVG(D8,$D$7:$D$11,1)","v":1},null,2,{"$f":"INDEX($J$15:$J$19,MATCH(M16,$K$15:$K$19,0))","v":3},{"$f":"INDEX($D$7:$D$11,MATCH(M16,$K$15:$K$19,0))","v":0.75},{"$f":"INDEX($E$7:$E$11,MATCH(M16,$K$15:$K$19,0))","v":{"v":75000,"d":"75,000"}},{"$f":"INDEX($F$7:$F$11,MATCH(M16,$K$15:$K$19,0))","v":{"v":61000,"d":"61,000"}},{"$f":"Q16/P16","v":{"v":0.8133333333333334,"d":"81.3%"}},{"$f":"Q16/(P16*O16)","v":{"v":1.0844444444444445,"d":"108.4%"}}]
["(b)","Demonstrate whether Plan A or Plan B gives too much credit for good experience.",{"$nulls":7},3,2,null,3,5,0.87,{"v":125000,"d":"125,000"},{"v":116000,"d":"116,000"},{"v":0.928,"d":"92.8%"},{"v":1.0666666666666667,"d":"106.7%"}]
[{"$f":"INDEX('Point Grid'!$C$8:$I$28,MATCH($A$1,'Point Grid'!$A$8:$A$28,0),MATCH(A17,'Point Grid'!$C$7:$I$7,0))","v":0.5},{"$nulls":8},4,4,null,4,4,0.89,{"v":125000,"d":"125,000"},{"v":104000,"d":"104,000"},{"v":0.832,"d":"83.2%"},{"v":0.9348314606741573,"d":"93.5%"}]
[0.5,{"$nulls":8},5,3,null,5,1,1.03,{"v":75000,"d":"75,000"},{"v":60000,"d":"60,000"},{"v":0.8,"d":"80.0%"},{"v":0.7766990291262136,"d":"77.7%"}]
[]
[{"$nulls":9},"Manual premium dispersion",null,null,{"$f":"MAX(R7:R11)-MIN(R7:R11)","v":{"v":0.15466666666666673,"d":"15.5%"}}]
[{"$nulls":9},"Both plans produce the same manual premiums so are equally good at identifying differences between risks."]
[{"$nulls":9},"Alice: \"If the two plans produced different manual premiums then you should comment on how well the plans identify differences"]
[{"$nulls":9},"between risks.\""]
[]
[{"$nulls":9},"Plan A standard loss ratio dispersion",{"$nulls":3},{"$f":"MAX(S7:S11)-MIN(S7:S11)","v":{"v":0.09629629629629632,"d":"9.6%"}}]
[{"$nulls":9},"Plan B standard loss ratio dispersion",{"$nulls":3},{"$f":"MAX(S15:S19)-MIN(S15:S19)","v":{"v":0.3440739177336899,"d":"34.4%"}}]
[]
[{"$nulls":9},"Plan A has a much smaller range of standard loss ratios than Plan B. Further, Plan A has no discernable pattern to its standard loss ratios"]
[{"$nulls":9},"whereas Plan B has a clear downward trend. This means Plan A is more equitable."]
[]
[{"$nulls":8},"b.)","Plan B gives too much credit for good experience because for insureds with the lowest mods have the highest standard loss ratios and as"]
[{"$nulls":9},"the mods increase the standard loss ratios decline."]

{"sheet":"8","index":10,"n_rows":36,"n_cols":10,"rows_kept":36}
[8,"Finished",null,null,"Navigator",null,"Solution"]
[{"$nulls":5},"a.)","First, detrend the large loss threshold for two years using the annual claim severity trend."]
["Points","A portfolio of insurance policies currently has the size of loss distribution shown below:",{"$nulls":4},{"$f":"B22/((1+B27)^2)","v":{"v":200012.16073937298,"d":"$200,012.16"}}]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('8'!A1,'Point Grid'!A:A,0))","v":3.5},"Occurrence Limit","Probability that a Claim is less than the Occurrence Limit","Expected Limited Severity for the Occurrence Limit",null,null,"We'll round this to $200,000 to use the table."]
[null,{"v":100000,"d":"100,000"},{"v":0.673,"d":"67.3%"},{"v":48000,"d":"$48,000"},null,null,"This means claims today that are over $200,000 are claims which we expect to be over $250,000 if they occur in "]
[null,{"v":125000,"d":"125,000"},{"v":0.689,"d":"68.9%"},{"v":59000,"d":"$59,000"},null,null,"two year's time based on inflationary estimates."]
[null,{"v":150000,"d":"150,000"},{"v":0.712,"d":"71.2%"},{"v":63500,"d":"$63,500"}]
[null,{"v":175000,"d":"175,000"},{"v":0.712,"d":"71.2%"},{"v":68000,"d":"$68,000"},null,null,"Next, compute the percent of claims today in excess of the detrended limit."]
[null,{"v":200000,"d":"200,000"},{"v":0.818,"d":"81.8%"},{"v":73500,"d":"$73,500"},null,null,"P(X > $200,000) =",null,{"$f":"1-C9","v":{"v":0.18200000000000005,"d":"18.2%"}}]
[null,{"v":225000,"d":"225,000"},{"v":0.819,"d":"81.9%"},{"v":90500,"d":"$90,500"}]
[null,{"v":250000,"d":"250,000"},{"v":0.864,"d":"86.4%"},{"v":120000,"d":"$120,000"},null,null,"Then, without accounting for exposure growth, (i.e. due to inflation alone) we expect an increase in large loss "]
[null,{"v":300000,"d":"300,000"},{"v":0.884,"d":"88.4%"},{"v":123000,"d":"$123,000"},null,null,"claims after two years of:"]
[null,{"v":400000,"d":"400,000"},{"v":0.895,"d":"89.5%"},{"v":124000,"d":"$124,000"},null,null,{"$f":"I9/(1-C11)-1","v":{"v":0.3382352941176474,"d":"33.8%"}}]
[null,{"v":500000,"d":"500,000"},{"v":0.903,"d":"90.3%"},{"v":130500,"d":"$130,500"}]
[null,{"v":750000,"d":"750,000"},{"v":0.903,"d":"90.3%"},{"v":135000,"d":"$135,000"},null,null,"Lastly, account for the anticipated exposure growth and apply to the number of large loss claims today."]
[null,{"v":800000,"d":"800,000"},{"v":0.93,"d":"93.0%"},{"v":140500,"d":"$140,500"},null,null,{"$f":"B23*(1+B28)^2*(1+G13)","v":{"v":96.35294117647061,"d":"96.4"}}]
[null,{"v":900000,"d":"900,000"},{"v":0.957,"d":"95.7%"},{"v":153000,"d":"$153,000"}]
[null,{"v":1000000,"d":"1,000,000"},{"v":0.972,"d":"97.2%"},{"v":159500,"d":"$159,500"},null,"b.)","Assume E[S] = E[N]*E[X]"]
[null,{"v":1250000,"d":"1,250,000"},{"v":0.974,"d":"97.4%"},{"v":178000,"d":"$178,000"},null,null,"E[N] =",{"$f":"G16","v":{"v":96.35294117647061,"d":"96.4"}}]
[null,{"v":1500000,"d":"1,500,000"},{"v":0.979,"d":"97.9%"},{"v":186000,"d":"$186,000"}]
[{"$nulls":7},"In 2 years","Today","Use"]
[null,{"v":250000,"d":"$250,000"},"\"Large Loss\" threshold for the insurer today",{"$nulls":3},"Upper limit",{"$f":"10^6+250000","v":{"v":1250000,"d":"$1,250,000"}},{"$f":"H22/((1+B27)^2)","v":{"v":1000060.8036968649,"d":"$1,000,061"}},{"v":1000000,"d":"$1,000,000"}]
[null,50,"It's estimated the portfolio policies will generate this many ",{"$nulls":3},"Lower limit",{"$f":"B22","v":{"v":250000,"d":"$250,000"}},{"$f":"H23/((1+B27)^2)","v":{"v":200012.16073937298,"d":"$200,012"}},{"v":200000,"d":"$200,000"}]
[null,null,"\"large losses\" during the next two years."]
[{"$nulls":6},"Excess layer severity today:",null,{"$f":"(D18-D9)/(1-C9)","v":{"v":472527.4725274724,"d":"$472,527.47"}},"Remember - claims that go completely through the layer still contribute"]
[null,"The actuary estimates the following:",{"$nulls":7},"to the layer. Hence 1 - P(X > $200,000) in the denominator."]
[null,{"v":0.118,"d":"11.8%"},"Annual claim severity inflationary trend",{"$nulls":3},"Apply annual claim severity trend:",null,{"$f":"I25*(1+B27)^2","v":{"v":590623.4285714284,"d":"$590,623.43"}}]
[null,{"v":0.2,"d":"20%"},"Expected annual exposure growth",{"$nulls":3},"This is E[X]"]
[]
["(a)","Calculate the expected number of claims that exceed the large loss threshold in two years time.",{"$nulls":4},"Hence, E[S] ="]
[{"$f":"INDEX('Point Grid'!$C$8:$I$28,MATCH($A$1,'Point Grid'!$A$8:$A$28,0),MATCH(A30,'Point Grid'!$C$7:$I$7,0))","v":1.5},{"$nulls":5},{"$f":"G16*I27","v":{"v":56908304.47058823,"d":"$56,908,304.47"}}]
[1.5]
[]
["(b)","Calculate the expected losses for this portfolio in the layer $1,000,000 excess of $250,000 after "]
[{"$f":"INDEX('Point Grid'!$C$8:$I$28,MATCH($A$1,'Point Grid'!$A$8:$A$28,0),MATCH(A34,'Point Grid'!$C$7:$I$7,0))","v":2},"two years."]
[2]

{"sheet":"9","index":11,"n_rows":54,"n_cols":21,"rows_kept":54,"merged":[[0,7,0,8]]}
[9,"Finished",{"$nulls":5},"Navigator",null,null,"Solution"]
[{"$nulls":9},"a.)","Since the company has been with the same insurer on a claims-made program for the last 15 years"]
["Points","Consider the following loss experience as of June 30, 2021 under a claims-made commercial general",{"$nulls":8},"we may assume all data given is for mature claims-made policies."]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('9'!A1,'Point Grid'!A:A,0))","v":4},"liability policy:"]
[{"$nulls":10},"The experience period for the policy we're pricing is the three years prior, lagged by one. So 1/1/2018 – 12/31/2020."]
[null,"Policy Year","Claim Number","Basic Limit Indemnity","ALAE",{"$nulls":5},"So claims 1 and 2 are not relevant."]
[null,2016,1,{"v":25000,"d":"$25,000"},{"v":1000,"d":"$1,000"}]
[null,2017,2,{"v":8500,"d":"$8,500"},{"v":0,"d":"$0"},{"$nulls":5},"First, we need to find the Company Subject Loss Cost (CSLC)."]
[null,2018,3,{"v":100000,"d":"$100,000"},{"v":55000,"d":"$55,000"},{"$nulls":5},"Since there's no information about shifts in exposure we assume we may use the standard approach for the CSLC"]
[null,2018,4,{"v":55000,"d":"$55,000"},{"v":5000,"d":"$5,000"},{"$nulls":5},"Year","Type","BLEL","PAF 13B","PAF 13C","DeTrend","Total","EER","LDF","Total"]
[null,2020,5,{"v":0,"d":"$0"},{"v":150000,"d":"$150,000"},{"$nulls":5},2018,"Prem/Ops",{"$f":"$B$18*$B$20","v":{"v":68000,"d":"$68,000"}},{"$f":"$D$28","v":1.05},{"$f":"$D$34","v":0.94},{"$f":"D44","v":0.839},{"$f":"PRODUCT(M11:P11)","v":{"v":56310.324,"d":"56,310"}},{"$f":"$L$24","v":0.917},0,{"$f":"PRODUCT(Q11:S11)","v":0}]
[null,2020,6,{"v":32000,"d":"$32,000"},{"v":800,"d":"$800"},{"$nulls":5},2018,"Products",{"$f":"$B$19*$B$20","v":{"v":17000,"d":"$17,000"}},{"$f":"$D$29","v":1.24},{"$f":"$D$35","v":0.72},{"$f":"D45","v":0.777},{"$f":"PRODUCT(M12:P12)","v":{"v":11792.9952,"d":"11,793"}},{"$f":"$L$24","v":0.917},0,{"$f":"PRODUCT(Q12:S12)","v":0}]
[{"$nulls":10},2019,"Prem/Ops",{"$f":"$B$18*$B$20","v":{"v":68000,"d":"$68,000"}},{"$f":"$D$28","v":1.05},{"$f":"$D$34","v":0.94},{"$f":"D42","v":0.876},{"v":58793.616,"d":"58,794"},0.917,0,0]
[null,"The policy being rated has the following characteristics:",{"$nulls":8},2019,"Products",{"$f":"$B$19*$B$20","v":{"v":17000,"d":"$17,000"}},{"$f":"$D$29","v":1.24},{"$f":"$D$35","v":0.72},{"$f":"D43","v":0.828},{"v":12567.052799999998,"d":"12,567"},0.917,0,0]
[null,"   • The policy always runs from January 1st through December 31st each year.",{"$nulls":8},2020,"Prem/Ops",{"$f":"$B$18*$B$20","v":{"v":68000,"d":"$68,000"}},{"$f":"$D$28","v":1.05},{"$f":"$D$34","v":0.94},{"$f":"D40","v":0.916},{"v":61478.256,"d":"61,478"},0.917,0,0]
[null,"   • The company has been with the same insurer in a claims-made program for over 15 years.",{"$nulls":8},2020,"Products",{"$f":"$B$19*$B$20","v":{"v":17000,"d":"$17,000"}},{"$f":"$D$29","v":1.24},{"$f":"$D$35","v":0.72},{"$f":"D41","v":0.882},{"v":13386.643199999999,"d":"13,387"},0.917,0,0]
[null,"   • The company's annual basic limit premium is:",{"$nulls":13},"CSLC →",{"$f":"SUM(Q11:Q16)","v":{"v":214328.8872,"d":"214,329"}},null,null,{"$f":"SUM(T11:T16)","v":0},"← Expected Future Development"]
[null,{"v":100000,"d":"$100,000"},"Premises/Operations",{"$nulls":7},"BLEL = annual basic limit premium * ELR"]
[null,{"v":25000,"d":"$25,000"},"Products/Completed Operations",{"$nulls":7},"PAF 13b takes the prospective policy type to a per-occurrence policy"]
[null,{"v":0.68,"d":"68%"},"Expected Loss Ratio",{"$nulls":7},"PAF 13c takes the per-occurrence policy to the historical policy."]
[]
["(a)","Calculate the experience modification factor for the policy effective between January 1 and ",{"$nulls":8},"Now look up the CSLC to get"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$28,MATCH($A$1,'Point Grid'!$A$8:$A$28,0),MATCH(A22,'Point Grid'!$C$7:$I$7,0))","v":4},"December 31, 2022.",{"$nulls":8},"Z","EER","MSL"]
[4,{"$nulls":9},{"$f":"C51","v":0.38},{"$f":"D51","v":0.917},{"$f":"E51","v":{"v":153400,"d":"153,400"}}]
[null,"You may use the following information below:"]
[null,null,"Occurrence","Claims-Made Year in Program (YIP)",{"$nulls":6},"Since we have a claims made policy the LDFs are all 0."]
[null,"Subline","Policy","Mature","Fourth","Third","Second","First",null,null,"Apply the EER and LDFs to the Totals used for the CSLC to get the ARULL"]
[null,"Prem/Ops",{"v":1,"d":"1.00"},1.05,1.14,{"v":1.2,"d":"1.20"},1.32,1.62]
[null,"Products",{"v":1,"d":"1.00"},1.24,1.51,1.59,2.03,2.39,null,null,"Now cap the basic limit indemnity losses, then add the ALAE and cap at "]
[null,"Table 13.B",{"$nulls":8},"the maximum single loss. The basic cap is $100,000 per the manual."]
[]
[null,null,"Occurrence","Claims-Made Year in Program (YIP)",{"$nulls":7},"Cap at","Add","Cap at"]
[null,"Subline","Policy","Mature","Fourth","Third","Second","First",null,null,"Claim #","Basic Limit","ALAE","MSL"]
[null,"Prem/Ops",{"v":1,"d":"1.00"},0.94,0.84,0.78,0.67,0.47,null,null,1,"NA","NA","NA"]
[null,"Products",{"v":1,"d":"1.00"},0.72,0.49,0.43,0.35,0.22,null,null,2,"NA","NA","NA"]
[null,"Table 13.C",{"$nulls":8},3,{"$f":"MIN(100000,D9)","v":{"v":100000,"d":"$100,000"}},{"$f":"L36+E9","v":{"v":155000,"d":"$155,000"}},{"$f":"MIN(M36,$M$24)","v":{"v":153400,"d":"$153,400"}}]
[{"$nulls":10},4,{"$f":"MIN(100000,D10)","v":{"v":55000,"d":"$55,000"}},{"$f":"L37+E10","v":{"v":60000,"d":"$60,000"}},{"$f":"MIN(M37,$M$24)","v":{"v":60000,"d":"$60,000"}}]
[null,"Experience ",null,"Factors for",{"$nulls":6},5,{"$f":"MIN(100000,D11)","v":{"v":0,"d":"$0"}},{"$f":"L38+E11","v":{"v":150000,"d":"$150,000"}},{"$f":"MIN(M38,$M$24)","v":{"v":150000,"d":"$150,000"}}]
[null,"Period Year","Subline","Rule 5.B","Rule 5.C",{"$nulls":5},6,{"$f":"MIN(100000,D12)","v":{"v":32000,"d":"$32,000"}},{"$f":"L39+E12","v":{"v":32800,"d":"$32,800"}},{"$f":"MIN(M39,$M$24)","v":{"v":32800,"d":"$32,800"}}]
[null,"Latest","Prem/Ops",0.916,0.949]
[null,null,"Products",0.882,0.914,{"$nulls":5},"Ratable Loss",null,null,{"$f":"SUM(N36:N39)","v":{"v":396200,"d":"$396,200"}}]
[null,"2nd Latest","Prem/Ops",0.876,0.919]
[null,null,"Products",0.828,0.869,{"$nulls":5},"AER = (Ratable Loss + Expected Future Development) / CSLC "]
[null,"3rd Latest","Prem/Ops",0.839,0.887,{"$nulls":5},{"$f":"(T17+N41)/Q17","v":{"v":1.8485608971145724,"d":"1.85"}}]
[null,null,"Products",0.777,0.821]
[null,"Table 14",{"$nulls":8},"Experience Modification = (AER - EER) / EER * Credibility"]
[{"$nulls":10},"Mod =",{"$f":"(K44-L24)/L24*K24","v":{"v":0.3860339595458424,"d":"0.39"}}]
[null,"CSLC","Credibility","EER","MSL"]
[null,"194,380 - 203,002",0.36,0.911,{"v":147550,"d":"147,550"},{"$nulls":5},"Experience Modification Factor"]
[null,"203,003 - 211,901",0.37,0.914,{"v":150450,"d":"150,450"},{"$nulls":5},{"$f":"1+L47","v":{"v":1.3860339595458424,"d":"1.39"}}]
[null,"211,902 - 221,089",0.38,0.917,{"v":153400,"d":"153,400"}]
[null,"221,090 - 230,581",0.39,{"v":0.92,"d":"0.920"},{"v":156450,"d":"156,450"}]
[null,"230,582 - 240,392",{"v":0.4,"d":"0.40"},0.923,{"v":159600,"d":"159,600"}]
[null,"Table 16"]

{"sheet":"10","index":12,"n_rows":20,"n_cols":8,"rows_kept":20}
[10,"Finished",{"$nulls":3},"Navigator",null,"Solution"]
[{"$nulls":6},"a.)","Workers' Compensation losses are generally frequency driven (Med Only claims) with the occasional low frequency, high severity loss."]
["Points","Answer the following in the context of the NCCI experience rating formula.",{"$nulls":5},"Splitting into primary and excess components allows the plan to respond to both frequency and severity."]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('10'!A1,'Point Grid'!A:A,0))","v":1.75},{"$nulls":6},"This improves the accuracy of the expected losses."]
[]
["(a)","Explain why splitting the formula into primary and excess components is",{"$nulls":4},"b.)","The primary component is designed to respond to the insured's loss frequency."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$28,MATCH($A$1,'Point Grid'!$A$8:$A$28,0),MATCH(A6,'Point Grid'!$C$7:$I$7,0))","v":0.5},"preferable for Workers' Compensation risks."]
[0.5,{"$nulls":5},"c.)","The excess component is capped to limit the impact of high severity claims. The assumption is these"]
[{"$nulls":7},"claims are not reflective of future loss experience."]
["(b)","Briefly describe the function of the primary component of the formula."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$28,MATCH($A$1,'Point Grid'!$A$8:$A$28,0),MATCH(A10,'Point Grid'!$C$7:$I$7,0))","v":0.25},{"$nulls":5},"d.)","A goal of experience rating is Individual risk equity which means charging each insured for their true loss potential."]
[0.25,{"$nulls":6},"Giving full credibility to loss experience conflicts with the goal of individual risk equity because it penalizes"]
[{"$nulls":7},"an insured for having random losses that aren't reflective of their true long-term loss experience."]
["(c)","Briefly describe why the excess component is capped."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$28,MATCH($A$1,'Point Grid'!$A$8:$A$28,0),MATCH(A14,'Point Grid'!$C$7:$I$7,0))","v":0.25}]
[0.25]
[]
["(d)","Discuss how using full credibility conflicts with one goal of experience rating."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$28,MATCH($A$1,'Point Grid'!$A$8:$A$28,0),MATCH(A18,'Point Grid'!$C$7:$I$7,0))","v":0.75}]
[0.75]

{"sheet":"11","index":13,"n_rows":30,"n_cols":9,"rows_kept":30}
[11,"Finished",{"$nulls":4},"Navigator",null,"Solution"]
[{"$nulls":7},"a.)","Since this occurred five years ago it is fully reflected within the experience period so it is"]
["Points",{"$nulls":7},"not appropriate for schedule rating."]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('11'!A1,'Point Grid'!A:A,0))","v":2.75},"ABC Incorporated is a theme park operator who is purchasing a Commercial General Liability ",{"$nulls":6},"0% debit/credit out of a maximum of 0%."]
[null,"policy with an effective date of January 1, 2022. Assume ABC Incorporated is eligible for both "]
[null,"experience and schedule rating, and each scenario listed below is independent.",{"$nulls":5},"b.)","Adding a doctor at each location and training staff in emergency first aid should reduce the"]
[{"$nulls":8},"severity of claims. A schedule credit is merited because this is not reflected at all in the "]
[null,"For each scenario identify whether a debit or credit should apply to the schedule rating,",{"$nulls":6},"experience period."]
[null,"suggest an appropriate figure and state the maximum possible debit or credit that could",{"$nulls":6},"Recommend: 2% credit (cooperation - safety program and medical facilities)"]
[null,"be applied.",{"$nulls":6},"Maximum: 2% credit"]
[]
["(a)","In 2017, ABC Incorporated implemented a mandatory one-month training program for all"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$28,MATCH($A$1,'Point Grid'!$A$8:$A$28,0),MATCH(A12,'Point Grid'!$C$7:$I$7,0))","v":0.5},"new hires that teaches equipment maintenance, safety programs, and first aid.",{"$nulls":5},"c.)","Adding inexperienced hires that aren't reflected in the experience period merits a"]
[0.5,{"$nulls":7},"schedule debit. The magnitude depends on how many staff are new out of the total."]
[{"$nulls":8},"Assuming less than half the staff are new"]
["(b)","In 2021, ABC Incorporated added a full-time, on-site doctor in each of its locations and trained",{"$nulls":6},"Recommend: 3% debit modification (Employees - selection, training, supervision, experience)"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$28,MATCH($A$1,'Point Grid'!$A$8:$A$28,0),MATCH(A16,'Point Grid'!$C$7:$I$7,0))","v":0.5},"all employees in emergency first aid.",{"$nulls":6},"Maximum: 6% debit"]
[0.5]
[{"$nulls":7},"d.)","Replacing older equipment should improve safety so merits a credit. "]
["(c)","Due to an increase in visitors in 2021, ABC Incorporated reduced ride downtimes and hired",{"$nulls":6},"The older equipment is present in the experience period and assuming it's replaced by Q1 2022"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$28,MATCH($A$1,'Point Grid'!$A$8:$A$28,0),MATCH(A20,'Point Grid'!$C$7:$I$7,0))","v":0.5},"inexperienced employees to staff its locations. ABC Incorpated usually only hires employees",{"$nulls":6},"the policy period will only use the newer equipment."]
[0.5,"with at least three years of experience.",{"$nulls":6},"Recommend: 8% credit (Equipment - type, care & condition)"]
[{"$nulls":8},"Maximum: 10% credit"]
["(d)","ABC Incorporated began replacing all equipment over 5-years old in each of its locations"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$28,MATCH($A$1,'Point Grid'!$A$8:$A$28,0),MATCH(A24,'Point Grid'!$C$7:$I$7,0))","v":0.5},"in the second quarter of 2021. The upgrades will be completed by Q1 2022.",{"$nulls":5},"e.)","Increasing the capacity without making changes reduces the safety margin which increases risk."]
[0.5,{"$nulls":7},"This changes the exposure for the locations. "]
[{"$nulls":8},"Recommend: 5% debit (Location - exposure inside and out. No changes to outside)"]
["(e)","In order to accommodate more visitors in 2022, ABC Incorporated has increased the maximum ",{"$nulls":6},"Maximum: 5% debit inside, 5% debit outside"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$28,MATCH($A$1,'Point Grid'!$A$8:$A$28,0),MATCH(A28,'Point Grid'!$C$7:$I$7,0))","v":0.75},"capacity of its indoor rides across all locations without making any physical changes. "]
[0.75,"No changes are made to outdoor rides."]

{"sheet":"12","index":14,"n_rows":20,"n_cols":13,"rows_kept":20}
[12,"Finished",{"$nulls":4},"Navigator",null,"Solution"]
[{"$nulls":7},"a.)","Credibility must be between 0 and 1."]
["Points",{"$nulls":7},"Credibility increases as the exposure increases."]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('12'!A1,'Point Grid'!A:A,0))","v":3},{"$nulls":7},"Credibility increases at a decreasing rate."]
[]
["(a)","Using words, identify the three criteria that define an effective credibility function ",{"$nulls":5},"b.)","0 <= Z <= 1 where Z is the assigned credibility."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$28,MATCH($A$1,'Point Grid'!$A$8:$A$28,0),MATCH(A6,'Point Grid'!$C$7:$I$7,0))","v":1},"for experience rating.",{"$nulls":6},"dZ/dE >= 0 where E is the exposure."]
[1,{"$nulls":7},"d/dE (Z/E) <= 0"]
[]
["(b)","Using equations, describe the three criteria from part (a), making sure to ",{"$nulls":5},"c.)","Criterion 1 is met as all credibilities are between 0% and 100%."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$28,MATCH($A$1,'Point Grid'!$A$8:$A$28,0),MATCH(A10,'Point Grid'!$C$7:$I$7,0))","v":1},"define all notation used.",{"$nulls":6},"Criterion 2 is met because the credibilities increase as the exposure (expected losses) increases."]
[1,{"$nulls":7},"Criterion 3 is not met because"]
[{"$nulls":8},"Exposure, E","Credibility, Z","Z/E","Rate of Change"]
["(c)","For each of the three criteria, briefly explain whether or not the following credibility function ",{"$nulls":6},{"v":5000,"d":"5,000"},{"$f":"D17","v":{"v":0,"d":"0%"}},{"$f":"J14/I14","v":{"v":0,"d":"0.0000000"}}]
[{"$f":"INDEX('Point Grid'!$C$8:$I$28,MATCH($A$1,'Point Grid'!$A$8:$A$28,0),MATCH(A14,'Point Grid'!$C$7:$I$7,0))","v":1},"satisfies the criterion.",{"$nulls":6},{"v":5001,"d":"5,001"},{"$f":"D18","v":{"v":0.35,"d":"35%"}},{"$f":"J15/I15","v":{"v":6.9986002799440113E-5,"d":"0.0000700"}},{"$f":"(K15-K14)/(I15-I14)","v":{"v":6.9986002799440113E-5,"d":"0.00007"}},"> 0"]
[1,null,"Expected Losses Between","Credibility"]
[null,null,"$0 to $5,000",{"v":0,"d":"0%"}]
[null,null,"$5,001 to $10,000",{"v":0.35,"d":"35%"}]
[null,null,"$10,001 to $25,000",{"v":0.8,"d":"80%"}]
[null,null,"$25,001 or more",{"v":1,"d":"100%"}]

{"sheet":"13","index":15,"n_rows":29,"n_cols":11,"rows_kept":29}
[13,"Finished",{"$nulls":4},"Navigator",null,"Solution"]
[{"$nulls":7},"a.)","Since this is a balanced retrospective rating plan we have"]
["Points","The following information applies to a balanced retrospectively rated policy:",{"$nulls":6},"Guaranteed Cost Premium = (e + E)*T where"]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('13'!A1,'Point Grid'!A:A,0))","v":2},{"$nulls":7},"e is the expense figure, E is the loss figure and T is the tax multiplier."]
[null,{"v":26000,"d":"$26,000"},"Standard Premium",{"$nulls":5},"Solving for T yields"]
[null,{"v":24000,"d":"$24,000"},"Guaranteed Cost Premium",{"$nulls":5},"T =",{"$f":"B6/B7","v":1.2}]
[null,{"v":20000,"d":"$20,000"},"Provision for Losses and Expenses excluding Taxes"]
[null,{"v":15000,"d":"$15,000"},"Expected Losses",{"$nulls":4},"b.)","The Balance Equations are:"]
[null,1.15,"Loss Conversion Factor",{"$nulls":5},"phi(rH) - phi(rG) = ( (e+E[A])*T - H) / (c*E[A]*T)"]
[null,0.06,"Charge for Maximum",{"$nulls":5},"rG - rH = (G - H) / (c*E[A]*T)"]
[null,0.57,"Savings for Maximum"]
[null,0.685,"Charge for Minimum",{"$nulls":5},"We want to calculate H/SP where SP is the standard premium and H is the minimum premium."]
[null,0.019,"Savings for Minimum",{"$nulls":5},"Solve the first balance equation for H"]
[{"$nulls":8},"H =",{"$f":"B6-(B12-B10)*(B9*B8*J6)","v":{"v":11062.5,"d":"$11,062.50"}}]
["(a)","Determine the tax multiplier.",{"$nulls":6},"H/SP =",{"$f":"J14/B5","v":{"v":0.4254807692307692,"d":"0.42548"}}]
[{"$f":"INDEX('Point Grid'!$C$8:$I$28,MATCH($A$1,'Point Grid'!$A$8:$A$28,0),MATCH(A15,'Point Grid'!$C$7:$I$7,0))","v":0.5}]
[0.5,{"$nulls":6},"c.)","The converted insurance charge is given by c*(phi(rG) - psi(rH))"]
[{"$nulls":8},"c*I = ",{"$f":"B9*(B10-B13)","v":0.04714999999999999}]
["(b)","Determine the minimum premium ratio as a percentage of standard premium.",{"$nulls":6},"So the converted insurance charge in dollars is c*I * expected losses"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$28,MATCH($A$1,'Point Grid'!$A$8:$A$28,0),MATCH(A19,'Point Grid'!$C$7:$I$7,0))","v":0.5},{"$nulls":7},{"$f":"J18*B8","v":{"v":707.2499999999999,"d":"$707.25"}}]
[0.5]
[{"$nulls":7},"d.)","At minimum premium the entry ratio is rH = LH / E[A]"]
["(c)","Calculate the converted insurance charge in dollars.",{"$nulls":6},"Use psi(rH) = phi(rH) + rH - 1 to solve for rH"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$28,MATCH($A$1,'Point Grid'!$A$8:$A$28,0),MATCH(A23,'Point Grid'!$C$7:$I$7,0))","v":0.5},{"$nulls":7},"rH =",{"$f":"B13-B12+1","v":0.33399999999999996}]
[0.5,{"$nulls":7},"LH = ",{"$f":"J24*B8","v":{"v":5009.999999999999,"d":"$5,010"}}]
[{"$nulls":8},"ELRH = LH / SP =",null,{"$f":"J25/B5","v":{"v":0.19269230769230766,"d":"19.3%"}}]
["(d)","Determine the loss ratio at minimum."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$28,MATCH($A$1,'Point Grid'!$A$8:$A$28,0),MATCH(A27,'Point Grid'!$C$7:$I$7,0))","v":0.5}]
[0.5]

{"sheet":"14","index":16,"n_rows":12,"n_cols":7,"rows_kept":12}
[14,"Finished",null,null,"Navigator",null,"Solution"]
[{"$nulls":5},"a.)","Expected Ground Up Loss"]
["Points","Using the information below, calculate the premium for a $50,000 deductible policy.",{"$nulls":4},{"$f":"B6*B5","v":{"v":204230,"d":"$204,230"}}]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('14'!A1,'Point Grid'!A:A,0))","v":2}]
[null,{"v":314200,"d":"$314,200"},"Standard Premium",{"$nulls":3},"Expected Excess Loss"]
["(a)",{"v":0.65,"d":"65%"},"Expected Loss ratio",{"$nulls":3},{"$f":"G3*(1-B11)","v":{"v":138876.4,"d":"$138,876"}}]
[{"$f":"INDEX('Point Grid'!$C$8:$I$28,MATCH($A$1,'Point Grid'!$A$8:$A$28,0),MATCH(A6,'Point Grid'!$C$7:$I$7,0))","v":2},{"v":0.1,"d":"10%"},"ALAE as a percentage of loss"]
[2,{"v":0.175,"d":"18%"},"Commission as a percentage of premium",{"$nulls":3},"Expected ALAE (remember ALAE is paid on all claims whether excess or not)"]
[null,{"v":0.08,"d":"8%"},"Other variable expenses",{"$nulls":3},{"$f":"B5*B6*B7","v":{"v":20423,"d":"$20,423"}}]
[null,{"v":22000,"d":"$22,000"},"Fixed expenses"]
[null,0.32,"Loss Elimination Ratio at a $50,000 deductible",{"$nulls":3},"Premium = (Expected loss & ALAE + fixed expenses) / ( 1 - commission - variable expenses)"]
[{"$nulls":6},{"$f":"(G6+G9+B10)/(1-B8-B9)","v":{"v":243354.89932885906,"d":"$243,354.90"}}]

{"sheet":"15","index":17,"n_rows":50,"n_cols":14,"rows_kept":50,"merged":[[17,2,17,3],[18,2,18,3],[19,2,19,3],[20,2,20,3],[21,2,21,3]]}
[15,"Finished",{"$nulls":4},"Navigator",null,"Solution"]
[{"$nulls":7},"a.)","Since we're not given the expected loss and ALAE for the book of business we must estimate it"]
["Points","An insurer writes five identical workers compensation risks for one year and observes ",{"$nulls":6},"E[A] =",{"$f":"AVERAGE(C18:D22)","v":{"v":1000000,"d":"$1,000,000"}}]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('15'!A1,'Point Grid'!A:A,0))","v":4},"the following:"]
[{"$nulls":8},"Compute the Table M entry ratio for each risk"]
[null,{"v":1250000,"d":"$1,250,000"},"Standard Premium for each risk",{"$nulls":5},"Risk #","Entry Ratio"]
[null,{"v":0.06,"d":"6%"},"General Overhead Expense as a Percent of Standard Premium",{"$nulls":5},1,{"$f":"C18/$J$3","v":{"v":0.4,"d":"0.40"}}]
[null,{"v":0.045,"d":"4.50%"},"Credit Risk Charge as a Percent of Standard Premium",{"$nulls":5},2,{"$f":"C19/$J$3","v":{"v":0.8,"d":"0.80"}}]
[null,{"v":0.11,"d":"11%"},"Expected ULAE as a Percent of Loss and ALAE",{"$nulls":5},3,{"v":1,"d":"1.00"}]
[null,{"v":0.04,"d":"4%"},"Loss Based Assessment as a Percent of Loss and ALAE",{"$nulls":5},4,{"v":1.2,"d":"1.20"}]
[null,{"v":0.1,"d":"10%"},"Excess Ratio for a $250,000 Deductible (% of Loss and ALAE)",{"$nulls":5},5,{"v":1.6,"d":"1.60"}]
[null,{"v":0.08,"d":"8%"},"Acquisition Expenses as a Percent of Standard Premium"]
[null,{"v":0.03,"d":"3%"},"Premium Tax as a Percent of Standard Premium",{"$nulls":5},"Since we want a Table M at certain intervals, use the horizontal slicing approach."]
[null,{"v":0.05,"d":"5%"},"Profit Load as a Percent of Net Premium",{"$nulls":5},"Entry Ratio","# Risks","# Risks Above","% Risks Above","φ(r)","ψ(r)"]
[{"$nulls":8},0,{"$f":"COUNTIF($J$7:$J$11,I15)","v":0},{"$f":"COUNTIF($J$7:$J$11,\">\"&I15)","v":5},{"$f":"K15/5","v":{"v":1,"d":"100.0%"}},{"$f":"M16+L15*(I16-I15)","v":1},{"$f":"M15+I15-1","v":0}]
[null,"Actual losses for each risk are as follows:",{"$nulls":6},{"$f":"I15+0.2","v":0.2},{"$f":"COUNTIF($J$7:$J$11,I16)","v":0},{"$f":"COUNTIF($J$7:$J$11,\">\"&I16)","v":5},{"$f":"K16/5","v":{"v":1,"d":"100.0%"}},0.8,{"$f":"M16+I16-1","v":0}]
[null,"Risk #","Actual Loss & ALAE",{"$nulls":5},{"$f":"I16+0.2","v":0.4},1,4,{"v":0.8,"d":"80.0%"},0.6,0]
[null,1,{"v":400000,"d":"$400,000"},{"$nulls":5},0.6000000000000001,0,4,{"v":0.8,"d":"80.0%"},0.4399999999999999,0.040000000000000036]
[null,2,{"v":800000,"d":"$800,000"},{"$nulls":5},0.8,1,3,{"v":0.6,"d":"60.0%"},0.2799999999999999,0.08000000000000007]
[null,3,{"v":1000000,"d":"$1,000,000"},{"$nulls":5},1,1,2,{"v":0.4,"d":"40.0%"},0.15999999999999998,0.15999999999999992]
[null,4,{"v":1200000,"d":"$1,200,000"},{"$nulls":5},1.2,1,1,{"v":0.2,"d":"20.0%"},0.07999999999999999,0.28]
[null,5,{"v":1600000,"d":"$1,600,000"},{"$nulls":5},1.4,0,1,{"v":0.2,"d":"20.0%"},{"$f":"M23+L22*(I23-I22)","v":0.039999999999999994},0.43999999999999995]
[{"$nulls":8},{"$f":"I22+0.2","v":1.5999999999999999},1,0,{"v":0,"d":"0.0%"},0,0.5999999999999999]
["(a)","Construct a Table M for this book of business by displaying the insurance charges and savings"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$28,MATCH($A$1,'Point Grid'!$A$8:$A$28,0),MATCH(A24,'Point Grid'!$C$7:$I$7,0))","v":2},"for entry ratios from 0.00 to 1.60 in increments of 0.20.",{"$nulls":5},"b.)","An LDD policy pays all claims and seeks reimbursement for deductible losses."]
[2]
[{"$nulls":8},{"$f":"J3*B11","v":{"v":100000,"d":"$100,000"}},"Expected loss excess of the per-occurrence deductible"]
["(b)","For any risk above, estimate the prospective premium for a large dollar deductible (LDD) policy with",{"$nulls":6},{"$f":"J3-I27","v":{"v":900000,"d":"$900,000"}},"Expected Limited Loss (deductible loss)"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$28,MATCH($A$1,'Point Grid'!$A$8:$A$28,0),MATCH(A28,'Point Grid'!$C$7:$I$7,0))","v":2},"a per-occurrence deductible of $250,000 and an annual aggregate deductible of $1,260,000.",{"$nulls":6},{"$f":"B7*B6","v":{"v":75000,"d":"$75,000"}},"General Expenses"]
[2,{"$nulls":7},{"$f":"B8*B6","v":{"v":56250,"d":"$56,250"}},"Credit Risk Charge"]
[{"$nulls":8},{"$f":"B9*J3","v":{"v":110000,"d":"$110,000"}},"ULAE, paid on all losses"]
[{"$nulls":8},{"$f":"B10*J3","v":{"v":40000,"d":"$40,000"}},"Loss based assessment charge"]
[{"$nulls":8},{"$f":"B12*B6","v":{"v":100000,"d":"$100,000"}},"Acquisition expenses"]
[{"$nulls":8},{"$f":"B13*B6","v":{"v":37500,"d":"$37,500"}},"Premium Tax"]
[]
[{"$nulls":8},"We need to determine the charge resulting from introducing the aggregate deductible"]
[{"$nulls":8},"To do this we really need a Limited Table M but we only have a Table M."]
[{"$nulls":8},"We assume its okay to apply the Table M in place of the Limited Table M. This is "]
[{"$nulls":8},"equivalent to performing the ICRLL process with the adjustment landing in the same"]
[{"$nulls":8},"expected loss group."]
[{"$nulls":8},"Limited Entry Ratio = aggregate limit / expected limited loss"]
[{"$nulls":8},{"$f":"1260000/I28","v":{"v":1.4,"d":"1.40"}}]
[]
[{"$nulls":8},"Insurance charge =  phi(1.40)*expected limited loss"]
[{"$nulls":8},{"$f":"M22*I28","v":{"v":35999.99999999999,"d":"$36,000"}}]
[]
[{"$nulls":8},"LDD Premium = (Excess of Per-occurrence + insurance charge + expenses + ULAE +"]
[{"$nulls":9}," credit charge + loss based charge + acquisition expenses + taxes) / (1 - Profit Provision)"]
[]
[{"$nulls":8},{"$f":"(I27+SUM(I29:I34)+I45)/(1-B14)","v":{"v":583947.3684210527,"d":"$583,947"}}]

{"sheet":"16","index":18,"n_rows":40,"n_cols":13,"rows_kept":40}
[16,"Finished",{"$nulls":4},"Navigator",null,"Solution"]
[{"$nulls":7},"a.)","E[A] =",{"$f":"AVERAGE(0,1000000)","v":{"v":500000,"d":"$500,000"}}]
["Points","The following information applies to a company's retrospectively rated Workers'",{"$nulls":6},"Expected retrospective premium is given by R = (B + c*E[L])*T"]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('16'!A1,'Point Grid'!A:A,0))","v":4},"Compensation policy:",{"$nulls":6},"E[L] = E[A] - I, where I is the net insurance charge."]
[null,"   • Its losses are expected to be uniformly distributed on [0, 1,000,000].",{"$nulls":6},"Tax multiplier =",null,{"$f":"1/(1-B11)","v":{"v":1.0526315789473684,"d":"1.05"}}]
[]
[null,{"v":750000,"d":"$750,000"},"Losses at maximum premium",{"$nulls":5},"Drawing a Lee diagram may help with this question."]
[null,{"v":225000,"d":"$225,000"},"Losses at minimum premium",{"$nulls":5},"Probability of a loss over $750,000 =",{"$nulls":3},{"$f":"1-B7/10^6","v":0.25}]
[null,1.15,"Loss conversion factor",{"$nulls":5},"Expected excess loss over $750,000 =",{"$nulls":3},{"$f":"AVERAGE(B7,10^6)-B7","v":{"v":125000,"d":"$125,000"}}]
[null,{"v":130000,"d":"$130,000"},"Basic Premium",{"$nulls":5},"Insurance Charge =",{"$nulls":3},{"$f":"M8*M9","v":{"v":31250,"d":"$31,250"}}]
[null,{"v":0.05,"d":"5%"},"Tax Rate"]
[{"$nulls":8},"Probability of a loss under $225,000 =",{"$nulls":3},{"$f":"B8/10^6","v":{"v":0.225,"d":"0.23"}}]
["(a)","Calculate the expected retrospective premium for this Workers' Compensation policy.",{"$nulls":6},"Expected loss under $225,000 =",{"$nulls":3},{"$f":"AVERAGE(0,B8)","v":{"v":112500,"d":"$112,500"}}]
[{"$f":"INDEX('Point Grid'!$C$8:$I$28,MATCH($A$1,'Point Grid'!$A$8:$A$28,0),MATCH(A13,'Point Grid'!$C$7:$I$7,0))","v":2},{"$nulls":7},"Insurance Savings =",{"$nulls":3},{"$f":"M12*M13","v":{"v":25312.5,"d":"$25,313"}}]
[2]
[{"$nulls":8},"Net insurance charge =",{"$nulls":3},{"$f":"M10-M14","v":{"v":5937.5,"d":"$5,938"}}]
["(b)","The company is considering whether to implement a fraud detection device."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$28,MATCH($A$1,'Point Grid'!$A$8:$A$28,0),MATCH(A17,'Point Grid'!$C$7:$I$7,0))","v":2},"This addition would result in a shift in the loss distribution to a uniform distribution",{"$nulls":6},"E[L] = ",{"$f":"J2-M16","v":{"v":494062.5,"d":"$494,063"}}]
[2,"between [0, 800,000]."]
[null,"Assuming no other plan parameters change, calculate the resulting premium savings.",{"$nulls":6},"Retro Premium, R = ",null,{"$f":"(B10+B9*J18)*K5","v":{"v":734917.7631578947,"d":"$734,918"}}]
[]
[{"$nulls":7},"b.)","Now assume the loss distribution is uniform on [0, 800,000]"]
[{"$nulls":8},"E[A] =",{"$f":"AVERAGE(0,800000)","v":{"v":400000,"d":"$400,000"}}]
[]
[{"$nulls":8},"Probability of a loss over $750,000 =",{"$nulls":3},{"$f":"1-B7/800000","v":{"v":0.0625,"d":"0.063"}}]
[{"$nulls":8},"Expected excess loss over $750,000 =",{"$nulls":3},{"$f":"AVERAGE(B7,800000)-B7","v":{"v":25000,"d":"$25,000"}}]
[{"$nulls":8},"Insurance Charge =",{"$nulls":3},{"$f":"M25*M26","v":{"v":1562.5,"d":"$1,563"}}]
[]
[{"$nulls":8},"Probability of a loss under $225,000 =",{"$nulls":3},{"$f":"B8/800000","v":{"v":0.28125,"d":"0.281"}}]
[{"$nulls":8},"Expected loss under $225,000 =",{"$nulls":3},{"$f":"AVERAGE(0,B8)","v":{"v":112500,"d":"$112,500"}}]
[{"$nulls":8},"Insurance Savings =",{"$nulls":3},{"$f":"M29*M30","v":{"v":31640.625,"d":"$31,641"}}]
[]
[{"$nulls":8},"Net insurance charge =",{"$nulls":3},{"$f":"M27-M31","v":{"v":-30078.125,"d":"($30,078)"}}]
[]
[{"$nulls":8},"E[L] =",{"$f":"J23-M33","v":{"v":430078.125,"d":"$430,078"}}]
[]
[{"$nulls":8},"Retro Premium, R = ",null,{"$f":"(B10+B9*J35)*K5","v":{"v":657462.9934210526,"d":"$657,463"}}]
[]
[{"$nulls":8},"Premium Savings "]
[{"$nulls":8},{"$f":"K20-K37","v":{"v":77454.76973684214,"d":"$77,454.77"}}]

{"sheet":"17","index":19,"n_rows":28,"n_cols":13,"rows_kept":28}
[17,"Finished",{"$nulls":4},"Navigator",null,"Solution"]
[{"$nulls":7},"a.)","The ISO approach to risk loading ILFs is"]
["Points","Given the following information about an insurer's book of business:"]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('17'!A1,'Point Grid'!A:A,0))","v":1.5},{"$nulls":7},"The general formula for (risk-loaded) ILFs is:"]
[null,{"v":10000,"d":"$10,000"},"Basic limit",null,"Claim Size","Probability"]
[null,{"v":250000,"d":"$250,000"},"Policy limit",null,{"v":250,"d":"$250"},0.1768]
[null,{"v":500,"d":"$500"},"Average ALAE",null,{"v":2500,"d":"$2,500"},0.5709]
[null,{"v":0.02,"d":"2.0%"},"ULAE",null,{"v":15000,"d":"$15,000"},0.1565,null,null,"Claim Size","Cap @ Basic","Cap @ Policy","(X; b)2","(X; L)2"]
[null,{"v":4,"d":"4.0"},"Expected Claim Count",null,{"v":75000,"d":"$75,000"},0.0958,null,null,{"$f":"E6","v":{"v":250,"d":"$250"}},{"$f":"MIN(I9,$B$5)","v":{"v":250,"d":"$250"}},{"$f":"MIN(I9,$B$6)","v":{"v":250,"d":"$250"}},{"$f":"J9^2","v":{"v":62500,"d":"$62,500"}},{"v":62500,"d":"$62,500"}]
[null,{"v":0.15,"d":"15.0%"},"ISO k-value",{"$nulls":5},{"$f":"E7","v":{"v":2500,"d":"$2,500"}},{"$f":"MIN(I10,$B$5)","v":{"v":2500,"d":"$2,500"}},{"$f":"MIN(I10,$B$6)","v":{"v":2500,"d":"$2,500"}},{"v":6250000,"d":"$6,250,000"},{"v":6250000,"d":"$6,250,000"}]
[{"$nulls":8},{"v":15000,"d":"$15,000"},{"v":10000,"d":"$10,000"},{"$f":"MIN(I11,$B$6)","v":{"v":15000,"d":"$15,000"}},{"v":100000000,"d":"$100,000,000"},{"v":225000000,"d":"$225,000,000"}]
[null,"• Claim counts are assumed to follow a Poisson distribution.",{"$nulls":6},{"v":75000,"d":"$75,000"},{"v":10000,"d":"$10,000"},{"$f":"MIN(I12,$B$6)","v":{"v":75000,"d":"$75,000"}},{"v":100000000,"d":"$100,000,000"},{"v":5625000000,"d":"$5,625,000,000"}]
[null,"• The insurer follows the ISO approach to load their ILFs for risk."]
[{"$nulls":8},"Expectations",{"$f":"SUMPRODUCT(J9:J12,F6:F9)","v":{"v":3994.45,"d":"$3,994"}},{"$f":"SUMPRODUCT(K9:K12,F6:F9)","v":{"v":11003.95,"d":"$11,004"}}]
["(a)","Calculate the ILF at the policy limit with and without including the risk load."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$28,MATCH($A$1,'Point Grid'!$A$8:$A$28,0),MATCH(A15,'Point Grid'!$C$7:$I$7,0))","v":1.5},{"$nulls":7},"Without Risk Loading"]
[1.5,{"$nulls":7},"ILF =",{"$f":"((K14+B7)*(1+B8))/((J14+B7)*(1+B8))","v":{"v":2.559590161198812,"d":"2.560"}}]
[]
[{"$nulls":8},"With Risk Loading"]
[{"$nulls":8},"Since the claim count distribution is Poisson, we have δ = 0."]
[]
[{"$nulls":8},"E[X2; b] =",{"$f":"SUMPRODUCT(L9:L12,F6:F9)","v":{"v":28809175,"d":"$28,809,175"}}]
[{"$nulls":8},"E[X2; L] =",{"$f":"SUMPRODUCT(F6:F9,M9:M12)","v":{"v":577666675,"d":"$577,666,675"}}]
[]
[{"$nulls":8},"τ(b) = ",{"$f":"B10/SQRT(B9)*SQRT(J22)","v":{"v":402.55634310615454,"d":"$402.56"}}]
[{"$nulls":8},"τ(L) = ",{"$f":"B10/SQRT(B9)*SQRT(J23)","v":{"v":1802.6022985880716,"d":"$1,802.60"}}]
[]
[{"$nulls":8},"ILF =",{"$f":"((K14+B7)*(1+B8)+J26)/((J14+B7)*(1+B8)+J25)","v":{"v":2.71444062232046,"d":"2.714"}}]

{"sheet":"18","index":20,"n_rows":52,"n_cols":17,"rows_kept":52,"merged":[[0,6,0,7]]}
[18,"Finished",{"$nulls":4},"Navigator",null,null,"Solution"]
[{"$nulls":8},"a.)"]
["Points","An insured is subject to experience rating under the National Council on Compensation Insurance "]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('18'!A1,'Point Grid'!A:A,0))","v":3},"(NCCI) Experience Rating Plan Manual for Workers Compensation and Employers Liability Insurance."]
[{"$nulls":9},"Use the class code to look up the associated ELR and D-ratio."]
[null,"The following information about the insured is given:",{"$nulls":7},"ELR","D-ratio"]
[null,{"v":4000000,"d":"$4,000,000"},"Payroll for the experience period",{"$nulls":6},{"$f":"C30","v":1.17},{"$f":"D30","v":{"v":0.2,"d":"0.20"}}]
[null,"AL","State"]
[null,4279,"Class",{"$nulls":6},{"$f":"B7/100","v":{"v":40000,"d":"$40,000"}},"Payroll in (100)s"]
[null,{"v":5000,"d":"$5,000"},"Primary/Excess split point"]
[null,{"v":167000,"d":"$167,000"},"State per Claim Accident Limitation",{"$nulls":6},"Expected Loss, E =",null,{"$f":"J9*J7","v":{"v":46800,"d":"$46,800"}}]
[null,{"v":334000,"d":"$334,000"},"State Multiple Claim Accident Limitation"]
[null,{"v":6.7,"d":"6.70"},"G value",{"$nulls":6},"The expected primary loss is Ep = E * D-Ratio =",{"$nulls":3},{"$f":"K7*L11","v":{"v":9360,"d":"$9,360"}}]
[]
[null,"The following claims apply to the experience period.",{"$nulls":7},"The expected excess loss is Ee = E - Ep =",{"$nulls":3},{"$f":"L11-N13","v":{"v":37440,"d":"$37,440"}}]
[null,"Excluding Medical Only Losses",null,null,"Medical Only Losses"]
[null,"Claim #","Indemnity",null,"Claim #","Indemnity",{"$nulls":3},"Use the expected loss, E, to look up the associated ballast and weight values."]
[null,1,{"v":2000,"d":"$2,000"},null,7,{"v":500,"d":"$500"},{"$nulls":3},"Ballast B =",{"$f":"F34","v":{"v":20100,"d":"20,100"}}]
[null,2,{"v":2500,"d":"$2,500"},null,8,{"v":1750,"d":"$1,750"},{"$nulls":3},"Weight W =",{"$f":"C33","v":{"v":0.1,"d":"0.10"}}]
[null,3,{"v":4000,"d":"$4,000"},null,9,{"v":3000,"d":"$3,000"}]
[null,4,{"v":5000,"d":"$5,000"},null,10,{"v":25000,"d":"$25,000"},{"$nulls":3},"Use the claims information to compute the actual primary and actual excess experience."]
[null,5,{"v":25000,"d":"$25,000"}]
[null,"6*",{"v":350000,"d":"$350,000"},{"$nulls":6},"First apply the state multiple claim limit."]
[null,"* Only one person was injured in claim 6.",{"$nulls":7},"This only has the potential to impact claim 6. However, we're told claim 6 has a single claimant so it isn't impacted."]
[{"$nulls":9},"Now apply the state per claim limit. "]
[null,"You may use the following tables to answer the question below."]
[null,"Class Code","ELR","D Ratio",{"$nulls":5},"Claim #","Cap at single","Primary","Excess"]
[null,4263,1.01,0.22,{"$nulls":5},{"$f":"B18","v":1},{"$f":"MIN(C18,$B$11)","v":{"v":2000,"d":"$2,000"}},{"$f":"MIN(K28,$B$10)","v":{"v":2000,"d":"$2,000"}},{"$f":"K28-L28","v":{"v":0,"d":"$0"}}]
[null,4273,0.94,0.21,{"$nulls":5},2,{"v":2500,"d":"$2,500"},{"$f":"MIN(K29,$B$10)","v":{"v":2500,"d":"$2,500"}},{"$f":"K29-L29","v":{"v":0,"d":"$0"}}]
[null,4279,1.17,{"v":0.2,"d":"0.20"},{"$nulls":5},3,{"v":4000,"d":"$4,000"},{"v":4000,"d":"$4,000"},{"v":0,"d":"$0"}]
[{"$nulls":9},4,{"v":5000,"d":"$5,000"},{"v":5000,"d":"$5,000"},{"v":0,"d":"$0"}]
[null,"Expected Losses","Weighting Value",null,"Expected Losses","Ballast Value",{"$nulls":3},5,{"v":25000,"d":"$25,000"},{"v":5000,"d":"$5,000"},{"v":20000,"d":"$20,000"}]
[null,"31,844 to 47,399",{"v":0.1,"d":"0.10"},null,"0 to 36,038",{"v":16750,"d":"16,750"},{"$nulls":3},"6*",{"v":167000,"d":"$167,000"},{"v":5000,"d":"$5,000"},{"v":162000,"d":"$162,000"}]
[null,"47,400 to 61,236",0.11,null,"36,039 to 62,025",{"v":20100,"d":"20,100"},{"$nulls":3},{"$f":"E18","v":7},{"$f":"MIN(F18*(1-70%),$B$11)","v":{"v":150.00000000000003,"d":"$150"}},{"$f":"MIN(K34,(1-70%)*$B$10)","v":{"v":150.00000000000003,"d":"$150"}},{"$f":"K34-L34","v":{"v":0,"d":"$0"}}]
[null,"61,237 to 74,710",0.12,null,"62,026 to 91,884",{"v":23450,"d":"23,450"},{"$nulls":3},{"$f":"E19","v":8},{"$f":"MIN(F19*(1-70%),$B$11)","v":{"v":525.0000000000001,"d":"$525"}},{"$f":"MIN(K35,(1-70%)*$B$10)","v":{"v":525.0000000000001,"d":"$525"}},{"$f":"K35-L35","v":{"v":0,"d":"$0"}}]
[{"$nulls":9},9,{"$f":"MIN(F20*(1-70%),$B$11)","v":{"v":900.0000000000001,"d":"$900"}},{"v":900.0000000000001,"d":"$900"},{"v":0,"d":"$0"}]
["(a)","Calculate the experience modification for this insured.",{"$nulls":7},10,{"$f":"MIN(F21*(1-70%),$B$11)","v":{"v":7500.000000000001,"d":"$7,500"}},{"v":1500.0000000000002,"d":"$1,500"},{"v":6000.000000000001,"d":"$6,000"}]
[{"$f":"INDEX('Point Grid'!$C$8:$I$28,MATCH($A$1,'Point Grid'!$A$8:$A$28,0),MATCH(A37,'Point Grid'!$C$7:$I$7,0))","v":2.5}]
[2.5]
[{"$nulls":9},"Ap =",{"$f":"SUM(L28:L37)","v":{"v":26575,"d":"$26,575"}}]
["(b)","Select all true statements below:",{"$nulls":7},"Ae =",{"$f":"SUM(M28:M37)","v":{"v":188000,"d":"$188,000"}}]
[{"$f":"INDEX('Point Grid'!$C$8:$I$28,MATCH($A$1,'Point Grid'!$A$8:$A$28,0),MATCH(A41,'Point Grid'!$C$7:$I$7,0))","v":0.5}]
[0.5,"i.) The state per-accident and multiple claim accident limits do not apply to disease losses.",{"$nulls":7},"Maximum allowed experience mod = 1.10 + (0.0004 * Expected Losses / G) =",{"$nulls":6},{"$f":"1.1+0.0004*L11/B13","v":{"v":3.894029850746269,"d":"3.89"}}]
[null,"ii.) Policy level disease losses are capped at 3*(State per-claim accident limit) + 120% Expected Loss."]
[null,"iii.) Primary disease losses are capped at 2x the primary/excess split point.",{"$nulls":7},"Experience mod =",null,{"$f":"ROUND(MIN(Q43,(K40+(1-K19)*N15+K18+K19*K41)/(N13+(1-K19)*N15+K18+K19*N15)),2)","v":{"v":1.48,"d":"1.480"}}]
[null,"iv.) Primary disease losses are capped at 2x primary/excess split point + 40% Expected Primary Loss."]
[null,"v.) Disease losses do not include losses due to occupational exposures such as exposure to asbestos.",{"$nulls":6},"b.)","i.) FALSE – the state per-accident and multiple claim accident limits should be applied first."]
[{"$nulls":9},"ii.) TRUE – after applying the state per- and multiple claim accident limits, disease losses are capped at the policy level by"]
[{"$nulls":9},"3*(State per-claim accident limit) + 120% Expected Loss."]
[{"$nulls":9},"iii.) FALSE – primary disease losses are capped at 2x primary/excess split point + 40% Expected Primary Loss."]
[{"$nulls":9},"iv.) TRUE"]
[{"$nulls":9},"v.) FALSE – disease losses include those due to occupational exposures such as asbestos."]

{"sheet":"19","index":21,"n_rows":31,"n_cols":15,"rows_kept":31}
[19,"Finished",{"$nulls":6},"Navigator",null,"Solution"]
[{"$nulls":9},"a.)","Loss elimination ratio = C(d) ="]
["Points"]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('19'!A1,'Point Grid'!A:A,0))","v":2.5},"Given the following information about an insurer's book of business which ",{"$nulls":8},"Modified Frequency = φ(1- FX(d))"]
[null,"utilizes straight deductibles:"]
[{"$nulls":10},"Modified Severity ="]
[null,0.0003,"Ground-up claim frequency"]
[null,{"v":100000,"d":"100,000"},"Basic policy limit"]
[null,{"v":12310,"d":"12,310"},"Basic limit expected loss",{"$nulls":9},"Modified"]
[null,150,"ALAE",{"$nulls":7},"Deductible","C(d)","Frequency","Severity","Pure Premium"]
[null,{"v":0.12,"d":"12.0%"},"ULAE",{"$nulls":7},0,{"$f":"(C18+D18*$B$10)/($B$9+$B$10)","v":{"v":0,"d":"0.0000"}},{"$f":"$B$7*(1-D18)","v":{"v":0.0003,"d":"0.000300"}},{"$f":"(($B$9-C18)+(1-D18)*$B$10)/(1-D18)*(1+$B$11)","v":{"v":13955.2,"d":"$13,955"}},{"$f":"M11*N11","v":{"v":4.18656,"d":"$4.19"}}]
[{"$nulls":10},{"v":1000,"d":"1,000"},{"v":0.05872431781701445,"d":"0.0587"},{"v":1.5458999999999997E-4,"d":"0.000155"},{"v":25491.345623908404,"d":"$25,491"},{"$f":"M12*N12","v":{"v":3.9407071199999995,"d":"$3.94"}}]
[null,{"v":250000,"d":"$250,000"},"Policy limit",{"$nulls":7},{"v":2000,"d":"2,000"},{"v":0.09637520064205458,"d":"0.0964"},{"v":0.00012033,"d":"0.000120"},{"v":31439.20418848168,"d":"$31,439"},{"v":3.7830794400000003,"d":"$3.78"}]
[null,{"v":1000,"d":"$1,000"},"Policy Deductible",{"$nulls":7},{"v":3000,"d":"3,000"},{"v":0.1265951043338684,"d":"0.1266"},{"v":0.00010125,"d":"0.000101"},{"v":36114.1925925926,"d":"$36,114"},{"v":3.656562,"d":"$3.66"}]
[null,{"v":4684.152990107914,"d":"$4,684"},"Policy Premium",{"$nulls":7},{"v":5000,"d":"5,000"},{"v":0.17507704654895667,"d":"0.1751"},{"v":7.9079999999999981E-5,"d":"0.000079"},{"v":43672.09711684372,"d":"$43,672"},{"v":3.4535894400000005,"d":"$3.45"}]
[{"$nulls":10},{"v":10000,"d":"10,000"},{"v":0.26221709470304977,"d":"0.2622"},{"v":5.3549999999999994E-5,"d":"0.000054"},{"v":57680.1568627451,"d":"$57,680"},{"v":3.0887723999999994,"d":"$3.09"}]
[null,"Deductible","E[X; d]","FX(d)",null,"Limit","ILF"]
[null,0,0,0,null,{"v":100000,"d":"100,000"},{"v":0.6666666666666666,"d":"0.667"},null,null,"b.)","Premium = Pb*(I(L) - C(d))"]
[null,{"v":1000,"d":"1,000"},659,0.4847,null,{"v":250000,"d":"250,000"},{"v":1,"d":"1.000"}]
[null,{"v":2000,"d":"2,000"},{"v":1111,"d":"1,111"},0.5989,null,{"v":500000,"d":"500,000"},{"v":1.1666666666666667,"d":"1.167"},{"$nulls":3},"To use this formula the ILFs must be rebased at the basic limit ($100,000)."]
[null,{"v":3000,"d":"3,000"},{"v":1478,"d":"1,478"},0.6625,null,{"v":1000000,"d":"1,000,000"},{"v":1.2666666666666666,"d":"1.267"},{"$nulls":3},"Limit","ILF"]
[null,{"v":5000,"d":"5,000"},{"v":2071,"d":"2,071"},0.7364,{"$nulls":6},{"v":100000,"d":"100,000"},{"$f":"G18/$G$18","v":{"v":1,"d":"1.000"}}]
[null,{"v":10000,"d":"10,000"},{"v":3144,"d":"3,144"},0.8215,{"$nulls":6},{"v":250000,"d":"250,000"},{"$f":"G19/$G$18","v":{"v":1.5,"d":"1.500"}}]
[{"$nulls":10},{"v":500000,"d":"500,000"},{"$f":"G20/$G$18","v":{"v":1.7500000000000002,"d":"1.750"}}]
["(a)","Calculate the loss elimination ratio, modified frequency, severity and pure premium ",{"$nulls":8},{"v":1000000,"d":"1,000,000"},{"$f":"G21/$G$18","v":{"v":1.9,"d":"1.900"}}]
[{"$f":"INDEX('Point Grid'!$C$8:$I$28,MATCH($A$1,'Point Grid'!$A$8:$A$28,0),MATCH(A25,'Point Grid'!$C$7:$I$7,0))","v":1.5},"for each deductible in the table above."]
[1.5,{"$nulls":9},"Basic Premium =",null,{"$f":"B15/(L23-L12)","v":{"v":3250.004872775762,"d":"$3,250.00"}}]
[]
["(b)","Calculate the premium for a policy with a $1,000,000 limit and $2,000 deductible.",{"$nulls":8},"New Policy Premium =",null,{"$f":"M27*(L25-L13)","v":{"v":5861.789386572528,"d":"$5,861.79"}}]
[{"$f":"INDEX('Point Grid'!$C$8:$I$28,MATCH($A$1,'Point Grid'!$A$8:$A$28,0),MATCH(A29,'Point Grid'!$C$7:$I$7,0))","v":1}]
[1]

{"sheet":"20","index":22,"n_rows":38,"n_cols":18,"rows_kept":38,"merged":[[0,8,0,9]]}
[20,"Finished",{"$nulls":6},"Navigator",null,null,"Solution"]
[{"$nulls":10},"a.)",null,null,"Modified","Excess","Expected","Policy"]
["Points","Given the following information about a retrospectively rated Workers' Compensation policy which is",{"$nulls":10},"Hazard","Expected","Ratio at","Excess","Excess"]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('20'!A1,'Point Grid'!A:A,0))","v":2.5},"also subject to experience rating.",{"$nulls":9},"State","Group","Loss","Loss Limit","Loss","Ratio"]
[{"$nulls":11},"X","A",{"$f":"$B$6*D11*$B$7","v":{"v":43890,"d":"43,890"}},{"$f":"E11","v":0.72},{"$f":"N5*O5","v":{"v":31600.8,"d":"31,601"}}]
[null,0.95,"Experience Modification Factor",{"$nulls":4},"Table of Policy Excess Ratio Ranges",{"$nulls":3},"Y","B",{"$f":"$B$6*D12*$B$7","v":{"v":75240,"d":"75,240"}},{"$f":"E12","v":0.05},{"$f":"N6*O6","v":{"v":3762,"d":"3,762"}}]
[null,{"v":0.66,"d":"66%"},"Expected Unlimited Loss Ratio",{"$nulls":4},"Sub-table","Excess Ratio Range",null,null,"Z","C",{"$f":"$B$6*D13*$B$7","v":{"v":84645,"d":"84,645"}},{"$f":"E13","v":0.59},{"v":49940.549999999996,"d":"49,941"}]
[{"$nulls":7},7,"0.146 - 0.181",null,null,"Total",null,{"$f":"SUM(N5:N7)","v":{"v":203775,"d":"203,775"}},null,{"$f":"SUM(P5:P7)","v":{"v":85303.35,"d":"85,303"}},{"$f":"ROUND(P8/N8,3)","v":0.419},"← policy excess ratio"]
[null,null,"Hazard","Manual","Excess Ratio ","Avg. Cost",null,8,"0.182 - 0.221"]
[null,"State","Group","Premium","at Loss Limit","per Case",null,9,"0.222 - 0.269",null,null,"This corresponds to sub-table 12."]
[null,"X","A",{"v":70000,"d":"70,000"},0.72,{"v":25000,"d":"25,000"},null,10,"0.270 - 0.316"]
[null,"Y","B",{"v":120000,"d":"120,000"},0.05,{"v":17000,"d":"17,000"},null,11,"0.317 - 0.358",null,"b.)",null,null,"Modified",null,"Expected"]
[null,"Z","C",{"v":135000,"d":"135,000"},0.59,{"v":20000,"d":"20,000"},null,12,"0.359 - 0.420",{"$nulls":3},"Hazard","Expected","Avg Cost","Number "]
[{"$nulls":7},13,"0.421 - 0.484",null,null,"State","Group","Loss","per Case","of Claims"]
[{"$nulls":11},"X","A",{"$f":"$B$6*D11*$B$7","v":{"v":43890,"d":"43,890"}},{"$f":"F11","v":{"v":25000,"d":"25,000"}},{"$f":"N15/O15","v":{"v":1.7556,"d":"1.76"}}]
["(a)","Calculate the policy excess ratio and associated sub-table to use when rating this policy under the",{"$nulls":9},"Y","B",{"$f":"$B$6*D12*$B$7","v":{"v":75240,"d":"75,240"}},{"$f":"F12","v":{"v":17000,"d":"17,000"}},{"$f":"N16/O16","v":{"v":4.425882352941176,"d":"4.43"}}]
[{"$f":"INDEX('Point Grid'!$C$8:$I$28,MATCH($A$1,'Point Grid'!$A$8:$A$28,0),MATCH(A16,'Point Grid'!$C$7:$I$7,0))","v":0.5},"NCCI Retrospective rating plan.",{"$nulls":9},"Z","C",{"v":84645,"d":"84,645"},{"v":20000,"d":"20,000"},{"v":4.23225,"d":"4.23"}]
[0.5,{"$nulls":10},"Total",null,{"$f":"SUM(N15:N17)","v":{"v":203775,"d":"203,775"}},null,{"$f":"ROUND(SUM(P15:P17),1)","v":10.4},"← Expected number of claims"]
[]
["(b)","Calculate the total number of expected claims for this policy."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$28,MATCH($A$1,'Point Grid'!$A$8:$A$28,0),MATCH(A20,'Point Grid'!$C$7:$I$7,0))","v":0.5},{"$nulls":9},"c.)","See the inset yellow boxes."]
[0.5]
[{"$nulls":10},"d.)","Advantages: Any TWO of:"]
["(c)","Organize the following according to whether or not they are part of the policy's basic premium.",{"$nulls":9},"• The table of aggregate loss factors shares the methodology improvements of the ALFs on demand so they are more accurate."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$28,MATCH($A$1,'Point Grid'!$A$8:$A$28,0),MATCH(A24,'Point Grid'!$C$7:$I$7,0))","v":0.75},{"$nulls":10},"• It automatically accounts for claim size inflation over time."]
[0.75,{"$nulls":10},"• It removes the need for updates to hazard group differentials and to the expected loss group ranges."]
[{"$nulls":11},"• More recent data is used to generate these tables."]
[null,"Included in Basic Premium",null,null,"Not Part of Basic Premium"]
[{"$nulls":11},"Disadvantage:"]
[{"$nulls":11},"• The table of aggregate loss factors applies countrywide so doesn't reflect state and hazard group severity distribution differences."]
[]
[]
[]
[]
[]
["(d)","Identify two advantages and one disadvantage of using the table of aggregate loss factors over the"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$28,MATCH($A$1,'Point Grid'!$A$8:$A$28,0),MATCH(A36,'Point Grid'!$C$7:$I$7,0))","v":0.75},"previous table of net insurance charges."]
[0.75]

{"sheet":"21","index":23,"n_rows":30,"n_cols":14,"rows_kept":30}
[21,"Finished",{"$nulls":5},"Navigator",null,"Solution"]
[{"$nulls":8},"a.)","Size","F(x)","E[X; x]","e(x)","Fitted"]
["Points","An actuary wishes to fit a claim size distribution to a sample of 1,000 claims.",{"$nulls":7},100,{"$f":"SUM(D6:$D$6)/$D$13","v":0.099},{"$f":"(SUM(E6:$E$6)+SUM(D7:$D$12)*J3)/$D$13","v":{"v":95.1,"d":"95"}},{"$f":"($K$11-L3)/(1-K3)","v":{"v":1219.6448390677026,"d":"1,219.64"}},{"$f":"_xlfn.FORECAST.LINEAR(J3,$M$3:$M$8,$J$3:$J$8)","v":{"v":1253.0416589072365,"d":"1,253.04"}}]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('21'!A1,'Point Grid'!A:A,0))","v":2.25},{"$nulls":8},500,{"$f":"SUM(D$6:$D7)/$D$13","v":0.392},{"$f":"(SUM(E$6:$E7)+SUM(D8:$D$12)*J4)/$D$13","v":393},{"$f":"($K$11-L4)/(1-K4)","v":{"v":1317.4342105263158,"d":"1,317.43"}},{"$f":"_xlfn.FORECAST.LINEAR(J4,$M$3:$M$8,$J$3:$J$8)","v":{"v":1330.137979204394,"d":"1,330.14"}}]
[null,null,"Size Group","# Claims","Total Loss","Severity",{"$nulls":3},{"v":1000,"d":"1,000"},{"$f":"SUM(D$6:$D8)/$D$13","v":0.613},{"$f":"(SUM(E$6:$E8)+SUM(D9:$D$12)*J5)/$D$13","v":638},{"$f":"($K$11-L5)/(1-K5)","v":{"v":1436.6925064599484,"d":"1,436.69"}},{"v":1426.5083795758405,"d":"1,426.51"}]
[null,null,"0 – 100",99,{"v":5000,"d":"5,000"},{"$f":"E6/D6","v":{"v":50.505050505050505,"d":"51"}},{"$nulls":3},{"v":2000,"d":"2,000"},{"$f":"SUM(D$6:$D9)/$D$13","v":0.826},{"$f":"(SUM(E$6:$E9)+SUM(D10:$D$12)*J6)/$D$13","v":904},{"v":1666.6666666666663,"d":"1,666.67"},{"v":1619.249180318734,"d":"1,619.25"}]
[null,null,"101 – 500",293,{"v":84000,"d":"84,000"},{"$f":"E7/D7","v":{"v":286.68941979522185,"d":"287"}},{"$nulls":3},{"v":4000,"d":"4,000"},{"$f":"SUM(D$6:$D10)/$D$13","v":0.953},{"$f":"(SUM(E$6:$E10)+SUM(D11:$D$12)*J7)/$D$13","v":{"v":1098,"d":"1,098"}},{"v":2042.55319148936,"d":"2,042.55"},{"v":2004.7307818045206,"d":"2,004.73"}]
[null,null,"501 – 1,000",221,{"v":162000,"d":"162,000"},{"v":733.0316742081448,"d":"733"},{"$nulls":3},{"v":5000,"d":"5,000"},{"$f":"SUM(D$6:$D11)/$D$13","v":0.973},{"$f":"(SUM(E$6:$E11)+SUM(D12:$D$12)*J8)/$D$13","v":{"v":1136,"d":"1,136"}},{"v":2148.1481481481464,"d":"2,148.15"},{"v":2197.471582547414,"d":"2,197.47"}]
[null,null,"1,001 – 2,000",213,{"v":305000,"d":"305,000"},{"v":1431.924882629108,"d":"1,432"},{"$nulls":3},{"v":10000,"d":"10,000"},{"$f":"SUM(D$6:$D12)/$D$13","v":1},{"$f":"(SUM(E$6:$E12))/$D$13","v":1194},"NA","NA"]
[null,null,"2,001 – 4,000",127,{"v":354000,"d":"354,000"},{"v":2787.4015748031497,"d":"2,787"}]
[null,null,"4,001 – 5,000",20,{"v":91000,"d":"91,000"},{"v":4550,"d":"4,550"},{"$nulls":3},"E[X] =",{"$f":"F13","v":{"v":1194,"d":"1,194"}}]
[null,null,"5,001 – 10,000",27,{"v":193000,"d":"193,000"},{"v":7148.148148148148,"d":"7,148"}]
[null,null,"Total",{"$f":"SUM(D6:D12)","v":{"v":1000,"d":"1,000"}},{"$f":"SUM(E6:E12)","v":{"v":1194000,"d":"1,194,000"}},{"$f":"E13/D13","v":{"v":1194,"d":"1,194"}},{"$nulls":3},"Since we can't graph in the Pearson VUE environment, we can try fitting a straight line using the FORECAST.LINEAR function."]
[{"$nulls":9},"We can assess the goodness of fit via the R2 statistic as measured using the square of CORREL function"]
["(a)","Calculate the empirical mean excess claim size distribution and identify the distribution.",{"$nulls":7},"R2 =",{"$f":"CORREL(N3:N8,M3:M8)^2","v":{"v":0.9899168602810338,"d":"0.9899"}}]
[{"$f":"INDEX('Point Grid'!$C$8:$I$28,MATCH($A$1,'Point Grid'!$A$8:$A$28,0),MATCH(A15,'Point Grid'!$C$7:$I$7,0))","v":1}]
[1,{"$nulls":8},"Since the R2 value is very close to 1 we deduce the mean excess claim size distribution"]
[{"$nulls":9},"follows a straight line and thus is best described using a Pareto distribution."]
["(b)","Using the distribution identified in part a.) above, calculate the empirical parameters"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$28,MATCH($A$1,'Point Grid'!$A$8:$A$28,0),MATCH(A19,'Point Grid'!$C$7:$I$7,0))","v":1},"associated with that distribution.",{"$nulls":6},"b.)","A Pareto distribution has"]
[1]
[{"$nulls":9},"Using LINEST on the fitted data allows us to get the parameters for y = mx + b"]
["(c)","Identify a condition on the claim data set in order for the above analysis to produce",{"$nulls":7},"m ","b"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$28,MATCH($A$1,'Point Grid'!$A$8:$A$28,0),MATCH(A23,'Point Grid'!$C$7:$I$7,0))","v":0.25},"an accurate distribution.",{"$nulls":7},{"$f":"LINEST(N3:N8,J3:J8)","v":0.19274080074289338},1233.7675788329473]
[0.25]
[{"$nulls":9},"Solving m for α yields α =",null,{"$f":"1/J24+1","v":{"v":6.188315064302084,"d":"6.1883"}}]
[{"$nulls":9},"Then solving b for β yields β =",null,null,{"$f":"K24*(L26-1)","v":{"v":6401.174915106489,"d":"6,401.17"}}]
[{"$nulls":9},"These are the parameters of the fitted Pareto distribution."]
[]
[{"$nulls":8},"c.)","We need there to be sufficiently many large claims in the data set so we can reliably estimate e(x) for large x values."]

{"sheet":"22","index":24,"n_rows":40,"n_cols":9,"rows_kept":40,"merged":[[0,5,0,6]]}
[22,"Finished",{"$nulls":3},"Navigator",null,null,"Solution"]
[{"$nulls":7},"b.)","Large data sets can lead to dense clouds of deviance residuals which makes pattern recognition challenging."]
["Points","An actuary is about to peer review a GLM. They are refreshing their memory on some model",{"$nulls":6},"The residuals should be binned prior to graphing. One way of doing this is to use binned working residuals."]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('22'!A1,'Point Grid'!A:A,0))","v":1.75},"refinement techniques."]
[{"$nulls":7},"c.)","i.) TRUE"]
[{"$nulls":8},"ii.) FALSE - a good model has normally distributed deviance residuals"]
["(a)","Match the following descriptions to the correct techniques.",{"$nulls":6},"iii.) TRUE"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$29,MATCH($A$1,'Point Grid'!$A$8:$A$29,0),MATCH(A7,'Point Grid'!$C$7:$I$7,0))","v":0.75},{"$nulls":7},"iv.) FALSE - a good model has normally distributed deviance residuals"]
[0.75,"Cook's distance",{"$nulls":6},"v.) FALSE - this implies the chosen GLM distribution did not have enough right skewness."]
[]
[]
[null,"Cross validation"]
[]
[]
[null,"Bootstrapping"]
{"$rows_empty":12}
["(b)","Identify a weakness of graphically analyzing deviance residuals and briefly describe how to"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$29,MATCH($A$1,'Point Grid'!$A$8:$A$29,0),MATCH(A28,'Point Grid'!$C$7:$I$7,0))","v":0.5},"overcome it."]
[0.5]
[]
["(c)","Identify all true statements below:"]
[{"$f":"INDEX('Point Grid'!$C$8:$I$29,MATCH($A$1,'Point Grid'!$A$8:$A$29,0),MATCH(A32,'Point Grid'!$C$7:$I$7,0))","v":0.5}]
[0.5," i.) Deviance residuals should have no predictable pattern."]
[null,"ii.) The shape of the deviance residuals should match the choice of GLM distribution."]
[null,"iii.) Deviance residuals should be normally distributed with constant variance."]
[null,"iv.) When plotting deviance residuals, significant deviations from normality"]
[null,"imply the model fit is good."]
[null,"v.) Both ends of a q-q plot for deviance residuals lie above the line of equality. This means the"]
[null,"chosen GLM distribution was too skewed to the right."]

{"sheet":"23","index":25,"n_rows":25,"n_cols":12,"rows_kept":25,"merged":[[0,6,0,7]]}
[23,"Finished",{"$nulls":4},"Navigator",null,null,"Solution"]
[{"$nulls":8},"a.)","The GLM relativity and the βj coefficients are not on the same scale because the GLM relativities have had their link function inverted."]
["Points","An actuary has built a GLM for a countrywide rating plan. ",{"$nulls":7},"Therefore, the rating factors are given by (GLM Relativity)*exp(βj)"]
[{"$f":"INDEX('Point Grid'!B:B,MATCH('23'!A1,'Point Grid'!A:A,0))","v":2},"They are using a lasso credibility model with a log-link function to produce"]
[null,"a rating plan for one of their mid-size states.",{"$nulls":9},"Lasso"]
[null,"The actuary uses the relativities from the countrywide GLM as the offsets.",{"$nulls":7},"VehicleType",null,"Relativity"]
[{"$nulls":9},{"$f":"B13","v":"Sedan"},null,{"$f":"D13*EXP(F13)","v":{"v":1,"d":"1.000"}}]
["(a)","Using the information below, construct the table of rating factors for",{"$nulls":7},{"$f":"B14","v":"Van"},null,{"$f":"D14*EXP(F14)","v":{"v":0.8599203973715848,"d":"0.860"}}]
[{"$f":"INDEX('Point Grid'!$C$8:$I$30,MATCH($A$1,'Point Grid'!$A$8:$A$30,0),MATCH(A8,'Point Grid'!$C$7:$I$7,0))","v":0.5},"the VehicleType rating variable for the mid-size state.",{"$nulls":7},{"$f":"B15","v":"SUV"},null,{"v":1.114414456251118,"d":"1.114"}]
[0.5,{"$nulls":8},{"$f":"B16","v":"Luxury SUV"},null,{"v":1.3099103743517786,"d":"1.310"}]
[{"$nulls":3},"GLM",null,"βj","Mid-Size",null,null,{"$f":"B17","v":"Convertible"},null,{"$f":"D17*EXP(F17)","v":1.065}]
[null,"VehicleType",null,"Relativity",null,"Coefficients","Exposures"]
[null,"Sedan",null,{"v":1,"d":"1.000"},null,{"v":0,"d":"0.0000"},{"v":250000,"d":"250,000"},null,"b.)","The complement is given full credibility when the βj coefficient is zero."]
[null,"Van",null,0.691,null,0.2187,{"v":15000,"d":"15,000"},null,null,"This occurs for Sedans and Convertibles."]
[null,"SUV",null,1.187,null,-0.0631,{"v":140000,"d":"140,000"},null,null,"Sedans have the greatest exposure so the experience is likely to be too similar to the countrywide model for the lasso model to deviate."]
[null,"Luxury SUV",null,1.285,null,0.0192,{"v":53000,"d":"53,000"},null,null,"Convertibles have next to no exposures so there is likely insufficient data to overcome the lasso credibility model penalty."]
[null,"Convertible",null,1.065,null,{"v":0,"d":"0.0000"},500]
[{"$nulls":8},"c.)","Since Convertibles have next to no exposures the lasso credibility model doesn't deviate from the countrywide GLM relativity."]
["(b)","Identify which vehicle types have full credibility on their complement.",{"$nulls":7},"If the Convertibles have limited exposures in the countrywide GLM dataset then the complement itself may be inappropriate."]
[{"$f":"INDEX('Point Grid'!$C$8:$I$30,MATCH($A$1,'Point Grid'!$A$8:$A$30,0),MATCH(A19,'Point Grid'!$C$7:$I$7,0))","v":1},"Briefly explain why.",{"$nulls":7},"This would cause the Convertible relativity to be incorrect."]
[1]
[]
["(c)","Briefly explain why the VehicleType relativity for Convertibles found in "]
[{"$f":"INDEX('Point Grid'!$C$8:$I$30,MATCH($A$1,'Point Grid'!$A$8:$A$30,0),MATCH(A23,'Point Grid'!$C$7:$I$7,0))","v":0.5},"part a.) above may be incorrect."]
[0.5]