Question

2.As a broker at Churnem & Burnem Securities, you recommend stocks toyour clients. After gathering data on Furniture Factory, you have foundthat its dividend has been growing at a rate of 3% per year to the current(D0) $1.25 per share. The stock is now selling for $30 per share, and youbelieve that an appropriate rate of return for this stock is 9% per year.

a.If you expect that the dividend will grow at a 3% rate into theforeseeable future, what is the highest price at which you wouldrecommend purchasing this stock to your clients?

b.Suppose now that you believe that the company’s new product linewill cause much higher growth in the near future. Your new estimateis for a three-year period of 12% annual growth to be followed by areturn to the historical 3% growth rate. Under these new assumptions,what is the value using the two-stage dividend growth model?

c.You now realize that it is likely that the growth will transition from12% down to 3% gradually, rather than instantaneously. If you believethat the transition will take five years, what is the value of the stock?Use the three-stage and H-Model valuation methods.

d.For each of the answers from above, create an IF statement that showswhether the stock is undervalued, overvalued, or fairly valued.

e.Create a data table showing the stock value using the above modelsfor long-run growth rates between 0% and 10% in 1% increments.Use an XY Scatter chart to visualize the results.

Answer 1

The Solution to part 2a)

Since the dividend will grow at a constant rate until perpetuity, the value of the stock can be calculated using the Dividend Discount Growth Model. According to that,

Value of Share = D₁ / (K_e - g)

where,

D₁ = Next year dividend

Ke = Cost of Equity

g = growth rate

In the given case

Next year dividend = current dividend * (1+g)

= $ 1.25 * (1+3%)

= $ 1.29

Hence,

Value of Share = D₁ / (K_e - g)

= $ 1.29 / (9% - 3%)

$ 21.45

Thus, the highest recommended price for buying the stock will be $ 21.45

The Solution to 2b)

Calculation of Dividend at the end of each year

D1 = D0 * (1+g%) = $ 1.25 * (1+12%) = $ 1.40

D2 = D1 * (1+g%) = $ 1.40 * (1+12%) = $ 1.57

D3 = D2 * (1+g%) = $ 1.57 * (1+12%) = $ 1.76

It is mentioned that after three years the dividend will grow at a constant rate of 3%.

Hence, as per the two-stage dividend discount model, the value of the stock will be:

= D1/(1+r)¹ + D2/(1+r)² + D3/(1+r)³ + [D3*(1+g%)/(K_e - g)]/(1+r)³​​​​​​​

= $ 1.40/(1+9%)¹ + $ 1.57/(1+9%)² + $ 1.76/(1+9%)³ + [ $ 1.76*(1+3%)/(9%-3%)] / (1+9%)³

= $ 1.28 + $ 1.32 + $ 1.36 + $ 23.28

= $ 27.24

Thus, the value of the stock under two stage dividend discount model is US $ 27.24

The Solution to 2c)

The value of the stock using the three-stage H model can be calculated as:

P₀ = D₀ * (1+g₂) / (Ke - g₂ ) + D₀ * H * (g₁ - g₂) / (Ke - g₂ )

where

D₀ = Current year dividend = $ 1.25

g₁ = Initial growth rate = 12%

g₂ = terminal growth rate = 3%

Ke = Required rate of return = 9%

H = half of the anticipated transition period = 5 years*1/2 = 2.5 year

Hence,

The value of the stock using the three-stage H model will be:

P₀ = D₀ * (1+g₂) / (Ke - g₂ ) + [D₀ * H * (g₁ - g₂)] / (Ke - g₂ )

= $ 1.25 * (1+3%) / (9%-3%) + [ $ 1.25 * 2.5 * (12%-3%) ] / (9%-3%)

= $ 21.46 + $ 4.69

= $ 26.15

The value of stock using the three-stage H model is $ 26.15