代做Decision Models帮做R程序

Decision Models

Retirement Planning Exercise

Assignment: Build the simulation to answer the questions at the end of the case. You may work with your teammates. However, each student should individually setup a Crystal Ball model and run it, and submit your model and answers online.

A few software hints that may help you as you work on this case:

1.    The overlay chart is under "View Charts" on the CB toolbar. You can only create an overlay   chart after you have run a simulation. Once the Overlay Chart window comes up, click the “New” bottom to open the “Choose forecasts” window and choose the forecasts to include in the chart.

2.    Make sure you are looking at the Cumulative view (as opposed to the Frequency view) of the chart; use the View menu in the Overlay Chart window to select the view.

3.   To compare the cumulative probability distributions, set the Chart Type to be “line” (as opposed to “column”). This can be done from the Overlay Chart menu “Preferences => Chart…=> Chart Type => Type => Line.”  Also the "hot key" control-t cycles through chart types.

4.    You can copy the Chart from CB using the Edit...Copy command on the Overlay Chart window and then paste the chart into Word.

Retirement Planning Exercise

Your next-door neighbor, Peter Gittins, having finished sending his children to college, has decided he needs to get serious about saving for retirement. He hopes to retire in six years and though he has retirement benefits provided by his employer, he believes he should now start saving aggressively to provide additional income after his retirement.

Peter has gone through his budget and found that he can save $2,000 per month. The question is: how to invest these funds?  Peter is considering two index funds, both offered by Vanguard. The first fund follows an investment strategy designed to match the return of the S&P 500 (the Vanguard 500 Index Fund Investor Shares (VFINX)). The second fund invests in short-term

Treasury bills (the Vanguard Short-Term Treasury Fund Investor Shares (VFISX)). Both funds have low fees.

Peter will follow a strategy where he invests a fixed fraction of the $2,000 to each fund, in each  month. One financial adviser suggests that he invest 75% of the $2,000 in the S&P 500 fund and the other 25% in the Treasury fund. Noting that the S&P 500 has averaged higher returns than the Treasury fund, he claims "even though stock returns are risky investments in the short run, the risk would be fairly minimal over the longer six-year period." A second financial advisor recommends just the opposite: invest 25% in the S&P 500 fund and 75% in the Treasury fund.

"Treasury bills are backed by the U.S. government," he said, "if you put your money in treasuries, your expected return may be lower, but you won't lose much of your precious savings." Not knowing which adviser to believe, Peter has come to you for help.

In the spreadsheet “ RetirementPlanningData.xlsx“ you will find the data for 200 monthly returns of the S&P 500 and Treasury funds and a deterministic spreadsheet model for this investment decision.

1.    Set up a simulation of the suggested investment strategies over Peter’s six-year investment period. Use the historical data to fit a distribution for the monthly returns for each investment type. There are detailed instructions on how to do this on the “ RetirementPlanningData.xlsx” spreadsheet.

2.    Vary the percentage invested in the S&P 500: Consider fractions 0%, 25%, 50%, 75% and 100%. Compare the cumulative probability distributions for the total amount available at the end of six years for these five strategies.

3.   Suppose Peter's goal is to accumulate a total of $170,000 in this account over the next six years. This amount, plus his pension and social security, should allow Peter and his wife to live comfortably. What is the probability of achieving this goal with the different investment strategies?