Assignment #2 – Using Excel to Analyze Health Data
To complete this assignment, use the Excel file, “Data for Assignment 2,” posted on the
Assignment 2 page.
It is preferred that students work in pairs on this assignment, with one assignment submitted
per pair; both students in a pair will receive the same grade. It is strongly recommended that
both students actively participate in preparing the assignment. If, instead, partners choose to
split the assignment (with each being responsible for different parts of the assignment), be
aware than one person’s poor/non-completion of his/her portion is NOT an acceptable reason
for submitting the assignment late. Students may choose to work individually on this
assignment.
At the completion of your assignment, you should have a file with one worksheet. Name the file
as follows: OurLastNamesFirst Initials_Assign2.xlsx (or MyLastNameFirstInitial_Assign2.xlsx),
where you replace OurLastNamesFirst Initials with your first initials and last names (e.g.,
WolfJ_LoboJ_Assign2.xlsx). Your names must be a part of the file name. Upload your
completed Excel document to Canvas on the assignment page.
INSTRUCTIONS – READ CAREFULLY:
These instructions apply to all parts of Assignment 2. Points are associated with all
instructions; not following the instructions will result in a loss of points.
Use cell referencing in all formulas, and use functions discussed in the instructional videos and
class whenever possible; a significant part of your grade is dependent on your use of
appropriate cell referencing and use of functions. Show zero (0) decimal places in all answers.
TIPS: Make it easy to locate all requested information. You can use bold or another font color or
try using the paint can to fill the cell with color ( ) Use column/row titles to describe the
data being shown.
Information on Deductibles:
A deductible is the amount you pay for covered health care services before your insurance plan
starts to pay claims. For example, if you have a $500 annual deductible, you must pay the first
$500 of covered expenses in a year before your insurance pays anything. To calculate the
amount the insurance company will pay if there is a deductible involved, you must first subtract
the deductible from the yearly charges; you would then multiply the percentage of the charges
that the insurance will pay by the total charges after you have subtracted the deductible.
Example: Total charges expected for the year: $2000
PH 307
2
Deductible: $500
Total charges eligible for insurance coverage: = $2000 – $500 (or $1500)
If insurance pays 70% of eligible expenses, insurance will pay 70% of the $1500
(= 70% * 1500 = $1050)
Question (70 points)
You work as a data analyst for a health insurance company. Your firm is considering two
alternative plans for insuring employees of a company during the upcoming year.
Plan A: Your company would pay 80% of charges for all services received during the year after
each employee pays a $900 annual deductible. (NOTE: If the total charges are less than
the deductible, the company will pay nothing. Rather than showing a negative number,
the amount should show $0.)
Plan B: Your company would pay 75% of all charges, with no deductible or patient copayment.
Last year’s claims by the employees of the company you are insuring are contained in the
worksheet titled “Data for Assignment 2” workbook. There are three variables in this file: Total
Charges = last year’s $ charges for medical services; Age = age of the individual; Sex = sex of the
individual. You have been asked to analyze these plans to estimate the company’s payout under
each alternative for the next year, using the assumption that charges will increase 10% next
year over last year’s values. [NOTE: I have provided cells (highlighted in blue) for you to type
some of the information you may need for cell referencing in the formulas.]
Please answer the following questions without sorting any data:
A. In a new column, show each individual’s charges with the 10% increase. Cell reference all
components of the formula. (4 pts)
NOTE: The remaining parts of this assignment should be based on the results of Part A.
B. 1. Use the IF function to calculate the the company’s responsibility for Plan A charges. (If
the formula is done correctly, rather than getting a negative number in any row, you
should see a zero.) (4 pts)
- Cell reference all components of the formula used in calculating charges under Plan A. (2
pts)
C. 1. Calculate the company’s responsibility for each individual’s charges under Plan B. (3 pts) - Cell reference all components of the formula used in calculating charges under Plan B. (1
pt)
PH 307
3
D. 1. Using the most appropriate function discussed in the videos, calculate the company’s
total responsibility (payout) under Plan A. (2 pts) - Cell reference all components in this formula. (1 pt)
E. 1. Using the most appropriate function discussed in the videos, calculate the company’s
total responsibility (payout) under Plan B. (2 pts) - Cell reference all components in this formula. (1 pt)
F. 1. Using the most appropriate function discussed in the videos, calculate the company’s
total responsibility (costs) for females under Plan A. (2.5 pts) - Cell reference all components in this formula. (1 pts)
G. 1. Using the most appropriate function discussed in the videos, calculate the company’s
total responsibility (costs) for males under Plan B. (2.5 pts) - Cell reference all components in this formula. (1 pts)
H. 1. Using the most appropriate function discussed in the videos, calculate the company’s
Plan A cost per male under age 30. (3 pts) - Cell reference all components in this formula. (2 pts)
I. 1. Using the most appropriate function discussed in the videos, calculate the company’s
total Plan B costs for females age 45 and older. (3 pts) - Cell reference all components in this formula. (2 pts)
J. 1. Using the most appropriate function discussed in the videos, calculate the company’s
total costs for individuals with Plan A costs less than $3,200. (3 pts) - Cell reference all components in this formula. (2 pts)
K. 1. Using the most appropriate function discussed in the videos, calculate the company’s
Plan B cost per male over age 55. (3 pts) - Cell reference all components in this formula. (2 pts)
L. 1. Using the most appropriate function discussed in the videos, calculate the Plan A cost per
female age 21 through 35. (3 pts) - Cell reference all components in this formula. (2 pts)
M. 1. Using the most appropriate function discussed in the videos, calculate the total costs for
males with Plan B costs greater than $4,000. (3 pts) - Cell reference all components in this formula. (2 pts)
PH 307
4
N. 1. What is the number of males with Plan A costs of $1,500 through $5,500? Use the most
appropriate function discussed in the videos. (3 pts) - Cell reference all components in this formula. (2 pts)
O. 1. What is the number of females age 20 through 45? Use the most appropriate function
discussed in the videos. (3 pts) - Cell reference all components in this formula. (2 pts)
P. 1. Use the 1000 separator and show no decimals. (2 pts) - Include a header with your first and last name(s) in the top right corner. (1 pt)
- Set up this worksheet in landscape orientation. (1 pt)
- Set the gridlines to print. (1 pt)
