ALLISON DAVEY (00:01)
All right. It looks like we have quite a big group with us this afternoon. I am Allison Davey with the Center for IDEA Fiscal Reporting. And we are pleased to have you with us today for our demonstration of the new and improved LEA Maintenance of Effort Calculator version 2.0. If you have questions, you can put those in chat. We’re going to keep a close eye on that, so we’ll get all those answered for you. And if you’re a local education agency and questions come up today about regulations or requirements, you can reach out directly to your state education agency to discuss those.
State education agencies, you know, if questions come up, you can always reach out to your CIFR TA liaison for additional support. All right, so here is our agenda for today. As you know, we’re going to provide a demonstration of the Calculator. We’re also going to take a look at the instructional resources that have been created to support you.
And for the end of the session, we will share some tips and tricks that we learned through the testing process that we think are also going to be helpful to you as you get started. We are pleased to have our CIFR Technical Assistance Liaisons Laura Johnson and Elena Lincoln joining us to share their expertise today. Laura and the CIFR team have worked very hard to get this new and improved version of the Calculator ready for you. And we’re really excited to be able to share that with you today. I will now turn it over to Laura.
LAURA JOHNSON (01:34)
Thanks, everybody, and welcome. So what are some of the new features in our new and improved LEA MOE Calculator 2.0? The most important new feature is that the years are now flexible, and they cover a 10-year period, and that period is selected by the SEA or the LEA. It used to be in our prior Calculator that each file covered a fixed five-year period. So the later in those five years you started using the file, the more you had to do to really get it going. Now, you can start it at any time you want. We have a new Summary tab for each fiscal year that displays both eligibility and compliance at the same time. It used to be you had to toggle between the two, and now you can see both together for one fiscal year.
And finally, there’s less burden for entry of historical data. Especially if your LEA met MOE by all four methods in the prior year, there’s very little historical data you will have to enter. And I will show you that as we go along. We have some supplements for our LEA MOE Calculator 2.0. First, we have some comprehensive instructions. As with most of our calculator tools, the instructions are pretty lengthy at about 16 pages, but they’re well structured and well worth the read before you start using it. For those of you who don’t have a memory that can memorize all 16 pages of our comprehensive instructions, we also have a one-page Instructions At a Glance, and I will be showing you that as well.
And finally, we have a sample LEA MOE Calculator 2.0 with data for a hypothetical LEA. And this is what I’ll be showing you during the demo today, so you can see how it works with data in it. And again, I want to emphasize this is a fake data, a fake LEA with data that we’ve made up. We’ve made it up to be realistic, but it is not at all to be interpreted as an actual LEA. And all are available at the link below. I believe Anthony has also put that link in the chat.
So if you want to go ahead and download either the blank file or the sample file, so if you have two screens, you might want to go along with me while I’m doing the demo. And don’t be alarmed by the word archive, that’s just a structure of our new website. It does not mean at all that the Calculator is archived; it is in fact a current active tool. So I’m going to walk you through our sample Calculator with the hypothetical LEA. So the first tab is just our Title Page that has the usual disclaimers and authorship information. So the tab you really need to start with is tab 2, Getting Started. In the Getting Started tab, you’re going to enter some basic information for the LEA that will allow the Calculator to do the work that it needs to do. So first, you will enter the LEA name and ID.
ANTHONY MAKUNA (04:46)
We have a question in the chat.
From Bridget, When do we begin this Calculator?
LAURA JOHNSON (04:54)
You can begin it at any time you want. If you’re in an LEA, you may need to talk to your state about if they want you to make the transition to the new file. Does that answer the question, Bridget?
She said, Okay, thank you.
Okay. So first, you’ll enter your LEA name and ID and then the start and the end of the state fiscal year. And I believe we’ve entered July 1 and June 30, just as a default, because that’s the standard state fiscal year for a lot of states, not all of them. If it’s different, you’ll want to change that. And then you need to select the year you want to use as Year 1. And it’s important to note that Year 1 is the first year that you want to measure the compliance standard. Remember that the compliance standard is usually… for the same fiscal year, you do the eligibility standard, which is the budget, about a year or a year and a half ahead of when you do the compliance standard.
So if you’re starting with an eligibility test, that needs to be Year 2 and whatever the prior year would be Year 1, so that when you come back to do compliance a year and a half later, you have somewhere to put it. So we don’t allow eligibility for Year 1, you have to start, if you’re starting with eligibility, set that as Year 2. I hope that makes sense. In this case, we’ve chosen 2016 as a starting point.
Then down below, you need to enter, for each of the four methods, you know, total local meth… total local funds, total state and local, local funds per capita, and state and local funds per capita, you need to enter the last year the LEA met the compliance standard, along with the amount that they spent in that year to meet MOE and the child count. And you’ll see for three of the four methods they met last year, but one of them they did not meet last year. So they have intervening years, and I’ll explain that in a second. And then finally, down below, you enter the information for the prior year — prior to Year 1.
And this information may populate if you have completed it for the compliance standard above. For the eligibility standard, you will need to enter that. And this is just so the Calculator can do calculations for exception (b), and other things without giving errors. So are there any questions at this point?
ANTHONY MAKUNA (07:31)
Yeah, one in the chat from Kristin: Is there a common space that we should go to for child counts? And this is the amount that is one that is on the November child count?
LAURA JOHNSON (07:44)
Yeah, typically, you would use the most recent child count data that you have from your October or November, whenever, your fall child count. Does that answer that question?
ANTHONY MAKUNA (07:57)
And the first part was: Is there a common space that we should go to for a child count?
LAURA JOHNSON (08:05)
Um, you may need to contact your data person in your state, I mean or in your district, to get that number.
She says thank you.
Okay. So because we have this message here that says there are intervening years, we need to go to the next tab, the intervening years tab, tab 3a. And as you know, or as you should know, with MOE, if an LEA fails MOE they are allowed to count exceptions from the year of that failure and any intervening years, cumulatively, so that they can meet MOE in the future by that method.
So, if we remember they failed the local funds per capita last year, but they last met it in 2014, so they are allowed to enter the exceptions and adjustment they had for 2015. And here, you just want to enter a total, and it will take that into account when doing the calculations.
Then the next tab, 3b, is the high cost fund and this is if your state has a high cost fund operated under §300.704(c). This is specifically for exception (e). This high cost fund is funded with IDEA funds and not state funds. Most states don’t have one under §300. 704(c), so we’ve set the default to “No,” but if it does apply to your state in any year, you just need to change that “No” to a “Yes.” So next we’re going to go to tab 4. And this is a carryover from the original calculator. And I chose to name tabs 3a and 3b as a and b so that we can maintain tab 4 as tab 4, the Multi-Year MOE Summary. A lot of my users of the prior Calculator just refer to this as tab 4 and I didn’t want to break that. And this is going to summarize all of the data entered in the file. And since this is our sample, it has all of the years entered, so it displays all of the years here. And it will show for all four methods, whether the LEA met, met with exceptions or adjustments, or did not meet all four methods for each year. And this one, we’re seeing a lot of green — green means met, red or pink means not met — except for one year here, Year 7 or 2022, they did not meet all four methods.
The default here is compliance, but if you do want to look at your eligibility data, you can just change this in column C, and it will switch over. Here, you’ll notice we have links, this will take you directly to the Summary tab for each year, so if I click here, we’re at the Year 2 Summary tab, so we can look in depth at what’s going on in Year 2. And then if we want to go back to the Multi-Year MOE summary, we can click this link at the bottom of the Summary tab. Any questions about the Multi-Year MOE summary? Great. So, I will admit that when I create Excel files, I tend to include a lot of tabs; this one has 44 of them. But, for each year, there are only three tabs that you need to worry about at a time. So if you think about it as a smaller detail like that, I think that’ll be helpful.
So, we’re going to start with Year 2, because Year 1 we’re not doing eligibility. So, we’re just going to go straight to Year 2. The first tab is the red tab or the Amounts tab. And this is pretty much identical to what we had in the prior versions of the Calculator. The only thing that really has changed here is the color of the shading. It’s now blue, and I hope it looks blue to everybody. So, here you just enter the budget information on the left. And then when it’s time to do compliance, you scroll to the right and enter the final expenditure information.
You can enter your object descriptions; these codes are editable so that you can make them whatever headers work for your state. You enter amounts for local, state, and then it will calculate state and local. If you cannot break down local and state, enter everything in the state column and leave the local column blank.
You must also enter for the eligibility standard your projected child count. And for the compliance standard, your final child count. And that will allow it to calculate down at the bottom, it will calculate your total and your per capita amounts. Any questions about the Amounts tab?
One thing I want to say about this tab, this is the tab where people most often use “cut” as they’re moving things around. Please don’t use “cut” in this file. If you use “cut,” it will break the formulas and then you’ll have to send it to me and I’ll have to fix it. And I don’t mind fixing it, but it does take time away from your work and from mine. So if you’re moving things around, just be sure to use “copy” and preferably “paste values.” If you “paste,” you may run the risk of pasting in a formula or a format that maybe you don’t want here.
So, if you “paste values,” which is available up at the top — I need to copy something — “paste values” is this little button here that has the clipboard with numbers at the bottom and it says “values.” All right, I’m going on to the exceptions and adjustment tabs. Unfortunately, Excel does not allow us to have the full words spelled out because of the limit on the length of tab names, so they are unfortunately abbreviated to “Exc & Adj.” But that’s the best we can do.
So here we have some reminders at the top that you need to scroll to the right when you’re entering your data for compliance or expenditures. And we have identical tables on both sides. We also have reminders that this worksheet may contain PII, because you may be entering staff salary and benefits and you may be entering student IDs, so you need to follow appropriate procedures for protecting that PII. And it could be that you might change some of that information so that you’re not using names but you’re using pseudonyms or you’re not using student IDs, but another way to identify the student, or you just need to protect the file. So, we start with exception (a) where you would enter — let me find a tab in the sample where we have some exception (a) data. Here we go, in Year 6. So, you would enter the position title.
And here, we do have the employee name and the reason for leaving — and remember, it must be voluntary departure or departure for just cause — the salary amounts and the benefits. And if you don’t have them broken up, just enter in one column, and it’ll be fine. And then it will sum up the total of all the salary and benefits for your departing personnel. Then you need to enter the same information for your replacement personnel. So here, we’ve got three retire … two retirements and a resignation and two new employees. And we see the difference comes to $120,700. So, that would be the allowable exception, if the SEA approves this information, for exception (a). Exception (b) is calculated automatically; exception (b) is the decrease in the enrollment of children with disabilities. So, this pulls in all the information automatically, so you may see exceptions populated that you haven’t entered and that’s because of exception (b). It uses a method that is approved by OSEP to get to the amounts. And I will point out exception (b) is the only one where the local amount, and the state and local amount are different, just because it’s based on calculations.
For exception (c), you would enter the student identifier. Then you would use the drop-down for the reason why they are … why you no longer need to provide the high cost program, and then the expenditures, and then we’ll add it up for all the students. Exception (d) is the termination of costly expenditures for long-term purchases. So, we need to enter a description of what the long-term purchase is, and I know there’s an example in here of that. Yes?
ANTHONY MAKUNA (17:10)
We have a question from Jennifer: If our retired staff does not have a replacement yet for that position, what is the best practice for those instances?
LAURA JOHNSON (17:21)
I believe you can still enter that and take the exception. And you would be able to take their full amount. Sorry while I’m skipping around, I’m trying to find a year that has an exception (d). So, here, exception (d) was for intensive classroom renovations. And they get … the cost in the final year is the amount of the exception.
So here it’s $14,680. It’s not the cumulative cost; only the cost in the final year. Exception (e) is for the high cost fund, any students assumed by the high cost fund. And if we remember back on tab 3b, we said that we do not have a high cost fund in our state. So this exception is not valid. And if we try to enter some money, we’re gonna get an error message and it’s not going to count it. So, if you do have a high cost fund, you will need to go back to tab 3b and indicate that.
Something that is new in this version of the Calculator is a total of all the exceptions. The old Calculator would bring each exception in separately; here, we’ve just done a total. And finally, a place for you to enter the … any adjustment taken by the LEA. And this will be, you know, important with the increase in allocations coming up. And, because IDC has already created a pretty good tool for the MOE Reduction Eligibility Decision Tree and Worksheet, we have a link there that will take you to that tool that we recommend LEAs fill out and then come back and enter the final number here.
So, let’s look at a Summary. So here we have a summary for … this is for fiscal year 2018. And this statement here helps you remember, what does fiscal year 2018 mean? So, for each of the four methods for eligibility at the top and compliance at the bottom, it’s going to tell you what it’s comparing it to, what is the comparison year amount, what were the final expenditures this year, and did the LEA meet, or not meet, or meet with exceptions and adjustments? And then for both, it’s going to calculate your failure amount. For eligibility that might help with planning. For compliance, this information is needed in case they fail all four years [methods].
And I’m going to show you that I believe they failed in Year 7. So, you see in Year 7, they failed all four years [methods]. And so, it calculates what are the failure amounts for each method. Then down here, you will need to complete this table with your 611 subgrant, 619 subgrant, and the total amount, and then the Calculator will determine what is your repayment amount. Because, if you remember, the repayment amount is the lowest of the four failures and the total allocation, the total Part B allocation.
So, in this case, it’s going to be the $2,297.95, with which the LEA failed the total local method. Then down below here, just for reference, we have a table that shows all of the exceptions that were taken this year. And you see that they projected to have a decrease in child count, but it didn’t work out that way, which may be part of why they failed. But they also had some resignations and retirements that they didn’t anticipate that came up for compliance. And the adjustment was pretty much what they had anticipated, although it changed slightly. And maybe that’s because their allocation changed somewhat between the time they did the budgeting and the final allocation.
And then again, we can click here and go to the Multi-Year MOE summary, and see how everything fits together. So, any questions about anything I’ve shown you so far? I know it’s a lot, but when you get into it, I think you’ll find it’s not as complex as it may look.
So now I’m going to scroll way to the end to show you some of what we’ve got at the end of the file. We have four yellow tabs that are the calculation tabs that calculate for each of the four methods. You don’t need to do anything with these tabs, you don’t even need to look at them, but I’m going to tell you what they are just in case you’re curious. And this is where we really calculate, Does the LEA meet MOE for each of the four methods? In the prior Calculator, we did the calculations using VLOOKUPs and INDEX MATCH and some really, really complex formulas. But I figured out a way to do it using subtraction, which keeps the formulas a lot simpler, and should keep the Calculator from bogging down or slowing down too much.
Sometimes I found with the prior version, when you were in Year 5, and you had all of your data entered, it could take a few minutes or a few seconds for it to open; it just seemed to take a little longer. So, this method approved by OSEP will get you the correct MOE result. And all of this is fed into the Summary tabs; you really don’t need to look at these, but I’m just telling you, they’re here, in case you want to do a more in-depth look, or you really like studying Excel formulas. And then at the very end, we have two blank tabs. One is an SEA or LEA worksheet. And this could be used, for example, on your exceptions and adjustment tabs, you see you only have room for five departing and five replacement employees.
But you could have a lot more. So you might want to go to your SEA or LEA worksheet and kind of do a sum. So, let’s say you have five special ed teachers leaving, you could enter that data here and do a total and then put that on the exceptions and adjustment tab. The final tab is added at a state request during the pilot process. And this is for SEA guidance. And this is a place where your SEA can enter specific information or guidance for the districts as they’re working through the file. For example, do we have a high cost fund? Can we use exception (e)? Can you separate the meth, the… what is the method for separating state and local funds? Is there a particular way of doing it in your state? Things like that. They could also put deadlines and timelines in here. And that is the Calculator. Are there any questions before I show you the beautiful instructions? So, our instructions: we used to have the instructions as a tab in the Calculator, but we have taken that out, because it really wasn’t that useful because you couldn’t print it out. You couldn’t really find what you needed. So, we just have a PDF now that you can either look at online, or you can print out and have handy for you. It is 16 pages long, but it’s really well structured so that it should be easy to find what you want. We have a table of contents where everything is clickable.
The introduction is just background information. Then we have a handy table that describes each of the tabs and what they do, so you know which tab you need to use for what. I will point out in the Sample, we have all of the tabs visible, but in the blank ones, Year 6 and on are hidden.
So, we have instructions here about how to hide/unhide a tab. And they may work differently depending on if you have a PC or a Mac. We have our warning about not using “cut,” and about using “paste values” when pasting data. This warning, if you’ve used prior tools, you may notice it’s gotten bigger and a little more alarming over time, because again, this is one of the most common things I find with people who are using some of our Excel tools. And it’s something I really wish Excel would address, that Ctrl+X would not break our formulas, but that is the way it is.
The next thing I want to show you is we have a step-by-step chart for how to fill out the Calculator. And this is a great tool if you’re a first-time user just to walk through and to know what you need to fill out and in what order. And it also tells you what page in the instructions to go to, to read information about filling out that step. So it’ll tell you first read and review the instructions document; print out the Instructions At a Glance, I’m going to show you that in a minute; check your SEA guidance worksheet; and then start with Getting Started. You have to determine what is Year 1, then you go to Getting Started.
Then you go to your Amounts tab, etc. It walks you through all the steps for entering data for a single standard. And then we have detailed instructions for each tab for each row for pretty much each cell: what do you need to enter where, and any specific instructions or information about where you might find the data, etc. And yes, it is 16 pages long. So we also have our one page Instructions At a Glance document. And this is available both in color and in black and white, for those of you who don’t have access to a black and white printer, we wanted to make sure you could print out a high-quality copy.
And I recommend having this printed out and next to you as you’re working through the Calculator. After you read the instructions, as you’re working on the Calculator, you may come to something and say, you know, why is it not pulling in my compliance standard data? Well, it might be because you need to scroll to the right to enter your compliance standard; you may have entered it for the eligibility standard instead. Or, here’s our handy reminder about don’t use Ctrl+X.
So these are pulled … these tips are pulled from the TA I’ve done over the years with states using the prior version of the Calculator, as well as from suggestions from our testers internally and state testers. So, great, I’m going to turn it over to Elena. Is there something you want me to show, Elena?
ELENA LINCOLN (28:29)
No, I don’t have anything to show, Laura, if you want to go back to PowerPoint, or one of the PowerPoint slides it’d be fine.
And great presentation, Laura. It is a lot to take in, I think, and so what I’m going to try to do and actually I think a lot of this will be some things you’ve been hearing Laura say that you know, in teaching, you repeat it so that helps us remember it. So, I’ve got some, some ideas, some thoughts I wanted to share about, like my experience. I really was a true novice user in developing the pretend or the Sample Calculator. I think that in some ways, creating pretend data might even be more challenging than having actual data to enter. But I think the experience was just something I thought I’d try to reflect on and share some strategies or tips for each of you. So, I’m going to walk through about six strategies or tips, and then you guys can ask me questions or things like that. Laura can assist me in responding to those as well. So first, I would say, Oh, yes, sorry. Go ahead.
ANTHONY MAKUNA (29:33)
We have a question in the chat from Bridget.
Will it be appropriate for this recording to be part of the SEA guidance for LEAs to learn about 2.0?
I would suspect so, but I’ll let Laura respond to that for sure.
LAURA JOHNSON (29:52)
Um, yes, we are recording it and we will make it available. It’s, you know, going to take some time to get it, to get it just right … um, but we will make it available, so SEAs could share it with their LEAs.
ELENA LINCOLN (30:07)
Great, thanks, Laura. Okay, so what I wanted to start with is lean on the instructions and Laura went through the instructions or the At a Glance, I think I agree with that, keeping that out at your side, but even the full complete instructions. Think of it almost like a checklist, refer back and forth to as you enter information, you refer back to the instructions. And a suggestion might be even to kind of check off things as you as you go along, so really utilizing those instructions. A second tip is to take it one year at a time.
As you saw from the sample that Laura went through, we did it in a way that would … it would cover, fill the calculator, we filled the calculator with years. That won’t necessarily be the case for you, depending on what you start with. But take it one year at a time. With many more years of data available and the flexibility of selecting your year, those extra tabs can feel overwhelming. So, just take it one year at a time. And then take advantage of the hidden tabs feature that Laura set up. Until you actually need those years, I’d be comfortable just letting those tabs remain hidden so that you can navigate and see what you’re working with at the bottom of the screen and not having to scroll to see all your tabs.
And third, speaking of scrolling, the scroll to the right, I did hear Laura mention it, it’s one of the things that I think I actually ended up forgetting to do several times as I created the sample. So, remember to scroll to the right entering exceptions, and actually exceptions, but also even when you’re entering the budget data.
So entering that on the eligibility and the compliance side, remembering that you need to scroll and make sure when you’re entering eligibility versus compliance. Thank you, Laura, this is awesome, you’re following along with me. So scrolling to the right, I actually sometimes minimized my screen, like made it small so I could kind of see both sides a little better. But for data entry and really looking at that it depends on the screen size, possibly, but just remembering to scroll was a pretty big factor for me.
And then the fourth tip, Laura has mentioned this also, copy but don’t cut. But copying, as you see here, where you have budget object codes information already entered for one year, especially when you go from eligibility to compliance side, you could probably copy and paste at least the object and function codes as basic information to the next to the right, and then maybe again into the next year, then just make adjustments as needed, and entering in the updated actual budget information. So, using that copy cell values only and pasting into the next year is a good tip.
The fifth tip I have is navigating to the Summary table, I think I didn’t use the previous Calculator, you know, as a state or an LEA, just as a TA provider. But I when I went to enter the sample, I used that tab, the link that lets you go from the Multi-Year Summary. And then when I was in the Multi-Year Summary, I could click on that year to go back to the year I was working in. And I really found that very helpful.
So, I think taking advantage of that feature is pretty important to navigating from a year to seeing the big picture in the summary table, then getting back into your particular year for data. And then my final tip is kind of, “Don’t worry, just enter.”
So, Laura has assured me that the spreadsheet is password protected, it’s locked, it is difficult to break it. The only thing that we know, you saw the warning signs in the instructions, that causes issues is when you’re cutting. So, go ahead and enter that data and get started, don’t stress over maybe making an error or causing a problem in the spreadsheet itself. She’s got a lot of things password protected and locked, so that those of us that aren’t Excel experts can still navigate fairly easily, so don’t stress over that.
I would say the … the … the navigation and overall just trying to get started using the instructions so that you can just … go ahead and get started. Don’t look … looking at the whole Calculator looking things over. Just don’t worry, start entering.
And I hope these tips are helpful. But most importantly, just know Laura, your TA liaisons for your state, or, if you’re an LEA, contacting your state for support with this tool. I would say don’t stay stuck for too long. Let yourself use the instructions and reach out for help if you do get stuck or have an issue and reach out to CIFR if you need support. Thanks, Laura.
LAURA JOHNSON (34:44)
Thanks, Elena, for those great tips. You know, Elena really worked hard on creating that sample. She wanted to get it right. And it was also a great opportunity for her to give some insight into how the Calculator worked. So, thank you for sharing that, Elena. So just a reminder, some technical assistance resources for any SEA staff, you know, you can contact your state TA liaison, you can contact CIFR at the email address there. And if you have a real, you know, problem with the Calculator, they will get you in touch with me and I will work to resolve it. For LEAs that are attending the call, you should reach out to your SEA, and they may be able to help you or they may reach out to us.