PLEASE ANSWER QUESTION 3

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 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.)
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 PortfolioAllocation 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.
IV: AUTOMATE THE SENSITIVITY ANALYSIS (EXTRA CREDIT)
Set up a macro that allows your spouse to run a custom sensitivity analysis. This macro should permit him/her to determine the effect of Initial Capital outlays within a range specified by him/her, in increments specified by him/her. Finish by highlighting final portfolio values >= $500,000. If you are ambitious, set up this macro to let him/her simultaneously vary Initial Capital and the Investment Period.

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

	AXP	JNJ	ORCL	MSFT	YUM	Total
Current Price	$ 161.39	$ 166.58	$ 83.38	$ 303.33	$ 124.50
Dividend Yield	$ 1.72	$ 4.24	$ 1.28	$ 2.48	$ 2.00
Number of Shares to be Purchased	Buy 400 (sm)	Buy 400 (sm)	Buy 800 (sm)	Buy 200 (sm)	Buy 500 (sm)
Amount Invested	$ 64,556.00	$ 66,632.00	$ 66,704.00	$ 60,666.00	$ 62,250.00	$320,808.00
Annual Income	$ 688.00	$ 1,696.00	$ 1,024.00	$ 496.00	$ 1,000.00	$ 4,904.00
Final Portfolio Value	$ 139,371.56	$ 143,853.49	$ 144,008.93	$ 130,973.34	$134,393.08	$692,600.41