(PLEASE PROVIDE EXCEL FILE) Your next-door neighbor, Scott Jansen, has a 12-year-old daughter, and he intends to pay the

[answerhappygod]

(PLEASE PROVIDE EXCEL FILE) Your next-door neighbor, ScottJansen, has a 12-year-old daughter, and he intends to pay thetuition for her first year of college six years from now. Thetuition for the first year will be $22,500. Scott has gone throughhis budget and finds that he can invest $3000 per year for the nextsix years. Scott has opened accounts at two mutual funds. The firstfund follows an investment strategy designed to match the return ofthe S&P 500. The second fund invests in 3-month Treasury bills.Both funds have very low fees. Scott has decided to follow astrategy in which he contributes a fixed fraction of the $3000 toeach fund. An adviser from the first fund suggested that in eachyear he should invest 80% of the $3000 in the S&P 500 fundand the other 20% in the T-bill fund. The adviser explained thatthe S&P 500 has averaged much larger returns than the T-billfund. Even though stock returns are risky investments in the shortrun, the risk should be fairly minimal over the longer six-yearperiod. An adviser from the second fund recommended just theopposite: invest 20% in the S&P 500 fund and 80% in T-bills,because treasury bills are backed by the United States government.If you follow this allocation, he said, your average return will belower, but at least you will have enough to reach your $22,500target in six years. Not knowing which adviser to believe, Scotthas come to you for help. Questions The file C16_01.xlsx containsannual returns of the S&P 500 and 3-month Treasury bills from1960. Suppose that in each of the next 72 months (six years), it isequally likely that any of the historical returns will occur.Develop a spreadsheet model to simulate the two suggestedinvestment strategies over the six-year period. Plot the value ofeach strategy over time for a single iteration of the simulation.What is the total value of each strategy after six years? Do eitherof the strategies reach the target? Simulate 1000 iterations of thetwo strategies over the six-year period. Create a histogram of thefinal fund values. Based on your simulation results, which of thetwo strategies would you recommend? Why? Suppose that Scott needsto have $25,000 to pay for the first year’s tuition. Based on thesame simulation results, which of the two strategies would yourecommend now? Why? What other real-world factors might beimportant to consider in designing the simulation and making arecommendation?