Please help me to do this excel assignment follow instructions in word document

Valuing a Constant Growth Stock D1 P0 (using formula) rs P0 (summing PVs) g Year 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 Dividend PV of Dividend 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 Problem 10-18: WACC and Optimal Capital Budget Interest rate (rd) Tax rate (T) Preferred dividend (Dp) Capital component Long-term debt Preferred stock Weight Cost Common equity Preferred stock price (Pp) WACC Common stock price (P0) Next dividend (D1) Growth rate (g) Project 1 2 3 4 Return Accept? Chapter 11 Problem ST-2: Capital Budgeting Criteria WACC Year 0 1 2 3 4 NPV IRR Project X Project Y WACC 0% 5% 10% 15% 20% NPV(X) NPV(Y) Download the Excel workbook which contains the template for the assignment. Note that there are three worksheets - one for each problem below. Complete the empty cells in accordance with the instructions below. Part 1 (20 points) Consider a stock that is expected to pay a dividend (D 1) of $2.00 one year from now, assuming the dividend grows at a constant annual rate of 3% and the stock's required return is 10%. Enter the appropriate values in cells B3 through B5. Then calculate (or estimate) the stock's value two different ways. Method 1: In cell E3, enter an appropriate Excel formula that computes the stock price via the constant growth model. Method 2: Forecast the stock's expected future dividends, and then find the sum of their present values. Technically, you would have to do this for an infinite number of years, but we will use the spreadsheet to forecast dividends for 100 years, which is sufficient for practical purposes. In cell B8, enter a reference to cell B3 (the value of D 1). In cell B9, enter a formula that calculates D 2 from the previous dividend and the annual growth rate (g). Use fixed and relative cell references judiciously so that you can easily copy the formula from cell B9 down to the remaining cells (B10 through B107). In cell C8, write an Excel formula that calculates the present value of the dividend in cell B8, taking into account the required return and the year in which the dividend is paid. Again, use fixed and relative cell references judiciously, and then copy your formula from C8 all the way down to the bottom of the table (cells C9 through C107). In cell E4, enter an Excel formula that estimates the stock price by summing the present values of all expected future dividends. (Hint: Your estimate should be just slightly less than the exact price computed in E3.) Part 2 (20 points) Adams Corporation is considering four average-risk projects with the following costs and rates of return Project (1)---Cost $2,000, Expected Rate of Return--16.00% Project (2)--Cost $3,000, Expected Rate of Return--15.00% Project (3)---Cost--$5,000, Expected Rate of Return--13.75% Project (4)--Cost $2,000, Expected Rate of Return--12.50% The company estimates that it can issue debt at a rate of rd = 10%, and its tax rate is 30%. It can issue preferred stock that pays a constant dividend of $3 per year at $40 per share. Also, its common stock currently sells for $30 per share, the next expected dividend, D1, is $3.25, and the dividend is expected to grow at a constant rate of 4% per year. The target capital structure consists of 75% common stock, 15% debt, and 10% preferred stock. a. What is the cost of each of the capital components? b. WACC? c. Only projects with expected returns that exceed WACC will be accepted. Which projects should Adamson accept? Fill in cells B3 through B11 with the appropriate values from the problem. 1) Fill in cells E4 through E6 with the capital structure weights stated in the problem. 2) In cells F4 through F6, enter the appropriate formulas to compute the after-tax cost of debt [r d(1T)], the cost of preferred stock (rp), and the cost of common equity (rs). 3) Enter the appropriate formula in cell F8 to compute the WACC. For full credit, use Excel's SUMPRODUCT function. 4) In cells E11 through E14, enter the expected rates of return for each project. 5) In cells F11 through F14, use Excel's IF function to check whether the project's return exceeds the WACC, and then display either \"Yes\" or \"No\" to indicate whether the project should be accepted. The general format for this function is as follows: =IF( condition to check , value to display if true , value to display if false ) When writing this formula, you'll want to use a fixed cell reference (i.e. with dollar signs) for the WACC so that you can write the formula once in cell F11 and then easily copy it down to the remaining cells. You'll also need to actually put the words \"Yes\" and \"No\" in quotation marks since they are not numerical values. As always, use cell references whenever possible. Do not type numerical values directly into any of the formulas in column F. Part 3 (20 points) Project L costs $65,000, its expected cash inflows are $12,000 per year for 9 years, and its WACC is 9% 1) Fill in cell B3 with the WACC given in the problem. 2) Enter the Project X and Y cash flows for Years 0-4 into the table. 3) In cells B12 and C12, use Excel's NPV function to compute the NPV of each project. The general format for this is as follows: = CF0 + NPV( rate , CF1:CFn ) 4) In cells B13 and C13, use Excel's IRR function to compute the IRR of each project. The general format for this is as follows: =IRR( CF0:CFn ) As you may notice, when calculating NPV, the initial cash flow (Year 0) must be specified separately. Within the NPV function itself, you should highlight only CF1 through CFn (Year 1 through the end). However, when calculating IRR, the Year 0 cash flow isn't handled separately. You simply highlight it along with all the others. As always, use cell references whenever possible. No numerical values should be typed directly into rows 12 and 13. Now complete a data table that will show the NPV of each project at different possible values for WACC. 5) In cells F6 and G6, enter cell references to the calculated NPVs in cells B12 and C12, respectively. I have the font color set to a light gray because the values in F6 and G6 are not exactly part of the data table, even though they are necessary for it to work. 6) Highlight the range of cells from E6 down through G11. Select \"Data\" from the menu bar, then \"What-If Analysis\" and \"Data Table\". In the box that subsequently pops up, you want to tell Excel to take each value in Column E, plug it in for WACC in cell B3, and display the NPV values that would result in each case. Therefore, you should specify B3 as the \"Column input cell\". Since there are no input variables listed horizontally in your data table, you will leave \"Row input cell\" blank. Click \"OK\". (Note: Once a data table has been created, you cannot directly modify values or formulas within it. If you make a mistake and need to change something, you will need to delete the entire portion of the table Excel has computed - in this case, cells F7 through G11 - and then try again.)