Management accounting and controlling in R

October 29, 2018
By

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

    Categories

    1. Data Management

    Tags

    1. Data Visualisation
    2. R Programming
    3. Tips & Tricks

    In this article you learn how to make visualizations & tables for management accounting & controlling in R.

    Read packages into R library

    First we need to read the packages into our R library. The main package used for management accounting & controlling is finstr:

    # Management accounting & controlling
    library(finstr)
    # Data tables
    library(knitr)
    library(kableExtra)
    library(flextable)
    library(htmlTable)
    library(htmlwidgets)
    library(DT)
    library(xlsx)
    # Data management
    library(skimr)
    library(dplyr)
    library(tidyverse)
    library(lubridate)
    # Visualization
    library(ggplot2)
    library(waterfalls)
    library(plotly)
    

    You need to install the finstr with github instead of cran:

    # Installation
    library(devtools)
    install_github("bergant/finstr")
    

    Getting data for analysis

    Now it is time to get the data for the analysis:

    # XBRL (Apple 10-K report):
    url2014 <- "https://www.sec.gov/Archives/edgar/data/320193/000119312514383437/aapl-20140927.xml"
    url2013 <- "https://www.sec.gov/Archives/edgar/data/320193/000119312513416534/aapl-20130928.xml"
    old_o <- options(stringsAsFactors = FALSE)
    options(old_o)
    

    Display financial statments in a table

    After getting the data it is time to make the financial statements for the management accounting & controlling analysis:

    # Prepare statements
    st2013 <- xbrl_get_statements(xbrl_data_aapl2013)
    st2014 <- xbrl_get_statements(xbrl_data_aapl2014)
    st2014
    # Get a single statement:
    balance_sheet2013 <- st2013$StatementOfFinancialPositionClassified
    balance_sheet2014 <- st2014$StatementOfFinancialPositionClassified
    income2013 <- st2013$StatementOfIncome
    income2014 <- st2014$StatementOfIncome
    balance_sheet2013
    balance_sheet2014
    

    The above balance_sheet2014 gives us the following txt table:

    balance_sheet2014
    Financial statement: 2 observations from 2013-09-28 to 2014-09-27 
     Element                                             2014-09-27 2013-09-28
     Assets =                                            231839     207000    
     + AssetsCurrent =                                    68531      73286    
       + CashAndCashEquivalentsAtCarryingValue            13844      14259    
       + AvailableForSaleSecuritiesCurrent                11233      26287    
       + AccountsReceivableNetCurrent                     17460      13102    
       + InventoryNet                                      2111       1764    
       + DeferredTaxAssetsNetCurrent                       4318       3453    
       + NontradeReceivablesCurrent                        9759       7539    
       + OtherAssetsCurrent                                9806       6882    
     + AvailableForSaleSecuritiesNoncurrent              130162     106215    
     + PropertyPlantAndEquipmentNet                       20624      16597    
     + Goodwill                                            4616       1577    
     + IntangibleAssetsNetExcludingGoodwill                4142       4179    
     + OtherAssetsNoncurrent                               3764       5146    
     LiabilitiesAndStockholdersEquity =                  231839     207000    
     + Liabilities =                                     120292      83451    
       + LiabilitiesCurrent =                             63448      43658    
         + AccountsPayableCurrent                         30196      22367    
         + AccruedLiabilitiesCurrent                      18453      13856    
         + DeferredRevenueCurrent                          8491       7435    
         + CommercialPaper                                 6308          0    
       + DeferredRevenueNoncurrent                         3031       2625    
       + LongTermDebt                                     28987      16960    
       + OtherLiabilitiesNoncurrent                       24826      20208    
     + CommitmentsAndContingencies                            0          0    
     + StockholdersEquity =                              111547     123549    
       + CommonStocksIncludingAdditionalPaidInCapital     23313      19764    
       + RetainedEarningsAccumulatedDeficit               87152     104256    
       + AccumulatedOtherComprehensiveIncomeLossNetOfTax   1082       -471
    

    If you want to have the above balance_sheet2014 in a html table use the following code:

    # Balance sheet HTML table
    BS_2014_table=print(balance_sheet2014, html = TRUE, big.mark = ".", dateFormat = "%Y", align="l", align.header="l", rnames = "FALSE")
    htmlTable(BS_2014_table)
    

    Export statements to Excel

    If you need to export the statement to Excel, use the following code:

    # Export sheet to Excel
    write.xlsx(as.data.frame(t(balance_sheet2014)), "/ExcelSheet.xlsx") # write sheet

    Controlling for errors in statements

    Next when you need to do some controlling of the statements the following coding is very handy:

    # Validate balance sheet statements
    check <- check_statement(balance_sheet2014)
    check
    Number of errors:  0 
    Number of elements in errors:  0 
    
    # In case of errors:
    check_statement(
      within(balance_sheet2014, InventoryNet <- InventoryNet * 2))
    
    Number of errors:  2 
    Number of elements in errors:  1 
    
    Element: AssetsCurrent  =  + CashAndCashEquivalentsAtCarryingValue + AvailableForSaleSecuritiesCurrent + AccountsReceivableNetCurrent + InventoryNet + DeferredTaxAssetsNetCurrent + NontradeReceivablesCurrent + OtherAssetsCurrent 
            date   original calculated      error
    3 2013-09-28 7.3286e+10 7.5050e+10 -1.764e+09
    4 2014-09-27 6.8531e+10 7.0642e+10 -2.111e+09
    

    If you need to check a specific part of the specific statement you can:

    # Check operating income from income statement
    check <- check_statement(income2014, element_id = "OperatingIncomeLoss")
    check
    Number of errors:  0 
    Number of elements in errors:  0 
    

    Merge statements from different time periods

    If you need to merge e.g. balance sheet statements from different time periods use:

    #Merge statements from different periods
    balance_sheet <- merge( balance_sheet2013, balance_sheet2014)
    balance_sheet 
    Financial statement: 3 observations from 2012-09-29 to 2014-09-27 
     Element                                             2014-09-27 2013-09-28 2012-09-29
     Assets =                                            231839     207000     176064    
     + AssetsCurrent =                                    68531      73286      57653    
       + CashAndCashEquivalentsAtCarryingValue            13844      14259      10746    
       + AvailableForSaleSecuritiesCurrent                11233      26287      18383    
       + AccountsReceivableNetCurrent                     17460      13102      10930    
       + InventoryNet                                      2111       1764        791    
       + DeferredTaxAssetsNetCurrent                       4318       3453       2583    
       + NontradeReceivablesCurrent                        9759       7539       7762    
       + OtherAssetsCurrent                                9806       6882       6458    
     + AvailableForSaleSecuritiesNoncurrent              130162     106215      92122    
     + PropertyPlantAndEquipmentNet                       20624      16597      15452    
     + Goodwill                                            4616       1577       1135    
     + IntangibleAssetsNetExcludingGoodwill                4142       4179       4224    
     + OtherAssetsNoncurrent                               3764       5146       5478    
     LiabilitiesAndStockholdersEquity =                  231839     207000     176064    
     + Liabilities =                                     120292      83451      57854    
       + LiabilitiesCurrent =                             63448      43658      38542    
         + AccountsPayableCurrent                         30196      22367      21175    
         + AccruedLiabilitiesCurrent                      18453      13856      11414    
         + DeferredRevenueCurrent                          8491       7435       5953    
         + CommercialPaper                                 6308          0          0    
       + DeferredRevenueNoncurrent                         3031       2625       2648    
       + LongTermDebt                                     28987      16960          0    
       + OtherLiabilitiesNoncurrent                       24826      20208      16664    
     + CommitmentsAndContingencies                            0          0          0    
     + StockholdersEquity =                              111547     123549     118210    
       + CommonStockValue                                     0          0      16422    
       + RetainedEarningsAccumulatedDeficit               87152     104256     101289    
       + AccumulatedOtherComprehensiveIncomeLossNetOfTax   1082       -471        499    
       + CommonStocksIncludingAdditionalPaidInCapital     23313      19764          0
    

    If you need to merge all statements you can do this:

    # merge all statements
    st_all <- merge( st2013, st2014 )
    

    If you need to merge multiple and specific types of statements you can do this:

    # Merge muliple and specific types of statements
    merge(st_all$StatementOfFinancialPositionClassified, 
    st_all$StatementOfIncome )
    

    Calculate financial ratios

    Now let us calculate some financial rations and display them with visualizations:

    # Calculate financial ratios
    FR% transmute(date = endDate, 
    CurrentRatio = AssetsCurrent / LiabilitiesCurrent)
    # Chart on financial ratios
    FR %>% ggplot(aes(x=date, y= CurrentRatio)) + geom_col(color = "blue",fill ="green") 
    ggplotly(p = ggplot2::last_plot())
    

    The above coding gives us the following financial ratio visualization:

    Let us calculate the financial ratio using finstr:

    # Calculate financial ratios By using finstr::calculat
    balance_sheet$endDate<-as.POSIXct(balance_sheet$endDate, format="%Y-%m-%d")
    balance_sheet$endDate1<-year(balance_sheet$endDate)
    balance_sheet.num<-as.data.frame(lapply(balance_sheet[c(5:35)], as.numeric)) # Make variables numeric for cor analysis
    names(balance_sheet.num)[31]<-"endDate"
    colnames(balance_sheet.num)
    # Calculations of financial ratios
    FRF% calculate(digits = 2,
    Current_Ratio = AssetsCurrent / LiabilitiesCurrent,Quick_Ratio =  
    (CashAndCashEquivalentsAtCarryingValue + AvailableForSaleSecuritiesCurrent +
    AccountsReceivableNetCurrent) / LiabilitiesCurrent)
    colnames(FRF)
    # Chart on financial ratios
    FRF %>% ggplot(aes(x=date, y= Quick_Ratio)) + geom_col(color = "blue",fill ="green")
    

    The above coding gives us the following financial ratios visualization:

    Calculate short financial statements

    It is also possible to make a shorter and easier financial statement to enterpret:

    # Short financial statement
    # Assets & liabilities
    expose( balance_sheet,
            # Assets
            `Current Assets` = "AssetsCurrent",
            `Noncurrent Assets` = other("Assets"),      
            # Liabilites and equity
            `Current Liabilities` = "LiabilitiesCurrent",
            `Noncurrent Liabilities` = other(c("Liabilities", "CommitmentsAndContingencies")),
            `Stockholders Equity` = "StockholdersEquity")
    Financial statement: 3 observations from 2012-09-29 to 2014-09-27 
     Element                             2014-09-27 2013-09-28 2012-09-29
     Assets =                            231839     207000     176064    
     + Current.Assets                     68531      73286      57653    
     + Noncurrent.Assets                 163308     133714     118411    
     LiabilitiesAndStockholdersEquity =  231839     207000     176064    
     + Current.Liabilities                63448      43658      38542    
     + Noncurrent.Liabilities             56844      39793      19312    
     + Stockholders.Equity               111547     123549     118210   
    
    # Short financial statement
    #Tangible and then intangible assets:
    expose( balance_sheet,
            # Assets
            `Tangible Assets` = 
              "Assets" %without% c("Goodwill", "IntangibleAssetsNetExcludingGoodwill"),
            `Intangible Assets` = other("Assets"),
            # Liabilites and equity
            `Liabilities` = c("Liabilities", "CommitmentsAndContingencies"),
            `Stockholders Equity` = "StockholdersEquity")
    Financial statement: 3 observations from 2012-09-29 to 2014-09-27 
     Element                             2014-09-27 2013-09-28 2012-09-29
     Assets =                            231839     207000     176064    
     + Tangible.Assets                   223081     201244     170705    
     + Intangible.Assets                   8758       5756       5359    
     LiabilitiesAndStockholdersEquity =  231839     207000     176064    
     + Liabilities                       120292      83451      57854    
     + Stockholders.Equity               111547     123549     118210  
    

    You can also calculate lagged difference of financial statements:

    # Calculate lagged difference
    diff(balance_sheet)
    Financial statement: 2 observations from 2013-09-28 to 2014-09-27 
     Element                                             2014-09-27 2013-09-28
     Assets =                                             24839      30936    
     + AssetsCurrent =                                    -4755      15633    
       + CashAndCashEquivalentsAtCarryingValue             -415       3513    
       + AvailableForSaleSecuritiesCurrent               -15054       7904    
       + AccountsReceivableNetCurrent                      4358       2172    
       + InventoryNet                                       347        973    
       + DeferredTaxAssetsNetCurrent                        865        870    
       + NontradeReceivablesCurrent                        2220       -223    
       + OtherAssetsCurrent                                2924        424    
     + AvailableForSaleSecuritiesNoncurrent               23947      14093    
     + PropertyPlantAndEquipmentNet                        4027       1145    
     + Goodwill                                            3039        442    
     + IntangibleAssetsNetExcludingGoodwill                 -37        -45    
     + OtherAssetsNoncurrent                              -1382       -332    
     LiabilitiesAndStockholdersEquity =                   24839      30936    
     + Liabilities =                                      36841      25597    
       + LiabilitiesCurrent =                             19790       5116    
         + AccountsPayableCurrent                          7829       1192    
         + AccruedLiabilitiesCurrent                       4597       2442    
         + DeferredRevenueCurrent                          1056       1482    
         + CommercialPaper                                 6308          0    
       + DeferredRevenueNoncurrent                          406        -23    
       + LongTermDebt                                     12027      16960    
       + OtherLiabilitiesNoncurrent                        4618       3544    
     + CommitmentsAndContingencies                            0          0    
     + StockholdersEquity =                              -12002       5339    
       + CommonStockValue                                     0     -16422    
       + RetainedEarningsAccumulatedDeficit              -17104       2967    
       + AccumulatedOtherComprehensiveIncomeLossNetOfTax   1553       -970    
       + CommonStocksIncludingAdditionalPaidInCapital      3549      19764
    

    If you want to make a HTML table of a short financial sttement, use the below coding:

    # Save short financial statement
    bs_simple <- expose( balance_sheet,          
                         # Assets
                         `Current Assets` = "AssetsCurrent",
                         `Noncurrent Assets` = other("Assets"),
                         # Liabilites and equity
                         `Current Liabilities` = "LiabilitiesCurrent",
                         `Noncurrent Liabilities` = other(c("Liabilities", "CommitmentsAndContingencies")),
                         `Stockholders Equity` = "StockholdersEquity")
    # Print as a HTML table
    bs_simple_tab=print(bs_simple, html = TRUE, big.mark = ".", dateFormat = "%Y")
    htmlTable(bs_simple_tab)
    

    Let us make a stacked bar chart of the short financial statement:

    # Stacked bar chart with plotly
    plot_double_stacked_bar(bs_simple)
    ggplotly(p = ggplot2::last_plot())
    

    The above coding gives us the following stacked bar chart with plotly:

    It is also possible to make a side-by-side stacked bar chart:

    # Side-by-side stacked bar chart
    plot_double_stacked_bar(bs_simple, by_date = FALSE)
    ggplotly(p = ggplot2::last_plot())
    

    The above coding gives us the following side-by-side stacked bar chart:

    Lastly it is also possible to make a proportional stacked bar chart:

    # Using proportional stacked bar chart
    bs_simple_prop <- proportional(bs_simple)
    plot_double_stacked_bar(bs_simple_prop)
    ggplotly(p = ggplot2::last_plot())
    

    The above coding gives us the following proportional stacked bar chart:

    References

    1. Using knitr in R – CRAN.R-project.org
    2. Using kableExtra in R – CRAN.R-project.org
    3. Using flextable in R – CRAN.R-project.org
    4. Using htmlwidgets in R – CRAN.R-project.org
    5. Using DT in R – CRAN.R-project.org
    6. Using xlsx in R – CRAN.R-project.org
    7. Using skimr in R – CRAN.R-project.org
    8. Using tidyverse in R – CRAN.R-project.org
    9. Using lubridate in R – CRAN.R-project.org
    10. Using dplyr in R – CRAN.R-project.org
    11. Using ggplot2 in R – CRAN.R-project.org
    12. Using plotly in R – CRAN.R-project.org

    Related Post

    1. Use R with Excel: Importing and Exporting Data
    2. Combining data in R: the skill of merging, joining, and stacking
    3. Efficient data management and SQL data selection in R
    4. Essential data cleaning for ad-hoc tasks in R
    5. Proteomics Data Analysis (2/3): Data Filtering and Missing Value Imputation

    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)