Businessweek magazine periodically publishes a ranking of the Top Companies: Bes

Question

Businessweek magazine periodically publishes a ranking of the Top Companies: Best Place to Work. The ranking gives employers, employees, and potential employees a snapshot of the overall achievements these companies have made in creating a productive and rewarding work environment.   The ranking considers a number of factors (we will call these independent variables) including surveys of career service directors at U.S. colleges, surveys of the organizations themselves (on pay, benefits, and training programs, etc), and surveys of undergraduate students.   The survey data is used to create a ranking for each independent variable of the top companies.   The rankings and survey data from the 2009 ranking are shown below, for the top 25 ranked organizations. Required: Using regression analysis in Excel, build a regression model to predict rank based on the four independent variables – employer survey rank, student survey rank, career service director rank, and average pay.   Determine which, if any of these four independent variables are significantly correlated with the overall rank.   Which is the strongest and which is the weakest independent variable? Organization/Industry Rank Survey Employer Student Career Svs Average Pay Deloitte&Touche/Accounting 1 0 3 0 26 Ernst&Young/Accounting 2 2 1 2 24 U.S. State Dept./Government 3 12 2 0 24 Goldman Sachs/Investment banking 4 7 4 2 23 Siemens 5 6 0 10 26 KPMG/Accounting 6 1 4 8 26 J.P. Morgan/Investment banking 7 10 2 3 13 Disney 8 8 8 1 19 IBM/Technology 9 5 5 7 30 Target/Retail 10 5 8 6 23 General Electric/Manufacturing 11 2 20 6 28 Accenture/Consulting 12 1 14 11 23 Lockheed Martin 13 0 15 13 26 Abbott Laboratories/Health 14 33 0 4 20 Microsoft/Technology 15 14 25 1 16 Intel/Technology 16 9 6 4 28 Phillip Morris/Consumer products 17 3 17 10 22 Verizon Communications/Utilities 18 12 4 15 36 Walt Disney/Hospitality 19 4 26 17 26 Central Intelligence Agency (CIA)/Gov't 20 7 10 28 26 DOD (USA Govt) 21 4 26 31 18 Harris Corp 22 7 13 25 20 Aflac/Insurance 23 19 3 13 21 Raytheon/Manufacturing 24 10 18 9 27 Enterprise Rent-A-Car/Transportation 25 9 13 16 24 Average Pay Career Svs Student Survey Employer LINEST 0.264532945 0.46459333 0.420222747 0.534756458 -6.0091 0.175192892 0.101310252 0.110464757 0.119467296 4.907149 0.779219596 3.788235241 #N/A #N/A #N/A 17.64693748 20 #N/A #N/A #N/A 1012.985475 287.0145248 #N/A #N/A #N/A t value 1.509952497 4.585847149 3.804134084 4.476174458 Rank t value descending order 4 1 3 2 Independent Variable Coefficient used in Regression Strongest t value 1 2 3 Weakest t value 4 Intercept Businessweek magazine periodically publishes a ranking of the Top Companies: Best Place to Work. The ranking gives employers, employees, and potential employees a snapshot of the overall achievements these companies have made in creating a productive and rewarding work environment.   The ranking considers a number of factors (we will call these independent variables) including surveys of career service directors at U.S. colleges, surveys of the organizations themselves (on pay, benefits, and training programs, etc), and surveys of undergraduate students.   The survey data is used to create a ranking for each independent variable of the top companies.   The rankings and survey data from the 2009 ranking are shown below, for the top 25 ranked organizations. Required: Using regression analysis in Excel, build a regression model to predict rank based on the four independent variables – employer survey rank, student survey rank, career service director rank, and average pay.   Determine which, if any of these four independent variables are significantly correlated with the overall rank.   Which is the strongest and which is the weakest independent variable? Organization/Industry Rank Survey Employer Student Career Svs Average Pay Deloitte&Touche/Accounting 1 0 3 0 26 Ernst&Young/Accounting 2 2 1 2 24 U.S. State Dept./Government 3 12 2 0 24 Goldman Sachs/Investment banking 4 7 4 2 23 Siemens 5 6 0 10 26 KPMG/Accounting 6 1 4 8 26 J.P. Morgan/Investment banking 7 10 2 3 13 Disney 8 8 8 1 19 IBM/Technology 9 5 5 7 30 Target/Retail 10 5 8 6 23 General Electric/Manufacturing 11 2 20 6 28 Accenture/Consulting 12 1 14 11 23 Lockheed Martin 13 0 15 13 26 Abbott Laboratories/Health 14 33 0 4 20 Microsoft/Technology 15 14 25 1 16 Intel/Technology 16 9 6 4 28 Phillip Morris/Consumer products 17 3 17 10 22 Verizon Communications/Utilities 18 12 4 15 36 Walt Disney/Hospitality 19 4 26 17 26 Central Intelligence Agency (CIA)/Gov't 20 7 10 28 26 DOD (USA Govt) 21 4 26 31 18 Harris Corp 22 7 13 25 20 Aflac/Insurance 23 19 3 13 21 Raytheon/Manufacturing 24 10 18 9 27 Enterprise Rent-A-Car/Transportation 25 9 13 16 24 Average Pay Career Svs Student Survey Employer LINEST 0.264532945 0.46459333 0.420222747 0.534756458 -6.0091 0.175192892 0.101310252 0.110464757 0.119467296 4.907149 0.779219596 3.788235241 #N/A #N/A #N/A 17.64693748 20 #N/A #N/A #N/A 1012.985475 287.0145248 #N/A #N/A #N/A t value 1.509952497 4.585847149 3.804134084 4.476174458 Rank t value descending order 4 1 3 2 Independent Variable Coefficient used in Regression Strongest t value 1 2 3 Weakest t value 4 Intercept

Explanation / Answer

We have given that the regression output from that we have to give answer.

And Intercept = -6.0091

Independent variable coefficient used in regression strongest t value 1 4.5858 0.46459333 2 4.4762 0.534756458 3 3.8041 0.420222747 weakest t value 4 1.5099 0.264532945

Related Questions

Navigate

• Browse (All)
• Browse B
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.