Data case 2 Econ 670 Show all your work to receive full credit. Completed assignments will include an Excel file. Please use one sheet for each question. Include your answer for Q7 (although done in Stata) in excel as well. The files must be submitted through Canvas by November 14, 2020, 6:45pm. You have just started working for a financial planning firm and have been asked to analyze the performance and risk of the following 10 stocks to decide on the optimal portfolio for your client: The stocks (tickers) are: Apple (AAPL) Autodesk (ADSK) Boeing (BA) Caterpillar (CAT) Ebay (EBAY) Goldman Sachs (GS) Hershey (HSY) IBM (IBM) J.P. Morgan (JPM) Walmart (WMT) S&P 500 (GSPC) 13 Week Treasury Bill Problem Collect information on the dividend-adjusted price of each stock and the S&P 500 index and the risk-free rate from Yahoo (finance.yahoo.com) as follows: 1 a. Enter the stock ticker, and click "historical data" on the right tab. Enter start data October 27, 2015 and end data October 27, 2020 to get five years of data. Click on show "historical prices" and frequency nonthly". b. Click on "apply" and "download data". Save the "date" and the "adj close" column and delete all other columns. Continue repeating step b) until you have downloaded all the tickers and keep pasting the "adj close" in columns next to each other, labeling each column with the ticker, except the S&P500 index should be labeled as the market "MKT" and the 13-week Treasury bill as the risk free rate "RF". Label the tab in the excel as "DATA". c. Convert these dividend-adjusted prices to monthly returns in a sepa- rate spreadsheet within your Excel file. Name this tab as "RETURNS". Create return series for each stock and the S&P500 index (Note: us- ing log-returns is easier). Convert the annualized T-bill rate which is quoted in percentage points to monthly return by dividing by 1,200 (there are 12 months per year and 100 percentage points per unit re- turn). Format all the returns as "percentage" with 2 decimal places. Problem 2 Label it as Problem2 in your excel. a. Compute the mean monthly return and standard deviation for each of the 10 stocks and the S&P500 index. Convert the monthly statistics to annual for easier interpretation multiply the mean monthly return by 12, and the standard deviation by the square root of 12). b. Using the annual statistics, create an Excel plot with the standard deviation (volatility) on the x-axis and average return on the y-axis. Do you see any relation between the volatility and return of the individual stocks? Why why not)? Note: The easy way to create a scatter plot with mean return on the y-axis and SD on the x-axis is to reorder the rows in Excel, putting SD in the row above mean, highlighting the two rows and choosing an "XY scatter plot" Problem 3 Copy the "RETURNS worksheet and rename it as "Problem3" (right click on the "RETURNS" tab, click move or copy). Keep only the returns for the 10 stocks and delete the other series. Your manager would like you to construct a portfolio from the 10 stocks. He suggests that you first try an equal weighted portfolio, using the weight of 0.1 for each stock, and determine the portfolio's average return and standard deviation. (Hint: create a column called "portfolio" to the right of your stocks, and calculate the return for each day using stocks and weights. Then compute the average portfolio return and standard deviation.) As before, your manager would like you to express the portfolio return and standard deviation on an annual basis. Your manager also told you to include a control sum of the weights in your spreadsheet to make sure that they sum to one. Problem 4 Copy the Problem3 sheet and label it as Problem4. You believe that you can do better than the equal weighted portfolio sug- gested by your manager. You will surprise your manager with a portfolio using the optimum weights that will provide the best risk and return combi- nations for the 10 stocks in your portfolio. Use the solver function in Excel to perform this analysis. First, use the solver to solve for the absolute minimum standard deviation you can obtain by combining the 10 stocks as follows: "Set objective to the cell where you have the portfolio standard deviation, and check "min" in the menu of choices to minimize the standard deviation. Choose "By changing variable cells" by highlighting all the 10 portfolio weights to tell Excel to look for the optimal weights. Add a con- straint by clicking the Add button next to "Subject to constraints". For the moment, the only constraint is that the weights have to sum to one. So point on the cell where you have the sum of weights, and specify that the cell must equal one. Make sure that the box "make unconstrained vari- The solver tool in Excel can be found on the "data" tab in the "Analysis Tools" section. If the tool is not available, load it into Excel by following these in- structions: https://support.office.com/en-us/article/Load-the-Solver-Add-in- 612926fc-d53b-46b4-872c-e24772f078ca Problem 5 Copy the Probleme sheet and label it as Problem5. Now compute the minimum standard deviation for each of the following annual return levels so you can plot the efficient frontier: 0%, 5%, 10%, 15%, 20%....,50%. This calculation is performed in the same way as before, except you add one more constraint on the return. Add a constraint by clicking the Add button next to "Subject to constraints". Point on the cell where you have the portfolio average return, and specify that the cell must equal 0%, 5%, 10%, 15%, 20%...50%. Keep copying and pasting the VALUE of the result of the optimization in a separate table to build the efficient frontier. Plot the efficient frontier by creating and XY scatter plot (similar to the one you did for the return and standard deviation of each stock, except connecting the dot). The standard deviation should be on the X axis and the return on the Y axis. The easiest way to achieve this is to copy the standard deviation in the row just above the returns, highlight the cells, and click on the scatter plot. How does your efficient frontier compare to the equal-weighted portfolio from c) above? Is the equal weighted portfolio efficient? Problem 6 Redo the analysis by adding a new risk-free security as security #11 in your portfolio. Begin by setting up new worksheet in your Excel file by copying the "PORTFOLIO" worksheet and renaming it as "Problem6'. The risk free security you consider has a return of 0.1% each month. (Note: Do NOT use the risk-free rate you downloaded for this problem because its standard deviation is not strictly zero). Include a weight for this security 4 when calculating the monthly portfolio return. That is, there will now be 11 weights making up your portfolio. Again, the 11 weights must sum to one. Compute the minimum standard deviation for each of the following return levels so you can plot the efficient frontier: 1.2%, 5%, 10%, 15%, 20%...50%. (note that the minimum return is now 1.2%, corresponding to the risk-free rate). Plot this efficient portfolio along the previous efficient frontier without the risk free rate. How does this efficient frontier compare to the previous one? Problem 7 Load your RETURNS data into Stata or similar econometric software for further analysis. a. Adjust all returns by subtracting the risk-free rate. Estimate the CAPM alpha and beta for each of the 10 stocks using the S&P500 as the market index and 13-week T-bill rate you downloaded as the risk-free rate. b. Report the estimated alphas (annualize the alphas by multiplying by 12) and the corresponding t-statistics. According to your estimates, did any of the stocks earn a significant positive alpha? In an efficient market, do you expect that the estimated alphas are informative about future alphas? c. Create a plot with the beta on the x-axis and average return (not the alpha) on the y-axis. (either in Stata or Excel) Do you see any relation between the beta and return of the individual stocks? Did you expect to see any? Why (why not)? Data case 2 Econ 670 Show all your work to receive full credit. Completed assignments will include an Excel file. Please use one sheet for each question. Include your answer for Q7 (although done in Stata) in excel as well. The files must be submitted through Canvas by November 14, 2020, 6:45pm. You have just started working for a financial planning firm and have been asked to analyze the performance and risk of the following 10 stocks to decide on the optimal portfolio for your client: The stocks (tickers) are: Apple (AAPL) Autodesk (ADSK) Boeing (BA) Caterpillar (CAT) Ebay (EBAY) Goldman Sachs (GS) Hershey (HSY) IBM (IBM) J.P. Morgan (JPM) Walmart (WMT) S&P 500 (GSPC) 13 Week Treasury Bill Problem Collect information on the dividend-adjusted price of each stock and the S&P 500 index and the risk-free rate from Yahoo (finance.yahoo.com) as follows: 1 a. Enter the stock ticker, and click "historical data" on the right tab. Enter start data October 27, 2015 and end data October 27, 2020 to get five years of data. Click on show "historical prices" and frequency nonthly". b. Click on "apply" and "download data". Save the "date" and the "adj close" column and delete all other columns. Continue repeating step b) until you have downloaded all the tickers and keep pasting the "adj close" in columns next to each other, labeling each column with the ticker, except the S&P500 index should be labeled as the market "MKT" and the 13-week Treasury bill as the risk free rate "RF". Label the tab in the excel as "DATA". c. Convert these dividend-adjusted prices to monthly returns in a sepa- rate spreadsheet within your Excel file. Name this tab as "RETURNS". Create return series for each stock and the S&P500 index (Note: us- ing log-returns is easier). Convert the annualized T-bill rate which is quoted in percentage points to monthly return by dividing by 1,200 (there are 12 months per year and 100 percentage points per unit re- turn). Format all the returns as "percentage" with 2 decimal places. Problem 2 Label it as Problem2 in your excel. a. Compute the mean monthly return and standard deviation for each of the 10 stocks and the S&P500 index. Convert the monthly statistics to annual for easier interpretation multiply the mean monthly return by 12, and the standard deviation by the square root of 12). b. Using the annual statistics, create an Excel plot with the standard deviation (volatility) on the x-axis and average return on the y-axis. Do you see any relation between the volatility and return of the individual stocks? Why why not)? Note: The easy way to create a scatter plot with mean return on the y-axis and SD on the x-axis is to reorder the rows in Excel, putting SD in the row above mean, highlighting the two rows and choosing an "XY scatter plot" Problem 3 Copy the "RETURNS worksheet and rename it as "Problem3" (right click on the "RETURNS" tab, click move or copy). Keep only the returns for the 10 stocks and delete the other series. Your manager would like you to construct a portfolio from the 10 stocks. He suggests that you first try an equal weighted portfolio, using the weight of 0.1 for each stock, and determine the portfolio's average return and standard deviation. (Hint: create a column called "portfolio" to the right of your stocks, and calculate the return for each day using stocks and weights. Then compute the average portfolio return and standard deviation.) As before, your manager would like you to express the portfolio return and standard deviation on an annual basis. Your manager also told you to include a control sum of the weights in your spreadsheet to make sure that they sum to one. Problem 4 Copy the Problem3 sheet and label it as Problem4. You believe that you can do better than the equal weighted portfolio sug- gested by your manager. You will surprise your manager with a portfolio using the optimum weights that will provide the best risk and return combi- nations for the 10 stocks in your portfolio. Use the solver function in Excel to perform this analysis. First, use the solver to solve for the absolute minimum standard deviation you can obtain by combining the 10 stocks as follows: "Set objective to the cell where you have the portfolio standard deviation, and check "min" in the menu of choices to minimize the standard deviation. Choose "By changing variable cells" by highlighting all the 10 portfolio weights to tell Excel to look for the optimal weights. Add a con- straint by clicking the Add button next to "Subject to constraints". For the moment, the only constraint is that the weights have to sum to one. So point on the cell where you have the sum of weights, and specify that the cell must equal one. Make sure that the box "make unconstrained vari- The solver tool in Excel can be found on the "data" tab in the "Analysis Tools" section. If the tool is not available, load it into Excel by following these in- structions: https://support.office.com/en-us/article/Load-the-Solver-Add-in- 612926fc-d53b-46b4-872c-e24772f078ca Problem 5 Copy the Probleme sheet and label it as Problem5. Now compute the minimum standard deviation for each of the following annual return levels so you can plot the efficient frontier: 0%, 5%, 10%, 15%, 20%....,50%. This calculation is performed in the same way as before, except you add one more constraint on the return. Add a constraint by clicking the Add button next to "Subject to constraints". Point on the cell where you have the portfolio average return, and specify that the cell must equal 0%, 5%, 10%, 15%, 20%...50%. Keep copying and pasting the VALUE of the result of the optimization in a separate table to build the efficient frontier. Plot the efficient frontier by creating and XY scatter plot (similar to the one you did for the return and standard deviation of each stock, except connecting the dot). The standard deviation should be on the X axis and the return on the Y axis. The easiest way to achieve this is to copy the standard deviation in the row just above the returns, highlight the cells, and click on the scatter plot. How does your efficient frontier compare to the equal-weighted portfolio from c) above? Is the equal weighted portfolio efficient? Problem 6 Redo the analysis by adding a new risk-free security as security #11 in your portfolio. Begin by setting up new worksheet in your Excel file by copying the "PORTFOLIO" worksheet and renaming it as "Problem6'. The risk free security you consider has a return of 0.1% each month. (Note: Do NOT use the risk-free rate you downloaded for this problem because its standard deviation is not strictly zero). Include a weight for this security 4 when calculating the monthly portfolio return. That is, there will now be 11 weights making up your portfolio. Again, the 11 weights must sum to one. Compute the minimum standard deviation for each of the following return levels so you can plot the efficient frontier: 1.2%, 5%, 10%, 15%, 20%...50%. (note that the minimum return is now 1.2%, corresponding to the risk-free rate). Plot this efficient portfolio along the previous efficient frontier without the risk free rate. How does this efficient frontier compare to the previous one? Problem 7 Load your RETURNS data into Stata or similar econometric software for further analysis. a. Adjust all returns by subtracting the risk-free rate. Estimate the CAPM alpha and beta for each of the 10 stocks using the S&P500 as the market index and 13-week T-bill rate you downloaded as the risk-free rate. b. Report the estimated alphas (annualize the alphas by multiplying by 12) and the corresponding t-statistics. According to your estimates, did any of the stocks earn a significant positive alpha? In an efficient market, do you expect that the estimated alphas are informative about future alphas? c. Create a plot with the beta on the x-axis and average return (not the alpha) on the y-axis. (either in Stata or Excel) Do you see any relation between the beta and return of the individual stocks? Did you expect to see any? Why (why not)