Assume someone you know and care about came up to you with an idea to purchase some stock in a company, and they want your advice. Pick a publicly traded company that has paid dividends for at least five years, come up with a value estimate for the company’s stock, and offer a recommendation on whether someone should buy, hold or sell shares in this company. Refer to my Starbucks example in the Financial Planning and Forecasting lecture for as a guide. In order to estimate the company’s value, please compute the following metrics based on at least five years of company data: Retention Ratio – the bare minimum would be to simply use a five year average. If there seems to be a trend, feel free to project the trend into the future. Use one retention ratio for the calculation of internal and sustainable growth rates. ROA & ROE – again, feel free to use an average or project future amounts based on the recent trend. Use on ROA and one ROE figure for the internal and sustainable growth rates. Using these three variables and any other data you find relevant, calculate the internal and sustainable growth rates to use for dividend forecasts. You may pick one or the other or use a growth rate somewhere in between; discuss briefly (even as little as one sentence) why you chose the dividend growth rate you did. Apply this dividend growth rate to forecast six years of dividends (the sixth year is used to calculate terminal value). Estimate a terminal growth rate (I suggest average revenue growth for the company or simply a 3% growth rate to mimic long-run economic growth), and use present value calculations with a 10% discount rate to compute the present value of future dividends. Further, try to find a discount rate that would make the present value of the projected dividends equal the company’s current share price. As I did in my Starbucks example, calculate investments and debt per share and apply those to determine a final stock price valuation. Please turn in an Excel file and a Word document. The Excel file should show your work so I can award partial credit if need be. The Word document should be no more than a page and include your recommendation along with key assumptions you made. Email me with any questions you have about including non-recurring income or any other financial statement considerations.