Due Monday August 29
1. The Excel file Physician Admissions.xlsx, contains information on 56 physicians who
are affiliated with your hospital.
a. What is the average and standard deviation for visits and admissions?
b. Calculate the proportion of admissions for each doctor.
c. What is the overall proportion of admissions for all doctors?
d. Use the vlookup command in Excel to pull the number of visits by Dr. Brooks.
e. Use the vlookup command to pull the percentage of admissions for Dr. Ulrich.
f. Use the vlookup command to calculate the difference in admissions between Dr.
Simmons and the average physician.
g. Write a command that shows the maximum proportion of admissions.
h. Write a single command that shows the name of the doctor who has the
maximum number of admissions.
i. Create a graph that shows the admission rate for each doctor in ascending order
along with the overall proportion of admissions.
j. Which doctors stick out as high or low admitters?
2. The file Patient Data0510.xls contains information from a sample of admitted patients
for the years 2005 and 2010 for a particular hospital. Use the Pivot Table Wizard in
Excel to examine the following:
a. What are the largest sources of admissions? How have these changed between
2005 and 2010?
b. Create a table to show average length of stay by financial class by year for the
following categories: Blue Cross, CHIP, Commercial, HMO/PPO, Medicaid,
Medicaid MC, Medicare, and Medicare MC. Where has LOS changed the most?
c. Create a table that displays the average LOS by the 5 most common DRGs by
year, and then create a pivot chart of these data.
d. Finally create a table that shows the average and standard deviation of Length of
stay for the top 5 DRGs (combining years). Where is the variation in LOS the
largest? Why might it be interesting to focus on variation in LOS as well as the