How to calculate Basic Aid ADA

What is Basic Aid?

Basic aid is the Average Daily Attendance (ADA) that is reported by grade span and by District of Residence. In other words, you look at the student’s home address and the District that they would be otherwise attending if they weren’t attending the client school. You then take the student’s ADA and report it under that District and their grade level.

Most SISs have the ability to automatically split the ADA based on the student’s District of Residence.

For PowerSchool, the most updated version of the CA state reports must be installed and the District of Residence must be populated for all students. You would then run the Attendance Summary by Grade report using the Report by District of Residence feature.

This report will then give you a breakdown of the ADA by District:

If the SIS does not have the ability to split the ADA by District of Residence, then you must manually calculate this on an excel spreadsheet. To do so:

1. Obtain a list of students and their days of attendance for the specific reporting period. This list of students needs to include ALL students who were enrolled at any point from the start of school up until the P-Reporting Cut-Off date.

2. Identify the District of Residence for each individual student

3. On a separate column, identify the ADA per student. To do so, divide the student’s days of attendance by the total number of days in session for that reporting period.

4. Create a table next to your list of students that lists the grade levels across the top and the Districts of Residence down the left column of your table. This table will add the days of attendance for students who meet two criteria for the specific cell selected: 1- the grade level and 2- the District of Residence. For the example shown below, we are using the District Code Number that represents the District of Residence instead of the actual name of the District of Residence. The name will be added on later:

4A. As previously mentioned, in this table you will be adding up the days in attendance for the district of residence and the grade level that align to the specific cell. In the example shown below, the cell that is highlighted, you will be adding the days of attendance for those students in grade level -1 and that reside in the District of Residence 3066530:

In that first cell that is selected, you will enter a =SUMIFS formula that will look something like this:

where G:G = the column with the days of attendance

where C:C = the column with the grade levels

where M1 = the grade level for that cell

where I:I = the column with the District of Residence

where L2 = the District of Residence for that cell

This formula would need to be copied across all cells for the different grade levels and the different district of residence. The only two items in the formula that would change is the grade level for the cell and the District of Residence for that cell. In other words, if you move one cell to the right, the M1 in the formula would change to N1 to search for that specific grade level. Since the District of Residence is the same for that row, L2 would stay the same. Please see example below:

4B. Once you have entered all the formula in all the cells, your table should look like this:

At the bottom of each column, SUM up the days in attendance by each Grade Level.

4C. Then, next to the last column, SUM up the days in attendance by District of Residence.

4D. To ensure that all days of attendance are being accounted for in the cells, we are gong to add a Validation for the SUM of the total days in attendance by all the grade levels versus the SUM of the total days in attendance by all the Districts of Residence. On the same row as the total days of attendance by grade level, add a SUM formula to add up that row.

On the same column as the total days of attendance by district of residence, add a SUM formula to add up that column.

The Validation Formula is going to compare the total number of days in attendance by grade level to the total number of days in attendance by district of attendance. The validation formula should look like this:

Essentially what the formula is checking for is that if the SUM of the total number of days in attendance by grade level is the same as the SUM of the total number of days in attendance by district of attendance, then the data validates OK , if not, the validation will read “Error”.

If you receive an “error” for your validation, it might be that one of the formulas in the cell was not properly edited to search for the correct criteria. You would need to look at each cell individually to ensure that the formulas are calculating correctly. If you receive an “ok” for your validation, you can move on to the next step.

5. Underneath the first table you have created, you will create another table with the same headings for the grade levels and District of Residence:

5A. In the first cell, you are going to divide the total days in attendance from the first table by the total number of days in session for that P-Reporting period. The formula in that first cell should look like this:

Where M2 is the corresponding cell from the first table and 71 is the total number of in-session days for that P-Reporting period.

5B. Copy this formula across and down the entire table ensuring the corresponding cell from the first table is being considered.

5C. To ensure that all the formulas were entered properly in the cells, we are gong to add a Validation for the SUM of the ADA by all the grade levels versus the ADA by all the Districts of Residence. On the same row as the total ADA by grade level, add a SUM formula to add up that row.

On the same column as the total ADA by district of residence, add a SUM formula to add up that column.

We are going to add the same validation formula as before and apply it to this table. The Validation Formula is going to compare the total ADA by grade level to the total ADA by district of residence. The validation formula should look like this:

Essentially what the formula is checking for is that if the SUM of the total ADA by grade level is the same as the SUM of the total ADA by district of residence, then the data validates OK , if not, the validation will read “Error”. If you receive an “error” for your validation, it might be that one of the formulas in the cell was not properly edited to calculate the ADA using the first table. You would need to look at each cell individually to ensure that the formulas are calculating correctly. If you receive an “ok” for your validation, you can move on to the next step.

6. Now that you have your ADA broken down by the District of Residence and by Grade Level, you can know add up the ADA by the Grade Level Spans that we use for P-Reporting. As a reminder, grade levels TK-3 get reported together, grade levels 4-6 get reported together, grade levels 7-8 get reported together, and grade levels 9-12 get reported together. Your table should look something like this:

6A. In this table, all you would have to do is enter a SUM formula that adds the grade level ADA together from the second table. For the first cell, you will add the ADA from the second table for grade levels TK-3 for that District.

It will be the same process for the rest of the cells. Once you finish entering the formulas, a completed table would look like this:

6B. We can now add the name of the District of Residence in front of the District Code column:

You can now use this table to fill out the Basic Aid Report for the client school on the state software.