Can someone help me with this assignment please?

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

Reuse your formula in cell F25 and paste it down to complete the "Copayment" column of the table

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.

Reuse your formula in cell G25 and paste it down to complete the Payment towards Deductible column

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.

Reuse your formula in cell H25 and paste it down to complete the Coinsurance column

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.

Reuse your formula in cell I25 and paste it down to complete the Potential Cost column

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.

Reuse your formula in cell J25 and paste it down to complete the Total Cost column

Use the Sum function in cell E36 of the CatastropheCoverageLowActivity worksheet to calculate the total of claim amounts

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.

Calculate the total premium cost in cell E19 of the CatastropheCoverageLowActivity worksheet. The total premium cost is the monthly premium amount times 12.

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.

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.

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.

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.

Use the Sum function in cell E43 of the CatastropheCoverageWithSurgery worksheet to calculate the total of claim amounts.

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.

Calculate the total premium cost in cell E19 of the CatastropheCoverageWithSurgery worksheet. The total premium cost is the monthly premium amount times 12.

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

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

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.

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.

Use the Sum function in cell E36 of the PremiumCoverageLowActivity worksheet to calculate the total of claim amounts.

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.

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.

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.

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.

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.

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.

Use the Sum function in cell E43 of the PremiumCoverageWithSurgery worksheet to calculate the total of claim amounts.

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.

Calculate the total premium cost in cell E19 of the PremiumCoverageWithSurgery worksheet. The total premium cost is the monthly premium amount times 12

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

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.

In cell C4 of the Summary worksheet enter 0. If you buy no insurance and have no health activity, you have spent nothing.

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.

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.

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

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.

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.

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

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.

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.

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.

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.

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.

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

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.

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

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.

File Home Insert Page Layout Formulas Data Review View Help Power Pivot Tell me what you want to do Instruction Task Submit View Report Sheet Guide Show Instructions Automatic Scoring F25 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 Doctor $100 Emergency Room-No charge after deductible Hospital Services- No charge after deductible Monthly premium Maximum out-of-pocket $160 $ 6,600 13 Type Deductible Co-Pay Co-Ins Primary doctor Other doctors Emergency room B S0100 $ 6,600 $ D S 6,600$ 15 17 ital services 20 21 Total Premium Expense Total Medical Payments Total Medical Cost Cost after limit Budget CashFlow Summary CatastropheCoverageLowActivty CatastropheCoverageWithSun Ready Average: $1,669 Count: 13 Sum: $13,350 - + 100% File Home Insert Page Layout Formulas Data Review View Help Power Pivot Tell me what you want to do Share Instruction Task Submit View Sheet Guide Show Instructions Automatic Scoring F25 Total Premium Expense Total Medical Payments Total Medical Cost Cost after limit to MAX out-of Pocket Claim Potential Cost Month Description Type Amount Copayment Deductible Coinsurance to Insured 24 January Primary Doctor 26 27 March Emergency Room&Lab tests Apri Dermatologist office visit C 1,707 B 366 B $545 B 175 B $250 Primary Doctor May Urologist office visit June Primary Doctor Eye Doctor exam Primary Doctor Physical Therapist office visit July August December Primary Doctor Totals 37 Budget CashFlow Summary CatastropheCoveragelo Activity CatastrophecoverageWithSun + Ready Average: $1,669 Count: 13 Sum: $13,350 100% File Home Insert Page LayoutFormulas DataReviewView Help Power Pivot Assignment Tell me what you want to do Share Instruction Task Submit View Sheet Guide Show Instructions Automatic Scoring Cost after limit to MAX out-of Pocket Claim towards Potential Cost 23 24 Month Description Type Amount Copayment Deductible Coinsurance to Insured anuary Primary Doctor FebruaryCardiologist Office visit FebruaryUrologist Office Visit February Primary Doctor 26 27 $ 251 B $147 March Emergency Room &Lab tests Apr Dermatologist office visit May Primary Doctor May Urologist office visit June Primary Doctor C $1,70 B $366 B $545 B $175 B 250 31 Eye Doctor exam Primary Doctor Physical Therapist office visit Physical Therapist office visit July August 35 September B $420 October Surgery&Hospital stay December Primary Doctor December Emergency Room &CT scan DecemberPrimary Doctor D 53,816 39 A $133 $4,300 Ready 100% File Home Insert Page LayoutFormulas DataReviewView Help Power Pivot Assignment Tell me what you want to do Share Instruction Task Submit View Sheet Guide Show Instructions Automatic Scoring E20 Cost after limit Potential Cost to MAX out-of- Claim Month Description Type Amount Copayment Deductible Coinsurance to Insured 24 anuary Primary Doctor March Emergency Room &Lab tests Apri Dermatologist office visit Ma May Urologist office visit June Primary Doctor C 1,70 26 27 28 29 30 B 366 B $ 545 B S175 B 250 Primary Doctor July August Eye Doctor exam Primary Doctor Physical Therapist office visit December Primary Doctor 80 35 36 37 38 Totals 41 catastrophecoverageWithSurgery PremiumCoveragelo Activity PremiumCoverageWithSurgery D Ready 100% File Home Insert Page LayoutFormulas DataReview View Help Power Pivot Tell me what you want to do Share Instruction Task Submit View Report Sheet Guide Show Instructions Automatic Scoring E19 UnitedHealthcare Gold Compass Plus 500 Single Adult Age 22- Monthly premium $230- Deductible $500-Out-of-pocket Maximum $6,600 Copayment Primary doctor-$20 Copayment Specialist-$40 Co-Payment Emergency Room-$250 plus cost of service up to deductible Co-Insu Hospital Services-20% coinsurance after deductible rance Emergency Room-20% after deductible 10 Monthly premium Maximum out-of-pocket $ 230 6,600 12 Type Deductible Co-Pay Co-Ins Primary doctor Other doctors Emergency room Hospital services 20 15 C $500250 D S500 17 20% 19 Total Premium Expense Total Medical Payments Total Medical Cost 20 Cost after limit 4 Ready PremiumCoveragelowActivity PremiumCoverageWithSurgery 100% File Home Insert Page LayoutFormulas DataReviewView Help Power Pivot Assignment Tell me what you want to do Share Instruction Task Submit View Report Sheet Guide Show Instructions Automatic Scoring E19 Potential Cost Cost after limit Type Amount Copayment towards Coinsurance to Insured to MAX out-of Claim Description 24 26 27 28 ryPrimary Doctor February Cardiologist Office visit February Urologist Office Vist February Primary Doctor $ 251 $ 147 A S93 C 1,707 B $366 March Emergency Room &Lab tests Apri Dermatologist office visit May Primary Doctor May Urologist office visit June Primary Doctor June July Primary Doctor AugustPhysical Therapist office visit B 545 Eye Doctor exam B S175 B 250 $ 420 D 53,816 A 133 C $ 4,300 37 September October December December December Physical Therapist office visit Surgery&Hospital stay Primary Doctor Emergency Room &CT scan Primary Doctor 39 Totals ..PremiumCoverageLowActivity PremiumCoverageWithSurgery Ready -+ 100% File Home Insert Page Layout Formulas DataReview View Help Power Pivot ?Tell me what you want to do Instruction Task Submit View Report Sheet Guide Show Instructions Automatic Scoring E12 of Medical Costs to Insured Alternative Scenarios No Activ Low Activity One Sur Catastrophe Coverage Premium Coverage Summary of Gross Profit of Insurance Com Alternative Scenarios No Activ No Insurance Catastrophe Coverage Premium Coverage Low Activity One Sur 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 comnanv between the actual cost af the service and the amount naid hy the natient, Not all medi Budget CasowSummary CatastropheCoveragelowActivity CatastropheCoverageWithsun.. Ready +100%