ࡱ> xzstuvw 1bjbj !`'Al444J8,2|~K(JJJJJJJ$M O`#K#K.8K...^J.J..aC1G p"nD^D,1GNK0~K E$PP1G.l^A College Fund Investment Decision Alex Ferrara has a 12-year-old daughter. He wants to make investment plans to pay the $17,500 tuition for her first year of college 6 years from now. Alex has gone through his budget and finds that he can invest $200 per month for the next 6 years. He has decided to follow a strategy in which he contributes a fixed fraction of the $200 to each of 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 short-term Treasury bills. Both funds have very low fees, which can safely be ignored here. Keith Bernard, an adviser from the first fund, suggests that each month he should invest 80% of the $200 in the S&P 500 fund and the other 20% in the T-bill fund. Dont be a wuss, he explained. The S&P 500 has averaged much larger returns than the T-bill fund. Even though stock returns are risky investments in the short run, the risk will be fairly minimal over the longer 6-year period. Emily Leonard, an adviser from the other fund, recommends just the opposite: she suggests 20% in the S&P 500 fund and 80% in T-bills, because Treasury bills are backed by the United States government and are very safe. If you follow this allocation, she said, your average return will be lower, but at least you will have enough to reach your $17,500 target in 6 years. Not knowing which adviser to believe, Alex has come to you for help. Your job is two develop two simulation models; a parametric one, and one based on the resampling approach. These two approaches and some Crystal Ball hints are provided as an appendix to this document. The spreadsheet COLLEGE.XLS contains 261 monthly returns of the S&P 500 and Treasury bills from January 1970 through September 1991, which you are to use as the basis for your analysis. Develop your two spreadsheet models to simulate the two suggested investment strategies over the 6-year period. Use both of your models to answer each of these questions: Simulate 10,000 iterations of the two strategies over the 6-year period. Create histograms of the final fund values for each strategy from each model. Based on your simulation results, which of the two strategies would you recommend? Why? Suppose that Alex needs to have $19,500 to pay for the first year's tuition. Based on the same simulation results, which of the two strategies would you recommend now? Why? Write something brief and intelligent about the differences and/or similarities in your conclusions between the two models. Appendix: Using Historical Data in Crystal Ball There are two basic approaches to using historical data in a simulation, which we will refer to here as the parametric approach and the resampling approach. Each has advantages and disadvantages, and the modeler will use one or the other depending on the circumstances. The Parametric Approach In this approach, we fit the data to some theoretical distribution (such as normal or exponential) and estimate the parameters appropriate to the distribution (such as mean and standard deviation for a normal distribution, or lambda for an exponential distribution). In Alexs case, we will be able to define the returns on each mutual fund in terms of one of Crystal Balls built in probability distributions. This approach has significant advantages in terms of simplicity, because a random variable can be described with a few parameters instead of all the data. The model will be relatively small, with each random variable described with a relatively modest amount of information. The disadvantage is that we need some assurance that the theoretical distribution we choose is in fact a good fit to the data. This gives rise to a special kind of hypothesis test, called a goodness-of-fit test. Simulation software frequently includes a distribution-fitting utility, and Crystal Ball is no exception. Here we demonstrate the Crystal Ball fitting procedure on the data set shown in Table 1 below. We will need to find which theoretical distribution best fits each variable, estimate the proper parameters for each, and specify a correlation coefficient for the relationship between the two variables. XY1037110960100769492100799590115491027310568988210666113521086291105111559784998190778295921018545949810174891128788968611838106641046911058Table 1: Data Set Preliminary analysis reveals the sample means, sample standard deviations, and sample correlation coefficient as shown here:  Now we make a place for our two assumption cells in E9:F9 (remember to enter a number first; here we have entered zeroes).  Now well use the Crystal Ball distribution-fitting procedure to find the distribution that most closely fits the X variable. We select cell E9 and click on the Crystal Ball Define Assumption button.  Click on Fit. Enter the range containing the X data and click Next. The default option is to use the Chi-square test to see which one out of all the continuous distributions best fits our data. The default procedure is fine, so just click OK.  At the end of the procedure, it ranks the various distributions in descending order of fit to our data. Here are the six best fits to our X variable (note the descending p-values): In this case, all six of the distributions shown above pass the Chi-square test; we cannot reject the possibility that our X data came from any one of them. For our purposes in this exercise, well take the distribution that Crystal Ball ranks as #1 the Extreme Value distribution, with Mode = 95.52 and Scale = 8.46. Click on the Accept button to finish defining this assumption.  Using the same approach, we determine that the best fit to our Y data is a Logistic distribution with Mean = 75.0 and Scale parameter = 10.56. Now both assumptions have been defined:  The last step is to define the correlation between these two variables. You can specify the correlation between any pair of variables by selecting either assumption cell, clicking the Define Assumption button and then clicking on Correlate.  Here weve opened the X variable. The