Description
please read the instructions and finish the questions thank you
Unformatted Attachment Preview
Module 5: HR Analytics Review
Purpose
In this final module, you will engage in various HR Analytics effects that reflect the entirety of
what we have covered this semester. This will overlap with content from the prior modules, as
well as have questions related to the last few topics we covered. Note that because you have
gotten pretty good at analyzing data by now, more emphasis in this module will be placed on
your interpretations. Being able to explain what you found, its implications, and why it is
important is critical for being successful with HR Analytics.
Note. Please use your own Mr. Macky’s dataset (not the one we use for class exercise)
Possible Options of Excel Techniques
Menu Insert
Menu Data
Pivot Table
Filter
Sort
Excel Functions If (If, CountIf, and /or
AverageIf)
Date
Average
STDEV.S
Correl
Norm.S.Dist
Excel Add-in:
Descriptive statistics
Analysis
Correlation
Toolpak
Regression
Excel Techniques you may need for this module.
Related Questions
Q1
X
X
X
X
X
X
X
Q2
X
X
X
X
X
X
X
Q3
X
X
Q4
X
X
X
X
X
X
X
X
X
X
X
X
X
X
Q5
X
X
X
X
X
X
Note. Not all techniques are necessary for each question. This table illustrates possible
options of Excel techniques that can be helpful. Some of them may be somewhat easier to
use than others, although that also depends on the user. There are usual multiple ways to
solve each problem.
83
Q1. Examining Wait staff
This first question considers specifically issues that Mr. Macky’s may be having with its wait
staff.
a) Provide basic information about currently employed wait staff:
Wait Staff
Number currently employed
% Women in position
Answer
% URM in position
Mean Pay (in 2022)
Hint: You can use
the Pivot Table to
calculate the basic
information of the
currently employed
wait staff.
Standard Deviation of (2022) Pay
Mean tenure
Most common recruitment source
Turnover information (So, looking at past and current employees)
In this question, we define the unit turnover rate as:
=
ℎ ℎ
× 100
ℎ
Answer the following question:
b) What is the average number of wait staffs employed per unit on January 1, 2022?
________
c) What is the average number of wait staffs who left each unit in 2022? _________
d) Which unit has the highest unit turnover rate of wait staffs in 2022? _______
e) Which unit has the lowest unit turnover rate of wait staffs in 2022? _______
Hint 1: Average is determined by averaging the
number of individuals employed on January 1 with the
number employed on December 31.
Hint 2: Using the COUNTIFS function to calculate
Q1b, and creating the Pivot Table to calculate Q1c.
For Q1d and Q1e, you can use the Sort function.
84
Q2. Managers’ leadership behaviors
In addition to engagement, Mr. Macky’s has included in its annual attitude survey questions
about what employees think of their managers’ leadership. These questions are located with the
information on the engagement survey, called “MGR Vision,” “MGR Staff Development,”
“MGR Supportive Leadership,” and “MGR Innovative Thinking.”
Hint: To answer Question 2 parts a-c, you should be using the
“2022 Raw Engagement Data Subset” data tab.
a) It is predicted that all of these individual questions capture information about a manager’s
overall leadership ability. Therefore, a leadership measure may be best approximated by
averaging each individual’s responses to the four questions. But before doing this, this
assumption needs to be tested.
To answer this question, you can use the ANOVA: Two-Factor without Replication”,
where (as a reminder, this is from week 9 lecture 23):
= 1 − (
)
What is the coefficient alpha of this four-item measure of leadership? _________
Hint: You can use VAR.S function to calculate the
sample variance and total variance of the four questions.
b) The company also thinks that the measure can help differentiate between units. This
assumption also needs to be tested. What is the percent of variance attributable to unit
membership of the leadership score.
What percent of variance of this leadership measure is attributable to the unit (i.e., the
unit)? __________
=
c) What is the correlation of Leadership and Employee Engagement (employee engagement
is the average of the nine engagement questions) based on the individual-level data?
___________
85
d) Now, look at the full engagement survey results (from the Engagement Survey Results
tab). What is the relationship between Leadership (based on the four items) and
Engagement (based on the nine items) for Non-Exempt employees by year?
Non-Exempt Employees
Correlation between Leadership and Engagement
2016
2017
2018
2019
2020
2021
2022
Hint 1: To answer Question 2d, you should be using
the “Engagement Survey Results” data tab.
Hint 2: You can use both Filter and Pivot Table to
solve this question.
86
Run a regression to determine the relative effects of Performance, Engagement, and Leadership.
e) Run the appropriate regression.
Y= Sales (years 2017-2022)
Coefficients
P-value
(Use 4 Decimal
Places)
Significant Effect?
(p < .05)
X1= Prior Year’s Sales
(Yes or No)
X2= Market (1=Large, 0 =
Medium)
X3= Assistant Manager
performance rating
(Yes or No)
X4= Manager performance rating
(Yes or No)
X5 = Non-Exempt Employee
Ratings of Engagement
X6 = Non-Exempt Employee
Ratings of Managerial Leadership
(Yes or No)
(Yes or No)
(Yes or No)
Hint: You should filter out the blank values of “Manager’s performance rating” and the
“Assistant Manager’s performance rating”(X3 and X4). Also, you can use the “Restaurant
performance Info” data tab to calculate Y, X1 and X2, use the “Manager Data by Year” to
calculate X3 and X4, and use the “Engagement Survey Results” to calculate X5 and X6.
f) How do you interpret these findings? Using plain language, explain the implications
of your findings to Mr. Macky’s.
87
Q3. Moderation
In Q3, you are asked to examine whether certain effects are moderated; that is, does the effect of
one thing depend on the level of something else? In particular, you want to see if the value of
employee performance is moderated. You will examine this in a few ways.
Look to see if the effect of managers’ performance ratings is non-linear. Predict sales, controlling
for prior sales, market, and assistant manager performance ratings. Is the effect of managers’
performance non-linear?
a) Run the appropriate regression.
Y= Sales (years 2017-2022)
Coefficients
P-value
(Use 4 Decimal
Places)
Significant Effect?
(p < .05)
X1= Prior Year’s Sales
(Yes or No)
X2= Market (1=Large, 0 =
Medium)
X3= Assistant Manager
performance rating
(Yes or No)
X4= Manager performance rating
(Yes or No)
X5 = Non-linear effect of
Manager’s performance rating
(Yes or No)
(Yes or No)
Hint: You should filter out the blank value of “Manager’s performance rating”
and the “Assistant Manager’s performance rating”(X3 and X4). Also, you can
use the “Manager Data by Year” data tab to calculate X3 and X4.
b) Using plain language, interpret what you found and explain its implications to Mr. Macky’s.
88
Now, look to see if the effect of managers’ performance ratings depends on the type of store
being examined? That is, is the effect of a manager’s performance different in a large market
store than in a medium market store?
c) Run the appropriate regression (without the nonlinear component)
Y= Sales (years 2017-2022)
Coefficients
P-value
(Use 4 Decimal
Places)
Significant Effect?
(p < .05)
X1= Prior Year’s Sales
(Yes or No)
X2= Market (1=Large, 0 =
Medium)
X3= Assistant Manager
performance rating
(Yes or No)
X4= Manager performance rating
(Yes or No)
X5 = Moderating effect of
manager’s performance by store
market
(Yes or No)
(Yes or No)
d) Run the appropriate regression (with the nonlinear component)
Y= Sales (years 2017-2022)
Coefficients
P-value
(Use 4 Decimal
Places)
Significant Effect?
(p < .05)
X1= Prior Year’s Sales
(Yes or No)
X2= Market (1=Large, 0 =
Medium)
X3= Assistant Manager
performance rating
(Yes or No)
X4= Manager performance rating
(Yes or No)
X5 = Non-linear effect of
manager’s performance rating
X5 = Moderating effect of
Manager’s performance by store
Market
(Yes or No)
(Yes or No)
(Yes or No)
e) Using plain language, interpret what you found and explain its implications to Mr. Macky’s.
89
Now, look to see if the effect of managers’ performacne ratings depends on the level of the
assistant manager’s performance rating. Again, predict sales, controlling for prior sales, market,
and assistant manager performance ratings. Is the effect of a manager’s performance moderated
by the assistant manager’s performance?
f) Run the appropriate regression
Y= Sales (years 2017-2022)
Coefficients
P-value
(Use 4 Decimal
Places)
Significant Effect?
(p < .05)
X1= Prior Year’s Sales
(Yes or No)
X2= Market (1=Large, 0 =
Medium)
X3= Assistant Manager
performance rating
(Yes or No)
X4= Manager performance rating
(Yes or No)
X5 = Moderating effect of
Manager’s performance by
Assistant Manager’s performance
(Yes or No)
(Yes or No)
g) Using plain language, interpret what you found and explain its implications to Mr. Macky’s.
90
Q4. Path Models
In Q4, you are asked to think more deeply about the way manager and assistant manager
performance end up influencing unit performance. It is possible the higher manager performance
leads to higher assistant manager performance, which influences unit performance. It is also
possible that higher assistant manager performance increases managers’ performance, and thus
has this indirect effect on unit performance. It could also be that the effects of performance are
unrelated. Consider these issues in this question.
a. What is the correlation between manager and assistant manager performance (use all
possible years of data)? _____________
Conduct the necessary regressions to fill in the effects for the following models. (Note that you
should use the same dataset for all three regressions here).
b) Regression Model 1
Y= Unit Sales (years 2017-2022)
Coefficients
P-value
(Use 4 Decimal
Places)
Significant Effect?
(p < .05)
X1= Prior Year’s Sales
(Yes or No)
X2= Market (1=Large, 0 = Medium)
(Yes or No)
X3= Assistant Manager
performance rating
(Yes or No)
(Yes or No)
X4= Manager performance rating
Hint: You should filter out the blank value
of “Manager’s performance rating” and the
“Assistant Manager’s performance rating”.
c) Regression Model 2
Y= Assistant Manager performance
rating (Same dataset as for Model 1)
Coefficients
P-value
(Use 4 Decimal
Places)
Significant Effect?
(p < .05)
X1= Market (1=Large, 0 = Medium)
(Yes or No)
X2= Manager performance rating
(Yes or No)
d) Regression Model 3
Y= Manager performance rating
(Same dataset as for Model 1)
Coefficients
P-value
(Use 4 Decimal
Places)
Significant Effect?
(p < .05)
X1= Market (1=Large, 0 = Medium)
(Yes or No)
X2= Assistant Manager performance
rating
(Yes or No)
91
e) Fill in the following path model and answer the related questions. (Note: this should be based
on the two regressions you just ran. There is no need to put in the effects of market or prior
sales into the figure, although they are based on regressions that used this information.)
(Please use three decimal places).
Path 3
Coefficient: ______________
Standard Error: ___________
p-value: _________________
Manager
Performance
Unit
Performance
Path 1
Coefficient: ____________
Standard Error: _________
Assistant
Manager
Performance
Path 2
Coefficient: ______________
Standard Error: ___________
p-value: _______________
p-value: _________________
92
Formula for Sobel Test
= ℎ 1 × ℎ 2
= {[( ℎ 1 )2 ×
( ℎ 2)2] + [( ℎ 2 )2 × ( ℎ 1)2]}
Z Score = Size of Indirect Effect / Standard Error of Indirect Effect
f) What is the INDIRECT EFFECT of Manager Performance through Assistant Manager
performance? ____________
Hint 1: You can’t just look these numbers up in a single
regression output. You need to compute it yourself, to conduct a
“Sobel Test.”
g) What is the standard error of this indirect effect?____________
Hint 2: Make sure you are using the data in the Excel, otherwise
your number may be slightly different due to rounding.
h) What is the Z-score of the indirect effect? Is it statistically significant?____________
Hint 3: After computing the Z score, the first step you need to do
is to decide if it is the one-tailed or a two-tailed test. Then, for
one-tailed test, if Z-score > 1.645, it is statistically significant. For
two-tailed test, if Z-score > 1.960, it is statistically significant.
Make sure you are using the cut off score for a two-tailed test.
i) Using plain language, interpret your results:
93
j) Fill in the following path model. (Note: this should be based on the two regressions you just
ran. There is no need to put in the effects of market or prior sales into the figure, although
they are based on regressions that used this information.)
Path 3
Coefficient: ______________
Standard Error: ___________
p-value: _________________
Assistant
Manager
Performance
Unit
Performance
Path 1
Coefficient: ____________
Standard Error: _________
Manager
Performance
Path 2
Coefficient: ______________
Standard Error: ___________
p-value: _______________
p-value: _________________
k) What is the INDIRECT EFFECT of Assistant Manager Performance through Manager
performance? ___________
l) What is the standard error of this indirect effect? ___________
m) What is the Z-score of the indirect effect? Is it statistically significant? ___________
n) Interpret your results:
94
o) Consider the model with NO mediation effects:
Path 1
Coefficient: ______________
Standard Error: ___________
p-value: _________________
Assistant
Manager
Performance
Unit
Performance
Manager
Performance
Path 2
Coefficient: ______________
Standard Error: ___________
p-value: _________________
p) Based on the three models you ran, which one do you think best describes the way that
Managers’ and Assistant Managers’ performance influences Unit Performance. Why did
you reach this conclusion?
95
Q5. Leadership Trajectories
Explore the effects of leadership a little bit more. Calculate the trajectory of leadership scores as
rated by non-exempt employees, from 2016 to 2022 (see the Engagement Survey Results data
tab).
To measure a leadership trajectory, you need to begin by organizing your data appropriately.
You will need to compute the leadership score (based on the four items), and then put them in a
single row.
Hint 1: There are multiple ways to do this. One way is to first filtered out everyone
but the Non-Exempt data and pasted that on a new sheet. Then used an IF
statement to look at year, and then reference the appropriate cell (something like
“if the year is 2022, put in the value for 2022, otherwise blank”; then the next
column was “if the year is 2021, put in the value for 2021, otherwise blank.”
Repeat this for all the data to 2016. You can then filter out all the blank data for
2022 (but could use any year) and pasted the remaining data in a new sheet. This
ends up giving you all 7 years of leadership scores in a single row.
Once the leadership data is in a single row, compute the correlation between leadership scores
and time (from 2016=0 to 2022=6). Note that every line will have data for all 7 years, so you
don’t have to worry about blank values or error checking. Likewise, because there is no missing
data, you can simply compute the standard deviation of the values from 0 to 6. Then, you need to
multiply each row’s correlation by the standard deviation of that row’s leadership scores (the Y
variable), and divide it by the standard deviation of time (the X variable).
Hint 2: When you calculate performance trajectories, you have to
worry the about all the performance scores being equal. Given the
nature of the data (i.e., it is an average to two decimal places), it is very
unlikely that you need to worry about this issue for this problem.
a) Answer the following questions:
Statistic
Average trajectory
Answer
Standard deviation of trajectory scores
Minimum trajectory
Maximum trajectory
96
b) Does leadership trajectory matter? Run the following regression. Note that this regression is
predicting ONLY 2022 sales, and the leadership and engagement data are ONLY for nonexempt employees.
Y= 2022 Unit Sales
Coefficients
P-value
(Use 4 Decimal
Places)
Significant Effect?
(p < .05)
X1= 2021 Unit Sales
(Yes or No)
X2= Market (1=Large, 0 = Medium)
(Yes or No)
X3= Assistant Manager
performance rating (2022)
X4= Manager performance rating
(2022)
(Yes or No)
X5 = 2022 Leadership Score
(Yes or No)
X6 = 2022 Engagement Score (from
Non-Exempt Employees)
X7 = Leadership Trajectory (from
Non-Exempt Employees)
(Yes or No)
(Yes or No)
(Yes or No)
c) Interpret the above regression. That is, what does this say seems to matter most for predicting
2022 sales (not including prior sales or market)?
97
Module 5: HR Analytics Review
Name: ___________
Dataset Number: ______________
Purpose
In this final module, you will engage in various HR Analytics effects that reflect the entirety of
what we have covered this semester. This will overlap with content from the prior modules, as
well as have questions related to the last few topics we covered. Note that because you have
gotten pretty good at analyzing data by now, more emphasis in this module will be placed on
your interpretations. Being able to explain what you found, its implications, and why it is
important is critical for being successful with HR Analytics.
Note. Please use your own Mr. Macky’s dataset (not the one we use for class exercise)
Possible Options of Excel Techniques
Menu Insert
Menu Data
Pivot Table
Filter
Sort
Excel Functions If (If, CountIf, and /or
AverageIf)
Date
Average
STDEV.S
Correl
Norm.S.Dist
Excel Add-in:
Descriptive statistics
Analysis
Correlation
Toolpak
Regression
Excel Techniques you may need for this module.
Related Questions
Q1
X
X
X
X
X
X
X
Q2
X
X
X
X
X
X
X
Q3
X
X
Q4
X
X
X
X
X
X
X
X
X
X
X
X
X
X
Q5
X
X
X
X
X
X
Note. Not all techniques are necessary for each question. This table illustrates possible
options of Excel techniques that can be helpful. Some of them may be somewhat easier to
use than others, although that also depends on the user. There are usual multiple ways to
solve each problem.
83
84
Q1. Examining Wait staff
This first question considers specifically issues that Mr. Macky’s may be having with its wait
staff.
a) Provide basic information about currently employed wait staff:
Wait Staff
Number currently employed
% Women in position
Answer
% URM in position
Mean Pay (in 2022)
Hint: You can use
the Pivot Table to
calculate the basic
information of the
currently employed
wait staff.
Standard Deviation of (2022) Pay
Mean tenure
Most common recruitment source
Turnover information (So, looking at past and current employees)
In this question, we define the unit turnover rate as:
=
ℎ ℎ
× 100
ℎ
Answer the following question:
b) What is the average number of wait staffs employed per unit on January 1, 2022?
________
c) What is the average number of wait staffs who left each unit in 2022? _________
d) Which unit has the highest unit turnover rate of wait staffs in 2022? _______
e) Which unit has the lowest unit turnover rate of wait staffs in 2022? _______
Hint 1: Average is determined by averaging the
number of individuals employed on January 1 with the
number employed on December 31.
Hint 2: Using the COUNTIFS function to calculate
Q1b, and creating the Pivot Table to calculate Q1c.
For Q1d and Q1e, you can use the Sort function.
85
Q2. Managers’ leadership behaviors
In addition to engagement, Mr. Macky’s has included in its annual attitude survey questions
about what employees think of their managers’ leadership. These questions are located with the
information on the engagement survey, called “MGR Vision,” “MGR Staff Development,”
“MGR Supportive Leadership,” and “MGR Innovative Thinking.”
Hint: To answer Question 2 parts a-c, you should be using the
“2022 Raw Engagement Data Subset” data tab.
a) It is predicted that all of these individual questions capture information about a manager’s
overall leadership ability. Therefore, a leadership measure may be best approximated by
averaging each individual’s responses to the four questions. But before doing this, this
assumption needs to be tested.
To answer this question, you can use the ANOVA: Two-Factor without Replication”,
where (as a reminder, this is from week 9 lecture 23):
= 1 − (
)
What is the coefficient alpha of this four-item measure of leadership? _________
Hint: You can use VAR.S function to calculate the
sample variance and total variance of the four questions.
b) The company also thinks that the measure can help differentiate between units. This
assumption also needs to be tested. What is the percent of variance attributable to unit
membership of the leadership score.
What percent of variance of this leadership measure is attributable to the unit (i.e., the
unit)? __________
=
c) What is the correlation of Leadership and Employee Engagement (employee engagement
is the average of the nine engagement questions) based on the individual-level data?
___________
86
d) Now, look at the full engagement survey results (from the Engagement Survey Results
tab). What is the relationship between Leadership (based on the four items) and
Engagement (based on the nine items) for Non-Exempt employees by year?
Non-Exempt Employees
Correlation between Leadership and Engagement
2016
2017
2018
2019
2020
2021
2022
Hint 1: To answer Question 2d, you should be using
the “Engagement Survey Results” data tab.
Hint 2: You can use both Filter and Pivot Table to
solve this question.
87
Run a regression to determine the relative effects of Performance, Engagement, and Leadership.
e) Run the appropriate regression.
Y= Sales (years 2017-2022)
Coefficients
P-value
(Use 4 Decimal
Places)
Significant Effect?
(p < .05)
X1= Prior Year’s Sales
(Yes or No)
X2= Market (1=Large, 0 =
Medium)
X3= Assistant Manager
performance rating
(Yes or No)
X4= Manager performance rating
(Yes or No)
X5 = Non-Exempt Employee
Ratings of Engagement
X6 = Non-Exempt Employee
Ratings of Managerial Leadership
(Yes or No)
(Yes or No)
(Yes or No)
Hint: You should filter out the blank values of “Manager’s performance rating” and the
“Assistant Manager’s performance rating”(X3 and X4). Also, you can use the “Restaurant
performance Info” data tab to calculate Y, X1 and X2, use the “Manager Data by Year” to
calculate X3 and X4, and use the “Engagement Survey Results” to calculate X5 and X6.
f) How do you interpret these findings? Using plain language, explain the implications
of your findings to Mr. Macky’s.
88
Q3. Moderation
In Q3, you are asked to examine whether certain effects are moderated; that is, does the effect of
one thing depend on the level of something else? In particular, you want to see if the value of
employee performance is moderated. You will examine this in a few ways.
Look to see if the effect of managers’ performance ratings is non-linear. Predict sales, controlling
for prior sales, market, and assistant manager performance ratings. Is the effect of managers’
performance non-linear?
a) Run the appropriate regression.
Y= Sales (years 2017-2022)
Coefficients
P-value
(Use 4 Decimal
Places)
Significant Effect?
(p < .05)
X1= Prior Year’s Sales
(Yes or No)
X2= Market (1=Large, 0 =
Medium)
X3= Assistant Manager
performance rating
(Yes or No)
X4= Manager performance rating
(Yes or No)
X5 = Non-linear effect of
Manager’s performance rating
(Yes or No)
(Yes or No)
Hint: You should filter out the blank value of “Manager’s performance rating”
and the “Assistant Manager’s performance rating”(X3 and X4). Also, you can
use the “Manager Data by Year” data tab to calculate X3 and X4.
b) Using plain language, interpret what you found and explain its implications to Mr. Macky’s.
89
Now, look to see if the effect of managers’ performance ratings depends on the type of store
being examined? That is, is the effect of a manager’s performance different in a large market
store than in a medium market store?
c) Run the appropriate regression (without the nonlinear component)
Y= Sales (years 2017-2022)
Coefficients
P-value
(Use 4 Decimal
Places)
Significant Effect?
(p < .05)
X1= Prior Year’s Sales
(Yes or No)
X2= Market (1=Large, 0 =
Medium)
X3= Assistant Manager
performance rating
(Yes or No)
X4= Manager performance rating
(Yes or No)
X5 = Moderating effect of
manager’s performance by store
market
(Yes or No)
(Yes or No)
d) Run the appropriate regression (with the nonlinear component)
Y= Sales (years 2017-2022)
Coefficients
P-value
(Use 4 Decimal
Places)
Significant Effect?
(p < .05)
X1= Prior Year’s Sales
(Yes or No)
X2= Market (1=Large, 0 =
Medium)
X3= Assistant Manager
performance rating
(Yes or No)
X4= Manager performance rating
(Yes or No)
X5 = Non-linear effect of
manager’s performance rating
X5 = Moderating effect of
Manager’s performance by store
Market
(Yes or No)
(Yes or No)
(Yes or No)
e) Using plain language, interpret what you found and explain its implications to Mr. Macky’s.
90
Now, look to see if the effect of managers’ performacne ratings depends on the level of the
assistant manager’s performance rating. Again, predict sales, controlling for prior sales, market,
and assistant manager performance ratings. Is the effect of a manager’s performance moderated
by the assistant manager’s performance?
f) Run the appropriate regression
Y= Sales (years 2017-2022)
Coefficients
P-value
(Use 4 Decimal
Places)
Significant Effect?
(p < .05)
X1= Prior Year’s Sales
(Yes or No)
X2= Market (1=Large, 0 =
Medium)
X3= Assistant Manager
performance rating
(Yes or No)
X4= Manager performance rating
(Yes or No)
X5 = Moderating effect of
Manager’s performance by
Assistant Manager’s performance
(Yes or No)
(Yes or No)
g) Using plain language, interpret what you found and explain its implications to Mr. Macky’s.
91
Q4. Path Models
In Q4, you are asked to think more deeply about the way manager and assistant manager
performance end up influencing unit performance. It is possible the higher manager performance
leads to higher assistant manager performance, which influences unit performance. It is also
possible that higher assistant manager performance increases managers’ performance, and thus
has this indirect effect on unit performance. It could also be that the effects of performance are
unrelated. Consider these issues in this question.
a. What is the correlation between manager and assistant manager performance (use all
possible years of data)? _____________
Conduct the necessary regressions to fill in the effects for the following models. (Note that you
should use the same dataset for all three regressions here).
b) Regression Model 1
Y= Unit Sales (years 2017-2022)
Coefficients
P-value
(Use 4 Decimal
Places)
Significant Effect?
(p < .05)
X1= Prior Year’s Sales
(Yes or No)
X2= Market (1=Large, 0 = Medium)
(Yes or No)
X3= Assistant Manager
performance rating
(Yes or No)
(Yes or No)
X4= Manager performance rating
Hint: You should filter out the blank value
of “Manager’s performance rating” and the
“Assistant Manager’s performance rating”.
c) Regression Model 2
Y= Assistant Manager performance
rating (Same dataset as for Model 1)
Coefficients
P-value
(Use 4 Decimal
Places)
Significant Effect?
(p < .05)
X1= Market (1=Large, 0 = Medium)
(Yes or No)
X2= Manager performance rating
(Yes or No)
d) Regression Model 3
Y= Manager performance rating
(Same dataset as for Model 1)
Coefficients
P-value
(Use 4 Decimal
Places)
Significant Effect?
(p < .05)
X1= Market (1=Large, 0 = Medium)
(Yes or No)
X2= Assistant Manager performance
rating
(Yes or No)
92
e) Fill in the following path model and answer the related questions. (Note: this should be based
on the two regressions you just ran. There is no need to put in the effects of market or prior
sales into the figure, although they are based on regressions that used this information.)
(Please use three decimal places).
Path 3
Coefficient: ______________
Standard Error: ___________
p-value: _________________
Manager
Performance
Unit
Performance
Path 1
Coefficient: ____________
Standard Error: _________
Assistant
Manager
Performance
Path 2
Coefficient: ______________
Standard Error: ___________
p-value: _______________
p-value: _________________
93
Formula for Sobel Test
= ℎ 1 × ℎ 2
= {[( ℎ 1 )2 ×
( ℎ 2)2] + [( ℎ 2 )2 × ( ℎ 1)2]}
Z Score = Size of Indirect Effect / Standard Error of Indirect Effect
f) What is the INDIRECT EFFECT of Manager Performance through Assistant Manager
performance? ____________
Hint 1: You can’t just look these numbers up in a single
regression output. You need to compute it yourself, to conduct a
“Sobel Test.”
g) What is the standard error of this indirect effect?____________
Hint 2: Make sure you are using the data in the Excel, otherwise
your number may be slightly different due to rounding.
h) What is the Z-score of the indirect effect? Is it statistically significant?____________
Hint 3: After computing the Z score, the first step you need to do
is to decide if it is the one-tailed or a two-tailed test. Then, for
one-tailed test, if Z-score > 1.645, it is statistically significant. For
two-tailed test, if Z-score > 1.960, it is statistically significant.
Make sure you are using the cut off score for a two-tailed test.
i) Using plain language, interpret your results:
94
j) Fill in the following path model. (Note: this should be based on the two regressions you just
ran. There is no need to put in the effects of market or prior sales into the figure, although
they are based on regressions that used this information.)
Path 3
Coefficient: ______________
Standard Error: ___________
p-value: _________________
Assistant
Manager
Performance
Unit
Performance
Path 1
Coefficient: ____________
Standard Error: _________
Manager
Performance
Path 2
Coefficient: ______________
Standard Error: ___________
p-value: _______________
p-value: _________________
k) What is the INDIRECT EFFECT of Assistant Manager Performance through Manager
performance? ___________
l) What is the standard error of this indirect effect? ___________
m) What is the Z-score of the indirect effect? Is it statistically significant? ___________
n) Interpret your results:
95
o) Consider the model with NO mediation effects:
Path 1
Coefficient: ______________
Standard Error: ___________
p-value: _________________
Assistant
Manager
Performance
Unit
Performance
Manager
Performance
Path 2
Coefficient: ______________
Standard Error: ___________
p-value: _________________
p) Based on the three models you ran, which one do you think best describes the way that
Managers’ and Assistant Managers’ performance influences Unit Performance. Why did
you reach this conclusion?
96
Q5. Leadership Trajectories
Explore the effects of leadership a little bit more. Calculate the trajectory of leadership scores as
rated by non-exempt employees, from 2016 to 2022 (see the Engagement Survey Results data
tab).
To measure a leadership trajectory, you need to begin by organizing your data appropriately.
You will need to compute the leadership score (based on the four items), and then put them in a
single row.
Hint 1: There are multiple ways to do this. One way is to first filtered out everyone
but the Non-Exempt data and pasted that on a new sheet. Then used an IF
statement to look at year, and then reference the appropriate cell (som
