Case: You Can't Take It with You Individual Case Assignment #3 The goal in this individual assignment is to use Excel's advanced what-if functions (goal seek and data table) to answer the case questions. Use your previously constructed spreadsheet for Marsha (completed in individual case assignment=2), and a spreadsheet for Matt that will be provided as a downloadable file. Case questions to answer: 1) Using Marsha's spreadsheet from case assignment =2. how much money can she withdraw on an annual basis to exhaust her savings (balance equals $0) by the age of 90? By the age of 100? 2) Using Marsha's spreadsheet from case assignment=2. how much money does she need in her portfolio to live her current lifestyle to the age of 90? To the age of 100? 3) Make a table showing Marsha's ending balance at age 90 by varying her withdrawals. Start with a $40,000 withdrawal that ends at $100.000 using $5,000 increments. 4) Make a table showing Matt's final accumulated savings at age 60 using various stock returns. Begin with 5% and end at 20% using increments of 1% Please review your Excel tutorials on what-if functions (goal seek and data table), and feel free to seek clarifications on any aspect of the assignment. se Beg Bal 5. Loen Philan 9.000 2,099 670 9,78: 9,78: 9.391 9,781 9,78: $0 $57,000 $22.93 $89,869 $10.000 $9,781 $9,000 $9,000 0.050 0.019 0.010 0.000 0.130 9,78: 9,781 9,78: 9,704 3,813 3.923 6,025 6,150 9,75 Case Parameters Savings initial Balance 670 Initial Income 797 Initial Housing Rent) 943 Home 1.112 Downpayment-home 1,306 Student Loon Payment 1,329 Initial Philanthropy 1,789 Initial Vacation 2,080 Salary increase 2,417 Inflation 2.804 Savings Pector (1) 3,247 Savings Pector (2) 3,795 Avg Stock Returns 4,327 SD Stock Returns 2.210 Stock Allocation 2,982 Avg Stable Returns 12,799 SD Stable Returns 15,097 Stable Allocation 17,790 20.998 24.449 28.329 9.78: 6,257 0.900 9,78: 6.307 0.046 6,737 0.100 Investments Cashin APR: APR2 Income Housing 0.130 0.046 57.000 22.586 0.15 94 70,390 22.954 0.15 0.046 73.868 0.15 0.046 77.56 0.15 0.046 492 81.489 24.298 0.15 0.046 674 89 911 24,750 0.15 0.046 888 89.785 29.220 0.15 0.046 94 275 29,710 0.15 0.046 98.990 26.198 0.13 0.046 1,789 103,999 0.15 109,436 27.203 0.15 0.046 2.609 114,993 27,720 0.15 0.046 120.222 28.247 0.15 0.046 947 126 333 39.869 0.15 0.046 182.699 29.859 0.15 0.046 1,616 189 209 39.869 0.15 3.397 145 293 0.15 0.046 9.500 193.969 39.859 0.13 0.046 7,989 161 243 39.849 0.15 0.046 10,904 169 300 0.13 0.046 14,217 177,77: 39,869 0.13 0.046 18.301 185.699 39.249 0.13 0.046 22.941 195.992 39.859 0.13 0.046 28 332 205,792 29.869 0.19 0.046 34,983 215.082 39.869 0.15 0.046 41.827 225.888 29.069 0.13 0.046 50,200 288 280 39.869 0.15 99,888 0.19 0.046 74,019 29249 39.859 0.15 0.046 83 867 279,781 39.859 0.15 0.046 289 370 0.13 0.046 119.640 304,049 39,869 0.15 0.046 139,202 319.294 39.849 0.15 0.046 197,642 339 214 39.859 0.13 0.046 183,292 391,974 29.869 0.13 0.046 212.923 959 573 0.13 0.046 245,776 383.092 0.13 0.046 285.900 407,494 29.859 2,411 3,923 28 6,233 30 10,223 32 12,640 33 19,444 18,691 22,447 38 6,783 8,994 28 11,375 24,239 29,432 37,222 78,110 102,333 131,097 154,331 202,931 247,746 299,613 299,395 429,893 908,733 500,754 706,693 828,313 968,497 55 1,129,239 37 1,213,693 38 1,923,248 39 1,767,739 60 2.049,999 7.016 7,190 VECE Liv. Exp. 9,000 29,013 3,093 30,711 9,192 33,364 3,290 36,378 9,891 29,763 42,129 46,682 3,704 30,434 3,813 9,923 38,377 6,039 62.990 6,130 67,645 6,257 72,337 70.698 6,507 76,664 89,764 6,797 99,407 6,889 102.399 7,016 109,729 7,130 7,289 129,333 134,076 7,369 143,083 7,709 132,443 162.341 8,004 172,789 3,135 183,665 2.311 193,148 207,114 219,894 2,794 223,217 8,951 247,216 9,132 261,925 9,309 277,392 293,622 9,552 310,684 328,611 10,033 347,449 7,709 2.004 2,136 2.311 38,620 44 793 91,872 99,978 69,298 79,879 92.031 105,929 124,820 129,934 160,742 2.630 8.794 9,132 9.305 9,562 244.990 242.491 277,820 318,136 10,033 Case: You Can't Take It with You Individual Case Assignment #3 The goal in this individual assignment is to use Excel's advanced what-if functions (goal seek and data table) to answer the case questions. Use your previously constructed spreadsheet for Marsha (completed in individual case assignment=2), and a spreadsheet for Matt that will be provided as a downloadable file. Case questions to answer: 1) Using Marsha's spreadsheet from case assignment =2. how much money can she withdraw on an annual basis to exhaust her savings (balance equals $0) by the age of 90? By the age of 100? 2) Using Marsha's spreadsheet from case assignment=2. how much money does she need in her portfolio to live her current lifestyle to the age of 90? To the age of 100? 3) Make a table showing Marsha's ending balance at age 90 by varying her withdrawals. Start with a $40,000 withdrawal that ends at $100.000 using $5,000 increments. 4) Make a table showing Matt's final accumulated savings at age 60 using various stock returns. Begin with 5% and end at 20% using increments of 1% Please review your Excel tutorials on what-if functions (goal seek and data table), and feel free to seek clarifications on any aspect of the assignment. se Beg Bal 5. Loen Philan 9.000 2,099 670 9,78: 9,78: 9.391 9,781 9,78: $0 $57,000 $22.93 $89,869 $10.000 $9,781 $9,000 $9,000 0.050 0.019 0.010 0.000 0.130 9,78: 9,781 9,78: 9,704 3,813 3.923 6,025 6,150 9,75 Case Parameters Savings initial Balance 670 Initial Income 797 Initial Housing Rent) 943 Home 1.112 Downpayment-home 1,306 Student Loon Payment 1,329 Initial Philanthropy 1,789 Initial Vacation 2,080 Salary increase 2,417 Inflation 2.804 Savings Pector (1) 3,247 Savings Pector (2) 3,795 Avg Stock Returns 4,327 SD Stock Returns 2.210 Stock Allocation 2,982 Avg Stable Returns 12,799 SD Stable Returns 15,097 Stable Allocation 17,790 20.998 24.449 28.329 9.78: 6,257 0.900 9,78: 6.307 0.046 6,737 0.100 Investments Cashin APR: APR2 Income Housing 0.130 0.046 57.000 22.586 0.15 94 70,390 22.954 0.15 0.046 73.868 0.15 0.046 77.56 0.15 0.046 492 81.489 24.298 0.15 0.046 674 89 911 24,750 0.15 0.046 888 89.785 29.220 0.15 0.046 94 275 29,710 0.15 0.046 98.990 26.198 0.13 0.046 1,789 103,999 0.15 109,436 27.203 0.15 0.046 2.609 114,993 27,720 0.15 0.046 120.222 28.247 0.15 0.046 947 126 333 39.869 0.15 0.046 182.699 29.859 0.15 0.046 1,616 189 209 39.869 0.15 3.397 145 293 0.15 0.046 9.500 193.969 39.859 0.13 0.046 7,989 161 243 39.849 0.15 0.046 10,904 169 300 0.13 0.046 14,217 177,77: 39,869 0.13 0.046 18.301 185.699 39.249 0.13 0.046 22.941 195.992 39.859 0.13 0.046 28 332 205,792 29.869 0.19 0.046 34,983 215.082 39.869 0.15 0.046 41.827 225.888 29.069 0.13 0.046 50,200 288 280 39.869 0.15 99,888 0.19 0.046 74,019 29249 39.859 0.15 0.046 83 867 279,781 39.859 0.15 0.046 289 370 0.13 0.046 119.640 304,049 39,869 0.15 0.046 139,202 319.294 39.849 0.15 0.046 197,642 339 214 39.859 0.13 0.046 183,292 391,974 29.869 0.13 0.046 212.923 959 573 0.13 0.046 245,776 383.092 0.13 0.046 285.900 407,494 29.859 2,411 3,923 28 6,233 30 10,223 32 12,640 33 19,444 18,691 22,447 38 6,783 8,994 28 11,375 24,239 29,432 37,222 78,110 102,333 131,097 154,331 202,931 247,746 299,613 299,395 429,893 908,733 500,754 706,693 828,313 968,497 55 1,129,239 37 1,213,693 38 1,923,248 39 1,767,739 60 2.049,999 7.016 7,190 VECE Liv. Exp. 9,000 29,013 3,093 30,711 9,192 33,364 3,290 36,378 9,891 29,763 42,129 46,682 3,704 30,434 3,813 9,923 38,377 6,039 62.990 6,130 67,645 6,257 72,337 70.698 6,507 76,664 89,764 6,797 99,407 6,889 102.399 7,016 109,729 7,130 7,289 129,333 134,076 7,369 143,083 7,709 132,443 162.341 8,004 172,789 3,135 183,665 2.311 193,148 207,114 219,894 2,794 223,217 8,951 247,216 9,132 261,925 9,309 277,392 293,622 9,552 310,684 328,611 10,033 347,449 7,709 2.004 2,136 2.311 38,620 44 793 91,872 99,978 69,298 79,879 92.031 105,929 124,820 129,934 160,742 2.630 8.794 9,132 9.305 9,562 244.990 242.491 277,820 318,136 10,033