The second worksheet in the Excel file contains data on 200 couples: number of d
ID: 3055380 • Letter: T
Question
The second worksheet in the Excel file contains data on 200 couples: number of dependent children, combined annual salary of husband and wife, current mortgage on home, average amount of other (non-mortgage) debt and per cent of combined income invested in retirement plans (assumed to be limited to 15%).
You want to find out what variables are related to how much people invest in retirement plans. Using what you have learned about correlations, scatterplots and regression analysis, provide an analysis of the key factors that affect the tendency of people to invest in retirement plans.
Couple Children Salary Mortgage Debt Invested 1 2 95920 87340 6230 6.0% 2 3 99850 105320 15240 9.9% 3 1 81940 35720 2030 9.0% 4 2 70370 78610 13450 8.4% 5 1 77430 55640 5270 15.0% 6 4 55090 130020 11260 0.0% 7 2 89730 99600 11520 4.8% 8 2 58330 85320 10630 2.9% 9 3 50730 102220 11740 0.0% 10 3 65760 114050 19380 9.5% 11 0 100820 76250 9140 9.7% 12 2 48310 39840 3260 3.6% 13 2 44690 47760 9880 9.4% 14 2 76120 35650 9370 8.4% 15 3 32120 65560 10500 7.5% 16 2 110950 120710 16970 15.0% 17 2 61180 68890 6650 8.8% 18 2 100290 104540 19480 3.9% 19 0 43810 39540 7850 15.0% 20 1 73320 75800 5980 6.8% 21 1 103070 103040 3010 15.0% 22 3 71960 67280 12800 4.7% 23 1 87170 83130 14940 10.1% 24 2 71700 59920 6930 13.9% 25 1 110580 94030 8230 11.8% 26 3 86930 103450 13600 14.0% 27 1 57620 50980 5130 5.8% 28 1 54420 54850 10590 0.0% 29 1 100450 75050 15790 5.3% 30 2 39680 72590 15810 4.5% 31 1 97360 76020 13180 7.6% 32 3 53050 76390 16870 0.0% 33 2 82760 47790 16490 10.6% 34 2 53780 100470 18740 0.0% 35 0 52180 75910 5880 8.2% 36 3 58240 96630 14220 11.4% 37 2 68480 83230 1240 5.3% 38 3 98060 124960 11630 7.2% 39 0 89470 77950 4630 13.6% 40 2 128230 143480 20620 5.7% 41 4 77470 128690 23670 0.0% 42 2 85390 94010 4050 10.4% 43 1 60130 70270 4520 7.5% 44 3 90090 114130 8390 7.5% 45 1 74660 56740 16160 7.0% 46 1 90970 101050 3310 8.2% 47 2 62450 44200 11100 11.7% 48 3 89760 137030 17120 0.0% 49 1 68600 113640 7100 1.4% 50 2 75040 92510 13100 4.4% 51 3 38310 23760 12360 5.2% 52 3 72330 69200 10210 12.6% 53 2 97680 94650 9670 10.5% 54 3 49810 71860 8900 5.7% 55 3 73420 149560 20940 0.0% 56 3 63030 85950 3530 12.0% 57 3 46410 73330 13830 1.5% 58 2 51280 51240 9250 4.2% 59 0 78970 51290 620 12.4% 60 2 36270 64380 7460 1.5% 61 0 79910 22310 0 9.0% 62 1 36370 20580 8510 4.4% 63 2 63810 69330 7970 7.3% 64 3 64110 124480 7820 8.7% 65 1 90630 82530 17090 4.3% 66 2 83210 94290 4390 4.7% 67 1 103280 60610 4210 10.4% 68 2 71020 80630 13850 8.4% 69 1 95590 71820 9500 15.0% 70 2 98950 53410 5840 15.0% 71 4 33020 100920 17800 0.0% 72 3 71330 66790 16700 8.5% 73 2 40380 73450 7430 0.9% 74 2 73020 112630 14080 6.3% 75 3 63730 93420 14910 0.0% 76 1 96500 99330 12190 10.6% 77 2 132060 97140 10910 15.0% 78 1 70160 93190 12430 2.9% 79 1 72380 86350 2000 6.0% 80 3 45200 99300 6300 0.0% 81 1 86990 100670 12100 1.0% 82 3 44620 96890 9850 3.6% 83 3 90950 110720 14750 10.5% 84 1 77160 75780 4400 9.4% 85 2 49140 50760 10230 2.2% 86 2 62800 102240 7030 0.0% 87 3 35060 46090 9430 5.0% 88 2 75610 45470 4400 10.6% 89 2 54770 89750 1960 7.2% 90 1 56070 72030 3840 5.2% 91 1 32640 25460 8060 7.1% 92 0 124360 73880 7300 15.0% 93 3 84740 98820 15670 1.9% 94 2 110010 106890 13470 10.3% 95 2 45490 55330 10780 3.6% 96 1 121760 120800 8040 14.1% 97 0 59240 21840 3090 15.0% 98 2 64660 21250 0 14.3% 99 3 90040 120950 9810 6.9% 100 2 47740 64370 8800 8.3% 101 3 83150 132920 15420 2.6% 102 3 78630 107140 9160 5.0% 103 3 58160 104090 7830 4.4% 104 1 82370 82450 12340 15.0% 105 2 76450 86220 13290 10.2% 106 3 43060 53570 11410 0.0% 107 2 83380 83370 3060 15.0% 108 3 105510 99530 17210 4.7% 109 1 59620 18190 9140 8.1% 110 2 58500 47290 7370 10.1% 111 2 44220 84250 13150 1.9% 112 2 76670 97520 11320 0.0% 113 1 69850 22220 6830 9.8% 114 3 89370 133680 21310 3.3% 115 0 78220 60650 4290 15.0% 116 3 58940 126350 10620 7.2% 117 3 90460 101000 11190 8.4% 118 1 66970 49830 4050 11.3% 119 3 73780 36360 16420 7.6% 120 1 88910 52420 5680 7.2% 121 2 73100 90030 7220 9.4% 122 2 35000 13540 9900 10.9% 123 3 46840 49490 16110 0.0% 124 2 102960 98590 9970 4.8% 125 3 38320 109310 3250 7.2% 126 4 25150 55610 11900 6.6% 127 1 52460 37820 2450 7.5% 128 1 65850 76890 13180 7.0% 129 2 33140 97840 11330 0.3% 130 2 114810 106680 11470 10.7% 131 2 48300 56650 8650 5.2% 132 2 46960 15440 5000 3.5% 133 4 60240 59510 10780 12.5% 134 1 87580 95190 1740 12.3% 135 3 77080 89110 13180 1.1% 136 2 69020 98460 9290 5.4% 137 2 103880 86070 3870 15.0% 138 1 53310 51480 7350 8.5% 139 2 91270 118430 3790 10.8% 140 2 36970 38720 4860 3.9% 141 2 25610 36930 7240 6.6% 142 1 70860 96660 12970 2.7% 143 1 73050 89210 2910 9.1% 144 1 82600 125180 9840 2.8% 145 2 45580 63090 7350 7.3% 146 2 31360 39560 1910 3.4% 147 1 111880 97300 15070 14.8% 148 1 77890 93980 5640 7.8% 149 2 50350 53710 10680 2.3% 150 2 55330 78080 200 13.7% 151 4 36410 69470 13140 3.2% 152 2 79870 85440 8030 7.8% 153 0 106560 77630 8480 14.4% 154 2 109180 106570 14830 3.0% 155 3 85930 47860 6520 7.9% 156 3 82060 68330 6150 10.3% 157 3 77040 71310 11480 0.0% 158 1 93640 94130 10230 8.2% 159 1 76040 51790 7510 7.6% 160 2 52530 103960 10500 8.2% 161 2 59950 78980 13670 3.9% 162 3 66380 99630 11060 7.2% 163 3 103620 145260 18860 1.9% 164 3 53110 94680 11730 7.1% 165 4 89610 185460 19870 0.0% 166 2 86120 92170 2060 15.0% 167 2 47740 68390 4980 3.6% 168 3 49240 78220 12680 0.0% 169 3 59270 76940 12140 10.9% 170 1 79050 72910 14400 8.3% 171 0 79740 54710 3090 10.3% 172 3 16650 53530 16210 0.0% 173 1 59250 13590 0 15.0% 174 2 58140 46000 7190 10.1% 175 1 91970 88710 6590 4.9% 176 3 73150 116170 14820 0.0% 177 2 84990 66780 11320 11.0% 178 3 79900 106840 15720 4.3% 179 2 101760 74260 15230 10.2% 180 1 53500 74580 12190 3.0% 181 1 75330 78800 10330 12.0% 182 3 32010 55310 6470 3.5% 183 3 78230 77720 15570 3.4% 184 3 50700 89490 7580 6.3% 185 3 10770 35160 12590 0.0% 186 3 64670 116990 11860 0.8% 187 2 31640 63620 11890 10.5% 188 2 83170 87790 6810 14.8% 189 2 104640 62030 3720 15.0% 190 2 79910 108590 10420 7.7% 191 1 71870 71570 8040 9.0% 192 1 82800 92230 14590 8.1% 193 2 37310 5070 7720 10.3% 194 2 125830 116960 15510 10.4% 195 0 83727 76075 10000 10.1% 196 1 80241 78416 11047 9.3% 197 2 80296 73387 11151 9.5% 198 3 81701 73690 11670 9.6% 199 5 81518 70600 11183 9.9% 200 4 88886 81521 11760 9.8%Explanation / Answer
using ms-excel following correlation matrix
there is positive relationship between invested and salary and invested is negatively related to number of children, mortage and debt.
following regression analysis has been donek using ms-excel
Children Salary Mortgage Debt Invested Children 1 Salary -0.22466 1 Mortgage 0.299072 0.461718 1 Debt 0.42786 0.126821 0.430193 1 Invested -0.37951 0.405616 -0.21685 -0.39934 1