Ten years ago, in 2010, George Reeby founded a small mail order company selling high quality sports equipment. Since those early days Reeby Sports has grown steadily and been consistently profitable. The company has issued 2 million shares, all of which are owned by George Reeby and his five children. For some months George has been wondering whether the time has come to take the company public. This would allow him to cash in on part of his investment and would make it easier for the firm to raise capital should it wish to expand in the future. But how much are the shares worth? George's first instinct is to look at the firm's balance sheet, which shows that the book value of the equity is $26.34 million, or $13.17 per share. A share price of $13.17 would put the stock on a P/E ratio of 6.6. That is quite a bit lower than the 13.1 P/E ratio of Reeby's larger rival, Molly Sports. George suspects that book value is not necessarily a good guide to a share's market value. He thinks of his daughter Jenny, who works in an investment bank. She would undoubtedly know what the shares are worth. He decides to phone her after she finishes work that evening at 9 o'clock or before she starts the next day at 6.00 a.m. Before phoning, George jots down some basic data on the company's profitability. After recovering from its early losses, the company has earned a return that is higher than its estimated 10% cost of capital. George is fairly confident that the company could continue to grow fairly steadily for the next six to eight years. In fact, he feels that the company's growth has been somewhat held back in the last few years by the demands from two of the children for the company to make large dividend payments. Perhaps, if the company went public, it could hold back on dividends and plow more money back into the business. There are some clouds on the horizon. Competition is increasing and only that morning Molly Sports announced plans to form a mail order division. George is worried that beyond the next six or so years it might become difficult to find worthwhile investment opportunities. There are some clouds on the horizon. Competition is increasing and only that morning Molly Sports announced plans to form a mail order division. George is worried that beyond the next six or so years it might become difficult to find worthwhile investment opportunities George realizes that Jenny will need to know much more about the prospects for the business before she can put a final figure on the value of Reeby Sports, but he hopes that the information is sufficient for her to give preliminary indication of the value of the shares. 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020E Earnings per share ($) Dividend ($) - 2.10 0.00 9.80 -27.10 -0.70 0.23 0.81 1.10 1.30 0.00 0.00 0.20 0.20 0.30 7.70 7.00 7.618.51 9.51 -7.1 3.0 11.6 14.5 15.3 1.52 0.30 10.73 16.0 164 0.60 11.77 15.3 2.00 0.60 13.17 2.03 0.80 14.40 Book value per share ($) ROE (%) 17.0 15.4 Earnings per share (S) Dividend ($) 2011 -2.10 0.00 9.80 -27.10 2012 2013 2014 2015 2016 0.70 0.23 0.81 1.10 1.30 0.00 0.00 0.20 0.20 0.30 7.70 7.00 7.61 8.51 9.51 -7. 1 3.0 11.6 14.5 15.3 2017 1.52 0.30 10.73 16.0 2018 .64 0.60 1177 15.3 2019 2.00 0.60 13.17 17.0 2020 2.03 0.80 14.40 15.4 Book value per share (S) ROE (%) Questions 1) Help Jenny to forecast dividend payments for Reeby Sports and to estimate the value of the stock. You do not need to provide a single figure. For example, you may wish to calculate two figures, one on the assumption that the opportunity for further profitable investment disappears after six years and another assuming it disappears after eight years. 2) How much of your estimate of the value of Reeby's stock comes from the present value of growth opportunities? In the forecasting you can use the ratio that you want. I mean, make the assumptions by yourself. Also I need solutions in excel. Can you put the screenshots of your excel solutions. Additional info: Please construct your excel spreadsheet solutions starting from year 2014. And also I need the formulas. Thank you