duction This is a piece of Excel project for the MS3043 course. You are expected to use Excel to answer the questions in each worksheet (there are four questions). Your answers/Excel output should be provided in each worksheet with appropriate comments. You can insert text box You may also use appropriate shapes, formatting etc. to highlight your findings and your comments This project can be done individually or as a a group. You must save your file under a suitable name (i.e. your first and Only one submission is allowed per individual or group. Please submit your file to Blackboard Learn server as demons the announced due date (see bleow). Please save your work under a suitable file name, which should include your full name (e.g Fredgren_Michael_ExcelP Due date: No later than 11:59am, Wednesday 22 March 2017. Submit your work via Blackboard before the stated P.S.: No late or email submissions will be accepted! Do not send your files via email to Maho! These will not be grade Thanks! Maho Page 1 Introduction r questions). ments. You can insert text boxes to type in your comments. table name (i.e. your first and last name). oard Learn server as demonstrated in class by e.g Fredgren_Michael_ExcelProject.xlsx) lackboard before the stated due date and time. aho! These will not be graded and given a zero grade! Page 2 Many car magazines regularly conduct comparative road tests on new models of cars. The data below Select a pair of variables (i.e. 2 variables) between which you think there may be a relationship. Provide descriptive statistics for each of the variables you selected Use a scatterplot to examine a possible relationship between the two variables you selected (n.b them). From examining your scatterplot, if a few cars or makes of cars seem to perform differently from management/marketing implications. These data are taken from Car & Driver, July 1998 and appear with McClave, Benson and Sincich (2000) Statistics for Business and Economics (8th Editio Model Acura-Integra-Type-R Acura-NSX-T Acura-3.0CL Acura-3.2TL AM-General-Hummer Aston-Martin-DB7-Volante Audi-A4-Avant-Quattro-wagon Audi-A6-2.8-Quattro Audi-A8-3.7 Audi-A8-4.2-Quattro Bentley-Continental-T BMW-M-roadster BMW-M3-convertible BMW-Z3-2.8 BMW-318ti-Club-Sport BMW-323is BMW-528i-manual BMW-540i-manual BMW-740iL Buick-Regal Cadillac-Catera Cadillac-de-Ville-Concours Cadillac-Eldorado-Touring-Coupe Cadillac-Seville-Touring-Sedan Chevrolet-Blazer-LS-ZR2 Chevrolet-Camaro-Z28 Chevrolet-Corvette Chevrolet-Corvette-automatic Chevrolet-Monte-Carlo-Z34 Chevrolet-Prizm-LSi Chevrolet-Tahoe-Police-Package Chrysler-Sebring-JX Chrysler-300M List Price $ 23,535 92,943 26,880 33,385 84,502 146,530 31,540 35,900 60,136 71,032 347,740 43,245 47,738 37,423 25,850 29,270 45,254 57,458 70,850 23,495 33,635 46,280 45,643 48,476 27,676 24,366 42,959 42,195 22,933 15,034 31,300 22,365 30,800 Time 0 - 60 mph seconds 6.6 4.8 7.8 8.1 15.2 7.3 7.4 9.7 8.1 7.1 6.4 5.4 6.0 6.3 8.5 6.6 7.1 5.4 6.8 6.6 8.2 7.1 6.8 6.7 10.6 5.2 5.1 5.3 7.9 8.4 8.7 11.2 7.8 Dodge-Dakota-5.9-R/T Dodge-Durango-SLT Dodge-Grand-Caravan-ES Dodge-Intrepid-ES Dodge-Neon-R/T Dodge-Ram-1500SS/T Dodge-Stratus-ES Dodge-Viper-RT/10 Eagle-Talon-ESi Ferrari-F50 Ferrari-F355 Ferrari-550-Maranello Ford-Contour-GL Ford-Crown-Vic-Police-Intercepto Ford-Escort-ZX2-Sport Ford-Explorer-XLT Ford-Explorer-Sport Ford-Mustang-Cobra Ford-Mustang-GT Ford-Tauras-SE Ford-Taurus-SHO Ford-Windstar-LX General-Motors-EV1 Honda-Accord-EX Honda-Accord-LX Honda-CR-V-EX Honda-EV-Plus Honda-Prelude-Sequential-SportS Hyundai-Sonata-GL-V-6 Hyundai-Tiburon Infiniti-QX4 Infiniti-O45t Isuzu-Amigo-V-6 Isuzu-Rodeo-LS Isuzu-Trooper Jaguar-XJR Jaguar-XJ8-Vanden-Plus Jeep-Cherokee-SE Jeep-Grand-Cherokee-5.9-Limite Jeep-Wrangler-Sport Kia-Sephia-LS Kia-Sportage Land-Rover-Defender-90 Land-Rover-Discovery-SE7 Lexus-ES300 Lexus-GS400 Lexus-LS400 Lexus-RX300 Lexus-SC300 Lincoln-Mark-VII 23,265 31,935 29,580 25,250 13,795 24,535 20,485 72,396 17,053 560,640 128,800 220,000 18,565 24,240 14,090 36,525 29,665 29,440 18,805 23,040 30,585 28,547 33,995 24,545 20,015 21,013 53,399 24,595 18,129 15,734 37,872 51,871 21,519 31,300 30,975 72,145 67,223 19,725 38,996 21,190 13,844 18,554 37,985 39,125 33,822 45,946 60,869 38,671 48,284 42,918 7.3 9.1 10.1 8.7 7.5 8.4 9.4 4.0 8.3 3.8 4.5 4.3 11.9 8.4 8.0 10.6 7.9 5.9 6.6 8.1 7.7 9.4 8.4 7.6 9.4 10.3 16.9 7.8 9.3 8.6 12.0 8.3 8.0 9.2 9.6 5.6 6.8 12.0 7.0 9.1 9.6 12.8 9.3 11.9 7.8 5.8 6.4 8.2 7.4 7.0 Lincoln-Town-Car-Signature Lingenfelter-Corvette Lotus-Esprit-V-8 Mazda-Millenia-L Mazda-MX-5-Mata Mazda-626ES Mercedes-Benz-CLK320 Mercedes-Benz-E300-diesel Mercedes-Benz-E300-turbo-diese Mercedes-Benz-E320-wagon Mercedes-Benz-ML320 Mercedes-Benz-S320 Mercedes-Benz-SLK230 Mitsubishi-Diamante-LS Mitsubishi-Mirage-LS-coupe Mitsubishi-Montero-Sport-LS Mitsubishi-3000GT-VR4 Nissan-Altima-GXE Nissan-Frontier-SE Nissan-Maxima-SE Oldsmobile-Aiero-GLS Oldsmobile-Cutlass-GLSt-LSS Oldsmobile-Intrigue-GL Oldsmobile-Intrigue-GL Plymouth-Prowler Pontiac-Bonneville-SE Pontiac-Firebird-Trans-Am Pontiac-Trans-Am-Firehawk Pontiac-Trans-Sport-Montana Porsche-Boxster Porsche-911-Carrera Porsche-911-Carrera-4S Porsche-911-Turbo-S Saab-9-5 Saab-900SE-Turbo-convertible Saturn-SC2 Saturn-SL2 Subaru-Forester-L Subaru-Impreza-2.5RS Subaru-Legacy-GT-Limited Subaru-Legacy-SUS-Limited Suzuki-Esteem-GLX-wagon Suzuki-Grand-Vitara-JLX Toyota-Camry-LE Toyota-Paseo-convertible Toyota-RAV4 Toyota-Sienna-XLE Toyota-Supra-Turbo Toyota-4Runner-Limited Volkswagen-Cabrio 41,022 100,052 85,270 34,049 24,195 25,395 44,860 42,782 46,887 49,171 34,545 70,128 42,095 32,355 16,038 29,825 47,462 19,508 21,480 27,657 21,200 19,970 23,995 24,970 39,210 24,595 26,500 33,878 28,070 46,385 73,319 78,820 164,510 33,470 44,214 17,345 15,928 21,075 16,690 25,390 26,164 16,229 18,500 25,617 18,145 22,575 30,090 41,255 38,689 22,870 8.2 3.6 4.1 9.2 7.9 9.3 6.7 11.8 8.8 7.6 9.8 8.7 7.2 8.1 8.7 10.0 5.1 9.8 11.3 8.2 7.7 8.5 8.3 8.1 7.0 8.1 5.3 5.0 10.4 6.1 4.9 4.9 3.7 8.0 7.1 8.2 7.6 9.5 8.3 9.5 9.0 12.3 10.4 7.9 10.1 10.2 9.4 5.0 10.1 12.6 Volkswagen-GTI Volkswagen-Jetta-GLX Volkswagen-New-Beetle Volkswagen-Passat-GLX Volvo-C70 Volvo-V70-AWD-wagon 16,700 22,105 16,580 27,825 43,196 39,958 10.3 6.9 10.1 8.6 6.9 7.8 ative road tests on new models of cars. The data below are from a mid-1998 set of tests. tween which you think there may be a relationship. variables you selected tionship between the two variables you selected (n.b. you don't have to attempt to build a model to describe rs or makes of cars seem to perform differently from the other, highlight them (graphically if possible) and b 998 (2000) Statistics for Business and Economics (8th Edition), Prentice Hall International Time over 0.25 miles at full throttle Maximum Speed seconds 15.2 13.4 16.1 16.3 19.9 15.6 15.9 17.5 16.1 15.4 14.9 14.0 14.5 14.9 16.5 15.3 15.6 14.1 15.2 15.1 16.3 15.5 15.2 15.2 18.0 13.8 13.6 13.8 16.1 16.6 16.6 18.3 16.0 Worth 20points Braking Distance (70 - 0) mph 143 165 136 134 88 149 141 130 133 131 157 137 139 129 116 127 129 156 133 108 128 131 145 130 91 158 171 162 108 111 123 110 143 feet 164 162 184 181 241 183 179 195 180 192 190 172 162 171 166 183 176 168 183 189 177 201 196 188 235 172 173 167 197 212 212 202 181 15.8 17.0 17.6 16.7 15.9 16.6 17.1 12.2 16.4 12.1 13.0 12.7 18.9 16.5 16.3 18.0 16.2 14.6 15.1 16.3 15.9 17.2 16.7 16.0 17.2 17.7 20.5 16.0 17.1 16.7 18.7 16.4 16.2 17.1 17.3 14.2 15.3 18.6 15.5 16.9 17.2 19.1 17.6 18.7 16.1 14.5 15.0 16.4 15.8 15.3 113 114 112 119 121 110 125 177 128 194 179 195 104 130 102 106 106 150 140 112 139 113 80 . 129 119 107 80 134 119 123 100 141 110 115 108 154 150 95 110 106 121 98 92 103 135 150 149 109 137 124 190 223 184 210 190 211 204 157 206 176 165 164 179 185 202 216 231 163 171 214 177 199 187 178 187 196 169 190 200 205 180 191 207 216 193 187 212 195 216 215 202 214 211 179 166 172 186 175 175 16.4 11.5 12.7 17.0 16.1 17.1 15.2 18.7 16.7 15.9 17.6 16.7 15.5 16.3 16.8 17.4 13.8 17.4 18.4 16.5 16.1 16.7 16.4 16.4 15.6 16.2 13.9 13.5 17.6 14.7 13.5 13.5 12.2 16.3 15.6 16.4 15.9 17.0 16.4 17.3 16.9 18.9 17.9 16.2 17.5 17.8 17.2 13.6 17.7 19.2 112 212 173 129 123 123 130 124 130 130 112 131 144 131 117 106 167 112 91 127 105 107 107 117 117 108 159 158 107 146 169 161 188 138 141 123 110 108 124 119 110 101 105 127 109 107 105 160 106 106 194 151 165 195 174 190 169 165 179 188 202 184 170 176 197 190 169 203 211 202 176 189 194 194 168 213 163 160 209 164 170 161 151 194 168 184 193 208 180 177 183 201 204 187 201 193 197 154 197 198 17.5 15.5 17.7 16.7 15.3 16.0 113 127 110 131 147 129 194 185 188 179 169 201 uild a model to describe the precise relationship between hically if possible) and briefly comment on any Worth 20points city fuel economy Road-holding (grip) during cornering mpg gravitational force (g) 25 17 20 19 13 14 17 17 17 17 11 20 20 19 22 20 20 15 17 18 18 17 17 17 17 18 18 17 19 32 14 20 16 0.88 0.90 0.76 0.76 0.63 0.82 0.80 0.75 0.76 0.78 0.76 0.88 0.85 0.87 0.85 0.80 0.78 0.82 0.81 0.77 0.79 0.73 0.79 0.82 0.65 0.84 0.90 0.86 0.81 0.79 0.76 0.76 0.77 13 12 17 19 29 13 20 12 22 8 10 . 0.85 0.69 0.71 0.77 0.82 0.81 0.76 0.97 0.75 0.95 1.02 . 24 16 26 13 15 18 17 19 17 17 . 21 23 22 . 21 18 22 15 18 18 16 15 16 17 18 13 17 24 19 14 14 19 19 19 19 19 18 0.82 0.82 0.74 0.66 0.73 0.86 0.81 0.78 0.80 0.71 0.73 0.80 0.79 0.69 0.76 0.81 0.76 0.80 0.70 0.77 0.74 0.74 0.71 0.85 0.79 0.70 0.74 0.78 0.78 0.63 0.67 0.69 0.76 0.81 0.79 0.72 0.82 0.80 17 . 15 20 23 20 20 28 26 20 17 17 22 18 29 17 . 18 22 18 21 20 20 19 19 . 19 18 17 18 19 19 16 13 21 20 27 25 21 21 21 21 27 19 . 19 30 22 18 17 16 22 0.78 0.95 0.94 0.81 0.89 0.75 0.80 0.81 0.78 0.77 0.70 0.80 0.90 0.78 0.78 0.88 0.75 0.87 0.79 0.83 0.72 0.80 0.77 0.84 0.75 0.87 0.88 0.71 0.86 0.93 0.87 0.94 0.80 0.81 0.80 0.78 0.71 0.79 0.80 0.79 0.77 0.76 0.82 0.72 0.71 0.88 0.70 0.80 23 19 23 18 18 18 0.76 0.78 0.82 0.78 0.84 0.75 How is CEO pay related to company performance and / or sales? 1) Produce scatter plots of the dependent variable and the independent variables. Comment on these sc 2) Perform a multiple regression analysis and comment on the output. 3) Now, re-produce scatter plots of the dependent and independent varaibles WITHOUT DELL data and the scatter plots. 4) Now, perform a multiple regression analysis without Dell data and comment on the output. Compare a the outputs with and without Dell Computer data. These data are taken from Chief Executive, Sept. 1999, pp.45-59 and appear in McClave, Benson and Sincich (2000) Statistics for Business and Economics (8th Edition), International, p.549 Worth 40 points Company CEO Total Pay Company Performance Company Sales ($ thousands) ($ millions) Cummins 4,338 0.8 6266 Bank of New York 7,121 52.5 63579 SunTrust Banks 3,882 33.0 93170 Bear Stearns 25,002 46.1 7980 Charles Schwab 16,506 85.5 3388 Coca-Cola 12,712 22.9 18813 Time Warner 25,136 49.6 14582 Humana 4,516 -13.3 9781 Engelhard 6,189 -1.8 4172 Chubb 4,052 12.3 6337 American Home Products 8,046 35.5 13463 Merck 7,178 33.4 26898 Schering-Plough 6,818 61.8 8077 Home Depot 2,900 58.6 30219 BellSouth 18,134 35.8 23123 Delta Airlines 17,085 20.8 14138 Dell Computer 115,797 222.4 13663 s. Comment on these scatter plots. THOUT DELL data and comment on n the output. Compare and contrast Economics (8th Edition), Prentice Hall Multiple Regression Analysis with curvilinear models Use the following data to develop a curvilinear model to predict y. Include both x 1 and x2 in the model addition to x12 and x22, and the interaction term x1x2. Comment on the overall strength of the model an the significance of each predictor (independent) variable. Develop another regression model with the same variables as the first model above but WITHOUT th interaction variable (i.e. include only variables, x1 and x2 in addition to x12 and x22). Compare this mod to the model with interaction. Comment on the outputs. These data are taken from Black, Ken. Business Statistics for Contemporary Decision Making, (7th Edition), p. 566. Worth 20 points y 34 56 78 90 23 34 45 67 78 65 45 34 23 89 76 56 43 23 45 56 x1 x2 120 105 108 110 78 98 89 92 95 85 96 79 66 88 80 73 69 75 63 74 190 240 238 250 255 230 266 270 272 288 245 288 312 315 320 335 335 250 372 360 x 1 and x2 in the model in ength of the model and bove but WITHOUT the 2 ). Compare this model ecision Making, (7th Stepwise regression & Multicollinearity Given below are data for a dependent variable and four potential predictors. Use these data and a stepwise regression procedure to develop a multiple regression model to predict y. Examine the valu of t and R2 at each step and comment on those values. Construct a correlation matrix for the four independent variables and search for possible multicollinearity. What did you find and why? These data are taken from Black, Ken. Business Statistics for Contemporary Decision Making, (7th Edition), p. 580. Worth 20 points y x1 x2 x3 x4 101 127 98 79 118 114 110 94 96 73 108 124 82 89 76 109 123 125 2 4 9 5 3 1 3 2 8 6 2 5 6 9 1 3 2 6 77 72 69 53 88 53 82 61 60 64 76 74 50 57 72 74 99 81 1.2 1.7 2.4 2.6 2.9 2.7 2.8 2.6 2.4 2.1 1.8 2.2 1.5 1.6 2.0 2.8 2.6 2.5 42 26 47 65 37 28 29 22 48 42 34 11 61 53 72 36 17 48 e these data and a t y. Examine the values matrix for the four d and why? ecision Making, (7th