10. Using the data in the Excel file Demographics, apply the Excel Regression tool using unemployment rate as the dependent variable and cost of living index as the independent variable. Interpret all key regression results, hypothesis tests, and confidence intervals in the output.File Edit View Tools Window O Q 8 Tue Jun 21 6:46 PM Excel Insert Format Data Help AutoSave OFF A HEY C ... 3 Demographics Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Clear HH L&. Queries & Connections 21 72 Y *Show Detail OOO Properties Reapply Get Data from Refresh Sort Filter Text to Flash Remove Data Consolidate What-If Group Ungroup Subtotal -= Hide Detail Analysis Data Solver Edit Links Advanced Duplicates Validation Analysis Tools Columns Fill Analysis Data Picture All A37 fx BOISE C D E F G H K M N P Q R S T B Demographic Data Unemployment Cost of Living Population Metropolitan Area State (July 1999) 1999 - US Avg = 100 (July 1999) ANCHORAGE AK 3.60% 125.90 257,808 BIRMINGHAM AL 2.70% 39.10 915,077 HUNTSVILLE AL 2.70% 95.20 343,418 MOBILE AL 3.60% 1.80 535,472 MONTGOMERY AL 2.90% 92.40 322,441 LITTLE ROCK AR 3.20% 87.20 559,074 0 PHOENIX AZ 2.60% 99.90 3,013,696 11 TUCSON AZ 2.30% 99.70 803,618 12 BAKERSFIELD CA 12.50% 106.10 642,495 3 FRESNO CA 13.80% 105.80 879,829 14 LOS ANGELES CA 6.30% 122.00 25,366,576 15 SACRAMENTO CA 4.10% 14.00 3,328,431 16 SAN DIEGO CA 3.20% 122.80 2,820,844 17 SAN FRANCISCO CA 2.50% .70 8.5 8 COLORADO SPRINGS co 3.70% 96.80 499,994 19 DENVER co 2.60% 105.30 4,417,908 0 PUEBLO co 5.80% 92.50 136,987 1 HARTFORD CT 3.40% 121.80 1,147,504 22 WASHINGTON DC 2.90% 132. 39,999 23 WILMINGTON DE 3.20% 108.10 571,420 24 FORT MYERS FL 4.40% 7.20 400,542 5 JACKSONVILLE FL 2.90% 95.40 1,056,332 26 MIAMI FL 6.70% 104.50 2,175,634 27 ORLANDO FL 2.90% 97.00 1,535,004 28 PENSACOLA FL 3.70% 93.60 403,384 29 TALLAHASSEE FL 3.10% 100.10 0.003 30 TAMPA FL 2.90% 97.80 2,278,169 31 WEST PALM BEACH 5.60% 104.70 1,049,420 32 ATLANTA GA 3.00% 97.40 3,857,097 33 COLUMBUS GA 4.40% 93.90 271,417 34 MACON GA 4.70% 95.10 321,586 35 DES MOINES A 1.80% 94.70 443,496 6 DUBUQUE 2.60% 107.50 88,112 37 BOISE 3.30% 102.70 407,844 38 POCATELLO 4.50% 29.80 74.881 Sheet1 Data + Sum: 407946.733 - 100% ReadyQ 8 Tue Jun 21 6:46 PM Excel File Edit View Insert Format Tools Data Window Help O AutoSave OFF A HEY G ... 3 Demographics Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments HH &. Queries & Connections 21 72 Y Clear *Show Detail OOO Properties Reapply Get Data from Refresh Sort Filter Text to Flash Remove Data Consolidate What-If Group Ungroup Subtotal - Hide Detail Analysis Data Solver Tools Data Picture Fill Duplicates Validation Analysis All Edit Links Advanced Columns Analysis A74 fx LINCOLN C D E F G H K M N P Q R S T 8 POCATELLO 4.50% 99.80 74,881 39 CHICAGO IL 4.00% 121.60 8,008,507 40 ROCKFORD IL 4.10% 103.60 358,640 41 SPRINGFIELD IL 3.60% 5.10 358,640 2 FORT WAYNE IN 2.40% 93.30 484,320 43 INDIANAPOLIS IN 2.30% 94.90 44 SOUTH SEND IN 2.40% 90.90 258,537 45 TOPEKA KS 4.30% 95.10 170,773 46 WICHITA KS 3.30% 94.80 548,714 47 LEXINGTON KY 1.90% 95.90 455,617 48 LOUISVILLE KY 2.70% 92.80 1,005,849 49 ROUGE LA 3.70% 98.50 578,946 50 NEW ORLEANS LA 4.00% 94.50 1,305,479 51 SHREVEPORT LA 4.80% 4.90 377,673 52 MA 2.20% 136.80 3,297,201 53 BALTIMORE MD 4.70% 102.30 2,491,254 54 DETROIT MI 2.80% 113.00 4,474,614 55 GRAND RAPIDS MI 2.50% 102.10 1,052,092 56 MI 2.10% 102.90 450,789 $7 MINNEAPOLIS-ST.PAUL MN 1.50% 99.70 2,872,109 58 ROCHESTER MN 1.209 97.50 119,077 59 MO 1.40% 93.10 130,179 60 KANSAS CITY MC 3.20% 96.10 1,756,899 1 SPRINGFIELD MO 2.50% 34.50 308,332 62 ST. LOUIS MO 3.50% 97.40 2,569,029 63 JACKSON MS 2.80% 94.30 432.647 64 BILLINGS MT 3.80% 102.70 127,258 65 GREAT FALLS MT 5.70 102.50 782,132 66 MISSOULA MT 5.20% 101.80 89,344 67 ASHEVILLE NO 3.30% 100.00 215,180 8 CHARLOTTE NC 5.00% 96.80 1,417,217 59 GREENSBORO-WNSTN-S NC 2.80% 97.50 1,179,384 70 RALEIGH NC 1.90% 97.30 1,106 71 WILMINGTON NC 1.20% 97.80 222,10 72 BISMARCK ND 1.80% 99.90 91,939 73 FARGO ND 1.10% 100.80 170,122 74 LINCOLN NE 1.40% 88.80 237,057 75 OMAHA NE 1.90% 2.30 698.875 Sheet1 Data Sum: 237145.814 100% ReadyExcel File Edit View Insert Format Tools Data Window Help O Q 8 Tue Jun 21 6:47 PM AutoSave OFF A H E Y C ... 3 Demographics Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments HH &. Queries & Connections 24 72 Y Clear OOO *Show Detail Properties Reapply Get Data from Refresh Sort Filter Text to Flash Remove Data Consolidate What-If Group Ungroup Subtotal - Hide Detail Analysis Data Solver Data Picture All Edit Links Advanced Columns Fill Duplicates Validation Analysis Tools Analysis A111 fx AMARILLO B C D E F G H K M N P Q R S T 75 OMAHA NE 1.90% 92.30 698,875 76 CONCORD NH 2.30% 108.80 239,069 7 ATLANTIC CITY NJ 12.70% 132.60 337,635 78 ALBUQUERQUE NM 4.60% 102.80 678,820 9 LAS VEGAS NV 3.30% 105.60 1,381,086 80 RENO NV 2.80% 111.80 319,816 1 ALBANY NY 3.10% 109.70 869,474 82 NY 4.50% 97.30 1,142,121 83 NEW YORK NY 7.60% 226.50 20,196,649 4 ROCHESTER NY 3.50% 110.40 1,079,073 85 SYRACUSE NY 3.40% 102.90 732,920 86 AKRON OH 3.70% 96.30 689,435 37 CINCINNATI OH 3.20% 101.10 1,960,995 88 CLEVELAND OH 4.20% 106.00 2,221,181 39 COLUMBUS OH 2.50% 101.40 1,489,487 90 TOLEDO OH 4.60% 96.90 608,976 91 OKLAHOMA CITY OK 3.00 90.20 1,046,28 92 TULSA OK 3.00% 89.40 786,117 93 EUGENE OR 5.10% 108.90 314,901 94 PORTLAND OR 4.30% 107.30 1,845,84 95 SALEM OR 5.50% 103.30 335, 156 6 ALLENTOWN PA 4.40% 104.40 618,350 97 PA 4.70% 101.60 276,993 98 HARRISBURG PA 2.70% 104.90 618,375 19 PHILADELPHIA PA 4.00% 127.40 4,949,567 100 PITTSBURGH PA 4.31% 113.30 2,331,336 101 WILLIAMSPORT PA 5.10% 97.50 116,709 02 CHARLESTON SC 2.50% 95.20 552,803 103 COLUMBIA SC 1.80% 4.20 516,251 104 GREENVILLE SC 2.50% 95.20 929,565 05 RAPID CITY SO 2.40% 100.20 88, 117 106 SIOUXFALLS SO 1.40% 96.60 164,481 107 KNOXVILLE TN 3.40% 93.80 672,087 108 MEMPHIS TN 3.20 95.30 1,105,050 109 NASHVILLE TN 2.50% 91.70 1,171,755 110 ABILENE TX 3.40% 91.80 122,478 111 AMARILLO TX 3.10% 90.00 208,691 112 AUSTIN TX 2.40% 100.90 1, 146,050 Sheet1 Data + Ready Sum: 208781.031 - 100%Edit View Help O Q 8 Tue Jun 21 6:47 PM Excel File Insert Format Tools Data Window AutoSave OFF A H E Y C ... 3 Demographics Formulas Data Review View Share Home Insert Draw Tell me Comments Page Layout &. Queries & Connections 21 72 Y Clear *Show Detail HH OOO Properties Reapply Get Data from Refresh Sort Filter Text to Flash Remove Data Consolidate What-If Group Ungroup Subtotal -= Hide Detail Analysis Data Solver Data Picture All Edit Links Advanced Tools Analysis Columns Fill Duplicates Validation Analysis A111 fx AMARILLO P Q R S T B C D E F G H K L M N 112 AUSTIN TX 2.409 00.90 1,146,050 113 CORPUS CHRISTI TX 6.40% 93.60 387,105 114 DALLAS-FORT WORTH TX 2.90% 101.80 3,280,310 115 ELPASO TX 9.80% 95.00 701,908 116 HOUSTON TX 3.80% 96.80 4,010,969 SAN ANT TX 3.20% 99.60 1,564,949 118 WAC TX 3.30% 92.10 204,244 119 SALT LAKE CITY UT 2.70% 100.90 1,275,076 120 NORFOLK VA 3.30% 100.50 1,562,635 121 RICHMOND VA 2.80% 102.00 961,416 22 ROANOKE VA 2.10% 93.00 277,741 123 BURLINGTON VT 1.90% 109.60 165,917 LYMPIA WA 4.80% 103.40 205,459 125 SEATTLE WA 3.10% 119.70 2,334,934 126 SPOKANE WA 5.30% 106.70 409,736 127 YAKIMA WA 11.70% 102.60 220,785 128 GREEN BAY WI 2.40% 97.00 216,522 129 LA CROSSE WI 2.50% 98.50 121,927 130 MILWAUKEE WI 3.30% 107.50 1,462,422 131 CHARLESTON W 4.40% 96.60 251, 199 HUNTINGTON WV 5.80% 100.80 312,447 133 CASPER WY 5.00% 101.40 63 ,157 34 CHEYENNE WY 3.30% 95.00 78,877 135 136 137 138 139 140 Sheet1 Data + Sum: 208781.0310 - Ready16. Use the results for Problem 10 (Demographics) to analyze the residuals to determine if the assumptions underlying the regression analysis are valid. In addi- tion, use the standard residuals to determine if any possible outliers exist. Excel File Edit View Insert Format Tools Data Window Help Q Wed Jun 22 5:37 PM AutoSave OFF A HEY VC ... Demographics Home Insert Draw Page Layout Formulas Data Review View Tell me LE Share Comments X Helv 10 AA ab Wrap Text v General Paste B I UV Merge & Center v $ ~ % " Conditional Format Cell Insert Delete Format Sort & Find & Analyze Formatting as Table Styles Filter Select Data A29 X V fx A95% condience interval for the slop of cost of living index is between 0.001 and 0.006. Which concludes that there is a 95% confient that the slope of cost of living index is between 0.0001 and 0.0006. B C G M O R U SUMMARY OUTPUT Regression Statistics O U A W N Multiple R 0.2412025 R Square 0.05817864 Adjusted R S 0.0508777 Standard Errc 0.02017629 8 Observations 131 10 ANOVA df SS MS F Significance F 12 Regression 0.0032439 0.0032439 7.96865042 0.00551458 13 Residual 129 0.05251365 0.00040708 Total 130 0.05575755 Coefficients Standard Erro t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0% Intercept 0.00269795 0.01243682 0.21693249 0.82860367 -0.0219086 0.02730451 -0.0219086 0.02730451 X Variable 1 0.00033885 0.00012004 2.82287981 0.00551458 0.00010135 0.00057635 0.00010135 0.00057635 From the data given from this summary output, the regression equation of unemployemin and cost of living index is y= 0.0027+0.003x. The y intercept (0.0027) has no meaning but the slope (0.003), each unit increases in the cost of living index, which predicts unemployment to increase by 0.003. The coefficent of determination ( R^2) is 0.0582, 5.82% of sample variation in unemployment rate can be explained by the model contaning cost of living. Overall the regression model is significant and at 5% level of significant assumed. The F test statistics is 7.97 and the p value is 0.0055. Since the p value is less than 0.05 which is 0.0055, rejecting the null hypothesis at 5% level of significance. 27 There is suffiencet evidence that the overall regression model of unemployment on cost of living index is significant. Since the t test is 2.82 and agin the p value is 0.0055 again we see that it is less than 0.05 which shows sufficient evidence that the slop of cost of living index Is different from 0 A 95% condience interval for the slop of cost of living index is between 0.001 and 0.006. Which concludes that there is a 95% confient that the slope of cost of living index is between 0.0001 and 0.0006. Sheet1 Data + Edit 100%