Question

1. The table below shows the total dividends, share repurchases, and stock issues for a firm in each year from 2003-2013 (copy and paste this table into Excel) Please use excel and show all work/formulas:

Year ending	Dividends	Repurchases	Stock issues
31-Dec-03	$620,250,000	$241,500,000	$840,000
31-Dec-04	730,500,000	309,000,000	1,117,500
31-Dec-05	852,750,000	564,000,000	1,687,500
31-Dec-06	978,750,000	3,697,500,000	2,017,500
31-Dec-07	1,109,250,000	575,000,000	1,657,500
31-Dec-08	1,293,000,000	729,750,000	2,902,500
31-Dec-09	1,535,250,000	1,127,500,000	3,855,000
31-Dec-10	1,785,750,000	1,303,500,000	2,925,000
31-Dec-11	2,059,500,000	887,250,000	2,332,500
31-Dec-12	2,438,250,000	500,000,000	12,000,500
31-Dec-13	2,844,750,000	1,287,750,000	5,220,000

The current stock price for this firm is $42.15 and they have 2,919,842,000 shares outstanding. Do the following to calculate the cost of equity for this firm:

1. Calculate the total cash flow to equity for each year, and then find the year-to-year percentage change in the total cash flow to equity.
2. Using a line graph, graph dividends, repurchases, stock issues, and total cash flow to equity where year is on the x-axis and the cash flow amount is on the y-axis. This graph will give you an idea of how each cash flow to equity component has behaved over the past ten years.
3. Calculate the compound growth rate in total cash flow to equity over the past 10 years and 5 years separately.
4. Calculate the average year-to-year % change in total cash flow to equity over the past 10 years and 5 years separately.
5. Calculate the cost of equity using the Gordon model and each of the four growth rates you calculated in parts (c) and (d). You should have four cost of equities.
6. In a textbox, give a brief description of what is going on in the data for total cash flow to equity, and identify what you think is the true cost of equity for the firm and why.

Answer 1

Year ending	Dividends	Repurchases	Stock issues	total cash flow to equity
31-Dec-03	620,250,000	241,500,000	840,000	860,910,000
31-Dec-04	730,500,000	309,000,000	1,117,500	1,038,382,500
31-Dec-05	852,750,000	564,000,000	1,687,500	1,415,062,500
31-Dec-06	978,750,000	3,697,500,000	2,017,500	4,674,232,500
31-Dec-07	1,109,250,000	575,000,000	1,657,500	1,682,592,500
31-Dec-08	1,293,000,000	729,750,000	2,902,500	2,019,847,500
31-Dec-09	1,535,250,000	1,127,500,000	3,855,000	2,658,895,000
31-Dec-10	1,785,750,000	1,303,500,000	2,925,000	3,086,325,000
31-Dec-11	2,059,500,000	887,250,000	2,332,500	2,944,417,500
31-Dec-12	2,438,250,000	500,000,000	12,000,500	2,926,249,500
31-Dec-13	2,844,750,000	1,287,750,000	5,220,000	4,127,280,000

Year ending	Dividends	% change YoY	Repurchases	% change YoY	Stock issues	% change YoY	total cash flow to equity	% change YoY
31-Dec-03	620,250,000	-	241,500,000	-	840,000	-	860,910,000	-
31-Dec-04	730,500,000	17.78%	309,000,000	27.95%	1,117,500	33.04%	1,038,382,500	20.61%
31-Dec-05	852,750,000	16.74%	564,000,000	82.52%	1,687,500	51.01%	1,415,062,500	36.28%
31-Dec-06	978,750,000	14.78%	3,697,500,000	555.59%	2,017,500	19.56%	4,674,232,500	230.32%
31-Dec-07	1,109,250,000	13.33%	575,000,000	-84.45%	1,657,500	-17.84%	1,682,592,500	-64.00%
31-Dec-08	1,293,000,000	16.57%	729,750,000	26.91%	2,902,500	75.11%	2,019,847,500	20.04%
31-Dec-09	1,535,250,000	18.74%	1,127,500,000	54.50%	3,855,000	32.82%	2,658,895,000	31.64%
31-Dec-10	1,785,750,000	16.32%	1,303,500,000	15.61%	2,925,000	-24.12%	3,086,325,000	16.08%
31-Dec-11	2,059,500,000	15.33%	887,250,000	-31.93%	2,332,500	-20.26%	2,944,417,500	-4.60%
31-Dec-12	2,438,250,000	18.39%	500,000,000	-43.65%	12,000,500	414.49%	2,926,249,500	-0.62%
31-Dec-13	2,844,750,000	16.67%	1,287,750,000	157.55%	5,220,000	-56.50%	4,127,280,000	41.04%
Compound growth rate- 10 years		18.44%		20.44%		22.51%		19.02%
Compound growth rate- 5 years		20.16%		31.85%		36.34%		23.76%
Average growth rate- 10 years		14.97%		69.15%		46.12%		29.71%
Average growth rate- 5 years		13.20%		101.42%		26.81%		40.54%

Gordon growth model
Stock price= D/(k-g)
k= D/stock price + g
Current stock price/ share	42.15
No of shares outstanding	2,919,842,000
Equity value	123,071,340,300

		Dividends		Repurchases		Stock issues		total cash flow to equity
Cost of equity		21.18%		21.70%		22.51%		23.02%

Dividends	Growth in dividend is very steady. An 18% growth rate over the 10 years in pretty impressive. Since this rate is stable, it can be used to find the cost of equity as a reliable measure
Repurchases	Repurchases fluctuate to a high level. This is in a way, cash flow to existing shareholders. However, this cant be used a reliable measure as it not steady and often -ve
Stock issues	Stock issues fluctuate to a high level. This is in a way, -ve cash flow to existing shareholders if we assume they buy the additional reissue. However, this cant be used a reliable measure as it not steady and often -ve
total cash flow to equity	This is the most comprehensive measure of total cash flow to the shareholders. There are fluctuations due to the changes in the components. However, it may be used as a measure of growth rate in Gordon model.

Note- the scale of stock issues and other 3 cash flows is not the same. Hence, it is mapped separately