Please complete assignment using excel and please show formulas

1. Using the past dividend payout information between 2016 and 2020, compute the growth rate (g) for these companies. Your companies must have a complete history of dividend payout during these periods.

Company B - Dividend Yield Summary

	2016	2017	2018	2019	2020	2021
Dividends	1.93	1.64	1.80	2.15	2.05	1.20
Average Price	12.66	16.74	19.46	21.51	17.89	28.28
Yield	15.25%	9.80%	9.25%	10.00%	11.46%	4.24%

The above Dividend, Average Price, and Yield values for 2021 are year-to-date.

Dividend Dates

Ex Dividend Date	2021-06-14
Dividend Pay Date

DivInfo Metrics

52-Week Projected Earnings Per Share	2.87
52-Week Projected Dividends Per Share	2.19
Dividend Cover	1.31 : 1
52-Week Projected Yield	7.46%
52-Week Trailing Yield	8.93%
6-Year Average Yield	9.24%
6-Year Dividend Change	13.47%
6-Year Yearly Dividend Change	2.25%
Number of Dividend Payments (2009-12-08 - 2021-06-14)	28

Date	Amount Per Share	Note
2021-06-14	$0.70
2021-03-19	$0.50
2020-12-17	$0.47
2020-09-18	$0.58
2020-07-14	$0.56
2020-03-17	$0.44
2019-12-13	$0.71
2019-09-19	$0.58
2019-06-13	$0.46
2019-03-14	$0.40
2018-12-17	$0.50
2018-09-14	$0.48
2018-06-14	$0.42
2018-03-19	$0.40
2017-12-15	$0.44
2017-09-21	$0.44
2017-05-26	$0.40
2017-03-16	$0.36
2016-12-13	$0.40
2016-09-16	$0.43
2016-06-16	$0.35
2016-03-18	$0.35
2016-01-05	$0.40
2015-11-16	$2.69
2015-10-20	$0.50
2015-06-25	$0.47
2015-03-26	$0.39
2009-12-08	$0.02

2. First, lets assume that the required return (R) is 10% for all these companies. Using R, dividend information, and growth rate (g) from question 1, compute the stock prices of these companies. Second, lets assume that the required return (R) is 20% for all these companies. Using R, dividend information, and growth rate (g) from question 1, compute the stock prices of these companies (40 points).

3. Compare your estimated stock prices with actual stock prices as of January 4, 2021. Then tell me whether each stock is undervalued or fairly-valued or overvalued based on your estimation. Whats your decision if you hold these stocks? Whats your decision if you dont hold these stocks? (20 points).