A1 fo D B Spreadsheet Exercise: Problem 8.26 2 Create a spreadsheet that applies the variable-growth model to predict the intrinsie value of the Rhyhom Company common stock. Assume that dividends will grow at a variable rate for the next three years (2019, 2020, and 2021). After that, the amal rate of growth in dividends is expected to be 7% and stay there for the foreseeable future. Starting with the latest (2018) anal dividend of $2.00 per share, Rhybom's earnings and dividends are estimated to grow by 18% in 2019, by 14% in 2020, and by 9% in 2021 before dropping to a 7% rate. Given the risk profile of the firm, assume a minimum required tate of retum of Mleast 12% Complete the steps below using cell references to give 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 color, an absolute cell reference or a maed call reference may be preferred. If a specific Excel function is to be used the directions will specife thee of that function. Do not type in numerical data into a cell or function instead male a reference to the cell in which the data is found. Make your computations only be the bine cells highlighted below. In all cases, les otherwise directed, use the earliest appearance of the deata in your formulas, urually the Gun Data section To Do a. Calculate the projected to dividends over the years 2019, 2020 and 2021 b. Determine the present value of dividends during the initial variable growth period c. What do you believe the price of Rlybom stock will be at the end of the initial growth period (20219? . Having determined the expected future price of Rhytom stock in parte, discount the price of the stock back to its present value t. Determine the total intrinsie value of Rhyhom stock based on your previous calculations 19 13 12 Solution a. Cere the projected mal dividends over the years 2019 2020 and 2021 15 Year 2018 Growth Rate Dividends 1 = = Merge & Center $ -% - Clipboard Font For Alignment Number B F e. Determine the total intrinsic value of Rhyhom stock based on your previous calculations. G Solution 4 5 a. Calculate the projected annual dividends over the years 2019, 2020, and 2021. Growth Rate 6 17 18 Dividends $2.00 Year 2018 2019 2020 2021 19 1896 14% 996 21 22 b. Determine the present value (PV) of dividends during the initial variable-growth period. 23 + Rate of return 24 25 1296 26 Year PV of future dividends Present Value 27 28 c. What do you believe the price of Rhyhom stock will be at the end of the initial growth period (2021)? 30 11 32 Growth rate after 2021 Stock value at the end of 2021 79 31 14 d. Having determined the expected future price of Rhyhom stock in parte discount the price of the stock back to its present value. 15 10 17 PV of future stock value e. Determine the total intrinsie value of Rhyhom stock based on your previous calendar Format Painter BIU - OA lol Merge & Center $ % 8-9 Conditional FC Formatting Clipboard Font Alignment Number SEY fx B D G d. Having determined the expected future price of Rhyhom stock in part c, discount the price of the stock back to its present value. PV of future stock vahe e. Determine the total intrinsic value of Rhyhom stock based on your previous calculations. Intrinsic value of stock Points Points 1 1 16 1 1 Requirements 4 1 In cell E18, by using cell references, calculate the annual dividend in 2019. 15 2 To calculate the anual dividends in 2020 and 2021. copy cell E18 and paste it onto cells E 19:E20. 3 In cell D27, by using cell references and the function NPV. calculate the present value of the future dividends. 4 in cell D32, by using cell references, calculate the value of Rhyhom stock at the end of 2021. 5 In cell D36, by using cell references and the function PV, calculate the present value of the future price of Rhyhorn stock in Note: The output of the expression or function you typed in this cell is expected as a positive number 6 In cell D40, by using cell references, calculate the total intrinsic value of Rhyhom stock Note: Refer to the values from Steps 3 and 5 in your calculations. 52 7 Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed 68 patc 1 49 1 1 0 53 54 55 58 57 58 3