Assignment 2 Directions: Create the spreadsheet for the mean-variance optimized portfolio using the 5 stocks and weights you chose previously. Steps for creating the optimized portfolio in Excel: Step 1: Create 6 columns, 1 for the date and the other 5 for each stock ticker. Then add the closing price for each month for every stock. Step 2: Create a similar set of columns for Returns starting in column H for all 5 stocks. Important here, you will not have values in the first row of returns. From above picture, that will be the 11/1/2017 row. To create the values for these columns copy and paste for all 5 columns and 60 rows the following: =((B4/B3)-1)*100 into cell H4 or whatever cell matches up with your first column but 2nd row of data. Note the highlighted row, this is the first row of data that is now left blank. Step 3: Create a similar set of columns for Statistics starting in column N for all 5 stocks. In column N, label the following 4 cells vertically: Average Monthly Return, Monthly Variance, Annual Return, Annual Variance. To create the Average Monthly Return values, use Excel Average function for each column of stocks in the Returns columns. This would be columns H L on my examples. To create Monthly Variance values, use Excel Var.P function for each column of stocks in the Returns columns. This would be columns H L on my examples. To create Annual Return values, multiply the Average Monthly Return value by 12 for each of the stocks. To create Annual Variance values, multiply the Monthly Variance value by 12 for each of the stocks. Step 4: Create a similar set of columns for Excess Returns starting in column U for all 5 stocks. Since we will be finding the difference between the monthly returns and the annual return we will need to lock the value for annual return before copying and pasting for all values. We accomplish this by inserting a $ before both the column and row number of the cell that we want to lock. Using my row and columns example, you would enter: =H4-$O$3 for the first stock and copy and paste straight down for 1 company at a time. The next company (MU) will have the following entered in the first cell: =I4-$P$3 and then copied straight down. This will create a 5 x 60 matrix of the difference in monthly return values and the average monthly return. Continue this for all 5 stocks. Step 5: Create a similar set of columns for Variance-Covariance Matrix starting in column AA for all 5 stocks. In column AA list all of the 5 stocks vertically as well. To create the Variance-Covariance Matrix you will need to highlight the entire empty matrix cells and type the following into cell AB3 in my example (this is the cell that corresponds to TGT TGT row/column): =MMULT(TRANSPOSE(U3:Y61),U3:Y61)*12/59 This is performing matrix multiplication on the 5 columns and 60 rows of the Excess Returns matrix (note* you actually only use the first 59 rows, leave the last row out of the calculations). Do NOT hit enter after typing this into the function bar, you must use control + shift + enter to execute this cell. Step 6: Add the Risk Free Treasury rate to a cell just below your Variance Covariance Matrix (currently its 4.16 Step 7: Label Annual Returns in a cell just below the Risk Free Treasury Rate cell and add 5 vertical labels for each stock just below that and copy the annual returns for each stock into their corresponding cells beside the labels Step 8: Create a label named My Weight Portfolio just below Annual Returns and add 5 vertical labels for each stock. Add the weights you chose for each stock next to their respective stock label. My example uses equal weights of .2 for each stock (these must add up to 1 for all stocks). Next create vertical labels for Expected Return, Risk and Sharpe Ratio and use the following equations for each of the values. Expected Return: =MMULT(TRANSPOSE(AB21:AB25),AB12:AB16) where AB21:AB25 is the weights you chose and AB12:AB16 is the annual returns for each stock Risk: =SQRT(MMULT(MMULT(TRANSPOSE(AB21:AB25),AB3:AF7),AB21:AB25)) where AB3:AF7 is the Variance Covariance matrix Sharpe Ratio: =(AB27 AB9)/AB28 where AB27 is expected return, AB9 is Risk free rate and AB28 is Risk Step 9: Label Optimally Weighted Portfolio just below Sharpe Ratio and create same labels as My Weighted Portfolio. Use your weights to start with and now we will use Excels solver to find the optimum weights and values when we maximize the Sharpe Ratio. Recreate all of the above steps to find Expected Return, Risk and Sharpe Ratio. At this point your Optimum and My Weighted results should be identical. Open up the solver from the Data tab in Excel (if you do not have it, you must go to Excel add-ins and click the box for it). Below is the screenshot of what solver will look like once you have it open. Yellow highlight: Add the cell label for the Sharpe Ratio you just created (this is the value from the Optimally Weighted section) Blue highlight: Add the cell range for the weights from the Optimally Weighted section (this is what solver will attempt to maximize) Green highlight: Click the add button first then set the Sum cell equal to 1. We dont want to allow investments of more than 100% of our resources. Yellow highlight is where you add cell refence for Sum (my example is AB37), and blue highlight will be set to, the number 1. Make sure and use the dropdown to change the <= to just =. Pink highlight: Default setting is checked. Just make sure the box is checked, this forces no short selling.

the stocks that this is for Nasdaq , Colgate , Centene Corp, Clearway Energy, Honeywell international