--- title: "Exercise template for 'Financial statements: A second look'" author: Ian Gow format: html bibliography: book.bib --- We have included a bibliography entry in the header information here. The file for this can be found on [the `far_templates` page](https://github.com/iangow/far_templates/blob/main/README.md). ```{r} #| include: false library(dplyr, warn.conflicts = FALSE) library(DBI) library(ggplot2) ``` ```{r} #| include: false db <- dbConnect(RPostgres::Postgres(), bigint = "integer") funda <- tbl(db, Id(schema = "comp", table = "funda")) company <- tbl(db, Id(schema = "comp", table = "company")) funda_mod <- funda |> filter(indfmt == "INDL", datafmt == "STD", consol == "C", popsrc == "D") ``` ## Within-statement articulation ```{r} #| include: false na_sample_firm_years <- funda_mod |> filter(gvkey == "008902", datadate >= "2000-01-01") |> select(gvkey, datadate) ``` ```{r} #| include: false tol <- 1e-3 funda_na_fixed <- funda_mod |> select(gvkey, datadate, act, ppent, ivaeq, ivao, intan, ao, at, dc, aox, che, rect, invt, aco) |> mutate(at_calc = coalesce(act, 0) + coalesce(ppent, 0) + coalesce(ivaeq, 0) + coalesce(ivao, 0) + coalesce(intan, 0) + coalesce(ao, 0), at_diff = at_calc - at, balance = abs(at_diff) < tol) ``` ### Exercises 1. What is the value in `funda_na_fixed` of `at` when `balance` is `NA`? Is this surprising? 2. Write code to calculate `decade`, the decade in which `datadate` for each observation falls. (Hint: The functions `floor()` and `year()` and the number `10` may be helpful.) 3. Are there any cases in `funda_na_fixed` where `at_calc` is greater than zero and `at` is `NA`? Which decades are these mostly found in? If you were doing research with these data, how might you handle these cases? 4. Consider the firm with `gvkey` equal to `016476`. Write code to obtain the company name and CIK from the `company` table defined above. Using that CIK, find the 10-K filing for the year ending November 2003 on [the SEC's website.](https://www.sec.gov/edgar/searchedgar/companysearch.html) (*Note*: The 10-K will be released some time after `2003-11-30`.) 5. Looking at the 10-K you just found, what seems to be going on with the observation below? ```{r} funda_na_fixed |> filter(gvkey == "016476", datadate == "2003-11-30") |> select(datadate, at, at_calc, at_diff, act, ppent, intan, dc, aox) |> collect() ``` *Hint*: The additional balancing model `ao = dc + aox` may help interpret these amounts. 6. Using the approach above, we can find the 10-K for the observation examined in the following code on the SEC's [EDGAR site.](https://go.unimelb.edu.au/fzw8) What's going on with this case? What's the most significant difference between this case and the one above? *Hint*: The additional balancing model `act = che + rect + invt + aco` may help. ```{r} funda_na_fixed |> filter(gvkey == "145003", datadate == "2001-12-31") |> select(datadate, at, at_calc, at_diff, act, ppent, intan, ivao, ao) |> collect() ``` ## Across-statement articulation ```{r} #| include: false tol <- 0.1 funda_cf_balance <- funda_mod |> select(gvkey, datadate, oancf, ivncf, fincf, exre, chech, che, ch, ivst) |> mutate(across(oancf:exre, \(x) coalesce(x, 0)), chech_calc = oancf + ivncf + fincf + exre, chech_balance = abs(chech_calc - chech) < tol) |> filter(chech_calc != 0, chech_balance) |> group_by(gvkey) |> dbplyr::window_order(datadate) |> mutate(lag_datadate = lag(datadate), d_che = che - lag(che), d_ch = ch - lag(ch)) |> ungroup() |> filter(!is.na(d_che) | !is.na(d_ch)) |> mutate(artic_desc = case_when(abs(d_ch - d_che) < tol & abs(chech - d_che) < tol ~ "Articulates (CHE == CH)", abs(chech - d_che) < tol ~ "Articulates using CHE", abs(chech - d_ch) < tol ~ "Articulates using CH", abs(chech - d_che) >= tol ~ "Does not articulate", abs(chech - d_ch) >= tol ~ "Does not articulate", .default = "Other")) funda_cf_balance |> count(artic_desc) |> collect() ``` ### Exercises 1. In checking cash flow statement articulation, we used the `across()` function (the documentation `? dplyr::across` provides more detail). Rewrite the code to create `funda_na_fixed` above to use a similar approach. Check that you get the same results with respect to `count(balance)` as you get using the original code. 2. Consider the case of American Airlines (GVKEY: `001045`) for the year ended 31 December 2020. Look up the relevant American Airlines 10-K on `sec.gov`. (*Hint*: You can get the CIK from `comp.company`.) What explains the gaps seen in the output below? ```{r} funda_cf_balance |> filter(datadate == "2020-12-31", gvkey == "001045") |> select(gvkey, datadate, chech, d_che, d_ch) |> collect() ``` ## Missing R&D In this section, we discuss @Koh:2015vz, who "investigate whether missing R&D expenditures in financial statements indicates a lack of innovation activity. Patent records reveal that 10.5% of missing R&D firms file and receive patents, which is 14 times greater than zero R&D firms." ### Discussion questions 1. What is the following code doing? @Koh:2015vz use a similar filter. What is their rationale for this? ```{r} included_firms <- company |> mutate(sic = as.integer(sic)) |> filter(!between(sic, 4900, 4999), !between(sic, 6000, 6999)) |> select(gvkey) rd_data <- funda_mod |> semi_join(included_firms, by = "gvkey") |> mutate(missing_rd = is.na(xrd), zero_rd = xrd == 0, year = year(datadate)) |> select(gvkey, datadate, year, xrd, missing_rd, zero_rd, at) |> collect() ``` 2. Is there a balancing model that includes R&D spending? 3. Focusing on years 1980--2019, what have been the trends in the proportion of firms not reporting R&D and the "average" amount of R&D (scaled by assets)?^[At the time of initial writing, 2020 was too incomplete to be meaningful.] (In calculating the "average" does it make sense to use the `mean()` or `median()`?) Speculate as to what might explain these trends. 4. @Koh:2015vz find that many "studies in *The Accounting Review* use R&D in their analysis and code the missing values as zero, implicitly assuming that blank R&D is equal to zero R&D, [while many] articles in the *Journal of Finance* [code] ... the blank values as zero and including a dummy variable to indicate blank R&D firms. ... In contrast, 42% of the studies in the *Strategic Management Journal* use R&D and they take a very different approach, often replacing the missing R&D values with either the industry average R&D, or a historical value from prior years." Why do scholars from different fields make such different choices? In light of @Koh:2015vz, do you think that one approach is more correct than the others? 5. Consider the [10-K filing](https://go.unimelb.edu.au/3cd8) made by IHS Markit Ltd on 2021-01-22. Based on the information in the filing, do you think that IHS Markit engages in research and development activity? Does IHS Markit generate patents? Are there other forms of intellectual property protection that IHS Markit relies on? Does IHS Markit report an amount for research and development expenditure? Who is IHS Markit's external auditor? Do you think that IHS Markit is in violation of GAAP? Or is its reporting choice with respect to R&D a within-GAAP use of reporting discretion? 6. @Koh:2015vz state that "our first set of tests compare patent activity between non-reporting R&D firms and firms that report zero R&D. ... We use both full sample and propensity score matched samples. ... While full sample tests allow for greater external validity, the matched sample tests potentially improve the local treatment effect. ... Based on the propensity score matched sample, our multivariate tests indicate that, on average, non-reporting R&D firms file about 14 times more patent applications than the matched zero R&D firms." In speaking of "treatment effects", @Koh:2015vz implicitly view disclosure of R&D as a treatment variable and patent applications as an outcome. Does this make sense to you? Do you think that this is really what @Koh:2015vz want to do? If not, what is the inference they are trying to draw? 7. What exactly is the take-away from the analysis in section 4.4 of @Koh:2015vz, which uses "the rapid demise of Arthur Andersen (AA) as a quasi-natural experiment"? For example, what is the treatment? What assumptions are needed to generalize from the specific treatment to a treatment of wider applicability? What additional analyses can you suggest that might provide additional assurance that the results reflect a causal effect of interest? 8. What are the suggested implications of the simulation analysis of section 4.5 of @Koh:2015vz? What are the inherent limitations in a simulation analysis like this one? ```{r} #| include: false dbDisconnect(db) ```