Please show the formulas!!

E19 . x foc A B C D H 1 K 1 2 3 5 6 7. United Healthcare Catastrophic Compass Plus 5500 Single Adult Age 22 - Monthly premium $160-Deductible $5,800 - 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 Monthly premium Maximum out of pocket $ $ 10 11 12 13 160 6,600 Coins 15 16 17 Primary doctor Other doctors Emergency room Hospital services Type Deductible Co Pay A $ $ $ 6,800 $ $ 6,800 $ SO 100 0 06 06 0 19 20 21 Total Premium Expense Total Medical Payments Total Medical Cost Payment towards Deductible Claim Type Amount Potential Cost to insured Cost after limit to MAX out of Pocket 0 23 Month Copayment Coinsurance 25 26 27 A C January March April May May June June July August December Description Primary Doctor Emergency Room & lab tests Dermatologist office visit Primary Doctor Urologist office visit Primary Doctor Eye Doctor cam Primary Doctor Physical Therapist office visit Primary Doctor nama $ $ $ $ $ $ $ $ $ $ 80 1,707 355 80 545 80 175 80 250 80 30 31 32 33 nom A 35 Totals 37 38 39 40 41 42 Health Insurance Use a VLOOKUP function in cell F25 of the CatastropheCoverageLow Activity 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 [1 point] 1 Health Insurance Reuse your formula in cell F25 and paste it down to complete the "Copayment" column of the table. (0.5 points) 3 In cell G25 of the CatastropheCoverageLow Activity 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". (1.25 points] Health Insurance Reuse your formula in cell G25 and paste it down to complete the "Payment towards Deductible" column. 10.5 points 4 Health Insurance Coinsurance is a percentage applied to the amount of the claim in excess of the deductible. In cell H25 of the Catastrophe CoverageLow Activity 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". (1.25 points] 5 Health Insurance Reuse your formula in cell H25 and paste it down to complete the "Coinsurance column (0.5 points) 6 Health Insurance In 125 of the Catastrophe CoverageLow Activity 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. [0.5 points] 7 Health Insurance Reuse your formula in cell 125 and paste it down to complete the "Potential Cost" column. [0.25 points) 8 Health Insurance In Cell 325 of the CatastropheCoverageLow Activity 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. [1.25 points] 9 10 Health Insurance Reuse your formula in cell J25 and paste it down to complete the "Total Cost..." column. [0.5 points) Health Insurance Use the Sum function in cell E36 of the Catastrophe CoverageLow Activity worksheet to calculate the total of claim amounts, 10.5 points 11 Health Insurance In cell 136 of the Catastrophe CoverageLow Activity worksheet, calculate the total of costs 12 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. (0.5 points] Health Insurance Calculate the total premium cost in cell E19 of the Catastrophe CoverageLow Activity worksheet. The total premium cost is the monthly premium amount times 12. (0.5 points] 13 Health Insurance Calculate the total medical payments in cell E20 of the Catastrophe CoverageLow Activity worksheet. The total medical payments are equal to the total cost after limit to MAX out-of- pocket. (0.5 points) 14 Health Insurance Calculate the total medical cost in cell E21 of the Catastrophe CoverageLowActivity 15 worksheet. The total medical cost is equal to the sum of the total premium expense and the total medical payments. (0.75 points] Health Insurance Copy the range F25:125 from the Catastrophe CoverageLow Activity 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. (1.25 points) 16 Health Insurance Reuse your formulas in cells F25:125 of the Catastrophe Coverage With Surgery worksheet and paste them down to row 41 to complete the "Copayment" through "Cost after limit..." columns of the table. (1.25 points) 17 Health Insurance Use the Sum function in cell E43 of the CatastropheCoverageWithSurgery worksheet to calculate the total of claim amounts. [0.5 points) 18 E19 . x foc A B C D H 1 K 1 2 3 5 6 7. United Healthcare Catastrophic Compass Plus 5500 Single Adult Age 22 - Monthly premium $160-Deductible $5,800 - 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 Monthly premium Maximum out of pocket $ $ 10 11 12 13 160 6,600 Coins 15 16 17 Primary doctor Other doctors Emergency room Hospital services Type Deductible Co Pay A $ $ $ 6,800 $ $ 6,800 $ SO 100 0 06 06 0 19 20 21 Total Premium Expense Total Medical Payments Total Medical Cost Payment towards Deductible Claim Type Amount Potential Cost to insured Cost after limit to MAX out of Pocket 0 23 Month Copayment Coinsurance 25 26 27 A C January March April May May June June July August December Description Primary Doctor Emergency Room & lab tests Dermatologist office visit Primary Doctor Urologist office visit Primary Doctor Eye Doctor cam Primary Doctor Physical Therapist office visit Primary Doctor nama $ $ $ $ $ $ $ $ $ $ 80 1,707 355 80 545 80 175 80 250 80 30 31 32 33 nom A 35 Totals 37 38 39 40 41 42 Health Insurance Use a VLOOKUP function in cell F25 of the CatastropheCoverageLow Activity 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 [1 point] 1 Health Insurance Reuse your formula in cell F25 and paste it down to complete the "Copayment" column of the table. (0.5 points) 3 In cell G25 of the CatastropheCoverageLow Activity 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". (1.25 points] Health Insurance Reuse your formula in cell G25 and paste it down to complete the "Payment towards Deductible" column. 10.5 points 4 Health Insurance Coinsurance is a percentage applied to the amount of the claim in excess of the deductible. In cell H25 of the Catastrophe CoverageLow Activity 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". (1.25 points] 5 Health Insurance Reuse your formula in cell H25 and paste it down to complete the "Coinsurance column (0.5 points) 6 Health Insurance In 125 of the Catastrophe CoverageLow Activity 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. [0.5 points] 7 Health Insurance Reuse your formula in cell 125 and paste it down to complete the "Potential Cost" column. [0.25 points) 8 Health Insurance In Cell 325 of the CatastropheCoverageLow Activity 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. [1.25 points] 9 10 Health Insurance Reuse your formula in cell J25 and paste it down to complete the "Total Cost..." column. [0.5 points) Health Insurance Use the Sum function in cell E36 of the Catastrophe CoverageLow Activity worksheet to calculate the total of claim amounts, 10.5 points 11 Health Insurance In cell 136 of the Catastrophe CoverageLow Activity worksheet, calculate the total of costs 12 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. (0.5 points] Health Insurance Calculate the total premium cost in cell E19 of the Catastrophe CoverageLow Activity worksheet. The total premium cost is the monthly premium amount times 12. (0.5 points] 13 Health Insurance Calculate the total medical payments in cell E20 of the Catastrophe CoverageLow Activity worksheet. The total medical payments are equal to the total cost after limit to MAX out-of- pocket. (0.5 points) 14 Health Insurance Calculate the total medical cost in cell E21 of the Catastrophe CoverageLowActivity 15 worksheet. The total medical cost is equal to the sum of the total premium expense and the total medical payments. (0.75 points] Health Insurance Copy the range F25:125 from the Catastrophe CoverageLow Activity 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. (1.25 points) 16 Health Insurance Reuse your formulas in cells F25:125 of the Catastrophe Coverage With Surgery worksheet and paste them down to row 41 to complete the "Copayment" through "Cost after limit..." columns of the table. (1.25 points) 17 Health Insurance Use the Sum function in cell E43 of the CatastropheCoverageWithSurgery worksheet to calculate the total of claim amounts. [0.5 points) 18