Q: Using the demographics data posted below this question, 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. The hypothesis test and confidence interval that needs to be interpreted are for the slope. Include the Excel data, output, and your interpretations. Also interpret the standard error of the estimates and the R^2 coefficient. 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% 99.10 915,077 HUNTSVILLE AL 2.70% 95.20 343,418 MOBILE AL 3.60% 91.80 535,472 MONTGOMERY AL 2.90% 92.40 322,441 LITTLE ROCK AR 3.20% 87.20 559,074 PHOENIX AZ 2.60% 99.90 3,013,696 TUCSON AZ 2.30% 99.70 803,618 BAKERSFIELD CA 12.50% 106.10 642,495 FRESNO CA 13.80% 105.80 879,829 LOS ANGELES CA 6.30% 122.00 25,366,576 SACRAMENTO CA 4.10% 114.00 3,328,431 SAN DIEGO CA 3.20% 122.80 2,820,844 SAN FRANCISCO CA 2.50% 144.70 8,559,292 COLORADO SPRINGS CO 3.70% 96.80 499,994 DENVER CO 2.60% 105.30 4,417,908 PUEBLO CO 5.80% 92.50 136,987 HARTFORD CT 3.40% 121.80 1,147,504 WASHINGTON DC 2.90% 132.00 4,739,999 WILMINGTON DE 3.20% 108.10 571,420 FORT MYERS FL 4.40% 97.20 400,542 JACKSONVILLE FL 2.90% 95.40 1,056,332 MIAMI FL 6.70% 104.50 2,175,634 ORLANDO FL 2.90% 97.00 1,535,004 PENSACOLA FL 3.70% 93.60 403,384 TALLAHASSEE FL 3.10% 100.10 200,003 TAMPA FL 2.90% 97.80 2,278,169 WEST PALM BEACH FL 5.60% 104.70 1,049,420 ATLANTA GA 3.00% 97.40 3,857,097 COLUMBUS GA 4.40% 93.90 271,417 MACON GA 4.70% 95.10 321,586 DES MOINES A 1.80% 94.70 443,496 DUBUQUE A 2.60% 107.50 88,112 BOISE ID 3.30% 102.70 407,844 POCATELLO ID 4.50% 99.80 74,881 CHICAGO IL 4.00% 121.60 8,008,507 ROCKFORD IL 4.10% 103.60 358,640 SPRINGFIELD IL 3.60% 95.10 358,640 FORT WAYNE IN 2.40% 93.30 484,320 INDIANAPOLIS IN 2.30% 94.90 2 SOUTH SEND IN 2.40% 90.90 258,537 TOPEKA KS 4.30% 95.10 170,773 WICHITA KS 3.30% 94.80 548,714 LEXINGTON KY 1.90% 95.90 455,617 LOUISVILLE KY 2.70% 92.80 1,005,849 BATON ROUGE LA 3.70% 98.50 578,946 NEW ORLEANS LA 4.00% 94.50 1,305,479 SHREVEPORT LA 4.80% 94.90 377,673 BOSTON MA 2.20% 136.80 3,297,201 BALTIMORE MD 4.70% 102.30 2,491,254 DETROIT MI 2.80% 113.00 4,474,614 GRAND RAPIDS MI 2.50% 102.10 1,052,092 LANSING MI 2.10% 102.90 450,789 MINNEAPOLIS-ST.PAUL MN 1.50% 99.70 2,872,109 ROCHESTER MN 1.20% 97.50 119,077 COLUMBIA MO 1.40% 93.10 130,179 KANSAS CITY MO 3.20% 96.10 1,756,899 SPRINGFIELD MO 2.50% 94.50 308,332 ST. LOUIS MO 3.50% 97.40 2,569,029 JACKSON MS 2.80% 94.30 432,647 BILLINGS MT 3.80% 102.70 127,258 GREAT FALLS MT 5.70% 102.50 782,132 MISSOULA MT 5.20% 101.80 89,344 ASHEVILLE NC 3.30% 100.00 215,180 CHARLOTTE NC 5.00% 96.80 1,417,217 GREENSBORO-WNSTN-S NC 2.80% 97.50 1,179,384 RALEIGH NC 1.90% 97.30 1,106 WILMINGTON NC 4.20% 97.80 222,109 BISMARCK ND 1.80% 99.90 91,939 FARGO ND 1.10% 100.80 170,122 LINCOLN NE 1.40% 88.80 237,057 OMAHA NE 1.90% 92.30 698,875 CONCORD NH 2.30% 108.80 239,069 ATLANTIC CITY NJ 12.70% 132.60 337,635 ALBUQUERQUE NM 4.60% 102.80 678,820 LAS VEGAS NV 3.30% 105.60 1,381,086 RENO NV 2.80% 111.80 319,816 ALBANY NY 3.10% 109.70 869,474 BUFFALO NY 4.50% 97.30 1,142,121 NEW YORK NY 7.60% 226.50 20,196,649 ROCHESTER NY 3.50% 110.40 1,079,073 SYRACUSE NY 3.40% 102.90 732,920 AKRON OH 3.70% 96.30 689,435 CINCINNATI OH 3.20% 101.10 1,960,995 CLEVELAND OH 4.20% 106.00 2,221,181 COLUMBUS OH 2.50% 101.40 1,489,487 TOLEDO OH 4.60% 96.90 608,976 OKLAHOMA CITY OK 3.00% 90.20 1,046,283 TULSA OK 3.00% 89.40 786,117 EUGENE OR 5.10% 108.90 314,901 PORTLAND OR 4.30% 107.30 1,845,840 SALEM OR 5.50% 103.30 335,156 ALLENTOWN PA 4.40% 104.40 618,350 ERIE PA 4.70% 101.60 276,993 HARRISBURG PA 2.70% 104.90 618,375 PHILADELPHIA PA 4.00% 127.40 4,949,567 PITTSBURGH PA 4.31% 113.30 2,331,336 WILLIAMSPORT PA 5.10% 97.50 116,709 CHARLESTON SC 2.50% 95.20 552,803 COLUMBIA SC 1.80% 94.20 516,251 GREENVILLE SC 2.50% 95.20 929,565 RAPID CITY SO 2.40% 100.20 88,117 SIOUXFALLS SO 1.40% 96.60 164,481 KNOXVILLE TN 3.40% 93.80 672,087 MEMPHIS TN 3.20% 95.30 1,105,050 NASHVILLE TN 2.50% 91.70 1,171,755 ABILENE TX 3.40% 91.80 122,478 AMARILLO TX 3.10% 90.00 208,691 AUSTIN TX 2.40% 100.90 1,146,050 CORPUS CHRISTI TX 6.40% 93.60 387,105 DALLAS-FORT WORTH TX 2.90% 101.80 3,280,310 ELPASO TX 9.80% 95.00 701,908 HOUSTON TX 3.80% 96.80 4,010,969 SAN ANTONIO TX 3.20% 99.60 1,564,949 WACO TX 3.30% 92.10 204,244 SALT LAKE CITY UT 2.70% 100.90 1,275,076 NORFOLK VA 3.30% 100.50 1,562,635 RICHMOND VA 2.80% 102.00 961,416 ROANOKE VA 2.10% 93.00 277,741 BURLINGTON VT 1.90% 109.60 165,917 OLYMPIA WA 4.80% 103.40 205,459 SEATTLE WA 3.10% 119.70 2,334,934 SPOKANE WA 5.30% 106.70 409,736 YAKIMA WA 11.70% 102.60 220,785 GREEN BAY Wl 2.40% 97.00 216,522 LA CROSSE WI 2.50% 98.50 121,927 MILWAUKEE Wl 3.30% 107.50 1,462,422 CHARLESTON WV 4.40% 96.60 251,199 HUNTINGTON WV 5.80% 100.80 312,447 CASPER WY 5.00% 101.40 63,157 CHEYENNE WY 3.30% 95.00 78,877
Correlation
Correlation defines a relationship between two independent variables. It tells the degree to which variables move in relation to each other. When two sets of data are related to each other, there is a correlation between them.
Linear Correlation
A correlation is used to determine the relationships between numerical and categorical variables. In other words, it is an indicator of how things are connected to one another. The correlation analysis is the study of how variables are related.
Regression Analysis
Regression analysis is a statistical method in which it estimates the relationship between a dependent variable and one or more independent variable. In simple terms dependent variable is called as outcome variable and independent variable is called as predictors. Regression analysis is one of the methods to find the trends in data. The independent variable used in Regression analysis is named Predictor variable. It offers data of an associated dependent variable regarding a particular outcome.
Q: Using the demographics data posted below this question, 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.
The hypothesis test and confidence interval that needs to be interpreted are for the slope. Include the Excel data, output, and your interpretations. Also interpret the standard error of the estimates and the R^2 coefficient.
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% | 99.10 | 915,077 |
HUNTSVILLE | AL | 2.70% | 95.20 | 343,418 |
MOBILE | AL | 3.60% | 91.80 | 535,472 |
MONTGOMERY | AL | 2.90% | 92.40 | 322,441 |
LITTLE ROCK | AR | 3.20% | 87.20 | 559,074 |
PHOENIX | AZ | 2.60% | 99.90 | 3,013,696 |
TUCSON | AZ | 2.30% | 99.70 | 803,618 |
BAKERSFIELD | CA | 12.50% | 106.10 | 642,495 |
FRESNO | CA | 13.80% | 105.80 | 879,829 |
LOS ANGELES | CA | 6.30% | 122.00 | 25,366,576 |
SACRAMENTO | CA | 4.10% | 114.00 | 3,328,431 |
SAN DIEGO | CA | 3.20% | 122.80 | 2,820,844 |
SAN FRANCISCO | CA | 2.50% | 144.70 | 8,559,292 |
COLORADO SPRINGS | CO | 3.70% | 96.80 | 499,994 |
DENVER | CO | 2.60% | 105.30 | 4,417,908 |
PUEBLO | CO | 5.80% | 92.50 | 136,987 |
HARTFORD | CT | 3.40% | 121.80 | 1,147,504 |
WASHINGTON | DC | 2.90% | 132.00 | 4,739,999 |
WILMINGTON | DE | 3.20% | 108.10 | 571,420 |
FORT MYERS | FL | 4.40% | 97.20 | 400,542 |
JACKSONVILLE | FL | 2.90% | 95.40 | 1,056,332 |
MIAMI | FL | 6.70% | 104.50 | 2,175,634 |
ORLANDO | FL | 2.90% | 97.00 | 1,535,004 |
PENSACOLA | FL | 3.70% | 93.60 | 403,384 |
TALLAHASSEE | FL | 3.10% | 100.10 | 200,003 |
TAMPA | FL | 2.90% | 97.80 | 2,278,169 |
WEST PALM BEACH | FL | 5.60% | 104.70 | 1,049,420 |
ATLANTA | GA | 3.00% | 97.40 | 3,857,097 |
COLUMBUS | GA | 4.40% | 93.90 | 271,417 |
MACON | GA | 4.70% | 95.10 | 321,586 |
DES MOINES | A | 1.80% | 94.70 | 443,496 |
DUBUQUE | A | 2.60% | 107.50 | 88,112 |
BOISE | ID | 3.30% | 102.70 | 407,844 |
POCATELLO | ID | 4.50% | 99.80 | 74,881 |
CHICAGO | IL | 4.00% | 121.60 | 8,008,507 |
ROCKFORD | IL | 4.10% | 103.60 | 358,640 |
SPRINGFIELD | IL | 3.60% | 95.10 | 358,640 |
FORT WAYNE | IN | 2.40% | 93.30 | 484,320 |
INDIANAPOLIS | IN | 2.30% | 94.90 | 2 |
SOUTH SEND | IN | 2.40% | 90.90 | 258,537 |
TOPEKA | KS | 4.30% | 95.10 | 170,773 |
WICHITA | KS | 3.30% | 94.80 | 548,714 |
LEXINGTON | KY | 1.90% | 95.90 | 455,617 |
LOUISVILLE | KY | 2.70% | 92.80 | 1,005,849 |
BATON ROUGE | LA | 3.70% | 98.50 | 578,946 |
NEW ORLEANS | LA | 4.00% | 94.50 | 1,305,479 |
SHREVEPORT | LA | 4.80% | 94.90 | 377,673 |
BOSTON | MA | 2.20% | 136.80 | 3,297,201 |
BALTIMORE | MD | 4.70% | 102.30 | 2,491,254 |
DETROIT | MI | 2.80% | 113.00 | 4,474,614 |
GRAND RAPIDS | MI | 2.50% | 102.10 | 1,052,092 |
LANSING | MI | 2.10% | 102.90 | 450,789 |
MINNEAPOLIS-ST.PAUL | MN | 1.50% | 99.70 | 2,872,109 |
ROCHESTER | MN | 1.20% | 97.50 | 119,077 |
COLUMBIA | MO | 1.40% | 93.10 | 130,179 |
KANSAS CITY | MO | 3.20% | 96.10 | 1,756,899 |
SPRINGFIELD | MO | 2.50% | 94.50 | 308,332 |
ST. LOUIS | MO | 3.50% | 97.40 | 2,569,029 |
JACKSON | MS | 2.80% | 94.30 | 432,647 |
BILLINGS | MT | 3.80% | 102.70 | 127,258 |
GREAT FALLS | MT | 5.70% | 102.50 | 782,132 |
MISSOULA | MT | 5.20% | 101.80 | 89,344 |
ASHEVILLE | NC | 3.30% | 100.00 | 215,180 |
CHARLOTTE | NC | 5.00% | 96.80 | 1,417,217 |
GREENSBORO-WNSTN-S | NC | 2.80% | 97.50 | 1,179,384 |
RALEIGH | NC | 1.90% | 97.30 | 1,106 |
WILMINGTON | NC | 4.20% | 97.80 | 222,109 |
BISMARCK | ND | 1.80% | 99.90 | 91,939 |
FARGO | ND | 1.10% | 100.80 | 170,122 |
LINCOLN | NE | 1.40% | 88.80 | 237,057 |
OMAHA | NE | 1.90% | 92.30 | 698,875 |
CONCORD | NH | 2.30% | 108.80 | 239,069 |
ATLANTIC CITY | NJ | 12.70% | 132.60 | 337,635 |
ALBUQUERQUE | NM | 4.60% | 102.80 | 678,820 |
LAS VEGAS | NV | 3.30% | 105.60 | 1,381,086 |
RENO | NV | 2.80% | 111.80 | 319,816 |
ALBANY | NY | 3.10% | 109.70 | 869,474 |
BUFFALO | NY | 4.50% | 97.30 | 1,142,121 |
NEW YORK | NY | 7.60% | 226.50 | 20,196,649 |
ROCHESTER | NY | 3.50% | 110.40 | 1,079,073 |
SYRACUSE | NY | 3.40% | 102.90 | 732,920 |
AKRON | OH | 3.70% | 96.30 | 689,435 |
CINCINNATI | OH | 3.20% | 101.10 | 1,960,995 |
CLEVELAND | OH | 4.20% | 106.00 | 2,221,181 |
COLUMBUS | OH | 2.50% | 101.40 | 1,489,487 |
TOLEDO | OH | 4.60% | 96.90 | 608,976 |
OKLAHOMA CITY | OK | 3.00% | 90.20 | 1,046,283 |
TULSA | OK | 3.00% | 89.40 | 786,117 |
EUGENE | OR | 5.10% | 108.90 | 314,901 |
PORTLAND | OR | 4.30% | 107.30 | 1,845,840 |
SALEM | OR | 5.50% | 103.30 | 335,156 |
ALLENTOWN | PA | 4.40% | 104.40 | 618,350 |
ERIE | PA | 4.70% | 101.60 | 276,993 |
HARRISBURG | PA | 2.70% | 104.90 | 618,375 |
PHILADELPHIA | PA | 4.00% | 127.40 | 4,949,567 |
PITTSBURGH | PA | 4.31% | 113.30 | 2,331,336 |
WILLIAMSPORT | PA | 5.10% | 97.50 | 116,709 |
CHARLESTON | SC | 2.50% | 95.20 | 552,803 |
COLUMBIA | SC | 1.80% | 94.20 | 516,251 |
GREENVILLE | SC | 2.50% | 95.20 | 929,565 |
RAPID CITY | SO | 2.40% | 100.20 | 88,117 |
SIOUXFALLS | SO | 1.40% | 96.60 | 164,481 |
KNOXVILLE | TN | 3.40% | 93.80 | 672,087 |
MEMPHIS | TN | 3.20% | 95.30 | 1,105,050 |
NASHVILLE | TN | 2.50% | 91.70 | 1,171,755 |
ABILENE | TX | 3.40% | 91.80 | 122,478 |
AMARILLO | TX | 3.10% | 90.00 | 208,691 |
AUSTIN | TX | 2.40% | 100.90 | 1,146,050 |
CORPUS CHRISTI | TX | 6.40% | 93.60 | 387,105 |
DALLAS-FORT WORTH | TX | 2.90% | 101.80 | 3,280,310 |
ELPASO | TX | 9.80% | 95.00 | 701,908 |
HOUSTON | TX | 3.80% | 96.80 | 4,010,969 |
SAN ANTONIO | TX | 3.20% | 99.60 | 1,564,949 |
WACO | TX | 3.30% | 92.10 | 204,244 |
SALT LAKE CITY | UT | 2.70% | 100.90 | 1,275,076 |
NORFOLK | VA | 3.30% | 100.50 | 1,562,635 |
RICHMOND | VA | 2.80% | 102.00 | 961,416 |
ROANOKE | VA | 2.10% | 93.00 | 277,741 |
BURLINGTON | VT | 1.90% | 109.60 | 165,917 |
OLYMPIA | WA | 4.80% | 103.40 | 205,459 |
SEATTLE | WA | 3.10% | 119.70 | 2,334,934 |
SPOKANE | WA | 5.30% | 106.70 | 409,736 |
YAKIMA | WA | 11.70% | 102.60 | 220,785 |
GREEN BAY | Wl | 2.40% | 97.00 | 216,522 |
LA CROSSE | WI | 2.50% | 98.50 | 121,927 |
MILWAUKEE | Wl | 3.30% | 107.50 | 1,462,422 |
CHARLESTON | WV | 4.40% | 96.60 | 251,199 |
HUNTINGTON | WV | 5.80% | 100.80 | 312,447 |
CASPER | WY | 5.00% | 101.40 | 63,157 |
CHEYENNE | WY | 3.30% | 95.00 | 78,877 |
Trending now
This is a popular solution!
Step by step
Solved in 2 steps