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	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.
21	0.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	0.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	0.5	Use the Sum function in cell E36 of the PremiumCoverageLowActivity worksheet to calculate the total of claim amounts.
26	0.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	0.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	0.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	0.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	0.5	Use the Sum function in cell E43 of the PremiumCoverageWithSurgery worksheet to calculate the total of claim amounts.
33	0.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	0.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	0.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	0.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	0.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	0.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	0.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	0.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 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.
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 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.

0 UnitedHealthcare Catastrophic Compass Plus 6600 Singl e Adult Age 22- Monthly premium $160 -Deductible $6,600-Out-of-pocket maximum $6,60D Copayment Primary doctor-$50 Copayment Specialist Dactor $100 Emergency Room- No charpe after deductible Haspital Services No charge after deductible Lab Services No charge after deductible Monthly premiurn Maximum out-ofpocket 160 6,600 Type Deductible Co-Pay Co-Ins Primary doctor Other doctors Emergency room Hospital services 100 C 6,600 D $6,600 16 17 18 19 0% Total Premium Expense Total Medical Payments Total Medical Cost 21 Cost after limit to Potential Cost MAX out-of 23 Month Description Type AmountCopayment DeductibleCoinsurance to Insured 25 26 27 28 29 Primary Doctor Ernergency Room& Lab tests Dermatologist officevisit Primary Doctor Uralogist office visit Primary Doctor Eye Dactor exam Primary Doctor Physical Therapist office visit Primary Doctor January C 1,707 $366 B $545 80 B $175 31 32 lune $250 December 35 36 37 38 39 40 41 42 43 Tatals Budget CashFlow GatastropheGoverageLowActivity UnitedHealthcare Catastrophic Compass Plus 6600 Single Adult Age 22 -Monthly premium $160 Deductible $6,600 Out of pocket maximum $6,600 Copayment Primary doctor-$50 Copayment Specialist Doctar $100 Emerpency Room -No charge after deductible Hospital Services-No charge after deductible Lab Services No charee after deductible Monthly premium Maximum out-of-pocket $160 6,500 10 12 Type DeductibleCo-Pay Co-Ins Primary doctor Other doctors Emergency room Hospital services 5100 C $6,600 6,600 $ 16 17 18 19 20 0% Total Premium Expense Total Medical Payments Total Medical Cost Cost after limit to MAX out-of- Pocket Potential Cost 23 24 25 26 27 28 Type AmountCopayment Deductible Coinsurance to Insured Primary Doctar Cardiologist Office visit Urolagist Office Visit Primary Doctor Emergency Room & Lab tests Dermatolagist affice visit Primary Doctor Urolagist office visit Primary Doctor Eye Doetor exarn Primary Doctor February February February B 251 B $147 93 C $ 1,707 31 32 80 B S545 80 B S175 34 uly B $250 36 37 38 39 August Physical Therapist office visit Septembe Physical Therapist afficevisit October DecemberPrimary Doctar December Emergency Room & CT scan December Primary Doctor D $ 53,816 A S133 $ 4,300 Surgery & Hospital stay 43 Budget CashFlow S Summary Ca wActivity CatastropheCoverageWithSurgery PremiumCoverageWithSurgery UnitedHeathcare Gold Compass Plus 500 Single Male Age 22 -Monthly premium $230-Deductible $500 Out-of-pocket Maximum $6,600 Copayment Primary dactor $20 Copayment Other Doctor-$40 Co-Payment Energency Raam-$250 Co-insurance Emergency Room-20% after deductible al Services-20% coin rance after deductible $230 6,600 10 Monthly premium Maximum out-of 12 13 14 15 Type Deductible Co Pay Primary doctor Other doctors Emerzency room $500$ 500 $ 250 service 20% 18 19 20 21 Total Premium Expense Total Medical Payments Total Medical Cost Potential Cost Cost after limit to Type Amount Copayment Deductible Coinsurance to Insured MAX out-of-Pocket 23 24 25 Month Description anuary Primary Doctor C $ 1,707 Emergency Room& Lab tests Dermatolopist office visit Prinary Doctar Urolopist office visit Prirmary Doctor Fyr Dactor exam Primary Doctor Physical Therapist affice visit 28 $ 545 June $ 31 32 175 250 34 December Primary Doctor 80 Totals 36 37 39 40 43 Budget CashFlow Summary CatastropheCoverageLowActivity CatastropheCoverageWithSurgery PremiumCoverageLowActivity PremiumCoverageWithSurgery UnitedHealthcare Gold Compass Plus S00 Single Adult Age 22 -Monthly premium $230 Deductible $500-Out-ofpocket Maximum $6,600 Capayment Primary doctar $20 Copayment Specialist-$40 Ca-Payment Emergency Raam-$250 plus cost of service up to deductible Co-insurance Emereency Room-20% after deductible Hospital Services-20% coinsurance after deductible 10 Monthly premium Maximum out-of-pocket $230 6,600 12 Type DeductibleCo-Pay Co-Ins Primary doctar Other doctors Ernergency room Hospital services C S500 $ 16 17 18 250 500 20% Total Premium Expense Total Medical Payments Total Medical Cost 21 Cost after limit to MAXout-of- Pocket Clalm towards Potential Cost 23 Description Type AmountCopayment Deductible Coinsurance to Insured 24 Primary Doetor 26 27 28 29 February Cardoloeist Office visit FebruaryUralogist Office Visit February Primary Doctor $ 251 B $ 147 C 1,707 B366 Ernergency Room&Lab tests Dermatoloist office visit Primary Doetor Urologist office visit Primary Doetor Eye Doctor exam Primary Doetor Physical Therapist ofi ce visit 31 B 5 545 $ 175 $ 250 B $420 D 53,816 $ 133 C 4,300 37 September Physical Therapist officevisit October Sureery & Hospital stay Decernber Primary Doctor December Emereency Room & CT scan Decernber Primary Doctor 39 43 Budget CashFlow Summary CatastropheCoverageWithSurgery PremiumCoverageWithSurgery 0 UnitedHealthcare Catastrophic Compass Plus 6600 Singl e Adult Age 22- Monthly premium $160 -Deductible $6,600-Out-of-pocket maximum $6,60D Copayment Primary doctor-$50 Copayment Specialist Dactor $100 Emergency Room- No charpe after deductible Haspital Services No charge after deductible Lab Services No charge after deductible Monthly premiurn Maximum out-ofpocket 160 6,600 Type Deductible Co-Pay Co-Ins Primary doctor Other doctors Emergency room Hospital services 100 C 6,600 D $6,600 16 17 18 19 0% Total Premium Expense Total Medical Payments Total Medical Cost 21 Cost after limit to Potential Cost MAX out-of 23 Month Description Type AmountCopayment DeductibleCoinsurance to Insured 25 26 27 28 29 Primary Doctor Ernergency Room& Lab tests Dermatologist officevisit Primary Doctor Uralogist office visit Primary Doctor Eye Dactor exam Primary Doctor Physical Therapist office visit Primary Doctor January C 1,707 $366 B $545 80 B $175 31 32 lune $250 December 35 36 37 38 39 40 41 42 43 Tatals Budget CashFlow GatastropheGoverageLowActivity UnitedHealthcare Catastrophic Compass Plus 6600 Single Adult Age 22 -Monthly premium $160 Deductible $6,600 Out of pocket maximum $6,600 Copayment Primary doctor-$50 Copayment Specialist Doctar $100 Emerpency Room -No charge after deductible Hospital Services-No charge after deductible Lab Services No charee after deductible Monthly premium Maximum out-of-pocket $160 6,500 10 12 Type DeductibleCo-Pay Co-Ins Primary doctor Other doctors Emergency room Hospital services 5100 C $6,600 6,600 $ 16 17 18 19 20 0% Total Premium Expense Total Medical Payments Total Medical Cost Cost after limit to MAX out-of- Pocket Potential Cost 23 24 25 26 27 28 Type AmountCopayment Deductible Coinsurance to Insured Primary Doctar Cardiologist Office visit Urolagist Office Visit Primary Doctor Emergency Room & Lab tests Dermatolagist affice visit Primary Doctor Urolagist office visit Primary Doctor Eye Doetor exarn Primary Doctor February February February B 251 B $147 93 C $ 1,707 31 32 80 B S545 80 B S175 34 uly B $250 36 37 38 39 August Physical Therapist office visit Septembe Physical Therapist afficevisit October DecemberPrimary Doctar December Emergency Room & CT scan December Primary Doctor D $ 53,816 A S133 $ 4,300 Surgery & Hospital stay 43 Budget CashFlow S Summary Ca wActivity CatastropheCoverageWithSurgery PremiumCoverageWithSurgery UnitedHeathcare Gold Compass Plus 500 Single Male Age 22 -Monthly premium $230-Deductible $500 Out-of-pocket Maximum $6,600 Copayment Primary dactor $20 Copayment Other Doctor-$40 Co-Payment Energency Raam-$250 Co-insurance Emergency Room-20% after deductible al Services-20% coin rance after deductible $230 6,600 10 Monthly premium Maximum out-of 12 13 14 15 Type Deductible Co Pay Primary doctor Other doctors Emerzency room $500$ 500 $ 250 service 20% 18 19 20 21 Total Premium Expense Total Medical Payments Total Medical Cost Potential Cost Cost after limit to Type Amount Copayment Deductible Coinsurance to Insured MAX out-of-Pocket 23 24 25 Month Description anuary Primary Doctor C $ 1,707 Emergency Room& Lab tests Dermatolopist office visit Prinary Doctar Urolopist office visit Prirmary Doctor Fyr Dactor exam Primary Doctor Physical Therapist affice visit 28 $ 545 June $ 31 32 175 250 34 December Primary Doctor 80 Totals 36 37 39 40 43 Budget CashFlow Summary CatastropheCoverageLowActivity CatastropheCoverageWithSurgery PremiumCoverageLowActivity PremiumCoverageWithSurgery UnitedHealthcare Gold Compass Plus S00 Single Adult Age 22 -Monthly premium $230 Deductible $500-Out-ofpocket Maximum $6,600 Capayment Primary doctar $20 Copayment Specialist-$40 Ca-Payment Emergency Raam-$250 plus cost of service up to deductible Co-insurance Emereency Room-20% after deductible Hospital Services-20% coinsurance after deductible 10 Monthly premium Maximum out-of-pocket $230 6,600 12 Type DeductibleCo-Pay Co-Ins Primary doctar Other doctors Ernergency room Hospital services C S500 $ 16 17 18 250 500 20% Total Premium Expense Total Medical Payments Total Medical Cost 21 Cost after limit to MAXout-of- Pocket Clalm towards Potential Cost 23 Description Type AmountCopayment Deductible Coinsurance to Insured 24 Primary Doetor 26 27 28 29 February Cardoloeist Office visit FebruaryUralogist Office Visit February Primary Doctor $ 251 B $ 147 C 1,707 B366 Ernergency Room&Lab tests Dermatoloist office visit Primary Doetor Urologist office visit Primary Doetor Eye Doctor exam Primary Doetor Physical Therapist ofi ce visit 31 B 5 545 $ 175 $ 250 B $420 D 53,816 $ 133 C 4,300 37 September Physical Therapist officevisit October Sureery & Hospital stay Decernber Primary Doctor December Emereency Room & CT scan Decernber Primary Doctor 39 43 Budget CashFlow Summary CatastropheCoverageWithSurgery PremiumCoverageWithSurgery