Before beginning the assignment, read the instructions carefully.To complete this assignment, use the Excel file, “Data for Assignment 1,” posted on Blackboard in the Excel Assignment folder on the “Assignments” tab. You are to work in pairs, submitting one assignment to be graded; each individual in the pair will receive the same grade. Only one partner should upload the completed assignment. It is to your advantage that both partners work on all questions, either together or independently (and then compared), to ensure that the uploaded assignment contains your combined best efforts. It is up to the partners to ensure that both individuals participate in completing the assignment.

When it is appropriate to show decimals, you should show 2 decimal places. Use cell referencing in all formulas, and use functions whenever possible; a significant part of your grade is dependent on your use of appropriate cell referencing and use of functions.

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. Use column and row columns to describe the data included.

At the completion of your assignment, you should have one workbook with four tabs (the original three tabs + one worksheet you have created). Name the file as follows: OurLastNamesAssign1.xlsx (or OurLastNamesAssign1.xls), where you replace OurLastNameswith your names. Upload your completed Exceldocument to Blackboard on the assignment page.

Question 1

Using the Second Quarter 2019 California Hospital QuarterlyFinancial and Utilization data (the first tab in the “Data for Assignment 1”), copy the San Diego and Los Angeles County hospitals to a new worksheet within this workbook. Name this tab “San Diego and Los Angeles.” Sort this worksheet by county. Remove any hospitals where the results of your calculations equal “#DIV/0!.” (To make the data easier to work with, I suggest that you hide columns that you will not need to answer the question.)

A. Calculate the inpatient operating expenses (est.) for eachhospital 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 inpatient operating expenses.)

B. What are the mean and median inpatient operating expenses (est.) for San Diego and Los Angeles Counties combined?

C. Calculate the percent gross revenue collected for each hospital located in San Diego County and Los Angeles County.

D. In a new column, use the appropriate function to recode the inpatient operating expense for each hospital as follows:

1) 0 if the inpatient operating expense is less than $35,000,000;

2) 1 if the inpatient operating expense is greater than $35,000,000.

E. Set up this worksheet as follows:

1) Landscape orientation;

2) Fit to 1 pages wide by 3 pages tall;

3) Include a header with your names and the date the assignment is due in the top right corner;

4) Set the titles to repeat at top and the facility number and name to repeat at left;

5) Have the gridlines print.

F. 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.

Question 2

The data on the “Hysterectomy” tab is a random sample of all hysterectomies performed in the US in 1992.

A.Using this data, create a frequency table and calculate relative frequency, cumulative frequency, and cumulative relative frequency for the age intervals 15-24, 25-34, 35-44, 45-54, 55-64, 65-74, 75-84. To get credit on this question, you must use functions to calculate the frequencies; sorting the ages will not give you credit.

B.Calculate the mean age of hysterectomies.

C.What is the percentage of women under the age of 35 who have had a hysterectomy?

Please place parts B and C directly below the frequency table you have created.

D. Set up this worksheet as follows:

1) Portrait orientation;

2) Fit to 1 page wide by 1 page tall;

3) Set the print area sufficiently wide to contain column A through the columns used to complete thefrequency table and sufficiently tall to contain all the responses to all parts of this question.

4) Include a header with your name and the date the assignment is due in the top right corner;

5) Have the gridlines print.

Question 3

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 $700 annual deductible.

Plan B: Your company would pay 75% of full 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 (Insurance Question) in the “Data for Assignment 1” 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 next year’s payout under each alternative, using the assumption that charges will increase 9% next year over last year’s values.

Please answer the following questions:

A. What is the total payout under each alternative?

B. What is the total cost for females and for males under each of the plans? What is the average cost perfemale and permale under each of the plans?

NOTE: Remember that your firm will pay nothing under Plan A for someone who incurs less than $700 in charges during the year (i.e., the employee’s charges do not exceed the $700 deductible).

C. Set up this worksheet as follows:

1) Landscape orientation;

2) Fit to 1 page wide by 15 pages tall;

3) Include a header with your name and the date the assignment is due in the top right corner;

4) Have the gridlines print.