Health Care Management

7.5 End-of-Chapter Practice Problems

1. Suppose an administrator at a multi-institutional health service organization

wants to study the insured population at the organization to determine the

relationship between the number of visits to the nearest clinic in the past year

and both the age of the patient and the distance from the nearest clinic of the

patient’s home (measured in miles). Is there a relationship between the age of the patient, the distance to the nearest clinic, and the number of visits to the nearest

clinic during the past year?

You have decided to use a multiple correlation and multiple regression analysis

to answer these questions, and to test your Excel skills, you have collected the

data of a random sample of 17 patients during the past year. These hypothetical

data appear in Fig. 7.10:

Fig. 7.10 Worksheet Data for Chap. 7: Practice Problem #1

(a) Create an Excel spreadsheet using VISITS as the criterion (Y), and AGE

(X1) and DISTANCE (X2) as the predictors.

(b) Use Excel’s multiple regression function to find the relationship between these three variables and place it below the table.

(c) Use number format (two decimal places) for the multiple correlation on the

SUMMARY OUTPUT, and use four decimal places for the coefficients in

the SUMMARY OUTPUT

(d) Print the table and regression results below the table so that they fit onto

one page.

(e) Save this file as: VISITS11

Answer the following questions using your Excel printout:

1. What is the multiple correlation Rxy? 2. What is the y-intercept a? 3. What is the coefficient for AGE b1? 4. What is the coefficient for DISTANCE b2? 5. What is the multiple regression equation?

6. Predict the number of visits you would expect for an age of 34 and a

distance of 5 miles.

(f) Now, go back to your Excel file and create a correlation matrix for these three variables, and place it underneath the SUMMARY OUTPUT.

(g) Re-save this file as: VISITS11

(h) Now, print out just this correlation matrix on a separate sheet of paper.

Answer the following questions using your Excel printout. Be sure to include

the plus or minus sign for each correlation:

7. What is the correlation between AGE and VISITS?

8. What is the correlation between DISTANCE and VISITS?

9. What is the correlation between DISTANCE and AGE?

10. Discuss which of the two predictors is the better predictor of VISITS.

11. Explain in words how much better the two predictor variables together

predict VISITS than the better single predictor by itself.

2. Suppose you wanted to study the records of patients who were admitted into the

health care facility with the same condition. Is there a relationship between the

number of lab tests run during the patient’s stay in the facility, the income of the patient (measured in thousands of dollars), and the number of lab tests run before

the patient was admitted to the facility? To simplify the problem, presume that

lab tests prior to admission are independent of lab tests during a patient’s stay in the health care facility. The hypothetical data for 15 patients are presented in

Fig. 7.11.

7.5 End-of-Chapter Practice Problems 173

(a) create an Excel spreadsheet using LAB TESTS DURING STAY as the

criterion (Y), and the other variables as the two predictors of this criterion.

(b) Use Excel’s multiple regression function to find the relationship between these variables and place it below the table.

(c) Use number format (two decimal places) for the multiple correlation on the

Summary Output, and use number format (three decimal places) for the

coefficients and all other decimal figures in the Summary Output.

(d) Print the table and regression results below the table so that they fit onto

one page.

(e) By hand on this printout, circle and label:

(1a) multiple correlation Rxy (2b) coefficients for the y-intercept, INCOME, and LAB TESTS BEFORE

ADMISSION.

(f) Save this file as: TESTS10

(g) Now, go back to your Excel file and create a correlation matrix for these

three variables, and place it underneath the Summary Table. Change each correlation to just two decimals. Save this file again as: TESTS10

(h) Now, print out just this correlation matrix in portrait mode on a separate sheet of paper.

Answer the following questions using your Excel printout:

1. What is the multiple correlation Rxy?

2. What is the y-intercept a? 3. What is the coefficient for INCOME b1?

Fig. 7.11 Worksheet Data for Chap. 7: Practice Problem #2

174 7 Multiple Correlation and Multiple Regression

4. What is the coefficient for LAB TESTS BEFORE ADMISSION b2? 5. What is the multiple regression equation?

6. Underneath this regression equation by hand, predict the LAB TESTS

DURING STAY you would expect for an INCOME of $36,000 and

6 LAB TESTS BEFORE ADMISSION.

Answer the following questions using your Excel printout. Be sure to include

the plus or minus sign for each correlation:

7. What is the correlation between INCOME and LAB TESTS DURING

STAY?

8. What is the correlation between LAB TESTS BEFORE ADMISSION and

LAB TESTS DURING STAY?

9. What is the correlation between INCOME and LAB TESTS BEFORE

ADMISSION?

10. Discuss which of the two predictors is the better predictor of LAB

TESTS DURING STAY.

11. Explain in words how much better the two predictor variables combined

predict LAB TESTS DURING STAY than the better single predictor by

itself.

3. Suppose that you wanted to study the relationship between the number of visits

to a health care clinic during the past year by the insured population (i.e., the

volume of care provided to the patient) and the ability of the patient to pay for

care services (measured by dividing the disposable family income of the patient

by the patient’s family size) and the distance (to the nearest mile) from the patient’s residence to the clinic. For example, is distance negatively correlated with the number of visits?

You have decided to use a multiple correlation and multiple regression analysis,

and to test your Excel skills, you have collected the data of a random sample of

15 patients who were treated for the same condition during the past year.

These hypothetical data appear in Fig. 7.12.

7.5 End-of-Chapter Practice Problems 175

(a) create an Excel spreadsheet using the number of visits as the criterion and

the other two variables as the predictors.

(b) Use Excel’s multiple regression function to find the relationship between these three variables and place the SUMMARY OUTPUT below the table.

(c) Use number format (two decimal places) for the multiple correlation on

the Summary Output, and use number format (three decimal places) for

the coefficients in the summary output and for all other decimal figures in the

SUMMARY OUTPUT.

(d) Save the file as: VISITS21

(e) Print the table and regression results below the table so that they fit onto

one page.

Answer the following questions using your Excel printout:

1. What is multiple correlation Rxy? 2. What is the y-intercept a? 3. What is the coefficient for INCOME b1? 4. What is the coefficient for DISTANCE b2? 5. What is the multiple regression equation?

6. Predict the number of visits you would expect for an adjusted INCOME

of $26,000 and a distance of 4 miles.

Fig. 7.12 Worksheet Data for Chap. 7: Practice Problem #3

176 7 Multiple Correlation and Multiple Regression

(f) Now, go back to your Excel file and create a correlation matrix for these

three variables, and place it underneath the SUMMARY OUTPUT on your

spreadsheet.

(g) Re-save this file as: VISITS21

(h) Now, print out just this correlation matrix on a separate sheet of paper.

Answer the following questions using your Excel printout. Be sure to

include the plus or minus sign for each correlation:

7. What is the correlation between INCOME and VISITS?

8. What is the correlation between DISTANCE and VISITS?

9. What is the correlation between DISTANCE and INCOME?

10. Discuss which of the two predictors is the better predictor of VISITS.

11. Explain in words how much better the two predictor variables combined

predict VISITS than the better single predictor by itself.

References

Keller G. Statistics for management and economics. 8 th

ed. Mason: South-Western Cengage

Learning; 2009.

Levine D, Stephan D, Krehbiel T, Berenson M. Statistics for managers using Microsoft Excel. 6 th

ed. Boston: Pearson Prentice Hall; 2011.

Veney JE. Statistics for health policy and administration using Microsoft Excel. San Francisco:

Jossey-Bass; 2003

Veney JE, Kros JF, Rosenthal DA. Statistics for health care professionals: working with Excel. 2 nd

ed. San Francisco: Jossey-Bass; 2009.

References 177