123 A B Problem 13-2 C D E G H J Complete the steps below using cell references to given data or previous calculations. In some cases, a simple cell reference is all you need. To copy/paste a formula across a row or down a column, an absolute cell reference or a mixed cell reference may be preferred. If a specific Excel function is to be used, the directions will specify the use of that function. Do not type in numerical data into a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the blue cells highlighted below. In all cases, unless otherwise directed, use the earliest appearance of the data in your formulas, usually the Given Data section. Assume that the CAPM is a good description of stock price returns. The market expected return is 7% with 10% volatility and the risk-free rate is 3%. News arrives that does not change any of these numbers but it does change the expected return of the following stocks: 11 456789222319922222222222222-330 Expected Return Volatility Beta Green Leaf 12% 20% 1.5 NatSam 10% 40% 1.8 HanBel 9% 30% 0.75 Rebecca Automobile 6% 35% 1.2 a. At current market prices, which stocks represent buying opportunities? b. On which stocks should you put a sell order in? Market expected return Market volatility Risk-free rate 7% 10% 3% a. At current market prices, which stocks represent buying opportunities? Green Leaf NatSam HanBel Expected return Alpha Buying opportunity? Rebecca Automobile b. On which stocks should you put a sell order in? Green Leaf NatSam HanBel Rebecca Automobile Sell order? 37 38 Requirements 39 40 40 41 22 42 43 44 45 46 48 47 1. Start Excel - completed. 2. In cell D24, by using cell references, calculate the expected return for Green Leaf based on the market data and the stock's beta (1 pt.). 3. To calculate the expected returns for NatSam, HanBel, and Rebecca Automobile, copy cell D24 and paste it onto cells D25:D27 (1 pt.). 4. In cell E24, by using cell references, calculate the alpha for Green Leaf by finding the difference between the expected return based on the market data and the expected return based on the recent news. (1 pt.). 5. To calculate the alphas for NatSam, HanBel, and Rebecca Automobile, copy cell E24 and paste it onto cells E25:E27 (1 pt.). 6. In cell F24, by using cell references and the function IF, determine whether the stock of Green Leaf represents a buying opportunity. If the alpha for Green Leaf is greater than zero then "Yes", else "No" (1 pt.). Copy cell F24 and paste it onto cells F25:F27 (1 pt.). 7. In cell D32, by using cell references and the function IF, determine whether the stock of Green Leaf should be sold. If the alpha for Green Leaf is lower than zero then "Yes", else "No" (1 pt.). Copy cell D32 and paste it onto cells D33:D35 (1 pt.). 8. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.