Assignment 5

This is an R Markdown Notebook. When you execute code within the notebook, the results appear beneath the code.

Try executing this chunk by clicking the Run button within the chunk or by placing your cursor inside it and pressing Cmd+Shift+Enter.

# load packages
library(dplyr)
library(tidyr)
library(ggplot2)

# view dataset
View(financialanalysis)

#summary of dataset
summary(financialanalysis)

    gvkey              datadate              conm                act                 ap          
 Length:1632        Min.   :1997-01-31   Length:1632        Min.   :    0.07   Min.   :    0.12  
 Class :character   1st Qu.:2000-08-31   Class :character   1st Qu.:  132.01   1st Qu.:   24.41  
 Mode  :character   Median :2005-01-31   Mode  :character   Median :  366.32   Median :   78.30  
                    Mean   :2005-11-07                      Mean   : 1972.03   Mean   :  757.92  
                    3rd Qu.:2011-01-31                      3rd Qu.: 1142.68   3rd Qu.:  277.23  
                    Max.   :2017-08-31                      Max.   :63278.00   Max.   :41433.00  
       at                 ceq               che               cogs               csho         
 Min.   :     0.24   Min.   :-3824.0   Min.   :   0.00   Min.   :     0.0   Min.   :   0.001  
 1st Qu.:   230.10   1st Qu.:  101.8   1st Qu.:  17.14   1st Qu.:   289.6   1st Qu.:  17.328  
 Median :   642.70   Median :  313.7   Median :  78.77   Median :   787.0   Median :  42.855  
 Mean   :  4749.65   Mean   : 1860.1   Mean   : 393.85   Mean   :  6936.2   Mean   : 153.360  
 3rd Qu.:  2288.80   3rd Qu.: 1121.4   3rd Qu.: 249.47   3rd Qu.:  2753.6   3rd Qu.: 115.092  
 Max.   :204751.00   Max.   :81394.0   Max.   :9135.00   Max.   :355913.0   Max.   :4470.000  
      dlc                 dltt                dv               ebit               invt         
 Min.   :    0.000   Min.   :    0.00   Min.   :   0.00   Min.   :-1877.00   Min.   :    0.00  
 1st Qu.:    0.000   1st Qu.:    0.00   1st Qu.:   0.00   1st Qu.:   10.56   1st Qu.:   67.96  
 Median :    1.283   Median :   25.41   Median :   0.00   Median :   63.18   Median :  194.77  
 Mean   :  173.963   Mean   : 1031.90   Mean   :  95.78   Mean   :  539.93   Mean   : 1164.16  
 3rd Qu.:   17.525   3rd Qu.:  265.96   3rd Qu.:  17.66   3rd Qu.:  264.06   3rd Qu.:  760.34  
 Max.   :12719.000   Max.   :47079.00   Max.   :6294.00   Max.   :26027.00   Max.   :45141.00  
      lct                 lt                 mrc1              mrc2              mrc3              mrc4        
 Min.   :    0.15   Min.   :     0.00   Min.   :   0.00   Min.   :   0.00   Min.   :   0.00   Min.   :   0.00  
 1st Qu.:   56.64   1st Qu.:    85.72   1st Qu.:  25.49   1st Qu.:  23.15   1st Qu.:  20.95   1st Qu.:  17.75  
 Median :  160.81   Median :   259.83   Median :  64.29   Median :  59.27   Median :  54.08   Median :  47.72  
 Mean   : 1460.92   Mean   :  2839.03   Mean   : 148.95   Mean   : 137.04   Mean   : 123.00   Mean   : 108.45  
 3rd Qu.:  618.96   3rd Qu.:  1188.13   3rd Qu.: 164.57   3rd Qu.: 155.44   3rd Qu.: 138.06   3rd Qu.: 119.08  
 Max.   :71818.00   Max.   :121921.00   Max.   :2270.00   Max.   :1989.00   Max.   :1794.00   Max.   :1697.00  
      mrc5             mrcta                ni                oancf              ppent          
 Min.   :   0.00   Min.   :    0.00   Min.   :-4803.000   Min.   :-2167.00   Min.   :     0.00  
 1st Qu.:  13.43   1st Qu.:   28.41   1st Qu.:    2.667   1st Qu.:   16.36   1st Qu.:    65.94  
 Median :  40.87   Median :  122.86   Median :   32.086   Median :   72.84   Median :   187.24  
 Mean   :  93.62   Mean   :  530.85   Mean   :  299.073   Mean   :  572.52   Mean   :  2218.62  
 3rd Qu.: 103.88   3rd Qu.:  386.41   3rd Qu.:  143.500   3rd Qu.:  289.32   3rd Qu.:   809.78  
 Max.   :1530.00   Max.   :12438.00   Max.   :16999.000   Max.   :31530.00   Max.   :117907.00  
       re                rect                revt               txc                xint                sich     
 Min.   :-7064.00   Min.   :    0.000   Min.   :     0.0   Min.   :-133.000   Min.   :   0.0000   Min.   :5311  
 1st Qu.:   22.57   1st Qu.:    1.389   1st Qu.:   456.7   1st Qu.:   0.084   1st Qu.:   0.2815   1st Qu.:5331  
 Median :  190.78   Median :   10.007   Median :  1270.1   Median :  14.540   Median :   3.4155   Median :5621  
 Mean   : 1532.31   Mean   :  305.465   Mean   :  9655.8   Mean   : 165.049   Mean   :  71.4836   Mean   :5521  
 3rd Qu.:  775.93   3rd Qu.:   55.071   3rd Qu.:  4252.7   3rd Qu.:  73.585   3rd Qu.:  27.5822   3rd Qu.:5651  
 Max.   :78609.00   Max.   :31622.000   Max.   :483521.0   Max.   :8619.000   Max.   :2587.0000   Max.   :5661  
     prcc_c              year     
 Min.   :  0.0026   Min.   :1997  
 1st Qu.:  7.7300   1st Qu.:2000  
 Median : 18.2250   Median :2005  
 Mean   : 23.3104   Mean   :2006  
 3rd Qu.: 31.4331   3rd Qu.:2011  
 Max.   :186.1200   Max.   :2017  

# compute variables
ratios<-financialanalysis %>%
  group_by(gvkey)%>%
  mutate(mrc6=mrcta/5,
         mrc7=mrcta/5,
         mrc8=mrcta/5,
         mrc9=mrcta/5,
         mrc10=mrcta/5,
         pv_mrc1=mrc1/1.06,
         pv_mrc2=mrc2/1.06^2,
         pv_mrc3=mrc3/1.06^3,
         pv_mrc4=mrc4/1.06^4,
         pv_mrc5=mrc5/1.06^5,
         pv_mrc6=mrc6/1.06^6,
         pv_mrc7=mrc7/1.06^7,
         pv_mrc8=mrc8/1.06^8,
         pv_mrc9=mrc9/1.06^9,
         pv_mrc10=mrc10/1.06^10,
         total_pv=pv_mrc1+pv_mrc2+pv_mrc3+pv_mrc4+pv_mrc5+pv_mrc6+pv_mrc7+pv_mrc8+pv_mrc9+pv_mrc10,
         
         adj_assets=at+total_pv,
         adj_liab=lt+total_pv,
         adj_debt=dltt+dlc+total_pv,
         
         roa=ni/lag(adj_assets),
         roe=ni/lag(ceq),
         ros=ni/revt,
         gpm=(revt-cogs)/revt,
         daysAR=rect/(revt/365),
         daysInvt=invt/(cogs/365),
         daysAP=ap/(cogs/365),
         ppeTO=revt/(ppent+total_pv),
         currentRt=act/lct,
         quickRt=(act-invt)/lct,
         cashRt=che/lct,
         optCFRt=oancf/lct,
         liabToEqRt=adj_liab/ceq,
         debtToEqRt=adj_debt/ceq,
         intCovRt=(ni+txc+xint)/xint,
         divPayoutRt=dv/ni)
        

# compute median of valuation ratios by year
medians_by_year<-ratios %>% group_by(year)%>% summarize (median_roa=median(roa, na.rm=TRUE  ),
                                                         median_roe=median(roe, na.rm=TRUE  ),
                                                         median_ros=median(ros, na.rm=TRUE  ),
                                                         median_gpm=median(gpm, na.rm=TRUE),
                                                         median_daysAR=median(daysAR, na.rm=TRUE),
                                                         median_daysInvt=median(daysInvt, na.rm=TRUE),
                                                         median_daysAP=median(daysAP, na.rm=TRUE),
                                                         median_ppeTO=median(ppeTO, na.rm=TRUE),
                                                         median_currentRt=median(currentRt, na.rm=TRUE),
                                                         median_quickRt=median(quickRt, na.rm=TRUE),
                                                         median_cashRt=median(cashRt, na.rm=TRUE),
                                                         median_optCFRt=median(optCFRt, na.rm=TRUE),
                                                         median_liabToEqRt=median(liabToEqRt, na.rm=TRUE),
                                                         median_debtToEqRt=median(debtToEqRt, na.rm=TRUE),
                                                         median_intCovRt=median(intCovRt, na.rm=TRUE),
                                                         median_divPayoutRt=median(divPayoutRt, na.rm=TRUE))
                                                    

# create dataframe containing only data for your company
dsw_ratios<-ratios %>% filter(gvkey=="024171")

# merge dataframe for your company with dataframe containing year medians.  merge by year.
firm_with_year_medians <- merge(dsw_ratios,medians_by_year,by="year")

# for each valuation ratio create a barplot of median by year with a line graph for your company's ratio by year
ggplot(firm_with_year_medians)+geom_bar(aes(x =year, y=median_roa), stat="identity") +
  geom_line(aes(x = year, y = roa))

# for each valuation ratio create a barplot of median by year with a line graph for your company's ratio by year
ggplot(firm_with_year_medians)+geom_bar(aes(x =year, y=median_roe), stat="identity") +
  geom_line(aes(x = year, y = roe))

# for each valuation ratio create a barplot of median by year with a line graph for your company's ratio by year
ggplot(firm_with_year_medians)+geom_bar(aes(x =year, y=median_ros), stat="identity") +
  geom_line(aes(x = year, y = ros))

# Gross Profit Margin
ggplot(firm_with_year_medians)+geom_bar(aes(x =year, y=median_gpm), stat="identity") +
  geom_line(aes(x = year, y = gpm))

# Days Receivables Ratio
ggplot(firm_with_year_medians)+geom_bar(aes(x =year, y=median_daysAR), stat="identity") +
  geom_line(aes(x = year, y = daysAR))

# Days Inventory Ratio
ggplot(firm_with_year_medians)+geom_bar(aes(x =year, y=median_daysInvt), stat="identity") +
  geom_line(aes(x = year, y = daysInvt))

# Days Payable Ratio
ggplot(firm_with_year_medians)+geom_bar(aes(x =year, y=median_daysAP), stat="identity") +
  geom_line(aes(x = year, y = daysAP))

#PP&E Turnover
ggplot(firm_with_year_medians)+geom_bar(aes(x =year, y=median_ppeTO), stat="identity") +
  geom_line(aes(x = year, y = ppeTO))

#Current Ratio
ggplot(firm_with_year_medians)+geom_bar(aes(x =year, y=median_currentRt), stat="identity") +
  geom_line(aes(x = year, y = currentRt))

#Quick Ratio
ggplot(firm_with_year_medians)+geom_bar(aes(x =year, y=median_quickRt), stat="identity") +
  geom_line(aes(x = year, y = quickRt))

#Cash Ratio
ggplot(firm_with_year_medians)+geom_bar(aes(x =year, y=median_cashRt), stat="identity") +
  geom_line(aes(x = year, y = cashRt))

#Operating Cash Flow Ratio
ggplot(firm_with_year_medians)+geom_bar(aes(x =year, y=median_optCFRt), stat="identity") +
  geom_line(aes(x = year, y = optCFRt))

#Liabilities-to-Equity Ratio
ggplot(firm_with_year_medians)+geom_bar(aes(x =year, y=median_liabToEqRt), stat="identity") +
  geom_line(aes(x = year, y = liabToEqRt))

#Debt-to-Equity Ratio
ggplot(firm_with_year_medians)+geom_bar(aes(x =year, y=median_debtToEqRt), stat="identity") +
  geom_line(aes(x = year, y = debtToEqRt))

#   Interest Coverage Ratio (earnings basis)
ggplot(firm_with_year_medians)+geom_bar(aes(x =year, y=median_intCovRt), stat="identity") +
  geom_line(aes(x = year, y = intCovRt))

#   Dividend Payout Ratio
ggplot(firm_with_year_medians)+geom_bar(aes(x =year, y=median_divPayoutRt), stat="identity") +
  geom_line(aes(x = year, y = divPayoutRt))

View(firm_with_year_medians)

Part 2

#Altman Z-Score Ratios 
Zscore_ratios<-ratios %>%
  group_by(gvkey) %>%
  mutate(Z=1.2*((lag(act)-lag(lct))/lag(adj_assets))
         + 1.4*(lag(re)/lag(adj_assets))
         + 3.3*(ebit/lag(adj_assets))
         + .6*((lag(csho)*lag(prcc_c))/lag(adj_debt))
         + (revt/lag(adj_assets))
         )

#Medians
Zscore_medians_by_year<-Zscore_ratios %>% group_by(year)%>% summarize (Zscore_median=median(Z, na.rm=TRUE))
Zscore_dsw<-Zscore_ratios %>% filter(gvkey== "024171")
Zscore_firm_with_year_medians <- merge(Zscore_dsw,Zscore_medians_by_year,by="year")
View(Zscore_firm_with_year_medians)

#Model
ggplot(Zscore_firm_with_year_medians)+geom_bar(aes(x =year, y=Zscore_median), stat="identity") +
  geom_line(aes(x = year, y = Z))