1 Use a VLOOKUP function in cell F25 of the CatastropheCoverageLowActivity worksheet to determine the appropriate Copayment for the first visit to a Primary Doctor. The Type in cell D25 is one of the parameters. Use absolute and relative references when appropriate 2 0.5 Reuse your formula in cell F25 and paste it down to complete the "Copayment" column of the table. 3 1.25 In cell G25 of the CatastropheCoverageLowActivity worksheet calculate the Payment towards Deductible as the lesser (use the MIN function) of the Claim Amount or the Deductible for the indicated Type. You will need to use a VLOOKUP function to determine the deductible for the indicated Type. 4 0.5 Reuse your formula in cell G25 and paste it down to complete the Payment towards Deductible column. 5 1.25 Coinsurance is a percentage applied to the amount of the claim in excess of the deductible. In cell H25 of the CatastropheCoverageLowActivity worksheet use VLOOKUP to find the applicable percentage for the Type of claim. Multiply that percentage times the greater of the claim amount less the deductible, or zero. You will need to use a VLOOKUP function to determine the deductible for the indicated Type. 6 0.5 Reuse your formula in cell H25 and paste it down to complete the Coinsurance column 7 0.5 In I25 of the CatastropheCoverageLowActivity worksheet calculate the Potential Cost to the Insured for the individual claim. This is the sum of the Copayment, the Payment towards the Deductible, and the Coinsurance. 8 0.25 Reuse your formula in cell I25 and paste it down to complete the Potential Cost column. 9 1.25 In Cell J25 of the CatastropheCoverageLowActivity worksheet calculate the running total of the cost to the insured after considering the annual maximum out-of-pocket cost by adding the current claim potential cost to the previous total and taking the lesser of that, and the annual maximum out-of-pocket. 10 0.5 Reuse your formula in cell J25 and paste it down to complete the Total Cost column. 11 0.5 Use the Sum function in cell E36 of the CatastropheCoverageLowActivity worksheet to calculate the total of claim amounts. 12 0.5 In cell J36 of the CatastropheCoverageLowActivity worksheet, calculate the total of costs after limit to MAX out-of-pocket. Since the values in this field are a running total, the total cost is the value in column J for the last claim. 13 0.5 Calculate the total premium cost in cell E19 of the CatastropheCoverageLowActivity worksheet. The total premium cost is the monthly premium amount times 12. 14 0.5 Calculate the total medical payments in cell E20 of the CatastropheCoverageLowActivity worksheet. The total medical payments are equal to the total cost after limit to MAX out-of-pocket. 15 0.75 Calculate the total medical cost in cell E21 of the CatastropheCoverageLowActivity worksheet. The total medical cost is equal to the sum of the total premium expense and the total medical payments. 16 1.25 Copy the range F25:J25 from the CatastropheCoverageLowActivity worksheet and paste it into the same range on the CatastropheCoverageWithSurgery worksheet. You will be using the same logic for handling claims on the two worksheets, but the claims themselves will be different. 17 1.25 Reuse your formulas in cells F25:J25 of the CatastropheCoverageWithSurgery worksheet and paste them down to row 41 to complete the "Copayment" through Cost after limit columns of the table. 18 0.5 Use the Sum function in cell E43 of the CatastropheCoverageWithSurgery worksheet to calculate the total of claim amounts. 19 0.5 In cell J43 of the CatastropheCoverageWithSurgery worksheet, calculate the total of costs after limit to MAX out-of-pocket. Since the values in this field are a running total, the total cost is the value in column J for the last claim. 20 0.5 Calculate the total premium cost in cell E19 of the CatastropheCoverageWithSurgery worksheet. The total premium cost is the monthly premium amount times 12.