🎯 Overview

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.

  • For example, true/unadjusted EPS of 100.4 cents will be rounded to $1.00 on the financial statements while true/unadjusted EPS of 100.5 cents will be rounded to $1.01. While that extra one cent may not seem like much, remember that EPS is earnings per one share of stock outstanding, which might be very, very large.
    • The average amount of stock outstanding in our data is over 165 million shares. Thus, $0.01 of earnings for 165 million shares is $1,650,000 of earnings.

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.

🔧 Load packages and data

# 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

🧪 Instructions

👉 1a. Bar chart of digit_diluted

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')

Separate Quarter

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')

👉 1b. Quarter with smallest difference in 4s and 5s

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.

👉 2a. Year 1998

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')

👉 2b. Year 2019

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')

👉 2c. Differences between 1998 and 2019

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.

👉 3. Worst 10 offenders

  • Next, use your analytics skills to find and report the worst 10 offenders of EPS rounding to the SEC.
  • Specifically, create a table of the 10 worst offenders following the criteria below.
  • You will need to create a table that has one observation per company. Thus, you will need to aggregate each company’s results down to one row using a summarize function.
  • Additionally, you will only include a company in the table if the aggregated results from that company meet the following criteria:
    • The company has more than 56 observations (quarters/rows) in the original assign_data_EPS rounding_after class.rds dataset.
    • Less than 1.18% of all of the company’s observations have a 4 in the digit after the decimal point of EPS (e.g., in the EPS_diluted_cents column).
    • More than 11% of all of the company’s observations have a 5 in the digit after the decimal point of EPS (e.g., in the 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.

% of companies based on integers 4 & 5

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.

👉 3a. Show table

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

👉 3b. Observations

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