Category

Finance & Accounting
Sponsored Projects/Research

Submitted By

Rochelle Hubbart, rochelle.hubbart@cuanschutz.edu [2], Research Administrator

Project Team

Rochelle Hubbart, rochelle.hubbart@cuanschutz.edu [2], Research Administrator

Project Description

I built an automated SpeedType One-Liner spreadsheet in Excel that displays a speedtype’s identifying attributes (Project ID, PI Name, Project Description, Award Reference Code, Project Status, and Start/End Dates) and financial information (Budget, To-Date Actuals, Available Balance, and Cash Balance) into a single row. It preserves the integrity of each corresponding cell alongside Department and Office of Grants and Contracts (OGC) notes/comments on closeout status. Additional columns track important factors in the sponsored project closeout process like Contract Type, Project Status, Total Billed/Invoiced To Date, Encumbrances, and the assigned DBMI administrator so OGC knows who to contact about certain projects. The spreadsheet pulls the data from the School of Medicine Administrative Analysis Environment (A2E) and an Administrator Reference Sheet using complex Excel functions (XLOOKUP, CUBEVALUE, and COUNTIF) and presents an easy-to-read, up-to-date reference that both the Department and Central Office can use during sponsored project closeout.

Project Efficiency 

Greatest efficiency gain is converting manual lookups and repeated explanations into instant, standardized outputs. What used to take multiple m-fin/CU Data reports, screenshots, and follow-up emails is now a one-step pull that produces the same “source of truth” every time. This spreadsheet reduces back and forth questions between the department and OGC. The administrator identification column has enabled faster routing when central reviewers need to reach out with questions about a project. Additionally, this spreadsheet has improved the efficiency of our quarterly sponsored project closeout meeting with OGC. We have gone from hour-long meetings to covering everything in 25 minutes or less.

Project Inspiration 

The idea came from recurring delays when our different teams described the same SpeedType differently and had to re-verify basic attributes each time questions arose. My department wanted a user-friendly summary that updates quickly and reflects current records and agreed-upon business rules—especially for time-sensitive sponsored projects. Initially, each team used separate tracking templates that were updated independently and reconciled during meetings. This collaborative Excel One-Liner, shared through Microsoft Teams, pulls the key information both groups need in one place and lets us see each other’s notes so we stay aligned. Both teams now use this one collaborative spreadsheet.

What Makes You Happiest about this Project?

I am happiest that it improves collaboration without adding complexity: both teams get quicker answers, see cleaner documentation, and everyone spends less time on repetitive “where do I find this?” work. It also builds trust—because the One-Liner is consistent and has accurate information. Meeting conversations focus on solutions rather than on reconciling data. Seeing turnaround times shrink and confusion drop has been the most rewarding outcome. I’ve received nothing but positive feedback from my internal team members and the central office on how this spreadsheet has improved working efficiency and saved them time.