Computation of Dividend Growth Rate for Johnson and Johnson (JNJ) Employing Log-linear Regression

This is a project designed to get you familiar with using the log-linear regression method to determine the growth rate of dividends. A partial dividend history for JNJ is given below:

Year Annual dividend

1991  0.16000

1992  0.18625

1993  0.21500

1994  0.24500

1995  0.27750

1996  0.31500

1997  0.36000

1998  0.41250

1999  0.47000

2000  0.53500

2001  0.60500

2002  0.68500

2003  0.78500

2004  0.91000

2005  1.06000

2006  1.23000

2007  1.40500

2008  1.58000

2009  1.74000

2010  1.90500

2011  2.25000

As you can see, the data above starts from 1991 and ends in 2011. This represents 21 years of ANNUAL dividend per share data in a time-series sequence.

Please use the log-linear method to determine the growth rate of dividends for JNJ. To do this, you need to note down the dividends series above. You could probably cut and paste the data above into an excel worksheet. Then, create a column for the relative year (this is your X-variable for the regression), starting with 1 for 1991, 2 for 1992, etc. Then take natural log of the dividend and enter this into another column. You can use the excel function for this purpose called LN. So, for example, if you want to take the natural log of the value in cell B4, you would use the following formula in EXCEL : =LN(B4)

After creating the column of the natural log of the dividends, please realize that this is your Y-variable for the regression. Then, do the linear regression and compute the value of the constant growth rate, after doing the appropriate mathematical operations.

Note: You can compute the value of ex by using the excel function: =exp(x)  

Expectations from the assignment:

The product from this assignment should be a report that consists of:

(1) A discussion which explains the process of the log-linear method of regression to determine the dividend growth rate. Please ensure that your answer is able to convince me that you understand the method thoroughly.

(2) A spreadsheet with all appropriate computations.

(3) A discussion which contains an interpretation of relevant statistical measures which refers explicitly to the numbers in your spreadsheet (see item 3 above), including whether you think that the constant growth model is appropriate to model the dividend stream of JNJ. Where is this growth rate used in corporate finance? Specifically, what applications is it employed in?