Your next door neighbour, Scott Jansen, has a 12-year-old daughter, and he intends to pay the tuition for her first year of university six years from now. The tuition for the first year will be $18,500. Scott has gone through his budget and finds that he can invest $3,000 per year for the next six years. Scott has opened accounts at two mutual funds. The first fund follows an investment strategy designed to match the return of the S&P 500. The second fund invests in 3-month Treasury bills. Both funds have very low fees. Scott has decided to follow a strategy in which he contributes a fixed fraction of the $3,000 to each fund. An adviser from the first fund suggested that in each year he should invest 80% of the $3,000 in the S&P 500 fund and the other 20% in the T-bill fund. The adviser explained that the S&P 500 has averaged a much larger returns than the T-bill fund. Even though stock returns are risky investments in the short-run, the risk should be fairly minimal over the longer six-year period. An adviser from the second fund recommended just the opposite: invest 20% in the S&P 500 fund and 80% in T-bills, because treasury bills are backed by the Government. Going for this allocation plan, the average return will be lower, but at least there will be enough to reach the $18,500 target in six years. Not knowing which adviser to believe, Scott has come to you for help.

Assignment Tasks1. Based on historical data, the annual return rate of the S&P 500 fund is Triangularly distributed with 2%, -4%, and 6% as the most likely, worst, and best values respectively. The annual return rate of the T-bills is Continuous and Uniformly distributed between 0.1% and 3%. Construct a Monte Carlo spreadsheet model to simulate the two suggested investment strategies over the six-year period, using Analytic Solver. Calculate the total funds at the end of six years for both strategies and present them. Include a line graph of the annual total funds over time for each of the two strategies. Do either of the strategies satisfy the target? 2. Using Analytic Solver, simulate 2,000 runs of the two strategies over the six-year period. Present in your answer the histograms of the outputs for both strategies, along with the expected total funds. 3. What other real-world factors might be important to consider in designing the simulation and making a recommendation? 4. With the target amount after six years in mind, what other spreadsheet techniques can Scott use to determine how much he should invest in each year? Briefly describe one such techniques.