Overview
These instructions apply to all parts Points are associated with all instructions; not following the instructions will result in a loss of points. When it is appropriate to show decimals, you should show 2 decimal places; use of decimal places should be consistent within a column (or variable). Use cell referencing in all formulas, and use functions that we have discussed in class whenever possible; a significant part of your grade is dependent on your use of appropriate cell referencing and use of functions. While you may already be familiar with Excel and know other methods for answering the questions, for this assignment you must use the functions covered in class. 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 included.
Question (40 points possible) Using the Second Quarter 2023 California Hospital Quarterly Financial and Utilization data (the first tab in the “Data for Assignment 1”), complete parts A through L.
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)
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)