using excel crystal ball to solve the question; please answer the question Please write down the steps or take photos.

Simulation #1

A client has asked for you to assemble a portfolio of stocks for her to invest in for the next five years. She has asked that you blend small and large cap stocks (using your firms large and small cap index funds) so that there is at least a 60% chance that she will have between $125 and $375 from a $100 investment made today. She has asked that you set the portfolio structure today and not change it over the next five years. This should avoid excessive transaction costs in future years.

Your research department has provide you the following information about the stock portfolios (each assumes a normal distribution):

ASSET CLASS	EXPECTED RETURN	RETURN STANDARD DEVIATION	CORRELATION BETWEEN PORTFOLIOS
Large Cap Common Stocks	12.0%	21.1%	0.82
Small Cap Common Stock	17.7%	35.9%

Using these statistics prepare a simulation analysis of the two stock groups and your recommended portfolio blend.

To do this you will use Crystal Ball. To begin, layout the inputs (the returns for large and small cap stocks for each of the next five years). While defining the assumptions (one for each year, for each stock group) you will have to correlate the small and large cap stock returns. To do this you will use the graphical interface (directions in book) in Crystal Ball to define each return assumption (do not use the cb.normal function unless you know explicitly how to induce the correlation). To work with the correlation function once you have defined one return for a given year (say large cap stocks), you can correlate the second return select by clicking on the Correlate button on the menu while you define the assumption for the second return (say small cap stocks). This will ask which other assumption you wish to be correlated with and ask for the correlation value. Once the correlation is entered Crystal Ball will show you a picture of how the returns from the simulation will look. Further information on how to use these functions is available under the HELP function.

As outputs compute the Future Value of a $100 dollar investment in each asset class over the five-year horizon (small and large cap stocks). From these future values you can select a portfolio with varying initial investment amounts in small and large cap stocks. Experiment with the portfolio design and re-run the simulation until you find the structure you would like to recommend (be sure to rewind the simulation as you repeat it).

For each asset class and for the portfolio, your client has you to provide the following summary statistics:

Small Cap Stocks

NUMBER OF TRIALS
Mean Asset Value
Median Asset Value
Mode Asset Value
Standard Deviation
Variance
Skewness
Minimum Value
Maximum Value
Probability in range (125,375)

Large Cap Stocks

NUMBER OF TRIALS
Mean Asset Value
Median Asset Value
Mode Asset Value
Standard Deviation
Variance
Skewness
Minimum Value
Maximum Value
Probability in range (125,375)

Portfolio

NUMBER OF TRIALS
Mean Asset Value
Median Asset Value
Mode Asset Value
Standard Deviation
Variance
Skewness
Minimum Value
Maximum Value
Probability of losing money
Percent in Small Cap Stocks
Probability in range (125,375)