The employees of General Manufacturing Corp.receive health insurance through a group plan issued by Wellnet.During the past year,40 percent of the employees did not file any health insurance claims,40 percent filed only a small claim,and 20 percent filed a large claim.The small claims were spread uniformly between 0 and $2,000,whereas the large claims were spread uniformly between $2,000 and $20,000.Based on this experience,Wellnet now is negotiating the corporation's premium payment per employee for the upcoming year.You are an operations research analyst for the insurance carrier,and you have been assigned the task of estimating the average cost of insurance coverage for the corporation's employees.(a)Use the random numbers 0.4071,0.5228,0.8185,0.5802,and 0.0193 to simulate whether each of five employees files no claim,a small claim,or a large claim.Then use the random numbers 0.9823,0.0188,0.8771,0.9872,and 0.4129 to simulate the size of the claim (including zero if no claim was file d).Calculate the average of these claims to estimate the mean of the overall distribution of the size of employee's health insurance claims.(b)Formulate and apply a spreadsheet model to simulate the cost for 300 employees' health insurance claims.Calculate the average of these random observations.(c)The true mean of the overall probability distribution of the size of an employee's health insurance claim is $2,600.Compare the estimates of this mean obtained in parts a and b with the true mean of the distribution.

Question

Quizplus · Accepted Answer

The Answer is (a)Let the numbers 0.0000 through 0.3999 correspond to no claim,0.4000 through 0.7999 correspond to a small claim,and 0.8000 through 0.9999 correspond to a large claim.To simulate a small claim with a uniform distribution between $0 and $2000,Claim = 0+($2,000)*(Random Number)To simulate a large claim with a uniform distribution between $2,000 and $20,000,Claim = $2,000 + ($18,000)*(Random Number)For the given random numbers:

(a)Let the numbers 0.0000 through 0.3999 correspond to no claim,0.4000 through 0.7999 correspond to a small claim,and 0.8000 through 0.9999 correspond to a large claim.To simulate a small claim with a uniform distribution between $0 and $2000,Claim = 0+($2,000)*(Random Number)To simulate a large claim with a uniform distribution between $2,000 and $20,000,Claim = $2,000 + ($18,000)*(Random Number)For the given random numbers: Average: $4,353 (b)As in part a,two random numbers are needed to simulate the size of the claim.The first random number is used to determine the type of claim (no claim,small claim,or large claim)and the second random number is used to determine the size of the claim given the claim type.The simulation for each of the 300 employees will be a separate row in the spreadsheet.A random number is generated in column B and D using the RAND()function.If the random number in column B is between 0.0000 and 0.3999,there will be no claim (40% probability).If the random number in column B is between 0.4000 and 0.7999,there will be a small claim (40% probability).Otherwise,if the random number in column B is greater than or equal to 0.8000,there will be a large claim (20% probability).This is calculated with the following IF statement: Claim Type = IF(RandomNumber1 < NoClaimProb,NoClaim,IF(RandomNumber1 < NoClaimProb + SmallClaimProb,SmallClaim,LargeClaim)).Given the claim type,the size of the claim is generated using the second random number.In general,since the size of the claim follows the uniform distribution,Claim Size = Min + (Max - Min)*(Random Number).An IF statement is used to determine the Claim Size as a function of the Claim Type as follows: ClaimSize = IF(ClaimType = NoClaim,0,IF(ClaimType = SmallClaim,SmallClaimMin + (SmallClaimMax - SmallClaimMin)*RandomNumber2,LargeClaimMin + (LargeClaimMax - LargeClaimMin)*RandomNumber2)). (c)The estimate of the mean in part a is way off the true mean ($4,353 vs.$2,600).A sample size of 5 is not sufficient to give an accurate estimate of the true mean.The estimate of the mean in part b is fairly close ($2,738 vs.$2,600).

Average: $4,353 (b)As in part a,two random numbers are needed to simulate the size of the claim.The first random number is used to determine the type of claim (no claim,small claim,or large claim)and the second random number is used to determine the size of the claim given the claim type.The simulation for each of the 300 employees will be a separate row in the spreadsheet.A random number is generated in column B and D using the RAND()function.If the random number in column B is between 0.0000 and 0.3999,there will be no claim (40% probability).If the random number in column B is between 0.4000 and 0.7999,there will be a small claim (40% probability).Otherwise,if the random number in column B is greater than or equal to 0.8000,there will be a large claim (20% probability).This is calculated with the following IF statement: Claim Type = IF(RandomNumber1 < NoClaimProb,NoClaim,IF(RandomNumber1 < NoClaimProb + SmallClaimProb,SmallClaim,LargeClaim)).Given the claim type,the size of the claim is generated using the second random number.In general,since the size of the claim follows the uniform distribution,Claim Size = Min + (Max - Min)*(Random Number).An IF statement is used to determine the Claim Size as a function of the Claim Type as follows: ClaimSize = IF(ClaimType = NoClaim,0,IF(ClaimType = SmallClaim,SmallClaimMin + (SmallClaimMax - SmallClaimMin)*RandomNumber2,LargeClaimMin + (LargeClaimMax - LargeClaimMin)*RandomNumber2)).

(c)The estimate of the mean in part a is way off the true mean ($4,353 vs.$2,600).A sample size of 5 is not sufficient to give an accurate estimate of the true mean.The estimate of the mean in part b is fairly close ($2,738 vs.$2,600).

The Employees of General Manufacturing Corp