A Workflow Demonstration

This script will present several new components of the Nonprofit Open Data Collective data environment using an example motivated by the “Glass Cliff”, or the idea the women are more likely to be appointed to precarious positions of power relative to their male counterparts.

We specifically want to highlight the value of an integrated data environment developed around the EFILE Database, one of the emerging research centerpieces in nonprofit scholarship. This tutorial demonstrates a reproducible data engineering workflow that follows FAIR Data Guidelines. We will answer the research question using a dataset created through the following 8 Steps:

  1. Build tables using the irs990efile package.
  2. Standardize names of the leadership team present in Part VII (the compensation tables) using the peopleparser package.
  3. Standardize titles in Part VII using the titleclassifier package.
  4. Identify CEO transition years within the data.
  5. Build a financial dataset by combining the following 990 Parts: revenues, expenses, and assets.
  6. Generate common financial operating ratios using the fiscal package.
  7. Add NTEE codes and standardized address fields from the Unified BMF.
  8. Merge financial tables and compensation tables into a single table for the analysis.

We are using the term “integrated data environment” here to mean:

These things allow us to create more expressive and intuitive data steps that anyone can replicate. It also allows researchers to create custom tools for refining or analyzing data, as demonstrated by the packages used in this workflow.

Glass Cliff Research

The glass cliff is a hypothesized phenomenon in which women are more likely to break the “glass ceiling” (i.e. achieve leadership roles in business and government) during periods of crisis or downturn when the risk of failure is highest.

We know from previous studies that male CEOs are put in charge of large, thriving nonprofits while women are more likely to be hired to lead smaller social services organizations.

Here we explore the question of whether female CEOs are more likely to be hired when the current leader is struggling.

Grasse, N. J., Heidbreder, B., Kukla-Acevedo, S. A., & Lecy, J. D. (2024). Some Good News, More Bad News: Two Decades of the Gender Pay Gap for Nonprofit Directors and Chief Financial Officers. Review of Public Personnel Administration, 0734371X241248854.

Data and Software

Efile Data

Processed IRS 990 Efile data are housed in the NCCS Data Catalog. You can find further information about sources, processing, and variable descriptions here:

R Packages

There is a growing library of nonprofit tools:

We will be using the following packages for the demo:

# install.packages( "devtools" )
devtools::install_github( 'Nonprofit-Open-Data-Collective/peopleparser' )
devtools::install_github( 'nonprofit-open-data-collective/titleclassifier' )
devtools::install_github( 'nonprofit-open-data-collective/fiscal')
devtools::install_github( 'nonprofit-open-data-collective/irs990efile')
package.list <- 
  c( "tidyverse",
     "knitr", "pander",
     "ggrepel", "RecordLinkage",
     "data.table", "reshape2",
     "utils")

install.packages( package.list )
library( tidyverse )
library( pander )
library( data.table )
library( RecordLinkage )
library( reshape2 )
library( ggrepel )
library( utils )

# nonprofit data packages 
library( peopleparser )
library( titleclassifier )
library( fiscal )

# helper functions for the demo: 
nodc <- "https://raw.githubusercontent.com/Nonprofit-Open-Data-Collective/"
repo <- "arnova-2024/refs/heads/main/"
file <- "functions.R"
source( paste0( nodc, repo, file ) )

STEP 1: Building Efile Tables from Scratch

This step builds Efile tables by loading raw XML efile returns from the Data Commons and using the irs990efile package to parse XML files into rectangular CSV tables.

This code creates data for a sample of 100 nonprofits from 2018:

library( irs990efile )

index <- build_index( tax.years=2018 )

index100 <-
  index %>% 
  filter( FormType %in% c("990","990EZ") ) %>%
  sample_n( 100 )

TABLES <- c( "F9-P00-T00-HEADER",
             "F9-P01-T00-SUMMARY",
             "F9-P08-T00-REVENUE",
             "F9-P09-T00-EXPENSES",
             "F9-P11-T00-ASSETS" )

URLS <- index100$URL

build_tables( urls=URLS, year=2018, table.names=TABLES  )

Typically you would not need to replicate this step since it is a computationally-intensive process. It can take a couple of days to build the full efile database. It is much easier to pull existing CSV files from NCCS:

EFILE DATA CATALOG.

There are two main ways to access the pre-built tables of the data. You can download the data locally to your computer from the data catalog and read it into your R environment or you can use some helper functions to read it directly in R. We present examples of both below.

# LOCAL DATA

df2010 <- read.csv( "Coding/Data/PartVII/PartVII-2010.csv" )
df2011 <- read.csv( "Coding/Data/PartVII/PartVII-2011.csv" )
# HELPER FUNCTIONS:
# nodc <- "https://raw.githubusercontent.com/Nonprofit-Open-Data-Collective/"
# repo <- "arnova-2024/refs/heads/main/"
# file <- "functions.R"
# source( paste0( nodc, repo, file ) )

df2010.fun <- get_partvii(2010)
df2011.fun <- get_partvii(2011)
get_partvii <- function( year ){
  root <- "https://nccs-efile.s3.us-east-1.amazonaws.com/parsed/partvii/PARTVII-"
  url  <- paste0( root, year, ".csv" )
  df   <- data.table::fread( url, colClasses=c( "ObjectId"="character" )  )
  df$EIN2 <- format_ein( df$ORG_EIN )
  return( df )
}

STEP 2: Parse DTK Names

The main variables of interest in these data are the board member names and their titles. We will utilize pre-built packages from the Nonprofit Open Data Repositories to clean these variables further (the packages are peopleparser and titleclassifier).

root    <- paste0( nodc, repo )
fn      <- "data/PART-VII-SAMPLE-10.CSV"
url     <- paste0( root, fn )
partvii <- read.csv( url )
Table continues below
NAME TAXYR FORMTYPE F9_07_COMP_DTK_NAME_PERS
President and Fellows of Middlebury College 2009 990 Ronald Liebowitz
President and Fellows of Middlebury College 2009 990 Frederick M Fritz
President and Fellows of Middlebury College 2009 990 Marna C Whittington
President and Fellows of Middlebury College 2009 990 Kendrick R Wilson III
President and Fellows of Middlebury College 2009 990 S Carolyn Ramos
President and Fellows of Middlebury College 2009 990 Roxanne M Leighton
F9_07_COMP_DTK_TITLE F9_07_COMP_DTK_AVE_HOUR_WEEK F9_07_COMP_DTK_COMP_ORG
President 40 409609
Chair 5 0
Vice Chair 5 0
Vice Chair 5 0
Alumni Trustee 1 0
Charter Trustee 1 0

Now let’s use peopleparser to clean the names. Names do not arrive standardized in any specific format:

## Louis Bacon  ;;  KAREN HELENE  ;;  KEVIN LENIHAN
## MICHAEL K LAUF  ;;  DELAINNA BURTON  ;;  Patrick J Norton
## Deborah Buckley  ;;  Elisabeth B Robert  ;;  Ann D Peterson
## ALEXANDER WESTPHAL MD  ;;  VIRGINIA HOWARD  ;;  Ann Williams Jackson
## ELIZABETH OLIVEIRA  ;;  Katie Smith Abbott  ;;  Frank W Sesno

The peopleparser package will remove nuisance text and split the name string into 5 parts:

And add a predicted gender label (M/F/U) plus confidence level once it is able to identify the individual’s first name:

peopleparser::parse.name( "(1) Rev Kendrick R Wilson III" )
## [1] "REV|KENDRICK|R|WILSON|III||M|100"
peopleparser::parse.name( "Doctor Wilson III, Kendrick R" )
## [1] "DOCTOR|KENDRICK|R|WILSON|III||M|100"
# gender prediction is based on first names
peopleparser::parse.name( "Wilson, K R Until June 2008" )
## [1] "|K|R|WILSON|||U|0.0"
nm <- partvii[[ "F9_07_COMP_DTK_NAME_PERS" ]] %>% unique()
nm.parsed <- peopleparser::parse.names( nm )
Table continues below
name salutation first_name middle_name last_name
Ronald Liebowitz RONALD LIEBOWITZ
Frederick M Fritz FREDERICK M FRITZ
Marna C Whittington MARNA C WHITTINGTON
Kendrick R Wilson III KENDRICK R WILSON
S Carolyn Ramos S CAROLYN RAMOS
Roxanne M Leighton ROXANNE M LEIGHTON
suffix status gender gender_confidence
M 99.7
M 100
F 100
III M 100
F 100
F 100

Join parsed names back to the original data frame.

# CORE R VERSION OF A TABLE JOIN 
partvii <- 
  partvii %>% 
  merge( nm.parsed, 
         by.x="F9_07_COMP_DTK_NAME_PERS", by.y="name", 
         all.x=T )
# TIDYVERSE VERSION OF A TABLE JOIN
partvii <- 
  partvii %>% 
  left_join( nm.parsed, 
             by=c( "F9_07_COMP_DTK_NAME_PERS" = "name" ) )
write.csv( partvii, "data/PART-VII-SAMPLE-10-PARSED-NAMES.CSV", row.names=F )

STEP 3: Standardize Titles

root    <- paste0( nodc, repo )
fn      <- "data/PART-VII-SAMPLE-10-PARSED-NAMES.CSV"
url     <- paste0( root, fn )
partvii <- read.csv( url )
# steps from titleclassifier package 

titles <-  
  partvii %>% 
  standardize_df() %>% 
  remove_dates() %>% 
  standardize_conj() %>% 
  split_titles() %>% 
  standardize_spelling() %>% 
  gen_status_codes() %>% 
  standardize_titles() %>%
  categorize_titles()
## ✔ standardize df step complete
## ✔ remove dates step complete
## ✔ standardize conjunctions step complete
## ✔ split titles step complete
## ✔ standardize spelling step complete
## ✔ generate status codes step complete
## ✔ standardize titles step complete
## ✔ categorize titles step complete
dtk.name title.raw title.mult.x title.order
KATHRYN DUPREE EXECUTIVE DIRECTOR 0 1
LINDA GRIMM ACADEMY DIRECTOR 0 1
ARLENE KAYE DIRECTOR OF CHILDREN’S BEHAVIORAL SERVICES 0 1
TACIE LOWE DIRECTOR OF INDIVIDUAL & FAMILY SUPPORT 0 1
CARL J CASPER CHAIR 0 1
THOMAS IGOE TREASURER/SECRETARY 1 2
THOMAS IGOE TREASURER/SECRETARY 1 1
LARRY WOOD DIRECTOR 0 1
STEPHEN SIMONSON RESIDENTIAL DIRECTOR 0 1
ARLENE KAYE CBD DIRECTOR 0 1

Title processing steps from the package:

Table continues below
  title.raw title.v4
150 EXECUTIVE DIRECTOR/FSE DIRECTOR EXECUTIVE DIRECTOR
151 EXECUTIVE DIRECTOR/FSE DIRECTOR FSE DIRECTOR
152 DIRECTOR DIRECTOR
153 DIRECTOR DIRECTOR
154 DIRECTOR DIRECTOR
155 DIRECTOR, FLUTIE FDN. AND FSE DIRECTOR AND FLUTIE FDN AND FSE
156 CO-CHAIR/DIRECTOR CO-CHAIR
157 DIRECTOR DIRECTOR
158 CO-CHAIR/DIRECTOR DIRECTOR
159 DIRECTOR DIRECTOR
160 DIRECTOR DIRECTOR
161 DIRECTOR DIRECTOR
162 EXECUTIVE DIRECTOR EXECUTIVE DIRECTOR
163 CLERK/DIRECTOR CLERK
164 CLERK/DIRECTOR DIRECTOR
165 DIRECTOR DIRECTOR
  title.v7 title.standard
150 CEO CEO
151 FSE DIRECTOR NA
152 DIRECTOR DIRECTOR
153 DIRECTOR DIRECTOR
154 DIRECTOR DIRECTOR
155 DIRECTOR AND FLUTIE FDN AND FSE NA
156 CO-CHAIR BOARD PRESIDENT
157 DIRECTOR DIRECTOR
158 DIRECTOR DIRECTOR
159 DIRECTOR DIRECTOR
160 DIRECTOR DIRECTOR
161 DIRECTOR DIRECTOR
162 CEO CEO
163 CLERK CLERK
164 DIRECTOR DIRECTOR
165 DIRECTOR DIRECTOR

Pay and hours tabulated relative to other employees in the same org:

Table continues below
  dtk.name title.standard tot.hours
15 KATHRYN DUPEE CEO 37.5
16 JOHN BALDINO DIRECTOR OF FACILITIES 37.5
17 JOSEPH SPITERI GENERAL MANAGER 37.5
18 TACIE LOWE NA 37.5
19 THOMAS IGOE BOARD TREASURER 0.5
20 KARA FARACLAS BOARD VICE PRESIDENT 0.5
21 JAMES MCPARTLAND PHD DIRECTOR 0.5
22 JANETTE JOHNSON DIRECTOR 0.5
23 STEPHEN WIZNER DIRECTOR 0.5
  hours.rank tot.comp pay.max pay.rank
15 1 176077 188130 3
16 1 141983 188130 7
17 1 141538 188130 8
18 1 120327 188130 18
19 2 0 188130 25
20 2 0 188130 25
21 2 0 188130 25
22 2 0 188130 25
23 2 0 188130 25 
partvii <- 
  merge( partvii, titles, 
         by.x=c("EIN","TAXYR"), by.y=c("ein","taxyr"),
         all.x=TRUE )
fn <- "data/PART-VII-SAMPLE-10-PARSED-NAMES-TITLES.CSV"
write.csv( partvii, fn, row.names=F )

STEP 4: Identify CEO Transitions

There are several data cleaning and preparation steps that need to be performed on the base data before our analysis. We are omitting this step because it is outside the scope of the workflow. All that you need to know is leadership transitions are identified by isolating CEOs and finding periods where the individuals change. Transitions are labeled as:

We provide a cleaned subset of the data. We provide two dataframes of 1000 and 10 unique nonprofits that experienced at least one CEO transition between 2009 and 2019. It should be noted that it is possible for these organizations to experience more than one transition during this time frame. As such, we have a total of 1067 transitions. We can analyze various facets of these transitions.

STEP 5: Compile Financials

We will first build a financials table by selecting the relevant 990 parts, then combining them:

##   JOINING ONE TO ONE TABLES

root <- paste0( nodc, repo )

fn1  <- "data/F9-P00-T00-HEADER-SAMPLE-10.CSV"
fn2  <- "data/F9-P01-T00-SUMMARY-SAMPLE-10.CSV"
fn3  <- "data/F9-P08-T00-REVENUE-SAMPLE-10.CSV"
fn4  <- "data/F9-P09-T00-EXPENSES-SAMPLE-10.CSV"
fn5  <- "data/F9-P10-T00-BALANCE-SHEET-SAMPLE-10.CSV"

d1 <- read.csv( paste0( root, fn1 )  )
d2 <- read.csv( paste0( root, fn2 )  )
d3 <- read.csv( paste0( root, fn3 )  )
d4 <- read.csv( paste0( root, fn4 )  )
d5 <- read.csv( paste0( root, fn5 )  )

The efile one-to-one tables all share the same IDs:

intersect( names(d1), names(d2) )
## [1] "OBJECTID"       "URL"            "RETURN_VERSION" "ORG_EIN"       
## [5] "ORG_NAME_L1"    "ORG_NAME_L2"    "RETURN_TYPE"    "TAX_YEAR"      
## [9] "EIN2"

Which makes merging files easy:

df <- merge( d1, d2 )
df <- merge( df, d3 )
df <- merge( df, d4 )
df <- merge( df, d5 )
write.csv( df, "data/FINANCIALS.CSV" )

Note that dataset dimensions should not change for one-to-one merges:

dim(d1)
## [1] 107  55
dim(d2)
## [1] 107  50
dim(df)
## [1] 107 376

See the appendix for the code used to compile the five tables above for the sample.

We can also follow similar steps for the 1000 EIN observations. We don’t show the steps here due to their run time but instead provide the compiled df and some further steps to prepare it for analysis and understand the data.

peek <- 
  c( "EIN","TAXYR",
     "period", "CEO1", 
     "CEO2",  "trans_type" )

ceo_trans_1000_fncl_wT[ 1:40, peek ] %>% pander::pander()
EIN TAXYR period CEO1 CEO2 trans_type
10024245 2013 T-2 JOHN PORTER NA MF
10024245 2014 T-1 JOHN PORTER NA MF
10024245 2015 T0 JOHN PORTER NA MF
10024245 2016 T1 DEB NEUMAN NA MF
10024245 2017 T2 DEB NEUMAN NA MF
10196194 2014 T-2 NORMAND DUBREUIL NA MM
10196194 2015 T-1 NORMAND DUBREUIL NA MM
10196194 2016 T0 NORMAND DUBREUIL COLE TUCKER MM
10196194 2017 T1 COLE TUCKER NA MM
10196194 2018 T2 COLE TUCKER NA MM
10206603 2010 T-2 DONNA STECKINO NA FM
10206603 2011 T-1 DONNA STECKINO NA FM
10206603 2012 T0 KERRY WOOD NA FM
10206603 2013 T1 KERRY WOOD NA FM
10206603 2014 T2 KERRY WOOD NA FM
10206603 2012 T-2 KERRY WOOD NA MF
10206603 2013 T-1 KERRY WOOD NA MF
10206603 2014 T0 KERRY WOOD NA MF
10206603 2015 T1 JENNIFER HOGAN NA MF
10206603 2016 T2 JENNIFER HOGAN NA MF
10211481 2014 T-2 STACY SCHAFFER NA FF
10211481 2015 T-1 STACY SCHAFFER NA FF
10211481 2016 T0 STACY SCHAFFER BETHANY BILODEAU FF
10211481 2017 T1 BETHANY BILODEAU NA FF
10211481 2018 T2 BETHANY BILODEAU NA FF
10211484 2009 T-2 KENT ULERY NA MM
10211484 2010 T-1 KENT ULERY NA MM
10211484 2011 T0 ROBERT GROVE-MARKWOOD NA MM
10211484 2012 T1 ROBERT GROVE-MARKWOOD NA MM
10211484 2013 T2 ROBERT GROVE-MARKWOOD NA MM
10211497 2012 T-2 WILLIAM ADAMS NA MM
10211497 2013 T-1 WILLIAM ADAMS NA MM
10211497 2014 T0 DAVID GREENE WILLIAM ADAMS MM
10211497 2015 T1 DAVID GREENE NA MM
10211497 2016 T2 DAVID GREENE NA MM
10211509 2009 T-2 DANIEL KUNKLE NA MM
10211509 2010 T-1 DANIEL KUNKLE NA MM
10211509 2011 T0 DANIEL KUNKLE NA MM
10211509 2012 T1 MATTHEW RUBY NA MM
10211509 2013 T2 MATTHEW RUBY NA MM

There are 349 transitions from male to male (MM) transitions, 253 male to female transitions (MF), 160 female to male transitions (FM), and 305 female to female transitions (FF) in our dataset. FF orgs have the smallest number of employees, on average while MF have the highest. It should be noted that the standard deviation for number of employees is pretty high, suggesting a wide range of nonprofits. MM nonprofits have the highest total expeness on average and the highest total assets. Again, the SDs are wide, suggesting large variation across observations.

STEP 6: Add Financial Operating Ratios

root <- paste0( nodc, repo )
fn   <- "data/FINANCIALS.CSV"
df   <- read.csv( paste0( root, fn )  )
F9_01_EXP_TOT_PY F9_01_EXP_REV_LESS_EXP_CY F9_01_EXP_REV_LESS_EXP_PY
3217608 56922 31606
870793 -194147 -257955
692598 42635 31852
11765651 519017 403144
8708339 -558773 -308774

The fiscal package contains the following financial ratios:

df <- get_aer( df )     #   Assets to Revenues Ratio
df <- get_arr( df )     #   Assets to Revenues Ratio
df <- get_cr( df )      #   Current Ratio
df <- get_dar( df )     #   Debt to Asset Ratio
df <- get_der( df )     #   Debt to Equity Ratio
df <- get_dgdr( df )    #   Donation/Grant Dependence Ratio
df <- get_dmr( df )     #   Debt Management Ratio
df <- get_doch( df )    #   Days of Operating Cash on Hand
df <- get_doci( df )    #   Days of Operating Cash and Investments
df <- get_eidr( df )    #   Earned Income Dependence Ratio
df <- get_er( df )      #   Equity Ratio
df <- get_ggr( df )     #   Government Grants Ratio
df <- get_iidr( df )    #   Investment Income Dependence Ratio
df <- get_lar( df )     #   Lands to Assets Ratio
df <- get_moch( df )    #   Months of Operating Cash on Hand
df <- get_or( df )      #   Operating Margin
df <- get_per( df )     #   Program Efficiency Ratio
df <- get_podpm( df )   #   Post-Depreciation Profitability Margin
df <- get_predpm( df )  #   Pre-Depreciation Profitability Margin
df <- get_qr( df )      #   Quick Ratio
df <- get_ssr( df )     #   Self Sufficiency Ratio
df <- get_stdr( df )    #   Short Term Debt Ratio

We will add the post-depreciation profitability margin and the quick ratio to the data:

df <- get_podpm( df ) 
## [1] "Revenues cannot be equal to zero: 0 cases have been replaced with NA."
##      podpm             podpm.w            podpm.n           podpm.p  
##  Min.   :-0.50950   Min.   :-0.46216   Min.   :-3.3121   Min.   : 1  
##  1st Qu.:-0.07950   1st Qu.:-0.07950   1st Qu.:-0.4535   1st Qu.:25  
##  Median : 0.01107   Median : 0.01107   Median : 0.2230   Median :49  
##  Mean   :-0.01858   Mean   :-0.01879   Mean   : 0.0000   Mean   :49  
##  3rd Qu.: 0.05557   3rd Qu.: 0.05557   3rd Qu.: 0.5555   3rd Qu.:73  
##  Max.   : 0.38204   Max.   : 0.31425   Max.   : 2.4878   Max.   :97  
##  NA's   :10         NA's   :10         NA's   :10        NA's   :10

df <- get_dgdr( df )
## [1] "Net assets cannot be zero: 0 cases have been replaced with NA."
##       dgdr             dgdr.w            dgdr.n            dgdr.p  
##  Min.   :0.04165   Min.   :0.04638   Min.   :-1.6821   Min.   : 1  
##  1st Qu.:0.42370   1st Qu.:0.42370   1st Qu.:-0.4077   1st Qu.:10  
##  Median :0.61054   Median :0.61054   Median : 0.2233   Median :19  
##  Mean   :0.54480   Mean   :0.54442   Mean   : 0.0000   Mean   :19  
##  3rd Qu.:0.80102   3rd Qu.:0.80102   3rd Qu.: 0.8667   3rd Qu.:28  
##  Max.   :0.92779   Max.   :0.90902   Max.   : 1.2314   Max.   :37  
##  NA's   :70        NA's   :70        NA's   :70        NA's   :70

For details on the definitions and calculation of the ratios try:

help( get_dgdr )
write.csv( df, "data/FINANCIALS-W-RATIOS.CSV", row.names=F )

STEP 7: Add BMF Fields

The Business Master File contains important information not available on 990 forms such as organizational NTEE codes. In addition, the NCCS BMF files contain standardized geographies and other useful information.

The BMF rows for our sample have been precompiled:

root <- paste0( nodc, repo )
fn   <- "data/bmf_unified_10.csv"
bmf  <- read.csv( paste0( root, fn ) )
root <- paste0( nodc, repo )
fn   <- "data/FINANCIALS-W-RATIOS.CSV"
df   <- read.csv( paste0( root, fn )  )
df <- merge( df, bmf, by="EIN2" )
write.csv( df, "data/FINANCIALS-W-RATIOS-PLUS-BMF.CSV", row.names=F )

STEP 8: Glass Cliff Analysis

Now we’re ready for some analyses! The Glass Cliff Phenomenon hypothesizes that women are chosen for positions of power when these positions are more precarious. One way to denote a precarious position is by the firm’s financial performance; poor financial performance suggests more precariousness. We acknowledge that financial performance is comprised of several different dimensions and it is sometimes hard to arrive at clean conclusions about “poorly performing nonprofits.” For demonstration purposes we will specifically focus on the financial performance metric of post-depreciation profitability margin (podpm). This is defined as an income measure that determines a firm’s profitability after incorporating non-cash expenses. Higher values of this metric are generally desirable because the indicate that an org is not lost its revenue to expenses. We will use the package fiscal from the Open Data Collective to calculate our variable of interest. The default parameters of the respective functions are already built for the 990 naming conventions so usage is pretty straight forward!

ceo_trans_1000EIN_fncl_wT <- read.csv( "data/toy_ceo_trans_1000EIN_fncl_wT.csv" )
ceo_trans_1000EIN_fncl_wT <- get_podpm(ceo_trans_1000EIN_fncl_wT)
## [1] "Revenues cannot be equal to zero: 0 cases have been replaced with NA."
##      podpm              podpm.w            podpm.n            podpm.p      
##  Min.   :-619.4798   Min.   :-0.64627   Min.   :-4.04004   Min.   :  1.00  
##  1st Qu.:  -0.0327   1st Qu.:-0.03269   1st Qu.:-0.32851   1st Qu.: 25.00  
##  Median :   0.0194   Median : 0.01937   Median :-0.01358   Median : 50.00  
##  Mean   :  -0.0964   Mean   : 0.02162   Mean   : 0.00000   Mean   : 50.29  
##  3rd Qu.:   0.0809   3rd Qu.: 0.08092   3rd Qu.: 0.35867   3rd Qu.: 75.00  
##  Max.   :  39.8564   Max.   : 0.57166   Max.   : 3.32717   Max.   :100.00  
##  NA's   :203         NA's   :203        NA's   :203        NA's   :203

#Let's plot these measures
plot_temp <- ceo_trans_1000EIN_fncl_wT %>%
  group_by(trans_type, period)%>%
  summarize(median_podpm =median(podpm, na.rm = T))

txt <- 
  "Median Post-Depreciation Profitability Margin by Transition Type"

ggplot( data = plot_temp, 
        aes( x = period, y = median_podpm, 
             group = trans_type, color = trans_type ) ) + 
  geom_line(linewidth = 1.5)+ 
  geom_text_repel( aes(label = round(median_podpm,3)), size = 5, 
                   nudge_x = -0.07, nudge_y = 0.001, 
                   segment.size = 0, segment.color = NA ) +
  theme_bw( ) + 
#  theme(text = element_text(size = 24))+
  scale_x_discrete(labels = c("T-2", "T-1", "Transition", "T+1", "T+2"))+
  labs(color = "Transition" ) +
  xlab( "Period" )+
  ylab( "Median Post-Depreciation Profitability Margin" ) +
  ggtitle( txt ) +
  geom_vline( xintercept = 3, linetype = "dashed" )

################################################
#Now doing density plots of these respective vars
################################################

#Let's compare the MM density to MF density in the t-1 period for the vats

ceo_trans_1000_fncl_MM <- 
  ceo_trans_1000EIN_fncl_wT %>% 
  filter(trans_type == "MM" & period == "T-1")

ceo_trans_1000_fncl_MF <- 
  ceo_trans_1000EIN_fncl_wT %>% 
  filter(trans_type == "MF"& period == "T-1")

txt <-
  "Density ofPost-Depreciation Profitability Margin by Transition at t-1"

ggplot() + 
  geom_density( data = ceo_trans_1000_fncl_MM,  
                aes(x = podpm, fill = "lightblue"), alpha = 0.5) +
  geom_density( data = ceo_trans_1000_fncl_MF,  
                aes(x = podpm, fill = "pink"), alpha = 0.5) +
  xlim(-1,1)+
  theme_bw()+

  scale_fill_manual( name = "Transition", 
                     values = c('lightblue', 'pink'), 
                     labels = c("pink" = "MF" ,  "lightblue" = "MM") ) +
  xlab( "Post-Depreciation Profitability Margin" )+
  ylab( "Density" ) +
  ggtitle( txt )

The glass cliff phenomenon suggest that financial precarious NPs (seen by a significant drop in their financials between T-2 and T-1) would more likely hire a female to the CEO position. We start by looking at the PODPM variable over the periods. MM organizations start with the highest median profitability margin but also experience the steepest drop between T-2 and T-1. We see a similar slope for FM organizations although the starting point is the lowest in the entire group. MF transitions do not appear to be preceded by steep changes in profitability. If the glass cliff hypothesis were true, we would expect the observed trajectory of FM but would not expect the other firms to have similar metrics around their transitions. This graph calls for further analysis on other financial measures to determine whether FM firms display a transition during significantly more precarious times than their counterparts.



As a final understanding of the data, we look at the distribution of PODPM at T-1. We consider the two most relevant groups of MM and MF. We see that the distribution of financial variables tends to be extremely similar to both types of transitions, again failing to provide strong motivation for a glass cliff phenomenon.

Financial Measures by Transitions

The Glass Cliff Phenomenon hypothesizes that women are chosen for positions of power when these positions are more precarious. One way to denote a precarious position is by the firm’s financial performance; poor financial performance suggests more precariousness. We acknowledge that financial performance is comprised of several different dimensions and it is sometimes hard to arrive at clean conclusions about “poorly performing nonprofits.” For demonstration purposes we will specifically focus on the financial performance metric of post-depreciation profitability margin (podpm). This is defined as an income measure that determines a firm’s profitability after incorporating non-cash expenses. Higher values of this metric are generally desirable because the indicate that an org is not lost its revenue to expenses. We will use the package fiscal from the Open Data Collective to calculate our variable of interest. The default parameters of the respective functions are already built for the 990 naming conventions so usage is pretty straight forward!

# Make sure to run the code chunk about 
# to ensure your df_long_fncl has all 
# the necessary variables 

#site: https://github.com/Nonprofit-Open-Data-Collective/fiscal/tree/main/R
ceo_trans_1000_fncl <- get_podpm( ceo_trans_1000_fncl )



#Let's plot these measures
plot_temp <- ceo_trans_1000_fncl %>%
  group_by(trans_type, period)%>%
  summarize(median_podpm =median(podpm, na.rm = T))


ggplot( data = plot_temp, 
        aes( x = period, 
             y = median_podpm, 
             group = trans_type, 
             color = trans_type) ) + 
  geom_line(linewidth = 1.5)+ 
  geom_text_repel( aes(label = round(median_podpm,3) ), 
                   size = 5, nudge_x = -0.07, 
                   nudge_y = 0.001, segment.size = 0, 
                   segment.color = NA) +
  theme_bw( ) + 
  scale_x_discrete(labels = c("T-2", "T-1", "Transition", "T+1", "T+2"))+
  labs(color = "Transition") +
  xlab( "Period" )+
  ylab( "Median Post-Depreciation Profitability Margin" ) +
  ggtitle("Median Post-Depreciation Profitability Margin by Transition Type") +
  geom_vline(xintercept = 3, linetype = "dashed")



################################################
#Now doing density plots of these respective vars
################################################

#Let's compare the MM density to MF density in the t-1 period for the vats

ceo_trans_1000_fncl_MM <- 
  ceo_trans_1000_fncl %>% 
  filter( trans_type == "MM" & period == "T-1" )

ceo_trans_1000_fncl_MF <- 
  ceo_trans_1000_fncl %>% 
  filter( trans_type == "MF" & period == "T-1" )


txt <- 
  "Density ofPost-Depreciation Profitability Margin by Transition at t-1"

ggplot() + 
  geom_density( data = ceo_trans_1000_fncl_MM,  
                aes(x = podpm, fill = "lightblue"), 
                alpha = 0.5 ) +
  geom_density( data = ceo_trans_1000_fncl_MF,  
                aes(x = podpm, fill = "pink"),
                alpha = 0.5 ) +
  xlim(-1,1)+
  theme_bw()+
  scale_fill_manual( name = "Transition", 
                     values = c('lightblue', 'pink'), 
                     labels = c("pink" = "MF" ,  "lightblue" = "MM") ) +
  xlab("Post-Depreciation Profitability Margin")+
  ylab("Density") +
  ggtitle( txt )

The glass cliff phenomenon suggest that financial precarious NPs (seen by a significant drop in their financials between T-2 and T-1) would more likely hire a female to the CEO position. We start by looking at the PODPM variable over the periods. MM organizations start with the highest median profitability margin but also experience the steepest drop between T-2 and T-1. We see a similar slope for FM organizations although the starting point is the lowest in the entire group. MF transitions do not appear to be preceded by steep changes in profitability. If the glass cliff hypothesis were true, we would expect the observed trajectory of FM but would not expect the other firms to have similar metrics around their transitions. This graph calls for further analysis on other financial measures to determine whether FM firms display a transition during significantly more precarious times than their counterparts.

As a final understanding of the data, we look at the distribution of PODPM at T-1. We consider the two most relevant groups of MM and MF. We see that the distribution of financial variables tends to be extremely similar to both types of transitions, again failing to provide strong motivation for a glass cliff phenomenon.



Appendix: Step 5

The demo files above (a sample of 10 nonprofits), read directly from the demo repo on GitHub, were compiled using the following code:

EIN2_10 <- 
c("EIN-02-0240383", "EIN-03-0179298", 
  "EIN-04-2104310", "EIN-04-2259692", 
  "EIN-04-2592472", "EIN-04-2596491", 
  "EIN-04-3266589", "EIN-04-3543134", 
  "EIN-05-0258941", "EIN-06-0840436" )

tables <- 
c( "F9-P00-T00-HEADER",
"F9-P01-T00-SUMMARY",
"F9-P08-T00-REVENUE",
"F9-P09-T00-EXPENSES",
"F9-P10-T00-BALANCE-SHEET")

for( i in tables )
{
  for( j in 2009:2020 )
  { 
    df <- NULL
    try( df <- get_table( i, j ) )
    if( is.null(df) ){ next }
    sub <- dplyr::filter( df$EIN2 %in% EIN2_10 )
    fn <- paste0( i, "-", j, "-SAMPLE-10.CSV" )
    write.csv( sub, fn, row.names=F, na="" )
  }
}

# COMBINE ALL YEARS TO SINGLE FILE

root <- "https://raw.githubusercontent.com/Nonprofit-Open-Data-Collective/arnova-2024/refs/heads/main/data/"

for( i in tables )
{
  d.list <- list()
  for( j in 2009:2020 )
  {
    fn <- paste0( i, "-", j, "-SAMPLE-10.CSV" )
    url <- paste0( root, fn )
    df <- read.csv( url, colClasses = "character" ) 
    d.list[[ as.character(j) ]] <- df
  }
  dd <- dplyr::bind_rows( d.list )
  filename <- paste0( i, "-SAMPLE-10.CSV" )
  write.csv( dd,  filename, row.names=F, na="" )
}

# [1] "F9-P00-T00-HEADER-SAMPLE-10.CSV"       
# [2] "F9-P01-T00-SUMMARY-SAMPLE-10.CSV"      
# [3] "F9-P08-T00-REVENUE-SAMPLE-10.CSV"      
# [4] "F9-P09-T00-EXPENSES-SAMPLE-10.CSV"     
# [5] "F9-P10-T00-BALANCE-SHEET-SAMPLE-10.CSV"