I need help with the formulas for the following excel assignment

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 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.

G9 A B D E F G H K L M N o Q 1 2 One Surgery 3 4. 5 6 Summary of Medical Costs to Insured Alternative Scenarios No Activity Low Activity No Insurance Catastrophe Coverage Premium Coverage 7 8 9 10 Summary of Gross Profit of Insurance Company Alternative Scenarios No Activity Low Activity One Surgery No Insurance Catastrophe Coverage Premium Coverage 11 12 13 14 15 16 17 18 19 20 The Patient Protection and Affordable Care Act, commonly called the Affordable Care Act (ACA) or Obamacare, was signed into federal law by President Barrack Obama in March of 2010. The goals of ACA were to improve quality and affordability of health insurance coverage, reduce the number of people nationally without health insurance, and reduce the overall cost of health care. The ACA requires that everyone must have health insurance. Those who do not have health insurance as part of their employer provided benefits package must purchase health insurance on their own. Selecting the appropriate health insurance plan can be difficult, because insurance policies can be confusing and expensive. Individuals pay monthly premiums to maintain their health insurance coverage. Those who are covered under a health insurance plan save money on health-related services, like going to the doctor or having surgery. The insurance plan specifies the amount that the patient will pay for a specific service. This price is less than the price the medical provider (i.e. the doctor) charges for their services. The patient pays the specified amount to the medical provider at the time of service. The insurance company pays the difference between the actual cost of the service and the amount paid by the patient. Not all medical services are treated the same by insurance companies. Some services require the patient to pay a copay (flat amount) for the services rendered. Other services require the patient to pay a percentage (coinsurance) of the total charges for the services rendered. Finally, insurance plans limit the total amount that a person pays a year for some services. This is called a deductible. Once the deductible is reached, the insurance pays 100% of the cost of those services. When shopping for Cash Flow Summary CatastropheCoveragelowActivity CatastropheCoverageWith Surgery Pr... + 21 22 23 24 25 26 B D F F H M N o P Q R S T 1 2 3 4 5 6 7 8 9 UnitedHealthcare Catastrophic Compass Plus 6600 Single Adult Age 22 - Monthly premium $160 - Deductible $6,600 - Out-of-pocket maximum $5,600 Copayment Primary doctor - $50 Copayment Specialist Doctor $100 Emergency Room - No charge after deductible Hospital Services - No charge after deductible Lab Services - No charge after deductible 10 Monthly premium Maximum out-of-pocket $ $ 160 6,600 11 12 13 14 15 Co-Ins Primary doctor Other doctors Emergency room Hospital services 50 100 Type Deductible Co-Pay A $ 0 $ B $ 0 $ C $ 6,600 $ D $ 6,600 $ 0 16 17 18 0% 0% 0 19 20 21 Total Premium Expense Total Medical Payments Total Medical Cost 22 Claim Amount Payment towards Copayment Deductible Potential Cost to Insured Cost after limit to MAX out-of- Pocket $ 0 Month Description Type Coinsurance 23 24 25 26 A 80 1,707 C January March April May May B 27 28 29 30 A 366 80 545 Primary Doctor Emergency Room & Lab tests Dermatologist office visit Primary Doctor Urologist office visit Primary Doctor Eye Doctor exam Primary Doctor Physical Therapist office visit Primary Doctor $ $ $ $ $ $ $ $ $ $ June A 80 B 175 80 A 31 32 33 34 June July August December B 250 80 A 35 36 Totals Cash Flow Summary CatastropheCoverageLowActivity CatastropheCoverageWithSurgery Pr ... + J20 fic A D E F H K L M N. o Q R S T B Lab Services - No charge after deductible 8 9 Monthly premium Maximum out-of-pocket $ $ 160 6,600 10 11 12 13 14 Co-Ins Primary doctor Other doctors Emergency room Hospital services Type Deductible Co-Pay A $ - S B $ S C $ 6,600 S D $ 6,600 S 50 100 0% 0% 15 16 17 18 19 20 21 Total Premium Expense Total Medical Payments Total Medical Cost 22 Month Claim Amount Payment towards Copayment Deductible Coinsurance Potential Cost to Insured Cost after limit to MAX out-of- Pocket $ 0 Month Description Type A B B A C B A B 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 January February February February March April May May June June July August September October December December December Primary Doctor Cardiologist Office visit Urologist Office Visit Primary Doctor Emergency Room & Lab tests Dermatologist office visit Primary Doctor Urologist office visit Primary Doctor Eye Doctor exam Primary Doctor Physical Therapist office visit Physical Therapist office visit Surgery & Hospital stay Primary Doctor Emergency Room & CT scan Primary Doctor A $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 80 251 147 93 1,707 366 80 545 80 175 80 250 420 53,816 133 4,300 80 B A B B D C A Totals Cash Flow Summary CatastropheCoverageLowActivity CatastropheCoverageWithSurgery Pr ... G9 A B D E F G H K L M N o Q 1 2 One Surgery 3 4. 5 6 Summary of Medical Costs to Insured Alternative Scenarios No Activity Low Activity No Insurance Catastrophe Coverage Premium Coverage 7 8 9 10 Summary of Gross Profit of Insurance Company Alternative Scenarios No Activity Low Activity One Surgery No Insurance Catastrophe Coverage Premium Coverage 11 12 13 14 15 16 17 18 19 20 The Patient Protection and Affordable Care Act, commonly called the Affordable Care Act (ACA) or Obamacare, was signed into federal law by President Barrack Obama in March of 2010. The goals of ACA were to improve quality and affordability of health insurance coverage, reduce the number of people nationally without health insurance, and reduce the overall cost of health care. The ACA requires that everyone must have health insurance. Those who do not have health insurance as part of their employer provided benefits package must purchase health insurance on their own. Selecting the appropriate health insurance plan can be difficult, because insurance policies can be confusing and expensive. Individuals pay monthly premiums to maintain their health insurance coverage. Those who are covered under a health insurance plan save money on health-related services, like going to the doctor or having surgery. The insurance plan specifies the amount that the patient will pay for a specific service. This price is less than the price the medical provider (i.e. the doctor) charges for their services. The patient pays the specified amount to the medical provider at the time of service. The insurance company pays the difference between the actual cost of the service and the amount paid by the patient. Not all medical services are treated the same by insurance companies. Some services require the patient to pay a copay (flat amount) for the services rendered. Other services require the patient to pay a percentage (coinsurance) of the total charges for the services rendered. Finally, insurance plans limit the total amount that a person pays a year for some services. This is called a deductible. Once the deductible is reached, the insurance pays 100% of the cost of those services. When shopping for Cash Flow Summary CatastropheCoveragelowActivity CatastropheCoverageWith Surgery Pr... + 21 22 23 24 25 26 B D F F H M N o P Q R S T 1 2 3 4 5 6 7 8 9 UnitedHealthcare Catastrophic Compass Plus 6600 Single Adult Age 22 - Monthly premium $160 - Deductible $6,600 - Out-of-pocket maximum $5,600 Copayment Primary doctor - $50 Copayment Specialist Doctor $100 Emergency Room - No charge after deductible Hospital Services - No charge after deductible Lab Services - No charge after deductible 10 Monthly premium Maximum out-of-pocket $ $ 160 6,600 11 12 13 14 15 Co-Ins Primary doctor Other doctors Emergency room Hospital services 50 100 Type Deductible Co-Pay A $ 0 $ B $ 0 $ C $ 6,600 $ D $ 6,600 $ 0 16 17 18 0% 0% 0 19 20 21 Total Premium Expense Total Medical Payments Total Medical Cost 22 Claim Amount Payment towards Copayment Deductible Potential Cost to Insured Cost after limit to MAX out-of- Pocket $ 0 Month Description Type Coinsurance 23 24 25 26 A 80 1,707 C January March April May May B 27 28 29 30 A 366 80 545 Primary Doctor Emergency Room & Lab tests Dermatologist office visit Primary Doctor Urologist office visit Primary Doctor Eye Doctor exam Primary Doctor Physical Therapist office visit Primary Doctor $ $ $ $ $ $ $ $ $ $ June A 80 B 175 80 A 31 32 33 34 June July August December B 250 80 A 35 36 Totals Cash Flow Summary CatastropheCoverageLowActivity CatastropheCoverageWithSurgery Pr ... + J20 fic A D E F H K L M N. o Q R S T B Lab Services - No charge after deductible 8 9 Monthly premium Maximum out-of-pocket $ $ 160 6,600 10 11 12 13 14 Co-Ins Primary doctor Other doctors Emergency room Hospital services Type Deductible Co-Pay A $ - S B $ S C $ 6,600 S D $ 6,600 S 50 100 0% 0% 15 16 17 18 19 20 21 Total Premium Expense Total Medical Payments Total Medical Cost 22 Month Claim Amount Payment towards Copayment Deductible Coinsurance Potential Cost to Insured Cost after limit to MAX out-of- Pocket $ 0 Month Description Type A B B A C B A B 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 January February February February March April May May June June July August September October December December December Primary Doctor Cardiologist Office visit Urologist Office Visit Primary Doctor Emergency Room & Lab tests Dermatologist office visit Primary Doctor Urologist office visit Primary Doctor Eye Doctor exam Primary Doctor Physical Therapist office visit Physical Therapist office visit Surgery & Hospital stay Primary Doctor Emergency Room & CT scan Primary Doctor A $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 80 251 147 93 1,707 366 80 545 80 175 80 250 420 53,816 133 4,300 80 B A B B D C A Totals Cash Flow Summary CatastropheCoverageLowActivity CatastropheCoverageWithSurgery Pr