Roosterfosho
New Member
- Joined
- Mar 18, 2013
- Messages
- 5
Hello all,
I have an excel problem due in my accounting class tonight at midnight, however, I'm quite confused by it. I know I need to use IF functions, but I'm unsure about how to go about it. The problem is as follows:
Background: You just landed you first full-time job and you reviewing the health plans available from your employer. Your 22 and single and you have no clue which plan would be the best for you so you decide to make a budget in excel to determine which plan would be least costly.
There are three plans: 1) High deductible, 2) Middle deductible, and 3) Open access plan. Based on review of the information sheets for each plan you compile the following information for each plan.
Semi-Monthly Premium:
<tbody>
</tbody>Plan Coverage Limits:
<tbody>
</tbody>You also note that all the plans cover 100% of preventative and pre-natal care, so all the plans will treate hospital bills the same. There is also additional cost for using an out of network doctor so you call your doctor and make sure they accept your company's health insurance provider.
Based on your research you also find out that you can only have a health savings account (HSA) if you select the high or middle deductible. The company will only contribute to your HSA if you select the high deductible plan as indicated in the first table above. You can make additional contributions from your pay check pre-tax up to $200 semi-monthly.
You can only change plans annually or upon a life event (i.e. birth, death, or change in job).
Requirements:
1. Prepare an analysis in excel that would show the total cost of each plan in year 1 based on varying levels of hospital bills. Create a model such that you can plug in different hospital bill amounts so that the total cost of the plan automatically calculates. Use $1,000, $5,000, $10,000, and $50,000 as examples to see how the cost of each plan varies.
Format the analysis is an easy to read and professional manner (your Dad & Mom want to make sure you're making the right choice and want to see your analysis). I have a format that I am envisioning makes the most sense to present the information in excel. The bulk of the points will be awarded based on how presentable your solution appears.
Make sure to show the results of each of the levels of hospital bills at $1,000, $5,000, $10,000, and $50,000 in addition to being able to show them how the formulas work so that they can see if you plug in any number the cost of each plan will automatically update.
2. Using your analysis above start to think about your future. What would be the cost of each plan for the next three years. What plan makes the most sense for you under the following scenarios:
a. You are 22, single and have no plans on getting married. You want to focus on your career and can be bothered to be tied down. Let's assume you are healthy and the only hospital bills you incur are normal doctor's visits.
b. You are married and not planning on starting a family anytime soon, but know that it is on the horizon. Again, let's assume you are healthy and the only hospital bills you incur are normal doctor's visits.
c. You and your spouse are expecting and you know the hospital bills for having a child are astronomical. Everything should go smoothly, but kids are expensive. Do you save money on the insurance and pay out of pocket? Or do you take the open access and have peace of mind that everything is covered?
For each of question above provide a quick write-up, two to three sentences, about why you chose the plan you did.
Submit your answer in excel to the Angel drop-box for excel problem #1. Call or e-mail me with any question you may have.
If you guys can provide any input on how to do this that would be great.
Thank you.
I have an excel problem due in my accounting class tonight at midnight, however, I'm quite confused by it. I know I need to use IF functions, but I'm unsure about how to go about it. The problem is as follows:
Background: You just landed you first full-time job and you reviewing the health plans available from your employer. Your 22 and single and you have no clue which plan would be the best for you so you decide to make a budget in excel to determine which plan would be least costly.
There are three plans: 1) High deductible, 2) Middle deductible, and 3) Open access plan. Based on review of the information sheets for each plan you compile the following information for each plan.
Semi-Monthly Premium:
| | High Deductible | Middle Deductible | Open Access Plan |
| Staff Only | $29.00 | $53.00 | $87.00 |
| Staff & spouse / domestic partner | 72.50 | 130.00 | 214.00 |
| Staff & Child(ren) | 61.50 | 111.50 | 183.00 |
| Staff & Family | 105.00 | 190.50 | 317.50 |
| | | | |
| Semi-Monthly HSA Company Contribution: | | | |
| Individual / Other Coverage Levels | $29.16/$58.33 | $0 | $0 |
| | | | |
<tbody>
</tbody>
| | High Deductible | Middle Deductible | Open Access Plan |
| Annual Deductible: | | | |
| Individual | $3,250 | $1,625 | $500 |
| Family | $6,450 | $3,225 | $1,000 |
| | | | |
| Individual | $2,000 | $2,000 | $2,000 |
| Family | $4,000 | $4,000 | $4,000 |
| Annual Out-of-pocket Maximum | | | |
| Individual | $5,250 | $3,625 | $2,500 |
| Family | $10,450 | $7,225 | $5,000 |
| Doctors' Office Visits | Deductible, then 10% coinsurance | Deductible, then 10% coinsurance | Deductible, then 10% coinsurance |
| | | | |
<tbody>
</tbody>
Based on your research you also find out that you can only have a health savings account (HSA) if you select the high or middle deductible. The company will only contribute to your HSA if you select the high deductible plan as indicated in the first table above. You can make additional contributions from your pay check pre-tax up to $200 semi-monthly.
You can only change plans annually or upon a life event (i.e. birth, death, or change in job).
Requirements:
1. Prepare an analysis in excel that would show the total cost of each plan in year 1 based on varying levels of hospital bills. Create a model such that you can plug in different hospital bill amounts so that the total cost of the plan automatically calculates. Use $1,000, $5,000, $10,000, and $50,000 as examples to see how the cost of each plan varies.
Format the analysis is an easy to read and professional manner (your Dad & Mom want to make sure you're making the right choice and want to see your analysis). I have a format that I am envisioning makes the most sense to present the information in excel. The bulk of the points will be awarded based on how presentable your solution appears.
Make sure to show the results of each of the levels of hospital bills at $1,000, $5,000, $10,000, and $50,000 in addition to being able to show them how the formulas work so that they can see if you plug in any number the cost of each plan will automatically update.
2. Using your analysis above start to think about your future. What would be the cost of each plan for the next three years. What plan makes the most sense for you under the following scenarios:
a. You are 22, single and have no plans on getting married. You want to focus on your career and can be bothered to be tied down. Let's assume you are healthy and the only hospital bills you incur are normal doctor's visits.
b. You are married and not planning on starting a family anytime soon, but know that it is on the horizon. Again, let's assume you are healthy and the only hospital bills you incur are normal doctor's visits.
c. You and your spouse are expecting and you know the hospital bills for having a child are astronomical. Everything should go smoothly, but kids are expensive. Do you save money on the insurance and pay out of pocket? Or do you take the open access and have peace of mind that everything is covered?
For each of question above provide a quick write-up, two to three sentences, about why you chose the plan you did.
Submit your answer in excel to the Angel drop-box for excel problem #1. Call or e-mail me with any question you may have.
If you guys can provide any input on how to do this that would be great.
Thank you.
