A. Copy the San Diego County and Los Angeles County hospitals to a new worksheet

A. Copy the San Diego County and Los Angeles County hospitals to a new worksheet within this
worksheet. (Copy the rows holding the variable names and Los Angeles County and San
PH 307
2
Diego County hospitals as described in the Watch & Practice: Practice Skills for Assignment
1 video; do not copy the whole worksheet.) (3 pts)
B. Sort the hospitals by county. (2 pts)
C. Name this tab Los Angeles and San Diego. (2 pts)
D. Hide the columns that you won’t need to answer this question; do not delete columns. (2
pts)
E. In a new column, calculate the net outpatient revenue (est.) for each hospital located in San
Diego County and Los Angeles County. (Hint: You will need to refer to the OSHPD
documentation to determine how to calculate the net outpatient revenue.) Format with
1000 separator. (5 pts)
F. Using the appropriate function, calculate the mean net outpatient revenue (est.) for San
Diego and Los Angeles Counties combined. This can be placed in another new column or at
the bottom of the list of hospitals. Make certain to label this as “Mean.” (5 pts)
G. Using the appropriate function, calculate the median net outpatient revenue (est.) for San
Diego and Los Angeles Counties combined. This can be placed under the mean. Make
certain to label this as “Median.” (5 pts)
H. In the next empty column, use the appropriate function discussed in the videos to recode
the net outpatient revenue for each hospital as follows:
1) 0 if the net outpatient revenue is less than $30,000,000;
2) 1 if the net outpatient revenue is equal to or greater than $30,000,000.
Cell reference the criteria used in part
H. (5 pts)
I. In the next empty column, calculate the cost-to-charge ratio for each hospital located in San
Diego County and Los Angeles County. (Refer to the OSHPD documentation to determine
the correct formula.) Format as a percent. (5 pts)
J. Set up this worksheet as follows:
1) Include a header with your first and last names and the date the assignment is due in
the top right corner (1 pt);
2) Set the variable names to repeat at top and the facility number and name to repeat at
left for printing (2 pts);
3) Set the gridlines to print. (1 pt)
K. All numbers should be right-aligned. (1 pt)
PH 307
3
L. Show only the facility number, facility name, county name, type_control, the columns used
in the calculation, and the columns holding the information you calculated. (1 pt)