Finance Assignment: Leverage, Executive Competition, and CEO Tenure

This finance assignment requires students to analyze the relationship between executive competition and firm leverage, utilizing datasets from WRDS (Compustat/ExecuComp). The assignment involves downloading and processing data, including compensation and firm performance metrics. Students are tasked with cleaning and manipulating the data, calculating variables such as leverage and control variables, and merging datasets. The core of the assignment involves running OLS regressions to assess the impact of executive competition on firm leverage, and exploring the conditional effects of CEO gender and tenure. The analysis requires the use of statistical software like Stata and involves interpreting the statistical and economic significance of the regression results. The assignment also includes tasks like winsorizing variables to handle outliers and generating dummy variables for CEO characteristics.

Introduction
In this assignment, you have to perform pre-specified, step-by-step tasks to
ensure you are familiar with (very) basic data downloading, data processing and
data analyzing basic techniques. This task should take you less than a day of work.
If you find any of these questions complex or it takes your more than one day to
perform them, please consider that you would need to devote additional time to
Assignment 3. This task is much easier than the required tasks for Assignment 3.
Instructions
Please fill in the blanks in the questions below. That is, you need to replace “?”
with the appropriate value and/or explanation.
To obtain the answers, you need to perform the tacks listed in bullet points by the
specified order. The questions are marked with the “Q:” sign and the blanks to be
filled with “???”. Inside [], I specify the value of that question, out of a maximum
of 10 points for this assignment.
You may use any software for this assignment (Excel, Eviews, SPSS, Stata, SAS,
Matlab, Python, and etc.). My preferred software is SAS+ STATA. SAS helps you
manipulate the data and clean the data in a visible way (you also learn SQL) and
STATA has many packages to run the regression and output the results. Erasmus
purchases license of STATA (which is much cheaper…) for students, so you could
easily install it. Therefore I provide some useful detailed STATA code instructions
included in green courier new font where coding is relevant.
I add some [NOTE: ...] that you may find useful.
Task Summary
In this assignment, we will evaluate the effect of executive competition on firm
leverage. And explore the effect conditional on CEO gender and CEO tenure.
The baseline model would be:
leveraget +1=α+ β compensationt +Control vart + εt

Part 1 Obtaining Compensation Datasets
 Login in WRDS
 Download “Compustat/ExecuComp/ Annual Compensation” data.
o Download the entire database by CUSIP for the (fiscal) years 2006-
2015. You need the variables CUSIP and Issue Number; Executive ID
number; CEOANN -- Annual CEO Flag; SALARY -- Salary ($); TDC1 --
Total Compensation (Salary + Bonus + Other An; OPTION_AWARDS –
Value of Option Awards – FAS 123R ($); YEAR -- Fiscal Year; CEO
gender—Gender; Date became CEO
o Use None Compression Type and the Output and Date Format more
appropriate to you. You may save this query if you prefer.
o [NOTE: Make sure that you handle cusip identifiers as a string
variable. Excel (and other non-advanced software) will have an
obsession in converting the all number identifiers to numbers instead
of text. This would imply that zeros on the left would be deleting,
creating possible problems in your matching.
i.e. make sure that cusip “0000360” is not converted to cusip “360”)
 Keep only CEOs’ data: variable ceoann has to be equal to “CEO”.
keep if ceoann=="CEO"
 First drop all observations generated by firms with more than one CEO in a
given year, and by CEOs work in more than one firm in a given year.
sort cusip year execid
by cusip year: gen count_obs = [_N]
drop if count_obs > 1 // Firm has more than 1 CEO in a year.
drop count_obs
sort execid year cusip
by execid year: gen count_obs = [_N]
drop if count_obs > 1 // CEO works in more than 1 firm in a
year.
drop count_obs
 Calculate the variables ln_salary, ln_totalcomp, ln_option. ln_salary is the
logarithm of salary, ln_totalcomp is the logarithm of tdc1, ln_option Is the
logarithm of option_awards.

gen ln_salary = ln(salary)
gen ln_totalcomp = ln(tdc1)
gen ln_option = ln(option_awards)
 Generate 8-digit CUSIP identifier from the first 8 digits in the CUSIP
gen cusip_8d = cusip
 Winsorize the three variables at the 1 %, 99% to prevent extreme values.
That means that you need to change the values above 99% into 99% and
the value below 1% to 1%. Importantly this the winsorization process
should take once every year in case there is any time trend. There is an
available package for this process which is winsor2. You could install it
according to the following code.
ssc install winsor2 //not every package is directly
available, you might want to search it first, just type findit
***(so easy!) and then install it.
 Now you have the package, let’s kick out the outliers.
winsor2 ln_salary, suffix(_w) cuts(1 99) by(year)//here you
generate a new variable, ln_salary_w
 For such kind of repeating work, we could use some loop. Try to google
“foreach stata” and see whether you could make it more efficient.
Let’s call this database, “CEO”.
Hint for Q1 &2 (google “summary statistics Stata”)
Q1: [0.5 point] For “CEO”, report the following and [0.5 point] explain how
winsorize process changes your data.
Variables Ob
s.
Mea
n
Standar
d
Deviatio
n
Minimu
m
Maximu
m
Skewne
ss
Kurtos
is
ln_salary ? ? ? ? ? ? ?
ln_salary_w ? ? ? ? ? ? ?
ln_totalcomp ? ? ? ? ? ? ?
ln_totalcomp
_w ? ? ? ? ? ? ?
ln_option ? ? ? ? ? ? ?

ln_option_w ? ? ? ? ? ? ?
Explanation: ???
Q2: [0.5 point] Report the mean of winsorized variables by year
year ln_salary_w ln_totalcomp_w ln_option_w
2006 ? ? ?
2007 ? ? ?
2008 ? ? ?
2009 ? ? ?
2010 ? ? ?
2011 ? ? ?
2012 ? ? ?
2013 ? ? ?
2014 ? ? ?
2015 ? ? ?
Part 2 Obtaining Firm Performance Datasets
 Login in WRDS
 Download “Compustat/North America/Fundamental Annual” data.
o Download the entire database by cusip for the fiscal years from
January 2006 to December 2016. You need the variables CUSIP;
FYEAR -- Data Year – Fiscal; AT -- Assets - Total; NI – Net Income
(Loss); DLTT – Long-term debt—total ; DLC—Debt in current
liabilities-total; CEQ—common/ordinary equity—total; CSHO—
Common shares outstanding; PRCC_F—Price close-annual fiscal
o Use None Compression Type and the Output and Date Format more
appropriate to you. You may save this query if you prefer.
o [NOTE: Make sure that you handle cusip identifiers as a string
variable. Excel (and other non-advanced software) will have an
obsession in converting the all number identifiers to numbers instead
of text. This would imply that zeros on the left would be deleting,
creating possible problems in your matching.
i.e. make sure that cusip “0000360” is not converted to cusip “360”)

[NOTE: Some variables are automatically downloaded. That’s fine]
 Drop all observations generated by firms with more than one observation
in a given year.
sort cusip fyear at
by cusip fyear: gen count_obs =[_N]
*Firm has more than 1 observations in a year.
drop if count_obs > 1
drop count_obs
 Calculate the variable ml. ml is total debt divided by market value of the
total assets. Generate control variables including size (ln of total assets) and
profitability (ROA)
gen ml = (dltt+dlc) / (at-ceq+cshod*prcc_f)
gen size=ln(at)
gen roa=ni/at
 Now construct the matching variable, firm level and year level.
gen year=fyear
 Keep only years 2006-2015
keep if year >= 2006 & year <= 2015
 Generate 8-digit CUSIP identifier from the first 8 digits in the CUSIP
gen cusip_8d = substr(cusip, 1, 8)
 Winsorize the interested variable and control variables at the 1 % and 99%
level. Easy, right?
 Let’s call this database, “FIRM”.
Q3: [0.5 point] For “FIRM”, report the following
Variables Obs. Mean Standard
Deviation Minimum Maximum Skewness Kurtosis
ml_w ? ? ? ? ? ? ?
size_w ? ? ? ? ? ? ?
roa_w ? ? ? ? ? ? ?

Part 3 Merging Databases
 Merge “database_ceo” and “database_firm” by cusip and year:
merge 1:1 cusip_8d year using CEO.dta
Q4: [1 point] Report the following
Number of Observations in CEO and
not in FIRM ?
Number of Observations in FIRM and
not in CEO ?
Matched Observations ?
 Keep only matched observations.
keep if _merge==3
Part 4 Analysis
 Recall that we are doing prediction? So the leverage should be a forward
looking one? Try to google “lead or lag variable in Stata”. And have a look at
the data, is there any problem? Hint: what does STATA do with the last
observation of a firm?
 After take the lead of market leverage, let’s call it merged data.
 Now be excited! let’s be regression monkeys and run the following OLS
regressions:
(1) mllead=α + βa
1∗ln ¿+ ε
(2) mllead=α + βa
2∗ln ¿+ ε

(3) mllead=α + βa
3∗ln ¿+ ε
(4) mllead=α + βb
1∗ln ¿+ controls+ ε
(5) mllead=α + βb
1∗ln ¿+ controls+ yearFE+ ε
(6) mllead=α + βb
1∗ln ¿+ controls+ firmFE + yearFE+ ε
o [Note: yearFE are dummy variables that takes value 1 for a specific
year, 0 otherwise. Similarly, firmFE are dummy variables that take
value 1 for a given firm (identified by cusip_8d), 0 otherwise.
o [Note: I do not include the code on how to run this regressions
neither how to generate the yearFE and firmFE variables in STATA.
You should learn how to do this by yourself: you can use manuals,
STATA help, google, etc. A hint here is the commands “reg” and
“areg”.
Q5: [1 point] Report the following
βa
1 ?
βa
2 ?
βa
3 ?
βb
1 ?
βb
2 ?
βb
3 ?
Q6: [0.5 points] Comment the statistical and economic significance of βa
1, βa
2,βa
3,
βb
1, βb
2, βb
3. [0,5 points] Comment the results. {Max 70 words for each}
Comments: ?
Q7: [1 point] In regression 6, in which year firms have the highest leverage,
ceteris paribus?
Year ?

Part 5 Gender & CEO tenure impact
 Use the merged data. Calculate the variable “male_d”. “male_d” is a
dummy variable that takes value 1 if CEO is a man.
 Generate how many years has the CEO been with the firm, use becameceo,
drop observations with becameceo date after the year (data error).
gen male_d = (gender==”MALE”)
gen tenure=year-year(becameceo)
drop if tenure<0
Q8: [1 point] Report the following
obs Mean Standard
Deviation Minimum Maximum
male_d ? ? ? 0 1
tenure ? ? ? ? ?
 Run the following regressions
(7) mllead =α + βa
1∗ln ¿∗mal ed +γa
1∗ln ¿ +θa
1∗maled +controls+ yearFE+ firmFE +ε
(8) lm llead=α +βb
1∗ln ¿∗tenure+γ b
1∗ln ¿+θb
1∗tenure+ controls+ yearFE+firmFE+ε

Q9: [1 point] Report the following
βa
1 θa
1 βb
1
θb
1
Estimate ? ? ? ?
t-stat ? ? ? ?
Q10: [1 points] Comment the statistical and economic significance of βa
1, θa
1,
Comment the results. Hint: Why do they become more significant or insignificant
at all? {Max 75 words for each}
Q11: [1 point Comment the statistical and economic significance of βb
1, θb
1,
Comment the results.Hint: what the coefficient really measure when we control
for firm fixed and year fixed effect? {Max 50 words}