a-d in excel

Problem: The Traveler List for 2012 provided ratings for the top 20 small cruise ships. The data shown on the next sheet are the scores each ship received based upon the results from the readers' survey. Each score represents the percentage of respondents who rated a ship as excellent or very good on several criteria, including Itineraries/Schedule, Shore Excursions, and Food/Dining, An overall score was also reported and used to rank the ships. The highest ranked ship, the Seabourn Odyssey, has an overall score of 94,4, the highest component of which is 97.8 for Food/Dining, Note: To get full credit, you must answer All following questions, AND show your regression analysis results for questions (1) and (4). Questions: (1) Using Excel to conduct regression analysis (refer to instructions in Appendix 13.2 on page 635 in textbook 2) to determine the estimated regression equation that can be used to predict the overall score given the scores for Itineraries/Schedule, Shore Excursions, and Food/Dining Answer: (2) Use the F test to determine the overall significance of the relationship. What is your conclusion at the 0.05 level of significance? Answer: (3) Use the t test to determine the significance of each independent variable. What is your conclusion at the 0.05 level of significance? Answer: (4) Remove any independent variable that is not significant from the estimated regression equation, and redo the regression analysis. What is your new estimated regression equation and what are your observations (open-ended question)? Answer: 100 1 Ship 2 Seabourn Odyssey 3 Seabourn Pride 4 National Geographic Endeavor 5 Seaboum Sojour 6 Paul Gauguin 7 Seabourn Legend 8 Seabourn Spirit 9 Silver Explorer 10 Silver Spirit 11 Seven Seas Navigator 12 Silver Whisperer 13 National Geographic Explorer 14 Silver Cloud 15 Celebrity Xpedition 16 Silver Shadow 17 Silver Wind 18 SeaDream II 19 Wind Star 20 Wind Surf 21 Wind Spirit 22 B D E Itineraries/ Shore Overall Schedule Excursions Food/Dining 94.4 94.6 90.9 97.8 93 96.7 84.2 96.7 92.9 100 88.5 91.3 88.6 94.8 97.1 90.5 95.1 87.9 91.2 90.3 92.5 82.1 98.8 90.2 96 86.3 92 89.9 92.6 92.6 88.9 89.4 94.7 85.9 90.8 89.2 90.6 83.3 90.5 89.2 90.9 82 88.6 89.1 93.1 93.1 89.7 88.7 92.6 78,3 91.3 87.2 93.1 91.7 73.6 87.2 91 75 89.7 86.6 94.4 78.1 91.6 86.2 95.5 77.4 90.9 86.1 94.9 76.5 91.5 86.1 92.1 72.3 89.3 85.2 93.5 77.4 91.9 Problem: The Traveler List for 2012 provided ratings for the top 20 small cruise ships. The data shown on the next sheet are the scores each ship received based upon the results from the readers' survey. Each score represents the percentage of respondents who rated a ship as excellent or very good on several criteria, including Itineraries/Schedule, Shore Excursions, and Food/Dining, An overall score was also reported and used to rank the ships. The highest ranked ship, the Seabourn Odyssey, has an overall score of 94,4, the highest component of which is 97.8 for Food/Dining, Note: To get full credit, you must answer All following questions, AND show your regression analysis results for questions (1) and (4). Questions: (1) Using Excel to conduct regression analysis (refer to instructions in Appendix 13.2 on page 635 in textbook 2) to determine the estimated regression equation that can be used to predict the overall score given the scores for Itineraries/Schedule, Shore Excursions, and Food/Dining Answer: (2) Use the F test to determine the overall significance of the relationship. What is your conclusion at the 0.05 level of significance? Answer: (3) Use the t test to determine the significance of each independent variable. What is your conclusion at the 0.05 level of significance? Answer: (4) Remove any independent variable that is not significant from the estimated regression equation, and redo the regression analysis. What is your new estimated regression equation and what are your observations (open-ended question)? Answer: 100 1 Ship 2 Seabourn Odyssey 3 Seabourn Pride 4 National Geographic Endeavor 5 Seaboum Sojour 6 Paul Gauguin 7 Seabourn Legend 8 Seabourn Spirit 9 Silver Explorer 10 Silver Spirit 11 Seven Seas Navigator 12 Silver Whisperer 13 National Geographic Explorer 14 Silver Cloud 15 Celebrity Xpedition 16 Silver Shadow 17 Silver Wind 18 SeaDream II 19 Wind Star 20 Wind Surf 21 Wind Spirit 22 B D E Itineraries/ Shore Overall Schedule Excursions Food/Dining 94.4 94.6 90.9 97.8 93 96.7 84.2 96.7 92.9 100 88.5 91.3 88.6 94.8 97.1 90.5 95.1 87.9 91.2 90.3 92.5 82.1 98.8 90.2 96 86.3 92 89.9 92.6 92.6 88.9 89.4 94.7 85.9 90.8 89.2 90.6 83.3 90.5 89.2 90.9 82 88.6 89.1 93.1 93.1 89.7 88.7 92.6 78,3 91.3 87.2 93.1 91.7 73.6 87.2 91 75 89.7 86.6 94.4 78.1 91.6 86.2 95.5 77.4 90.9 86.1 94.9 76.5 91.5 86.1 92.1 72.3 89.3 85.2 93.5 77.4 91.9