1	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	.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	.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	.5	Reuse your formula in cell H25 and paste it down to complete the Coinsurance column
7	.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	.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	.5	Reuse your formula in cell J25 and paste it down to complete the Total Cost column.
11	.5	Use the Sum function in cell E36 of the CatastropheCoverageLowActivity worksheet to calculate the total of claim amounts.
12	.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	.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	.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	.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	.5	Use the Sum function in cell E43 of the CatastropheCoverageWithSurgery worksheet to calculate the total of claim amounts.
19	.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	.5	Calculate the total premium cost in cell E19 of the CatastropheCoverageWithSurgery worksheet. The total premium cost is the monthly premium amount times 12.
21	.5	Calculate the total medical payments in cell E20 of the CatastropheCoverageWithSurgery worksheet. The total medical payments are equal to the total cost after limit to MAX out-of-pocket.
22	.75	Calculate the total medical cost in cell E21 of the CatastropheCoverageWithSurgery worksheet. The total medical cost is equal to the sum of the total premium expense and the total medical payments.
23	1.25	Copy the range F25:J25 from the CatastropheCoverageLowActivity worksheet and paste it into the same range on the PremiumCoverageLowActivity worksheet. You will be using the same logic for handling claims on the two worksheets, but the claims themselves will be different.
24	1.25	Reuse your formulas in cells F25:J25 of the PremiumCoverageLowActivity worksheet and paste them down to row 34 to complete the "Copayment" through Cost after limit columns of the table.
25	.5	Use the Sum function in cell E36 of the PremiumCoverageLowActivity worksheet to calculate the total of claim amounts.
26	.5	In cell J36 of the PremiumCoverageLowActivity 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.
27	.5	Calculate the total premium cost in cell E19 of the PremiumCoverageLowActivity worksheet. The total premium cost is the monthly premium amount times 12.
28	.5	Calculate the total medical payments in cell E20 of the PremiumCoverageLowActivity worksheet. The total medical payments are equal to the total cost after limit to MAX out-of-pocket.
29	.75	Calculate the total medical cost in cell E21 of the PremiumCoverageLowActivity worksheet. The total medical cost is equal to the sum of the total premium expense and the total medical payments.
30	1.25	Copy the range F25:J25 from the CatastropheCoverageLowActivity worksheet and paste it into the same range on the PremiumCoverageWithSurgery worksheet. You will be using the same logic for handling claims on the two worksheets, but the claims themselves will be different.
31	1.25	Reuse your formulas in cells F25:J25 of the PremiumCoverageWithSurgery worksheet and paste them down to row 41 to complete the "Copayment" through Cost after limit columns of the table.
32	.5	Use the Sum function in cell E43 of the PremiumCoverageWithSurgery worksheet to calculate the total of claim amounts.
33	.5	In cell J43 of the PremiumCoverageWithSurgery 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.
34	.5	Calculate the total premium cost in cell E19 of the PremiumCoverageWithSurgery worksheet. The total premium cost is the monthly premium amount times 12.
35	.5	Calculate the total medical payments in cell E20 of the PremiumCoverageWithSurgery worksheet. The total medical payments are equal to the total cost after limit to MAX out-of-pocket.
36	.75	Calculate the total medical cost in cell E21 of the PremiumCoverageWithSurgery worksheet. The total medical cost is equal to the sum of the total premium expense and the total medical payments.
37	.5	In cell C4 of the Summary worksheet enter 0. If you buy no insurance and have no health activity, you have spent nothing.
38	.5	In cell D4 of the summary worksheet calculate the total cost an individual would pay if they had low medical activity and no insurance by referencing the correct cell on the CatastropheCoverageLowActivity worksheet where you have already calculated the total cost of all medical claims for the relative low activity scenario.
39	.5	In cell E4 of the summary worksheet calculate the total cost an individual would pay if they had high medical activity and no insurance by referencing the correct cell on the CatastropheCoverageWithSurgery worksheet where you have already calculated the total cost of all medical claims for the relative high activity scenario.
40	1	In cell C5 of the Summary worksheet calculate total cost an individual would pay if they had catastrophe coverage and no activity by referencing the total premium expense you already calculated on the CatastropheCoverageLowActivity worksheet (if you don't have any medical activity, the only costs would be the medical premiums).
41	1	In cell D5 of the summary worksheet calculate the total cost an individual would pay if they had low medical activity and catastrophe insurance by referencing the correct cell on the CatastropheCoverageLowActivity worksheet where you have already calculated the total medical expense for the relative low activity scenario and catastrophe insurance.
42	1	In cell E5 of the summary worksheet calculate the total cost an individual would pay if they had high medical activity and catastrophe insurance by referencing the correct cell on the CatastropheCoverageWithSurgery worksheet where you have already calculated the total medical expense for the relative high activity scenario and catastrophe insurance.
43	1	In cell C6 of the Summary worksheet calculate total cost an individual would pay if they had premium coverage and no activity by referencing the total premium expense you already calculated on the PremiumCoverageLowActivity worksheet (if you don't have any medical activity, the only costs would be the medical premiums).
44	1	In cell D6 of the summary worksheet calculate the total cost an individual would pay if they had low medical activity and premium insurance by referencing the correct cell on the PremiumCoverageLowActivity worksheet where you have already calculated the total medical expense for the relative low activity scenario and premium insurance.
45	1	In cell E6 of the summary worksheet calculate the total cost an individual would pay if they had high medical activity and premium insurance by referencing the correct cell on the PremiumCoverageWithSurgery worksheet where you have already calculated the total medical expense for the relative high activity scenario and premium insurance.
46	.5	In cells C10:E10 of the Summary worksheet enter 0. This is the profit to the insurance company if an individual has no insurance, regardless of the level of medical activity.
47	1	In cell C11 of the Summary worksheet calculate the profit to the insurance company if an individual has catastrophe coverage and no medical activity by referencing appropriate cell(s) on the CatastropheCoverageLowActivity worksheet. Since there is no medical activity, the insurance company will incur no costs for medical claims. Therefore, the profit to the company is the total premiums paid by the individual.
48	1	In cell D11 of the Summary worksheet calculate the profit to the insurance company if an individual has catastrophe coverage and low medical activity by referencing appropriate cell(s) on the CatastropheCoverageLowActivity worksheet. The profit to the company is the total premiums paid by the individual minus the amount of the total claims paid by the insurance company. The amount of the total claims paid by the insurance company is the difference between the total cost of all claims and the total cost of the claims paid by the individual.
49	1	In cell E11 of the Summary worksheet calculate the profit to the insurance company if an individual has catastrophe coverage and high medical activity by referencing appropriate cell(s) on the CatastropheCoverageWithSurgery worksheet. The profit to the company is the total premiums paid by the individual minus the amount of the total claims paid by the insuance company. The amount of the total claims paid by the insurance company is the difference between the total cost of all claims and the total cost of the claims paid by the individual.
50	1	In cell C12 of the Summary worksheet calculate the profit to the insurance company if an individual has premium coverage and no medical activity by referencing appropriate cell(s) on the PremiumCoverageLowActivity worksheet. Since there is no medical activity, the insurance company will incur no costs for medical claims. Therefore, the profit to the company is the total premiums paid by the individual.
51	1	In cell D12 of the Summary worksheet calculate the profit to the insurance company if an individual has premium coverage and low medical activity by referencing appropriate cell(s) on the PremiumCoverageLowActivity worksheet. The profit to the company is the total premiums paid by the individual minus the amount of the total claims paid by the insuance company. The amount of the total claims paid by the insurance company is the difference between the total cost of all claims and the total cost of the claims paid by the individual.
52	1	In cell E12 of the Summary worksheet calculate the profit to the insurance company if an individual has premium coverage and high medical activity by referencing appropriate cell(s) on the PremiumCoverageWithSurgery worksheet. The profit to the company is the total premiums paid by the individual minus the amount of the total claims paid by the insuance company. The amount of the total claims paid by the insurance company is the difference between the total cost of all claims and the total cost of the claims paid by the individual.

ontily preni Naxinun out-of pocket $ 160 $6,600 Type Deductible Co-Pay Co-Ins Prinary doctor Other doctors Energency roon Hospital services 50 B100 0% 0% D6600 1, 920 1,920 0 Total Preniun Expense Total Medi cal Payments Total Medi cal Cost Paynent tOvards Deductibl Cost after Potential limit to IAX Clain Cost to out-of- Honth Description Type Anount CopaynenteCoinsurance Insured Pocket TanuaryPrinary Doctor 80A MarchEnergency Roon & Lab tests AprilDernatologist office visit Prinary Doctor C1, 707 366 99450 May May June June July 80A $545 B $ 80A B175 B Urologist office visit Primary Doctor Eye Doctor exan Prinary Doctor $80 August Physical Therapist office visit B250B ecenberPrinary Doctor A$ 80 Totals ontily preni Naxinun out-of pocket $ 160 $6,600 Type Deductible Co-Pay Co-Ins Prinary doctor Other doctors Energency roon Hospital services 50 B100 0% 0% D6600 1, 920 1,920 0 Total Preniun Expense Total Medi cal Payments Total Medi cal Cost Paynent tOvards Deductibl Cost after Potential limit to IAX Clain Cost to out-of- Honth Description Type Anount CopaynenteCoinsurance Insured Pocket TanuaryPrinary Doctor 80A MarchEnergency Roon & Lab tests AprilDernatologist office visit Prinary Doctor C1, 707 366 99450 May May June June July 80A $545 B $ 80A B175 B Urologist office visit Primary Doctor Eye Doctor exan Prinary Doctor $80 August Physical Therapist office visit B250B ecenberPrinary Doctor A$ 80 Totals