Company Balance Sheet Analysis

10k forms scraped from: https://www.sec.gov/.

• $AMZN (Amazon)
• $AAPL (Apple)
• $PEP (Pepsi)
• $NFLX (Netflix)

# To parse XML documents
library(XBRL)
# To visualize results
library(plotly)
library(ggplot2)
# To analyze balance sheets
library(finstr)
# Data munging
library(dplyr)
# HTML Tables
library(htmlTable)
setwd("~/Work/BigDataAssignments/secgovanalyze")

#AMZN forms
amzn_url2015<-'http://localhost:8000/amzn/2015/amzn-20141231.xml'
amzn_url2016<- 'http://localhost:8000/amzn/2016/amzn-20151231.xml'
old_o <- options(stringsAsFactors = FALSE)
amzn_2015 <- xbrlDoAll(amzn_url2015)
amzn_2016 <- xbrlDoAll(amzn_url2016)
options(old_o)
amzn2015 <- xbrl_get_statements(amzn_2015)
amzn2016 <- xbrl_get_statements(amzn_2016)

It's the ratio of current assests / current liabilities.

amznBalanceSheet %>% transmute(
  date = endDate, 
  CurrentRatio = AssetsCurrent / LiabilitiesCurrent
)

        date CurrentRatio
1 2013-12-31     1.071584
2 2014-12-31     1.115276
3 2015-12-31     1.075961

Inference : Amazon consistently had a ratio of 1.0 or greater which indicates that Amazon possesed more assets than liablities, which is a good sign for company's growth.

Consolidated Statement of Operations. This dataset highlights the Revenue earned by Amazon v/s amount spent in activities like marketing, admnistration expense, cost of goods and services sold, amazon fulfillment expense and other ops costs.

amznSOP_simple <- expose( amznSOP,
`Revenue` = 'SalesRevenueNet',
`Expenditure` = 'CostsAndExpenses'
)

print(amznSOP_simple, html = TRUE, big.mark = ",", dateFormat = "%Y")

Inference: Amazon as a company is increasing it's revenue every year, but this is only possible with greater expenditure. Bottomline: More the expenditure more is the revenue

This dataset highlights how the money is used in Amazon.

amznCashflow_simple <- expose( amznCashflow,
`Ops Activities` = 'NetCashProvidedByUsedInOperatingActivities',
`Investing` = 'NetCashProvidedByUsedInInvestingActivities',
`Financing` = 'NetCashProvidedByUsedInFinancingActivities'
)
print(amznCashflow_simple, html = TRUE, big.mark = ",", dateFormat = "%Y")

Inference: Amazon spends most of the money in Day to day operational activities, but invests some money in acquiring property plant, equipment, business interests, marketable services

This dataset highlights core components of Balance sheet of Amazon

amznBalanceSheet_simple<- expose( amznBalanceSheet,
# Assets
`Current Assets` = "AssetsCurrent",
`Noncurrent Assets` = other("Assets"),
# Liabilites and equity
`Current Liabilities` = "LiabilitiesCurrent",
`Stockholders Equity` = "StockholdersEquity"
)
print(amznBalanceSheet_simple, html = TRUE, big.mark = ",", dateFormat = "%Y")

#AAPL forms
aapl_url2015<-'http://localhost:8000/aapl/2015/aapl-20140927.xml'
aapl_url2016<- 'http://localhost:8000/aapl/2016/aapl-20150926.xml'
old_o <- options(stringsAsFactors = FALSE)
aapl_2015 <- xbrlDoAll(aapl_url2015)
aapl_2016 <- xbrlDoAll(aapl_url2016)
options(old_o)
aapl2015 <- xbrl_get_statements(aapl_2015)
aapl2016 <- xbrl_get_statements(aapl_2016)

It's the ratio of current assests / current liabilities.

aaplBalanceSheet %>% transmute(
date = endDate,
CurrentRatio = AssetsCurrent / LiabilitiesCurrent
)

        date CurrentRatio
1 2013-09-28     1.678639
2 2014-09-27     1.080113
3 2015-09-26     1.108771

Inference : Apple consistently had a ratio of 1.0 or greater which indicates that Apple possesed more assets than liablities, which is a good sign for company's growth.

Consolidated Statement of Income.

This dataset highlights the gross profit and operating expenses at Apple.

aaplIncome <- expose( aapl$StatementOfIncome,
`Op Exp` = 'OperatingIncomeLoss',
`Non Ops Exp` = 'NonoperatingIncomeExpense'
)

print(aaplIncome, html = TRUE, big.mark = ",", dateFormat = "%Y")

Inference: Apple as a company is increasing the net income while also increasing gross expenditure

This dataset highlights how the money is used in Amazon.

aaplCashflow_simple <- expose( aaplCashFlow,
`Ops Activities` = 'NetCashProvidedByUsedInOperatingActivitiesContinuingOperations',
`Investing` = 'NetCashProvidedByUsedInInvestingActivitiesContinuingOperations',
`Financing` = 'NetCashProvidedByUsedInFinancingActivitiesContinuingOperations'
)
print(aaplCashflow_simple, html = TRUE, big.mark = ",", dateFormat = "%Y")

Inference: Apple has reduced the amount spent in Ops which is a good thing, indicating that a lot of automation is happening inside the company to reduce this cost. Apple has varied it's amount of investing in other avenues. Apple also earns a tiny amount from a lot of it's patents as shown in the last column.

This dataset highlights core components of Balance sheet of Apple

aaplBalanceSheet_simple<- expose( aaplBalanceSheet,
# Assets
`Current Assets` = "AssetsCurrent",
`Noncurrent Assets` = other("Assets"),
# Liabilites and equity
`Current Liabilities` = "LiabilitiesCurrent",
`Noncurrent Liabilities` = other(c("Liabilities", "CommitmentsAndContingencies")),
`Stockholders Equity` = "StockholdersEquity"
)

print(aaplBalanceSheet_simple, html = TRUE, big.mark = ",", dateFormat = "%Y")

#Pepsi forms
pep_url2015 <-'http://localhost:8000/pep/2015/pep-20141227.xml'
pep_url2016 <-'http://localhost:8000/pep/2016/pep-20151226.xml'
old_o <- options(stringsAsFactors = FALSE)
pep_2015 <- xbrlDoAll(pep_url2015)
pep_2016 <- xbrlDoAll(pep_url2016)
options(old_o)
pep2015 <- xbrl_get_statements(pep_2015)
pep2016 <- xbrl_get_statements(pep_2016)

Consolidated Statement of Income.

This dataset highlights the gross income loss and operating expenses at Pepsi

pepIncomeStatement_simple <- expose( pepIncomeStatement,
`Op Exp` = 'OperatingIncomeLoss',
`Non Ops Exp` = 'pep_InterestIncomeAndOther'
)

print(pepIncomeStatement_simple, html = TRUE, big.mark = ",", dateFormat = "%Y")

Inference: Pepsi as a company is making losses

This dataset highlights how the money is used in Pepsi.

pepCashflow_simple <- expose( pepCashFlow)
print(pepCashflow_simple, html = TRUE, big.mark = ",", dateFormat = "%Y")

Inference: Pepsi has not been able to minimise it's expenditure on Ops Activities. Perhaps if Pepsi focus on reducing this, it can generate more profits.

#NFLX forms
nflx_url2015<-'http://localhost:8000/nflx/2015/nflx-20131231.xml'
nflx_url2016<- 'http://localhost:8000/nflx/2016/nflx-20141231.xml'
old_o <- options(stringsAsFactors = FALSE)
nflx_2015 <- xbrlDoAll(nflx_url2015)
nflx_2016 <- xbrlDoAll(nflx_url2016)
options(old_o)
nflx2015 <- xbrl_get_statements(nflx_2015)
nflx2016 <- xbrl_get_statements(nflx_2016)

It's the ratio of current assests / current liabilities.

nflxBalanceSheet %>% transmute(
  date = endDate, 
  CurrentRatio = AssetsCurrent / LiabilitiesCurrent
)

        date CurrentRatio
1 2012-12-31     1.337047
2 2013-12-31     1.419905
3 2014-12-31     1.479625

Inference : Netflix consistently had a ratio of 1.0 or greater which indicates that Netflix possesed more assets than liablities, which is a good sign for company's growth.

Consolidated Statement of Operations. This dataset highlights the Revenue earned by Netflix v/s amount spent in activities like marketing, admnistration expense and other ops costs.

nflxSOP_simple <- expose( nflxSOP,
`Revenue` = 'Revenues',
`Expenditure` = 'NonoperatingIncomeExpense'
)

print(nflxSOP_simple, html = TRUE, big.mark = ",", dateFormat = "%Y")

Inference: Netflix as a company is increasing it's revenue every year, and the highlight is that expenditure hasn't increased significantly.

Bottomline: Netflix being purely an internet company, has the advantage of controlling the expenditure while growing to lot of users and thus generating more revenue.

This dataset highlights how the money is used in Netflix.

nflxCashflow_simple <- expose( nflxCashflow,
`Ops Activities` = 'NetCashProvidedByUsedInOperatingActivities',
`Investing` = 'NetCashProvidedByUsedInInvestingActivities',
`Financing` = 'NetCashProvidedByUsedInFinancingActivities'
)
print(nflxCashflow_simple, html = TRUE, big.mark = ",", dateFormat = "%Y")

Inference: Netflix spends most of the money in Day to day operational activities, but invests some money in acquiring content library, additions to streaming content and payment of equipments like servers.

This dataset highlights core components of Balance sheet of Netflix

nflxBalanceSheet_simple<- expose( nflxBalanceSheet,
# Assets
`Current Assets` = "AssetsCurrent",
`Noncurrent Assets` = other("Assets"),
# Liabilites and equity
`Current Liabilities` = "LiabilitiesCurrent",
`Stockholders Equity` = "StockholdersEquity"
)
print(nflxBalanceSheet_simple, html = TRUE, big.mark = ",", dateFormat = "%Y")

#WMT forms
wmt_url2015<-'http://localhost:8000/wmt/2015/wmt-20140131.xml'
wmt_url2016<- 'http://localhost:8000/wmt/2016/wmt-20150131.xml'
old_o <- options(stringsAsFactors = FALSE)
wmt_2015 <- xbrlDoAll(wmt_url2015)
wmt_2016 <- xbrlDoAll(wmt_url2016)
options(old_o)
wmt2015 <- xbrl_get_statements(wmt_2015)
wmt2016 <- xbrl_get_statements(wmt_2016)

It's the ratio of current assests / current liabilities.

wmtBalanceSheet %>% transmute(
  date = endDate, 
  CurrentRatio = AssetsCurrent / LiabilitiesCurrent
)

        date CurrentRatio
1 2013-01-31    0.8346097
2 2014-01-31    0.8823275
3 2015-01-31    0.9694509

Inference : Walmart consistently had a ratio of less than 1.0 which indicates that Walmart possesed more liablities than assets, which is not a good sign for company's growth. But given Walmart, which is the largest employer in the world, this case is an exception as it even makes it to Rank 1 of Fortune500 list of companies.

This dataset highlights how the money is used in Netflix.

wmtCashflow_simple <- expose( wmtCashFlow,
`Ops Activities` = 'NetCashProvidedByUsedInOperatingActivities',
`Investing` = 'NetCashProvidedByUsedInInvestingActivities',
`Financing` = 'NetCashProvidedByUsedInFinancingActivities'
)
print(wmtCashflow_simple, html = TRUE, big.mark = ",", dateFormat = "%Y")

Inference: Netflix spends most of the money in finance activities, mainly because it's a high performant stock in the Wall Street and so there are regular payments of dividends by Walmart. Walmart has managed to keep it's Ops expenditure to a minimum which is a good thing, indicating automating is slowly coming into picture to reduce costs.

This dataset highlights core components of Balance sheet of Netflix

wmtBalanceSheet_simple<- expose( wmtBalanceSheet,
# Assets
`Current Assets` = "AssetsCurrent",
`Noncurrent Assets` = other("Assets"),
# Liabilites and equity
`Current Liabilities` = "LiabilitiesCurrent",
`Stockholders Equity` = "StockholdersEquity"
)
print(wmtBalanceSheet_simple, html = TRUE, big.mark = ",", dateFormat = "%Y")