CU I&E Submission: A&S Budget Office Position Analysis Tool

Category

Finance & Accounting

Submitted By

Meghan Perea, meghan.perea@colorado.edu, Divisional Budget Manager

Project Team

Meghan Perea, meghan.perea@colorado.edu, Divisional Budget Manager
Robert Clark, robert.d.clark@colorado.edu, Director of Budget
Joshua Firestone, joshua.firestone@colorado.edu, Divisional Budget Manager
Raúl San Agustín, raul.sanagustin@colorado.edu, Divisional Budget Manager

Project Description

The Position Analysis Tool is an in-house reporting suite that enables the A&S Budget Office to identify in a single Excel table if an employee's position budget aligns with their actuals/encumbrances and is balanced. It incorporates filtering functions, macros, and pivot tables to quickly sort which positions are out of balance and allows users to drill down for additional details helping identify if an issue is related to budget or payroll. The tool can sort by departments, speedtypes, position type, payroll data, etc. to enable users to quickly analyze position budget data across the college.

As A&S relies on position budgeting, this tool helps ensure that budget is correctly allocated for the rostered faculty and staff employees of CU Boulder's largest college. It enables the Budget Office to quickly identify position problems so that we can notify department leaders, thereby helping departments avoid over and underpayments.

Project Efficiency 

The Position Analysis Tool combines multiple reports from CU-Data and HCM into one workbook. By using PowerQuery to filter the data on the backend and utilizing Pivot Tables to present the data, the tool can distill thousands of lines of information into a user-friendly dashboard. This saves Budget Office team members extensive amounts of time, as we only need to refer to one data source as opposed to several. With the click of a button, the tool's sorting functions allow us to extract the specific position budget data we want to analyze without always needing to build a unique report.

Project Inspiration 

A&S recently reorganized, realigning the formerly centralized College budget into separate divisions. Therefore, it was imperative for us to create a shared tool that would allow us to analyze position budget data quickly, efficiently, and accurately, despite budget decentralization. By reimagining a previously used report, our office has been able to leverage PowerQuery and create a more robust tool that now includes search and filter functionality that increases data analysis options. A&S is identifying ways to create dynamic, efficient reporting for divisional, College, and department leadership, and tools like the Position Analysis Tool support this goal.

What Makes You Happiest about this Project?

It makes us happy that this tool supports our team daily with position analysis, saving team members time and brain power. It allows us to focus on diagnosing problems rather than being bogged down finding the problems in the first place. It frees our team members from the minutia so that we can focus on the wildly important. The tool also enables us to better support departments, resulting in more accurate budgeting and payroll for A&S employees. Budget accuracy has a ripple effect that brings us joy, knowing that our work allows for maximum use of student tuition dollars.