FIN 315

FIN 315 Final Project, Due December 8, 2017

Create a VBA model to value a share of stock when the company has two high growth periods.

45 points

Example Roper Technologies. Choose a different stock dividend paying stock!

Currently Roper Technologies, as of November 29, 2017, is selling at nearly 40x the trailing 12 month EPS. Clearly the market views this company as a growth stock. During the last five years the company’s dividends have growth by nearly 19% per year. See the exhibit below.

And, analysts are projecting an EPS growth of 15% for the next five years.

What is the intrinsic value of this stock? A simple application of the Constant growth model, D1/ (Ke-g), is not appropriate because the company cannot grow at a constant rate of 15%. At some point the company’s growth would revert to the average growth of the economy.

Your job (the final project) is to create a VBA model which would include two fast growth periods, followed by a constant growth. Use input boxes for:

· the growth rates and lengths for the first two periods,

· the current dividend,

· the beta,

· the 10-year risk free rate,

· the long-term growth rate.

After obtaining the information from the input boxes, the routine should compute the cost of equity capital, create an array for the future dividends during the two growth periods, the value of the discounted dividends, the estimated stock value at the end of the growth period, and finally, the intrinsic value of the stock today.

· Outputs to your spreadsheet should be:

· The array of dividends in row 3, beginning in column G.

· Place the text Forecasted Dividends in F3

· Insert the forecasted stock price, at end of growth period, in D16, “Forecasted Stock Price” in B16

· Both a message box with the intrinsic value, and inserting the intrinsic value in cell E4, “Intrinsic value” in E3.

Insert a button on your worksheet to run the macro. Also, include code lines to accommodate input errors. Either exit the program or repeat the input box if the user enters an incorrect response.

1. The macro should accommodate various growth periods…..not a fixed number of years.

2. Format output in the MsgBox and worksheet.

3. Input box messages should be correctly formatted.

4. Format spreadsheet, i.e., column width, through the subroutine

DateDividends

12/19/20120.165

4/10/20130.165

7/10/20130.165

10/9/20130.165

1/8/20140.2

4/9/20140.2

7/9/20140.2

10/8/20140.2

1/7/20150.25

4/8/20150.25

7/8/20150.25

10/7/20150.25

1/6/20160.3

4/6/20160.3

7/6/20160.3

10/5/20160.3

1/5/20170.35

4/5/20170.35

7/5/20170.35

10/5/20170.35