CU I&E Submission: Simplifying Quarterly cFTE Submissions

Category

Finance & Accounting
Technology

Submitted By

Anthony Pfaff, anthony.pfaff@cuanschutz.edu, Business Services Program Director 

Project Team

Anthony Pfaff, anthony.pfaff@cuanschutz.edu, Business Services Program Director 

Project Description

CU Medicine requires the submission of clinical full time equivalent (the portion of faculty's time dedicated to clinical activities--cFTE) quarterly for reporting compliance related to provider relief funds. The Department of Psychiatry has a cFTE database broken down by all the different funding sources faculty have, and historically it has been a painstaking process to manually sum all of a faculty member's cFTE and enter it into the CU Medicine spreadsheet format one by one by looking at the name in the CU Medicine file and manually entering the corresponding faculty member's cFTE from the Psychiatry database. The project's aim was to optimize this process so that it could be done for the entire department quickly by joining the two datasets based on employee ID, which would then allow automation of the process rather than manual effort comparing names and summing values.

Project Efficiency 

CU Medicine's cFTE submission file has no identifiers other than name, making it difficult to automate joining it to Psychiatry cFTE data. Despite this, CU Medicine has very effective business intelligence tools such as MyBI. While CU Medicine's cFTE spreadsheets only have faculty names and no other identifiers on them, MyBI has name information for faculty in the same format as the cFTE submission file. The innovation here was seeing how to join the CU Medicine spreadsheet with MyBI data to add employee ID, which was then joined in bulk to Psychiatry's cFTE data using Excel functions, saving time.

Project Inspiration 

After I became responsible for cFTE submissions for my division in the Department of Psychiatry, I realized that the process was manual and time intensive. I wanted to see if it would be possible to optimize the process and make it significantly less manual, such that after doing a bit of setup, I could populate the cFTE submission to CU Medicine in mere minutes and save significant time.

What Makes You Happiest about this Project?

Ultimately I'm happy that the concept worked and that now the cFTE submission process is much quicker, saving probably a day of effort per quarter across three people.