Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Please help with how to answer these by using Excel. I am stuck on some of it. Thanks Exercise 1: Correlation and Linear Regression For
Please help with how to answer these by using Excel. I am stuck on some of it. Thanks
Exercise 1: Correlation and Linear Regression
For the following exercise, characterize the relationship of the variables and interpret the F-test for the regression model. Using the p-value approach, determine whether the null hypothesis for the F-test is rejected or not. Discuss why or why not. Interpret the implication of these findings for the model.
- (a) Can you determine any correlation between the independent variablesDays (number of days the property is on the market)and thePrice (market price in dollars)? Similarly, are theSize (livable square feet of the property)and thePrice(market price in dollars)correlated? Use the .05 significance level. State the p-value of the test.
- (b) With thePrice (market price in dollars)as the dependent variable and theSize (livable square feet of the property)of the home as the independent variable, determine the regression equation and interpret the confidence level, the precision, and reliability of the sample. Using the regression model, forecast the future marketPricefor a home with a livable area of 2,200 square feet. Determine the 95% prediction interval for the market price of a home with a livable area of 2,200 square feet.
Exercise 2: Multiple Regression Analysis
Conduct the following analyses using thePrice (market price in dollars)as the dependent variable, determine the regression equation with theBedrooms (number of bedrooms),Size (livable square feet of the property),Township (area the property is located), and theBaths (number of bathrooms)as independent variables.
- (a) Develop a correlation matrix and discuss which independent variables have strong or weak correlations with the dependent variable. Utilize these results to discuss any issues with problems with the multicollinearity.
- (b) Use Excel to determine the multiple regression equation. Discuss how you selected the variables to include in the equation. Your regression equation should demonstrate a significant relationship. Report and interpret the R-square.
- (c) Using your results fromQuestion (b) evaluate the addition of the variables;Pool (1 = yes, 0 = no), and attachedGarage (1 = yes, 0 = no).Report your results and conclusions.
- (d) Develop a histogram of the residuals from thefinal regressionequation developed inQuestion (c). Is it reasonable to conclude that the normality assumption has been met?
- (e) Plot the residuals against the fitted values from thefinal regressionequation developed inQuestion (c). Plot the residuals on the vertical axis and the fitted values on the horizontal axis.
| record | Agent | Price | Size | Bedrooms | Baths | Pool (yes is 1) | Garage (Yes is 1) | Days | Township | Mortgage type | Years | FICO | Default (Yes is 1) |
| 1 | Marty | 206424 | 1820 | 2 | 1.5 | 1 | 1 | 33 | 2 | Fixed | 2 | 824 | 0 |
| 2 | Rose | 346150 | 3010 | 3 | 2 | 0 | 0 | 36 | 4 | Fixed | 9 | 820 | 0 |
| 3 | Carter | 372360 | 3210 | 4 | 3 | 0 | 1 | 21 | 2 | Fixed | 18 | 819 | 0 |
| 4 | Peterson | 310622 | 3330 | 3 | 2.5 | 1 | 0 | 26 | 3 | Fixed | 17 | 817 | 0 |
| 5 | Carter | 496100 | 4510 | 6 | 4.5 | 0 | 1 | 13 | 4 | Fixed | 17 | 816 | 0 |
| 6 | Peterson | 294086 | 3440 | 4 | 3 | 1 | 1 | 31 | 4 | Fixed | 19 | 813 | 0 |
| 7 | Carter | 228810 | 2630 | 4 | 2.5 | 0 | 1 | 39 | 4 | Adjustable | 10 | 813 | 0 |
| 8 | Isaacs | 384420 | 4470 | 5 | 3.5 | 0 | 1 | 26 | 2 | Fixed | 6 | 812 | 0 |
| 9 | Peterson | 416120 | 4040 | 5 | 3.5 | 0 | 1 | 26 | 4 | Fixed | 3 | 810 | 0 |
| 10 | Isaacs | 487494 | 4380 | 6 | 4 | 1 | 1 | 32 | 3 | Fixed | 6 | 808 | 0 |
| 11 | Rose | 448800 | 5280 | 6 | 4 | 0 | 1 | 35 | 4 | Fixed | 8 | 806 | 1 |
| 12 | Peterson | 388960 | 4420 | 4 | 3 | 0 | 1 | 50 | 2 | Adjustable | 9 | 805 | 1 |
| 13 | Marty | 335610 | 2970 | 3 | 2.5 | 0 | 1 | 25 | 3 | Adjustable | 9 | 801 | 1 |
| 14 | Rose | 276000 | 2300 | 2 | 1.5 | 0 | 0 | 34 | 1 | Fixed | 20 | 798 | 0 |
| 15 | Rose | 346421 | 2970 | 4 | 3 | 1 | 1 | 17 | 3 | Adjustable | 10 | 795 | 0 |
| 16 | Isaacs | 453913 | 3660 | 6 | 4 | 1 | 1 | 12 | 3 | Fixed | 18 | 792 | 0 |
| 17 | Carter | 376146 | 3290 | 5 | 3.5 | 1 | 1 | 28 | 2 | Adjustable | 9 | 792 | 1 |
| 18 | Peterson | 694430 | 5900 | 5 | 3.5 | 1 | 1 | 36 | 3 | Adjustable | 10 | 788 | 0 |
| 19 | Rose | 251269 | 2050 | 3 | 2 | 1 | 1 | 38 | 3 | Fixed | 16 | 786 | 0 |
| 20 | Rose | 547596 | 4920 | 6 | 4.5 | 1 | 1 | 37 | 5 | Fixed | 2 | 785 | 0 |
| 21 | Marty | 214910 | 1950 | 2 | 1.5 | 1 | 0 | 20 | 4 | Fixed | 6 | 784 | 0 |
| 22 | Rose | 188799 | 1950 | 2 | 1.5 | 1 | 0 | 52 | 1 | Fixed | 10 | 782 | 0 |
| 23 | Carter | 459950 | 4680 | 4 | 3 | 1 | 1 | 31 | 4 | Fixed | 8 | 781 | 0 |
| 24 | Isaacs | 264160 | 2540 | 3 | 2.5 | 0 | 1 | 40 | 1 | Fixed | 18 | 780 | 0 |
| 25 | Carter | 393557 | 3180 | 4 | 3 | 1 | 1 | 54 | 1 | Fixed | 20 | 776 | 0 |
| 26 | Isaacs | 478675 | 4660 | 5 | 3.5 | 1 | 1 | 26 | 5 | Adjustable | 9 | 773 | 0 |
| 27 | Carter | 384020 | 4220 | 5 | 3.5 | 0 | 1 | 23 | 4 | Adjustable | 9 | 772 | 1 |
| 28 | Marty | 313200 | 3600 | 4 | 3 | 0 | 1 | 31 | 3 | Fixed | 19 | 772 | 0 |
| 29 | Isaacs | 274482 | 2990 | 3 | 2 | 1 | 0 | 37 | 3 | Fixed | 5 | 769 | 0 |
| 30 | Marty | 167962 | 1920 | 2 | 1.5 | 1 | 1 | 31 | 5 | Fixed | 6 | 769 | 0 |
| 31 | Isaacs | 175823 | 1970 | 2 | 1.5 | 1 | 0 | 28 | 5 | Adjustable | 9 | 766 | 1 |
| 32 | Isaacs | 226498 | 2520 | 4 | 3 | 1 | 1 | 28 | 3 | Fixed | 8 | 763 | 1 |
| 33 | Carter | 316827 | 3150 | 4 | 3 | 1 | 1 | 22 | 4 | Fixed | 2 | 759 | 1 |
| 34 | Carter | 189984 | 1550 | 2 | 1.5 | 1 | 0 | 22 | 2 | Fixed | 17 | 758 | 0 |
| 35 | Marty | 366350 | 3090 | 3 | 2 | 1 | 1 | 23 | 3 | Fixed | 5 | 754 | 1 |
| 36 | Isaacs | 416160 | 4080 | 4 | 3 | 0 | 1 | 25 | 4 | Fixed | 12 | 753 | 0 |
| 37 | Isaacs | 308000 | 3500 | 4 | 3 | 0 | 1 | 37 | 2 | Fixed | 18 | 752 | 0 |
| 38 | Rose | 294357 | 2620 | 4 | 3 | 1 | 1 | 15 | 4 | Fixed | 10 | 751 | 0 |
| 39 | Carter | 337144 | 2790 | 4 | 3 | 1 | 1 | 19 | 3 | Fixed | 15 | 749 | 0 |
| 40 | Peterson | 299730 | 2910 | 3 | 2 | 0 | 0 | 31 | 2 | Fixed | 13 | 748 | 0 |
| 41 | Rose | 445740 | 4370 | 4 | 3 | 0 | 1 | 19 | 3 | Fixed | 5 | 746 | 0 |
| 42 | Rose | 410592 | 4200 | 4 | 3 | 1 | 1 | 27 | 1 | Adjustable | 9 | 741 | 1 |
| 43 | Peterson | 667732 | 5570 | 5 | 3.5 | 1 | 1 | 29 | 5 | Fixed | 4 | 740 | 0 |
| 44 | Rose | 523584 | 5050 | 6 | 4 | 1 | 1 | 19 | 5 | Adjustable | 10 | 739 | 0 |
| 45 | Marty | 336000 | 3360 | 3 | 2 | 0 | 0 | 32 | 3 | Fixed | 6 | 737 | 0 |
| 46 | Marty | 202598 | 2270 | 3 | 2 | 1 | 0 | 28 | 1 | Fixed | 10 | 737 | 0 |
| 47 | Marty | 326695 | 2830 | 3 | 2.5 | 1 | 0 | 30 | 4 | Fixed | 8 | 736 | 0 |
| 48 | Rose | 321320 | 2770 | 3 | 2 | 0 | 1 | 23 | 4 | Fixed | 6 | 736 | 0 |
| 49 | Isaacs | 246820 | 2870 | 4 | 3 | 0 | 1 | 27 | 5 | Fixed | 13 | 735 | 0 |
| 50 | Isaacs | 546084 | 5910 | 6 | 4 | 1 | 1 | 35 | 5 | Adjustable | 10 | 731 | 0 |
| 51 | Isaacs | 793084 | 6800 | 8 | 5.5 | 1 | 1 | 27 | 4 | Fixed | 6 | 729 | 0 |
| 52 | Isaacs | 174528 | 1600 | 2 | 1.5 | 1 | 0 | 39 | 2 | Fixed | 15 | 728 | 0 |
| 53 | Peterson | 392554 | 3970 | 4 | 3 | 1 | 1 | 30 | 4 | Fixed | 17 | 726 | 0 |
| 54 | Peterson | 263160 | 3060 | 3 | 2 | 0 | 1 | 26 | 3 | Fixed | 10 | 726 | 0 |
| 55 | Rose | 237120 | 1900 | 2 | 1.5 | 1 | 0 | 14 | 3 | Fixed | 18 | 723 | 0 |
| 56 | Carter | 225750 | 2150 | 2 | 1.5 | 1 | 1 | 27 | 2 | Fixed | 15 | 715 | 0 |
| 57 | Isaacs | 848420 | 7190 | 6 | 4 | 0 | 1 | 49 | 1 | Fixed | 5 | 710 | 0 |
| 58 | Carter | 371956 | 3110 | 5 | 3.5 | 1 | 1 | 29 | 5 | Fixed | 8 | 710 | 0 |
| 59 | Carter | 404538 | 3290 | 5 | 3.5 | 1 | 1 | 24 | 2 | Fixed | 14 | 707 | 0 |
| 60 | Rose | 250090 | 2810 | 4 | 3 | 0 | 1 | 18 | 5 | Fixed | 11 | 704 | 0 |
| 61 | Peterson | 369978 | 3830 | 4 | 2.5 | 1 | 1 | 27 | 4 | Fixed | 10 | 703 | 0 |
| 62 | Peterson | 209292 | 1630 | 2 | 1.5 | 1 | 0 | 18 | 3 | Fixed | 10 | 701 | 0 |
| 63 | Isaacs | 190032 | 1850 | 2 | 1.5 | 1 | 1 | 30 | 4 | Adjustable | 2 | 675 | 0 |
| 64 | Isaacs | 216720 | 2520 | 3 | 2.5 | 0 | 0 | 2 | 4 | Adjustable | 5 | 674 | 1 |
| 65 | Marty | 323417 | 3220 | 4 | 3 | 1 | 1 | 22 | 4 | Adjustable | 2 | 673 | 0 |
| 66 | Isaacs | 316210 | 3070 | 3 | 2 | 0 | 0 | 30 | 1 | Adjustable | 1 | 673 | 0 |
| 67 | Peterson | 226054 | 2090 | 2 | 1.5 | 1 | 1 | 28 | 1 | Adjustable | 6 | 670 | 0 |
| 68 | Marty | 183920 | 2090 | 3 | 2 | 0 | 0 | 30 | 2 | Adjustable | 8 | 669 | 1 |
| 69 | Rose | 248400 | 2300 | 3 | 2.5 | 1 | 1 | 50 | 2 | Adjustable | 4 | 667 | 0 |
| 70 | Isaacs | 466560 | 5760 | 5 | 3.5 | 0 | 1 | 42 | 4 | Adjustable | 3 | 665 | 0 |
| 71 | Rose | 667212 | 6110 | 6 | 4 | 1 | 1 | 21 | 3 | Adjustable | 8 | 662 | 1 |
| 72 | Peterson | 362710 | 4370 | 4 | 2.5 | 0 | 1 | 24 | 1 | Adjustable | 2 | 656 | 0 |
| 73 | Rose | 265440 | 3160 | 5 | 3.5 | 1 | 1 | 22 | 5 | Adjustable | 3 | 653 | 0 |
| 74 | Rose | 706596 | 6600 | 7 | 5 | 1 | 1 | 40 | 3 | Adjustable | 7 | 652 | 1 |
| 75 | Marty | 293700 | 3300 | 3 | 2 | 0 | 0 | 14 | 4 | Adjustable | 7 | 647 | 1 |
| 76 | Marty | 199448 | 2330 | 2 | 1.5 | 1 | 1 | 25 | 3 | Adjustable | 5 | 644 | 1 |
| 77 | Carter | 369533 | 4230 | 4 | 3 | 1 | 1 | 32 | 2 | Adjustable | 2 | 642 | 0 |
| 78 | Marty | 230121 | 2030 | 2 | 1.5 | 1 | 0 | 21 | 2 | Adjustable | 3 | 639 | 0 |
| 79 | Marty | 169000 | 1690 | 2 | 1.5 | 0 | 0 | 20 | 1 | Adjustable | 7 | 639 | 1 |
| 80 | Peterson | 190291 | 2040 | 2 | 1.5 | 1 | 1 | 31 | 4 | Adjustable | 6 | 631 | 1 |
| 81 | Rose | 393584 | 4660 | 4 | 3 | 1 | 1 | 34 | 3 | Adjustable | 7 | 630 | 1 |
| 82 | Marty | 363792 | 2860 | 3 | 2.5 | 1 | 1 | 48 | 5 | Adjustable | 3 | 626 | 0 |
| 83 | Carter | 360960 | 3840 | 6 | 4.5 | 0 | 1 | 32 | 2 | Adjustable | 5 | 626 | 1 |
| 84 | Carter | 310877 | 3180 | 3 | 2 | 1 | 1 | 40 | 1 | Adjustable | 6 | 624 | 1 |
| 85 | Peterson | 919480 | 7670 | 8 | 5.5 | 1 | 1 | 30 | 4 | Adjustable | 1 | 623 | 0 |
| 86 | Carter | 392904 | 3400 | 3 | 2 | 1 | 0 | 40 | 2 | Adjustable | 8 | 618 | 1 |
| 87 | Carter | 200928 | 1840 | 2 | 1.5 | 1 | 1 | 36 | 4 | Adjustable | 3 | 618 | 1 |
| 88 | Carter | 537900 | 4890 | 6 | 4 | 0 | 1 | 23 | 1 | Adjustable | 7 | 614 | 0 |
| 89 | Rose | 258120 | 2390 | 3 | 2.5 | 0 | 1 | 23 | 1 | Adjustable | 6 | 614 | 1 |
| 90 | Carter | 558342 | 6160 | 6 | 4 | 1 | 1 | 24 | 3 | Adjustable | 7 | 613 | 0 |
| 91 | Marty | 302720 | 3440 | 4 | 2.5 | 0 | 1 | 38 | 3 | Adjustable | 3 | 609 | 1 |
| 92 | Isaacs | 240115 | 2220 | 2 | 1.5 | 1 | 0 | 39 | 5 | Adjustable | 1 | 609 | 0 |
| 93 | Carter | 793656 | 6530 | 7 | 5 | 1 | 1 | 53 | 4 | Adjustable | 3 | 605 | 1 |
| 94 | Peterson | 218862 | 1930 | 2 | 1.5 | 1 | 0 | 58 | 4 | Adjustable | 1 | 604 | 0 |
| 95 | Peterson | 383081 | 3510 | 3 | 2 | 1 | 1 | 27 | 2 | Adjustable | 6 | 601 | 1 |
| 96 | Marty | 351520 | 3380 | 3 | 2 | 0 | 1 | 35 | 2 | Adjustable | 8 | 599 | 1 |
| 97 | Peterson | 841491 | 7030 | 6 | 4 | 1 | 1 | 50 | 4 | Adjustable | 8 | 596 | 1 |
| 98 | Marty | 336300 | 2850 | 3 | 2.5 | 0 | 0 | 28 | 1 | Adjustable | 6 | 595 | 1 |
| 99 | Isaacs | 312863 | 3750 | 6 | 4 | 1 | 1 | 12 | 4 | Adjustable | 2 | 595 | 0 |
| 100 | Carter | 275033 | 3060 | 3 | 2 | 1 | 1 | 27 | 3 | Adjustable | 3 | 593 | 0 |
| 101 | Peterson | 229990 | 2110 | 2 | 1.5 | 0 | 0 | 37 | 3 | Adjustable | 6 | 591 | 1 |
| 102 | Isaacs | 195257 | 2130 | 2 | 1.5 | 1 | 0 | 11 | 5 | Adjustable | 8 | 591 | 1 |
| 103 | Marty | 194238 | 1650 | 2 | 1.5 | 1 | 1 | 30 | 2 | Adjustable | 7 | 590 | 1 |
| 104 | Peterson | 348528 | 2740 | 4 | 3 | 1 | 1 | 27 | 5 | Adjustable | 3 | 584 | 1 |
| 105 | Peterson | 241920 | 2240 | 2 | 1.5 | 0 | 1 | 34 | 5 | Adjustable | 8 | 583 | 1 |
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started
Linear Algebra and Its Applications
Authors: David C. Lay
4th edition
321791541, 978-0321388834, 978-0321791542
