A tab has graphs that show how many vehicles are up for replacement and when. It allows fleets...

A tab has graphs that show how many vehicles are up for replacement and when. It allows fleets to understand future vehicle replacement needs.

Screencapture of the vehicle replacement tool

Many fleet managers face challenges with aging fleets, limited funds, lack of centralization, limited control, and little to no power to replace fleet vehicles. The key to making an impact in your organization is educating your administration on the importance of replacing out-of-lifecycle fleet units. By doing so, you are then better able to plan replacements and ensure that each division you serve will have the required dollars for future years to replace worn-out vehicles. This means savings from not having to perform excessive maintenance on aged vehicles and better tools for your various divisions to accomplish their required missions with reliable transportation.

Many fleets have limited resources, or are too small to have someone take the time to focus on this important task. We hope to make it easier for those fleets.

The Challenge

Earlier this year, the City of Columbus, Ohio, fleet division challenged graduate students from the Ohio State University (OSU) Max M. Fisher College of Business to create a tool to help fleets plan replacements. The goal was to enable fleet managers across the country to create a fleet purchasing plan using their own data. This tool would allow the fleet manager to frame his or her story for their respective finance departments to develop a fleet purchasing plan.

The Players

The City of Columbus fleet staff worked with Professor Elliot Bendoly from Ohio State, two groups of students from his Business Analytics class at Fisher, Utilimarc, and the Ohio State Transportation Division. These organizations provided students with data, support, and guidance.

Members of the group that created the final featured tool are: Bhargav Dharnikota, Sam Malhotra, Anna Mengqi, Raghav Sethi, Saurabh Suman,  and Qiu Yuxuan.

The Product

The tool, an Excel spreadsheet, allows users to choose up to three criteria for replacement. These criteria are:

  • Total cost of maintenance without fuel is greater than acquisition costs
  • Age of vehicle is greater than expected life
  • Miles is greater than expected miles. 

Users can adjust the acquisition cost inflation rate; the default is set for 2%.

Users enter their own vehicle data in the format provided. Data points required  include: user department name, equipment number, class code, class type, age, acquisition cost, miles, and cumulative total cost without fuel.

The final results are provided in table format as well as graphically with a concise fleet plan outlining vehicle replacements. The results show:

  • Vehicles out of lifecycle, from the worst of the fleet to the best. It shows units requiring the most immediate attention based on the inputs.
  • Replacement cost: This is an estimated sum of all vehicles by class code and division that require replacement in current and future years.
  • Replacement proximity: Three sets of bar charts are displayed in total or by division, graphically showing the proximity to the replacement standard.

The Result

We are pleased with the end result from these two groups and are happy to provide this tool to the industry for all fleets to begin their fleet planning. Ease of use was a very important ingredient for the tool so that fleets with limited resources can quickly “cut & paste” their data in the required fields and generate usable reports for planning purposes.

Following the guidance and instruction of Paul Milner from Utilimarc, the students did an outstanding job meeting this objective. He often became the lighthouse in stormy seas as the groups worked feverishly to complete their tool in just a few weeks. The tool was critiqued and after many iterations, it received a stamp of approval from Milner and the fleet teams from the City of Columbus and Ohio State.

The free fleet replacement tool, a video about how to use it, and sample data can be found in the Resources section of FleetSHARE. FleetSHARE is open to U.S. and Canadian public sector employees in fleet and related fields.

About the Author:

Kelly Reagan is the fleet administrator for the City of Columbus, Ohio.