Corporate Finance X1 Case 1 Assignment Due April 16, 2021

Please indicate the group and the group members.

1. 
   Collect the dividend information for each stock from finance.yahoo.com.
   

1. 
   Enter the stock symbol. On the page for that stock, click “Historical Data” on the top of the page.
   
2. 
   For the Time Period, enter the Start Date: 01/01/2011, and the End Date: 12/31/2020. This covers the recent 10-year period. Choose Dividends Only, Monthly, then press Apply, and Download.
   
3. 
   At the bottom of the page, click on the file name xxx.csv, where xxx refers to the stock ticker and the file is a CSV (comma-separated) file.
   
4. 
   The file consists of 2 columns: the date and the dividend amount paid. If the company pays quarterly dividends you should get 40 quarterly dividends, approximately. The last dividend should not be later than December 2020. If you get less than 10 years of historical data, use the longest available period but not less than 5 years.
   
5. 
   If there are special dividends, exclude them. Generally, these dividends are non-recurring, i.e., they occur outside the normal dividend cycle, and generally have larger amounts.
   
6. 
   If the dates are not in chronological order, sort the data.
   

2. 
   Calculate the annual dividends. This is done by adding all the dividends paid during the year. Exclude any special dividends.
   

3. 
   Create a bar chart of the annual dividends. Overlay the estimated trend line (linear) on the chart. Add the titles on the chart (x-axis, y-axis, and chart titles).
   

4. 
   Estimate the dividend for the year 2021. To estimate this, use a method that you think makes sense. One simple method is to extrapolate the historical dividends using regression. This assumes that the recent trend would continue for the following year. Another method is to use some third party estimate, for example, analyst reports from a brokerage company or an investment bank. If you use this method, please cite your reference. If you are creative, you may use your own estimation method. Denote your estimate by DIV₂₀₂₁.
   

5. 
   The price of the stock is given by the formula below. This formula shows that the price of a stock can be estimated as the present value of a growing perpetuity, where the dividends grow at a constant rate g percent per year forever and the cost of capital is k percent.
   

Using your dividend estimate from part (4) and the stock price formula above, calculate the price of the stock with different combinations of k and g. Do this by creating a table, see below, where k runs vertically from 2% to 20% in increments of 2%, and values of g runs horizontally from 0% to10% in increments of 1%. Fill in the table with the stock price. In cases where g ≥ k, denote the cell by “NA”.

6. 
   Check the stock price as of 3/31/2021. What combination(s) of k and g values would give a price closest to this 3/31/2021 price? If the market price is outside the range of the calculated values, you can add more columns or rows.
   

7. 
   Get the latest estimate of k, the cost of capital or sometimes called the weighted average cost of capital (WACC). An easy way is to Google “cost of capital ticker”. It will give you an estimate from Gurufocus.com. You may try other sources.
   

8. 
   Given the actual stock price on 3/31/2021 from part (6) and the estimate of k from part (7), calculate g, the growth rate of dividends. Interpret the results you get.
   

9. 
   For the output of your analysis, use the template shown below, and the spreadsheet, “Case 1 – Coca Cola.xlsx”. (Note: this template uses a different time period).