Compensation (human resources management)
accursedarenaaccording to the data answer five questions
3 years ago
30
files (2)Week12_Yourhealthcarecosts.docx
HealthCare.xlsx
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) |
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
- accounting
- Short Discussion Question on Transforming Thinking
- find the complement of the following expression:
- Silver is reacted with excess mercury (II) chloride, producing a solid silver chloride precipitate. If the actual yield of 25.5...
- BIO/101
- explain klockar's typology of work styles and how they relate to the four main functions of probation and parole officers
- 350 for nyanya
- Open a new data file in SPSS. You will create a file containing 1 variable for each item (question) on the survey (for a total of 10). You will score each individual survey and enter the values into the appropriate column of the SPSS data file for later a
- Math Assignment 5
- HIST102- LONG ESSAY ******FOR SMARTZENA********
