In class, we discussed Wall Street Journal articles based on academic research (see the references below) that used data analytics to detect when a company is potentially inflating its earnings per share number. The analytics work by highlighting when a company has fewer than a reasonable number of the integer 4 in the spot after the decimal point in diluted EPS reported in cents (for example 30.4 cents, 125.4 cents, 8.4 cents, etc.). On average, the integer 4, and all other integers, should appear directly after the decimal point of diluted EPS represented in cents approximately 10% of the time, since each of the 10 digits has an equal likely chance of being listed.
Thus, finding that a company routinely lists the digit 4 less frequently than 10% is a reason to suspect the company is manipulating its earnings.
Why is this evidence of misreporting? Because the company will round its EPS to the nearest cent on the financial statements and 4 will round down while a 5 will round up. Thus, the company might be reporting a 5, 6, 7, 8, or 9 in that spot so that their EPS will be rounded up and higher than it should be.
We explored this data in class, but let’s use the same data to gain some additional insight. Specifically, suppose you are the researcher who first reports the phenomenon of too few 4’s to the SEC (U.S. Securities and Exchange Commission), and you want to use your analytics skills to help them understand this phenomenon.
Follow the steps below to help make your point.
# uncomment and run the line below if you need to install tidyverse
# install.packages("tidyverse")
library(tidyverse)
## Warning: package 'tidyverse' was built under R version 4.3.2
## Warning: package 'tidyr' was built under R version 4.3.2
## Warning: package 'readr' was built under R version 4.3.2
## Warning: package 'dplyr' was built under R version 4.3.2
## Warning: package 'stringr' was built under R version 4.3.2
## Warning: package 'lubridate' was built under R version 4.3.2
## ── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
## ✔ dplyr 1.1.4 ✔ readr 2.1.5
## ✔ forcats 1.0.0 ✔ stringr 1.5.1
## ✔ ggplot2 3.5.0 ✔ tibble 3.2.1
## ✔ lubridate 1.9.3 ✔ tidyr 1.3.1
## ✔ purrr 1.0.2
## ── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
## ✖ dplyr::filter() masks stats::filter()
## ✖ dplyr::lag() masks stats::lag()
## ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors
df <- read_rds("assign_data_EPS rounding_after class.rds")
df %>% head(n = 5)
str(df)
## tibble [384,308 × 10] (S3: tbl_df/tbl/data.frame)
## $ tic : chr [1:384308] "AIR" "AIR" "AIR" "AIR" ...
## ..- attr(*, "label")= chr "Ticker Symbol"
## ..- attr(*, "format.sas")= chr "$"
## $ conm : chr [1:384308] "AAR CORP" "AAR CORP" "AAR CORP" "AAR CORP" ...
## ..- attr(*, "label")= chr "Company Name"
## ..- attr(*, "format.sas")= chr "$"
## $ fyearq : num [1:384308] 1998 1998 1999 1999 1999 ...
## ..- attr(*, "label")= chr "Fiscal Year"
## ..- attr(*, "format.sas")= chr "F"
## $ fqtr : num [1:384308] 3 4 1 2 3 4 1 2 3 4 ...
## ..- attr(*, "label")= chr "Fiscal Quarter"
## ..- attr(*, "format.sas")= chr "F"
## $ curcdq : chr [1:384308] "USD" "USD" "USD" "USD" ...
## ..- attr(*, "label")= chr "ISO Currency Code"
## ..- attr(*, "format.sas")= chr "$"
## $ ibadjq : num [1:384308] 10.3 11.7 10.8 10.9 11 ...
## ..- attr(*, "label")= chr "Income Before Extraordinary Items - Adjusted for Common Stock Equivalents"
## ..- attr(*, "format.sas")= chr "F"
## $ xidoq : num [1:384308] 0 0 0 0 0 0 0 0 0 0 ...
## ..- attr(*, "label")= chr "Extraordinary Items and Discontinued Operations"
## ..- attr(*, "format.sas")= chr "F"
## $ cshfdq : num [1:384308] 28 27.7 27.8 27.5 27.3 ...
## ..- attr(*, "label")= chr "Com Shares for Diluted EPS"
## ..- attr(*, "format.sas")= chr "F"
## $ EPS_diluted_cents: num [1:384308] 36.8 42.4 38.9 39.7 40.2 ...
## ..- attr(*, "label")= chr "Income Before Extraordinary Items - Adjusted for Common Stock Equivalents"
## ..- attr(*, "format.sas")= chr "F"
## $ digit_diluted : chr [1:384308] "7" "3" "9" "6" ...
## - attr(*, "label")= chr "Merged Fundamental Quarterly File"
nrow(df)
## [1] 384308
Using the dataset we cleaned up and used in class (called “…EPS rounding_after class” and provided for you as well as the data description sheet, “…Data Description Sheet_EPS rounding_after class.xlsx”), graph a bar chart that shows separate plots for the frequency of the EPS digit after the decimal point (called “digit_diluted” in the dataset) based upon the quarters of the year (1–4).
Thus, you will have four separate plots or one plot with four separate panels or facets.
HINT: We did this in class but had a separate plot
for each year instead of for each quarter. For R, use
facet_wrap() and for Alteryx use “Batch”.
df1 <- df %>%
group_by(fqtr, fyearq, digit_diluted) %>%
count(name = 'count_filings')
df1 %>%
ggplot(aes(x=digit_diluted, y=count_filings)) +
geom_col(aes(fill=digit_diluted)) +
facet_wrap(vars(as_factor(fqtr))) +
theme_bw() +
theme(legend.position = 'none') +
labs(title = 'Frequency of Digits in EPS Tenth of a Cent'
, x = 'Digit'
, y = 'Count')
Which quarter or quarters have the smallest difference in the frequency of integer 4 relative to integer 5? You don’t need to calculate this difference, just look at the four plots and make your best judgment.
Response: By review of the visualization, the smallest difference in the frequency of integers is between 3rd and 4th quarter.
Next, create and examine these same sets of plots for just 1998. Thus, you will have four sets of graphs for that year.
df_1998 <- df1[df1$fyearq == 1998, ]
#1998 Plots
df_1998 %>%
ggplot(aes(x=digit_diluted, y=count_filings)) +
geom_col(aes(fill=digit_diluted)) +
facet_wrap(vars(as_factor(fqtr))) +
theme_bw() +
theme(legend.position = 'none') +
labs(title = 'Frequency of Digits in EPS Tenth of a Cent'
, x = 'Digit'
, y = 'Count')
Next, create and examine this same set of plots for just 2019. Thus, you will have four sets of graphs for that year.
df_2019 <- df1[df1$fyearq == 2019, ]
#2019 Plots
df_2019 %>%
ggplot(aes(x=digit_diluted, y=count_filings)) +
geom_col(aes(fill=digit_diluted)) +
facet_wrap(vars(as_factor(fqtr))) +
theme_bw() +
theme(legend.position = 'none') +
labs(title = 'Frequency of Digits in EPS Tenth of a Cent'
, x = 'Digit'
, y = 'Count')
What differences do you notice between 1998 and 2019 in the frequency of integer 4 relative to the frequency of integer 5? (in less than 50 words)
Response: 1998 2nd and 4th quarter showed the biggest variation between 4th and 5th digit. Interestingly, all quarters did show variations. In 2019 shows 1st and 2nd quarter with biggest variation between 4th and 5th digit.However, all quarters showed smaller variations when compared to 1998. This leads me to believe there was more fraudlent activity in 1998.
assign_data_EPS rounding_after class.rds
dataset.EPS_diluted_cents column).EPS_diluted_cents column).Hint: Create columns in your table for each of the three bullet points above and then use a filter function. One way to do this is to create a table with one observation for each company and the following columns: the total times the company had a 4 in the digit after the decimal point of EPS, the total times the company had a 5 in the digit after the decimal point of EPS, the total observations (quarters/rows) in the original dataset for that company, the percentage of observations with 4 in the first digit after the decimal for that company, and the percentage of observations with 5 in the first digit after the decimal for that company.
#Company Data
df_summary <- df %>%
group_by(tic, conm) %>%
summarize(
num_filings = n(),
percent_4s = sum(EPS_diluted_cents == 4) / num_filings,
percent_5s = sum(EPS_diluted_cents == 5) / num_filings
)
## `summarise()` has grouped output by 'tic'. You can override using the `.groups`
## argument.
df_table <- df %>%
group_by(tic, conm) %>%
summarize(
num_filings = n(),
percent_4s = sum(digit_diluted == 4) / num_filings,
percent_5s = sum(digit_diluted == 5) / num_filings
) %>%
filter(
num_filings > 56,
percent_4s < 0.0118,
percent_5s > 0.11
)
## `summarise()` has grouped output by 'tic'. You can override using the `.groups`
## argument.
Show this table. At the minimum, the table (or a screenshot of the table) must contain the ticker (tic), company name (conm), percentages of observations with 4 in the first digit after the decimal, and percentages of observations with 5 in the first digit after the decimal.
df_table <- df %>%
group_by(tic, conm) %>%
summarize(
num_filings = n(),
percent_4s = sum(digit_diluted == 4) / num_filings,
percent_5s = sum(digit_diluted == 5) / num_filings
) %>%
filter(
num_filings > 56,
percent_4s < 0.0118,
percent_5s > 0.11
)
## `summarise()` has grouped output by 'tic'. You can override using the `.groups`
## argument.
df_table
What observations can you make about these companies? (in less than 100 words)
Response:
By observations of the table, the first 4 top companies show they do
not perform the rounding up using integer of 4. ABAX ABAXIS INC
FEIM FREQUENCY ELECTRONICS INC
FNLC FIRST BANCORP INC/ME
FULT FULTON FINANCIAL CORP
The following companies did perform the rounding up using the
integers of 5. FORWARD AIR CORP
HEALTHCARE SERVICES GROUP
MICREL INC
NU SKIN ENTERPRISES -CL INTERFACE INC
TETRA TECH INC