Question

I am asking the SAME question that was not answered completely in the ExpertQ&A. Others commented that they do not understnad as well. Please provide the answers in Excel and detail the answers to each questions. Thank you.

Question: Create a Final Regression Model based on the data below and provide answers to the questions below. Please provide the details on how you got to the answers.

(1) How does each independent variable in your model affect the salary?

(2) Which variables (if any) did you need to transform in order to achieve linearity?

(3) Which variables (even if they didn’t end up being in the final model) required transformation into dummy variables?

(4) How do you interpret your R^2 value?

Salary	Years_Previous_Experience	Years_Employed	College degree	Gender	Department	Number_Supervised
$34,062	0	0	None	Male	Marketing	2
$54,069	9	19	BS	Female	Marketing	6
$52,577	6	6	BS	Male	Operations	2
$79,055	5	12	MBA	Male	Operations	0
$50,211	5	7	BS	Male	Operations	1
$57,230	6	9	BS	Male	Operations	1
$76,657	0	25	MBA	Female	Marketing	3
$111,309	3	22	PHD	Female	Operations	45
$45,400	11	3	None	Female	Sales	6
$118,891	0	27	PHD	Male	Operations	44
$59,407	4	9	MBA	Female	Operations	4
$56,138	7	18	MBA	Female	Sales	5
$66,544	5	14	MBA	Male	Operations	5
$45,252	6	7	BS	Male	Marketing	6
$57,352	6	18	BS	Male	Sales	5
$51,101	2	8	None	Male	Operations	2
$51,309	4	6	BS	Female	Operations	2
$43,021	0	2	None	Male	Purchasing	5
$38,091	3	1	None	Male	Operations	0
$83,820	19	6	MBA	Female	Operations	40
$51,072	6	3	MBA	Male	Purchasing	3
$78,738	3	20	MBA	Male	Marketing	4
$35,639	2	6	None	Male	Sales	1
$81,109	3	12	MBA	Female	Purchasing	6
$62,891	9	6	BS	Female	Purchasing	2
$51,074	5	9	BS	Male	Purchasing	5
$49,293	2	6	MBA	Female	Operations	3
$47,206	1	0	MBA	Female	Purchasing	0
$38,229	1	5	None	Female	Operations	2
$60,320	16	22	MBA	Female	Sales	7
$52,662	1	6	BS	Female	Purchasing	2
$55,859	4	21	BS	Female	Sales	9
$38,272	6	0	None	Male	Purchasing	2
$47,405	3	15	MBA	Male	Sales	4
$52,541	5	6	None	Female	Operations	3
$68,452	5	15	MBA	Female	Marketing	4
$52,672	4	4	MBA	Female	Marketing	8
$56,637	3	9	MBA	Male	Operations	1
$98,532	2	25	PHD	Female	Operations	1
$74,445	6	18	MBA	Female	Operations	1
$66,505	3	20	BS	Female	Operations	1
$52,461	4	9	BS	Male	Purchasing	2
$49,870	4	5	BS	Female	Operations	0
$42,549	6	5	None	Female	Sales	0
$50,381	6	9	None	Male	Operations	3
$41,738	1	0	BS	Male	Sales	4

Answer 1

Please see the excel screenshots below , we transform all the category variables such as gender , degree and department by converting them to flag variables . We then goto data > data analysis tab and select regression

The regression equation is firmed using the coefficients.
All the variables whose p value is less than 0.05 significantly effect the salary variation . They are highlighted in blue

The r2 value is

0.899 , this means that the model is able to explain 89.9% variation is salary due to the independent variables , which is quite good as a model