Use the following information to construct a Retirement Planning analysis. Question 1: How much will be the total accumulation at the end of the retirement planning horizon? Answer Reports the amount of the total accumulation at the end of the horizon in this cell. Question 2: Sensitivity analysis (20 points) Provide a sensitivity analysis that shows how the total accumulation changes with annual contribution Use your own assumptions of annual contributions. Data and assumptions below are assumed to be constant for the planning horizon PLANNING YOUR RETIREMENT, NEXT 30 YEARS Annual Annual Desired Asset Return STDEV Weights T-bill 1 2.5% 3.2% 30.0% bond 2 3.2% 9.4% 30.0% Large Cap 3 8.0% 16.4% 20.0% Small Cap 4 12.0% 33.45 20.0% Strategy Desired Weights in Assets Must be Maintained Each Year. That is, portfolio will be rebalanced every year to the desired weights. Requirement: Portfolio weights will be rebalanced annually to the desired weights in assets. (Heavy penalty will result for failing to do so.) Current wealth in portfolio (BEG) $ 5,000 BEG indicates the beginning of the year. Annual Contribution, END $ 10,000 END Indicates the end of the year. The first contribution will be made the end of the first year, Finish the template by filling in the blank (30 points) Portfolio balance Year Contribution BEG END 1 2 3 4 Question 3: Solver with scenario manager (25 points) The objective now is a total accumulation of $1.2 million by the end of the planning horizon. Use Solver to find the annual contribution that is needed to achieve this objective and use Scenario Manager to store the outcome in a scenario. The original model must be showing when you submit your work for grading, 5 6 7 B 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 14 25 26 27 R 29 301 Use the following information to construct a Retirement Planning analysis. Question 1: How much will be the total accumulation at the end of the retirement planning horizon? Answer Reports the amount of the total accumulation at the end of the horizon in this cell. Question 2: Sensitivity analysis (20 points) Provide a sensitivity analysis that shows how the total accumulation changes with annual contribution Use your own assumptions of annual contributions. Data and assumptions below are assumed to be constant for the planning horizon PLANNING YOUR RETIREMENT, NEXT 30 YEARS Annual Annual Desired Asset Return STDEV Weights T-bill 1 2.5% 3.2% 30.0% bond 2 3.2% 9.4% 30.0% Large Cap 3 8.0% 16.4% 20.0% Small Cap 4 12.0% 33.45 20.0% Strategy Desired Weights in Assets Must be Maintained Each Year. That is, portfolio will be rebalanced every year to the desired weights. Requirement: Portfolio weights will be rebalanced annually to the desired weights in assets. (Heavy penalty will result for failing to do so.) Current wealth in portfolio (BEG) $ 5,000 BEG indicates the beginning of the year. Annual Contribution, END $ 10,000 END Indicates the end of the year. The first contribution will be made the end of the first year, Finish the template by filling in the blank (30 points) Portfolio balance Year Contribution BEG END 1 2 3 4 Question 3: Solver with scenario manager (25 points) The objective now is a total accumulation of $1.2 million by the end of the planning horizon. Use Solver to find the annual contribution that is needed to achieve this objective and use Scenario Manager to store the outcome in a scenario. The original model must be showing when you submit your work for grading, 5 6 7 B 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 14 25 26 27 R 29 301