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 PortfolioAllocation 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.)

Initial Capital	$350,000.00	Amount available for investment
Required Annual Income	$ 2,500.00	Minimum annual income needed to cover investment expenses
Investment Period	10	Duration for which stocks are to be held
Appreciation Rate	8.00%	Projected annual appreciation rate

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 4 Investment Period Capital 5 6 7 8 9 10 $ 200,000 $ 233,113 $ 251,762 $ 271,903 $ 293,655 $ 317,148 $ 342,519 $ 369,921 $ 205,000 $ 233,113 $ 251,762 $ 271,903 $ 293,655 $ 317,148 $ 342,519 $ 369,921 $ 210,000 $ 252,099 $ 272,266 $ 294,048 $ 317,572 $ 342,977 $ 370,416 $ 400,049 $ 215,000 $ 259,316 $ 280,061 $ 302,466 $ 326,663 $ 352,797 $ 381,020 $ 411,502 $ 220,000 $ 259,316 $ 280,061 $ 302,466 $ 326,663 $ 352,797 $ 381,020 $ 411,502 $ 225,000 $ 274,387 $ 296,338 $ 320,046 $ 345,649 $ 373,301 $ 403,165 $ 435,418 $ 230,000 $ 289,829 $ 313,015 $ 338,057 $ 365,101 $ 394,309 $ 425,854 $ 459,922 $ 235,000 $ 289,829 $ 313,015 $ 338,057 $ 365, 101 $ 394,309 $ 425,854 $ 459,922 $ 240,000 $ 297,046 $ 320,810 $ 346,475 $ 374,193 $ 404,128 $ 436,459 $ 471,375 $ 245,000 $ 310,317 $ 335,142 $ 361,953 $ 390,910 $ 422,182 $ 455,957 $ 492,434 $ 250,000 $ 310,317 $ 335,142 $ 361,953 $ 390,910 $ 422,182 $ 455,957 $ 492,434 $ 255,000 $ 310,317 $ 335,142 $ 361,953 $ 390,910 $ 422,182 $ 455,957 $ 492,434 $ 260,000 $ 310,317 $ 335,142 $ 361,953 $ 390,910 $ 422,182 $ 455,957 $ 492,434 $ 265,000 $ 310,317 $ 335,142 $ 361,953 $ 390,910 $ 422,182 $ 455,957 $ 492,434 $ 270,000 $ 317,534 $ 342,937 $ 370,372 $ 400,001 $ 432,002 $ 466,562 $ 503,887 $ 275,000 $ 317,534 $ 342,937 $ 370,372 $ 400,001 $ 432,002 $ 466,562 $ 503,887 $ 280,000 $ 351,591 $ 379,718 $ 410,096 $ 442,904 $ 478,336 $ 516,603 $ 557,931 $ 285,000 $ 367,033 $ 396,395 $ 428,107 $ 462,356 $ 499,344 $ 539,291 $ 582,435 $ 290,000 $ 367,033 $ 396,395 $ 428,107 $ 462,356 $ 499,344 $ 539,291 $ 582,435 $ 295,000 $ 387,520 $ 418,522 $ 452,004 $ 488,164 $ 527 217 $ 569,394 $ 614,946 $ 300,000 $ 387,520 $ 418,522 $ 452,004 $ 488,164 $ 527,217 $ 569,394 $ 614,946 $ 305,000 $ 387,520 $ 418,522 $ 452,004 $ 488,164 $ 527,217 $ 569,394 $ 614,946 $ 310,000 $ 387,520 $ 418,522 $ 452,004 $ 488,164 $ 527,217 $ 569,394 $ 614,946 $ 315,000 $ 387,520 $ 418,522 $ 452,004 $ 488,164 $ 527,217 $ 569,394 $ 614,946 $ 320,000 $ 394,738 $426,317 $ 460,422 $ 497,256 $ 537,036 $ 579,999 $ 626,399 $ 325,000 $ 394,738 $ 426,317 $ 460,422 $ 497,256 $ 537,036 $ 579,999 $ 626,399 $ 330,000 $ 394,738 $ 426,317 $ 460,422 $ 497,256 $ 537,036 $ 579,999 $ 626,399 $ 335,000 $ 409,809 $ 442,594 $ 478,001 $ 516,242 $ 557,541 $ 602,144 $ 650,316 $ 340,000 $ 409,809 $ 442,594 $ 478,001 $ 516,242 $ 557,541 $ 602,144 $ 650,316 $ 345,000 $ 445,738 $ 481,397 $ 519,909 $ 561,502 $ 606,422 $ 654,936 $ 707,331 $ 350,000 $ 464,724 $ 501,902 $ 542,054 $ 585,418 $ 632,252 $ 682,832 $ 737,459 eleononen 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 4 Investment Period Capital 5 6 7 8 9 10 $ 200,000 $ 233,113 $ 251,762 $ 271,903 $ 293,655 $ 317,148 $ 342,519 $ 369,921 $ 205,000 $ 233,113 $ 251,762 $ 271,903 $ 293,655 $ 317,148 $ 342,519 $ 369,921 $ 210,000 $ 252,099 $ 272,266 $ 294,048 $ 317,572 $ 342,977 $ 370,416 $ 400,049 $ 215,000 $ 259,316 $ 280,061 $ 302,466 $ 326,663 $ 352,797 $ 381,020 $ 411,502 $ 220,000 $ 259,316 $ 280,061 $ 302,466 $ 326,663 $ 352,797 $ 381,020 $ 411,502 $ 225,000 $ 274,387 $ 296,338 $ 320,046 $ 345,649 $ 373,301 $ 403,165 $ 435,418 $ 230,000 $ 289,829 $ 313,015 $ 338,057 $ 365,101 $ 394,309 $ 425,854 $ 459,922 $ 235,000 $ 289,829 $ 313,015 $ 338,057 $ 365, 101 $ 394,309 $ 425,854 $ 459,922 $ 240,000 $ 297,046 $ 320,810 $ 346,475 $ 374,193 $ 404,128 $ 436,459 $ 471,375 $ 245,000 $ 310,317 $ 335,142 $ 361,953 $ 390,910 $ 422,182 $ 455,957 $ 492,434 $ 250,000 $ 310,317 $ 335,142 $ 361,953 $ 390,910 $ 422,182 $ 455,957 $ 492,434 $ 255,000 $ 310,317 $ 335,142 $ 361,953 $ 390,910 $ 422,182 $ 455,957 $ 492,434 $ 260,000 $ 310,317 $ 335,142 $ 361,953 $ 390,910 $ 422,182 $ 455,957 $ 492,434 $ 265,000 $ 310,317 $ 335,142 $ 361,953 $ 390,910 $ 422,182 $ 455,957 $ 492,434 $ 270,000 $ 317,534 $ 342,937 $ 370,372 $ 400,001 $ 432,002 $ 466,562 $ 503,887 $ 275,000 $ 317,534 $ 342,937 $ 370,372 $ 400,001 $ 432,002 $ 466,562 $ 503,887 $ 280,000 $ 351,591 $ 379,718 $ 410,096 $ 442,904 $ 478,336 $ 516,603 $ 557,931 $ 285,000 $ 367,033 $ 396,395 $ 428,107 $ 462,356 $ 499,344 $ 539,291 $ 582,435 $ 290,000 $ 367,033 $ 396,395 $ 428,107 $ 462,356 $ 499,344 $ 539,291 $ 582,435 $ 295,000 $ 387,520 $ 418,522 $ 452,004 $ 488,164 $ 527 217 $ 569,394 $ 614,946 $ 300,000 $ 387,520 $ 418,522 $ 452,004 $ 488,164 $ 527,217 $ 569,394 $ 614,946 $ 305,000 $ 387,520 $ 418,522 $ 452,004 $ 488,164 $ 527,217 $ 569,394 $ 614,946 $ 310,000 $ 387,520 $ 418,522 $ 452,004 $ 488,164 $ 527,217 $ 569,394 $ 614,946 $ 315,000 $ 387,520 $ 418,522 $ 452,004 $ 488,164 $ 527,217 $ 569,394 $ 614,946 $ 320,000 $ 394,738 $426,317 $ 460,422 $ 497,256 $ 537,036 $ 579,999 $ 626,399 $ 325,000 $ 394,738 $ 426,317 $ 460,422 $ 497,256 $ 537,036 $ 579,999 $ 626,399 $ 330,000 $ 394,738 $ 426,317 $ 460,422 $ 497,256 $ 537,036 $ 579,999 $ 626,399 $ 335,000 $ 409,809 $ 442,594 $ 478,001 $ 516,242 $ 557,541 $ 602,144 $ 650,316 $ 340,000 $ 409,809 $ 442,594 $ 478,001 $ 516,242 $ 557,541 $ 602,144 $ 650,316 $ 345,000 $ 445,738 $ 481,397 $ 519,909 $ 561,502 $ 606,422 $ 654,936 $ 707,331 $ 350,000 $ 464,724 $ 501,902 $ 542,054 $ 585,418 $ 632,252 $ 682,832 $ 737,459 eleononen