Question

Your boss, Sally Maloney, treasurer of Fred Clark Enterprises(FCE), asked you to help her estimate the intrinsic value of thecompany's stock. FCE just paid a dividend of $1.00, and the stocknow sells for $15.00 per share. Sally asked a number of securityanalysts what they believe FCE's future dividends will be, based ontheir analysis of the company. The consensus is that the dividendwill be increased by 10% during Years 1 to 3, and it will beincreased at a rate of 5% per year in Year 4 and thereafter. Sallyasked you to use that information to estimate the required rate ofreturn on the stock, rs, and she provided you with the following template for use in the analysis.
Estimated rs = 10.00%
(must be changed to force Calculated Price to equal the Actual Market Price)
Actual Market Price, P0: $13.00
Sally told you that the growth rates in the template were just putin as a trial, and that you must replace them with the analysts 'forecasted rates to get the correct forecasted dividends and thenthe estimated TV. She also notes that the estimated value for rs,at the top of the template, is also just a guess, and you mustreplace it with a value that will cause the Calculated Price shownat the bottom to equal the Actual Market Price. She suggests that,after you have put in the correct dividends, you can manuallycalculate the price, using a series of guesses as to the Estimatedrs. The value of rs that causes the calculated price to equal theactual price is the correct one. She notes, though, that thistrial-and-error process would be quite tedious, and that thecorrect rs could be found much faster with a simple Excel model,especially if you use Goal Seek. What is the value of rs?
a. 11.84%
b. 12.21%
c. 12.58%
d. 12.97%
e. 13.36%

Answer 1

Answer:

Check the explanation

Explanation:

The way to solve this in excel:

find the PV of the dividend in each year.

Year 1 Year 2 Year 3 Year 4

1*1.10 1*1.1^2 1*1.1^3 1*1.1^3 *1.05

To find the terminal value of the growth after year 4 we use the following equation:

PV(n-1) = Div1/(Rs-Growth)

year 1 year 2 year 3 year 4

CF 1.1 1.21 1.331 1.3975

PV CF1/(1+rs)^1, CF2/(1+rs)^2, CF3/(1+rs)^3, (CF4/(rs-0.05))/(1+rs)^3

Since the price is equal to 15 the sum of the PV values have to add to 15

Set excel to solve for the sum of those functions to be equal to 15 by changing the value of rs.

Rs = 0.1297 or ~13%