Manufacturing Scheduler Platform

The Challenge

We were tasked with creating a web-based platform to track approximately 10,000 interdependent production dates and deadlines for a major publisher. The client had previously been using an Excel spreadsheet (aptly dubbed "The Monster Spreadsheet") with numerous users constantly inputting date adjustments and relying on complex cell formulas to manage the entire production schedule.

The project needed to:

• Replace the unwieldy Excel spreadsheet with a flexible web-based solution
• Preserve the complex interdependencies between production dates
• Support multiple users managing different parts of the schedule
• Function flawlessly in Internet Explorer 6 (a significant challenge at the time)

Additionally, two critical conditions needed to be met:

1. Develop a platform capable of capturing the flexibility of spreadsheet formulas
2. Automatically import all 10,000+ formulas from the existing Excel file

Our Solution

The web application we developed provided comprehensive production scheduling capabilities with several key features:

User-Friendly Interface

• Custom filtering based on user context and text filters
• Personalized views for different user roles
• Multiple viewing options (grid, daily roll, printable calendar)
• Color and glyph-based event grouping for quick visual scanning
• On-hover popups displaying all products referenced by specific events
• Single-click Excel export of filtered dates

Powerful Backend Architecture

• Formula-based calendar events using our custom Scheduler Formula Syntax Parser
• Automatic weekend and holiday avoidance based on regional work policies
• Event grouping by affected departments with role-based permissions
• Interactive date modification portal showing cascade effects of changes
• Complete system-wide logging of all modifications for audit purposes
• A sandbox environment for testing schedule changes
• Year-to-year data replication for planning subsequent production cycles

Data Migration

The most challenging aspect was importing 10,000 Excel sheet cells while preserving their relationships. We developed a custom Excel parser and translator, then conducted iterative scanning for human errors with manual correction. After multiple attempts, we successfully transferred the entire dataset with all relationships intact, automatically translating the Excel formulas to our custom syntax.

Security and Access Control

The platform featured strict, database-level relationships between data and user roles, ensuring departments could only access information relevant to their responsibilities. A comprehensive user manager allowed complete user control and rights assignment.

Results

The resulting platform successfully replaced the problematic spreadsheet system with a robust web application that:

• Maintained all the complex interdependencies of the original system
• Provided greater visibility and control for managers
• Allowed for simple scheduling changes with automatic downstream adjustments
• Secured departmental information with granular access controls
• Streamlined year-to-year planning and replication

This project demonstrates our ability to transform cumbersome legacy processes into elegant enterprise applications that enhance productivity and reduce operational friction.