You are required to write regarding the use of spreadsheet software to analyze financial data and compute return and risk measures of financial securities in a complex environment.

China Invest International

Lee Chen, the portfolio manager of Shanghais second largest investment company, China Invest International, is advising local clients on prospective stock investments in the US markets. Since you previously worked for a reputable investment firm in North America and, therefore, have gained extensive knowledge of the US stock markets, Mr. Chen requests your analytical input. Specifically, you are being asked to prepare an over-view of the historic risk and return characteristics of the well-known S&P 500 stock index. Mr. Lee would also like you to present a risk/return comparison of large and small company stocks and to prepare a portfolio example to demonstrate to clients the concept of the Security Market Line. To that extent, perform the following tasks using the last 20 years of data below:

		Annual returns in percent:
Year	Large Company Stocks	Small Company Stocks	S&P500 Index	Treasury Bills
1994	1.32	-3.34	-2.32	4.39
1995	37.58	33.21	13.73	5.61
1996	22.96	16.50	46.94	5.14
1997	33.36	22.36	24.69	5.19
1998	28.58	-2.55	30.54	4.86
1999	21.04	21.26	8.97	4.80
2000	-9.10	-3.02	-2.04	5.98
2001	-11.89	-1.03	-17.26	3.33
2002	-22.10	-21.58	-24.29	1.61
2003	28.89	47.25	32.19	0.94
2004	10.88	18.33	4.43	1.14
2005	4.91	4.55	8.36	2.79
2006	15.79	18.29	12.36	4.97
2007	5.49	7.42	-4.15	4.52
2008	-37.00	-40.54	-38.47	1.24
2009	26.46	43.89	23.49	0.15
2010	15.06	16.91	12.64	0.14
2011	5.53	-1.80	0.00	0.03
2012	7.26	15.91	13.29	0.05
2013	26.50	38.32	29.60	0.07

1. Compute the arithmetic and geometric mean return for large company stocks and the corresponding standard deviation.

2. Determine the coefficient of variation for large company stocks and interpret your results.

3. Use the return data provided in the box and repeat steps 1 and 2 above for the small company stocks and the S&P 500 index and compare the risk/return relationships based on the coefficient of variation (CV).

4. Calculate the beta of both large and small company stocks relative to the S&P 500 index and interpret your result.

5. Form an equally weighted portfolio of large and small stocks and compute the Reward-to-Risk Ratio (the slope of the Security Market Line).

6. Lower the weight of the equally weighted stock portfolio to 80 percent and add 20 percent US Treasury bills. Compute the resulting Reward-to-Risk Ratio. Increase the weight of US Treasury bills to 40 percent and 60 percent, repeat your computation, and interpret your results.