Twin Rivers Adult School CASAS Tracking Super-sheet

The Program of Practice

Staff and Teachers in the ESL and HSD programs were unable to see key student data in one place, affecting the ability to accurately and easily decide who needs to take a CASAS post-test and reducing transparency for all staff.

Lack of paired tests is identified as one of the top issues facing Adult Ed programs, and we wanted to improve our system. Testing decisions at TRAS are made on a combination of factors: Most recent test date, Total hours in the program or since their last test, whether they have a post-test at all, and whether or not they made an EFL gain. However, finding all that information relied on running multiple different reports from ASAP connected and TopsPro Enterprise (TE) and cross-referencing a student between them. This was too time-consuming and technologically complex for many staff to do reliably. Thus, many students fell through the cracks with no post-test or left the school with many hours of instruction between their last post-test and their exit, but no measured EFL gain.

This was the previous process of successive questions we previously used to make testing decisions, and the way we answered them for each student:
*Do they have a post-test and when were they last tested? →
**Process: Look at ASAP Test details (multiple clicks and possibly inaccurate without TE import) or TE (Student Gains Report + lots of filtering or scrolling)
*Do they have enough hours to test?
**Process: Look at ASAP Attendance Details and add up across multiple classes in the program, or if it was imported, use TE Total Student Hours report and add up across multiple classes in the program.
*Do they have an EFL Level gain?
**Process: Run TE Student Gains Report + lots of filtering or scrolling to find the student

Students leaving in the middle of the year for a job or being dropped for non-attendance may or may not need to be tested again. We wanted to identify those students more easily and accurately, and simplify our system of multiple reports by consolidating key data into one place. This would enable clearer, data-driven decisions on testing, made with criteria that could be viewed side by side. We also wanted to retain the accuracy of the data source if possible (TE or ASAP)

The Response

Our first method for solving this problem was introduced in 2022-2023. We made a shared score tracking sheet to help us track who needed to test, sorted by student level. It was useful to help all teachers see the data for testing, but with constant manual input of the data and the regular need for revision with new data, it was difficult to sustain accuracy throughout the year, and the information on the sheet was limited to CASAS scores, requiring running reports for other data. Before each testing cycle, a staff member would manually highlight all students on the Google sheet who needed to test based on their hours and EFL gain status. (2 reports) It was effective but time-consuming, requiring a lot of manpower and not always accurate, so we improved the practice.

To solve many of these issues, in January 2024, inspired by schools like Moorpark Adult School, which created their own student information system, we created a combined workbook in Excel with Power Query formulas that merge each student's data from 4 main reports:
1. Total class hours and First and last attendance day (TE Total student Hours report)
2. Student/Class Enrollments and student emails (ASAP Super Enrollment report)
3. CASAS Pre and post-test scores and forms, and EFL gains (TE California Student Gains report),
4. EL Civics COAAPs passed (Payment points report, drill down)

This Excel sheet (with Power Query formulas) filters all that information into one main spreadsheet and also into each teacher's tab in the workbook and sorts it by class, so teachers can see only their students. It is shared on Google Drive like the previous manually entered sheet. To help make testing decisions even easier and automatic, a conditional "TestNeededFlag” column was added that uses key data like last test date, total hours, and EFL gain to see if a student needs to test based on criteria TRAS can alter as needed throughout the year. It is also coded to show viewers why a student does or doesn’t need to test.

The sheet is updated weekly or more frequently during testing with fresh reports, ensuring that teachers do not retest a student who has already been tested in a different class. We also tried using a similar sheet with the high school program (ABE/ASE), but with limited resources, we focused 24 25 on refining the ESL sheet further.

The Unique Features of the Program

What makes this program unique is its ability to streamline and improve data transparency in ways that benefit decision making in many areas of our program, even beyond the intended problem of practice it was initially developed to address (Testing Decisions).

* Because we were able to add data points such as first and last attended date and hours totals, we have been able to use it to spot issues with student attendance and pinpoint who needs to be dropped from classes when teachers fall behind in dropping students.
* The update process to maintain accuracy takes only 15 minutes for CASAS score and enrollment data, and a little longer for hours and attendance data to be refreshed. This way it can be updated as needed.
* The report isn’t static and is shared live to all teachers and staff, refreshed at least weekly. When students are close to meeting the minimum hours we set to test, and the data is refreshed as they get more hours, the conditional column will change from “Low” to “Yes” indicating it is time to test them.
* Although the CASAS teacher portal has student scores and some more detailed reports on competencies, this sheet helps our teachers see students' scores side by side with columns for Pre-test, Post-test, and Latest-test, all in one place.
* The sheet also includes a section for students who have been dropped, allowing teachers or staff to contact them to request one last post-test before they reach 90 days of inactivity, which would invalidate their pre-test.
* With minor edits for new teachers and classes that change each year, we can use the same sheet every year thereafter without needing to rebuild it completely.
* We are able to achieve high rates of post-testing while maintaining continuous enrollment and avoiding over-testing students. Students are still mainly tested only every two or three months.

**Potential for further development**
We can add more features as needed to make our program more effective. Our next steps are to make a similar sheet that meets the needs of the HSD program. We are also considering creating visual conditional formatting flags for those students who may not be able to make an EFL gain in reading due to being at the bottom of the score ranges. We can use the sheet to flag those students early so they can be pre-tested in listening as well.

**Replicability**
Because the report uses TE reports and ASAP reports available at all or most Adult Education institutions and common programs like Power Query and Excel, the tool itself can be adapted to other schools with some changes to fit the institution using it. It would not even need to be fully rebuilt, but maintenance would need light training. Four local adult schools have reached out asking for help setting up the same report at their school, and we have plans to pilot adoption at one school this summer and present the tool to our consortium in Fall. It is also adaptable to different institutions' testing schedules and processes, because the parameters set for the date of the most recent test and the minimum program hours for testing can be easily changed. If other schools find the tool as helpful as we have, we have long-term goals to present at CCAE, partner with CASAS/ASAP to improve the tool's reports, or collaborate with CASAS to build a similar tool in the Teacher portal.

The Outcome

Staff Appreciates Transparency in testing
The automated sheet allows any staff member to determine if a student requires testing quickly. When a student withdraws, teachers and staff can consult the sheet for their last post-test date and direct them to the testing center if necessary. In the event of an absence on testing day, teachers can readily identify students who need follow-up. For persistent non-participation in post-testing, administrators can track the last attendance of students requiring assessment and collaborate with teachers to ensure testing occurs before continued attendance. Both teachers and office staff have expressed appreciation for the clarity the sheet brings to the task of post-testing students.

Decreases in students lacking post-tests
As a result of this shared tracking sheet, we have seen a steady decrease in the percentage of ESL students who have a pre-test and no post-test with many accumulated hours.

When we isolate our data to only ESL students (Taking total count of students on CAEP DIR and dividing it by ESL Enrollees in the CAEP Summary) we see significant improvements over these past 3 years. Our rate of ESL students with 70+ hours and no post-test was reduced from 10.3% in 21-22 to 4.1% in 22-23, when TRAS began to use the manually updated shared Google sheet in ESL to track testing.

In the 23-24 school year, in the first semester, testing decisions were made using the manually updated spreadsheet. In Spring, we began using the automated spreadsheet. The rate of students with 70+ hours and no post-test for 23-24 was 5.77%. It was slightly higher than 22-23, but still well under the 21-22 rates, as we quickly transitioned from a system that had not been working effectively with our reduced staff capacity.

After the full implementation of the spreadsheet in ESL classes this year, 24-25, the percentage of ESL students with 70+ hours and no post-test has reduced further, and as of May 24th, the number of students missing post tests with over 70 hours was only 1% of the total ESL population, from CAEP tables. (8 students out of 802 enrollees)

This drop in ESL students missing post-tests has helped our overall school numbers drop well below state averages on the CAEP Data Integrity Report for missing post tests for students with 70+ hours as well, and we hope it will drop further as we better the sheet each year and implement a similar sheet in the HSD program.

Item 22c “No pre-/post-test pair but 70+ hours of instruction”
Statewide Data Integrity for 2023-2024 Q4 : 6.23%
TRAS CAEP Data Integrity item 22c “: 21-22 (7.34%), 22-23 (3.37%), 23-24 (3.82%), 24-25 so far (3.17%)

Conservation of staff resources
The current automated sheet has minimal upkeep time and is highly reliable due to the import of source data. Staff formerly assigned to manage post-testing and track down students are now able to use that time to support the program and students in other ways.