Question

The personnel director for a manufacturing company is reviewing the annual salaries for all the machinists and millwrights that currently work for the company (a total of 24 workers). Besides salary, employee records also provide data for the most recent performance ratings, total years working for the company, and the number of specific jobs in the plant the worker is qualified to be assigned to. This information is included in the Assignment 5 spreadsheet. The director wants to build a regression model to estimate the average salary an employee should expect to receive You are to use the Assignment 5 spreadsheet to build the model requested. You must then use your model to answer the following 3 questions. You must submit all regression reports that were run to answer this question. If the director wanted to build a regression model to predict salary based on 2 independent variables, which two variables should be used? Explain why and develop a regression model based on these two variables

Obs	Salary	Avg Perf.	Years	Jobs
1	48.20	3.50	9	6
2	55.30	5.30	20	6
3	53.70	5.10	18	7
4	61.80	5.80	33	7
5	56.40	4.20	31	8
6	52.50	6.00	13	6
7	54.00	6.80	25	6
8	55.70	5.50	30	4
9	45.10	3.10	5	6
10	67.90	7.20	47	8
11	53.20	4.50	25	5
12	46.80	4.90	11	6
13	58.30	8.00	23	8
14	59.10	6.50	35	7
15	57.80	6.60	39	5
16	48.60	3.70	21	4
17	49.20	6.20	7	6
18	63.00	7.00	40	7
19	53.00	4.00	35	6
20	50.90	4.50	23	4
21	55.40	5.90	33	5
22	51.80	5.60	27	4
23	60.20	4.80	34	8
24	50.10	3.90	15	5

Please answer using Excel with the data above. Thanks!!

Answer 1

1) If the regression model was to be built with two independent variables then we should take "years" and "avg performance" as our requirements because these two variable are of prime importance and salary should be primarily based on these rather on the 3rd variable which is "no. of jobs"

Using minitab and excel we construct the regression models.

Using 2 independent variables we have the regression equation as :

salary = 38.25 + 1.443 avg perf + 0.3412 years

2) Using all 3 independent variables we have the regression equation as :

salary = 32.92 + 1.058 avg perf + 0.3252 years + 1.299 job

As working with more independent variable is better which are definitely of importance is better also R-sq for model 1 is 82.77% and R-sq for model 2 is 91.35% we prefer model 2 over model 1

3) We use model 3 for the estimation purpose.

Given, year= 10 years, performance=7.20, job on hand=4

we have predicted salary= 32.92 + 1.058*7.20 + 0.3252*10+ 1.299*4 = 48.9856