Great Deal! Get Instant $10 FREE in Account on First Order + 10% Cashback on Every Order Order Now

1. Review the powerpoint material for how to calculate 2. Do the calculations/analysis for the 4 questions in the excel document (show your work) 3. Answer the 4 questions based on the analysis...

1 answer below »
PowerPoint Presentation
Chapter 1
Excel Extension: Now You Try!
Computing Co
elations
Bauer, Fundamentals of Human Resource Management, 1e. © SAGE Publishing, 2021
1
1
Background
In this Excel Extension tutorial, you will learn how to compute a (Pearson product-moment) co
elation between two continuous variables using Microsoft Excel.
For the purposes of this exercise, imagine that the engagement and job satisfaction data for each employee were collected using an annual employee survey, and each employee’s unique ID was used to merge their survey data with their average annual customer satisfaction ratings, their supervisor-rated performance evaluation score, and the amount of annual sales revenue they generated.
For the purposes of this introductory exercise, we will ignore statistical significance, and instead practice interpreting the sign (positive or negative) and magnitude (size) of each co
elation.
Bauer, Fundamentals of Human Resource Management, 1e. © SAGE Publishing, 2021
2
Open the Excel workbook titled “Chapter 1--Excel Extension.xlsx”.
Note that the sheet includes six columns, where each column represents unique field/variable:
Unique employee ID (Employee ID)
Engagement (1 = low, 5 = high)
Job Satisfaction (1 = low, 5 = high)
Customer Satisfaction (1 = low, 7 = high)
Performance Evaluation Rating (1 = low, 10 = high)
Sales Revenue (U.S. dollars)
Note that each row below the column names represents a unique employee, where the total number of employees is 59.
Step 1
Bauer, Fundamentals of Human Resource Management, 1e. © SAGE Publishing, 2021
3
To practice, let’s compute the co
elation between Job Satisfaction and Performance Evaluation Rating.
Scroll down below the data to find empty cells.
Step 2
Bauer, Fundamentals of Human Resource Management, 1e. © SAGE Publishing, 2021
4
In a blank cell, enter the equal symbol (=) followed by the CORREL function, followed by a beginning parenthesis.
Enter the range of cell values that co
esponds to the a
ay of numeric values that fall below the Job Satisfaction variable name, which in this case is C2:C60. Note that you can enter the cell range manually by entering the first cell in the a
ay (C2), followed by a colon (:), and followed by the last cell in the a
ay (C60); alternatively, you can select the a
ay of cells by clicking and dragging your mouse.
Enter a comma (,).
Enter the range of cell values that co
esponds to the a
ay of numeric values that fall below the Performance Evaluation Rating variable name, which in this case is E2:E60. Enter an ending parenthesis, and click ENTER.
Step 3
Bauer, Fundamentals of Human Resource Management, 1e. © SAGE Publishing, 2021
5
Remember, co
elation coefficients can range from –1.00 to 1.00, where –1.00 indicates a perfect negative (inverse) linear association, 1.00 indicates a perfect positive linear association, and .00 indicates no association.
Note that the co
elation coefficient is approximately .015 (after rounding).
Regarding the sign, the co
elation coefficient is positive, which indicates a positive, linear association.
With that said, the magnitude of the co
elation coefficient is virtually zero. For our purposes, we describe the magnitude of a co
elation coefficient as follows:
.10 (negligible)
.10–.29 (small)
.30–.49 (medium)
.50–1.00 (large)
Step 4
Bauer, Fundamentals of Human Resource Management, 1e. © SAGE Publishing, 2021
6
It is generally a good practice to label each co
elation coefficient so that you can remember what association it refers to.
In a cell adjacent to the cell in which the co
elation coefficient appears, type information about the co
elation, and click ENTER.
Step 5
Bauer, Fundamentals of Human Resource Management, 1e. © SAGE Publishing, 2021
7

Sheet1
    Employee ID     Engagement    Job Satisfaction    Customer Satisfaction    Performance Score     Sales Revenue
    EP32    3.00    3.00    3.91    2.78    60177
    EP33    3.33    3.00    5.43    6.11    58914
    EP34    2.67    3.67    3.98    7.92    82403
    EP35    2.67    3.67    5.23    4.31    60171
    EP36    3.33    3.33    4.57    2.03    45458
    EP37    3.67    3.67    4.62    6.56    55568
    EP38    3.00    4.00    4.07    8.95    58257
    EP39    2.00    3.00    2.69    2.27    61588
    EP40    3.67    3.67    7.00    2.82    41500
    EP41    3.67    2.67    3.49    8.17    52654
    EP42    4.00    3.67    5.96    1.00    60394
    EP43    3.00    3.33    3.84    8.12    43965
    EP44    3.67    4.33    6.83    4.96    63184
    EP45    3.00    2.33    2.58    4.36    56528
    EP46    4.67    4.00    5.50    1.00    81725
    EP47    2.00    3.00    6.12    7.18    67816
    EP48    4.67    4.33    7.00    5.63    68747
    EP49    4.67    4.67    6.42    5.30    52062
    EP50    3.33    2.67    4.53    3.32    64743
    EP51    3.33    3.33    3.17    6.20    41489
    EP52    3.33    3.67    4.48    8.53    65859
    EP53    3.33    3.33    5.72    2.38    58289
    EP54    3.67    4.33    6.11    1.00    72278
    EP55    3.67    4.00    6.11    3.83    61545
    EP56    4.33    4.00    4.26    9.24    48676
    EP57    2.67    3.33    5.18    5.25    54567
    EP58    3.33    3.00    3.76    2.13    48966
    EP59    4.33    4.67    4.31    3.85    43097
    EP60    4.00    4.00    5.96    6.46    57753
    EP61    4.00    4.00    7.00    6.68    65517
    EP62    3.00    3.67    4.79    5.00    74679
    EP63    4.67    4.33    6.22    3.08    37489
    EP64    3.67    4.33    5.85    7.15    48624
    EP65    4.00    3.67    6.13    7.18    43493
    EP66    3.67    3.00    4.70    5.06    68586
    EP67    4.67    4.00    6.52    3.39    65035
    EP68    2.67    3.00    5.27    8.42    46502
    EP69    3.00    3.33    6.75    7.77    47626
    EP70    2.67    3.67    4.60    2.58    47941
    EP71    2.67    4.00    3.57    4.55    60813
    EP72    4.00    5.00    3.86    4.50    76878
    EP73    5.00    4.67    5.78    4.34    58443
    EP74    5.00    4.00    6.82    7.20    70686
    EP75    2.33    3.00    4.45    8.23    72295
    EP76    4.00    4.00    6.01    4.05    59139
    EP77    4.00    3.33    7.00    2.77    54572
    EP78    4.67    4.67    6.68    8.10    62575
    EP79    3.00    2.33    4.23    6.11    67908
    EP80    3.00    3.00    4.06    3.17    61539
    EP81    3.67    4.67    6.84    7.85    66603
    EP82    4.33    3.33    6.14    7.61    51412
    EP83    4.00    4.00    5.06    5.88    76823
    EP84    3.67    3.67    6.63    6.13    36194
    EP85    3.33    3.00    7.00    7.86    55104
    EP86    4.33    4.67    5.15    6.32    78592
    EP87    2.33    3.33    4.30    4.25    42043
    EP88    3.67    3.33    3.08    4.68    68745
    EP89    3.00    3.67    4.67    2.62    59975
    EP90    3.33    4.00    7.00    7.34    49173

MGMT 6100
HR Analytics Assignment
1. Open the Excel workbook titled “Chapter 1--Excel Extension.xlsx” (posted on Canvas > Assignments > HR Analytics Assignment) and answer the following four questions (a through d). Be sure to interpret the sign and magnitude of the co
elations.
*If you are not sure on how to calculate co
elations, look at How to Calculate Co
elations Using Excel (posted on Canvas) for an example.
*If you did not attend Session 3, please read the section on Co
elation Does Not Equal Causation on page 28 in your textbook.
a. What is the co
elation between Employee Engagement and Sales Revenue? (5 points)
. Consider the sign and the magnitude of the co
elation. How would you describe the nature of the relation between Engagement and Sales Revenue to a manager who does not know what a co
elation coefficient means? (5 points)
c. What is the co
elation between Job Satisfaction and Customer Satisfaction? (5 points)
d. Consider the sign and the magnitude of the co
elation. How would you describe the nature of the relation between Job Satisfaction and Customer Satisfaction? (5 points)
2. Submit your answers to the four questions in one WORD document and upload it on Canvas.
Answered Same Day Sep 14, 2023

Solution

Komalavalli answered on Sep 14 2023
26 Votes
a.
The co
elation between Employee Engagement and Sales Revenue is 0.05
.
Firstly, let's discuss the sign of the co
elation coefficient. A positive co
elation would suggest that as one variable (in this case, Engagement) increases, the other variable (Sales Revenue) tends to increase as well. Conversely, a negative co
elation would mean that as one variable increases, the other tends to decrease. However, in this case, with a co
elation coefficient of 0.05, it means that there is a very slight tendency for Engagement and Sales Revenue to move together in some way, but it's not a strong or noticeable pattern.
Imagine a scatter plot, which is a graphical representation of the data points for Engagement and Sales Revenue. In this case, the data points would be spread out in a way that doesn't show a clear trend or pattern. They don't form a tight cluster, and they...
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here