DEVELOPING A STOCK PORTFOLIO You and your spouse have inherited $350,000 and are making some decisions on how to use the inheritance. You wish to invest at least some of it so you can retire early. Yet, you both would like to spend some on travel and luxury purchases. You think you need $500,000 to retire. The more you invest now, the more income you are likely to earn for smaller outlays in the interim. The more you invest, the earlier you can retire. Currently, you and your spouse have set up a portfolio of five stocks, as depicted on the Portfolio Allocation sheet. This baseline allocation divides your investment amount equally among the five stocks. Of course, we would like to maximize our returns on our investment. Stepping through the analyses below will help with your decisions. I: SENSITIVITY ANALYSIS Develop data tables to analyze the effect of (i) changes in Initial Capital outlays on Annual Income, and (ii) changes in initial Capital outlays and Investment Period on Final Portfolio Value. The effects of initial Capital outlays should be considered from $200,000 to $350,000, in increments of $5000. Consider Investment Periods from 4 to 10 years in increments of 2. Create both data tables on the Budget sheet. On the first data table, use conditional formatting to highlight annual income values above $5,000; on the second, highlight portfolio values above $500,000. (See examples alongside.) II: ALTERNATE SCENARIOS FOR PROJECTED EARNINGS INCREASE You and your spouse have different opinions on what the average annual appreciation rate is likely to be. You think it will be 12%, while your spouse thinks it will be only 9.5%. You also differ on how much capital you should invest - you think you should invest $250,000 while your spouse thinks you should invest the entire inheritance. Create these two alternate scenarios, and a scenario summary that depicts the impact of the different projections on Total Annual Income and Final Portfolio Value. Rename the scenario summary sheet Projected Earnings Scenarios. III: PICK STOCKS Use Solver to help you identify your best stock picks. First, make a copy of your Portfolio Allocation worksheet to a new StockSelection worksheet. Your objective is to maximize the Final Portfolio Value of your portfolio by changing the Number of Shares purchased in each company, subject to the following constraints: 1. Your Total Amount Invested cannot exceed the Initial Capital amount indicated on the Budget sheet. 2. You will need a Total Annual Income in the amount specified as Required Annual Income on the Budget sheet. 3. Stock purchases should be non-negative integers. Generate an answer report, and leave the initial values on the StockSelection sheet. Name the answer report StockPicks. Capital Annual Income $200,000 $ 2,944 $205,000 $ 2,944 $210,000 $ 3,304 $215,000 $ 3,380 $220,000 $ 3,380 $225,000 $ 3,536 $230,000 $ 3,720 $235,000 $ 3,720 $240,000 $ 3,796 $245,000 $ 3,964 $250,000 $ 3,964 $255,000 $ 3,964 $260,000 $ 3,964 $265.000 $ 3,964 $270,000 $ 4,040 $275,000 $ 4,040 $280,000 $ 4,556 $285,000 $ 4,740 $290,000 $ 4,740 $295,000 $ 4,984 $300.000 $ 4,984 $305,000 $ 4,984 $310.000 $ 4.984 $315,000 $ 4,984 $320,000 $ 5,060 $325,000 $ 5,060 $330,000 $ 5,060 $335,000 $ 5,216 $340,000 $ 5,216 $345,000 $ 5,644 $350,000 $ 6,004 Capital $ 200,000 $ $ 205,000 $ 210,000 $ $ 215.000 $ $ 220.000 $ $ 225,000 $ $ 230,000 $ $ 235,000 $ $ 240,000 $ $ 245,000 $ 250.000 $ $ 255,000 $ $ 260,000 $ $ 265,000 $ $ 270.000 $ $ 275,000 $ $ 280,000 $ $ 285,000 $ $ 290,000 $ $ 295,000 $ $ 300.000 $ $ 305,000 $ $ 310,000 $ $ 315,000 $ $ 320,000 $ $ 325,000 $ $ 330,000 $ $ 335.000 $ $ 340.000 $ $ 345,000 $ $ 350,000 $ 4 233,113 $ 233,113 $ 252,099 $ 259,316 $ 259,316 $ 274,387 $ 289,829 $ 289,829 $ 297,046 $ 310,317 $ 310,317 $ 310,317 $ 310,317 $ 310,317 317,534 $ 317,534 $ 351,591 $ 367,033 $ 367,033 $ 387,520 $ 387,520 $ 387,520 $ 387,520 $ 387,520 $ 394,738$ 394,738$ 394,738$ 409,809 $ 409.809 $ 445,738 $ 464,724 $ Investment Period 5 6 7 251,762 $ 271,903 $ 293,655$ 251,762 $ 271,903 $ 293,655 $ 272,266 $ 294,048$ 317,572 $ 280.061 $ 302,466 $ 326,663 $ 280,061 $ 302.466 $ 326.663 $ 296,338 $ 320.046 $ 345,649 $ 313,015$ 338,057 $ 365, 101 $ 313,015$ 338,057 $ 365, 101$ 320,810 $ 346,475$ 374,193 $ 335,142 $ 361,953 $ 390.910 $ 335,142 $ 361,953 $ 390.910 $ 335,142 $ 361,953 $ 390,910 $ 335,142 $ 361,953 $ 390,910 $ 335,142 $ 361,953 $ 390,910 $ 342,937$ 370,372 $ 400,001 $ 342,937 $ 370,372 $ 400,001 $ 379,718$ 410,096 $ 442,904 $ 396,395 $ 428, 107 $ 462,356 $ 396,395 $ 428,107$ 462,356 $ 418,522 $ 452,004 $ 488,164 $ 418,522 $ 452,004 $ 488,164 $ 418,522 $ 452,004 $ 488,164 $ 418,522 $ 452,004 $ 488,164 $ 418,522 $ 452,004 $ 488,164 $ 426,317$ 460,422 $ 497,256 $ 426,317 $ 460,422 $ 497,256 $ 426,317$ 460,422 $ 497,256 $ 442,594 $ 478,001 $ 516,242 $ 442.594 $ 478,001$ 516.242 $ 481,397 $ 519,909 $ 561,502 $ 501,902 $ 542,054 | $ 585,418 $ 8 317,148 $ 317,148 $ 342,977 $ 352.797 $ 352.797 $ 373.301 $ 394,309 $ 394,309 $ 404,128 $ 422,182 $ 422,182 $ 422, 182 $ 422.182 $ 422,182 $ 432,002 $ 432.002 $ 478,336 $ 499,344 $ 499,344 $ 527,217 $ 527,217 $ 527,217 $ 527,217 $ 527,217 $ 537,036 $ 537,036 $ 537,036 $ 557,541 $ 557,541 $ 606,422 $ 632,252 $ 9 10 342,519 $ 369,921 342,519 $ 369,921 370,416 $ 400,049 381,020 $ 411,502 381,020 $ 411.502 403.165 $ 435,418 425,854 | $ 459,922 425,854 | $ 459,922 436,459 $ 471,375 455,957 | $ 492,434 455,957 $ 492,434 455,957 $ 492.434 455,957 $ 492.434 455,957 $ 492,434 466,562 $ 503.887 466,562 $ 503.887 516,603 $ 557,931 539,291 $ 582,435 539,291 $ 582,435 569,394 $ 614,946 569,394 $ 614,946 569,394 $ 614.946 569,394 $ 614,946 569,394 $ 614,946 579,999$ 626,399 579,999 $ 626,399 579,999$ 626,399 602,144 $ 650.316 602.144 | $ 650,316 654,936 $ 707,331 682,832 $ 737,459 Initial Capital Required Annual Income Investment Period Appreciation Rate $ 250,000.00 Amount available for investment 2,500.00 Minimum annual income needed to cover investment expenses 10 Duration for which stocks are to be held 8.00% Projected annual appreciation rate $ $ Current Price Dividend Yield Number of Shares to be purchased Amount Invested Annual Income Final Portfolio Value AXP JNJ ORCL MSFT YUM Total 104.05 $ 145.06 $ 59.69 $ 214.88 $ 99.53 1.65 $ 2.79 $ 1.61 $ 1.04 $ 1.89 Buy 400 (sm) Buy 300 (sm) Buy 800 (sm) Buy 200 (sm) Buy 500 (sm) 41,620.00 $ 43,518.00 $ 47,752.00 $ 42,976.00 $ 49,765.00 $ 225,631.00 660.00 $ 837.00 $ 1,288.00 $ 208.00 $ 945.00 $ 3,938.00 89,854.46 $ 93,952.10 $ 103,092.99 $ 92,781.96 $ 107,438.90 $ 487,120.41 $ $ $ DEVELOPING A STOCK PORTFOLIO You and your spouse have inherited $350,000 and are making some decisions on how to use the inheritance. You wish to invest at least some of it so you can retire early. Yet, you both would like to spend some on travel and luxury purchases. You think you need $500,000 to retire. The more you invest now, the more income you are likely to earn for smaller outlays in the interim. The more you invest, the earlier you can retire. Currently, you and your spouse have set up a portfolio of five stocks, as depicted on the Portfolio Allocation sheet. This baseline allocation divides your investment amount equally among the five stocks. Of course, we would like to maximize our returns on our investment. Stepping through the analyses below will help with your decisions. I: SENSITIVITY ANALYSIS Develop data tables to analyze the effect of (i) changes in Initial Capital outlays on Annual Income, and (ii) changes in initial Capital outlays and Investment Period on Final Portfolio Value. The effects of initial Capital outlays should be considered from $200,000 to $350,000, in increments of $5000. Consider Investment Periods from 4 to 10 years in increments of 2. Create both data tables on the Budget sheet. On the first data table, use conditional formatting to highlight annual income values above $5,000; on the second, highlight portfolio values above $500,000. (See examples alongside.) II: ALTERNATE SCENARIOS FOR PROJECTED EARNINGS INCREASE You and your spouse have different opinions on what the average annual appreciation rate is likely to be. You think it will be 12%, while your spouse thinks it will be only 9.5%. You also differ on how much capital you should invest - you think you should invest $250,000 while your spouse thinks you should invest the entire inheritance. Create these two alternate scenarios, and a scenario summary that depicts the impact of the different projections on Total Annual Income and Final Portfolio Value. Rename the scenario summary sheet Projected Earnings Scenarios. III: PICK STOCKS Use Solver to help you identify your best stock picks. First, make a copy of your Portfolio Allocation worksheet to a new StockSelection worksheet. Your objective is to maximize the Final Portfolio Value of your portfolio by changing the Number of Shares purchased in each company, subject to the following constraints: 1. Your Total Amount Invested cannot exceed the Initial Capital amount indicated on the Budget sheet. 2. You will need a Total Annual Income in the amount specified as Required Annual Income on the Budget sheet. 3. Stock purchases should be non-negative integers. Generate an answer report, and leave the initial values on the StockSelection sheet. Name the answer report StockPicks. Capital Annual Income $200,000 $ 2,944 $205,000 $ 2,944 $210,000 $ 3,304 $215,000 $ 3,380 $220,000 $ 3,380 $225,000 $ 3,536 $230,000 $ 3,720 $235,000 $ 3,720 $240,000 $ 3,796 $245,000 $ 3,964 $250,000 $ 3,964 $255,000 $ 3,964 $260,000 $ 3,964 $265.000 $ 3,964 $270,000 $ 4,040 $275,000 $ 4,040 $280,000 $ 4,556 $285,000 $ 4,740 $290,000 $ 4,740 $295,000 $ 4,984 $300.000 $ 4,984 $305,000 $ 4,984 $310.000 $ 4.984 $315,000 $ 4,984 $320,000 $ 5,060 $325,000 $ 5,060 $330,000 $ 5,060 $335,000 $ 5,216 $340,000 $ 5,216 $345,000 $ 5,644 $350,000 $ 6,004 Capital $ 200,000 $ $ 205,000 $ 210,000 $ $ 215.000 $ $ 220.000 $ $ 225,000 $ $ 230,000 $ $ 235,000 $ $ 240,000 $ $ 245,000 $ 250.000 $ $ 255,000 $ $ 260,000 $ $ 265,000 $ $ 270.000 $ $ 275,000 $ $ 280,000 $ $ 285,000 $ $ 290,000 $ $ 295,000 $ $ 300.000 $ $ 305,000 $ $ 310,000 $ $ 315,000 $ $ 320,000 $ $ 325,000 $ $ 330,000 $ $ 335.000 $ $ 340.000 $ $ 345,000 $ $ 350,000 $ 4 233,113 $ 233,113 $ 252,099 $ 259,316 $ 259,316 $ 274,387 $ 289,829 $ 289,829 $ 297,046 $ 310,317 $ 310,317 $ 310,317 $ 310,317 $ 310,317 317,534 $ 317,534 $ 351,591 $ 367,033 $ 367,033 $ 387,520 $ 387,520 $ 387,520 $ 387,520 $ 387,520 $ 394,738$ 394,738$ 394,738$ 409,809 $ 409.809 $ 445,738 $ 464,724 $ Investment Period 5 6 7 251,762 $ 271,903 $ 293,655$ 251,762 $ 271,903 $ 293,655 $ 272,266 $ 294,048$ 317,572 $ 280.061 $ 302,466 $ 326,663 $ 280,061 $ 302.466 $ 326.663 $ 296,338 $ 320.046 $ 345,649 $ 313,015$ 338,057 $ 365, 101 $ 313,015$ 338,057 $ 365, 101$ 320,810 $ 346,475$ 374,193 $ 335,142 $ 361,953 $ 390.910 $ 335,142 $ 361,953 $ 390.910 $ 335,142 $ 361,953 $ 390,910 $ 335,142 $ 361,953 $ 390,910 $ 335,142 $ 361,953 $ 390,910 $ 342,937$ 370,372 $ 400,001 $ 342,937 $ 370,372 $ 400,001 $ 379,718$ 410,096 $ 442,904 $ 396,395 $ 428, 107 $ 462,356 $ 396,395 $ 428,107$ 462,356 $ 418,522 $ 452,004 $ 488,164 $ 418,522 $ 452,004 $ 488,164 $ 418,522 $ 452,004 $ 488,164 $ 418,522 $ 452,004 $ 488,164 $ 418,522 $ 452,004 $ 488,164 $ 426,317$ 460,422 $ 497,256 $ 426,317 $ 460,422 $ 497,256 $ 426,317$ 460,422 $ 497,256 $ 442,594 $ 478,001 $ 516,242 $ 442.594 $ 478,001$ 516.242 $ 481,397 $ 519,909 $ 561,502 $ 501,902 $ 542,054 | $ 585,418 $ 8 317,148 $ 317,148 $ 342,977 $ 352.797 $ 352.797 $ 373.301 $ 394,309 $ 394,309 $ 404,128 $ 422,182 $ 422,182 $ 422, 182 $ 422.182 $ 422,182 $ 432,002 $ 432.002 $ 478,336 $ 499,344 $ 499,344 $ 527,217 $ 527,217 $ 527,217 $ 527,217 $ 527,217 $ 537,036 $ 537,036 $ 537,036 $ 557,541 $ 557,541 $ 606,422 $ 632,252 $ 9 10 342,519 $ 369,921 342,519 $ 369,921 370,416 $ 400,049 381,020 $ 411,502 381,020 $ 411.502 403.165 $ 435,418 425,854 | $ 459,922 425,854 | $ 459,922 436,459 $ 471,375 455,957 | $ 492,434 455,957 $ 492,434 455,957 $ 492.434 455,957 $ 492.434 455,957 $ 492,434 466,562 $ 503.887 466,562 $ 503.887 516,603 $ 557,931 539,291 $ 582,435 539,291 $ 582,435 569,394 $ 614,946 569,394 $ 614,946 569,394 $ 614.946 569,394 $ 614,946 569,394 $ 614,946 579,999$ 626,399 579,999 $ 626,399 579,999$ 626,399 602,144 $ 650.316 602.144 | $ 650,316 654,936 $ 707,331 682,832 $ 737,459 Initial Capital Required Annual Income Investment Period Appreciation Rate $ 250,000.00 Amount available for investment 2,500.00 Minimum annual income needed to cover investment expenses 10 Duration for which stocks are to be held 8.00% Projected annual appreciation rate $ $ Current Price Dividend Yield Number of Shares to be purchased Amount Invested Annual Income Final Portfolio Value AXP JNJ ORCL MSFT YUM Total 104.05 $ 145.06 $ 59.69 $ 214.88 $ 99.53 1.65 $ 2.79 $ 1.61 $ 1.04 $ 1.89 Buy 400 (sm) Buy 300 (sm) Buy 800 (sm) Buy 200 (sm) Buy 500 (sm) 41,620.00 $ 43,518.00 $ 47,752.00 $ 42,976.00 $ 49,765.00 $ 225,631.00 660.00 $ 837.00 $ 1,288.00 $ 208.00 $ 945.00 $ 3,938.00 89,854.46 $ 93,952.10 $ 103,092.99 $ 92,781.96 $ 107,438.90 $ 487,120.41 $ $ $