Photo courtesy of

Photo courtesy of

When Mel Galbraith, PCFM, fleet director for the City of Scottsdale, Ariz., was first hired as the parts supervisor for the city, parts invoices were received in both fleet software and in the city’s accounting software. To Galbraith, the solution was obvious: have a single entry, then upload others from the first.

His colleagues were skeptical. The records were housed using different software on different servers. A single entry couldn’t possibly work. But then he found an ally — colleague Jane Vazquez, systems integrator — and together they made it a reality; they were now able to extract files from fleet’s software and import it into the accounts payable system.

This was just the first step toward efficiency for Galbraith. Eventually he was promoted and became responsible for the department’s budget. Again, the current process just didn’t seem right — there had to be a better way. “I tried to learn the current process and loathed it so much that I vowed I would never do the budget the old way again,” he said. “So I created data extractions, reports that calculated and cut months of work down to seconds for the second year’s budget preparation.” Once again, extracted data and useful reports led to efficiencies. 

Since then, the tools have evolved. Where Microsoft Access and Excel were once the go-to solution, Crystal Reports and SQL Reporting Services classes led Galbraith down a more sophisticated path. “Learning that this latest tool would allow me to create reports, deploy them on our intranet, and set up e-mail schedules, I was hooked,” he said.

Today, the Scottsdale fleet uses a wide variety of reports to improve productivity in a number of areas, including the shop, equipment, vendors, vehicle replacement, and budgeting. Galbraith and his team share how fleet reports have made an impact on their organization and offer ideas for how other fleets could benefit, too.

Improving Shop Productivity

The City of Scottsdale uses fleet reports in a number of ways to understand and improve shop productivity. For starters, the fleet uses generally accepted flat-rate times from resources such as Mitchell on Demand and measures the technician’s ­actual productivity against these standards.
Another report, direct labor, is a measure of technician time spent working on billable equipment compared to non-­billable time. Each supervisor and technician gets an automatic e-mail report indicating their direct labor for the prior week, month, and fiscal year to date.“A ‘direct versus indirect labor’ report is automatically e-mailed weekly to the crew chiefs for their crew and to each technician for their own performance,” said Brent Hinkle, fleet technician crew chief. “The ‘weekly direct labor report’ is a convenient way to analyze technician direct labor.” 

Hsao-Chin Kouts, database administrator of the Information Technology Department, agrees reports provide improved visibility into shop operations. “Between inventory, labor cost, work orders, and repairs, you’ve got some great reports for analysis and decision making,” she said.

Scottsdale’s shop reports don’t begin and end with measuring performance against time standards. The city also tracks the number of days a work order is open from start to finish. “I find the ‘work order non-closed’ report the most valuable, as it gives an overview of everything we have going first thing in the morning, including anything written on the second shift, and helps in assessing the current workload,” said Jim Euler, fleet technician crew chief. “This then allows for the best assignment of jobs to the technicians so we can better meet our goals.” 

The fleet also measures the time from the close of one work order to the opening of the next and the number and duration of road calls. “Our ‘road calls report’ is a prime example of how fleet reports lead to results. We saw what was unacceptable for road call quantity and duration. Staff was made aware of the concern, a report was created, and we began emphasizing a new motto: ‘Find it! Fix it!’ If deficiencies are found and repaired when a unit is in the shop, road calls will diminish dramatically,” Galbraith said. “Over the past five years, we have seen an 80% sustained reduction in frequency and duration of road calls. That is how you know the report works — results!”

Likewise, several preventive maintenance (PM) reports are generated, including which PM services are coming due in the next two weeks, current work orders with PM services due, and a PM task checklist. “The ‘current work order with PM due’ report is a valuable tool, as it acts as a failsafe for personnel should there be a human oversight on what is due or coming due,” Hinkle said.

Purchasing Equipment

Fleet reports help­­ the city manage equipment as well. For instance, using fleet reports, the city was able to compare small pickups such as the Ford Ranger and Chevrolet S-10 with their full-size counterparts. The comparison included the purchase price and repair and fuel costs. “The full-size pickups were significantly less costly to own than their little brothers,” Galbraith said. “This fact holds even without including resale numbers.”

The City of Scottsdale also compares equipment costs by vehicle class, and repair costs are grouped in 10,000-mile buckets. “When we first changed from Crown Vics to Chargers and now to Tahoes, we knew the cost trends of each,” Galbraith commented.

Other reports used to manage equipment productivity include preventive maintenance due, road calls, shop supplies, count and listing of police vehicles down per day, equipment receipt to in-service days, and accidents interface.

Working with Vendors

The City of Scottsdale uses fleet reports to better manage those outside of the organization, too — its vendors. Just like in-house repairs, the time a unit is at a vendor for repairs is tracked. When a unit has been down for a fourth day, red text indicates the team should follow up on the repair status.

The fleet team also sets up blank purchase orders at the beginning of the fiscal year to use regularly in the months to come. “As the year progresses, we have a report that compares the time lapsed to the percent of the funds spent by vendor,” Galbraith said. “Based on this information, we can make decisions to request an increase in the purchase order, spread the purchases to other vendors, or postpone purchases.”

Determining Lifecycles & Replacement Schedules

Just as the city monitors preventive maintenance schedules via fleet reports, it also leverages reports to determine replacement schedules. For instance, one Excel report query extracts repair costs and places them in 10,000-mile buckets. From there, reports do the following:

  • Provide graphs that display where repair costs take an upturn, per equipment class
  • Establish a replacement point slightly prior to the upturn in repair costs
  • Calculate the lifecycle in months and miles.

Both lifecycles (months and miles) are then entered into the fleet software for equipment in the relevant class. Then, a score is calculated, with one point for each of the following:

  • Due by months of service
  • Past due by more than one year
  • Due by miles/hours
  • Past due by 112% of the established miles/hours
  • Plus 1 if last year’s miles are equal or greater than 12,000 
  • Minus 1 if last year’s miles are less than or equal to 5,000.

Lifecycles, in months, are used in calculating the acquisition rates (as indicated under “Managing Budgets” below). “A top-down selection until our budget allocation is reached gives us our preliminary replacement list,” Galbraith said.

Managing Budgets

Budgeting is another area in which fleet reports are a big help. For instance, early on in the budgeting process, internal rates must be established. These consist of acquisition (or rental), maintenance and repairs, and fuel.

Acquisition rates are computed in a report using P(1+R)^n , where P equals the original purchase price, R equals the rate (inflation factor), n equals the number of months the asset is expected to be in service. This future value is then divided by n, giving the monthly acquisition rate for the asset.

Life expectancy is then calculated in another report that groups equipment by class and groups repair costs in categories of 10,000 miles. Replacements are set prior to a significant upturn in repair costs. “The report includes an inflation factor parameter to accommodate changing economic times,” Galbraith said. “When budgets were extremely tight, the rates to our customers were calculated using a negative [deflation] factor.”

Once acquisition rates are set and approved, the user department is charged the same rate each month for the fiscal year.

Maintenance and repair rates are projected based on the 12 most recent months at the time of budgeting. The actual monthly charges are based on actual expenses for the month, and are posted to the general ledger by a report-generated interface.

Getting Started

For fleets interested in using reports to increase productivity in all areas of the fleet, Galbraith recommended looking first at which tasks are redundant, then finding ways to automate and simplify those tasks.

From there, establish goals. Which areas need to improve? Gather the historic data, then set a specific goal that is both relevant and attainable — but not too attainable. “The goal needs to require stretching, even if painful at first,” Galbraith recommended. “Then measure, report, and publish your progress over the timeframe established in your goal.”

With a goal in mind, look at your process. What kind of data do you provide to others on a regular basis? How much time do you spend providing that information? Automate these processes, and coordinate and cooperate with other departments in automating interactions. “Combined efforts between fleet, accounting, purchasing, risk management, and all other fleet equipment and information users has saved us several hundred and maybe thousands of hours every year,” Galbraith noted.

And finally, Galbraith recommended finding an advocate — that person in your organization who is passionate about efficiencies. “They are invaluable in your quest,” Galbraith said. “The world is full of naysayers. Professional fleet management requires real, accurate, meaningful, and timely data. Managing by intuition, no matter how good someone is, won’t carry a fleet manager into the future.”