Developing A Stock Portfolio For You And Your Spouse

Instructions DEVELOPING A STOCK PORTFOLIO You and your spouse have Inher

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.

Paper For Above instruction

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 $5,000. 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.

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 $150,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.

Budget Initial Capital $175,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 for the portfolio

$377,811.87 Portfolio

Allocation data of five stocks including current prices and dividend yields, along with initial purchase quantities and investment amounts, is provided for analysis.

Recommendation summary: Analyze the investment options considering different appreciation rates, initial capital levels, and stock selection strategies to optimize final portfolio value, enhance annual income, and plan for early retirement. Use Solver to determine optimal stock purchases, develop sensitivity analyses, and create macro tools to assist in decision-making, ensuring that constraints such as total investment limits and desired income are met.

References

  • Brealey, R. A., Myers, S. C., & Allen, F. (2020). Principles of Corporate Finance (13th ed.). McGraw-Hill Education.
  • Gitman, L. J., & Zutter, C. J. (2019). Principles of Managerial Finance (15th ed.). Pearson.
  • Hull, J. C. (2018). Options, Futures, and Other Derivatives (10th ed.). Pearson.
  • Damodaran, A. (2012). Investment Valuation: Tools and Techniques for Determining the Value of Any Asset (3rd ed.). Wiley.
  • Sharpe, W. F., Alexander, G. J., & Bailey, J. V. (2020). Investments (11th ed.). Pearson.
  • Brigham, E. F., & Houston, J. F. (2019). Fundamentals of Financial Management (15th ed.). Cengage Learning.
  • Ross, S. A., Westerfield, R. W., & Jaffe, J. (2019). Corporate Finance (12th ed.). McGraw-Hill Education.
  • Fernandez, P. (2017). Stock Market Investing: The Complete Guide to Investment Strategies. Wiley.
  • Larson, D., & Erhardt, M. (2021). Quantitative Investment Analysis (3rd ed.). CFA Institute.
  • Colander, D., & Klammer, T. (2019). Macroeconomics (11th ed.). McGraw-Hill Education.

Related Assignments