Question

Sandra is the manager of human resources at ABC Inc. As part of his yearly report to the CEO she is required to present an analysis of the salaried employees. Because there are over 1,000 employees, she does not have the staff to gather information on each salaried employee, so she selects a random sample of 40. For each employee she records monthly salary, service at ABC (in months), gender (1=male, 0=female), and whether the employee has a technical or clerical job (technical=1, clerical=0).

The data referring to all 40 employees is presented in the table below.

Please use Excel/Data Analysis or Stats to answer the following questions:

Which are the dependent and which are the independent variables? Is there a linear relationship between the dependent and each of the independent variables?

Which independent variable has the strongest correlation with the dependent variable? Which independent variable has the weakest correlation with the dependent variable? Does it appear there will be any problems with multicollinearity?

Employee	Salary	Service	Age	Gender	Job
1	1,945.9	93	42	0	0
2	1,914.0	104	33	1	0
3	2,135.1	104	42	0	1
4	2,603.7	126	57	1	0
5	2,713.7	98	30	1	1
6	1,804.0	99	49	1	1
7	1,931.6	94	35	1	0
8	1,876.6	96	46	0	1
9	1,943.7	124	56	0	0
10	1,320.0	73	23	0	1
11	1,876.6	110	67	0	1
12	2,183.5	90	36	0	1
13	1,710.5	104	53	0	0
14	1,923.9	81	29	0	0
15	2,261.6	106	45	1	0
16	1,901.9	113	55	0	1
17	2,404.6	129	46	1	1
18	2,043.8	97	39	0	0
19	2,000.9	101	43	1	1
20	1,485.0	91	35	0	1
21	2,233.0	100	40	1	0
22	2,805.0	123	59	1	0
23	1,698.4	88	30	0	0
24	1,942.6	117	60	1	1
25	2,130.7	107	45	1	1
26	1,860.1	105	32	0	1
27	1,785.3	86	33	0	0
28	1,970.1	131	56	0	1
29	2,201.1	95	30	1	1
30	2,061.4	98	47	0	0
31	2,211.0	120	60	1	1
32	1,870.0	87	29	0	0
33	1,844.7	100	65	0	0
34	2,082.3	105	27	0	1
35	2,136.2	86	37	1	0
36	1,735.8	93	39	1	1
37	2,871.0	97	47	1	0
38	1,939.3	100	42	0	0
39	2,075.7	105	40	1	1
40	2,436.5	127	49	0	1

Answer 1

Sandra is the manager of human resources at ABC Inc. As part of his yearly report to the CEO she is required to present an analysis of the salaried employees. Because there are over 1,000 employees, she does not have the staff to gather information on each salaried employee, so she selects a random sample of 40. For each employee she records monthly salary, service at ABC (in months), gender (1=male, 0=female), and whether the employee has a technical or clerical job (technical=1, clerical=0).

The data referring to all 40 employees is presented in the table below.

Please use Excel/Data Analysis or Stats to answer the following questions:

Which are the dependent and which are the independent variables? Is there a linear relationship between the dependent and each of the independent variables?

Here dependent variable is salary and independent variables are service , age, gender and job.

This is the problem of multiple linear regression.

Here we have to test the hypothesis that,

H0 : There is no relationship between dependent variable and independent variable.

H1 : There is relationship between dependent variable and independent variable.

Assume alpha = level of significance = 0.05

We can do this test in MINITAB.

steps :

ENTER data into MINITAB sheet --> Stat --> Basic statistics --> Correlation --> Variables : select all the variables together --> Display p-values --> ok

————— 09-12-2018 20:28:40 ————————————————————

Correlation: Salary, Service, Age, Gender, Job

Salary Service Age Gender
Service 0.463
0.003

Age 0.234 0.700
0.147 0.000

Gender 0.495 0.198 0.079
0.001 0.220 0.629

Job -0.100 0.202 0.013 0.055
0.540 0.211 0.938 0.734

Cell Contents: Pearson correlation
P-Value

Conclusion :

The p-value for salary and service is 0.003.

P-value < alpha

Reject H0 at 5% level of significance.

There is relationship between salary and service.

P-value for salary and age is 0.147 which is greator than 0.05.

Accept H0 at 5% level of significance.

There is no relationship between salary and age.

P-value for salary and gender is 0.001 which is less than 0.05.

Reject H0 at 5% level of significance.

Conclusion : There is relationship between salary and gender.

P-value for salary and job is 0.540 which is greator than 0.05.

Accept H0 at 5% level of significance.

Conclusion : There is no relationship between salary and job.

Which independent variable has the strongest correlation with the dependent variable? Which independent variable has the weakest correlation with the dependent variable? Does it appear there will be any problems with multicollinearity?

Here we have to find multicollinearity factor.

The multicollinearity factor is VIF or variance inflation factor.

We can find VIF in MINITAB.

steps :

ENTER data into MINITAB sheet --> Stat --> Regression --> Regression --> Fit regression model --> Responses : salary --> COntinuous predictors : select all the independent variables --> ok

Coefficients

Term Coef SE Coef T-Value P-Value VIF
Constant 895 319 2.81 0.008
Service 13.09 4.38 2.99 0.005 2.20
Age -5.36 5.17 -1.04 0.307 2.04
Gender 263.4 82.7 3.18 0.003 1.05
Job -148.7 83.6 -1.78 0.084 1.08

Here VIF < 10 therefore multicollinearity is low.