Quantitative methods
pcwc0506
workm3a2.xlsxQuestion 1
| 1. Using the data in the Excel file Demographics, determine if a linear relationship exist between unemployment rates and cost of living indexes by constructing a scatter chart and adding a trendline. |
| What is the regression model and R2? |
1 - Demographics
| 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 | om | 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 |
Question 3
| 3. The managing director of a consulting group has the following monthly data on total overhead costs and professional labor hours to bill to clients: | ||
| Total | Billable | |
| $340,000 | 3,000 | |
| $400,000 | 4,000 | |
| $435,000 | 5,000 | |
| $477,000 | 6,000 | |
| $529,000 | 7,000 | |
| $587,000 | 8,000 | |
| Develop a regresion model to identify the fixed overhead costs to the consulting group. | ||
| a. What is the constant component of the consultant group's overhead? | ||
| b. If a special job requiring 1,000 billable hours that would contribute a margin of $38,000 before overhead was available, would the job be attractive? |
Question 7
| 7. Using the data in the Excel file Student Grades, run a regression analysis using midterm grade as the independent variable and the final exam grade as the dependent variable. |
| Interpret all key regression results, hypothesis tests, and confidence intervals in the output. |
7 - Student Grades
| Student Grades | ||
| Student | Midterm | Final Exam |
| 1 | 76 | 62 |
| 2 | 84 | 90 |
| 3 | 79 | 68 |
| 4 | 88 | 84 |
| 5 | 76 | 58 |
| 6 | 66 | 79 |
| 7 | 75 | 73 |
| 8 | 94 | 93 |
| 9 | 66 | 65 |
| 10 | 92 | 86 |
| 11 | 80 | 53 |
| 12 | 87 | 83 |
| 13 | 86 | 49 |
| 14 | 63 | 72 |
| 15 | 92 | 87 |
| 16 | 75 | 89 |
| 17 | 69 | 81 |
| 18 | 92 | 94 |
| 19 | 79 | 78 |
| 20 | 60 | 71 |
| 21 | 68 | 84 |
| 22 | 71 | 74 |
| 23 | 61 | 74 |
| 24 | 68 | 54 |
| 25 | 76 | 97 |
| 26 | 72 | 79 |
| 27 | 99 | 89 |
| 28 | 58 | 53 |
| 29 | 82 | 78 |
| 30 | 72 | 82 |
| 31 | 77 | 69 |
| 32 | 95 | 98 |
| 33 | 72 | 93 |
| 34 | 71 | 80 |
| 35 | 72 | 82 |
| 36 | 96 | 96 |
| 37 | 72 | 61 |
| 38 | 89 | 84 |
| 39 | 94 | 97 |
| 40 | 85 | 89 |
| 41 | 60 | 72 |
| 42 | 66 | 93 |
| 43 | 96 | 84 |
| 44 | 83 | 87 |
| 45 | 88 | 99 |
| 46 | 92 | 97 |
| 47 | 80 | 92 |
| 48 | 80 | 93 |
| 49 | 91 | 78 |
| 50 | 74 | 82 |
| 51 | 96 | 99 |
| 52 | 80 | 72 |
| 53 | 62 | 79 |
| 54 | 70 | 75 |
| 55 | 87 | 90 |
| 56 | 90 | 95 |
Question 9
| 9. Data collected in 1960 from the National Cancer Institute provides the per capita numbers of cigarretes sold along with death rates fro various forms of cancer (see the Excel file Smoking and Cancer). |
| Use simple linear regression to determine if a significant relationship exist between the number of cigarrettes sold and each form of cancer. Examine the residuals for assumptions and outliers. |
9 - Smoking and Cancer
| Smoking and Cancer | |||||
| # Cigarettes | Deaths per 100K | Deaths per 100K | Deaths per 100K | Deaths per 100K | |
| State | sold per capita | from bladder cancer | from lung cancer | from kidney cancer | from leukemia |
| AL | 18.20 | 2.90 | 17.05 | 1.59 | 6.15 |
| AZ | 25.82 | 3.52 | 19.80 | 2.75 | 6.61 |
| AR | 18.24 | 2.99 | 15.98 | 2.02 | 6.94 |
| CA | 28.60 | 4.46 | 22.07 | 2.66 | 7.06 |
| CT | 31.10 | 5.11 | 22.83 | 3.35 | 7.20 |
| DE | 33.60 | 4.78 | 24.55 | 3.36 | 6.45 |
| DC | 40.46 | 5.60 | 27.27 | 3.13 | 7.08 |
| FL | 28.27 | 4.46 | 23.57 | 2.41 | 6.07 |
| ID | 20.10 | 3.08 | 13.58 | 2.46 | 6.62 |
| IL | 27.91 | 4.75 | 22.80 | 2.95 | 7.27 |
| IN | 26.18 | 4.09 | 20.30 | 2.81 | 7.00 |
| IA | 22.12 | 4.23 | 16.59 | 2.90 | 7.69 |
| KS | 21.84 | 2.91 | 16.84 | 2.88 | 7.42 |
| KY | 23.44 | 2.86 | 17.71 | 2.13 | 6.41 |
| IA | 21.58 | 4.65 | 25.45 | 2.30 | 6.71 |
| ME | 28.92 | 4.79 | 20.94 | 3.22 | 6.24 |
| MD | 25.91 | 5.21 | 26.48 | 2.85 | 6.81 |
| MA | 26.92 | 4.69 | 22.04 | 3.03 | 6.89 |
| MI | 24.96 | 5.27 | 22.72 | 2.97 | 6.91 |
| MN | 22.06 | 3.72 | 14.20 | 3.54 | 8.28 |
| MS | 16.08 | 3.06 | 15.60 | 1.77 | 6.08 |
| MO | 27.56 | 4.04 | 20.98 | 2.55 | 6.82 |
| MT | 23.75 | 3.95 | 19.50 | 3.43 | 6.90 |
| NB | 23.32 | 3.72 | 16.70 | 2.92 | 7.80 |
| NE | 42.40 | 6.54 | 23.03 | 2.85 | 6.67 |
| NJ | 28.64 | 5.98 | 25.95 | 3.12 | 7.12 |
| NM | 21.16 | 2.90 | 14.59 | 2.52 | 5.95 |
| NY | 29.14 | 5.30 | 25.02 | 3.10 | 7.23 |
| ND | 19.96 | 2.89 | 12.12 | 3.62 | 6.99 |
| OH | 26.38 | 4.47 | 21.89 | 2.95 | 7.38 |
| OK | 23.44 | 2.93 | 19.45 | 2.45 | 7.46 |
| PE | 23.78 | 4.89 | 12.11 | 2.75 | 6.83 |
| RI | 29.18 | 4.99 | 23.68 | 2.84 | 6.35 |
| SC | 18.06 | 3.25 | 17.45 | 2.05 | 5.82 |
| SD | 20.94 | 3.64 | 14.11 | 3.11 | 8.15 |
| TE | 20.08 | 2.94 | 17.60 | 2.18 | 6.59 |
| TX | 22.57 | 3.21 | 20.74 | 2.69 | 7.02 |
| UT | 14.00 | 3.31 | 12.01 | 2.20 | 6.71 |
| VT | 25.89 | 4.63 | 21.22 | 3.17 | 6.56 |
| WA | 21.17 | 4.04 | 20.34 | 2.78 | 7.48 |
| WI | 21.25 | 5.14 | 20.55 | 2.34 | 6.73 |
| WV | 22.86 | 4.78 | 15.53 | 3.28 | 7.38 |
| WY | 28.04 | 3.20 | 15.92 | 2.66 | 578 |
| AK | 30.34 | 3.46 | 25.88 | 4.32 | 4.90 |
