Category
Finance & Accounting
Submitted By
Audra Strang, Audra.strang@cu.edu, Payroll & Benefit Accounting Program Director
Project Team
Audra Strang, Audra.strang@cu.edu, Payroll & Benefit Accounting Program Director
Project Description
I developed a streamlined solution to expedite retirement vendor reconciliations after each payroll cycle. Using an Excel template with integrated macros, queries, and text submission files, data is consolidated into a user-friendly format. Users input raw data from HCM or FIN by copying and pasting directly into established tables, triggering macros to organize and verify financial accuracy. Each tab checks the posted raw data from payroll and ensures the submission files match. Most importantly, I created a sophisticated macro that organizes submission files into a readable format according to vendor specifications, saving significant time. Previously taking 35 hours, the process now averages 2 hours per vendor, saving 20+ hours each payroll cycle. The template identifies errors and discrepancies, facilitating timely corrections and ensuring accurate retirement deductions for CU's 35,000+ employees. This innovation safeguards against costly errors and enhances efficiency, benefiting both the university and its employees.
Project Efficiency
The innovation is in leveraging macros to drastically reduce time spent reconciling retirement vendor processes after each payroll. Previously, users invested considerable effort in manual calculations, now streamlined by simply pasting data from HCM and FIN and activating a macro. Notably, a macro is used to break down a text submission file into a readable format, enabling users to analyze individual employee data efficiently. This automated process swiftly identifies errors, adjustments, and variables, facilitating proactive corrections with payroll. By ensuring data accuracy before submission to third-party vendors, the spreadsheet mitigates future correction needs, optimizing efficiency and reliability in financial accuracy.
Project Inspiration
The project was inspired by recognizing an opportunity to reduce stress and time spent on retirement vendor reconciliation after each payroll cycle. Given the direct impact of retirement deductions on employees' financial goals, accuracy and timeliness were of utmost importance. Additionally, there was an opportunity to save the university time by proactively ensuring accuracy for each employee's submissions. I aimed for the spreadsheet to be as automated and accurate as possible. By integrating macros to automate manual processes, we save the university time and provide an auditable data record for each retirement vendor process.
What Makes You Happiest about this Project?
What brings me the most happiness about this project is the assurance that all employee's retirement deductions are accurate and submitted on time. It's also wonderful to see the significant reduction in stress and time for users utilizing these templates. These spreadsheets provide peace of mind, ensuring accuracy for the University of Colorado's staff. Also, this project has enhanced the transparency of financial accuracy between payroll and third-party vendors. By offering user-friendly spreadsheets, I've fostered an environment of transparency and accuracy, which brings me immense joy and satisfaction.
