Please answer the following questions based on the excel file below this introduction. Thank you! :)

Copy and paste this link into your browser to download the excel file: https://drive.google.com/file/d/1XogV9Jb2XyE4di7bC6KI5JpSyiT2Y2ay/view

Fortune magazine publishes an annual list of the 100 best companies to work for. The dataset in the excel file shows a portion of the data for a random sample of 30 of the companies that made the top 100 list for 2012. Using the dataset, you try to predict what will affect the average annual salary for salaried employee.

Here are more details about the variables

Salaried ($1000s) (Y): the average annual salary for salaried employees rounded to the nearest $1000.

Rank (X1):the rank of the company in the Fortune 100 list

Hourly ($1000s) (X2):the average annual salary for hourly employees rounded to the nearest $1000

Since the Size is a categorical variable, which has three levels. Namely,large(having more than 10,000 employees),Midsize(between 2500 and 10,000 employees) andsmall(having fewer than 2500 employees). We will introduce two dummy variables to define 3 levels of size as following andthe small is our based level:

X3,it is a dummy variable which isequal to 1,if it is a large sizecompany; which is equal to0, if it is not a large size company.

X4,it is a dummy variable which isequal to 1,if it is a midsize sizecompany; which is equal to0, if it is not midsize company.

B C D E F G Company Salaried ($1000s)Y Rank X1 Hourly ($1000s) X2 X3 X4 Size 2 Wegmans Food Markets 56.0000 4.0000 29.0000 Large NetApp 143.0000 5.0000 76.0000 Midsize 4 Camden Property Trust 71.0000 7.0000 37.0000 Small 5 Recreational Equip ment (REI) 103.0000 8.0000 28.0000 Large Quicken Loans 78.0000 10.0000 54.0000 Midsize 7 Zappos.com 48.0000 1.0000 25,0000 Midsize Mercedes-Benz USA 118,0000 12.0000 50.0000 Small 9 USAA 96.0000 20.0000 47.0000 Large 10 The Container Store 71.0000 22.0000 45,0000 Midsize 11 Ultimate Software 166.0000 25.0000 56.0000 Small 12 Plante Moran 73,0000 37.0000 45.0000 Small 13 Baptist Health South Florida 126.0000 42.0000 80.0000 Large 14 World Wide Technology 129,0000 50.0000 31,0000 Small 15 Methodist Hospital 100.0000 53.0000 83.0000 Large 16 Perkins Coie 189.0000 58.0000 63.0000 Small 17 American Express 114,0000 50.0000 35.0000 Large 18 TDIndustries 93.0000 64.0000 47.0000 Small 19 Quik Trip 69.0000 56.0000 44.0000 Large 20 EOG Resources 189.0000 72.0000 81.0000 Small 21 FootSet Research Systems 103,0000 75.0000 51.0000 Small 22 Stryker 71.0000 80.0000 43,0000 Large 23 SRC 84.0000 81.0000 33,0000 Small 24 Booz Allen Hamilton 105.0000 84.0000 77.0000 Large 25 CarMax 57.0000 91.0000 34.0000 Large 26 GoDaddy.com 105.0000 93.0000 71.0000 Midsize 27 KPMG 79.0000 94.0000 59.0000 Large 28 Navy Federal Credit Union 77.0000 95,0000 39.0000 Midsize 29 Schweitzer Engineering Labs 99.0000 97.0000 28.0000 Small 30 Darden Restaurants 57.0000 99.0000 24.0000 - + Large 31 Intercontinental Hotels Group 63.0000 100,0000 26.0000 LargeQuestion 15 points] J Saved Determine the multiple regression equation. {keep 4 decimals] NOTE: We only have 2 dummy variables, and if you do not know why we only have 2 dummy variables (instead of 3}, please revisit the lecture video 9 and 10 in week 11; Also, if the coefficient is negative, your answer should include negative sign. Video 10: Computer skill 5.3: Hovvr to Use Excel to analyze Categorical independent variables .?In. X1+ X2+ X3+ X4 Question 2 (2 points) Saved Please Interpret the partial coefficient b1: The average annual salary has to remain constant at 26.9659 even when there are no other factors that are affecting the average annual salary. AQuestion 3 (2 points) Saved Please Interpret the partial coefficient b2: The average annual salary would go up by 1.2440 because of every increase of 1 in hourly keeping all of the other factors constant. A/Question 4 (2 points) Saved Please Interpret the partial coefficient b3: This means that the average annual salary would go down by -3.2082 since the company is a midsize company and all of the other factors were held constant. A/Question 5 (4 points) Saved Please Interpret the partial coefficient b4: The average annual salary would go up by 34.4021 if the company is a small size company, keeping all other factors constant. AQuestion 6 [3 points] J Saved Referring to the scenario above what is the goodness of fit? {keep 4 decimal points) NOTE: please do not use adjusted one. r2= 51490? :5; 96 Question 7 [3 points] J Saved Please interpret the goodness of fit you got above. {After your first attempt, you might see "x" next to the question. but it might not mean you are wrong, since all the interpretation questions will be graded by the instructor. Your answer should includes all key word in "Feedback" seen after lst attempt} This means that 514907956 at the variation because of this variation is dependent variable because of the variation in the independent variables. Question 8 [5 points} At the 0.05 level, test the overall regression equation significance [Global F test} Write down 4-step. Based on the test, do you think the regression model work well to predict the average annual salary for salaried employees? I've already set up 4- step for you, and finish the blank. Step 1: Hypothesis {You can use "B" not "b" instead of "beta"} - Ha: Step 2: P-value {4-decimal places} P-va |ue= - Step 3: Rejection Rule (What is the criteria to reject]: - Step 4: Decision and conclusion {reject or not reject; how about the relationship] - Question 9 [5 points} At the 0.05 level, test the significance of partial coefficient for X4. Write down 4- step. Do you think midsize company offering significant different salary compared with the small size company? We already set up 4-step for you, and finish the blank. Step 1: Hypothesis (You can use "B" not "b" instead of "beta", please use - for equal, and \"=,f" for not equal 1 H0: HO: A/ Ha: A Step 2: P-value (4-decimal places) P-value= A/ Step 3: Rejection Rule (What is the criteria to reject): A/ Step 4: Decision and conclusion (reject or not reject; how about the relationship) A/Question 10 [3 points) Determine the 95% confidence interval for the population partial coefficients for Rank [keep 4 decimal places} { lower limit= :] 'v upper limit= \"5/ l Question 11 [3 points) Based on the interval above, do you think the firm's rank will significantly.f affect the annual salary for salaried employees? Why? Question 12 [3 points) What is the mean annual salary for salaried employees of the company that ranks 5 on Fortune 100 list, pays the 100 thousand dollar to hourly employees and is small size? Hint: you should use 100 instead of 100,000 for X3, since X3 is measured in thousand dollar. Your answer is in thousand already so don't convert it to thousand, since Y is measured in thousand as well. (Make sure you are not round any number until your nal answer, and accuracy is important} Question 13 [3 points) Interpret your answer above. (After your first attempt, you might see it next to the question, but it might not mean you are wrong, since all the interpretation questions will be graded by the instructor. Your answer should includes all key word in "Feedback" seen after lst attempt} :ls Question 14 (5 points) What is the 95% confidence interval of the mean annual salary for salaried employees as described in 12)? (Show detailed calculation on Excel file and submit to D2L). (