60.1K

Verified Solution

Link Copied!

In this part of the course project, you will create and use a simulation to analyze a cash flow situation with two random variables. You will then extend your use of the simulation to examine outcomes based on manipulating two decision variables. This project part has two required sections and a third optional section.

Scenario:

You are doing some retirement planning to make sure you have sufficient funds for retirement. You plan to retire in $15$ years. You have already saved $$250,000$ toward retirement. Presently, you are making $$100,000$ per year and contributing $10\%$ of your salary to retirement. You estimate that you will receive annual salary adjustments of $2\mathrm{.}5\%$ over the next $15$ years and will be able to generate an $8\%$ annual return on your investments. You feel relieved because according to your estimates, you will have over $$1,100,000$ at the time you retire.

But you are wondering about the impact of uncertainty on your retirement plans. While on average your raises have been $2\mathrm{.}5\%,$ they have been as low as $0\%$ some years and as high as $5\%$ other years. While average returns on your investments have been $8\%,$ they have a lot of uncertainty with a standard deviation of $4\%.$

Using the spreadsheet model as a template, build a simulation model to help investigate the impact of uncertainty on your retirement. Assume your annual salary adjustments are uniformly distributed between $0\%$ and $5\%,$ and that returns are normally distributed with a mean of $8\%$ and standard deviation of $4\%.$

To satisfy this part of the project, you will:

$$ Create an Excel workbook with a simulation of your retirement income

$$ Answer questions related to the baseline assumptions in the scenario

$$ Modify your Excel workbook to incorporate decision variables and then submit that modified workbook to your instructor

$$ Answer additional questions based on manipulating the decision variables you have included in your workbook

Section A

Working with the assumptions in the baseline scenario, create a simulation that projects the balance in your retirement account after $15$ years. An Excel template is provided on the course project page to help you get started. Be sure to include sufficient iterations in your simulation to generate a reliable sample.

Assuming you retire in $15$ years, use your simulation to answer the following questions:

$1.$ What is the average simulated value of your retirement account after $15$ years?

$2.$ What is the probability that you have more than $$1,000,000$ at retirement?

$3.$ What is the probability that you have less than $$750,000$ at retirement? $$

Answer & Explanation Solved by verified expert

See Answer