Region Four Program Tracker Application

Item #: 20240025

CONTACTS

Implementing Organization: Region Four
Implementation Lead: Bracken Davis and Cameron Gay
Development Team:
- Cameron Gay (Region Four Project Manager)
- Bracken Davis (Region Four GIS Manager)
- Kurt Marchant (Region Four Design Manager)
- Jordan Jarrett (Region Four Roadway Design Engineer)
- Anne Ogden (Region Four Traffic Engineer)
- Rashelle Crowther (Region Four Assistant Traffic Engineer)
- Rachel Morris (Region Four Assistant Traffic Engineer)
- Kevin Kitchen (Region Four Senior Communications Manager)
Article Written By: Mindi Cowley
Innovation Council Liaison: Mindi Cowley
Innovation Team Coordinator: Quinten Klingonsmith

STATUS

Implementation Date: March 1, 2023
Adoption Status: Fully Implemented
Adoptability Note: This spreadsheet and technique could be adopted by other regions.

APPLIES TO

Topic: Data Collection, Analysis and Visualization
Organization(s): Region 1, Region 2, Region 3, Region 4
Job Role(s): Administrative Staff / Commissions, Business Analyst, Construction Engineer, Finance / Accounting, Information Specialist, Materials Engineer, Program Director, Program Manager, Program Specialist
Tags: active transportation, highway transportation, capital productivity, infrastructure preservation, labor productivity, employee empowerment, job satisfaction, economic benefits, expenses, expenditures, tracking systems, accounting > billing, budgeting, payment, ( comptroller ), communications > public relations ( PR ), contract administration > bids, contracting, solicitations, contracts, ( RFP, FRQ ), finance > appraisals, investments, asset management, cost recovery, cost sharing, fees, loans, revenues, sales, taxes, usage charges, governance > auditing, policy making, procedures, compliance, law, ( legal ), human resources > personnel management > compensation, relations, personnel performance, retention, recruiting, hiring, supervision, turnover, retirement, information technology >> software, networks, leadership, logistics > inventory, distribution, purchasing, procurement, warehousing, supply chain management, marketing > advertising, risk management, insurance, ( data visualization ), construction, contracting, design, planning, program management, project management, quality of life

PROBLEM

Program Management, Design, Environmental, Traffic and Safety, and Public Information groups within Region Four require different information about projects. However, not all of these groups have access to this information in the ePM system, or, if they do have access it takes a long time to compile.

To provide each group with the information they need, the program manager manually copied information from ePM into a spreadsheet.

Unfortunately, this information had to be painstakingly hand-entered and regularly updated from ePM or ePM reports. The spreadsheet was often outdated because the information changes frequently and it takes so long to update.

The spreadsheet was also used by each group to report information back to the program manager and to aid in managing the projects within their discipline. There were tabs for tracking design, traffic and safety oversight, and other assignments. With so many people editing the spreadsheet, the program manager was concerned that users might alter fields that didn’t belong to them.

CHANGE

The old spreadsheet was enhanced to automatically update daily and protect data integrity.

To protect data, tabs were added for each group and locked so that only members of that group could edit the color-coded fields in their tab. Columns for needed project data not available in ePM were also added.

To keep data fresh, an FME (Feature Manipulation Engine) application was developed to automatically pull data from ePM and other areas of the spreadsheet into each group’s tab.

The data can be filtered on any one of the fields pulled from ePM or the manually added data making it very flexible to generate any kind of project list that is needed.

RESULT

Each user group has the information they need to track projects. Because most of the data is pulled from ePM and shared between tabs, users are required to update just a few fields with information that is not housed in ePM.

They don’t need to chase down discipline-specific information because they can see what other groups are managing in their tabs.

Users are less reliant on the program manager to create special reports because they can filter/query data quickly and efficiently. For example, if in the past the program manager was asked questions about a particular type of project in a given year or with a certain type of funding, it would have taken several hours to go through ePM to compile the information. Now it only takes a few minutes.

Next Steps: In the future, Region Four would like to include Right of Way, and Survey and Utilities information, as well as a schedule and funding year aspect into the spreadsheet.

IMPACT

BETTER MOBILITY

GOOD HEALTH

CONNECTED COMMUNITIES

STRONG ECONOMY

The region can track projects much easier with less help from the project manager
The program manager spends less time creating reports for others
Automation saves the program manager hours of manual data entry
Project information is always up to date

EFFICIENCIES*

Cost Avoidance: $366,862 over 20 years (Mar 1, 2023 - Mar 1, 2043)
Labor Savings: 220 hours annually
Benefit/Cost Ratio: 35:1

*Benefits are estimated net of initial and ongoing expenses. Savings are averaged over the expected benefit life of the innovation. See details.

Page updated

Report abuse