Blog

As mentioned in a previous post, I am in the process of updating the mathematical model used by my school to determine when students are ready to take college-level courses. This model is important to us because we send over a third of our juniors and half of our seniors to college each year and we don’t want to mistakenly send students to college before they are ready. Using this model, my team has gotten pretty good at determining readiness; last year our students passed 97% of the college courses they attempted.

Before the model can be applied, it must first be brought together into a single database or spreadsheet. Depending on your systems, this can be a quick or timely endeavor. For me, bringing together all of the data we have on students took a little over six hours. Here’s what I did:

Google Sheets

Because it is shareable and applies edits in real-time, I do all of my modeling in a single Google Sheet. For anyone who is an Excel devotee, this may sound crazy. It is. But, for me, the benefits outweigh the costs.

For this year’s update, I created a new Google Sheet called “Master Data File” where I pasted an export from our Student Information System (SIS) containing each student’s name, ID, DOB, sex, graduation year, and commutative GPA. Because our SIS contains the most up-to-date information regarding student enrollments, I always start there and then use that data as reference for gathering the rest. No need to gather data on a student no longer enrolled.

Microsoft Excel

So far, there is only one function I need that is not easily done in Google Sheets: consolidating data. At one time, I would spend hours manually inputting data from one system’s export file to another. Excel can consolidate data from two spreadsheets in minutes.

The Consolidate function is in the "Data" ribbon on Microsoft Excel.

For example, data downloaded from the College Board website looks different than data taken from our SIS. The College Board data includes some students who have left my school, is missing data for students who are newly enrolled, and may have other formatting differences that would make a simple copy/paste impossible to do.

As long as I have a single column that uniquely identifies individual student (student ID, “Last Name, First Name” combinations, etc.), Excel can consolidate the data from both sources into a single row to be included in the master file.

Data Brought Together

Here’s the data I consolidated into the single Google Sheet for each student organized by source:

Student information System

Demographic Information used for sorting and aggregated data analysis

High School Grade Point Average: used as a primary indicator of future college success. This topic will be expanded upon further in a later post.

College Board

PSAT 8/9, 10, and 11: We give the PSAT to all students every year in grades 8 through 11. While we do not yet use this data in our model, I decided to pull it in hopes of future analysis and reporting.

SAT: In Michigan, all 11 graders are required to take the new SAT. Our community college partner accepts SAT scores for determining college course placement, so we use these scores as part of our readiness model.

Accuplacer: While this is technically a College Board product, we get this data from our college partner. Our students take this college placement assessment each year until they place into college-level coursework beginning in the 9th grade.

ACT

ACT: Now that the state of Michigan has moved from ACT to the SAT for it’s college readiness assessment, we only have a few students each year who take this assessment. For those who do, though, I need to consider their scores when determining readiness.

Compass: Until this year, our college partner used the ACT’s Compass assessment for determining college placement. This assessment was replaced by Accuplacer but we still consider Compass data in determining students’ college readiness.

Other

Agency Score: Each year, we ask our teachers to rate each student’s skill at exercising agency on a scale of 0-5. Agency, for those not familiar with the concept is one’s ability to be an “agent” of his or her own learning. It consists of two components, both a part of our instructional model: 1.) ability to complete tasks to specification and on time, and 2.) growing from challenging work and setbacks. I simply ask teachers to rate each student and take the average of their input. More on this measure of college readiness later.

When recording assessment data, I like to separate it by the year it was taken relative to the student. I like to know what each student’s score was each year they took it. This allows me to see growth or stagnation in student performance, and makes analysis and reporting of data much easier to do.

Over the next few weeks, I will be updating the mathematical model I created to predict students' future success in college. That model, which my school has been using and revising for the past four years, looks for patterns in academic and behavioral data to help predict individual student's likelihood of earning passing scores in college coursework.

I created the model in response to learning that standardized test scores alone left far too many edge cases to accurately predict future academic success. Too many students had previously scored well on tests yet did poorly in college classes. Similarly, some students we thought could handle college coursework did not score well on traditional measures of college "readiness."

Using this model, my school sends a third of its juniors and half of its seniors to college. Last year, these students passed 97% of the courses attempted. Ninety-three percent passed with a C or better.

To learn more about my school and why we send so many students to college while still in high school, I recommend reading my post from June titled Early College For All.

There is nothing magical about the model. It simply applies what is already known about past students' success to predict how well current students might do in college coursework.

The model uses three primary sources of data:

Standardized college placement or college readiness scores: I have used data from different assessments over the years with relatively similar results (Compass, Accuplacer, ACT, and SAT).

High school grade point average: in my school, the strongest predictor of future academic success is past student success.

Teachers' subjective assessment of student "agency:" Each winter, I ask my faculty to evaluate each student on how well they are perceived to grow through challenging work and complete work on time.

Each year, the weight applied to each of these data sources has changed to reflect what we've learned about past student success. Last year, high school GPA and test scores were weighted about evenly. Agency, while found to be an accurate predictor, was weighted very little (approximately 10%) due to its subjective nature and the potential for perceived bias.

Over the coming weeks, as I update the model, I hope to share more of the details that go into its creation and revision. I see great value in having more schools analyzing data in this way and think it's a simple enough process that can be replicated with bit of time and effort.

Disclaimer: I am not a mathematician and do not claim to be an expert in inferential statistics. I am simply a practitioner with a good memory of his Statistics 101 class. I welcome any feedback from readers with stronger mathematical grounding.

If you have questions about this model that you would like me to expand upon or would simply like to learn more, feel free to leave a comment or reach out by email.

Last week, I wrote a press release about my school that was picked up by our local paper. I'm proud of the work that it represents and I'm proud of the students who make this possible. So, I want to share it here:

Two-Thirds of Meridian Grads Opt for Free First Year of College

SANFORD, MI - June 4, 2016 - One hundred twenty students walked across the stage at Meridian Public Schools commencement ceremony this past Thursday night, but only forty of them took home a diploma. That’s because the other eighty students – two-thirds of the graduating class – have chosen to participate in Meridian’s fifth year program for a free first year of college.

“As an early college high school, we are set up to offer all students five years of education,” said Patrick Malley, Meridian’s high school principal. “During students’ fifth year, they take a full-time course load with one of our early college partners.”

The majority of students will take courses at Delta College. Others have opted to earn vocational credentials through the Greater Michigan Construction Academy or Bayshire Beauty Academy.

None of the students participating in fifth year have to step foot on the high school campus. For the most part, they are treated just like any other first year college students.

“This graduating class has already earned over 1,600 college credits during their junior and senior years,” said Meridian Superintendent, Craig Carmoney. “Now, with so many of our students staying for fifth year, we estimate that over 90% of them will participate in postsecondary education.”

Meridian transitioned its high school to an early college four years ago, when the students in this class were just freshmen. According to Principal Malley, the decision to become an early college made sense considering the work they were already doing: “The district had just joined the New Tech Network in an effort to improve student success after high school. We had the support of our teachers, parents, community, and Board to re-imagine our high school to improve outcomes. We saw alignment between our work with New Tech and the Early College movement, so we went for it.”

As one of only twenty-two early college high schools in the state of Michigan, Meridian is offering students an experience that was unimaginable just five years ago. In the fifth year, students receive funding for tuition, books, and supplies. They also get monthly gas cards to help offset the cost of transportation, and are assigned a laptop that they can use in the classroom and take home. Additionally, they are linked with an Early College Coach who supports them through their first year college experience.

“Our goal is to remove as many barriers to college and career success as possible for these students,” said Superintendent Carmoney.What are the other third of the class not staying for the extra year doing next year? Most of them applied for an early graduation after just four years and are going on to a university, the military, or to work in a family business. Because of the opportunities offered, only a few of them graduated undecided about their next step after high school.

“While our evaluation of the success of this program will have to wait until students finish their fifth year, the early results appear very positive,” said Malley. “By eliminating the major stumbling blocks to college success – funding, transportation, and support – we anticipate we’ll see our first year college completion rates more than double our ten year average.”

Based on the program's past success and the number of students participating, it is likely that over eighty percent of Meridian graduates will earn a year of college credits before exiting the program.

According to Amy Boxey, Dean of Student Transitions at Meridian, some will even graduate with over 60 credits.

“Our goal is to send students to postsecondary programs once they are able to show us that they are ready,” said Boxey. “Students who demonstrated readiness their junior year went to college. More were ready and went to college during senior year. Now that these students are entering the fifth year, the opportunity has opened to all. We look forward to supporting so many of our students on their next step after high school.”

It was nine years ago that I launched NewSchool Learning, a small Moodle design company that has produced just shy of 1,000 custom themes for clients across the planet. As of this past December, sadly, NewSchool Learning is no more.

The reason I decided to close shop is simple: the company stopped making money. Revenue had fallen 50% each year for the past three years. This last quarter, expenses exceeded revenue and I knew it was time to call it quits.

The reason for the decline in revenue? I honestly cannot say. I’ve been too distant for too long from the day-to-day operations of the business and the Moodle community to even guess. I just know that the accounting stopped making sense and that meant it was time to call it quits.

The fact that NewSchool Learning lasted as long as it did is pretty amazing, and credit certainly belongs to Lead Designer and Developer, John Stabinger. As I moved from teacher to school administrator over the past five years, I stepped farther away from my work with and on the company. John has kept the lights on while I’ve paid the bills, and for that I’ll be forever grateful.

I’d be lying if I said I was wholly sad to see the company close; part of me is happy to be free of dealing with payroll, accounts, and corporate taxes. At the same time, nine years of relatively passive income from a company that started as a curiosity makes it challenging to say goodbye.

According to Katrina Schwartz's reporting on the pilot last month, the professors involved were surprised by what they, themselves, learned by doing assessments in this way:

Professors began realizing how much the language of their assignment prompts communicated what they expected from students. That might seem obvious, but without other samples to compare to, professors just thought their students didn’t have the skills.

"From an evolutionary perspective, school is an abnormal environment. Nothing like it ever existed in the long course of evolution during which we acquired our human nature. School is a place where children are expected to spend most of their time sitting quietly in chairs, listening to a teacher talk about things that don't particularly interest them, reading what they are told to read, writing what they are told to write, and feeding memorized information back on tests."

We need to be talking more as a society about our perceived need to medicate 12% of boys and 4% of girls to make it through each school day.

This year, using Google Forms and two Add-Ons, I cobbled together a system that allows teachers to account for our 400 high school students during a relatively open 30-minute period of their day. When developing this system, I had two primary objectives:

I wanted students to choose which class they go to for this 30-minutes.

I didn't want to use passes or paper and pencil sign-ups.

I needed to know where students were at and whether or not they attended, but I didn't want students to have to go to one teacher's class for attendance just to leave (as is done in typical seminar-like structures I've seen elsewhere). I find this to be a waste of time and energy.

After some help from the internet and time to tinker, I came up with the following system:

1. Students Register for the Class They Want to Attend

We offer these 30-minute periods, called FIT (for Focused Instructional Time) every Tuesday and Thursday in the middle of the afternoon. Prior to the start of each FIT period, students navigate to this page on our website, click on the name of the teacher who's class they need to focus on, and complete a Google Form letting the teacher know they plan to attend.

For reasons that will become evident in the next step, each of the registration links on this page go to a separate Google Form specific to that teacher's class.

2. Registrations are Capped at Thirty Students Per Class

To prevent some classes from becoming overrun with students, I use the formLimiter add-on by New Visions Cloud Lab. While not perfect, this add-on looks at the spreadsheet where registrations are being recorded and turns off the form once registration levels hit a pre-determined level (in our case thirty students).

3. Students Receive an Automated Email Confirming Registration

Whenever a student successfully registers for a class, they see a message on the screen and receive an email confirming our expectation that they will attend. This email is sent using Google's own Form Notificaitons add-on. It becomes the fall-back receipt in the event of a registration getting lost.

4. Registrations are Captured in a Single Google Sheet

To ensure my entire staff can easily find any student during this 30-minute period, I have set the destination of each of the individual teacher's registration forms to the same Google Sheet. My entire staff needs to have edit rights to this spreadsheet, so I protected all of the cells they shouldn't edit to prevent errors from breaking everything.

5. Teachers Take Attendance Using Registrations

Every Tuesday and Thursday, during this 30-minute period, teachers open up the spreadsheet, navigate to their course tab, and take attendance using the list of students who have registered to be in their class during FIT. If a student who registered is absent, the teacher copies the registration information and pastes it into another tab labeled "Absent." My dean of students checks the absent tab for students who should be present while my instructional coach and a paraprofessional "sweep" the halls looking for students who may have forgotten to register.

6. Once Attendance is Taken, Teachers Delete the Registrations

Within the sheet is a tab containing formulas that count the registrations for each teacher. It is this tab that each registration form looks at to determine whether or not the class is at capacity (30) and the form needs to close. Deleting the day's registrations after attendance is taken resets the count tab value to zero for the course allowing another 30 students to register the next time around.

If a teacher forgets to delete their registrations after taking attendance, then only as many students as seats available will be able to register the next time around before the form automatically shuts itself off. For this reason, deleting student registrations after attendance is taken is a key behavior to ensuring this system works.

Every Friday, I call five parents. While calling them, I share something great about their student from that week. It could be a concept they worked hard to improve, a great peer interaction, or showing respect to me or another teacher. I do this every Friday without fail.