Executive Summary

In this homework, we interested in creating an annual data set of earnings, cash flows, and leverage ratio. The finalized data set has 112573 rows and 7 variables. Companies were identified by The Global Company Key (a.k.a GVKEY), a unique 6-digit number key. The data was retrieved from Wharton Research Data Services (WRDS) Compustat-Capital IQ database. Besides the exisiting description variables, leverage ratio was also calculate in below. This report shows the overall data set summary statistics as well as summary statistics for each company.

Methodology & Variable Descriptions

  1. \[\text{Total Debt}=\text{Short-term Debt}+ \text{Long-term Debt}\]

2.\[\text{Leverage Ratio}=\frac{\text{Total Debt}}{\text{Total Asset}}\]

  1. CHECH: CHECH – Cash and Cash Equivalents Increase/(Decrease)

This item represents the net change in cash presented separately from cash equivalents for companies.

  1. AT : AT – Assets - Total

This item represents the total assets/liabilities of a company at a point in time.

  1. EBIT : EBIT – Earnings Before Interest and Taxes \[\text{Earnings Before Interest and Taxes}= \text{Net Sales}- \text{COGS}- \text{XSGA}-\text{Depreciation/Amortization}\]

  2. EBITDA: EBITDA – Earnings Before Interest

\[\text{Earnings Before Interest}= \text{Net Sales}- \text{COGS}- \text{XSGA}\]

Plots and Figures

Overall Summary Statistics

Summary Statistics By Company

Computer Code

data <- read.csv("D:/Academic/UCLA MFE/Winter Quarter 2021/404 Financial Decision/hw4_files/hw4_data.csv")
fd <- data$bast+data$dltt
leverage <- fd/data$at
newdata <- as.data.frame(cbind(data$datadate,data$gvkey,data$ebit,data$ebitda,data$chech,leverage))
colnames(newdata) <- c("Date","GVKEY","EBIT","EBITDA",
                       "Cash and Cash Equivalents Change","Leverage")
rownames(newdata) <- NULL
date <- lubridate::ymd(newdata$Date)
newdata$year <- year(date)
n <- newdata %>% 
  summarize(number.of.companies = length(unique(GVKEY)),
            avg.ebit=mean(EBIT,na.rm = TRUE),
            avg.ebitda = mean (EBITDA,na.rm = T),
            avg.leverage = mean(leverage,na.rm=T))
colnames(n) <- c("Numbers of Company included","Average EBIT Amount",
                 "Average EBITDA Amount","Average Leverage Ratio")
as.data.frame(n)
stat <- newdata %>% 
  group_by(GVKEY) %>% 
  summarise(avg.EBIT = mean(EBIT,na.rm = T),
            avg.EBITDA = mean(EBITDA,na.rm = T),
            avg.cash.cash = mean(`Cash and Cash Equivalents Change`,na.rm = T),
            time.frame = max(year)-min(year))
colnames(stat) <- c("GVKEY","Average EBIT Amount ","Average EBITDA Amount",
                    "Average Cash Flow Change","Time of the Company Info Included")
as.data.frame(stat)