Data Analysis Project [15 marks]
Due date: 5pm, October 4, 2019
Requirement: Pls finish the tasks according to the requirements. Pls copy all of your R code in the end of this word file, and then submit your word file via the Turnitin link in iLearn. You do not need to upload your data file.
Task 1: CAPM and Fama French three factors model. [6 marks]
• Pls download the daily price data of the assigned stock from Yahoo finance (https://au.finance.yahoo.com/) from January 3, 2011 to December 31, 2018.
Student ID ending number
Stock
0
The Boeing Company
1
IBM
2
Microsoft
3
Wal-Mart
4
American Express
5
Apple
6
Goldman Sachs
7
Intel
8
Google (Alphabet)
9
Amazon
• Download Fama French daily three factors data from Ken French’s data library (https://mba.tuck.dartmouth.edu/pages/faculty/ken.french/data_library.html).
• Calculate daily log return of your assigned stock using the daily adjusted price (Adj Close).
• Merge your stock data with the Fama French three factors data. [Hint: you can use the function of “merge”], and fill up the following summary statistic table. [Ensure stock return and three factors are measured in the same scale.] [2 marks]
Table 1: Summary Statistics (2011-2018)
Variable
Stock Return
Mkt.Rf
SMB
HML
RF
Min
Max
Mean
Median
SD
N
(Mkt.Rf stands for market risk premium, SD stands for standard deviation and N stands
for number of observations)
• Pls create a new variable Stock.Rf, which equals to the difference between Stock Return and RF, and run a regression of :
.
Report the regression results in a table (1 mark) and also make a figure, where Mkt.Rf is in the X-axis, and Stock.Rf is in the Y-axis, and also plot the fitted line in the figure (1 mark).
• Pls run a regression of :
.
Report the regression results in a table (1 mark) and make your comments by comparing two tables in e and f (1 mark).
Task 2: [9 marks]
In the attached dataset named as “firm financial dataset”, you are provided with the financial information of US firms in a long sample period. It is a panel dataset.
Here is a list of variables:
gvkey: firm identifier; each firm has a unique identifier;
fyear: fiscal year of the data
conm: firm name
sic: SIC industry code (https://en.wikipedia.org/wiki/Standard_Industrial_Classification)
at: total asset (millions)
bkvlps: book value per share (dollars)
ceq: common equity (millions)
che: cash and short-term investments (millions)
csho: common shares outstanding (millions)
dlc: debt in current liabilities (millions)
dltt: long term debt (millions)
ebitda: earnings before interest, depreciation and amortisation (millions)
invt: inventory (millions)
ppent: property, plant and equilibrium (millions)
sale: net sales (millions)
prcc_f: end of fiscal year share price (dollars)
Requirements:
a. First, keep observations with SIC codes between 2000 to 3999 (manufacturing firms) and keep observations with fiscal year between 1975 and 2015.
Second, construct variables based on the sample remaining from the first step using the following definitions:
• Market leverage: market leverage=
total liability/market value of asset=(DLTT+DLC)/( DLTT+DLC+ CSHO*PRCCF”)
• Cash holding: cash holding=cash and short-term investments/total asset=CHE/AT;
• Tangibility: tangibility=(Inventory+ Net Property, Plant and Equipment)/Total Asset=(INVT+PPENT)/AT;
• Market to book ratio: M/B= stock price of fiscal year/book value per share=PRCC_F/BKVLPS;
• Size: size=log(SALE)
• Profitability: profitability=EBITDA/Total Assets=EBITDA/TA;
Third, combine these variables with the original dataset and clean up data to:
exclude Inf,- Inf, and NAs;
exclude negative market leverage, tangibility, and market to book ratio measures;
[ 3 marks]
b) Run an Ols regression. The Y variable is Book leverage. The X variables are Tangibility, Market to book ratio, Size and Profitability.
You can use the method of Rajan and Zingales (1995) by averaging the Y and X variables across fiscal years for each firm and then run the OLS regression.
[Note: by doing so, we finally reach a cross sectional data set, and each firm has one Y variable and four X variables.] ;
Present your estimation results in an organized table (similar to Table IX of Ranjan and Zingales, 1995). [2 marks]
Also briefly discuss your results in the context of capital structure theories. [1 mark]
c) Media commentators have been saying that U.S. corporations hold too much cash for a while. Plot the median and mean of cash holding by year. Is there any trend in cash holding in your sample? (2 marks) If there is a trend, pls discuss in your opinion, why U.S. firms are holding more cash over time. (1 mark)
[Note: each year from 1975 to 2015, you need to calculate the median and mean of the cash holdings for all the firms.].
R code: