Migration of Financial Reporting to Google Cloud Saves Time

Item #: 20260002

CONTACTS

Implementing Organization: Finance / Comptroller
Implementation Lead: Brad Smith
Development Team: Brad Smith, Mike Brooks

Article Written By: Brad Smith
Innovation Council Liaison: Ben Teran
Innovation Team Coordinator: Quinten Klingonsmith

STATUS

Implementation Date: January 1, 2024
Adoption Status: Fully Implemented
Adoptability Note: What data is out there that your team could access in new ways to improve your work?

APPLIES TO

Topic: Data Collection, Analysis and Visualization
Organization(s): UDOT (all), Aeronautics, Amusement Ride Safety, Central Admin / Senior Leaders, Central Construction, Central Maintenance / Facilities Management, Central Materials, Central Preconstruction, Communication, Data Technology & Analytics, Employee Development, Environmental Division, Equipment Operations, Finance / Comptroller, Internal Audit, Motor Carrier Division, Performance and Asset Management, Planning, Policy / Legal, Program Financing, Railroad, Region 1, Region 2, Region 3, Region 4, Research and Innovation, Right of Way, Risk Management, Strategic Technologies, Structures, Traffic and Safety, Traffic Management Division, Transit and Trails
Job Role(s): Administrative Staff / Commissions, Business Analyst, Executive Leadership, Finance / Accounting, Information Specialist, Program Director, Program Manager, Program Specialist, Roadway Operations / IMT, ROW / Permits
Tags: labor productivity, employee empowerment, economic benefits, automation, redesign, accounting > billing, budgeting, payment, ( comptroller ), finance > appraisals, investments, asset management, cost recovery, cost sharing, fees, loans, revenues, sales, taxes, usage charges, information technology >> software, networks, comptroller, finance, report, BigQuery, Cognos, Google, Sheets

PROBLEM

UDOT moved away from Microsoft Excel, so managers needed a new way to access financial reports. Because they could not update information themselves, they had to send it to the Comptrollers to manually update it for them. Accessing and maintaining financial information became time-consuming and inefficient.

CHANGE

Brad Smith (UDOT Comptroller) worked with Mike Brooks (UDOT Data Analytics) and DTS to integrate financial information from the data warehouse (Cognos) into BigQuery (Google Cloud Services) so that reports could be generated within Google Sheets and accessible to all.

A rigorous testing phase, spanning nearly one and a half years, involved daily monitoring of various financial information types against established, validated reports to ensure data accuracy. Furthermore, several custom datasets were created by joining base datasets, providing users with access to consolidated data tables without requiring coding knowledge.

Once data accuracy was validated, various tools were developed that enable users to leverage Google Cloud Services within Google Sheets for self-service reporting and information retrieval.

To facilitate the effective use of these tools, training was provided across UDOT, including the regions, the Transportation Management Division, groups at the Complex, and one-on-one training for individuals.

RESULT

With the new system, a greater number of UDOT financial reports are available to managers throughout the department. The ease of sharing reports with managers and directors has been greatly enhanced. The new approach also benefits from the auto-updating, multi-viewer access, and auto-saving functionalities of Google Workspace.

This automation saves many hours across the department. One specific example is the budget team no longer needs to spend a week each month refreshing Excel files, which would sometimes crash the computers due to the large amount of data being processed. These files have been converted to Google Sheets with Big Query connections. Now, data refreshes are scheduled to occur automatically.

Improved access to accurate financial data facilitates good decision-making. For example, these resources were used to calculate the pay-for-performance bonus and ASIs for FY24 and FY25.