Department of Transportation

Technology Category

Analytics & Modeling - Predictive Analytics
Functional Applications - Enterprise Resource Planning Systems (ERP)

Applicable Industries

Transportation

Applicable Functions

Business Operation

Use Cases

Predictive Replenishment

Services

Software Design & Engineering Services
Training

The Challenge

A Midwestern Department of Transportation (DOT) wanted to manage and forecast their cash flow, but their spreadsheet-based system was inflexible and slow. The DOT had difficulty knowing how to apportion variable funding streams across over 2000 construction projects in different phases of completion. Looming budget cuts made understanding cash flow critical. The DOT’s spreadsheet-based financial forecasting system had multiple weaknesses: Forecasting was slow and prone to error: Forecasting took weeks. The DOT finance group manually inputted thousands of data points and assumptions from multiple sources into complex spreadsheets that were too slow to open and difficult to edit. Errors were inevitable. Timely responses to change not possible: The DOT could not easily adjust forecasts for common issues like motor fuel tax changes, budget cuts, seasonality, or project delays. Responses to legislative inquiries took approximately four weeks. Expertise resided in a single person due for retirement: Only one person could understand the forecasting process and its impact on the DOT’s cash flow. The DOT was unable to train another person due to the incredible complexity of the system.

About The Customer

The customer in this case study is a Midwestern Department of Transportation (DOT) in the United States. This DOT is responsible for managing and overseeing transportation infrastructure projects, including road construction and maintenance. The organization handles over 2000 construction projects at various stages of completion and faces the challenge of managing variable funding streams. The DOT's primary goal is to ensure efficient cash flow management and accurate financial forecasting to navigate budget cuts and other financial constraints. The organization previously relied on a spreadsheet-based system for financial forecasting, which proved to be inflexible, slow, and prone to errors. The DOT's finance group had to manually input thousands of data points and assumptions from multiple sources into complex spreadsheets, making the process time-consuming and difficult to manage. Additionally, the expertise required to operate the system resided in a single person who was nearing retirement, further complicating the situation.

The Solution

Vanguard Predictive Planning easily integrated with the DOT’s workflow and provided fast, accurate cash flow forecasts. Vanguard worked closely with the DOT to implement a software alternative to spreadsheets that featured the following: Complete and accurate cash flow picture: Employees can view real-time funding data from any road project and see cost spreads adjusted for inflation, seasonality, and other factors. To best reflect the DOT’s realities, Predictive Planning allows authorized subject matter experts to input and lock notes and data adjustments for specific projects, funding streams, and expenses. Such changes have clear chains of ownership which promotes transparency and allows for easy audits. Instant analysis of funding scenarios: Calculations are completed in seconds via Predictive Planning and accessed through its secure web interface. DOT users can produce forecasting reports for any number of complex scenarios without worrying about “breaking” spreadsheets or slowing their computer. Integration with current work flow: Vanguard adapted Predictive Planning to the DOT’s workflow through database integration, add-ins like cost spreads based on the state’s weather and project history, and custom report formats. The webbased interface is intuitive so users of different skillsets easily master it with little training. Vanguard provided training for DOT users, from leadership to individual analysts.

Operational Impact

Instant forecasting reports enabled the DOT to tightly control its budget and respond to historic cuts with revenue-generating investments.
A new revenue-generating strategy: Instant 10-year forecasts and analysis of cash flow meant the DOT could move funds around without impeding cash flow. The DOT decided to place large federal grants not ready for disbursement in interest-bearing accounts. This became a new revenue stream in a time of severe restrictions.
Controlled, even spending: As the DOT Secretary described it, DOT fund apportionment had become sophisticated. The DOT previously experienced both shortages in key projects and end-of-year surpluses in non-urgent areas. Having a clear picture of their cash flow adjusted for state-specific issues like seasonality meant the DOT understood when to start, delay, or stop projects. Their fund disbursements became well-timed and predictable.

Quantitative Benefit