One way to use the Sharpe Ratio is to find a “best” portfolio. The “Question 2” tab in the spreadsheet contains 10 years of returns for two stocks. Your goal is to find the optimal weights (by which I mean the weights that result in the highest Sharpe Ratio) for stock 1 and 2. Note that for computational purposes, you only need to find the weight for stock 1 (since Stock 2 will be whatever is left over to make the total 100%. I’ve used this fact in the spreadsheet FYI.) To find the weight, you’ll first need to use the weights to calculate the Portfolio Return column, which you’ll need to fill in the Average Return and Volatility cells on the right. For the Sharpe Ratio, you can assume that the risk-free rate is 0.5%. Once you’ve set up the calculations, you can run solver to find the weight in stock 1.

Please show formulas used to solve.

	Stock 1 Returns	Stock 2 Returns		Portfolio Return			Weight in Stock 1	Weight in Stock 2
Year 1	-8.0%	15.5%					50%	50%
Year 2	24.0%	13.5%
Year 3	16.5%	-6.4%
Year 4	9.7%	16.7%					Portfolio Average Return	0.00%
Year 5	-9.1%	22.7%					Portfolio Volatility	0.00%
Year 6	23.9%	11.7%
Year 7	8.8%	-11.7%					Sharpe Ratio	0.00
Year 8	-12.7%	4.7%
Year 9	14.1%	1.3%
Year 10	11.9%	8.6%