Using the Yahoo! Finance Web site (finance.yahoo.com) get the current price and five-year dividend history for Eli Lilly & Co. To gather this data, enter the ticker symbol (LLY) in the Get Quotes box at the top of the page, and click the GO button. Record the current price from this page. Now, at the left side of the quote page, click on the Historical Prices link. To get a table of previous dividends, select Dividends Only at the top of the table, set the Start Date to five years before today's date, and click the Get Prices button. Click the Download to Spreadsheet link at the bottom of the table to download a file with this data. You may have the choice of either saving the file or opening it directly in Excel. It is easier to let it open in Excel. Otherwise, save the .csv (comma separated variables) file, and then open it with Excel. It shouldn't need any further processing other than some formatting. You now have the dividends in a worksheet.

a. Because LLY pays dividends quarterly, calculate the quarterly percentage change in the dividends. Now, calculate the compound quarterly growth rate of the dividends using the GEOMEAN function.

b. Now, annualize the quarterly dividend growth rate.

c. Calculate the intrinsic value of the stock using a 10% required rate of return and the calculated annual growth rate. Use the sum of the most recent four dividends as D0.

d. How does the calculated intrinsic value compare to the actual market price of the stock? Use an IF statement to display whether the stock is undervalued, overvalued, or fairly valued. Would you buy the stock at its current price?