In: Operations Management
Employee | Years Employed | Salary |
1 | 27 | $65,487 |
2 | 20 | $46,184 |
3 | 0 | $32,782 |
4 | 12 | $54,899 |
5 | 7 | $34,869 |
6 | 8 | $35,487 |
7 | 5 | $26,548 |
8 | 15 | $32,920 |
9 | 5 | $29,548 |
10 | 6 | $34,231 |
11 | 0 | $23,654 |
12 | 9 | $39,331 |
13 | 6 | $36,512 |
14 | 3 | $35,467 |
15 | 25 | $68,425 |
16 | 9 | $35,468 |
17 | 4 | $36,578 |
18 | 18 | $39,828 |
19 | 6 | $36,487 |
20 | 19 | $37,548 |
21 | 3 | $31,528 |
22 | 5 | $34,632 |
23 | 14 | $46,211 |
24 | 2 | $29,876 |
25 | 6 | $43,674 |
26 | 18 | $38,985 |
27 | 25 | $53,234 |
28 | 18 | $51,698 |
29 | 22 | $41,889 |
30 | 21 | $38,791 |
31 | 22 | $69,246 |
32 | 6 | $48,695 |
33 | 9 | $34,987 |
34 | 0 | $28,985 |
35 | 6 | $35,631 |
36 | 20 | $54,679 |
37 | 9 | $39,743 |
38 | 9 | $41,255 |
39 | 9 | $36,431 |
40 | 0 | $26,578 |
41 | 15 | $47,536 |
42 | 6 | $36,571 |
43 | 12 | $56,326 |
44 | 7 | $31,425 |
45 | 6 | $24,749 |
46 | 1 | $26,452 |
The human resources manager of DataCom, Inc., wants to examine the relationship between annual salaries (Y) and the number of years employees have worked at DataCom (X). These data have been collected for a sample of employees and are given above.
1. Draw a normal probability plot of residuals by finding out the Z-score of the residuals. Use Excel. Plot Z-score at Y – axis and residuals at X – axis. Do the residuals seem normally distributed? Explain.
The initial graph for the data
Norml Probability Plot for X axis number of years employees have worked at DataCom.
Order the data according to the number of years employed. Then numbering is done in order from 1 - 46. Cumulative Probability is calculated.
i, number of the data . n = 46.
Cumulative Probability = (i-0.5)/n
Z score for the probability is calulated in excel using the formula NORMSINV(probability)
Years Employed | Salary | Number | Cumilative Probability | Z score |
0 | $ 32,782 | 1 | 0.0109 | -2.2949 |
0 | $ 23,654 | 2 | 0.0326 | -1.8438 |
0 | $ 28,985 | 3 | 0.0543 | -1.6041 |
0 | $ 26,578 | 4 | 0.0761 | -1.4319 |
1 | $ 26,452 | 5 | 0.0978 | -1.2940 |
2 | $ 29,876 | 6 | 0.1196 | -1.1772 |
3 | $ 35,467 | 7 | 0.1413 | -1.0745 |
3 | $ 31,528 | 8 | 0.1630 | -0.9820 |
4 | $ 36,578 | 9 | 0.1848 | -0.8973 |
5 | $ 26,548 | 10 | 0.2065 | -0.8185 |
5 | $ 29,548 | 11 | 0.2283 | -0.7446 |
5 | $ 34,632 | 12 | 0.2500 | -0.6745 |
6 | $ 34,231 | 13 | 0.2717 | -0.6076 |
6 | $ 36,512 | 14 | 0.2935 | -0.5433 |
6 | $ 36,487 | 15 | 0.3152 | -0.4811 |
6 | $ 43,674 | 16 | 0.3370 | -0.4208 |
6 | $ 48,695 | 17 | 0.3587 | -0.3619 |
6 | $ 35,631 | 18 | 0.3804 | -0.3043 |
6 | $ 36,571 | 19 | 0.4022 | -0.2477 |
6 | $ 24,749 | 20 | 0.4239 | -0.1919 |
7 | $ 34,869 | 21 | 0.4457 | -0.1367 |
7 | $ 31,425 | 22 | 0.4674 | -0.0818 |
8 | $ 35,487 | 23 | 0.4891 | -0.0272 |
9 | $ 39,331 | 24 | 0.5109 | 0.0272 |
9 | $ 35,468 | 25 | 0.5326 | 0.0818 |
9 | $ 34,987 | 26 | 0.5543 | 0.1367 |
9 | $ 39,743 | 27 | 0.5761 | 0.1919 |
9 | $ 41,255 | 28 | 0.5978 | 0.2477 |
9 | $ 36,431 | 29 | 0.6196 | 0.3043 |
12 | $ 54,899 | 30 | 0.6413 | 0.3619 |
12 | $ 56,326 | 31 | 0.6630 | 0.4208 |
14 | $ 46,211 | 32 | 0.6848 | 0.4811 |
15 | $ 32,920 | 33 | 0.7065 | 0.5433 |
15 | $ 47,536 | 34 | 0.7283 | 0.6076 |
18 | $ 39,828 | 35 | 0.7500 | 0.6745 |
18 | $ 38,985 | 36 | 0.7717 | 0.7446 |
18 | $ 51,698 | 37 | 0.7935 | 0.8185 |
19 | $ 37,548 | 38 | 0.8152 | 0.8973 |
20 | $ 46,184 | 39 | 0.8370 | 0.9820 |
20 | $ 54,679 | 40 | 0.8587 | 1.0745 |
21 | $ 38,791 | 41 | 0.8804 | 1.1772 |
22 | $ 41,889 | 42 | 0.9022 | 1.2940 |
22 | $ 69,246 | 43 | 0.9239 | 1.4319 |
25 | $ 68,425 | 44 | 0.9457 | 1.6041 |
25 | $ 53,234 | 45 | 0.9674 | 1.8438 |
27 | $ 65,487 | 46 | 0.9891 | 2.2949 |
In similar way, the normal probability plot fo rthe salary is drawn as shown below.
Employee | Years Employed | Salary | Number | Cumilative Probability | Z score |
11 | 0 | $ 23,654 | 1 | 0.0109 | -2.2949 |
45 | 6 | $ 24,749 | 2 | 0.0326 | -1.8438 |
46 | 1 | $ 26,452 | 3 | 0.0543 | -1.6041 |
7 | 5 | $ 26,548 | 4 | 0.0761 | -1.4319 |
40 | 0 | $ 26,578 | 5 | 0.0978 | -1.2940 |
34 | 0 | $ 28,985 | 6 | 0.1196 | -1.1772 |
9 | 5 | $ 29,548 | 7 | 0.1413 | -1.0745 |
24 | 2 | $ 29,876 | 8 | 0.1630 | -0.9820 |
44 | 7 | $ 31,425 | 9 | 0.1848 | -0.8973 |
21 | 3 | $ 31,528 | 10 | 0.2065 | -0.8185 |
3 | 0 | $ 32,782 | 11 | 0.2283 | -0.7446 |
8 | 15 | $ 32,920 | 12 | 0.2500 | -0.6745 |
10 | 6 | $ 34,231 | 13 | 0.2717 | -0.6076 |
22 | 5 | $ 34,632 | 14 | 0.2935 | -0.5433 |
5 | 7 | $ 34,869 | 15 | 0.3152 | -0.4811 |
33 | 9 | $ 34,987 | 16 | 0.3370 | -0.4208 |
14 | 3 | $ 35,467 | 17 | 0.3587 | -0.3619 |
16 | 9 | $ 35,468 | 18 | 0.3804 | -0.3043 |
6 | 8 | $ 35,487 | 19 | 0.4022 | -0.2477 |
35 | 6 | $ 35,631 | 20 | 0.4239 | -0.1919 |
39 | 9 | $ 36,431 | 21 | 0.4457 | -0.1367 |
19 | 6 | $ 36,487 | 22 | 0.4674 | -0.0818 |
13 | 6 | $ 36,512 | 23 | 0.4891 | -0.0272 |
42 | 6 | $ 36,571 | 24 | 0.5109 | 0.0272 |
17 | 4 | $ 36,578 | 25 | 0.5326 | 0.0818 |
20 | 19 | $ 37,548 | 26 | 0.5543 | 0.1367 |
30 | 21 | $ 38,791 | 27 | 0.5761 | 0.1919 |
26 | 18 | $ 38,985 | 28 | 0.5978 | 0.2477 |
12 | 9 | $ 39,331 | 29 | 0.6196 | 0.3043 |
37 | 9 | $ 39,743 | 30 | 0.6413 | 0.3619 |
18 | 18 | $ 39,828 | 31 | 0.6630 | 0.4208 |
38 | 9 | $ 41,255 | 32 | 0.6848 | 0.4811 |
29 | 22 | $ 41,889 | 33 | 0.7065 | 0.5433 |
25 | 6 | $ 43,674 | 34 | 0.7283 | 0.6076 |
2 | 20 | $ 46,184 | 35 | 0.7500 | 0.6745 |
23 | 14 | $ 46,211 | 36 | 0.7717 | 0.7446 |
41 | 15 | $ 47,536 | 37 | 0.7935 | 0.8185 |
32 | 6 | $ 48,695 | 38 | 0.8152 | 0.8973 |
28 | 18 | $ 51,698 | 39 | 0.8370 | 0.9820 |
27 | 25 | $ 53,234 | 40 | 0.8587 | 1.0745 |
36 | 20 | $ 54,679 | 41 | 0.8804 | 1.1772 |
4 | 12 | $ 54,899 | 42 | 0.9022 | 1.2940 |
43 | 12 | $ 56,326 | 43 | 0.9239 | 1.4319 |
1 | 27 | $ 65,487 | 44 | 0.9457 | 1.6041 |
15 | 25 | $ 68,425 | 45 | 0.9674 | 1.8438 |
31 | 22 | $ 69,246 | 46 | 0.9891 | 2.2949 |
For the residuals seem normally distributed, it has to fall on the staright line. The plot Z-score at – axis is nomally distributed, where as the salary has more outliers and hence it is not normally distributed.