Attached are hypothetical returns for Apple and Google. In this exercise, we will perform security selection. The objective of this exercise is to learn how to use numerical optimization and Solver to optimally choose investment weights.
1. Calculate the average, standard deviation and Sharpe ratios of Apple and Google's returns.
2. As a starting point, assume your portfolio has investment weights of 50% each in Apple and Google. Calculate the portfolio's average, standard deviation and Sharpe ratio.
3. Using Excel's Data Table, calculate the portfolio's expected return, standard deviation and Sharpe ratio for investment weights in Apple ranging from 0% to 100%.
4. Using the data from (3), plot the efficient frontier by plotting the portfolio's expected return against the portfolio's standard deviation.
5. Using the data from (3), plot the objective function by plotting the portfolio's Sharpe ratio against Apple's investment weight. From examining the chart, approximately what is the optimal investment weight in Apple?
6. Use Excel's Solver to find the optimal investment weights in Apple and Google that maximizes the portfolio's Sharpe ratio. Assume you cannot short sale, i.e. each investment weight must be non-negative.
- Give examples of infeasible solutions.
- Give examples of feasible solutions.
- What is the optimal solution?
- Looking at the plot of the objective function, explain why Excel's Solver choose it's optimal investment weights.
- Is the optimal solution unique?
- For each constraint, explain if it binding or slack. Provide hypothetical examples of binding constraints.