Question 4

(Problem Scenario taken from A Course in Business Statistics, fourth edition, by Groebner, Shannon, Fry and Smith)

Freedom Hospital is in the midst of contract negotiations with its resident physicians.There has been a lot of discussion about the hospital's ability to pay and the way patients are charged.The doctors' negotiator recently mentioned that the geriatric charge system does not make sense and there may be a difference in the way males are charged versus females.To look into this, the hospital has collected a random sample of patient data for 138 patients.The data are in the worksheet named Freedom Hospital.The operations manager wants your team to use this data to develop a 95% confidence interval estimate for the difference in average charges. To complete Question 4 you should follow the steps below in parts a through m.

Place your answers for Question 4 in the worksheet named Freedom Hospital.

a.2 Points:Sort the data in columns A, B, and C such that the Female Patient Charges are at the top of the list.

b.2 Points: Calculate the best point estimates of the population means using a built-in Excel function.Place your answers in cells F7 and G7.

c.2 Points: Calculate the sample standard deviations using a built-in Excel function. Place your answers in cell F8 and G8.

d.2 Points: Calculate the variances in cells F9 and G9 by writing a formula referencing the appropriate cells.

e.2 Points:Calculate the sample sizes (n values) in cells F10 and G10 using a built-in Excel function.

f.2 Points: Calculate the difference in sample means in cell F11 referencing the appropriate cells.

g.4 Points: Assuming the population variances are equal, calculate the value of the pooled sample variance by writing a formula in cell F12 which references the appropriate cells.

h.2 Points:Calculate the pooled standard deviation in cell F13.

i.4 Points: Place the indicated values for the confidence level (in decimal form) and a in cells L7 and L8.Calculate the appropriate degrees of freedom for your confidence interval by placing a formula referencing appropriate cells in cell L9.Determine your t or z value by placing the appropriate TINV or NORMINV expression in cell L10.

j.2 Points:Calculate the margin of error in cell L11 by writing a formula that references the appropriate cells.

k.2 Points: Calculate the lower limit of the confidence interval (LL) in cell F18 by writing a formula that references the appropriate cells.

l.2 Points: Calculate the upper limit of the confidence interval (UL) in cell H18 by writing a formula that references the appropriate cells.

m.4 Points: Based on your confidence interval results make a conclusion regarding the amount that male and female geriatric patients are charged.Place your statement in cell K14.

n.8 Points:Replicate using StatTools first unstacking the data (in a new worksheet) and then placing your output with the upper left hand cell in cell Q3.

Freedom Freedom Hospital Geriatric Patient Analysis Question 4 Grader Grader Sex (M/F) Principal Payer F F F F F F F CARE CARE CARE CARE CARE CARE CARE $ $ $ $ $ $ $ 5,418.85 4,575.10 12,806.88 5,295.55 3,290.40 3,896.16 3,282.90 F CARE $ 9,565.83 F CARE Total Charges $ 4,169.92 F F F CARE OTHR CARE $ $ $ 5,204.41 4,105.26 6,598.92 F F OTHR CARE $ $ 1,633.85 7,461.54 F F F F F F F F F F F F F F F F F F F F F F F F F F F F F F F F F F F F F F F F F F F CARE CARE CARE CARE CARE CAID CARE CARE CARE CAID CAID CARE CARE CARE CARE CARE CARE CARE CARE BC CARE CARE CARE CARE CARE CARE CARE CARE CARE CARE CARE CARE CARE CARE CARE CARE CARE CARE OTHR OTHR CARE CARE CARE $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 6,536.07 7,401.25 8,653.68 14,423.21 5,514.09 4,526.43 6,846.77 10,703.34 16,458.95 7,871.29 10,962.17 5,624.54 4,574.92 3,644.36 2,937.92 4,992.01 6,929.35 7,289.77 6,375.95 3,585.62 10,578.14 2,250.50 2,491.45 1,691.50 5,121.97 2,325.60 4,452.78 21,474.90 8,566.51 4,910.27 3,275.03 3,232.63 3,768.11 9,862.26 10,626.42 6,967.99 5,891.25 4,756.91 8,096.93 5,796.49 2,510.05 10,185.83 3,391.90 n a Confidence Interval Estimate for the Difference in Average Charges Sample Statistics Mean = Std Dev = Variance = n= Female Male Grader b c d e Point Estimate = f (Difference in Means) Pooled Sample Variance g Pooled Standard Deviation h Confidence Interval I/O C= a= df = t or z= Grader i j MOE = Conclusion: Grader m C. I. for m1-m2 LL UL