DATA607FinalProject

Introduction

Publicly traded coportation are required to file quarterly (10-Q) and annual statements (10-K) with the Securities and Exchange Commission (SEC). While there are many other filings filed with the SEC we will focus on these two versions in this project.

Individul and professional investors look through these reports for nuggets of information to help make decisions. Over the years parts of the reports have become automatable through technological advances and formatting. We will look at how the formatting has helped us analyze certain parts of the filing. The purpose is to help save time looking at the financials so we can spend more time analyzing the text. Improvements have been made in automated text analysis but it is still generally accepted that a human’s contribution is still very important.

What is XBRL? XBRL is the open international standard for digital business reporting, managed by a global not for profit consortium, XBRL International. We are committed to improving reporting in the public interest. XBRL is used around the world, in more than 50 countries. Millions of XBRL documents are created every year, replacing older, paper-based reports with more useful, more effective and more accurate digital versions.

We will look at possibly the most popular company for the past decade Apple (AAPL), a wall sreet darling. Each company is assigned a CIK number, in this case 0000320193. Another important identifier is the Standard Industry Classification (SIC) code, SIC:3571 Electronic Computers. This system aims to classify industries by a government accepted 4 digit code, these codes are utilized in many ways.

Data

Parse SEC filings 10-Ks and retrieve the income statement

library(pander)

## Warning: package 'pander' was built under R version 3.2.5

xbrl_url2014 <- "http://edgar.sec.gov/Archives/edgar/data/320193/000119312514383437/aapl-20140927.xml"
xbrl_url2013 <- "http://edgar.sec.gov/Archives/edgar/data/320193/000119312513416534/aapl-20130928.xml"
old_o <- options(stringsAsFactors = FALSE)
xbrl_data_aapl2014 <- xbrlDoAll(xbrl_url2014)
xbrl_data_aapl2013 <- xbrlDoAll(xbrl_url2013)
options(old_o)

st2013 <- xbrl_get_statements(xbrl_data_aapl2013)
st2014 <- xbrl_get_statements(xbrl_data_aapl2014)

income2013 <- st2013$StatementOfIncome
income2014 <- st2014$StatementOfIncome
is_all <- merge(income2013, income2014)
print(is_all, html = TRUE, big.mark = ",", dateFormat = "%Y")

	2014	2013	2012	2011
Net Income Loss	39,510	37,037	41,733	25,922
Income Loss From Continuing Operations Before Income Taxes Extraordinary Items Noncontrolling Interest	53,483	50,155	55,763	34,205
Operating Income Loss	52,503	48,999	55,241	33,790
Gross Profit	70,537	64,304	68,662	43,818
Sales Revenue Net	182,795	170,910	156,508	108,249
Cost Of Goods And Services Sold	112,258	106,606	87,846	64,431
Operating Expenses	18,034	15,305	13,421	10,028
Research And Development Expense	6,041	4,475	3,381	2,429
Selling General And Administrative Expense	11,993	10,830	10,040	7,599
Nonoperating Income Expense	`980</td>`	1,156	`522</td>`	`415</td>`
Income Tax Expense Benefit	13,973	13,118	14,030	8,283

Retrieve the balance sheet

balance_sheet2013 <- st2013$StatementOfFinancialPositionClassified
balance_sheet2014 <- st2014$StatementOfFinancialPositionClassified
bs_all <- merge(balance_sheet2013, balance_sheet2014)
print(bs_all, html = TRUE, big.mark = ",", dateFormat = "%Y")

	2014	2013	2012
Assets	231,839	207,000	176,064
Assets Current	68,531	73,286	57,653
Cash And Cash Equivalents At Carrying Value	13,844	14,259	10,746
Available For Sale Securities Current	11,233	26,287	18,383
Accounts Receivable Net Current	17,460	13,102	10,930
Inventory Net	2,111	1,764	`791</td>`
Deferred Tax Assets Net Current	4,318	3,453	2,583
Nontrade Receivables Current	9,759	7,539	7,762
Other Assets Current	9,806	6,882	6,458
Available For Sale Securities Noncurrent	130,162	106,215	92,122
Property Plant And Equipment Net	20,624	16,597	15,452
Goodwill	4,616	1,577	1,135
Intangible Assets Net Excluding Goodwill	4,142	4,179	4,224
Other Assets Noncurrent	3,764	5,146	5,478
Liabilities And Stockholders Equity	231,839	207,000	176,064
Liabilities	120,292	83,451	57,854
Liabilities Current	63,448	43,658	38,542
Accounts Payable Current	30,196	22,367	21,175
Accrued Liabilities Current	18,453	13,856	11,414
Deferred Revenue Current	8,491	7,435	5,953
Commercial Paper	6,308	`0</td>`	`0</td>`
Deferred Revenue Noncurrent	3,031	2,625	2,648
Long Term Debt	28,987	16,960	`0</td>`
Other Liabilities Noncurrent	24,826	20,208	16,664
Commitments and Contingencies	`0</td>`	`0</td>`	`0</td>`
Stockholders Equity	111,547	123,549	118,210
Common Stock Value	`0</td>`	`0</td>`	16,422
Retained Earnings Accumulated Deficit	87,152	104,256	101,289
Accumulated Other Comprehensive Income Loss Net Of Tax	1,082	-471	`499</td>`
Common Stocks, Including Additional Paid in Capital	23,313	19,764	`0</td>`

Retrieve the statement of cash flows

cashFlow2013 <- st2013$StatementOfCashFlowsIndirect
cashFlow2014 <- st2014$StatementOfCashFlowsIndirect
cf_all <- merge(cashFlow2013, cashFlow2014)
print(cf_all, html = TRUE, big.mark = ",", dateFormat = "%Y")

	2014	2013	2012	2011
Cash And Cash Equivalents Period Increase Decrease	-415	3,513	931	-1,446
Net Cash Provided By Used In Operating Activities	0	0	0	37,529
Net Income Loss	39,510	37,037	41,733	25,922
Net Income (Loss) Attributable to Parent	39,510	37,037	41,733	25,922
Depreciation Amortization And Accretion Net	7,946	6,757	3,277	1,814
Depreciation, Amortization and Accretion, Net	7,946	6,757	3,277	1,814
Share Based Compensation	2,863	2,253	1,740	1,168
Share-based Compensation	2,863	2,253	1,740	1,168
Deferred Income Tax Expense Benefit	2,347	1,141	4,405	2,868
Deferred Income Tax Expense (Benefit)	2,347	1,141	4,405	2,868
Increase Decrease In Accounts Receivable	4,232	2,172	5,551	-143
Increase (Decrease) in Accounts Receivable	4,232	2,172	5,551	-143
Increase Decrease In Inventories	`76</td>`	`973</td>`	`15</td>`	-275
Net Cash Provided by (Used in) Operating Activities, Continuing Operations	59,713	53,666	50,856	0
Increase (Decrease) in Inventories	`76</td>`	`973</td>`	`15</td>`	-275
Increase Decrease In Other Receivables	2,220	-223	1,414	1,934
Increase (Decrease) in Other Receivables	2,220	-223	1,414	1,934
Increase Decrease In Other Operating Assets	-167	-1,080	3,162	1,391
Increase (Decrease) in Other Operating Assets	-167	-1,080	3,162	1,391
Increase Decrease In Accounts Payable	5,938	2,340	4,467	2,515
Increase (Decrease) in Accounts Payable	5,938	2,340	4,467	2,515
Increase Decrease In Deferred Revenue	1,460	1,459	2,824	1,654
Increase (Decrease) in Deferred Revenue	1,460	1,459	2,824	1,654
Increase Decrease In Other Operating Liabilities	6,010	4,521	2,552	4,495
Increase (Decrease) in Other Operating Liabilities	6,010	4,521	2,552	4,495
Net Cash Provided By Used In Investing Activities	0	0	0	-40,419
Payments To Acquire Available For Sale Securities	217,128	148,489	151,232	102,317
Payments to Acquire Available-for-sale Securities	217,128	148,489	151,232	102,317
Proceeds From Maturities Prepayments And Calls Of Available For Sale Securities	18,810	20,317	13,035	20,437
Proceeds from Maturities, Prepayments and Calls of Available-for-sale Securities	18,810	20,317	13,035	20,437
Proceeds From Sale Of Available For Sale Securities	189,301	104,130	99,770	49,416
Proceeds from Sale of Available-for-sale Securities	189,301	104,130	99,770	49,416
Payments To Acquire Businesses Net Of Cash Acquired	3,765	`496</td>`	`350</td>`	`244</td>`
Net Cash Provided by (Used in) Investing Activities, Continuing Operations	-22,579	-33,774	-48,227	0
Payments to Acquire Businesses, Net of Cash Acquired	3,765	`496</td>`	`350</td>`	`244</td>`
Payments To Acquire Productive Assets	9,571	8,165	8,295	4,260
Payments to Acquire Productive Assets	9,571	8,165	8,295	4,260
Payments To Acquire Intangible Assets	`242</td>`	`911</td>`	1,107	3,192
Payments to Acquire Intangible Assets	`242</td>`	`911</td>`	1,107	3,192
Payments For Proceeds From Other Investing Activities	`-16</td>`	`160</td>`	`48</td>`	`259</td>`
Payments for (Proceeds from) Other Investing Activities	`-16</td>`	`160</td>`	`48</td>`	`259</td>`
Net Cash Provided By Used In Financing Activities	0	0	0	1,444
Proceeds From Issuance Of Common Stock	`730</td>`	`530</td>`	`665</td>`	`831</td>`
Proceeds from Issuance of Common Stock	`730</td>`	`530</td>`	`665</td>`	`831</td>`
Excess Tax Benefit From Share Based Compensation Financing Activities	`739</td>`	`701</td>`	1,351	1,133
Excess Tax Benefit from Share-based Compensation, Financing Activities	`739</td>`	`701</td>`	1,351	1,133
Payments Related To Tax Withholding For Share Based Compensation	1,158	1,082	1,226	`520</td>`
Payments Related to Tax Withholding for Share-based Compensation	1,158	1,082	1,226	`520</td>`
Net Cash Provided by (Used in) Financing Activities, Continuing Operations	-37,549	-16,379	-1,698	0
Payments Of Dividends And Dividend Equivalents On Common Stock And Restricted Stock Units	11,126	10,564	2,488	`0</td>`
Payments of Dividends and Dividend Equivalents on Common Stock and Restricted Stock Units	11,126	10,564	2,488	`0</td>`
Payments For Repurchase Of Common Stock	45,000	22,860	`0</td>`	`0</td>`
Payments for Repurchase of Common Stock	45,000	22,860	`0</td>`	`0</td>`
Proceeds From Issuance Of Long Term Debt	11,960	16,896	`0</td>`	`0</td>`
Proceeds from Issuance of Long-term Debt	11,960	16,896	`0</td>`	`0</td>`
Proceeds from (Repayments of) Commercial Paper	6,306	`0</td>`	`0</td>`	`0</td>`

Merge all statements and check if the balance sheets are merged

st_all <- merge(st2013, st2014)
balance_sheet <- st_all$StatementOfFinancialPositionClassified
bs_simple <- expose(balance_sheet,
                    `Current Assets` = "AssetsCurrent",
                    `Noncurrent Assets` = other("Assets"),
                    `Current Liabilities` = "LiabilitiesCurrent",
                    `Noncurrent Liabilities` = other(c("Liabilities", "CommitmentsAndContingencies")),
                    `Stockholders Equity` = "StockholdersEquity")
print(bs_simple, html = TRUE, big.mark = ",", dateFormat = "%Y")

	2014	2013	2012
Assets	231,839	207,000	176,064
Current Assets	68,531	73,286	57,653
Noncurrent Assets	163,308	133,714	118,411
Liabilities And Stockholders Equity	231,839	207,000	176,064
Current Liabilities	63,448	43,658	38,542
Noncurrent Liabilities	56,844	39,793	19,312
Stockholders Equity	111,547	123,549	118,210

Plots

plot_double_stacked_bar(bs_simple)

plot_double_stacked_bar(bs_simple, by_date = FALSE)

bs_simple_prop <- proportional(bs_simple)
plot_double_stacked_bar(bs_simple_prop)

Data Analysis

The current ratio is a liquidity and efficiency ratio that measures a firm’s ability to pay off its short-term liabilities with its current assets. The current ratio is an important measure of liquidity because short-term liabilities are due within the next year.

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

##         date CurrentRatio
## 1 2012-09-29     1.495849
## 2 2013-09-28     1.678639
## 3 2014-09-27     1.080113

Days sales outstanding (DSO) is a calculation used by a company to estimate their average collection period. It is a financial ratio that illustrates how well a company’s accounts receivables are being managed.

merge(balance_sheet, st_all$StatementOfIncome ) %>% calculate( digits = 2,

    .AccountReceivableLast = lag(AccountsReceivableNetCurrent),
    .AccountReceivableAvg = (.AccountReceivableLast + AccountsReceivableNetCurrent)/2,

    DaysSalesOutstanding = .AccountReceivableAvg / SalesRevenueNet * 365 
  )

##         date DaysSalesOutstanding
## 1 2012-09-29                   NA
## 2 2013-09-28                25.66
## 3 2014-09-27                30.51

Define Calculation - Profit Margins

profit_margins <- calculation(

  Gross_Margin = 
    (SalesRevenueNet -  CostOfGoodsAndServicesSold) / SalesRevenueNet,

  Operating_Margin =
    OperatingIncomeLoss / SalesRevenueNet,

  Net_Margin = 
    NetIncomeLoss / SalesRevenueNet

)

income2013 %>% calculate(calculations = profit_margins, digits = 2)

##         date Gross_Margin Operating_Margin Net_Margin
## 1 2011-09-24         0.40             0.31       0.24
## 2 2012-09-29         0.44             0.35       0.27
## 3 2013-09-28         0.38             0.29       0.22

income2014 %>% calculate(calculations = profit_margins, digits = 2)

##         date Gross_Margin Operating_Margin Net_Margin
## 1 2012-09-29         0.44             0.35       0.27
## 2 2013-09-28         0.38             0.29       0.22
## 3 2014-09-27         0.39             0.29       0.22

Define Calculation - Accounts Receivable / SalesRevenue

merge(balance_sheet, st_all$StatementOfIncome ) %>% calculate( digits = 2,

    .AccountReceivableLast = lag(AccountsReceivableNetCurrent),
    .AccountReceivableAvg = (.AccountReceivableLast + AccountsReceivableNetCurrent)/2,

    quality = .AccountReceivableLast / SalesRevenueNet
  )

##         date quality
## 1 2012-09-29      NA
## 2 2013-09-28    0.06
## 3 2014-09-27    0.07

Over the 3 periods, Apple’s ratio of Accounts Receivable to sales remained consistent year over year. We could not confirm their gift card liability to sales ratio but suspect that it remained constant as well. These two metrics indicates that they most probably did not overstate revenues. Aaple has been fortunate to increase revenues, albeit over the last 3 years, less fruitful companies could manipulate earnings by improperly recognizing deferred revenue. When we take a closer look at Apple’s margins, it is unlikely that they overproduced to spread costs over a larger base of goods to lower COGS. The profit margin declines are most likely due to new product designs. It is not included here, but Apple did in fact increase margins and the stock price reflected this margin expansion.

Another potential red flag is a significant change in revenues attributed to the product mix. In the table below we see consistency across Apple’s product and service segments. The fluctuations in the chart are due to seasonality, not actual material changes in the product mix.

Apple’s SG&A expense increased each year and it appears they the course and continued to investment for future earnings. The accounting quality of their expense reporting indicates that future earning should be consistent with current earnings.

Load industry data and run correlations

capital_structure <- read.csv(curl("https://raw.githubusercontent.com/danielhong98/DATA607/af5de57f3e226c48cb9003897b9673c93752e24a/606FinalProjectData.csv"), header = TRUE)
kable(head(capital_structure,10))

Industry	Firms	Beta	CostofEquity	EquityPct	StdevStock	CostofDebt	TaxRate	AftertaxCostofDebt	DebtPct	CostofCapital	NetIncomeGrowth	RevenueGrowth
Advertising	44	1.08	0.087	0.640	0.710	0.045	0.039	0.027	0.360	0.066	0.008	-0.005
Aerospace/Defense	92	1.33	0.103	0.830	0.469	0.035	0.135	0.021	0.170	0.089	0.126	0.047
Air Transport	20	1.27	0.099	0.589	0.520	0.040	0.183	0.024	0.411	0.068	0.254	0.094
Apparel	63	1.06	0.086	0.782	0.606	0.040	0.138	0.024	0.218	0.073	0.153	0.147
Auto & Truck	19	0.96	0.080	0.439	0.337	0.035	0.080	0.021	0.562	0.047	0.148	0.280
Auto Parts	65	1.29	0.100	0.752	0.551	0.040	0.096	0.024	0.248	0.081	0.245	0.053
Bank (Money Center)	9	1.11	0.089	0.316	0.328	0.035	0.258	0.021	0.684	0.043	0.094	0.122
Banks (Regional)	644	0.51	0.053	0.560	0.289	0.035	0.241	0.021	0.440	0.039	0.181	0.133
Beverage (Alcoholic)	22	0.94	0.079	0.846	0.585	0.040	0.113	0.024	0.154	0.071	0.201	0.095
Beverage (Soft)	43	1.15	0.092	0.813	0.546	0.040	0.060	0.024	0.187	0.079	0.037	0.088

# correlation of Stock price standard deviation to revenue growth
cor(capital_structure$StdevStock,capital_structure$RevenueGrowth)

## [1] 0.09179823

# correlation of cost of Equity to revenue growth
cor(capital_structure$CostofEquity,capital_structure$RevenueGrowth)

## [1] -0.0449382

Use the lm function to fit the linear model

# linear model using DebtPct
m1 <- lm(RevenueGrowth ~ StdevStock, data = capital_structure)

# linear model using CostofCapital
m2 <- lm(RevenueGrowth ~ CostofEquity, data = capital_structure)

The first argument in the function lm is a formula that takes the form y ~ x. We want the linear model of percentage standard deviation for m1 and Costofequity for m2 as a function of revenue growth.

The output of lm is an object that contains the information we need from the linear model that was just fit. We summarize the information:

summary(m1)

## 
## Call:
## lm(formula = RevenueGrowth ~ StdevStock, data = capital_structure)
## 
## Residuals:
##       Min        1Q    Median        3Q       Max 
## -0.232779 -0.040736 -0.000706  0.036862  0.272248 
## 
## Coefficients:
##             Estimate Std. Error t value Pr(>|t|)  
## (Intercept)  0.06552    0.02855   2.295    0.024 *
## StdevStock   0.04845    0.05479   0.884    0.379  
## ---
## Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
## 
## Residual standard error: 0.07595 on 92 degrees of freedom
## Multiple R-squared:  0.008427,   Adjusted R-squared:  -0.002351 
## F-statistic: 0.7819 on 1 and 92 DF,  p-value: 0.3789

summary(m2)

## 
## Call:
## lm(formula = RevenueGrowth ~ CostofEquity, data = capital_structure)
## 
## Residuals:
##       Min        1Q    Median        3Q       Max 
## -0.228186 -0.042992 -0.003003  0.031637  0.283230 
## 
## Coefficients:
##              Estimate Std. Error t value Pr(>|t|)  
## (Intercept)   0.10892    0.04502   2.420   0.0175 *
## CostofEquity -0.20127    0.46649  -0.431   0.6671  
## ---
## Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
## 
## Residual standard error: 0.07619 on 92 degrees of freedom
## Multiple R-squared:  0.002019,   Adjusted R-squared:  -0.008828 
## F-statistic: 0.1862 on 1 and 92 DF,  p-value: 0.6671

In the “Coefficients” table above; the first column displays the linear model’s y-intercept and the coefficient of Stdev for m1 and CostofEquity for m2. With this table, we can write down the least squares regression line for the linear model:

for m1:
\[ \hat{y} = 0.06552 + 0.04845 * StdevStock \]

for m2:
\[ \hat{y} = 0.10892 - 0.20127 * CostofCapital \]

The \(R^2\) value represents the proportion of variability in the response variable that is explained by the explanatory variable. For m1, 0.84% of the variability in RevenueGrowth is explained by StdevStock. For m2, 0.20% variablility is explained by CostofCapital.

We see a positive relationship in StdevStock, for every 1 percent of DebtPct we would see an increase of 0.04845 increase in revenue growth. We see a negative relationship in CostofCapital, for every 1 percent of CostofCapital we would see a decrease of 0.20127 in revenue growth.

To assess whether the linear model is reliable, we need to check for (1) linearity, (2) nearly normal residuals, and (3) constant variability.

plot(m1$residuals ~ capital_structure$StdevStock)
abline(h = 0, lty = 3)

plot(m2$residuals ~ capital_structure$CostofEquity)
abline(h = 0, lty = 3)

The function abline plots a line based on its slope and intercept.

plot(capital_structure$RevenueGrowth ~ capital_structure$StdevStock)
abline(m1)

plot(capital_structure$RevenueGrowth ~ capital_structure$CostofEquity)
abline(m2)

Conclusion

We looked at a process to help with our analytic approach, but this just scratches the surface. We can look at many other indicators to examine the quality of Property, Plant and Equipment (PPE), Quality of credit provisions and tax reporting, additional profitability measures (return on assets, return on capital, return on equity, return on net operating assets), and asset turnover. The statistical analysis is included for illustration. It gives an overall assessment for a basket of companies, which Apple is included but futher analysis can be done here as well.

References

https://www.xbrl.org https://www.osha.gov/pls/imis/sicsearch.html https://cran.r-project.org/web/packages/XBRL https://github.com/bergant/XBRLFiles http://people.stern.nyu.edu/adamodar/New_Home_Page/data.html