Assignment #1
Using Excel to Analyze Health Data
To complete this assignment, use the Excel file, Data for Assignment 1.
It is preferred that students work in pairs on this assignment, with one assignment submittedper pair; both students in a pair will receive the same grade. It is strongly recommended thatboth students actively participate in preparing the assignment. If, instead, partners choose tosplit the assignment (with each being responsible for different parts of the assignment), beaware than one persons poor/non-completion of his/her portion is NOT an acceptable reasonfor submitting the assignment late. You may work independently if you prefer.
At the completion of your assignment, you should have one workbook with two tabs (theoriginal + one worksheet you have created). Name the file as follows: OurLastNamesFirstInitials_Assign1.xlsx (or MyLastNameFirstInitial_Assign1.xlsx), where you replaceOurLastNamesFirst Initials with your first initials and last names (e.g., WolfJ_LoboJ_Assign1.xlsx). Your name(s) must be a part of the file name. Upload the completed Exceldocument to Canvas on the assignment page.
INSTRUCTIONS READ CAREFULLY:These instructions apply to all parts of Assignment 1. Points are associated with allinstructions; not following the instructions will result in a loss of points.Use cell referencing in all formulas, and use functions that we have discussed in class wheneverpossible; a significant part of your grade is dependent on your use of appropriate cellreferencing and use of functions. While you may already be familiar with Excel and know othermethods for answering the questions, for this assignment you must use the functions coveredin class.TIPS: Make it easy to locate all requested information. You can use bold or another font coloror try using the paint can to fill the cell with color ( ) Use column/row titles to describe thedata included.
Question (40 points possible)Using the 1st Quarter 2024 California Hospital Quarterly Financial and Utilization data, completeparts A through L.
A. Copy the Orange County, Riverside County, San Bernardino County, and San Diego Countyhospitals to a new worksheet within this workbook. (Copy the rows holding the variablePH 3072names and Orange County, Riverside County, San Bernardino County, and San Diego Countyhospitals only; do not copy the whole worksheet.) (3 pts)
B. Sort the hospitals by county. (2 pts)
C. Name this tab Four Counties. (1 pt)
D. Hide the columns that you wont need to answer this question; do not delete columns. (2pts)
E. In a new column, calculate the net inpatient revenue (est.) for each hospital located inOrange, Riverside, San Bernardino, and San Diego Counties. (Hint: You will need to refer tothe OSHPD documentation to determine how to calculate the net inpatient revenue.)Format with the 1000 separator. Show 0 decimal places. (5 pts)
F. Using the appropriate function, calculate the mean net inpatient revenue (est.) for Orange,Riverside, San Bernardino, and San Diego combined. This can be placed in another newcolumn or at the bottom of the list of hospitals. Show 0 decimal places. Make certain tolabel this as Mean. (5 pts)
G. Using the appropriate function, calculate the median net inpatient revenue (est.) forOrange, Riverside, San Bernardino, and San Diego Counties combined. This can be placedunder the mean. Show 0 decimal places. Make certain to label this as Median. (5 pts)
H. In the next empty column, use the appropriate function discussed class to recode the netinpatient revenue (est.) for each hospital as follows:1) 0 if the net inpatient revenue (est.) are less than $25,000,000;2) 1 if the net inpatient revenue (est.) are equal to or greater than $25,000,000.Cell reference all criteria used in part H. (5 pts)
I. In the next empty column on row 2, calculate the number of days in the reporting period.Refer to page 6 of the OSHPD documentation to determine the correct formula; the formulahas been highlighted in yellow. You only need to calculate this on row 2. (2 pts)
J. In the next empty column, calculate the Staffed Bed Occupancy Rate for each hospital inOrange, Riverside, San Bernardino, and San Diego Counties. Format as a percentage with 1decimal place. (4 pts)
K. Set up this worksheet as follows:
1) Include a header with your first and last names and the date the assignment is due inthe top right corner (1 pt);
2) Set the titles to repeat at top and the facility number and name to repeat at left forprinting (2 pts);
3) Set the gridlines to print. (1 pt)
L. All numbers should be right-aligned. (1 pt)
M. Show only the facility number, facility name, county name, the columns used in thecalculations, and the columns holding the information you calculated. (1 pt)
