Description

Create an annual data set of earnings, cash flows, and leverage ratios (FD/TA, market and book-based), as available to investors for sure in December of the year. Note that a firm that closes its financials in December 2012 usually won’t release these financials until March 2013. Also, you may want to read ahead to learn more about what earnings mean if you do not remember your accounting courses. (This will be used later. For the hand-ins, just provide good summary statistics describing your data set. Do not try to submit the entire data set.)

Data Calculation

The following fundamental data from each company is what we are interested in.

Total Assets = Total Liabilities + Total Equity

Total CashFlow = Operating CF + Invesment CF + Financing CF

And below are 3 of the most commonly used leverage ratios:

Debt-to-Assets Ratio = Total Debt / Total Assets

Debt-to-Equity Ratio = Total Debt / Total Equity

Debt-to-EBITDA Ratio = Total Debt / Earnings Before Interest Taxes Depreciation & Amortization (EBITDA)

We also cut off the data with the date that is later than October during the year, because the investor wouldn’t have the access to these data until next year march.

Summary

We can see that over the year total asset / total debt / total earning are all growing. And this could reflect on the leverage ratio. As the figure shows, Debt to Assets ratio is pretty stable throughout the years, means that although the assets/equity/revenue are growing, yet the debt is also getting larger and larger. The Debt/Equity & Debt/EBIDTA are relatively more volatile because equity shares fluctuate a lot (due to merger/IPO etc.) (the ratio plot due to missing data cause a large outlier value appears)

We also can see that the gap between big cap company and other is becoming larger and larger. Below figure shows the allocation of the average total asset over the years, we can see that only small amount of company have reached over 10b of total assets.

Code Block

library(ggplot2)
library(dplyr)
library(lubridate)
finlset_data_raw = read.csv("./hw4_data.csv")
ticker_data_all = finlset_data_raw %>% mutate( MON = month(ymd(datadate)))  %>% 
  filter(MON < 10) %>% group_by(gvkey, fyear) %>% na.omit() %>% 
  mutate(TA = (seq + lt), CF = (fincf+ivncf+oancf))%>%  
  group_by(gvkey) %>% filter_all(all_vars(!is.na(.))) %>% 
  summarise(
        Avg_TA = mean(TA),
        Avg_Eqt = mean(seq),
        Avg_D = mean(lt),
        Avg_EBITDA = mean(ebitda),
        Avg_Revenue = mean(revt),
        Avg_GP =mean(gp),
        Avg_CF = mean(CF),
      ) 
comp_plt = ticker_data_all %>% ggplot(aes(y = Avg_TA, x = gvkey)) +
  geom_point(aes(y=Avg_TA), size = .3) +
  labs(y = "Mil") +
  ggtitle("Total Assets vs Company")

yearly_data_all = finlset_data_raw %>% mutate( MON = month(ymd(datadate)))  %>% 
  filter(MON < 10) %>% group_by(gvkey, fyear) %>% na.omit() %>% 
  mutate(TA = (seq + lt), CF = (fincf+ivncf+oancf), 
         ) %>%  group_by(fyear) %>% 
  summarise(
        Avg_TA = mean(TA),
        Avg_Eqt = mean(seq),
        Avg_D = mean(lt),
        Avg_EBITDA = mean(ebitda),
        Avg_Revenue = mean(revt),
        Avg_GP =mean(gp),
        Avg_CF = mean(CF),
      )
n=dim(yearly_data_all)[1]
yearly_data_all=yearly_data_all[1:(n-1),]

Re-organized the data into yearly based

head(yearly_data_all)
## # A tibble: 6 x 8
##   fyear Avg_TA Avg_Eqt Avg_D Avg_EBITDA Avg_Revenue Avg_GP Avg_CF
##   <int>  <dbl>   <dbl> <dbl>      <dbl>       <dbl>  <dbl>  <dbl>
## 1  1989   676.    231.  444.       86.6        787.   214.  11.8 
## 2  1990   520.    182.  337.       66.2        603.   169.  -1.36
## 3  1991   578.    198.  380.       71.0        653.   185.   3.36
## 4  1992   651.    217.  434.       73.1        697.   195.   1.75
## 5  1993   751.    248.  503.       80.1        767.   213.   1.64
## 6  1994   852.    272.  580.       93.3        856.   237.   2.98
#making plots
leverage_data_all = finlset_data_raw %>% mutate(MON = month(ymd(datadate)))  %>%
  filter(MON < 10) %>% group_by(gvkey, fyear) %>% filter(!is.na(ebitda)&!is.na(lt)&!is.na(seq) ) %>% 
  mutate( DtE_ratio = lt/seq, DtTA_ratio = lt/(lt+seq), DtEB_ratio = lt/ebitda ) %>%  
  filter_all(all_vars(!is.infinite(.) &!is.na(.))) %>% 
  group_by(fyear) %>%
  summarise(
    Avg_Eqt = mean(seq),
    Avg_D = mean(lt),
    Avg_DE = Avg_D/Avg_Eqt,
    Avg_DtE_ratio = mean(DtE_ratio),
    Avg_DtTA_ratio = mean(DtTA_ratio),
    Avg_DtEB_ratio = mean(DtEB_ratio)
  )
n=dim(leverage_data_all)[1]
leverage_data_all=leverage_data_all[1:(n-1),]


yearly_plt = yearly_data_all %>% ggplot(aes(x = fyear)) +
  geom_line(aes(y = Avg_TA, color = "Asset")) +
  geom_line(aes(y = Avg_Eqt, color = "Equity")) +
  geom_line(aes(y = Avg_D, color = "Debt")) +
  geom_line(aes(y = Avg_EBITDA, color = "EBITDA")) +
  geom_line(aes(y = Avg_CF, color = "Cash Flow")) +
  geom_line(aes(y = Avg_GP, color = "Gross Profits")) +
  geom_line(aes(y = Avg_Revenue, color = "Revenue")) +
  labs(x = "YEAR", y = "Millions") +
  ggtitle("Historical Data") +
  theme_bw()

leverage_plt = leverage_data_all %>% ggplot(aes(x = fyear)) +
  geom_line(aes(y = Avg_DtE_ratio, color = "Debt/Equity")) +
  geom_line(aes(y = Avg_DtTA_ratio, color = "Debt/Assets")) +
  geom_line(aes(y = Avg_DtEB_ratio, color = "Debt/EBITDA")) +
  labs(x = "YEAR") +
  ggtitle("Leverage Ratio") +
  theme_bw()
comp_plt

yearly_plt

leverage_plt