Attracted by a combination of long shelf-lives, low-cost, and nostalgia, during the pandemic, Americans increased consumption of processed and packaged foods. This reversal of a broader consumer trend away from these foods towards fresher, healthier options, gave companies like The Kraft Heinz Company (ticker: KHC), Campbells Soup Company (ticker: CPB), and Conagra Brands, Inc. (ticker: CAG) a much needed boost in sales in previously flagging divisions. Whether this change in consumer buying habits is sustainable has beenthe discussion of recent articles (NYTimes; Marketplace). For the following exercise, you will be extracting monthly stock data from Yahoo Finance for each of the three companies listed above, as well as the market proxy, S&P 500 index (ticker: GSPC). For each of the provided tickers (three firms and the S&P500 index), use the Yahoo Finance search bar to pull up the firm/index summary page. Navigate to the Historical Data menu and download monthly historical prices for October 27, 2015 October 27, 2020." Combine each of your downloaded files into a single spreadsheet. You will need to keep the Date and Adjusted Closing Price of each stock. 1 Spreadsheet 1. Calculate the monthly returns on each security using the Adjusted Closing Price. 2. Create a scatter plot. Under the Insert menu, navigate to Charts and select the Scatter Plot. To plot a characteristic line for an asset, first highlight the cells containing the Market portfolio returns (S&P 500 index) and the cells containing the Asset returns (one for each firm ticker), and then under the Insert and Charts select "Scatter and Scatter with Markers." Once the graph appears you can move it, format the axis scales and titles, and remove the legend and gridlines. 3. The resulting chart should display a scatterplot of the observed monthly returns. Right click on an individual data point in the chart and select Add Trendline. Use the Format Trendline menu or right click on the trendline itself. Ensure that the trendline is set to be linear (this should be the default setting). Select the option to Display Equation on Chart. 4. Repeat steps 2-3 for each firm. (You should end up with 3 series/charts with three individual trendlines.) 2 Questions 1. Based on the trendline equation displayed on each chart, what is the B for eachfirm? 2. Rank the firms in order of responsiveness to the market (highest to lowest). 1 3. If you held an equally weighted portfolio of these three stocks, what would the overall portfolio beta be? 4. Use the average monthly return over the past 60 months to forecast an annual expected market return for the next year. The 10-year Treasury bond has a 0.79% yield on 10/27/2020. If we assume this is the risk-free rate, what is the expected return on each asset? 5. If you held an equally weighted portfolio of these three stocks, what would be the overall expected return be? 6. Your roommate has recently taken up day-trading on Robinhood. After reading articles and various Reddit posts, your roommate decides to reallocate their entire stock portfolio from a diversified S&P500 index fund to these three companies to take advantage of the change in consumer buying habits. What advice might you give to your roommate? Discuss the shift in the portfolio risk in terms of its two erlying types of total risk. Attracted by a combination of long shelf-lives, low-cost, and nostalgia, during the pandemic, Americans increased consumption of processed and packaged foods. This reversal of a broader consumer trend away from these foods towards fresher, healthier options, gave companies like The Kraft Heinz Company (ticker: KHC), Campbells Soup Company (ticker: CPB), and Conagra Brands, Inc. (ticker: CAG) a much needed boost in sales in previously flagging divisions. Whether this change in consumer buying habits is sustainable has beenthe discussion of recent articles (NYTimes; Marketplace). For the following exercise, you will be extracting monthly stock data from Yahoo Finance for each of the three companies listed above, as well as the market proxy, S&P 500 index (ticker: GSPC). For each of the provided tickers (three firms and the S&P500 index), use the Yahoo Finance search bar to pull up the firm/index summary page. Navigate to the Historical Data menu and download monthly historical prices for October 27, 2015 October 27, 2020." Combine each of your downloaded files into a single spreadsheet. You will need to keep the Date and Adjusted Closing Price of each stock. 1 Spreadsheet 1. Calculate the monthly returns on each security using the Adjusted Closing Price. 2. Create a scatter plot. Under the Insert menu, navigate to Charts and select the Scatter Plot. To plot a characteristic line for an asset, first highlight the cells containing the Market portfolio returns (S&P 500 index) and the cells containing the Asset returns (one for each firm ticker), and then under the Insert and Charts select "Scatter and Scatter with Markers." Once the graph appears you can move it, format the axis scales and titles, and remove the legend and gridlines. 3. The resulting chart should display a scatterplot of the observed monthly returns. Right click on an individual data point in the chart and select Add Trendline. Use the Format Trendline menu or right click on the trendline itself. Ensure that the trendline is set to be linear (this should be the default setting). Select the option to Display Equation on Chart. 4. Repeat steps 2-3 for each firm. (You should end up with 3 series/charts with three individual trendlines.) 2 Questions 1. Based on the trendline equation displayed on each chart, what is the B for eachfirm? 2. Rank the firms in order of responsiveness to the market (highest to lowest). 1 3. If you held an equally weighted portfolio of these three stocks, what would the overall portfolio beta be? 4. Use the average monthly return over the past 60 months to forecast an annual expected market return for the next year. The 10-year Treasury bond has a 0.79% yield on 10/27/2020. If we assume this is the risk-free rate, what is the expected return on each asset? 5. If you held an equally weighted portfolio of these three stocks, what would be the overall expected return be? 6. Your roommate has recently taken up day-trading on Robinhood. After reading articles and various Reddit posts, your roommate decides to reallocate their entire stock portfolio from a diversified S&P500 index fund to these three companies to take advantage of the change in consumer buying habits. What advice might you give to your roommate? Discuss the shift in the portfolio risk in terms of its two erlying types of total risk