Introduction to Finance FINC 331 Mini Case 4 (Due Thursday, 11/30/2017, Midnight) Please answer the following questions using Excel and submit your assignment via Blackboard. You should work with your group on the case assignments (please write the names of all group members). This is your first week at a prestigious brokerage firm as an equity analyst. Your boss asked you to estimate the expected return of the following stocks Exxon Mobil Corporation (XOM) and The Goldman Sachs Group, Inc. (GS). 1) Download the adjusted stock prices for XOM and GS for the last 5 years (10/2012- 10/2017) from Yahoo-finance (http://finance.yahoo.com/). a. Enter the ticker symbol XOM and then click on Historical Data. b. Set the date range (10/3/2012, 10/3/2017). Select historical prices, set frequency to monthly, and then click on apply. To download the data to a spreadsheet click on Download Data link on top right corner of the table. c. Delete the columns Open, High, Low, Close and Volume. You will need Date and the Adj Close columns. The latter one is the close price adjusted for dividends and splits. d. Repeat the steps a and b for the Goldman Sachs Group, Inc. (GS) and S&P 500 (^GSPC). e. Copy and paste these four columns (date and adj close for GS and S&P 500) next to adj close for XOM. f. After making sure that dates match for XOM, GS and S&P 500 delete the two of the date columns. After this step you should have Date as column A, Adj Close for XOM, GS, and S&P 500 as columns B, C, and D, respectively. g. Compute the monthly returns for XOM, GS and S&P 500. (eg. For 2012 April return of XOM you should use the following equation: =B3/B2-1 (end of month adj close/ beginning of month adj close -1). h. Drag this formula to the rest of the observations till 10/3/2017 (row 61). i.Copy and paste risk free rates (available on Blackboard). Make sure that dates(month) match. j. Repeat the same procedure (h and i) for GS and S&P 500. k. Compute risk premiums (ret-rf) for each month for XOM, GS, S& 500. l. To estimate beta for XOM, use slope function (=slope( , )). Select risk premium for XOM as y, and risk premium for S&P 500 as x. m. Repeat m for GS. 2) What is the beta of XOM? What is the beta of GS? 3) Using the beta estimates and a market risk premium of 6% and a risk free rate of 1% compute expected returns for GS and XOM. 4) Which one is aggressive stock? Which one is defensive stock? Why?