Week 5

# load packages
library(dplyr)

Attaching package: ‘dplyr’

The following objects are masked from ‘package:stats’:

    filter, lag

The following objects are masked from ‘package:base’:

    intersect, setdiff, setequal, union

library(tidyr)
library(ggplot2)

# view dataset
View(financialanalysis)

#summary of dataset
summary(financialanalysis)

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

# 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  ))

# 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))