Select Page

Need to work on this exam from problem 5 to 8. I have already attached submitted a file. all the question I need help with are already in that file.
exam.pdf
Unformatted Attachment Preview
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).
Fig. B.1 Worksheet Data
for Chap. 1 Test (Practical
Example)
(a) Create an Excel table for these data, and then use Excel to the right of the table
to ﬁnd 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 ﬁle 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
(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 ﬁts onto one page.
(f) Circle on your printout the I.D. number of the ﬁrst 20 patients that you would
(g) Save the ﬁle 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 ﬁnd
out how the wages of a speciﬁc 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
Fig. B.2 Worksheet Data
for Chap. 3 Test (Practical
Example)
(a) Create an Excel table for these data, and use Excel to the right of the table to
ﬁnd 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 ﬁnd the 95 % conﬁdence interval about the mean
for these data. Label your answers. Use two decimal places for the conﬁdence
interval ﬁgures in currency format.
(d) On your printout, draw a diagram of this 95 % conﬁdence interval by hand,
including the reference value.
(g) Print the data and the results so that your spreadsheet ﬁts onto one page.
(h) Save the ﬁle as: HOURLY3
Chapter 4: Test
The American College of Healthcare Executives (ACHE) is an international professional 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
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 ﬁnd 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)
(e) Type the result on your spreadsheet, and then type the conclusion in plain
(f) Save the ﬁle 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.
Fig. B.4 Worksheet Data
for Chap. 5 Test (Practical
Example)
(a) Write the null hypothesis and the research hypothesis.
(b) Create an Excel table that summarizes these data.
(c) Use Excel to ﬁnd the standard error of the difference of the means.
(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.
(h) Save the ﬁle as: STAY21
(i) Print the ﬁnal spreadsheet so that it ﬁts 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 satisﬁed 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 LIKELIHOOD 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 ﬁnd the equation for the leastsquares regression line for these data and display the results below the chart on
and three decimal places for the coefﬁcients
Print just the input data and the chart so that this information ﬁts onto one
page in portrait format.
Then, print just the regression output table on a separate page so that it ﬁts
onto one page.
By hand:
(d) Circle and label the value of the y-intercept and the slope of the regression line
(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 ﬁle as: SATIS10
Chapter 7: Test
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 Administration 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 ﬁrst 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 ﬁrst-year
GPA, and to check your skills in Excel, you have created the hypothetical data
given in Fig. B.6.
Fig. B.6 Worksheet Data for Chap. 7 Test (Practical Example)
(a) create an Excel spreadsheet using FIRST-YEAR GPA as the dependent (criterion) 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 ﬁnd the relationship between these
ﬁve 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 coefﬁcients in the
SUMMARY OUTPUT.
(d) Save the ﬁle as: GMAT10
(e) Print the table and regression results below the table so that they ﬁt onto
one page.
1.
2.
3.
4.
5.
What is the multiple correlation Rxy?
What is the y-intercept a?
What is the coefﬁcient for VERBAL, b1?
What is the coefﬁcient for QUANTITATIVE, b2?
What is the coefﬁcient for ANALYTICAL WRITING, b3?
6. What is the coefﬁcient 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 ﬁle and create a correlation matrix for these ﬁve
variables, and place it underneath the SUMMARY OUTPUT.
(g) Re-save this ﬁle 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 FIRSTYEAR GPA?
12. What is the correlation between INTEGRATED REASONING and FIRSTYEAR 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 INTEGRATED REASONING?
16. What is the correlation between QUANTITATIVE and INTEGRATED
REASONING?
17. Discuss which of the four predictors is the best predictor of FIRSTYEAR 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.
Fig. B.7 Worksheet Data for Chap. 8 Test (Practical Example)
(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 signiﬁcant, 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 ﬁts 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
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.