Goals:

• Create a solution that

  • helps automate this task

  • is simple for a non-computer savvy employee to use it

  • predetermines who is free at the needed time period

  • allows updates to the bell schedule in the near future

• Centralize staff schedules in an easy-to-access space and in an easy-to-read format

• Make it easy to print for distribution

• Make it in Google Sheets because the school uses Google Classroom and cannot afford a different system

• Restrict the ability to make big changes to the spreadsheet due to technical skill limitations of the staff

Desired Outcome: Create a dashboard and a minimally labor-intensive process to clean the data post extraction. 

The Problem

There is one staff member at A Perfectly Fine High School that is in charge of assigning substitutes for classes. The school's substitutes are generally staffed teachers who are not teaching during a certain class period.

Currently, they have to do it manually by hand and look through the staff's class schedules and determine who is free at a given class period. This means that multiple teachers will cover for the teacher that is out in a single day. This takes the employee 45 minutes - one hour to complete due to looking up multiple schedules, cross-checking schedules, and printing and writing out letters to deliver to the teachers' mailboxes.

This task is time-consuming and tedious and worsens when more staff are out.

The Dataflow

During the data profiling stage, I determined the main sources of information: a Staff Schedule, a Bell Schedule, and a Day Schedule. These are the three main tables that would create the composite schedule. These main tables also needed to be able to be updated as staff changes occur and if the bell or the A/B day schedule changes. I would say it's not likely that the bell schedule would change often, but it did change during this project.

From these three tables, I created different composite tables for different purposes:

• Show each teacher's schedule for all periods

• Show all teachers with a free period for all periods

• Create a dimension table with all possible bell and day schedules

These additional tables help provide the data that is shown in the SubSheet tab and updates accordingly with the inputs.

The Solution

A working dashboard designed as a schedule printout to send to fellow teachers for their sub assignments. The dashboard determines which teachers are available to substitute for a given class period depending on the following inputs:

1. Which teacher needs to be covered?

2. Is it an A Day or a B Day?

3. What's the bell schedule that day?

Additional Features

• Brief description of each worksheet

• Only 3 worksheets to update manually for changes to schedules: Staff Schedule, Bell Schedule, Day Code Schedule

• Macro Code to refresh/reset sheet's data validation inputs for SubSheet tab (Shortcut: CTRL+SHIFT+ALT+1)

• Dynamic data validation based on Bell Schedule, Day Code Schedule, and Teacher that requires coverage

• Sheet protection to avoid unnecessary edits in all other sheets

Outcome Achieved

Employee has determined that this dashboard increased efficiency by 30-45 minutes and successfully avoids inaccuracies in scheduling.