THATCHER WELDON: So thank you everybody for being here. Like I said, this is kind of a-- OK, you see the slideshow now, right? So everybody, thank you for joining. This is going to be a chat and discussion. And I want to just give you kind of a heads up of what it is we'll be going through so that maybe if it's not what you're looking for, there's another session you could check out. 

So last year, me and our team, we did a similar presentation about our data tables that we set up in Microsoft Excel. And some of the feedback from the group was that they wanted more information about how we set it up. We went through how we use them. We went through the basics of them, but we didn't show how we made them. 

So what we'll be going through on here is how to do an Excel TOPSPro data export, with that, how to develop an easy-to-read Excel data table for you. So I'll go over the basics there. And then how to consolidate the TOPSPro Excel export into your data tables. So how to go from that data dump from TOPS into something a little bit better to read, and then how to create tables to check increases by month and differences between fiscal years. So I've created these tables so that we can every month look at our gains and then look at how we're comparing to last year. 

And then how to create used functions to check on student retention, outcomes, and gains. So if that doesn't sound interesting, you're not going to hurt my feelings. And like I said, if you have any questions, stop me at any time, and we can discuss things. So here we go. 

The return of the data: how to go from a scruffy data Nerf herder to a data Jedi. I'm Thatcher Weldon. I'm the director of Adult education at Kern Community College District and the current adult education consortium. So who I am, I just said. So this session, like I said before, it's for anybody who feels like a total beginner in using data to make informed decisions for people who want a very basic start in using Microsoft Excel or Google Sheets to develop and format your own data tables to begin consolidating your data. 

And I put them in the chat, the data tables and all the stuff from this session. So feel free to download them and go along with me if that's going to be helpful for you. So a little bit about our consortium. Were the current adult education consortium. We consist of nine districts adult education providers, three county offices of education, two of which provide adult education services for community college campuses, and then one community college district office. 

And we serve over 24,800 square miles from rural farming communities to rural mountain communities, the ninth largest city in California, Bakersfield. And we cover four counties, so we're Tulare, Kern, Inyo, and Mono. And let's see. And let's see. 

And so we have one of the largest adult schools in our consortium, and then we also have tiny rural consortia or members that consist of one lead and then a few part-time teachers and such. So very different groups, part of our community, as I'm sure many of you have too. And then here is our service area. We are the largest geographic service area adult education consortium in the state. And these are some of the services our members provide. 

So just a couple of questions. So what tools does your consortium use for data planning and goals? So I believe simple tools can help consortia, schools, colleges, staff, faculty, make informed decisions on goals, instruction, and attainable outcomes. So what are the data tools you guys use at your site in your consortia? So data meetings, good. On quarterly basis. 

And one of the reasons I created this was when I first started, I was the only one in the consortium lead or in the consortium management position. I had some assistants in the office here who provided part-time support, but we didn't have a full-time data person for the consortia. All the sites have their data processes and whatnot. So it was me, and I wanted a way to look at data and to-- good, quarterly and yearly comparisons. That's great. 

And how do you do those quarterly and yearly comparisons is one of the questions I wanted to think about too because I'm going to show you how we do ours every month. Good. WestEd, TOPSPro, LaunchBoard. Yearly review, they're all great. But they're yearly reviews, so it's not something I can pull next month and compare to this month, so good. Extract to Excel. Yeah, that's good. So you're already there. So Brenda said extract Excel. 

So yeah, that's what we're going to do here. And hopefully, this will make things a little bit easier to view and use for your consortium on your site. So we all know and use these tables. For me, I have the manager summary reports. You might have your CAP tables for your site that break down by your classes, et cetera. But we all know this. 

It's easy to read. If we've been in adult ed for a while, this makes sense to us. So what do you guys use to TOPS tables for? I'm curious because-- so quarterly reviews. We've got, do you have meetings with faculty, staff, admin, consortia? Do you look at them in your monthly meetings? How do you use your TOPS tables like those? 

All of the above. Good. Good, compare with ATF, yep. Filling in the CAPs, good. So when I was pulling these, I like the PDF form. And the PDF form is really easy to read for me. Three-year planning, yep. You just started using the tables for three-year planning? Excellent. so hopefully, this will be a tool you can use to help with that. 

So some of the things I like. I said the PDFs are very easy to read. The negatives that Excel export is just kind of a data dump. It's hard to figure out what to do with that data if you're just using what they export from TOPS. So I'm guessing some of you are familiar with the Excel data tables that come out like this. 

So this is going to be a live demo, like I said. Just stop me at any time. I'm fine with people asking questions, unmuting, and having a dialogue, but we're going to go through this process together. I believe the files were shared again. So you can open those up if you have two screens, and you can follow along, or two computers. And we'll just go through, and you'll see why it's so important to set these up in the first place. 

So taking the time to set them up is really valuable for these to be meaningful and easy to use ongoing for your consortia. So I'm going to stop the slide show here. I'm going to minimize this. I'm going to open up student data tables. So can everybody see this? 

SPEAKER 1: Yes, we can. 

THATCHER WELDON: All right. So what I did was I wanted something where I could pull the numbers and put them into something that we all could read at our board meetings, that teachers could read, that staff could read. So I just created this. If you're even a novice in Excel, you can learn all this fairly easily. It's just moving these tabs to create height and column width, and then just creating borders. 

You can just simply create borders. You pick the cells you want and then you-- where is the borders on this? I was working on Google. Is it-- or you can click. There we go. And you can do the borders right here. And you can do thick borders. You can do regular borders. It's all simple. It's all how you want to do it, how you want to create this to make it easy to read for you. 

And then once you've got that, what you'll need to do-- let me go back here actually. And we're going to do-- so I'll show you. So if you've never done a TOPS Excel data export, the way you get there is go into TOPS. You go to Reports, State Reports, California. And then like I said, you might do CAEP tables if you're the lead of the site. 

I'm pulling the CAEP consortium manager reports. And then I'm just pulling CAEP manager tables. And that will take me to this screen. And I'm just going to click Generate. I want the current program year up to date what our numbers are. Click Generate. That'll pop out the PDF files tables like this, and then you're just going to click Export up here, if you haven't done this. 

And once you click Export, it'll ask you where you want to save them and what type. And you'll go down to Excel 2007 workbook. And then that will get you here. So that will give you something that looks like this. 

And like I said, it's kind of all jumbled together. Here's our numbers, but I can't just bring this to a board meeting and say, here, figure this out. This is what we're at. So I wanted to take this, and what I would do is I would copy all this, copy, and then I put them in our manager tables here. I paste them in here. I just click here, paste, and I get that Excel data dump into my first sheet here in my created Excel file. 

Now I want to get these numbers from Lowland Adult School. I want them to populate into here. So how can I do that? Well, I just click on the cells that I want. And I'm going to go to Data, and I'm going to consolidate. And it pop up something like this. 

And all I've got to do is click here, and then I go back to my manager tables. And I find Lowland Adult School. Here it is. There's my Lowland Adult School. Oops. No. So I go back to Lowland Adult School. So I just want my numbers, and I'm going to go all the way down to row 12. That's the final total unduplicated students for Lowland Adult. 

And I click there. Hit Enter and then Add. And then I click OK, and all those numbers will populate into my Excel sheet. Now I've been doing this for about 2 and 1/2 years with us. So I've been keeping track of this. I've saved these files at all of our monthly meetings. 

So I can go back and I can take-- I can go to Google Drive where I save all of our adult ed meetings, and I can take our previous month, and I plug it into another sheet. Now when I have this file, I have negatives here, and I'll show you why. So I want to know how much we've grown from the previous month. 

So in Excel, I can do a sum. So when I go to this next table, Lowland adult growth from the previous month, what I'm going to do is I'm going to consolidate again. And the function they have-- they don't have subtract. They have sum, so I'm going to sum these two, last month and the current month. 

So when I go back to the previous month, I want to make sure these are negative because I just want to look at growth. So I copy that negative 1. And I'm just going to Paste Special right there, Multiply. And that'll give me negatives for those. 

And then I'm going to go back to Lowland adult growth from previous month, and I'm going to consolidate now with the sum. Again, I click here. Then I go back to my manager tables, and I start for Lowland adult down to the total unduplicated students, 12, on row 12. And again, hit Enter then Add. 

And then I'm going to go to this table too, the previous month. And these are my negatives, row 1 through 12 because Lowland Adult is lining up in the same column for these tables. And I'm going to click Add again. And then this will give me this month minus the previous month. So I can see that Lowland Adult had 54 more unduplicated students served this month. We gained 54 students here. 

We had three more students get 12 hours of instruction. And then of those, we've had three students who have been pre-tested. So I can start seeing how we're doing each month. And then in addition to that, I also want to look at the previous year. So I plug in. I go back into our board meetings, and I go through what we had October of last year. 

And again I do the copy negative 1, and then paste, multiply, for the previous year because I want to know what the differences are. So then I'll go to the previous year, do the same thing. I'm just going to consolidate, sum again. And I'm going to go to AEP manager tables, Lowland Adult again, so through column 12. 

Enter, Add. And then I want to sum that with Lowland Adult. Well, look, it's different. You have to be careful because sometimes TOPS, their reports change things up. Every year, we might have a new member or, especially in the beginning of the year, maybe some members are behind on their data reporting. So they're not getting their numbers in yet, so not always it will line up correctly. 

So Lowland Adult is down here. I want to consolidate with these numbers. I'm going to hit Enter, and there we go. So I can compare how we're doing this year compared to last year. So from the previous year, we have 107 more unduplicated students served for Lowland Adult. 

For outcome enrollees, we have five more than October of last year. And then we have six more students who have had a pre-test compared to last year. So we can start making those comparisons. Does that make sense? Are there any questions, comments? 

So we have that. And what we can do with that-- if you remember, about two years ago, Jay Wright started focusing on enrolled students who receive one hour of instruction. And then of those students, how many stick around for 12 hours of instruction? And how many of our students, what percentage of our students get outcomes in adult ed? 

Well, we can create formulas for all that just by simply going in here. And the formulas, for AEP outcome-- let's start here. So enrolled students who receive one hour of instruction, how do we find that out? Do you remember? There was a-- I think it was-- what was her name? Carol Hirota from Stockton Adult. She was talking about reviewing with her teachers about their retainment, their outcomes. 

And so she had a sheet with all these formulas on how to calculate that. We can do that here so that we're getting it calculated automatically. So enrolled students who received one hour of instruction, we're just going to put in the formula; equals N14, and it's going to grab that number there, N minus N11. So that's our students who came in for services. Some of them come in, they get into a service enrollee. 

Actually, let me do this here. So equals N14 minus N11. So of the 317 students who come in and are served by our schools, 17 don't go into a class. There's no designated program. The rest of them are entering into some type of class. 

So that's our number, and then we can do a formula to get the percentage of the students who come in. So that will be N14 minus N11 and then divide it by N14. So 77.92 of our students are coming in for services and then entering a class at Lowland Adult. And then we can start making goals like what do we want to improve on, or is that a good number? Do we want to see that increase? 

Over here, we've got students with one hour or more who make it to 12 hours instruction. So how do we calculate that? So we've got students with one hour of instruction, and that's going to be F14. Wait, no, no. Students with one hour of instruction who make it to 12 hours of instruction, yes. So these are the students who have come in-- we have 247 who entered classes. 58 of those have gotten 12 hours of instruction. 

So to figure out that percentage, we're just going to do F14 divided by N14 minus N11. And that's going to give us-- of the students who come in, receive services, 23.48 are coming in and taking classes, getting 12 hours of instruction. That's when they're in our system. We're getting all the points from them, things like that. 

So that 12 hours is key. We want to make sure we're keeping students engaged. That means they're going to stick around a little longer even and help us get those student gains, and we can help them navigate their education into their careers. So this will percolate right there. 

Then we want to get our AEP outcomes attainment. So that is just simply sum of CAEP outcomes here all the way to here transitions post-secondary. So we want the sum of G14 to M14. And I'm saying G14 to M14 because that's my Excel columns. But in the TOPS data tables, it would be F through L. 

This school doesn't have any. And then we can do the percentage of that, would be equals Q18, the number we just pulled, divided by F14, and 0% outcome so far. Part of that could be that these numbers are so low, it's the beginning of the year. We're still trying to figure out how things are going with COVID and the pandemic. So that could be part of it. 

So that's how we get all of these over here. Then we've got pre and post-tested that we want to find out as well. Do we want to keep track of how we're doing with our pre and post-testing? So over here, it's a very simple formula. And again, these are all the formulas we got from Jay two years ago. I just plugged them into my system. 

So we're going to pull D4 up here for ESL. These are measurable skills, gains, enrollees pre and post-tested. So D4 divided by C4. So those who have been pre-tested, and then we're dividing those with pre and post by those who have been pre-tested. So 86.7% of our ESL students have been pre and post-tested when they're over here. 

So if I want to get the rest of these, all I have to do is that formula-- if I click this little dot here, pull down, that formula is going to populate all those rows below with the data connected to the programs there. So you'll see this because there are zeros in there. Workforce prep down to no designated program, we don't have any numbers. But the columns with numbers or the rows with numbers we've got all that populating automatically. 

So this is our enrollee pre and post-test. Then we go over here, and we get some enrollee gains. We want to see how many of our students are getting that pre-test to post-test with a gain. So enrollee gains is just B4 divided by C4. So we're getting the gains divided by those who have a pre-test. And again, we're just going to pull that formula down to populate all of these. 

So this is telling us how many gains we've got with the students who have a pre-test. But maybe we want to look at just how many students got to gain who had been pre and post-tested because we know two students haven't gotten a gain because they haven't taken that post-test. So I'm coming over here. And to get that, we do E4 divided by D4. And I'm just taking EFL gains achieved divided by measurable skills, gains, enrollees pre and post-tested and that'll give me 38.46, so a little bit better. 

We're doing a little bit better. It's still early in the year, so hopefully, these numbers will go up as the year progresses. So this is how I created this. Now like I said, I've got the previous month here. I can look at the growth, and I can look at the previous year how we compare to last year. 

And I do that for all of our sites. I would go through and do that for all of our sites as the consortium manager, pulling all that, consolidating all that. But I don't want to go through that again with everybody. So I have the completed tables. Let's see here. Here are all those consolidated. 

So when I pulled all that data, I've got the managers reports. This is the same data the previous month and the previous year. And then I go to LA, data is the same. Just what I did, just the formulas I posted. Growth from previous month, same. Growth from previous year is what I just did, consolidating that data. 

Like I said, I did that for the whole group. So here's Roseville. I consolidated Roseville Adult, looked at their numbers. Again, we don't have outcomes. Partially, this might be a smaller site that the year's just starting. They haven't gotten there yet. 

They're a little bit lower here. So we can start looking at that. Growth from the previous month, they've had 11 more growth from the previous year. Still doing good. Obviously, we didn't have a lot of classes last year, so these numbers over here are much better. And over here, we're doing better than last year too. So that's a good thing. 

Mountain, here's our big site. So I consolidated Mountain's data. This is where they are. They have all these enrollees. They're taking outcomes. They're already tracking outcomes. They've got them here. That same formula is pulling that data for me, and I'm getting those results automatically. Again, they're looking pretty good right now. As the year goes on, we'll see these numbers keep going up. 

And then at the end, I can look at our consortia. So I look at us. I consolidate. So to get this, I just consolidate from each of these tables here and pull them into the consortium table. And I get our numbers. I can look at us as a group. Hopefully, I can say, hey, guys, if you aren't doing well, if you're below here, you're hurting us as a consortium. 

We want to make sure everybody's trying to get above this average for the consortium. Otherwise, you're bringing down the group. So we can focus on these things, these percentages. And we can look as a group, hey, we need to pick up the pace here and who needs to pick up their testing. Maybe somebody's got to go in and look at their student hours see who's ready for pre and post-tests again. 

And then we look at the total of our gains from the next month. And usually, you are not going to see a decrease from one month to the next, but you will see some negatives here in no designated program. So I'm seeing negative 20 here from the previous month. Probably what happened is some of those students came back enrolled in one of the classes. So they dropped from no designated program, maybe jumped up into ABE, ASE, or ESL. Does that make sense? 

And then again, I'm just pulling from last year's data, all of our data. And we can compare how we're doing as a consortium in October. So again, this is in October, so I can do this every month. And in October, this is where we are in terms of our data compared to last year in October. 

Last year, we had a lot of service enrollees, but we didn't have many people entering classes. We were trying to figure out what we're doing, distance learning, all of that. When we're comparing, these formulas aren't going to matter. This is just the comparisons, so this is not meaningful here. 

You could look at last year's data and then compare, make another table of last year's numbers, and get these actual formulas for last year and make comparisons there. But I mainly want to look at our numbers from last year and see how we're doing. Are there questions on this, comments? Hoping you guys would engage a little more. 

SPEAKER 2: I did have one question. 

THATCHER WELDON: Yeah, go ahead. 

SPEAKER 2: This is only for your adult schools or do other members of your consortia also do pre and post-testing? 

THATCHER WELDON: One of our community colleges did pre and post-testing for ESL, but they're not doing that now. So it is just for the adult schools. I'm working with our institutional research department here to try and pull MIS data for our non-credit programs and trying to figure out how I can match that up with this. 

SPEAKER 2: Same here. And another question not regarding the process of Excel, but it was regarding transition services and also trying to match your adult school transitions into college when it's TE data and then it's MIS data. 

THATCHER WELDON: It's challenging, right? 

SPEAKER 2: That's to me more challenging. 

THATCHER WELDON: Yeah, and that's how big of a consortium are you? 

SPEAKER 2: We are Peralta College District, Oakland, northern Alameda, so- 

[INTERPOSING VOICES] 

THATCHER WELDON: That's one of our difficulties that we're working on. And thankfully, our colleges and our district is very supportive of adult ed, and I can get assistance from our institutional research department fairly quickly. And so we have somebody who's working with me on how we're going to be tracking that and making sure it's accurate, that those transitions, we're able to get those numbers somehow. But it is one of the difficulties we're facing. I agree. 

SPEAKER 2: Thank you. 

THATCHER WELDON: And like I said, I can share this consolidated table too. I'll share the formulas too. I printed them out. But if you need to watch this again, that's fine. I'm sharing this as a resource to everybody. Yeah, I agree. It's frustrating that we aren't sharing those. 

But this is a resource that I created for us to look at our growth, to make comparisons to where we were and where we want to be. And so I'm sharing it with everybody so that we can all-- you can use this. Pull your data and consolidate it into this. All you would have to do is just change the tabs here. Maybe add a few more tabs, copy these tables and plug in all the formulas, and you're good to go. 

When I do this every week-- oh, sorry, not every week, every month for our meetings, all I have to do is go back. So all I have to do is I'll take this month's managers table, copy and paste for November, and I'll paste that into the previous month. Then I'll pull our TOPS report for November, and I'll plug that into this month. And then because I've saved all these for two years, I just go back to our November 2020 board meeting, download that student data file, pull the manager's report, managers table, plug it into the previous year for November. 

And then usually, these numbers will be in the same place. You have to double-check at first, especially at the beginning of the year, like I said, some sites might not be collecting data. And then all I do is highlight, and I consolidate again, and it's going to pull from there automatically. And it's going to update, and all these formulas are going to update automatically once you pull those numbers in. 

So you're getting those updates automatically when you're just pulling these numbers. And that's why it's so important to set it up. Any other questions, comments? Let me close that, so I can share that in the chat. 

So there's the one with the consolidated numbers, and the formula is all built into it. Use that, make it your own. I was in the other data webinar with Jay and Harold and-- I forgot the other young lady's name, but they were talking about things. And there was a lot of discussion, and Ryan Whetstone brought up that maybe we could have monthly data meetings throughout the state or maybe quarterly. Who knows? 

But I think the more we all get involved in this, and we start realizing we can collect data and see these things, check our growth, why can't we? We're educators. We can figure this out and have meaningful data and use it. 

So let me just finish off with my PowerPoint here. So why is data important moving forward? Well, we're in our three-year plan. So we need to make sure we have meaningful data. We have lots of resources, like we said, WestEd, the LaunchBoard. But this has been useful for us because I can collect data every month and compare it to the previous month and previous year. So we're not waiting till the end of the year data. 

Data is important. Budget equity, we want to make sure our numbers that we're doing our best capturing the student served, what's happening with those students. People talk about return on investment. Well, data is important. What are our students doing after they come into our programs? And again, these are the difficult data points for us to capture, but like I said, let's get those meetings going like Ryan was talking about. And let's brainstorm as a group because I think we can figure it out. 

We need that narrative. We know all the incredible things we're doing in adult ed. We know the great programs we run, the great students we have, the great teachers and admin, but we have to present that to the public. We have to present that to our legislators. So it's part of our narrative, showing how we've evolved. 

During COVID, we had to change a lot, and we did it. We went from mainly in-person. I was pushing for distance learning a long time with our group because we have so many rural areas. And one at least benefit or good thing that came out of it is we did grow our distance learning programs and our outreach. So three-year planning, we need to have that quality data to make achievable goals. 

How to start? I'm not a data person by background, but I just started messing around with the Excel tables and developed this simple tool that's been meaningful. Start small and build from there. Develop work groups to support each other. Never think you are too small of a program to not have a meaningful data to show us a narrative. 

So even the small programs, if you're really rural and your numbers are low, well, you know what? You're serving people who are coming for services from miles and miles away without good public transportation, and that's part of it too. So we want to make sure we're capturing everything. 

Never too late to learn a new skill. We're all educators. We should all be learning new things. Let's all be data people. And then having that reliable data will help us show all the great things we're doing in adult ed. Any finishing thoughts, comments from you guys? Thank you for participating. I appreciate it. 

TODD: Thanks a lot, Thatcher. Hey, this is Todd from Santa Cruz, Watsonville. Hey, you were talking a little bit about your colleges and their MIS data, how would you merge or how do you try to compare the data you've collected in these spreadsheets with the MIS, or do you work on that end? 

THATCHER WELDON: So we're just starting with that. I've been focusing mainly on this data. Right now, that is what I'm working with the IR department at our district on how do we capture those similar outcomes and make sure we're capturing all the non-credit hours and students. So we're just getting there. And hopefully, by the end of this fiscal year, we'll have that. I'm hoping. 

SPEAKER 3: Thatcher, thank you so much for this. I am definitely not a data person. I do accounting and financial stuff, but this is definitely wonderful. Thank you. This is going to be a great tool for us to begin our three-year planning process. 

THATCHER WELDON: Yeah, thank you. I appreciate it. Like I said, my background is in-- I started off teaching English as a second language with international students. Then I came back from living internationally and came back and got into adult ed, and I love it. I think a lot of us, we found this great fit with these programs. 

And I've never seen students so eager to learn, so excited to learn as we see in our programs. I've never seen greater community than we have in a lot of our adult ed programs. So I think it's really important. It's important to at least try and work on this data because the things we're doing are really meaningful. And so sharing that information with everybody is essential as budgets get tighter and everything. 

Thank you all for participating. I think there's no other comments. 

SPEAKER 1: No. 

THATCHER WELDON: And email me. I'd be excited to see how you use this in the future. 

SPEAKER 1: All right, thank you, Thatcher. Thank you everybody for participating. You got a lot of kudos there in the chat. And I know you just read all that. So thank you again, Thatcher. Thank you, everyone, for joining today's session. I'm going to post-- 

THATCHER WELDON: Can I just-- 

SPEAKER 1: Yeah, go ahead. 

THATCHER WELDON: I'm just sharing the Excel formulas, and then I'll share again the consolidated report. So everything's in there. Go ahead. I'm sorry 

SPEAKER 1: Oh, no problem at all. I'm also going to post the evaluation link in the chat. So as Thatcher is posting those files, you might have to scroll up to see his files and the evaluation link. His PowerPoint is available on the resource section of the vFairs' platform. So just find his session block, and that PowerPoint will be there. 

I know some of you said you wanted to watch the recording again. You'll be able to do that once they're posted on the vFairs' platform. And it will be available through the end of the year. You'll be able to find those recordings in the agenda, and you'll see-- I can't talk. Sorry. You'll also see a button labeled Recording. The remediated recordings will be available on the caadulted.org website in the new year, and you'll be notified when those are available by email and the newsletter. 

And with that, I'm going to go ahead and close out the session. We have mini sessions beginning in about 13 minutes. So I will see you at one of them. Actually, Diana is here. Diana I'll see you in 13 minutes. I'll open the room up now. All right, take care everyone. Bye.