Streamlining Award Management for Higher Education: From Spreadsheets to Automation
The Challenge
The chemistry department's finance team managed a complex grant award lifecycle using a series of disconnected Excel spreadsheets. Staff manually moved data between sheets, drafted notification emails by hand, and created and maintained project folder structures for each award. The process was time-consuming, error-prone, and dependent on institutional knowledge rather than a reliable system.
The Approach
Bright Spectrum was engaged by Vorsite Corporation to architect and build a centralized solution on Microsoft 365. Working directly with department stakeholders throughout the engagement, we mapped the existing processes and translated them into a structured SharePoint implementation covering the full award lifecycle, from initial proposal submission through post-award budget tracking. Power Automate flows connected each stage, eliminating the manual handoffs that had previously slowed the team down.
To automate the manual work that had previously burdened the team, we designed and built a suite of 15 Power Automate flows organized into a single solution. Reusable child flows handled common tasks such as string sanitization, folder naming, working day calculations that respected university holidays, and standardized error notifications, ensuring consistency and maintainability across the entire system.
What We Built
- Automated folder structure creation triggered by award status changes, with role-based permissions applied automatically
- Automatic folder renaming when awards transitioned from proposal to funded status, keeping the document structure synchronized with the award lifecycle
- Business day deadline calculations across multiple pre-award milestone fields, accounting for university holidays and weekends
- Automated notifications for upcoming and overdue tasks across the award lifecycle
- Data promotion between tracking lists as awards progressed from proposal to funded status
- Color-coded urgency indicators in list views to surface at-risk items at a glance
- A PI-facing intake form feeding directly into the proposal tracking system
The Result - From Spreadsheets to Automation
What had previously required manual data entry across multiple spreadsheets, hand-crafted folder structures, and individually drafted emails became a largely automated process. Staff are now notified automatically when deadlines approach, project folders are created and organized without manual intervention, and the entire award lifecycle is tracked in a single centralized system giving both staff and principal investigators transparent, real-time visibility into award status.