Management accounting with balance sheet and income statement in R

June 2, 2019
By

(This article was first published on R Programming – DataScience+, and kindly contributed to R-bloggers)

Are you interested in guest posting? Publish at DataScience+ via your editor (i.e., RStudio).

Category

Tags

The demand for data analysis/science and data management are increasing in the field of management accounting. In this article, you learn how to get data for management accounting with the balance sheet and income statement in R. Furthermore you learn how to prepare the balance sheet and income statement in R and how to export it to Excel, which is a highly used tool in the management accounting field.

Load packages into R

First we read data pacakages into our R library:

# Management accounting & getting data 
library(finreportr)
# Dat management
library(reshape2)
library(xlsx)
# Data tables
library(htmlTable)

Get data for financial statements in R

Now it is time to get data for financial statements. You can get data in XRL format for any company from US through the package finreportr.

First let us get data for the balance sheet of 2017 from Google:

# Get balance sheet statement
balance_sheet2017_goo <- GetBalanceSheet("goog", 2017)
head(balance_sheet2017_goo)
                                        Metric Units      Amount startDate    endDate
1 Cash and Cash Equivalents, at Carrying Value   usd 18898000000       2013-12-31
2 Cash and Cash Equivalents, at Carrying Value   usd 18347000000       2014-12-31
3 Cash and Cash Equivalents, at Carrying Value   usd 16549000000       2015-12-31
4 Cash and Cash Equivalents, at Carrying Value   usd 12918000000       2016-12-31
5       Available-for-sale Securities, Current   usd 56517000000       2015-12-31
6       Available-for-sale Securities, Current   usd 73415000000       2016-12-31

Now let us get data for the income statement of Google for the year 2017:

# Get income statement
Income2017_goo <- GetIncome("goog", 2017)
head(Income2017_goo)
           Metric Units      Amount  startDate    endDate
1        Revenues   usd 66001000000 2014-01-01 2014-12-31
2        Revenues   usd 74989000000 2015-01-01 2015-12-31
3        Revenues   usd 90272000000 2016-01-01 2016-12-31
4 Cost of Revenue   usd 25691000000 2014-01-01 2014-12-31
5 Cost of Revenue   usd 28164000000 2015-01-01 2015-12-31
6 Cost of Revenue   usd 35138000000 2016-01-01 2016-12-31

Do Data management – prepare the financial statements for wide format

In order to analyze the balance sheet and the income statement in a HTML table, we need to make the dataset in wide format and do some data management. Let us start with the balance sheet:

# Data management and prepare the balance sheet for a html table
balance_sheet2017_goo.W <- dcast(balance_sheet2017_goo, Metric + Units ~ endDate, value.var="Amount")
balance_sheet2017_goo.W <- balance_sheet2017_goo.W[c(1,5:6)]
balance_sheet2017_goo.W
31   2016-12-31
1                                                       Accounts Payable, Current   1931000000   2041000000
2                                               Accounts Receivable, Net, Current  11556000000  14137000000
3                                                   Accrued Income Taxes, Current    302000000    554000000
4                                                    Accrued Liabilities, Current   4768000000   6144000000
5                                                           Accrued Revenue Share   2329000000   2942000000
6                       Accumulated Other Comprehensive Income (Loss), Net of Tax  -1874000000  -2402000000
7                                                                          Assets 147461000000 167497000000
8                                                                 Assets, Current  90114000000 105408000000
9                                          Available-for-sale Securities, Current  56517000000  73415000000
10                                   Cash and Cash Equivalents, at Carrying Value  16549000000  12918000000
11                             Cash, Cash Equivalents, and Short-term Investments  73066000000  86333000000
12                                                  Commitments and Contingencies                  
13                            Common Stocks, Including Additional Paid in Capital  32982000000  36307000000
14 Convertible Preferred Stock, Nonredeemable or Redeemable, Issuer Option, Value            0            0
15                                                                  Debt, Current   3225000000            0
16                                                      Deferred Revenue, Current    788000000   1099000000
17                                                   Deferred Revenue, Noncurrent    151000000    202000000
18                                           Deferred Tax Assets, Net, Noncurrent    251000000    383000000
19                                      Deferred Tax Liabilities, Net, Noncurrent    189000000    226000000
20                     Deposits Received for Securities Loaned, at Carrying Value   2428000000            0
21                                          Employee-related Liabilities, Current   3539000000   3976000000
22                                                                       Goodwill  15869000000  16468000000
23                                               Income Taxes Receivable, Current   1903000000     95000000
24                                    Intangible Assets, Net (Excluding Goodwill)   3847000000   3307000000
25                                                                 Inventory, Net    491000000    268000000
26                                                                    Liabilities  27130000000  28461000000
27                                                         Liabilities and Equity 147461000000 167497000000
28                                                           Liabilities, Current  19310000000  16756000000
29                              Liability for Uncertain Tax Positions, Noncurrent   3663000000   4677000000
30                                   Long-term Debt and Capital Lease Obligations   1995000000   3935000000
31                                                  Other Liabilities, Noncurrent   1822000000   2665000000
32                                                    Other Long-term Investments   5183000000   5878000000
33                        Prepaid Revenue Share Expenses And Other Assets Current   2648000000   4575000000
34                     Prepaid Revenue Share Expenses And Other Assets Noncurrent   3181000000   1819000000
35                                             Property, Plant and Equipment, Net  29016000000  34234000000
36                                 Receivable Under Reverse Repurchase Agreements    450000000            0
37                                        Retained Earnings (Accumulated Deficit)  89223000000 105131000000
38                                    Stockholders' Equity Attributable to Parent 120331000000 139036000000

Let us do the same thing for the income statement:

# Data management and prepare the income statement for a html table
Income2017_goo.W <- dcast(Income2017_goo , Metric + Units ~ endDate, value.var="Amount")
Income2017_goo.W <- Income2017_goo.W[c(1,3:5)]
Income2017_goo.W 
Metric  2014-12-31  2015-12-31  2016-12-31
1  Cost of Revenue 25691000000 28164000000 35138000000
2  Costs and Expenses 49505000000 55629000000 66556000000
3  General and Administrative Expense  5851000000  6136000000  6985000000
4  Income (Loss) from Continuing Operations Attributable to Parent 13620000000 16348000000 19478000000
5  Income (Loss) from Continuing Operations before Equity Method Investments, Income Taxes, Extraordinary Items, Noncontrolling Interest 17259000000 19651000000 24150000000
6  Income (Loss) from Discontinued Operations, Net of Tax, Including Portion Attributable to Noncontrolling Interest   516000000           0           0
7  Income Tax Expense (Benefit)  3639000000  3303000000  4672000000
8  Net Income (Loss) Attributable to Parent 14136000000 16348000000 19478000000
9  Net Income (Loss) Available to Common Stockholders, Basic 14136000000 15826000000 19478000000
10 Nonoperating Income (Expense)   763000000   291000000   434000000
11 Operating Income (Loss) 16496000000 19360000000 23716000000
12 Preferred Stock Dividends and Other Adjustments           0   522000000           0
13 Research and Development Expense  9832000000 12282000000 13948000000
14 Revenues 66001000000 74989000000 90272000000
15 Selling and Marketing Expense  8131000000  9047000000 10485000000

HTML table of balance sheet and income statement

Now we can make a HTML table of the balance sheet and the income statement. Let us start with the balance sheet:

# Balance sheet HTML table
htmlTable(balance_sheet2017_goo.W)

Let us do the same coding for HTML table of the income statement:

# Income statement HTML table
htmlTable(Income2017_goo.W)

Export balance sheet and income statement to Excel

It is also possible to export the balance sheet and income statement to Excel. Let us start with exporting the balance sheet to excel:

# Export balance sheet to Excel
write.xlsx(balance_sheet2017_goo.W), "/BaanceSheet.xlsx") # write sheet

Let us also export the income statement to Excel:

# Export income statement to Excel
write.xlsx(Income2017_goo.W), "/IncomeStatementSheet.xlsx") # write sheet

References

  1. Using xlsx in R – CRAN.R-project.org
  2. Using htmlTable in R – CRAN.R-project.org
  3. Using finreportr in R – CRAN.R-project.org
  4. Using reshape2 in R – CRAN.R-project.org

Related Post

To leave a comment for the author, please follow the link and comment on their blog: R Programming – DataScience+.

R-bloggers.com offers daily e-mail updates about R news and tutorials on topics such as: Data science, Big Data, R jobs, visualization (ggplot2, Boxplots, maps, animation), programming (RStudio, Sweave, LaTeX, SQL, Eclipse, git, hadoop, Web Scraping) statistics (regression, PCA, time series, trading) and more...



If you got this far, why not subscribe for updates from the site? Choose your flavor: e-mail, twitter, RSS, or facebook...

Comments are closed.

Search R-bloggers

Sponsors

Never miss an update!
Subscribe to R-bloggers to receive
e-mails with the latest R posts.
(You will not see this message again.)

Click here to close (This popup will not appear again)