Can somone please help me solve all of these and show the workthrough for them. I need to workthrough on excel and the answers
please. thank you!
C Problem 7-20 Assume Highline Company has just paid an annual dividend of $0.96. Analysts are predicting an 11% per year growth rate in earnings over the next five years. After then, Highline's earnings are expected to grow at the current industry average of 5.2% per year. If Highline's equity cost of capital is 8.5% per year and its dividend payout ratio remains constant, for what price does the dividend-discount model predict Highline stock should sell? Complete the steps below asing cell references to ghen denta or previon calendations. 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 mbed cell reference may be preferred. Ifa specific Ercal function is to de used, the directions will specify the use of that fimction Do not type in merical data into a cell or fonction. 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 ace the earliest appearance of the denta in your formulas, sually the Given Data section $ Dividend Growth rate (1) Growth period (1) Growth rate (2) Equity cost of capital 0.96 1195 5 5.295 8.596 Dividend next year Dividend in 6 years PV first 5 dividends PV remaining dividends Stock price Requirements 1 In cell D12 by using cell references, calculate the dividend that the company will pay next year (pt.) 2 In cell D13, by using cell references, calculate the dividend that the company will pay in sex years al pt.) 3 In cell D14, by using cell references, write an expression to calculate the present value of the first five dividends (years 1-5) pt.) Note. Use the Present Value of a Growing Annuity equation. The output of the expression or function you typed in this cell is expected as a positive number 4 In cell D15, by using cell references, write an expression to calculate the present value of all remaining dividends (1 pt.). Note: Combine the Present Value and Present Value of a Growing Perpetuity equations. The output of the expression or function you typed in this cell is expected as a positive number 5 In cell D16, by using cell references, calculate the price of the stock today I p.) 7-20 F20 D C Complete the steps below using cell references to given data or previous calculations. In some cases, 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 mused, the directions will aperify 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 celle highlighted below. In all cases, unlesn otherwine directed, use the earliest appearance of the data in your formulas, usually the Given Data section. You have 3 projects with the following cash flows: Year Project 1 Project 2 Project 3 0 $150 - $825 $20 1 $20 $0 $40 2 $40 $0 $60 3 $60 $7,000 $80 4 $80 $6,500 $245 11 13 14 a. For which of these projects is the IRR rule reliable? b. Estimate the IRR for each project to the nearest 1%) e. What is the NPV of each project if the cost of capital is 5%? 20% 50%? . For which of these projects is the IRR rule reliable? The IRR rule is reliable for b. Estimate the IRR for each project to the nearest 14 AR Project 1 Project 2 Project 3 BN What is the NPV of each project if the cost of capital is 5%20% 50%? 20 5% 20% 50% Project 1 Project 2 Project 3 30 31 32 Requirements 1 Start Excel - completed 06 2. In cell 15 select from the dropdown for which of the projects the frie is reliable 11 3. In cell D20, by usng cell references and the function IRR. calculate the internal rate of 33 return for Project 1 (1 pt.) 36 Note: Do not enter any value for the Caessargument of the function IRR at 4. To calculate the IRR for projects 2 and 3. copy cell D20 and paste it onto cells D21 D22 (1 5. In cell 027 by using cell references and the function NPV. calculate the net present value of Project if the cost of capital is 5 pt.) 6. To calculate the nel preter values of projects 2 and 3 if the cost of capital is 5%, copy cell D27 and paste it onto cells 28.029 To calculate the net present values of each project of the cost of capital is 20% od 50% respectively, copy cell D27 and paste it onto cells E271F29 a pt.) 7. Save the workbook Close the workbook and then exit Excel. Submit the workbook as 8-20 Ready 36 za Prekler 8-13 Beyt's fond Tecla 0.000 per you do it is in purchase and content Panchas y for $150,000. The wire 530.000 per Ich w for $250.000 Thishe w115.000 per year in reprises and will bottle cut by 510,000 per year. Alle 335.000 spent updates of the Suppose la propriate contpeyre hand day Maintenance and compaidat is the real of the machen wird die vyand that they halo your life with The corporate me 20% heild Beyond Tact.potheses com purchase the machine Complete powerpol Tec formulari awal law wol row with Dellorto videti free porn wie in de wereld , M Distate Texte life) Veure Y Year Year! 550,000 Yra 5.000 Yes SS Year -357 M Rel TCT NDV Ver 550.000 150.000 Ver 380,000 Year 10 550.000 . 53,000 330.000 50.000 $20.00 SED 10.000 520000 SNO 300.000 110,000 HCM Van Deco Ciphalted FCF photo 315.000 110.000 315.000 10.000 38.000 SIC SH000 10.000 S000 31,00 315.000 110100 SS. STO PAM M Owls De Cialigratita TC (od S. 50,000 S10 11000 350,000 Barys o 9-18 Ready Accessibility: Investigate D E G H 3 4 5 Requirements 1 In cell E15, by using cell references, calculate the free cash flow for year 1 in case of renting the machine (1 pt.). 2 To calculate the free cash flows for years 2 through 10 in case of renting the machine, copy cell E15 and paste it onto cells F15:N15 (1 pt.) In cell 016, by using cell references and the function NPV, calculate the net present value of renting the machine (1 pr.). In cell E20, by using cell references, calculate the depreciation of the current machine for year 1 (1 pr.). To calculate the depreciation of the current machine for years 2 through 7. copy cell E20 and paste it onto cells F20:K20 (1 pt.) 6 In cell D22, by using a cell reference calculate the free cash flow for year 0 in case of purchasing the current machine (1 pl.) 7 In cell E22, by using cell references, calculate the free cash flow for year 1 in case of purchasing the current machine (1 pt.) 8 To calculate the free cash flows for years 2 through 10 in case of purchasing the current machine, copy cell E22 and paste it onto cells F22:N22 (1 pt.). In cell 123. by using cell references and the function NPV, calculate the net present value of purchasing the current machine (1 pt.) 10 In cell E28, by using cell references, calculate the depreciation of the advanced machine for year 1 (1 pt.). 11 To calculate the depreciation of the advanced machine for years 2 through 7. copy cell E28 and paste it onto cells F28:K28 (1 pt.) 12 In cell D30, by using cell references, calculate the free cash flow for year in case of purchasing the advanced machine (1 pt.) 13 To calculate the free cash flows for years 2 through 10 in case of purchasing the advanced machine, copy cell D30 and paste it onto cells E30:N30 (1 pt.). 14 In cell D31, by using cell references and the function NPV, calculate the net present value of purchasing the advanced machine (1 pt.). 15 In cell D33, type whether Beryl's leed Tea should continue to rent, purchase the current machine, or purchase the advanced machine. (1 pr.) 45 9 46 47 AB 50 51 52 53 54 35 56 . 57 9-18
