Sunshine on Schools Project Overview

Introduction
In an effort to alleviate any confusion, the North Dakota Policy Council has decided to include this overview of the Sunshine on Schools project. The purpose of this overview is to provide you with details about the source(s) of the data, provide information on how calculations such as student teacher ratios were performed, and provide an explanation of problems that we encountered and how they were dealt with. As we mentioned on the home page, this is a work in progress, and as such the project overview will be minimal at first.

The Data Source(s)
At this point, the only data source for the material on this site is the North Dakota Department of Public Instruction (DPI). The financial facts that comprise all data not specifically concerning the Administrators, Teachers, or district debt is derived from the MS Excel Spreadsheet "9408facts.xls" that can be found on the DPI web site. Data on district debt was obtained from DPI using a formal request. For Administrator and Teacher data prior to 2005, the data was obtained from another MS Excel Spreadsheet, "06pos.xls", that is also available on the DPI web site. We requested more in depth salary data, that has been used in all Administrator and Teacher from 2005-2007. At some point in the future, this will also be used to display individual salary data.

Calculations
STUDENT/TEACHER RATIOS: The student/teacher ratios are calculated from the total enrollment numbers in the finance facts spread sheet, and either by the sum of the percentage of teaching assignment for both teachers and administrators, or by the total number of teachers based on whether this data was reported for the district. The formula used is Total Enrollment/Number of Teachers.

STATE AVERAGE TOTAL COST PER PUPIL/STUDENT: This is calculated using the sum of district expenditures divided by the sum of total enrollment from the finance facts spreadsheet.

Known Issues

Administration and Teacher Salary Data

• Administration and Teacher Salary data does not match the totals in the expenditures section. This was discussed with DPI, and they’re explanation is that this variance occurs because the salary data does not include part time and hourly employment data. For example, for the year 2005 if you were to add up all the teacher salary data for a district, and then compare this to the Teaching Staff Expenditures for the same district in 2005, the Teaching Staff Expenditures will always have a greater value. This is a result of the inclusion of the part and hourly employment data used in the calculation of the total Teaching Staff Expenditures that was not included in the salary data we received from DPI.
  
  
• In depth Administration and Teacher salary data only available from DPI for the 2004-2005, 2005-2006, 2006-2007 school years.
  
  
• Data not available for secondary and tertiary assignments for school years before 2004-2005.
  
  
• Total Days at base salary is not included (has a zero value) for some districts.
  
  
• Teacher Salary Data consists of teachers and other licensed staff such as Counselors, Psychologists, and Coordinators. For the in-depth historical reports, Sunshine on schools will only consider those positions where teaching is 100% of the individuals duties in regards to reporting average teacher salaries. Those positions that have multiple duties or duties that do not require teaching will be included in a separate section under other faculty positions where averages are displayed by the position in the Other Licensed Staff Compensation chart. For the Single District and District Comparison charts, the other licensed staff are included in the averages.
  
  

School Year vs Fiscal Year

Since data from DPI is not consistent in terms of whether or not School Year or Fiscal Year are used, we are using the fiscal year to reference the data in the reports. For example the Finance Facts xls file “9408facts.xls” uses the fiscal year while the “06pos.xls” that contains the salaries for Administrators and Teachers by position uses the school year. In terms of how to utilize this data fiscal year 2004 is school year 2003-2004, and fiscal year 2005 is school year 2004-2005.

School District Names

Since data from DPI is not consistent in terms of the names given to a district over time, I have normalized the data so that each district has one and only one name. In all cases, I have tried to use the most recent complete name given. For example, "N Sargent" is normalized to "NORTH SARGENT".

Changes and Additions

May 1, 2008 - 00:54:00 - Error in Average Cost Per Pupil Discovered and Fixed

In the State Rankings: Total Cost Per Pupil chart, there was an error in the stated state average cost per pupil number. The stated average was given as $ 12489.36, and was mistakenly taken from an average of the district total cost per pupil data for 2007. This has been corrected to the STATE AVERAGE TOTAL COST PER PUPIL/STUDENT as stated in the CALCULATIONS SECTION above. The correct value for this is $ 9077. 22.

May 13, 2008 - 04:37:00 - Added Comments to the site

Added the viewer comments module to the site, so viewers can provide feedback.

May 22, 2008 - 04: 42:00 - Added features to the Single District Reports

Added Historical Enrollment and AYP(Proficiency Ratings) Charts to the Single District Reports.

June 18, 2008 - 05:18:00 - Added Tax Levy Reports

Added Tax Levy Reports that display Historical Taxable Valuations, Local Revenue, and Mill Levy rates from 1994-2007 in comparison to state averages.

June 22, 2008 - 23:27:00 - Added Tax Levy State Ranking Reports

Added Tax Levy (Mill Rates) to State Ranking Reports, and added links from the State Ranking Reports to each individual district's in-depth report.

July 29, 2008 - 03:27:00 - Added Administrator, Teacher, and Professional Support Staff Salary Reports

Added reports for district Administrators, Teachers and Professional Support Staff. Also added top 100 reports for salaries in the state.

March 25, 2009 -21:04:00 - Added data for 2007-2008 school year

The data for the 2007-2008 school year, has been added to the database. All data for this school year except the district debt data has been added to the database. Note: For previous years the state student teacher ratio has been taken from the district demographics report published by DPI. Since this is not available until the end of the year, the raw data was used. This is calculated from the total of enrollment divided by the total of teaching percentages. For example, an employee that spends time between being a teacher and a counselor with 60% of their time spent as a teacher would add .6 to the total of teaching percentages.

May 6, 2009 -21:04:00 - Added Historical Salary Data and Yearly Salary Drill-Down Reports

Since there is no means of tracking faculty except by their names, the following conversions were made:

Full name conversion procedures and normalization:

1. Create Full Name field and run scripts to populate.
2. Run script to ensure all data is upper case.
3. Normalize names using last, first, mi, year and district - normalize to 2008 name of record or highest year if 2008 not applicable.

- - IF YEARS DO NOT OVERLAP, AND FIRST AND LAST NAMES ARE THE SAME CONSIDER AS A SINGLE INDIVIDUAL. FOR EXAMPLE, GIVEN THE FOLLOWING - NORMALIZE TO JOHN D. BARRY, IF NO OTHER JOHN JON OR JHON BARRY:

May 28, 2009 - 23:24:00 - Added the district debt data For 2007-2008 School Year

Added the district debt data to the site, and updated relevant reports.

February 21, 2010 -00:04:00 - Added 2009 Salary Data - Expanded Comparison, State Ranking, and Salary Reports to allow greater flexibility. - Added Chart Export, Data Export and Share Link Functionality.

Added Salary data for 2009, and made major changes to reports and application functionality.

April 15, 2010 -00:04:00 - Added 2009 School Finance Data, AYP data and District Debt data.

Site updated with the new finance, AYP, and Debt data for 2009. Note that the for AYP data a score of 95 is >= 95 and a score of 5 is <= 5.

4. If you notice information that is incorrect, please contact us at This email address is being protected from spam bots, you need Javascript enabled to view it