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?