An investor wants to put together a portfolio, drawing from a set of 5 candidate stocks.    
What is the best combination of stocks to get the maximum return with a given variance (risk) ?
  Stock 1 Stock 2 Stock 3 Stock 4 Stock 5   Total
Portfolio % 20.00% 20.00% 20.00% 20.00% 20.00% 100.00%
Beta-Coefficient 0.577381 1.672619 0.64881 0.934524 0.767857  
Alpha-Coefficient 0.047738 -0.04274 0.049881 0.043452 0.016786  
Residual Variance 0.000261 0.014236 0.000353 0.006059 0.000389  
Weighted Variance 0.00001 0.00057 0.00001 0.00024 0.00002    
Portfolio Variance 0.12%
Portfolio Return 8.56%
Historical data (Returns) on stocks        
  Stock 1 Stock 2 Stock 3 Stock 4 Stock 5 Market
Period 1 10.00% 15.00% 12.00% 18.00% 5.00% 8.00%
Period 2 12.00% 17.00% 13.00% 16.00% 8.00% 10.00%
Period 3 8.00% 4.00% 9.00% 3.00% 10.00% 9.00%
Period 4 7.00% -8.00% 7.00% 4.00% 9.00% 7.00%
Period 5 9.00% 15.00% 9.00% 8.00% 5.00% 4.00%
Period 6 7.00% 22.00% 11.00% 10.00% 4.00% 6.00%
Period 7 8.00% 3.00% 9.00% -3.00% 4.00% 5.00%
Period 8 6.00% -14.00% 6.00% 15.00% 6.00% 5.00%
Period 9 9.00% 2.00% 8.00% 20.00% 8.00% 6.00%
Period 10 11.00% 15.00% 10.00% 16.00% 10.00%   8.00%
Problem                
An investor wants to put together a portfolio consisting of up to 5 stocks. Using the Sharpe Single-Index method,
what is the best combination of stocks to maximize return for a given level of risk (variance)?    
               
Solution                
1) The variables are the percentage or fractional allocations of our funds to invest in each stock. In this worksheet,
the variables are given the name Portfolio_fractions. The sum of the allocations (which must be 100%) is computed
in the cell named Portfolio_Total.              
2) The constraints are very simple. First there are the logical constraints:        
  Portfolio_Fractions >= 0 via the Assume Non-Negative option      
  Portfolio_Total = 1            
Then there is a constraint that the portfolio variance should be no more than 0.0003 (in this example). The portfolio
variance is calculated in the cell named Variance:          
  Variance <= 0.0003            
3) The objective is to maximize portfolio return which is calculated according to the Sharpe method in the cell
named Return.                
                 
Remarks                
This model uses historical data on the stocks and market to calculate the returns and variance of the stocks and
the portfolio. It also uses Excel's regression tools to estimate the alphas and betas, as well as the residual error,
necessary to use the Sharpe Single Index method.          
A different approach to this problem would be to minimize risk for a certain return. It is easy to adjust the Solver to
do this. Simply change the set cell to be the variance and adjust the constraint to hold the return at a given level.