Seventh Wave Scheduling Tool

Senior Design Project, Fall 2007
Steven Shalwitz


Advisor:
Jody Debold
Project Manager
Seventh Wave Laboratories

Abstract:

Seventh Wave Laboratories, a preclinical development and consulting company, has quickly expanded its client base over recent years.  This has required them to more efficiently handle projects throughout several laboratories.  However, at present, Seventh Wave lacks an effective scheduling tool that can handle the constant flow of projects coming in.

 This project serves as an initial proof of principle that may be expanded into an overall scheduling tool.  It focuses on only the histology department, which is run completely internally at Seventh Wave.  To build this, I utilized two important concepts of operations research, dynamic programming and network flow graphs/ shortest paths.  The program was implemented using Excel and Visual Basic (i.e. an Excel macro).  It allows the user to input different project starting dates as well as desired deadlines and the number of animals that will be needed for each project.

The macro outputs a schedule for each hour of every day over the specified range of dates.  The schedule shows the optimal project and task that each lab technician should be attending to.  Further, the program outputs the expected date of completion for each project as well as a continual update of hours remaining within a project.

For Full Text: Senior Project Final Paper.pdf

Summary:

One of the primary methods used to build a scheduling tool is dynamic programming.  Dynamic programming is the process of finding the best solution to each interrelated sub-problem within a problem in order to find the overall solution.  This is known as the principle of optimality[1].  In the case of this scheduling tool, the sub-problems are simple binary assignment problems (i.e. what employee will do what task) that can be easily computed using a basic Excel Solver.  By repeatedly solving this sub-problem over several projects and many time steps, an overall schedule may be obtained.  These assignment problems are combined with the idea of network flow graphs, which are relatively simple in this case and have easily computable shortest paths.  Once the shortest paths are calculated, the program is able to prioritize projects and iterate through their corresponding assignment problem, thus creating an optimal schedule.

Accomplishments:

  • Successfully created a scheduling tool capable of handling up to 12 projects of two different complexities

  • Effectively implemented dynamic programming and network flow graphs/ shortest paths to compute an optimal schedule

  • Created an easily understandable output of daily schedules and project hours remaining (Sample Results)

  • Proved essential proof of principle that may be greatly expanded into a comprehensive dynamic scheduling tool


Sample Gantt Chart

Next Steps:

  • Introduction of worker calendars to account for vacation days, holidays, and business travel

  • Creation of a graphical user interface that may be dynamically updated to account for new projects, and unexpected delays

  • Introduction of statistical means and variances within tasks for better predictive qualities



[1] Dreyfus, Stuart E., and Averill M. Law. The Art and Theory of Dynamic Programming. New York, New York: Academic P, 1977. 2-5.