please do full thing in excel and make doc accessible!

Safari File Edit View History Bookmarks Window Help C $ 7% Tue 3:56 PM Q DE . . . A compass2g.illinois.edu C O CAPM Stock Valuation.. https://compass2g.illin... Homework Help - Q&A.. ARTH 403 | Course Exp.. My Audit - Audit Result... Smithsonian Dashboard. Photo Contest | Dashb... Chegg Internships + Fin 221 Fall 2019 CAPM Analysis and Stock Valuation Compass Submission Due by 11:59 PM, Friday, Nov. 8. Please use Excel cell formulas and functions to compute answers to these questions in your spreadsheet in order to receive credit. Please type the names of everyone in your group at the top of the first page of your workbook. You should use Excel to do your work for this project. Refer to the Excel Projects Guidelines file in the Group Corner folder. CAPM & Stock Valuation Project The estimation of company betas is normally brushed over in many introductory finance texts. This often leads students to view beta as some magical number and not until later courses in finance that they realize that the choice of different returns, indices and intervals examined can deliver different beta estimates. This internet exercise takes you through the basic calculation of beta using Excel in order to help demystify a company beta. Along the way you will find how to download share price and index data of the internet which can then be manipulated in Excel. Finally, you will tie the concept of beta and the CAPM to stock valuation. The Yahoo Finance site has a wealth of financial information on companies and countries from all over the world. Once you arrive at this site you will find a box titled "Get Quotes" which you need to type in the stock symbol (if you are unsure of the stock symbol go to "Symbol Lookup"). To download financial data from the internet follow the detailed instructions in the text box below: Instructions for Downloading Stock Data . In the Get Quotes box type in the stock symbol (for example Verizon is VZ and the S&P500 index is ^GSPC) and click the GO box. Click on Historical Data from the Quotes section under the more on VZ (for example) column. Click on the Time Period in the Set Date Range (then and enter the date range as 680 29 A PsSafari File Edit View History Bookmarks Window Help C $ 7%% Tue 3:57 PM QE . . . A compass2g.illinois.edu C Michelson Found Animals CAPM Stock Valuation.. https://compass2g.illin... Homework Help - Q&A.. ARTH 403 | Course Exp.. My Audit - Audit Result... Smithsonian Dashboar 13 Halloween Pet Safety Tips Pet costumes, treats and tech, oh my! View Online 1... instructed below and click Done button, Show: Historical Prices, set Frequency to Monthly, and click Apply. The first page of data should now be visible on the screen. Click the Download Data link. Save the data to a file, it will be saved as a CSV file. Open Excel and then open the file you saved. At this point you should have seven columns including the; Date, Open, High, Low and Closing share prices for that month, plus Volume and Adj Close data for each month You can only need the Date, Open and Close columns for each stock or index. To download dividend payments change the Show setting to Dividends Only, click Apply and export and save this info to a spreadsheet. You will need to do this for any of your stocks that have paid dividends over the time period below. Alternatively, you can just enter the dividends for each stock into your spreadsheet from the Yahoo Finance price table itself. The S&P 500 Index already includes dividends. Note: the dividends only esv/excel export file often is out of chronological order, you may want to sort the dividend data by date before entering dividends in the appropriate month rows as your stock's price data. 1. First download into Excel the Best Buy (BBY), Activision-Blizzard (ATVI), and S&P 500 Stock Index (^GSPC) monthly price data for the 5-year period from the Oct. 1, 2014 to Sept. 30, 2019 using the instructions outlined above. Using the Open and Close price data for each month (the date given is the first trading day for that month; i.e. the open and close for the month) to calculate the monthly percentage holding period returns for each stock and index where each month's Open price is the beginning of the month price and each month's Close price is the end of the month price. You can get rid of the other data provided for each month. In addition, you will need to include dividend payments for each stock to calculate monthly returns for the months that they made a dividend payment (the index doesn't have dividend payments). For example, September's monthly return is (Sept Close - Sept. Open + any Sept Div)/Sept Open. You should have 60 monthly returns for each stock and index from Oct. 2014 to Sept. 2019. Helpful Hint: I strongly urge creating a table of the appropriate opening & closing prices and dividends for each stock along with opening and closing prices for the S&P 500 Index on a separate worksheet and then do your calculations on this "fresh" worksheet. (6 pts) 29 ... Lr PsSafari File Edit View History Bookmarks Window Help C $ 7% Tue 3:57 PM QE . . . A compass2g.illinois.edu C O CAPM Stock Valuation.. https://compass2g.illin... Homework Help - Q&A.. ARTH 403 | Course Exp... My Audit - Audit Result... Smithsonian Dashboard. Photo Contest | Dashb... Chegg Internships + 2. What is the average monthly return for each stock and the S&P 500 index over the entire time period? Convert this average monthly return for each stock and index into an APR (annualized rate) by multiplying your monthly average for each stock and index by 12. Please convert your answers to percentages (4 pts) 3. Calculate the (population) standard deviation of monthly returns for each stock and the index using Excel. (2 pts) 4. Now calculate the beta for each stock using Excel's Slope or Linest function to use estimate the slope of the linear regression line, which is beta. Use the S&P 500 index as the market portfolio in finding beta. (4 pts) 5. Now use the CAPM/SML Equation to estimate each stock's required return. Use 10% as the required market return and the recent 1-year Treasury rate of 1.7% as the risk- free rate. Would you recommend buying BEST BUY and/or Activision if your APR average for each stock from #2 is your expected return? Explain your answer. (5 pts) 6. Construct a graph of the Security Market Line given by the parameters in #5. Point out the SML returns you calculated in #5 on your SML. Also, find and plot BEST BUY's and Activision's expected returns from the information given below gathered from their recent Yahoo Finance stock quotes against each stock's beta on your SML graph. Would you recommend buying BEST BUY and/or Activision based on this information? Explain your answers. Note: you are evaluating each stock individually, not in comparison in one another. (7 pts) Stock Best Buy Activision Current Price $68.40 $55.40 Estimated Price 1 year from now | $75.89 $57.52 Annual Expected Dividend (D1) $2.00 $0.37 7. Let's assume Best Buy is a variable growth stock with an expected annual growth rate in dividends of 10% in year 2, 14% in years 3 & 4, and 13.5% in year 5 and a constant annual growth rate of 8% after year 5. What is your valuation of Best Buy's stock today using the variable growth dividend discount model? Would you recommend buying BEST BUY's stock today and why? Use current price in #6 to help aid your recommendation. (5 pts) 29 AS Lr Ps Safari File Edit View History Bookmarks Window Help C $ 7% Tue 3:57 PM QE . . . A compass2g.illinois.edu C O CAPM Stock Valuation.. https://compass2g.illin... Homework Help - Q&A.. ARTH 403 | Course Exp.. My Audit - Audit Result... Smithsonian Dashboar Photo Contest | Dashb... Chegg Internships + 8. Now, assume that Best Buy's current price is in equilibrium and is a constant growth stock. What is Best Buy's expected constant growth rate based on your CAPM return? Use price and dividend data from #6. (2 pts) 9. Use the Corporate Valuation model is the most appropriate model to value Activision Here's Free Cash Flow projections (in millions of $) needed to value Activision as a firm. Year FCF 2071 2300 2 2576 After year 3, Activision's free cash flow is expected to grow at a 4% constant growth rate in year 4 and beyond. Activision's WACC is 8%. Activision has $2,850 million in debt (market value of debt) and 767 million shares of common stock outstanding? What is your valuation of Activision's common stock today? Would you recommend buying Activision's stock today and why? Use current price in #6 to help aid your recommendation. Should Prof. Dyer hold on to his 100 shares that he bought a few years ago for $21/share? @ (5 pts) 29 Lr Ps CJ