1. Description of the Empirical Problem
A. DATA:
Select a company you would like to investigate. You will use data available from the WRDS (Wharton Research Data Service) database. Their web address is:
https://wrds-web.wharton.upenn.edu/wrds/
You can request your own password from them. Alternatively, you can use the following login information:
Userid: sseechar
Pwd: Plza.123
For the company of your choice download the following time series from WRDS database:
1. Download data on monthly returns (choose Holding Period Return variable) for the most recent 5-year period (you will have 60 observations). The steps are:
a. Choose CRSP (Center for the Research of Security Prices) database
b. Choose North America
c. Choose Equities
d. Choose Monthly data files
e. Enter the dates
f. In Company Codes enter the ticker symbol for your stock
g. In Time Series Information click on “Holding Period Return”
h. Choose your output to be delivered in the Excel format
i. Submit your request
j. In addition, down load the price of the stock for the last date in your data sample for returns. This is your Po.
2. From the COMPUSTAT database (another part of WRDS), download dividends for the same 5-year period. Those are generally paid quarterly, so you will have 20 dividends. Find quarterly growth rate of dividends and then convert it into the monthly growth rate of dividends.
a. Go to COMPUSTAT
b. In Compustat Monthly Updates click on North America
c. Click on Fundamentals Quarterly
d. Enter the dates
e. Enter the ticker
f. In Quarterly Data Items select
DVTQ -- Dividends – Total
g. Download the data in the Excel format
B. ESTIMATION:
1. Calculate the mean monthly return. To estimate the mean return over the five-year period, take an average of your monthly returns. Use AVERAGE function in Excel.
2. Calculate the average quarterly growth rate of dividends. You will have to convert this to the monthly growth rate.
3. Use Gordon’s Model to find the projected price of the stock for your company at the end of the first quarter of 2016. Compare your results with the actual price on March 31st, 2016.
PMarch_31st_2016 = (Div2nd_quarter_of_2016)/(r-g) (*)
In equation (*), Div_Aprilr_of_2016 represents the projected dividend which your company is expected to pay in April 2016, assuming that the growth rate of dividends remains constant.
4. Perform sensitivity analysis: Investigate how changes in r and g affect your valuation. In your report present your data and answers to questions 1 – 4 in part B. Please, describe your findings, don’t just use formulas and numbers.