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

19 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 Calculate the total premium cost in cell E19 of the CatastropheCoverageWithSurgery worksheet. The total premium cost is the monthly premium amount times 12.

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

26 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 Calculate the total premium cost in cell E19 of the PremiumCoverageLowActivity worksheet. The total premium cost is the monthly premium amount times 12.

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

Budget Enable Macros to Begin Assignment CashFlow Summary file relies on a set of macros to handle submission and scoring. Before This assignment you may begin the assignment, you must enable macros in this workbook. Depending CatastropheCoverageLowActivity the version of Excel and the security settings, you may see a prompt similar to those below.CatastropheCoverageWithSurgery If you have already dismissed the prompt, you may need to close this workbook PremiumCoverageLowActivity and open it again. PremiumCoverageWithSurgery Prepare a personal budget for one month. Be sure to think through your sources of income and expenses. Be as as you wish; but include cells that calculate the following: total income (which calculates the sum of all income s total expenses (which calculates the sum of all expenses), and monthly savings (which calculates the difference the total income and expenses). After completing your budget, complete the tasks to create named ranges that reference the important cells outlined above. 856560 839600 16960 ome and expenses. Be as detailed s the sum of all income sources), alculates the difference between reate named ranges that Cash Flow Budget Worksheet Monthly Interest Rate 1.5% Line of Credit Needed December Beginning Cash Balance January February March $5,000.00 Cash Inflows (Income): Customer Payments Cash Sales Total Cash Inflows $35,000.00 $45,000.00 $53,000.00 $52,000.00 $62,000.00 $73,500.00 $87,000.00 $107,000.00 $126,500.00 Cash Outflows (Expenses): Advertising Loan Interest Insurance Inventory Purchases Office Supplies Payroll Rent or Lease Utilities & Telephone Total Expenses $20,000.00 $30,000.00 $35,000.00 $4,500.00 $46,000.00 $3,200.00 $11,000.00 $12,000.00 $2,700.00 $99,400.00 $4,500.00 $54,500.00 $3,200.00 $13,500.00 $12,000.00 $3,100.00 $125,800.00 Cash Flows (Net): -$12,400.00 -$10,700.00 $700.00 $6000.00* -$7,400.00 -$10,700.00 $700.00 Shortfall/Surplus Cash $4,500.00 $53,000.00 $3,200.00 $12,000.00 $12,000.00 $3,000.00 $117,700.00 Loan Calculatations Additional Cash Needed Cash Available to Retire Debt Cash Used to Retire Debt Loan Balance** $0.00 $6,000.00 $1,000.00 $0.00 Ending Cash Balance $5,000.00 * Note: there was a loan balance of $1000 in November which was paid to $0 with the cash surplus in December. Th value in January is $1000 less than the ending cash in December. ** A loan taken out in any given month cannot be paid off in that month. The earliest a loan will be paid off will be t heet Complete the statement of cash flow for January through June by completing the tasks. As part of the process you will need to use a circular reference. You will need to set Excel to handle circular references. April May June $64,680.00 $76,540.00 $90,540.00 $86,000.00 $100,600.00 $117,700.00 $150,680.00 $177,140.00 $208,240.00 $40,000.00 $50,000.00 $60,000.00 $4,500.00 $59,800.00 $3,500.00 $18,100.00 $12,000.00 $3,200.00 $141,100.00 $4,500.00 $71,900.00 $4,000.00 $17,700.00 $12,000.00 $3,500.00 $163,600.00 $4,500.00 $83,300.00 $4,000.00 $23,200.00 $12,000.00 $5,000.00 $192,000.00 $9,580.00 $13,540.00 $16,240.00 $9,580.00 $13,540.00 $16,240.00 ash surplus in December. This is why the beginning cash oan will be paid off will be the next month. nuary through June cess you will need to et Excel to handle Summary of Medical Costs to Insured Alternative Scenarios No Activity Low Activity One Surgery No Insurance Catastrophe Coverage Premium Coverage Summary of Gross Profit of Insurance Company Alternative Scenarios No Activity Low Activity One Surgery No Insurance Catastrophe Coverage Premium Coverage The Patient Protection and Affordable Care Act, commonly called the Affordable Care Act (ACA) or Obamacare, w President Barrack Obama in March of 2010. The goals of ACA were to improve quality and affordability of health number of people nationally without health insurance, and reduce the overall cost of health care. The ACA requ health insurance. Those who do not have health insurance as part of their employer provided benefits package their own. Selecting the appropriate health insurance plan can be difficult, because insurance policies can be co pay monthly premiums to maintain their health insurance coverage. Those who are covered under a health insu related services, like going to the doctor or having surgery. The insurance plan specifies the amount that the pati This price is less than the price the medical provider (i.e. the doctor) charges for their services. The patient pays medical provider at the time of service. The insurance company pays the difference between the actual cost of by the patient. Not all medical services are treated the same by insurance companies. Some services require the amount) for the services rendered. Other services require the patient to pay a percentage (coinsurance) of the t rendered. Finally, insurance plans limit the total amount that a person pays a year for some services. This is calle deductible is reached, the insurance pays 100% of the cost of those services. When shopping for an insurance p the plan's premiums, copays, coinsurance, and deductible amounts. Some people go to the doctor more than ot the doctor more than others benefit from plans with higher premiums and lower copay, coinsurance, and deduc go to the doctor, benefit from plans with higher copay, coinsurance, and deductible amounts and lower premium workbook to compare different insurance plan options and the amount of medical services a consumer expects buying decision. Care Act (ACA) or Obamacare, was signed into federal law by ality and affordability of health insurance coverage, reduce the st of health care. The ACA requires that everyone must have yer provided benefits package must purchase health insurance on se insurance policies can be confusing and expensive. Individuals re covered under a health insurance plan save money on healthecifies the amount that the patient will pay for a specific service. heir services. The patient pays the specified amount to the nce between the actual cost of the service and the amount paid nies. Some services require the patient to pay a copay (flat rcentage (coinsurance) of the total charges for the services r for some services. This is called a deductible. Once the en shopping for an insurance plan; a consumer should consider e go to the doctor more than others. Generally, those who go to copay, coinsurance, and deductible amounts. Those who rarely le amounts and lower premiums. Complete the tasks in this al services a consumer expects to consume impacts the insurance 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 Lab Services - No charge after deductible Monthly premium Maximum out-of-pocket Primary doctor Other doctors Emergency room Hospital services $ $ 160 6,600 Type Deductible A $ 0 B $ 0 C $ 6,600 D $ 6,600 Co-Pay $ 50 $ 100 $ 0 $ 0 Total Premium Expense Total Medical Payments Total Medical Cost Month 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 exam Primary Doctor Physical Therapist office visit Primary Doctor Totals Claim Amount Type A C B A B A B A B A $ $ $ $ $ $ $ $ $ $ 80 1,707 366 80 545 80 175 80 250 80 Copayment maximum $6,600 Co-Ins 0% 0% Payment towards Deductible Potential Cost Coinsurance to Insured Cost after limit to MAX out-ofPocket $ 0 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 Lab Services - No charge after deductible Monthly premium Maximum out-of-pocket Primary doctor Other doctors Emergency room Hospital services $ $ 160 6,600 Type Deductible A $ B $ C $ 6,600 D $ 6,600 Co-Pay $ 50 $ 100 $ $ - Total Premium Expense Total Medical Payments Total Medical Cost Month January February February February March April May May June June July August September October December December December Description 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 Totals Claim Amount Type A B B A C B A B A B A B B D A C A $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 80 251 147 93 1,707 366 80 545 80 175 80 250 420 53,816 133 4,300 80 Copayment maximum $6,600 Co-Ins 0% 0% Payment towards Deductible Potential Cost Coinsurance to Insured Cost after limit to MAX out-ofPocket $ 0 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 Monthly premium Maximum out-of-pocket Primary doctor Other doctors Emergency room Hospital services $ $ 230 6,600 Type Deductible A $ B $ C $ 500 D $ 500 Co-Pay $ 20 $ 40 $ 250 $ - Total Premium Expense Total Medical Payments Total Medical Cost Month 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 exam Primary Doctor Physical Therapist office visit Primary Doctor Totals Claim Amount Type A C B A B A B A B A $ $ $ $ $ $ $ $ $ $ 80 1,707 366 80 545 80 175 80 250 80 Copayment Maximum $6,600 Co-Ins 20% 20% Payment towards Deductible Potential Cost Coinsurance to Insured Cost after limit to MAX out-ofPocket $ 0 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-Insurance Emergency Room - 20% after deductible Hospital Services - 20% coinsurance after deductible Monthly premium Maximum out-of-pocket Primary doctor Other doctors Emergency room Hospital services $ $ 230 6,600 Type Deductible A $ B $ C $ 500 D $ 500 Co-Pay $ 20 $ 40 $ 250 $ - Total Premium Expense Total Medical Payments Total Medical Cost Month January February February February March April May May June June July August September October December December December Description 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 Totals Claim Amount Type A B B A C B A B A B A B B D A C A $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 80 251 147 93 1,707 366 80 545 80 175 80 250 420 53,816 133 4,300 80 Copayment Maximum $6,600 Co-Ins 20% 20% Payment towards Deductible Potential Cost Coinsurance to Insured Cost after limit to MAX out-ofPocket $ 0