Health Care Management

6.9 End-of-Chapter Practice Problems

1. Suppose you have been asked by a hospital administrator to determine the

relationship between the patient length of stay (measured as the number of

inpatient days before discharge) and the amount charged to a patient for that

stay. You have decided to use a correlation and simple linear regression analysis,

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

13 patients from the past 90 days. These hypothetical data appear in Fig. 6.33.

(a) create an Excel spreadsheet and chart using AMOUNT CHARGED ($) as

the criterion (dependent variable) and LENGTH OF STAY (LOS) as the

predictor using the following format:

– Top title: RELATIONSHIP BETWEEN LOS AND AMOUNT

CHARGED

– x-axis title: LENGTH OF STAY (LOS)

– y-axis title: AMOUNT CHARGED ($)

– Re-size the chart so that it is 7 columns wide and 25 rows long

– Delete the legend

– Delete the gridlines

– Move the chart below the table

(b) Create the least-squares regression line for these data on the scatterplot. (c) Use Excel’s regression function to find the equation for the least-squares

regression line for these data and display the results below the chart on your

spreadsheet.

(d) Use number format (two decimal places) for the correlation on the SUM-

MARY OUTPUT, and use number format (three decimal places) for all of

the other decimal figures in the SUMMARY OUTPUT.

(e) Print the input data and the chart so that this information fits onto one page.

(f) Then, print the regression output table so that this information fits onto a

separate page.

(g) Save the file as: CHARGE3

Answer the following questions using your Excel printout:

1. What is the correlation r?

2. What is the y-intercept a? 3. What is the slope b?

Fig. 6.33 Worksheet Data for Chap. 6: Practice

Problem #1

156 6 Correlation and Simple Linear Regression

4. What is the regression equation (use three decimal places for the

y-intercept and the slope)?

5. Use the regression equation to predict the AMOUNT CHARGED you

would expect for a stay of 6 days.

2. Suppose that a financial administrator of a 90-bed nursing home asked you to

determine the relationship between VOLUME (000) of care provided each

month from the previous year (measured in the total bed-care days during that

month) and the TOTAL COST ($000) for administering care for each month.

Create an Excel spreadsheet and enter the data using VOLUME (000) as the

independent (predictor) variable, and TOTAL COST ($000) as the dependent

(criterion) variable. You decide to test your Excel skills on last year’s data using the hypothetical data presented in Fig. 6.34.

Create an Excel spreadsheet and enter the data using VOLUME (000) as the

independent variable (predictor) and TOTAL COST ($000) as the dependent

variable (criterion).

(a) create an XY scatterplot of these two sets of data such that:

• top title: RELATIONSHIP BETWEEN VOLUME AND TOTAL COST

• x-axis title: VOLUME (000)

• y-axis title: TOTAL COST ($000)

• re-size the chart so that it is 7 columns wide and 25 rows long

• delete the legend

Fig. 6.34 Worksheet Data for Chap. 6: Practice

Problem #2

6.9 End-of-Chapter Practice Problems 157

• delete the gridlines

• move the chart below the table

(b) Create the least-squares regression line for these data on the scatterplot. (c) Use Excel to run the regression statistics to find the equation for the least-

squares regression line for these data and display the results below the chart on your spreadsheet. Use number format (two decimal places) for the

correlation, r, and for both the y-intercept and the slope of the line. Change

all other decimal figures to four decimal places.

(d) Print the input data and the chart so that this information fits onto one page.

(e) Then, print out the regression output table so that this information fits onto a

separate page.

By hand:

(1a) Circle and label the value of the y-intercept and the slope of the regression line on the regression output table that you just printed.

(2b) Estimate from the graph the TOTAL COST you would predict for a VOLUME of 2.00 bed-days of care for a given month, and write your answer in the space immediately below:

_____________________

(f) save the file as: TOTALCOST3

Answer the following questions using your Excel printout:

1. What is the correlation?

2. What is the y-intercept?

3. What is the slope of the line?

4. What is the regression equation for these data (use two decimal places for the

y-intercept and the slope)?

5. Use that regression equation to predict the TOTAL COST you would expect

for a VOLUME of 2.25 bed-days of care for a given month.

(Note that this correlation is not the multiple correlation as the Excel table

indicates, but is merely the correlation r instead.)

You should have found a positive correlation of +.83 between VOLUME and

TOTAL COST. You know that the correlation is a positive correlation for two

reasons: (1) the regression line slopes upward and to the right on the chart,

signaling a positive correlation, and (2) the slope is +159.06 which also tells you

that the correlation is a positive correlation.

But how does Excel treat negative correlations?

Important note: Since Excel does not recognize negative correlations in the SUM- MARY OUTPUT but treats all correlations as if they were positive correlations, you need to be careful to note when there is a negative correlation between the two variables under study.

158 6 Correlation and Simple Linear Regression

You know that the correlation is negative when:

(1) The slope, b, is a negative number, which can only occur when there is a negative correlation.

(2) The chart clearly shows a downward slope in the regres- sion line, which can only happen when the correlation is negative.

3. Suppose that you wanted to study the relationship between DIET (measured in

calories allowed per day) and WEIGHT LOSS (measured in kilograms, kg) for

adult women between the ages of 30 and 40 who are overweight for their height

and body structure, and who all weigh roughly the same number of kilograms

before undertaking the weight loss program. You want to test your Excel skills

on a random sample of these women based on their weight change over the past

4 months to make sure that you can do this type of research. The hypothetical

data appear in Fig. 6.35:

Create an Excel spreadsheet and enter the data using DIET (calories allowed per

day) as the independent variable (predictor) and WEIGHT LOSS (kg) as the

dependent variable (criterion). Underneath the table, use Excel’s ¼correl func- tion to find the correlation between these two variables. Label the correlation and

place it underneath the table; then round off the correlation to two decimal

places.

Fig. 6.35 Worksheet Data for Chap. 6: Practice

Problem #3

6.9 End-of-Chapter Practice Problems 159

(a) create an XY scatterplot of these two sets of data such that:

• top title: RELATIONSHIP BETWEEN DIET AND WEIGHT LOSS

• x-axis title: DIET (calories allowed per day)

• y-axis title: WEIGHT LOSS (kg)

• move the chart below the table and the correlation

• re-size the chart so that it is 8 columns wide and 25 rows long

• delete the legend

• delete the gridlines

(b) Create the least-squares regression line for these data on the scatterplot, and add the regression equation to the chart.

(c) Use Excel to run the regression statistics to find the equation for the least- squares regression line for these data and display the results below the chart on your spreadsheet. Use number format (two decimal places) for the

correlation and three decimal places for all other decimal figures, including

the coefficients.

(d) Print just the input data and the chart so that this information fits onto one

page. Then, print the regression output table on a separate page so that it fits

onto that separate page.

(e) save the file as: DIET3

Answer the following questions using your Excel printout:

1. What is the correlation between DIET and WEIGHT LOSS?

2. What is the y-intercept?

3. What is the slope of the line?

4. What is the regression equation?

5. Use the regression equation to predict the WEIGHT LOSS you would expect

for a woman who was practicing a DIET of 1500 calories allowed a day.

Show your work on a separate sheet of paper.

References

Black K. Business statistics: for contemporary decision making. 6 th ed. Hoboken: John Wiley &

Sons, Inc.; 2010.

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

ed. Boston: Prentice Hall Pearson; 2011.

Lewis JB, McGrath RJ, Seidel LF. Essentials of applied quantitative methods for health services

managers. Sudbury: Jones and Bartlett; 2011.

McCleery R, Watt T, Hart T. Introduction to statistics for biology. 3 rd ed. Boca Raton: Chapman &

Hall/CRC; 2007.

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

ed. San Francisco: Jossey-Bass; 2009.

160 6 Correlation and Simple Linear Regression