Developing School Data Dashboards

The value of a data visualization depends upon it showing the right data in the right way at the right time.  For adult charter schools in California, the major source of funding comes from attendance.  If there a not enough students attending, there will not be sufficient revenue to meet expenses.  Further, unlike charter schools for kids, there is no compulsory attendance, so attendance varies much more widely.  Also, unlike traditional schools, it is common for new students to enroll and start attending throughout the school year.

Thus the administrators of a California adult charter school need to have their pulse on attendance and hence revenue at all times.  To support this, an advanced Excel spreadsheet was developed that used PowerQuery to automatically download the actual daily attendance from the school’s student information system (SIS) using SQL queries.  This would show up as the jagged blue line in the graph.

But schools are not paid on actual attendance.  They are paid on the average daily attendance (ADA) for particular date ranges.  Thus, the spreadsheet would calculate this, and produce the dashed blue line in the graph.  The revenue generated up to that point in time could then be directly calculated, which is shown as the green line (whose dollar value can be seen on the right axis).   And to determine whether this revenue should be sufficient to meet the schools expenses, the budget to date was calculated, and shown as the red line.   So when the green line is below the red line, the school had not generated enough money yet to make its budget obligations (a bad thing), and when it was over the red line, it had generated more revenue than was budgeted (a good thing).

For dates in the future from when the graph was generated, predicted values would be generated based upon various models of how attendance was occurring for different types of students, and the average daily attendance would be calculated from this, and then the predicted revenue calculated from that.

By doing the data analysis in Excel, additional analysis and valuable visualizations were able to be created rapidly, and different predictive models could be easily tested.  Further, when the school decided to change SIS vendors, the spreadsheet just needed to have its SQL queries updated in PowerQuery, but was otherwise able to have the same output as before.

I have also done other data dashboard work for colleges, as I document here.


An advanced Excel spreadsheet was developed to automatically query the daily attendance from a school's student information system (SIS). This allowed administrators to keep their pulse on how many students were attending, and how much revenue generated compared to the school's budget.


Highlands Community Charter School


Ed Data Work
Knowledge Driven Education