Homework Problem Help

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:

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>
Plan Coverage Limits:

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

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,784
Office Version
  1. 2010
Platform
  1. Windows
We don't do homework, but will help if you have a specific and narrow question.

What have you done so far?
 

Roosterfosho

New Member
Joined
Mar 18, 2013
Messages
5
I've gotten the table down completely, but I'm unsure where to go from here. For example, when the hospital bills are higher than the deductible is the equation something like if(hospital bill amount> deductible, deductible + 10% of the amount of the remaining hospital bill, hospital bill)
I substituted the numbers as names to make it easier.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,129,568
Messages
5,637,093
Members
416,957
Latest member
Brovashift

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top