Car Fleet Manager

• Version
• 27'494 Downloads
• 31.8 Mb File Size
• January 4, 2022 Updated
• 14 Number of comments
• ★★★★★

  ★★★★★
  Rating

This Car Fleet Management spreadsheet is the spreadsheet you might need if you are managing more than one car in your company and need to do organizing, tracking and reporting in a single excel spreadsheet. This spreadsheet will allow you to organizing your car daily trips and regular car services, tracking its trip, mileage, and fuel and creating reports based on those trips, including mileage trip report for tax deduction. But, this is not a sophisticated spreadsheet that could cover all of your needs, especially recording all of your car technical information (like position monitoring, car on/off time, etc). It is aimed to help you to manage your fleet in a more general purposes. It is designed specifically for cars, but you can apply it with other fleet types, like motorcycles or boats.

Features

Features that you will get in this spreadsheet are :

Car Trip Management

You can monitor and assign cars as well as their drivers based on their availability. There is a daily monitor worksheet that could give you information about its availability quickly. Each car will have its own worksheet which could ease you see the history of specific car in one page. All information in this worksheet will be processed to give you a summary of your car usage. Summary that you will have are Mileage, Fuel Efficiency, Taken Services and Expenses. You can generate reports based on this summary and use it to evaluate your car or car usage or even your driver performance.

Mileage Log

Specific mileage log category is needed for tax deduction purposes. It is a big upgrade of Mileage Log spreadsheet that you might have used. You can easily define it by categorizing your trip into business category. You will get specific car mileage summary in Mileage Report worksheet that you can customize as your official business mileage report for tax deduction.

Fuel Efficiency Calculator

How fuel efficiency is calculated in this spreadsheet? Fuel efficiency is defined as fuel consumption mileage per gallon. It is calculated by subtracting the last refueling mileage with the previous refueling mileage and divide by the previous volume. Fuel efficiency depends on type of car, trip situation and driving behavior. For example, you might assign a more fuel-efficient car for more crowded trip traffic or you might send your driver to get some training on driving your company’s car to save more fuel.

For example :
Your last refueling mileage is at 20th January 2016, 10 gallons, at 10.000 read in Odometer while previous refueling is at 13th January 2016, 11 gallons, at 9.700 read in Odometer. Your fuel efficiency will be 10.000-9.700/10, it is equal to 30 Miles/Gallon. Remember that it is assumed that you refueling your car while your car tank is very close to empty. The number should be vary if it rely on one refueling in one period only. That's why fuel efficiency will be calculated by averaging those calculated numbers. It could cover from 1 - 100 refueling times. You will see its results in monthly and yearly report, but there will be no refueling chart based on refueling times. You can create one using the Pro version if you want to have one.

To understand more about this template, you can download and read a short guidance on how to use it below.

Scheduling Maintenance and Services

You can put your car planned services as stated in their service books and monitor which car that must be prepared for it. You can set its budget as well where you can see how much expenses that have to be prepared.

Monitoring Expenses

You might find that some cars are getting unplanned services more often because of bad car conditions, for example if the cars are rented, or because of reckless drivers where you have to decide either to ask for car changing or give those drivers some training.

Custom Features

You can easily customize this spreadsheet to add features. A few ideas that come to mind:

• Evaluate Driver Performance: You can add other columns, like hours to calculate how many hours your driver drives your cars. Combine with number of accidents, fine tickets, other expenses and fuel consumption, it will give you data to evaluate the performance of your drivers where you can give them rewards and penalties based on their performance
• Map Fuel Consumption Chart: You can create other type of fuel consumption chart and link it to driver or other technical information which could lead you to conclude the fuel efficiency or your cars.
• Show Fleet Financial Table and Chart: You can create other table and chart for financial purposes. You can group budget and expenses of your cars into one table and visualize it using chart to compare between allocated budget and real expenses to plan a new budget for the following year.
• Compare Rent vs Owned Car Financial Expenses: You can add depreciation calculation for your owned cars and compare all financial calculation with rental cars. You can use it to find the most economic decision to provide car transportation in your company.

This template is fully unlocked and it should ease you develop it freely. And it is a perfect fit if you are looking a simple solution which can be used with Windows or Mac where all data can be exchanged among Excel, Word and Powerpoint files.

How To Use This Template

Setup Worksheet

•  Go to setup worksheet and type your starting company fiscal year in cell C10. Only one year report can be made in lite version.
• Type your distance and fuel measurement unit in cell C12 and C13. This unit will be used as a header reference in all worksheets to make you type unit value correctly. There is no conversion calculation being made.

Similar with other created spreadsheets, I assign one worksheet to one car, and using hyperlink function to move between those worksheets. This connection requires you to replace the filename in setup worksheet with exactly the same name if you have saved the file as another name or you will get "can't open the specified file" message.

Setup Worksheet: Status Table
There are 4 status prepared to be used for categorizing your car ownership status. You can modify current terms. They are explained as follows :

• Rent : Car is rented from 3rd party
• Rent-Return : Car has been returned to 3rd party (because the data might have been filled into car log, this status will give you an option to exclude it from future analysis).
• Owned : Car is purchased
• Sold : Car is sold

Setup Worksheet: Category Table
There are 8 categories available to be used for categorizing your entry in car log worksheets. They are explained as follows :

• Business : Car is used for business purposes (this category is used as a reference to calculate mileage usage for tax deduction)
• Operational : Car is used for operational purposes, exclude business and private purposes.
• Expenses : Car expenses while being used for business/operational purposes (toll, fines, parking etc). There are 10 expenses subcategory you can set in Expenses Table at the right side.
• Refueling : Car fuel fill up
• Planned Service : Car general service, usually proposed by car manufacturer based on certain distance or date period
• Unplanned Service : Car unplanned service which usually caused by accidents or any car disfunctional operation which make it to be repaired.
• Accident : Car accident (this data might be needed for insurance)
• Private : Car usage as a private usage

Initial Info Worksheet
Type detail information of your cars here. There are Car Data, Rent and Owned area. Fill car data area and select between rent or owned area. Color helper will be shown in respective rows to guide you on typing in the correct cells when you set the ownership status of your car in column D.

Service Worksheet
Fill car service plan in this worksheet based on its service book. You can put its budget as well. Pro version will allow you to fill budget up to  3 years.

Budget Worksheet
Fill car expense budget in this worksheet. Pro version will allow you to fill budget until 3 years.

Employee Worksheet
Type your company's drivers and users here.

Log Worksheet

You can start to record your car daily usage information in this worksheet.

Log Worksheet : Top Panel
At the top panel, you can read important information about your car pulled from the information you put on setup worksheet (non white cells) or log table below it.  Beside pulled information, there are white color cells that you need to fill, as follows :

• Status: You have to set the status in cell F10 of each car every day (the status will be used for tracking purposes). There are 3 status options you can choose, Available, Not available, and Booked.
• Mileage rate: mileage rate for each car based on your country or company regulation
  Rate calculation : Car category where the rate will be applied
• Booked: If you set car status as Booked, you need to fill the Booked table and when you set the status as available you can erase it. It is optional where you can see it in Tracker worksheet if you fill it.

Log Worksheet : Daily Activity Table
This is your daily working table. You can put all activities related with your car in this table by typing its information in respective rows.

• Type the date
• Select activity category (Business, Operational, Expenses, Refueling, Planned Service, Unplanned Service, Accident, Private). Color helper will be shown to guide you to complete the information in respective rows.

Business: Columns to be filled : Date, Starting Place, Destination, Purpose, Start Mile, End Mile, Driver, User, Mileage
Operational: Columns to be filled : Date, Starting Place, Destination, Purpose, Start Mile, End Mile, Driver, User, Mileage
Expenses: Columns to be filled : Date, Destination, Driver, User, Mileage, one of 10 Expenses columns
Refueling: Columns to be filled : Date, Destination, Driver, User, 3 Refueling columns (Odometer, Volume and Cost)
Planned Service/Unplanned Service: Columns to be filled : Date, Destination, Driver, User, 2 Service columns (Odometer and Cost)
Accident: Columns to be filled : Date, Destination, Driver, User, Accident, Notes
Private: Columns to be filled : Date, Starting Place, Destination, Purpose, Start Mile, End Mile, Driver, User, Mileage

Tracker Worksheet

This is a worksheet to monitor the availability of your car everyday. All cars will be displayed in one big table with important information pulled from car logs.

Important information that will be displayed are :

• Availability status of cars
• Last/next service mileage/date of cars

You can use excel filter tool to show cars based on specific condition, for example, based on availability or next service status

Summary Worksheet

All Summary Worksheet
Select specific year and you will see summary of your car's usage for that period. You can see total mileage, total and average fuel consumption as well as its cost and total services.

Use excel filter tool to show cars based on certain condition.

Individual Car Summary Worksheet
Select specific year and car to see its information monthly. The same with all summary worksheet, you can see mileage, fuel, service and expense information each month from January until December. And based on this information you can analyze related car's performance and condition where at the end, you can decide whether your cars need to be sold, returned, go to car repair or continue to use it.

Dashboard Worksheet
In this simple dashboard, as you can see in the top picture, you can quickly see top/bottom five of your cars based on categories as follows :

• Top Service Expenses Spender
• Top All Expenses Spender
• Most Fuel-Efficiency Consumption
• Worst Fuel-Efficiency Consumption
• Longest Distance
• Top Unplanned Service Taker
• Most Accidents
• Most Fines Occurrences

Mileage Report Worksheet
This worksheet is allocated for getting report for tax deduction. Select the car and its period and you will get report for those specific period. You can hide blank rows and add signature boxes below the table before print it or you can copy and paste the information to your official mileage report file.

There are many development can be made using information in this spreadsheet. It is not created in this spreadsheet because having all features in one spreadsheet will make the excel runs very slow. Having just 1-2 additional features or chart and deleting unnecessary information and process will be more efficient because needs for one company could be different from others.

Other report and summary that you can develop using this spreadsheets are :

Driver performance evaluation
You can add hours column to calculate how many hours your driver drives your cars. Combine with number of accidents, fine tickets, other expenses and fuel consumption will give you data to evaluate the performance of your drivers

Fuel consumption chart
Mileage and fuel data is there. Monthly summary is available as well. You can create a chart based on those data if you want to visualize into a chart.

Car Budget and Expenses Table and Chart
You can group budget and expenses for your cars into one table and visualize it using chart to compare between allocated budget and real expenses to plan a new budget for the following year.

Rent vs Owned Car Financial Calculation
You can add leasing and depreciation calculation in this spreadsheet and compare the money that you need to expense to decide whether you continue to purchase the car or just rent it. Financial assumption might be different for different companies.

And many more..

Or, you can add more columns to put or move your car technical information into this spreadsheet where you can analyze your car more detail. To do all those customization, you will need the Pro version which is fully editable where you can customize anything to suit your needs.

This excel spreadsheet is created without Macro and Built-In Form function. It will make this spreadsheet can be exchanged between Windows and Mac OS without any incompatibility issues.