We are going to answer the question: How do the student demographics at 6 randomly selected schools in your CMO compare to their surrounding districts?
Specifically, we want to know the racial/ethnic composition of the schools, the proportion of ELL, SPED and low-income students (by free and reduced lunch eligibility).
FRL status is an imperfect measure, but the best one currently available for examining student poverty. To be eligible for reduced price lunch, a family of 4 makes $44,955 or less. To be eligible for free lunch, a family of 4 makes $31,590. Robert Cotto argues it’s important to examine both rates separately. A charter might have 79% FRL students, but only 20% of these students might be free lunch eligible, compared with a district that is also 79% FRL, but 65% of the students are free lunch eligible. Why is this difference be important?
Here are the steps to follow for collecting this data:
1. Select 6 schools from your CMO list using a random number generator.
2. Create the following google chart using this template. Share with your group members so that you can all enter the data at the same time.
3. There are two ways that you can go about finding school level data on race/ethnicity, FRL, ELL and SPED. First, locate the most recent school data at the state level website and download these reports, and then input this information into your spreadsheet. (These state reports may also have performance data which you can use later.)
CT’s version is called EdSight (panopticon, anyone?) http://edsight.ct.gov/SASPortal/main.do
4. Repeat this process by downloading district level data from the surrounding school district/s.Note as you select the school district, that you want the surrounding public district. Charters are sometimes listed as their own school district (which are composed of only the schools in the CMO.) We want to compare the charter schools to the surrounding public district.
5. Paste the district level data starting in column Q on the google sheet.
If you are able to find all of this data from state level websites, congratulations! Skip to step 20.
6. If the information is not available at the state level, you can look for most of it from the Federal National Center for Education Statistics. To derive school level data on Race/Ethnicity and FRL students, find the school listing on the state data site and the NCES federal data site. Go to http://nces.ed.gov/ccd/elsi/
7. Select Table Generator, and click “I agree” to the terms and conditions.
8. Under “Select a Table Row,” select for a Public School.
9. Under “Select Years,” select 2014-15
10. Under “Select Table Row,” select the following:
Information>>Contact Information (located at the bottom of the list) select
-
Location Address [Public School]
-
Location City [Public School]
This will give you the information to find the comparable district in the second part of this exercise.
11. Under Enrollments select “Total Enrollment.” This gives us the total figure of students to calculate the proportion of students in all of the categories.
Total Students [Public School]Under “Students in Special programs” select
Total Free and Reduced Lunch Students [Public School]Under “Enrollment by Race/Ethnicity” select
-
American Indian/Alaska Native Students [Public School] -
Asian or Asian/Pacific Islander Students [Public School]Hispanic Students [Public School]
-
Black Students [Public School]
-
White Students [Public School]
-
Hawaiian Nat./Pacific Isl. Students [Public School]
-
Two or More Races Students [Public School]
12. Click on the Green button at the bottom called Select Filter.
13. Select filter by individual state, and pick your state or multiple states.
14. Select the button at the bottom “Create Table”
15. Click on Export Excel and the File Download – Download Zip File.
If you messed up, you can select the button “Modify” to change your criteria.
16. Unzip the file and open the Excel Document.
17. Using the toolbar, Increase the display size to 125%.
18. Use the Find function ( control F) to locate the lines of data for your schools. Copy and paste these data lines to your google spreadsheet. Double check that your categories match up.
19. Repeat these instructions to download NCES information for the surrounding school districts. Note as you select the school district, that you want the surrounding public district. Charters are sometimes listed as their own school district (which are composed of only the schools in the CMO.) We want to compare the charter schools to the surrounding public district.
Now that you have your data, you will do the following steps to calculate demographic percentages and the difference from the school district.
20. Insert TWO columns after White Students 2014-15
Call the columns “% White 2014”, “% Students of color 2014”
16. Calculate the % White students in 2014-15.
SAMPLE FORMULA: =White Students [Public School] 2014-15/Total Students [Public School] 2014-15
Excel tip! You can quickly use the same formula for an entire column by dragging the small box in the bottom right corner of a formula cell down to highlight the length of the entire column.
17. Calculate the % Students of color in 2014.
SAMPLE FORMULA: =1-% White Students 2014-15
18. Insert FOUR columns after District White Students 2014-15
Call the columns “% D White 2014”, “% D Students of color 2014”
“% W diff”, “% SOC diff”
19. Calculate the % district White students and students of color.
20. Calculate the percentage difference of white students and students of color between the school and the surrounding district.
SAMPLE FORMULA: =School % White- District % White
23. Repeat these calculations to determine the % free, % reduced, % free and reduced lunch eligible students at the schools and the surrounding district.
24. Repeat these calculations for ELL and SPED students if you have this data.
25. Do your numbers make sense? Check your work!!
26. If you notice growth in a particular racial/ethnic category, you may also want to create separate columns to isolate and measure that difference .
27. Discuss in your group – what trends do you notice that are different? Are the charter enrollments significantly different than the districts? In what ways?
28. Last step: Create a chart (select icon “Insert chart” on google sheets) of the difference of student enrollments by students of color between your charter schools and the surrounding district. For your CMO project, you will want to create graphs for the several enrollment dimensions that you think are significant.
26. Download the googlesheet you have created with the accompanying chart, and turn it in on Canvas as your March 3rd data assignment. Good work team!