Chapter 1: Test
Suppose that you are an administrator in a health care facility and you want to compare the admission heart rate (in beats per minute, bpm) of adult women ages 30–40 who are current residents. You want to try out your Excel skills on a small random sample of residents. The hypothetical data is given below (see Fig. B.1).
(a) Create an Excel table for these data, and then use Excel to the right of the table
to find the sample size, mean, standard deviation, and standard error of the
mean for these data. Label your answers, and round off the mean, standard
deviation, and standard error of the mean to two decimal places.
(b) Save the file as: BEATS3
Chapter 2: Test
A health care facility has discharged 124 patients within the last 60 days. Suppose that you want to do a Customer Satisfaction Survey on a random sample of 20 of these 124 patients for this survey.
(a) Set up a spreadsheet of frame numbers for these patients with the heading:
FRAME NUMBERS
Fig. B.1 Worksheet Data for Chap. 1 Test (Practical Example)
(b) Then, create a separate column to the right of these frame numbers which
duplicates these frame numbers with the title: Duplicate frame numbers.
(c) Then, create a separate column to the right of these duplicate frame numbers
called RAND NO. and use the¼RAND() function to assign random numbers to all of the frame numbers in the duplicate frame numbers column. Change this
column format so that three decimal places appear for each random number.
(d) Sort the duplicate frame numbers and random numbers into a random order. (e) Print the result so that the spreadsheet fits onto one page.
(f) Circle on your printout the I.D. number of the first 20 patients that you would
use in your survey.
(g) Save the file as: RAND58
Important note: Everyone who does this problem will generate a different random order of patient ID numbers since Excel assigns a different random number each time the RAND() command is used. For this reason, the answer to this problem given in this Excel Guide will have a completely different sequence of random numbers from the random sequence that you generate. This is normal and is to be expected.
Chapter 3: Test
Suppose that you are an administrator at a health care clinic facility and want to find out how the wages of a specific type of technician in your facility compare to the average wages of similar technicians in the city and county of St. Louis, Missouri, USA. The current average wage for this type of technician in your facility is $25.00 per hour. You have been asked to “run the data” to see how this wage compares to those in St. Louis. You have decided to test your Excel skills on a random sample of technicians in St. Louis and you have created the hypothetical data given in Fig. B.2
(a) Create an Excel table for these data, and use Excel to the right of the table to
find the sample size, mean, standard deviation, and standard error of the mean
for these data. Label your answers, and round off the mean, standard deviation,
and standard error of the mean to two decimal places in currency format.
(b) By hand, write the null hypothesis and the research hypothesis on your printout.
(c) Use Excel’s TINV function to find the 95% confidence interval about the mean for these data. Label your answers. Use two decimal places for the confidence
interval figures in currency format.
(d) On your printout, draw a diagram of this 95% confidence interval by hand,
including the reference value.
(e) On your spreadsheet, enter the result. (f) On your spreadsheet, enter the conclusion in plain English. (g) Print the data and the results so that your spreadsheet fits onto one page.
(h) Save the file as: HOURLY3
Chapter 4: Test
The American College of Healthcare Executives (ACHE) is an international pro- fessional association that has more than 40,000 healthcare executives as members. ACHE holds an annual Congress on Healthcare Leadership which draws more than 4,500 participants from around the world to Chicago, Illinois (USA). ACHE offers a variety of educational programs. One format that it is using allows members to
Fig. B.2 Worksheet Data for Chap. 3 Test (Practical Example)
attend online Webinars, instead of having to spend travel funds and time to go to
seminars in cities around the world. Suppose that you have been asked to develop a
survey that can be emailed to members who have taken a Webinar to determine
their preference for that method of presentation. You are sure that you want to
include an item that deals with the extent to which Webinar participants prefer that
method of educational delivery, and you want to test your Excel skills on a small
sample of data using the hypothetical data given in Fig. B.3.
Fig. B.3 Worksheet Data for Chap. 4 Test (Practical Example)
(a) Write the null hypothesis and the research hypothesis on your spreadsheet.
(b) Create a spreadsheet for these data, and then use Excel to find the sample size,
mean, standard deviation, and standard error of the mean to the right of the data
set. Use number format (two decimal places) for the mean, standard deviation,
and standard error of the mean.
(c) Type the critical t from the t-table in Appendix E onto your spreadsheet, and label it.
(d) Use Excel to compute the t-test value for these data (use two decimal places)
and label it on your spreadsheet.
(e) Type the result on your spreadsheet, and then type the conclusion in plain English on your spreadsheet.
(f) Save the file as: RATING10
Chapter 5: Test
A healthcare administrator wants to determine if there is a cost of stay (COS) difference between male and female adult patients who were admitted with the same condition within the past 60 days, and who have since been discharged and billed for services rendered. You want to test your Excel skills on the hypothetical data given in Fig. B.4.
(a) Write the null hypothesis and the research hypothesis.
(b) Create an Excel table that summarizes these data.
(c) Use Excel to find the standard error of the difference of the means.
Fig. B.4 Worksheet Data for Chap. 5 Test (Practical Example)
(d) Use Excel to perform a two-group t-test. What is the value of t that you obtain (use two decimal places)?
(e) On your spreadsheet, type the critical value of t using the t-table in Appendix E. (f) Type the result of the test on your spreadsheet. (g) Type your conclusion in plain English on your spreadsheet. (h) Save the file as: STAY21
(i) Print the final spreadsheet so that it fits onto one page.
Chapter 6: Test
A healthcare administrator at a large multi-institutional organization (ABC) wants to do a “employee satisfaction survey” with managers at the different locations and has asked you to design a survey that can be sent via email to a random sample of managers. You have not yet completed the design of the survey, but know that you want to include items that ask the managers how satisfied they are with their jobs and also their likelihood their leaving employment at ABC sometime during the next 2 years. Suppose you want to study this relationship using the hypothetical data for Item 18 and item 30 in your current working draft of the survey and want to test your Excel skills on the hypothetical data that are given in Fig. B.5.
Fig. B.5 Worksheet Data for Chap. 6 Test (Practical Example)
Create an Excel spreadsheet, and enter the data.
(a) create an XY scatterplot of these two sets of data such that:
• top title: RELATIONSHIP BETWEEN JOB SATISFACTION AND LIKE-
LIHOOD OF LEAVING ABC
• x-axis title: JOB SATISFACTION
• y-axis title: LIKELIHOOD OF LEAVING ABC
• move the chart below the table
• re-size the chart so that it is 7 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. (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 the coefficients
Print just the input data and the chart so that this information fits onto one page in portrait format.
Then, print just the regression output table on a separate page so that it fits onto one page.
By hand: (d) Circle and label the value of the y-intercept and the slope of the regression line
on your printout.
(e) Write the regression equation by hand on your printout for these data (use three decimal places for the y-intercept and the slope).
(f) Circle and label the correlation between the two sets of scores in the regression analysis summary output table on your printout.
(g) Underneath the regression equation you wrote by hand on your printout, use the
regression equation to predict the likelihood of leaving ABC employment for a
manager with a job satisfaction score of 3.
(h) Estimate from the graph, the average likelihood of leaving ABC you would predict for a manager with a job satisfaction score of 6, and write your answer in
the space immediately below:
________________________
(i) save the file as: SATIS10
Chapter 7: Test
The Graduate Management Admission Test (GMAT) is a three-and-a-half hour exam that is accepted by almost 6,000 Business and Management programs in more than 80 countries as part of the admission application for people who want to obtain a graduate degree. This test is taken by more than 200,000 applicants each year. Suppose that a major university that offers a Master’s degree in Health Adminis- tration and requires a GMAT score as part of the application wants to know how well GMAT scores of applicants predict their grade-point average (GPA) at the end
of the first year of graduate school. The GMAT has four subtest scores: (1) Verbal
(score range 0–60), (2) Quantitative (score range 0–60), (3) Analytical writing
(score range 0–6 in 0.5 intervals), and (4) Integrated Reasoning (score range
1–8). You have decided to use these four subtest scores as predictors of first-year
GPA, and to check your skills in Excel, you have created the hypothetical data
given in Fig. B.6.
(a) create an Excel spreadsheet using FIRST-YEAR GPA as the dependent (crite-
rion) variable ( Y ), and the other variables as the four predictors of this criterion
(X1¼VERBAL, X2¼QUANTITATIVE, X3¼ANALYTICAL WRITING, and X4¼ INTEGRATED REASONING ).
(b) Use Excel’s multiple regression function to find the relationship between these five 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 three decimal places for the coefficients in the
SUMMARY OUTPUT.
(d) Save the file as: GMAT10
(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 the multiple correlation Rxy? 2. What is the y-intercept a? 3. What is the coefficient for VERBAL, b1? 4. What is the coefficient for QUANTITATIVE, b2? 5. What is the coefficient for ANALYTICAL WRITING, b3?
Fig. B.6 Worksheet Data for Chap. 7 Test (Practical Example)
6. What is the coefficient for INTEGRATED REASONING, b4? 7. What is the multiple regression equation?
8. Predict the FIRST-YEAR GPA you would expect for a VERBAL score of
52, a QUANTITATIVE SCORE OF 48, an ANALYTICAL WRITING
SCORE of 4.5, and an INTEGRATED REASONING SCORE OF 6.
(f) Now, go back to your Excel file and create a correlation matrix for these five
variables, and place it underneath the SUMMARY OUTPUT.
(g) Re-save this file as: GMAT10
(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):
9. What is the correlation between VERBAL and FIRST-YEAR GPA?
10. What is the correlation between QUANTITATIVE and FIRST-YEAR GPA?
11. What is the correlation between ANALYTICAL WRITING and FIRST-
YEAR GPA?
12. What is the correlation between INTEGRATED REASONING and FIRST-
YEAR GPA?
13. What is the correlation between VERBAL and QUANTITATIVE?
14. What is the correlation between QUANTITATIVE and ANALYTICAL
WRITING?
15. What is the correlation between ANALYTICAL WRITING and INTE-
GRATED REASONING?
16. What is the correlation between QUANTITATIVE and INTEGRATED
REASONING?
17. Discuss which of the four predictors is the best predictor of FIRST-
YEAR GPA.
18. Explain in words how much better the four predictor variables combined
predict FIRST-YEAR GPA than the best single predictor by itself.
Chapter 8: Test
A budget request from a long-term care facility needs to be based, in part, on the complexity of care required by each resident A healthcare administrator wants to determine the case complexity by comparing a random sample of patients from three facilities (A, B, C) that are part of this multi-institutional organization on the number of secondary diagnoses required by adult women patients (ages 50–60) who have been admitted to the facilities during the 12 months preceding the past 60 days. You decide to test your Excel skills on a small sample of residents from each of three facilities, and you have created the hypothetical data given in Fig. B.7.
(a) Enter these data on an Excel spreadsheet.
Let FACILITY A¼Group 1, FACILITY B¼Group 2, and FACILITY C ¼Group 3.
(b) On your spreadsheet, write the null hypothesis and the research hypothesis for
these data.
(c) Perform a one-way ANOVA test on these data, and show the resulting ANOVA table underneath the input data for the three facilities.
(d) If the F-value in the ANOVA table is significant, create an Excel formula to
compute the ANOVA t-test comparing FACILITY B versus FACILITY C, and
show the results below the ANOVA table on the spreadsheet (put the standard
error and the ANOVA t-test value on separate lines of your spreadsheet, and use
two decimal places for each value)
(e) Print out the resulting spreadsheet so that all of the information fits onto one
page
(f) On your printout, label by hand the MS (between groups) and the MS (within
groups)
(g) Circle and label the value for F on your printout for the ANOVA of the input
data
(h) Label by hand on the printout the mean for FACILITY B and the mean for
FACILITY C that were produced by your ANOVA.
(i) Save the spreadsheet as: SECONDARY3
Fig. B.7 Worksheet Data for Chap. 8 Test (Practical Example)
On a separate sheet of paper, now do the following by hand:
(j) Find the critical value of F in the ANOVA Single Factor results table.
(k) Write a summary of the result of the ANOVA test for the input data. (l) Write a summary of the conclusion of the ANOVA test in plain English for the
input data.
(m) Write the null hypothesis and the research hypothesis comparing FACILITY B
versus FACILITY C.
(n) Compute the degrees of freedom for the ANOVA t-test by hand for three types of facilities.
(o) Use your calculator and Excel to compute the standard error (s.e.) of the
ANOVA t-test.
(p) Use your calculator and Excel to compute the ANOVA t-test value.
(q) Write the critical value of t for the ANOVA t-test using the table in Appendix E.
(r) Write a summary of the result of the ANOVA t-test. (s) Write a summary of the conclusion of the ANOVA t-test in plain English.
