Please provide formulas

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 Typeo 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. 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 OType. 3 1.25 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 Typeo 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 6 0.5 7 0.5 In 125 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 125 and paste it down to complete the Potential Cost column. 9 1.25 In Cell 325 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 Costr column. 11 0.5 Use the Sum function in cell E36 of the Catastrophe CoverageLowActivity worksheet to calculate the total of claim amounts. 12 0.5 In cell 336 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 ) 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 Catastrophe CoverageLowActivity 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 Catastrophe CoverageLowActivity 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:225 from the CatastropheCoverageLowActivity worksheet and paste it into the same range on the CatastropheCoverage With Surgery 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:125 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 343 of the CatastropheCoverage With Surgery 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 ) 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 CatastropheCoverage With Surgery 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 CatastropheCoverage With Surgery 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:125 from the CatastropheCoverageLowActivity worksheet and paste it into the same range on the Premium CoverageLowActivity 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:125 of the Premium CoverageLowActivity 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 Premium CoverageLowActivity worksheet to calculate the total of claim amounts. 26 0.5 In cell 336 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 ) for the last claim. 27 0.5 Calculate the total premium cost in cell E19 of the Premium CoverageLowActivity 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 Premium CoverageLowActivity 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:125 from the CatastropheCoverage LowActivity worksheet and paste it into the same range on the Premium Coverage With Surgery 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:125 of the Premium Coverage With Surgery 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 Premium Coverage With Surgery worksheet to calculate the total of claim amounts. 33 0.5 In cell 343 of the Premium Coverage With Surgery 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) for the last claim. 34 0.5 Calculate the total premium cost in cell E19 of the Premium Coverage With Surgery 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 Premium Coverage With Surgery 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 Premium Coverage With Surgery 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. 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 CatastropheCoverageWith Surgery 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 CatastropheCoverage With Surgery 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 Premium CoverageLowActivity 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 Premium CoverageLowActivity 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 Premium Coverage With Surgery 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 CatastropheCoverage With Surgery 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 Premium CoverageLowActivity 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 Premium CoverageLowActivity 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 PremiumCoverage With Surgery 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. Total: 40 A C D E H I 1 1 2 2 3 4 5 United Healthcare 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 Lab Services - No charge after deductible 6 7 7 Monthly premium Maximum out-of-pocket $ $ 160 6,600 Co-ins 9 10 11 12 13 14 15 16 17 18 19 20 21 22 Primary doctor Other doctors Emergency room Hospital services Type Deductible Co-Pay A $ 0 $ B $ 0 S $ $ 6,600 $ D $ 6,600 $ 50 100 0 0% 0% 0 Total Premium Expense Total Medical Payments Total Medical Cost Claim Amount Payment towards Deductible Potential Cost to Insured Month Description Type Copayment Coinsurance Cost after limit to MAX out-of-Pocket $ 0 January March April 80 1,707 366 80 May 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 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 May June June July August December 545 80 A $ C $ B $ A $ B $ A $ B $ A $ B $ A $ 175 80 250 80 Totals Budget CashFlow Summary CatastropheCoverageLowActivity CatastropheCoverage With Surgery Premium CoverageLowActi + A C D E H K L M N O P 1 UnitedHealthcare Catastrophic Compass Plus 6600 Single Adult Age 22 - Monthly premium $160 - Deductible $6,600 - Out-of-pocket maximum $6,500 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 6 7 8 9 Monthly premium Maximum out-of-pocket -- $ $ 160 6,600 Co-ins 10 11 12 13 14 15 16 17 18 19 20 21 22 50 100 Type Deductible Co-Pay $ $ B $ $ $ 6,600 $ D$ 6,600 $ $ Primary doctor Other doctors Emergency room Hospital services 0% 0% Total Premium Expense Total Medical Payments Total Medical Cost 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 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 January February February February March April May May June June July August September October December December December Primary Doctor Cardiologist Office visit Uroloplst 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 B $ 147 A $ 93 C $ 1,707 B$ 366 AS 80 BS 545 AS 80 BS 175 $ 80 B $ 250 B $ 420 D$ 53,816 A $ 133 C$ 4,300 S 80 Totals Budget CashFlow Summary CatastropheCoverageLowActivity CatastropheCoverage With Surgery PremiumCoverageLowActi + A B C C D E H K . N 2 3 4 5 6 UnitedHealthcare Gold Compass Plus 500 Single Male Age 22 - Monthly premium $230-Deductible $500 - Out-of-pocket Maximum $6,600 Copayment Primary doctor - $20 Copayment Other Doctor- $40 Co-Payment Emergency Room - $250 Co-Insurance Emergency Room -20% after deductible Hospital Services - 20% coinsurance after deductible 7 9 10 Monthly premium Maximum out-of-pocket $ $ 230 6,600 Co-Ins 11 12 13 14 15 16 17 18 19 20 21 22 Primary doctor other doctors Emergency room Hospital services Type Deductible Co-Pay A $ $ B $ $ $ 500 $ D $ 500 $ $ 20 40 250 20% 20% Total Premium Expense Total Medical Payments Total Medical Cost Claim Amount Payment towards Deductible Month Description Type Copayment Coinsurance Potential Cost to Cost after limit to Insured MAX out-of-pocket $ 0 A $ $ $ B S S 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 January March April May May June June July August December Primary Doctor Emergency Room & Labtests Dermatologist office visit Primary Doctor Urologist office visit Primary Doctor Eye Doctor exam Primary Doctor Physical Therapist office visit Primary Doctor A B A B A B A 80 1,707 366 80 545 80 175 80 250 80 S S $ S $ Totals Cashflow Summary CatastropheCoverageLowActivity CatastropheCoverage With Surgery Premium CoverageLowActivity Pre + A B D E F G H I J K L M N 0 P 1 2 3 4 One Surgery Summary of Medical Costs to Insured Alternative Scenarios No Activity Low Activity No Insurance Catastrophe Coverage Premium Coverage 5 6 7 8 9 10 11 Summary of Gross Profit of Insurance Company Alternative Scenarios No Activity Low Activity One Surgery No Insurance Catastrophe Coverage Premium Coverage 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 22 33 34 35 36 37 32 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 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 an insurance plan; a consumer should consider the plan's premiums, copays, coinsurance, and deductible amounts. Some people go to the doctor more than others. Generally, those who go to the doctor more than others benefit from plans with higher premiums and lower copay, coinsurance, and deductible amounts. Those who rarely go to the doctor, benefit from plans with higher copay, coinsurance, and deductible amounts and lower premiums. Complete the tasks in this workbook to compare different insurance plan options and the amount of medical services a consumer expects to consume impacts the insurance buying decision. Summary CatastropheCoverageLowActivity CatastropheCoverage With Surgery Premium CoverageLowActivity Premium Coverage + 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 Typeo 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. 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 OType. 3 1.25 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 Typeo 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 6 0.5 7 0.5 In 125 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 125 and paste it down to complete the Potential Cost column. 9 1.25 In Cell 325 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 Costr column. 11 0.5 Use the Sum function in cell E36 of the Catastrophe CoverageLowActivity worksheet to calculate the total of claim amounts. 12 0.5 In cell 336 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 ) 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 Catastrophe CoverageLowActivity 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 Catastrophe CoverageLowActivity 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:225 from the CatastropheCoverageLowActivity worksheet and paste it into the same range on the CatastropheCoverage With Surgery 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:125 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 343 of the CatastropheCoverage With Surgery 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 ) 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 CatastropheCoverage With Surgery 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 CatastropheCoverage With Surgery 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:125 from the CatastropheCoverageLowActivity worksheet and paste it into the same range on the Premium CoverageLowActivity 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:125 of the Premium CoverageLowActivity 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 Premium CoverageLowActivity worksheet to calculate the total of claim amounts. 26 0.5 In cell 336 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 ) for the last claim. 27 0.5 Calculate the total premium cost in cell E19 of the Premium CoverageLowActivity 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 Premium CoverageLowActivity 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:125 from the CatastropheCoverage LowActivity worksheet and paste it into the same range on the Premium Coverage With Surgery 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:125 of the Premium Coverage With Surgery 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 Premium Coverage With Surgery worksheet to calculate the total of claim amounts. 33 0.5 In cell 343 of the Premium Coverage With Surgery 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) for the last claim. 34 0.5 Calculate the total premium cost in cell E19 of the Premium Coverage With Surgery 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 Premium Coverage With Surgery 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 Premium Coverage With Surgery 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. 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 CatastropheCoverageWith Surgery 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 CatastropheCoverage With Surgery 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 Premium CoverageLowActivity 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 Premium CoverageLowActivity 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 Premium Coverage With Surgery 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 CatastropheCoverage With Surgery 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 Premium CoverageLowActivity 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 Premium CoverageLowActivity 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 PremiumCoverage With Surgery 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. Total: 40 A C D E H I 1 1 2 2 3 4 5 United Healthcare 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 Lab Services - No charge after deductible 6 7 7 Monthly premium Maximum out-of-pocket $ $ 160 6,600 Co-ins 9 10 11 12 13 14 15 16 17 18 19 20 21 22 Primary doctor Other doctors Emergency room Hospital services Type Deductible Co-Pay A $ 0 $ B $ 0 S $ $ 6,600 $ D $ 6,600 $ 50 100 0 0% 0% 0 Total Premium Expense Total Medical Payments Total Medical Cost Claim Amount Payment towards Deductible Potential Cost to Insured Month Description Type Copayment Coinsurance Cost after limit to MAX out-of-Pocket $ 0 January March April 80 1,707 366 80 May 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 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 May June June July August December 545 80 A $ C $ B $ A $ B $ A $ B $ A $ B $ A $ 175 80 250 80 Totals Budget CashFlow Summary CatastropheCoverageLowActivity CatastropheCoverage With Surgery Premium CoverageLowActi + A C D E H K L M N O P 1 UnitedHealthcare Catastrophic Compass Plus 6600 Single Adult Age 22 - Monthly premium $160 - Deductible $6,600 - Out-of-pocket maximum $6,500 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 6 7 8 9 Monthly premium Maximum out-of-pocket -- $ $ 160 6,600 Co-ins 10 11 12 13 14 15 16 17 18 19 20 21 22 50 100 Type Deductible Co-Pay $ $ B $ $ $ 6,600 $ D$ 6,600 $ $ Primary doctor Other doctors Emergency room Hospital services 0% 0% Total Premium Expense Total Medical Payments Total Medical Cost 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 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 January February February February March April May May June June July August September October December December December Primary Doctor Cardiologist Office visit Uroloplst 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 B $ 147 A $ 93 C $ 1,707 B$ 366 AS 80 BS 545 AS 80 BS 175 $ 80 B $ 250 B $ 420 D$ 53,816 A $ 133 C$ 4,300 S 80 Totals Budget CashFlow Summary CatastropheCoverageLowActivity CatastropheCoverage With Surgery PremiumCoverageLowActi + A B C C D E H K . N 2 3 4 5 6 UnitedHealthcare Gold Compass Plus 500 Single Male Age 22 - Monthly premium $230-Deductible $500 - Out-of-pocket Maximum $6,600 Copayment Primary doctor - $20 Copayment Other Doctor- $40 Co-Payment Emergency Room - $250 Co-Insurance Emergency Room -20% after deductible Hospital Services - 20% coinsurance after deductible 7 9 10 Monthly premium Maximum out-of-pocket $ $ 230 6,600 Co-Ins 11 12 13 14 15 16 17 18 19 20 21 22 Primary doctor other doctors Emergency room Hospital services Type Deductible Co-Pay A $ $ B $ $ $ 500 $ D $ 500 $ $ 20 40 250 20% 20% Total Premium Expense Total Medical Payments Total Medical Cost Claim Amount Payment towards Deductible Month Description Type Copayment Coinsurance Potential Cost to Cost after limit to Insured MAX out-of-pocket $ 0 A $ $ $ B S S 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 January March April May May June June July August December Primary Doctor Emergency Room & Labtests Dermatologist office visit Primary Doctor Urologist office visit Primary Doctor Eye Doctor exam Primary Doctor Physical Therapist office visit Primary Doctor A B A B A B A 80 1,707 366 80 545 80 175 80 250 80 S S $ S $ Totals Cashflow Summary CatastropheCoverageLowActivity CatastropheCoverage With Surgery Premium CoverageLowActivity Pre + A B D E F G H I J K L M N 0 P 1 2 3 4 One Surgery Summary of Medical Costs to Insured Alternative Scenarios No Activity Low Activity No Insurance Catastrophe Coverage Premium Coverage 5 6 7 8 9 10 11 Summary of Gross Profit of Insurance Company Alternative Scenarios No Activity Low Activity One Surgery No Insurance Catastrophe Coverage Premium Coverage 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 22 33 34 35 36 37 32 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 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 an insurance plan; a consumer should consider the plan's premiums, copays, coinsurance, and deductible amounts. Some people go to the doctor more than others. Generally, those who go to the doctor more than others benefit from plans with higher premiums and lower copay, coinsurance, and deductible amounts. Those who rarely go to the doctor, benefit from plans with higher copay, coinsurance, and deductible amounts and lower premiums. Complete the tasks in this workbook to compare different insurance plan options and the amount of medical services a consumer expects to consume impacts the insurance buying decision. Summary CatastropheCoverageLowActivity CatastropheCoverage With Surgery Premium CoverageLowActivity Premium Coverage +