InTrans / Jan 25, 2021

New spreadsheet tool helps determine optimum time for equipment replacement

The ink is just about dry on local budgets, but it’s never too early start planning for the next year.

Thankfully, there’s a new spreadsheet tool that will help local agencies plan for their equipment needs with more certainty. The Equipment Life-Cycle Cost Analysis Tool (E-L-T) allows users to assess the optimum time to replace their trucks and motor graders considering both purchasing and leasing options.

“We found less than a third of local agencies in Iowa have a replacement process, and many that do have a process use a general standard of periodic replacement, which is not an efficient method,” said Jennifer Shane, a principal investigator on the project that developed the tool. “This tool will give Iowa counties and cities an enhanced ability to make defensible equipment management decisions.”

The tool is also designed to allow users to try different scenarios to better understand the sensitivity of their assumptions and thus better explain their decisions during budget seasons. The tool has two modules, one that provides a specific year for replacement based on the user inputs and one that runs several models based on a range of inputs to give a range of replacement options.

All results are displayed graphically to better gauge the optimum replacement time.

“We know that counties spend about half their budgets on maintenance, and equipment is about 27 percent of maintenance at any given time,” said Shane. “We wanted to develop something county engineers can tailor to their needs and use to present to their boards on their equipment needs.”

The research project included a couple of surveys of counties to get a better idea of what equipment data is currently collected and to better understand the replacement process. After determining that counties collected little data, the project also developed a record-keeping template as well so that counties can better use the tool in the future by providing their real-world data.

“A simple rule for replacing equipment is when the annual costs for maintenance and operating the equipment exceed the trade-in value, it’s time to replace the equipment,” Shane said. “Thus, keeping track of those costs and trade in-values are essential pieces of data in equipment management.”

While the project focused on Iowa counties, and came out of a discussion at a County Engineers Research Focus Group meeting, the tool will also work for other local agencies, including Iowa cities.

The spreadsheet tool, associated user’s manual, and the full research report are available here.