Compensation (human resources management)

accursedarena
Home>Homework Answsers>Human Resource Management homework help
EXCELdata

according to the data answer five questions

  • 3 years ago
  • 30
Report issue
files (2)

Week12_Yourhealthcarecosts.docx

“Your Healthcare Costs”

The purpose of this exercise is to help you understand how healthcare plans work. You will be given a spreadsheet, and will be asked to explore different medical outcomes. This will help inform which healthcare plans would work best for you (or your future employees).

Instructions:

Read the case below and answer each question.

IF Statement Reminder:

=IF ( Condition , Value if True, Value if False )

Is D2 = “Gryffindor”?

If True, then give 10 points. If false, give 0 points

Open the Excel spreadsheet. Be mindful of the different colors.

· This color represents your healthcare plan

· This color represents your medical costs

· This color is how much you can expect to pay under your different plans

Answer the following questions.

1. Look at the formula inside the following columns. Pretend you are explaining the formulas to an Excel novice. How would you explain what the formulas in the following cells are doing?

Deductible Paid (B26, C26, or D26)

Remaining Medical Expenses (B28, C28, or D28)

Remaining Medical Expenses You Must Pay (B29, C29, or D29)

Total Expenses (B36, C36, or D36)

2. Experiment with the different example medical costs. Think about your own medical history. Which plan would you prefer and why?

3. Imagine you have an employee (or friend) who has a lot of frequent visits to the same in-network professional (see “Sample Plans Tab” to see different plans). Which would you recommend and why?

4. Imagine you have an employee (or friend) who requires lots of visits to different specialists (some potentially out of network; see “Sample Plans Tab” to see different plans). Which would you recommend and why?

5. Which plan would be the best if you are healthy and expect very few medical costs. Why do you think that is?

image1.gif

HealthCare.xlsx

Calculator

PPO Plan HMO Plan HDHP Example Medical Costs
Blood test $50
Marginal Income tax rate 24% 24% 24% Psychologist (Mental Health) $150
Doctor Checkup $200
Medical Plan Features Specialist Checkup $300
Calendar year deductible 100 100 4,000 Urgent Care Check-in $500
Co-insurance (after deductable) 10% 10% 20% X-Ray $500
Co-pay per doctor/hospital visit 15 15 30 Ambulance $1,000
Annual out of pocket max 6,960 6,960 10,000 ER Visit $2,000
Annual Premium (Annual Fee) 2,342 2,221 1,210 Stomach Pump $10,000
Overnight stay at a hospital $12,500
HSA Plan Features (HDHP Only) Broken Arm Surgery $16,000
Employer HSA Contribution - - 750 Comprehensive Cancer Care $400,000
Employee contribution to HSA (Max is 3650 for individual; 7300 for family) - - 0
Tax Benefit of HSA Contr. - - 0
Total HSA Benefit - - 750
Anticipated expenses charged to you Plan Details (sample plans can be found in next tab)
Number of doctor/hospital visits 1 1 1 Play with these numbers at your discretion - what medical costs can you see yourself (or others) incurring?
Medical expenses charged to you 2,000 2,000 2,000 These are the results you want to look at
Mechanics of Insurance Plan
Medical expenses charged to you 2,000 2,000 2,000
Deductible 100 100 4,000
Deductible Paid 100 100 2,000
Remaining Medical Expenses 1,900 1,900 0
Remaining Medical Expenses You Must Pay 190 190 0
Total Charged to You
Total Medical Expenses You Must Pay 290 290 2,000
Premium Cost 2,342 2,221 1,210
CoPays 15 15 30
Total HSA Benefit (HDHP Only) -750
Total Expenses 2,647 2,526 2,490

Sample Plans

Medical Plans
Horizon PPO Direct2019 Horizon HMO Horizon HDHP Direct HD 4000
In-Network Out of Network In-Network Out of Network In-Network Out of Network
Deductable $100 single $400 single $100 single No Benefits $4000 single $4000 single
$750 family $1000 family $750 family $8000 family $8000 family
Co-insurance 90/10 of expenses after deductable 70/30 of expenses after deductable 90/10 of expenses after deductable No Benefits 80/20 of expenses after deductable 70/30 of expenses after deductable
Co-pay $15 per visit 70/30 of expenses after deductable $15 per visit No Benefits $30 per visit $30 per visit
Out of pocket maximum $6960 single $2000 single $6960 single No Benefits $10000 single $12000 single
$13920 family $5000 family $13920 family $20000 family $24000 family
Yearly Premium $2342 single $2221 single $1210 single
$4077 family $3867 family $2106 family
Note: For HMO plan, your primary care Note: For the HDHP plan, employer contributes $750 to the plan
must approve all medical procedures Note: For the HDHP plan, max individual contribution is $3650; max family contribution is $7300
(except emergency room visits)