Think Like an EdBuild-er:

Education Finance Data Overview

Sara Hodges
Director of Data and Visualization

Education Finance Data

• EdBuild master data
• Data sources
• Data processing
• Taming the System: Common Data Questions and Answers
• Why national data?
• Why revenues?
• Why no federal revenues?
• Which student count?
• Which economically disadvantaged count?
• How are pensions handled?
• Using master data
• Which master data should I use?
• Data questions

Education Finance Mapping

• Master geographic data
• Data source
• Shapefile processing
• Mapping the System: Common Geographic Questions and Answers
• What years are school district shapes available?
• What's with elementary and secondary districts?
• Mappy questions

EdBuild Data Resources

• Dividing Lines
• data.edbuild.org
• School Finance Dashboard
• EdBuild R packages
• Questions

Master Data

Data Sources

US Census Annual Survey of School System Finances (F33)

National Center for Education Statistics (NCES), Common Core of Data (CCD)

US Census Small Area Income and Poverty Estimates (SAIPE)

Education Demographic and Geographic Estimates (EDGE), US Census American Community Survey, 5-year

Master codebook

codebook = master_codebook()

Variable.Name	Description	Data.Source
State	State Name	F33
STATE_FIPS	ANSI/FIPS State Code	F33
CONUM	County Code	F33
NCESID	Agency ID - NCES Assigned	NCES
Name	School System Name	F33
ENROLL	Fall Membership	F33
SLR	Total Revenue from State and Local Sources	F33/EdBuild
SR	Total Revenue from State Sources	F33/EdBuild
LR	Total Revenue from Local Sources	F33/EdBuild
SLRPP	Total Revenue from State and Local Sources Per Pupil	F33/EdBuild
SRPP	Total Revenue from State Sources Per Pupil	F33/EdBuild
LRPP	Total Revenue from Local Sources Per Pupil	F33/EdBuild
SLRPP_cola	Total Revenue from State and Local Sources Per Pupil Cost-adjusted	F33/C2ER/EdBuild
SRPP_cola	Total Revenue from State Sources Per Pupil Cost-adjusted	F33/C2ER/EdBuild
LRPP_cola	Total Revenue from Local Sources Per Pupil Cost-adjusted	F33/C2ER/EdBuild
County	County Name	NCES
state_id	State Agency ID for District	NCES
dType	Local Educaion Agency Type	NCES
dUrbanicity	Locale, Urban-Centric	NCES
dOperational_schools	Total Number Operational Schools	NCES
dEnroll_district	Total Students, All Grades (Excludes AE)	NCES
FRL	Free and Reduced Lunch Students	NCES
FRL_rate	Percent of Free and Reduced Lunch Students	NCES/EdBuild
LEP	Limited English Proficient (LEP) / English Language Learners (ELL)	NCES
IEP	Individualized Education Program Students	NCES
dWhite	White Students	NCES
pctNonwhite	Nonwhite Enrollment	NCES
dBlack	Black Students	NCES
dHispanic	Hispanic Students	NCES
dHawaiian_PI	Hawaiian Nat./Pacific Isl. Students	NCES
dAsian_PI	Asian or Asian/Pacific Islander Students	NCES
dAmIndian_Aknative	American Indian/Alaska Native Students	NCES
d2plus_races	Two or More Races Students	NCES
Tpop	Estimated Total Population	SAIPE
StPop	Estimated Population age 5-17	SAIPE
StPov	Estimated number of relevant children 5 to 17 years old in poverty who are related to the householder	SAIPE
StPovRate	StPov/StPop	SAIPE
MHI	Median Household Income	EDGE, ACS 5-year
MPV	Median Owner-Occupied Property Value	EDGE, ACS 5-year
sd_area	Area (square miles)	EdBuild
students_per_sq_mile	Students per square mile	EdBuild/NCES

Data Processing

• Revenues
• Multiply by 1000
• Reduce year to year fluctuations:
• Subtract C11 (Capital outlay and debt service programs) from the calculation of state revenues
• Subtract U11 (Sale of property) from the calculation of local revenues
• Ensure that revenues and enrollments match:
• Subtract V92 (payments to charter schools) from state and local revenue proportionally
• State-specific processing
• Arkansas: Subtract C24 (Census state, NCES local revenue) from state revenue, add to local revenue
• Texas: To account for recapture, remove L12 (Payments to state governments) from the calculation of local revenue
• Vermont: The school district mergers and revenue pooling mandated by Act 46 in Vermont are still in-process. To be consistent with Act 46 and US Census data, all Vermont data is reported at the supervisory union-level. Student enrollment, state revenues, and local revenues and have been aggregated to supervisory union level.
• Total Revenue = Adjusted State Revenue + Adjusted Local Revenue
• Calculate cost-adjusted revenues
• Apply the county-level, cost-of-living index from the Council for Community and Economic Research (C2ER) to each school district based on CCD-assigned county.
• Demographics
• Calculate estimated student-age poverty rate
• Geographic calculations
• Calculate the area of each school district
• Calculate students per square mile

Taming the System: Common Data Questions... and Answers!

Why National Data?

• Comparability
• Standardized by US Census and NCES
• Clarity
• Even within states, different agencies publish different education-related datasets and these often do not match.
• Purpose
• The right choice for national level studies.
• Useful for comparative purposes for state-level research, reporting, and advocacy.
• Key points to remember
• Poverty threshold is the same across the country.
• Poverty is not FRL.
• Greater than 20% student poverty rate is considered high poverty
• Greater than 40% student poverty rate is considered concentrated poverty
• Data from US Census ACS and SAIPE are estimates, and the estimates are less accurate for lower population areas.
• Education finance and enrollment data are full of known and unknown caveats!

Why revenues?

• We recommend using revenues and not expenditures to assess equity in education funding
• More consistent year to year.
• Different laws and regulations across states make revenue more comparable than expenditures in any given year.

Why not include federal revenue?

• We recommend excluding federal revenue to assess equity in education funding
• Federal money is mostly supplemental, required not to supplant state revenue.
• It also narrows the assessment down to decisions by state lawmakers, those that are actually constitutionally required to provide education.

Which student count?

• We recommend using an enrollment count and not an attendance count.
• Counts the students that districts need to be prepared to educate.
• Attendance tends to undercount students in high poverty, high need districts.
• Student count measures
• Membership
• Average of the number of children enrolled every day throughout school year.
• Average of the number of children enrolled over multiple count periods.
• Number of children enrolled over single count period.
• Attendence
• Average of the number of children in attendance.
• Average of the number of children in attendance over multiple days.
• Number of children enrolled in attendance on one day, usualy around October 1.
• Enrollment (F33)
• Used by US Census; total student enrollment on October 1.

How student count impacts equity assessments

library(openxlsx)
library(ggplot2)
library(scales)
library(knitr)


master17 <- masterpull(data_year = "2017", data_type = "geo") 

### this file was downloaded from https://apps.dese.mo.gov/MCDS/home.aspx
### Current Expenditure Per ADA 2009-10 to 2017-18
mo_ada <- read.xlsx("/MO_ADA.xlsx", sheet = "2016-2017") %>% 
  select(District.Code, ADA) %>% 
  mutate(state_id = gsub("-", "", District.Code))

st_louis_county <- master17 %>% 
  right_join(mo_ada, by = "state_id")% 
  filter(CONUM == "29189" | CONUM == "29510") %>% 
  select(NAME, ENROLL, ADA, StPovRate, SR) %>% 
  mutate(`ADA/Enrollment Ratio` = ADA/ENROLL,
         StateRevper_ADA = SR/ADA,
         StateRevper_Enroll = SR/ENROLL,
         `Miscount Per Student` = StateRevper_ADA - StateRevper_Enroll) %>% 
  rename(`Student Poverty Rate` = StPovRate)

st_louis_f <- st_louis_county %>% 
  rename(`School District` = NAME) %>% 
  arrange(desc(`Student Poverty Rate`)) %>% 
  mutate(Enrollment = comma(ENROLL, big.mark = ","),
     ADA = comma(ADA, big.mark = ","),
     `State Revenue Per ADA` = dollar(StateRevper_ADA, accuracy=1, big.mark = ","),
     `State Revenue Per Enrollment` = dollar(StateRevper_Enroll, accuracy=1, big.mark = ","),
     `Miscount of revenue per student` = dollar(`Miscount Per Student`, accuracy=1, big.mark = ","),
     `Student Poverty Rate` = percent(`Student Poverty Rate`, accuracy = 1),
     `ADA/Enrollment Ratio` = percent(`ADA/Enrollment Ratio`, accuracy = 1),
     `State Revenue` = dollar(SR, accuracy=1, big.mark = ",")) %>% 	
  select(`School District`, `Student Poverty Rate`, Enrollment, ADA, `ADA/Enrollment Ratio`, 
     `State Revenue`, `State Revenue Per ADA`, `State Revenue Per Enrollment`, 
     `Miscount of revenue per student`) 

kable(st_louis_f)

School District	Student Poverty Rate	Enrollment	ADA	ADA/Enrollment Ratio	State Revenue	State Revenue Per ADA	State Revenue Per Enrollment	Miscount of revenue per student
Normandy Schools Collaborative	31%	3,770	3,390	90%	$28,116,000	$8,295	$7,458	$837
St. Louis City School District	31%	28,270	21,422	76%	$105,949,000	$4,946	$3,748	$1,198
Riverview Gardens School District	31%	5,915	5,235	88%	$38,849,000	$7,421	$6,568	$854
Jennings School District	29%	2,570	2,369	92%	$17,127,000	$7,229	$6,664	$565
Ferguson-Florissant R-II School District	22%	10,748	9,027	84%	$56,426,000	$6,251	$5,250	$1,001
Hancock Place School District	21%	1,283	1,413	110%	$9,514,000	$6,732	$7,415	$-684
Ritenour School District	20%	6,485	5,687	88%	$33,276,000	$5,851	$5,131	$720
Hazelwood School District	18%	17,952	16,508	92%	$90,920,000	$5,508	$5,065	$443
University City School District	14%	2,818	2,443	87%	$14,928,000	$6,112	$5,297	$814
Valley Park School District	13%	751	824	110%	$2,999,000	$3,638	$3,993	$-356
Pattonville R-III School District	13%	5,793	5,287	91%	$24,106,000	$4,559	$4,161	$398
Bayless School District	13%	1,571	1,640	104%	$9,077,000	$5,535	$5,778	$-243
Maplewood-Richmond Heights School District	10%	1,366	1,194	87%	$5,526,000	$4,628	$4,045	$582
Affton 101 School District	8%	2,474	2,409	97%	$8,760,000	$3,636	$3,541	$95
Mehlville R-IX School District	6%	10,140	9,558	94%	$36,377,000	$3,806	$3,587	$219
Brentwood School District	5%	676	728	108%	$3,553,000	$4,877	$5,256	$-379
Lindbergh School District	4%	6,677	6,116	92%	$18,660,000	$3,051	$2,795	$256
Parkway C-2 School District	4%	16,603	16,752	101%	$54,630,000	$3,261	$3,290	$-29
Ladue School District	4%	4,063	3,832	94%	$16,757,000	$4,373	$4,124	$248
Webster Groves School District	4%	4,518	4,198	93%	$18,701,000	$4,455	$4,139	$316
Clayton School District	3%	2,254	2,455	109%	$10,987,000	$4,475	$4,874	$-400
Rockwood R-VI School District	3%	19,675	19,852	101%	$76,819,000	$3,870	$3,904	$-35
Kirkwood R-VII School District	3%	5,780	5,263	91%	$18,844,000	$3,580	$3,260	$320

	
stlouis <- ggplot(st_louis_county, aes(`Student Poverty Rate`, `Miscount Per Student`, color = `Student Poverty Rate`)) +
  geom_point(mapping = aes(size = ENROLL)) + 
  scale_color_gradient(low="#dff3fe", high="#19596d") +
  theme_bw() +
  theme(legend.position="none") 
	

St Louis County, Miscount of State Revenue with ADA

Economically Disadvantaged Count

• For national comparisons of economic disadvantage, we recommend using the estimated student poverty rate.
• SAIPE Poverty Rate
• Proportion of relevant children aged 5-17 that are living in poverty (poverty threshold determined by the size of family)
• All children that the school district is responsible for, meaning age-relevant for elementary/secondary districts
• Not enrollment, children may go to private school or not attend school
• An estimation based on IRS data and ACS 5-year population data
• Free- and reduced price- lunch (FRL)
• Proportion of students at or below 185 percent of the poverty threshold, plus some additional non-poor children who meet other eligibility criteria, plus other students in schools and districts that have exercised the Community Eligibility option
• No longer accurate for many schools and districts because of CEP participation
• Under the Community Eligibility Provision (CEP), schools and LEAs with a minimum Identified Student Percentage (≥40 percent) in the prior school year are eligible for school or district-wide free lunch and report FRL as 100%.
• For a deeper look into the impact of CEP on economic disadvantage count, read this working paper.
• Direct Certification/Identified Student Percentage (ISP)
• Proportion of students approved for free meals without an application because they either have been identified as low income by another program (such as SNAP, formerly food stamps) or are considered at risk of hunger (because they are homeless or in foster care, for example).
• State-defined economically disadvantaged count
• Different definition across states, usually a combination of measures
• Ohio's definition:
• Eligible for FRL
• Direct certification
• Students whose families are eligible for public assistance via EMAD
• Through Title 1 form eligibility

How are pensions handled?

• We recommend you follow the US Census, F33 guidelines and keep the estimate of state payments into public school retirement systems in the state revenue calculation.
• There are two ways that payments are made into state public school retirement systems:
• school districts make the payments
• states make the payments on behalf of the school district
• Payments made by the state government into the state’s public school retirement systems on behalf of school districts are included in the tables that display state totals of elementary-secondary education finances. These payments have been estimated for local school systems and appear in the tabular detail for state revenue and expenditure of individual school systems in:
• Alaska, Arkansas, California, Connecticut, Georgia, Illinois, Indiana, Kansas, Kentucky, Maine, Maryland, Massachusetts, Oklahoma, Rhode Island, Texas, and West Virginia.
• Including the state's payments as state revenue makes revenues comparable across states
• In the 16 states that do make pension payments on behalf of school districts, many state-reported revenue calculations do not include them in state revenue, making them incomparable to states where school districts are required to make the payments directly.

Example: Illinois State Report Card- and US Census, F33- reported state revenues.

Illinois State Report Card vs
Census, F33

Illinois State Report Card vs
Census, F33 (w/out state pension payments)

Using master data

Edbuild standard exclusions

• With geography
• Excludes districts that do not have physical school district boundaries and thus are not included in the US Census, EDGE shapefile.
• General
• Excludes districts that are of types 5 (vocational or special education), 6 (nonoperating) or 7 (educational service agency) in the F33 data.
• If F33 school type is missing, excludes districts that are of types 4 (regional education service agency), 5 (state agency), 6 (federal agency), 7 (charter agency) or 8 (other education agency) based on CCD.
• Excludes districts that have missing or zero total enrollments.
• Excludes districts from the US territories.
• Finance
• Excludes districts that are of types 5 (vocational or special education), 6 (non-operating) or 7 (educational service agency) in the F33 data.
• If F33 school type is missing, excludes districts that are of types 4 (regional education service agency), 5 (state agency), 6 (federal agency), 7 (charter agency) or 8 (other education agency) based on CCD.
• Excludes districts that zero missing or total enrollments.
• Excludes districts that have missing or zero operational schools.
• Excludes districts that have missing revenues.
• Excludes districts that have very low revenues (<$500).
• Excludes districts that have very high revenues (>$100,000 in inflation-adjusted 2017 dollars).
• Excludes districts from the US territories.
• All
• Includes all districts, regardless of type and data.

Use the masterpull function to quantify the average school district under each exclusion.

library(scales)
master_fin = masterpull(data_year = "2017", data_type = "fin")

averages_fin <- master_fin %>% 
  summarise(Exclusion = "finance",
             Districts = comma(n()),
            `Average Student Poverty Rate` = percent(mean(StPovRate, na.rm = TRUE), accuracy = 1L),
            `Average Local Rev Per Pupil` = dollar(round(mean(LRPP, na.rm = TRUE), 0)),
            `Average Total Rev Per Pupil` = dollar(round(mean(SLRPP, na.rm = TRUE), 0)))

Average School District Revenues

Exclusion	Districts	Average Student Poverty Rate	Average Local Rev Per Pupil	Average Total Rev Per Pupil
finance	13,037	17%	$7,308	$14,413
general	13,184	17%	$7,439	$14,581
with geography	13,287	17%	$7,457	$14,601
all districts	18,381	17%	$7,923	$15,633

Data Questions

Education Finance Mapping

Data Source

Education Demographic and Geographic Estimates (EDGE), Composite School District Boundaries File.

Shapefile processing

• Correct geometries
• Simplify geometries
• Create districts for newly seceded school districts
• Join master datafile to shapes

Common School District Mapping Questions

What years are school district shapes available?

• Shapefiles come out every two years.
• Back to 1995 (to be used with data for 1989-90 school year).

What's with elementary and secondary districts?

• Most school districts are unified, meaning they educate students of all age and grade levels.
• Twenty-seven (27) states have elementary/secondary districts in addition to unified
• see elementary and secondary districts

master_shp <- sd_shapepull()

sdType <- as.data.frame(master_shp) %>% 
select(-geometry) %>% 
group_by(sdType) %>% 
summarise(districts = n())
	

sdType	districts
unified	10,865
elementary	1,958
secondary	486

EdBuild data tools and resources

• Dividing Lines
• Interactive map of every school district in the country, styled by student poverty rate, covid-19 prevalance, or other educataion finance and demographic measures.
• data.edbuild.org
• Download the master data file for any year 2013 to 2017.
• Download the school distirct shapefiles for any year 2013 to 2017.
• Read the methodology for creating EdBuild's master datasets.
• School Finance Data Dashboard
• Think like an EdBuild-er for real!
• Interactive dashboard to visualize education finance, enrollment and demogtaphic data for every school district and state.
• EdBuild's R packages
• edbuildr
• edbuildmapr
• Workshop on edbuildr and edbuildmapr

Questions