This unique template enables users to compile a complete vehicle logbook for all business and private travel. The template accommodates multiple vehicles and can also be used to calculate travel charges for invoicing to clients. The template is suitable for individuals who need to compile a vehicle logbook for income tax purposes and for businesses that need to manage a fleet of vehicles, calculate amounts that need to be charged to clients, manage pool cars and analyse business & private use of vehicles.
The template includes the following sheets:
Logbook - this sheet can be used to compile a comprehensive vehicle logbook for multiple vehicles. The vehicle registration number and client code selections are based on the contents of the Vehicles and Clients sheets. The travel distance is calculated based on the opening & closing odometer readings and is compared to the standard distance for the selected client which is defined on the Clients sheet.
Clients - create client codes for all clients on this sheet and enter a client name & standard travel distance. The calculations on this sheet can be used to calculate the amounts that need to be charged to clients based on any user defined date range & vehicle selection. The sheet also contains calculations which can be used to analyse the travel that is charged to clients on a monthly basis.
Vehicles - add vehicle registration numbers for all vehicles on this sheet and enter a vehicle description. The calculations on this sheet can be used to calculate the business & private mileage of each vehicle based on any user defined date range & client selection. The sheet also contains a monthly analysis of the total mileage, business travel % and number of business trips over a three month period.
Form - this sheet contains a form which can be printed and supplied to all drivers in order to record all business & private trip details. The trip details that are recorded on these forms can then be entered on the Logbook sheet in order to compile a comprehensive logbook for each vehicle.
Vehicle Logbook Details
All business & private trip details for each of the vehicles that are included in this template need to be recorded on the Logbook sheet. All the columns with yellow column headings require user input while the columns with light blue column headings contain formulas which are automatically copied for all the new rows that are added to this sheet.
Note: The contents on the Logbook sheet have been included in an Excel data table. All the columns on the Logbook sheet with yellow column headings require user input. The columns with light blue column headings contain formulas that are automatically copied for all new entries that are added to the table. Entries can be added to the table by simply entering a travel date in the first empty cell in column A - the table will be extended automatically to include the new entry.
The Logbook sheet contains the following user input columns (columns with yellow column headings):
Travel Date - enter the date of the trip in this column. All dates must be entered in accordance with the regional date settings that are specified in your System Control Panel.
Travel Time - enter the time of the trip in this column. All times must be entered in accordance with the regional time settings that are specified in your System Control Panel.
Form Number - enter the form number of the printed form which was used to record all the trip details. If forms are printed with sequential form numbers, the sequence of the form numbers can be checked in order to ensure that all forms have been recorded on the Logbook sheet.
Vehicle Reg Number - select the vehicle registration number from the list box in this column. The list box includes all the vehicles that have been added to the Vehicles sheet. New vehicles must therefore be added to the Vehicles sheet before the registration number will be available for selection on this sheet.
Odometer Opening - enter the odometer reading at the start of the trip in this column.
Odometer Closing - enter the odometer reading at the end of the trip in this column.
Travel Type - select either "Business" or "Private" from the list box in this column. If any cell in this column is left blank, the trip will be seen as a private trip.
Client Code - select the client code from the list box in this column. The list box includes all the client codes that have been added to the Clients sheet. New client codes must therefore be created on the Clients sheet before being available for selection on this sheet.
From - enter the location from which the trip started.
To - enter the trip destination in this column.
Purpose - enter the purpose of the trip in this column.
Rate - enter the rate per mile or kilometre that needs to be charged to the client. Charges are typically only applicable to business trips and a nil value is usually therefore specified for private trips.
Driver - enter the name of the driver in this column.
The Logbook sheet contains the following calculated columns (columns with light blue column headings):
Total Trip Distance - the distance of the trip is calculated by deducting the opening odometer reading from the closing odometer reading. If the odometer reading is in miles, the trip distance will be in miles. If the odometer reading is in kilometres, the trip distance will be in kilometres.
Total Trip Charges - this amount is calculated by multiplying the rate in column L by the trip distance in the previous column.
Standard Distance - the standard trip distance is defined for each client on the Clients sheet.
Distance Variance - this calculation is the difference between the standard client distance in the previous column and the trip distance which is calculated in column N. It therefore indicates a variance between the logbook entry and the standard distance to the appropriate client location and can be used to identify errors in recording the distance travelled for business purposes. There is no standard distance for private travel which means that this calculation is not performed for private trips.
Error Code - this column will contain an error code if there is a problem with the input in any of the user input columns. Refer to the Error Codes section of the instructions for more info on the error codes that may be encountered. All error codes must be resolved in order to ensure that the template calculations remain accurate.
Note: Trips can be added to the logbook on a single or round trip basis. If you are basically travelling to and from a client, it may not be necessary to add both trips to the logbook as it will only result in additional entries needing to be recorded. If round trips are used, the odometer readings should be entered accordingly in order for the correct distance to be calculated and the basis of entering round trips should be applied consistently to all the business & private trips that are added to this template.
Note: Aside from the mileage that is travelled, private trips usually do not need to be specified in detail on a per trip basis. The mileage of multiple private trips can therefore be determined at the start of the next business trip based on the odometer reading at the time. The template does however provide for entering individual private trips - this basis of input will only result in more entries having to be added to the template.
Note: A customer code of "XXX01" needs to be assigned to all private trips. This customer code has been created specifically for private trips and has been added to the Clients sheet. This default private trip client code therefore needs to be retained in the list of client codes.
All the column headings on the Logbook sheet contain filter selection arrows which indicate that the Filter feature has been activated on the Logbook sheet. This feature can be used to filter the data on the sheet based on requirements. Note that the totals above the column headings will also only be based on the filtered records once a filter has been applied to the sheet.
Note: The Filter feature can be used to display the detailed logbook records for any of the individual or combined totals on the Clients or Vehicles sheet by simply applying the same filter criteria as has been applied to the appropriate sheet to the columns on the Logbook sheet. This is especially useful if you want to supply a client with the detailed logbook entries which make up the total that is charged to the client for any particular period.
Client Charge Sheet
The Clients sheet can be used to calculate the travel charges that needs to be invoiced to each client based on any user defined date range. Simply create a new client code for all client accounts, enter a client name and enter the standard distance which is applicable when travelling to the particular client. The columns with light blue column headings contain formulas which need to be copied for all new client codes that are created.
Note: All the client codes that are created on the Clients sheet need to be entered in a continuous cell range otherwise some entries may not be included in the template calculations. You should therefore not insert any blank rows between the rows that contain data.
The Client sheet contains the following user input columns:
Client Code - a unique client code needs to be created for each client account. Our sample data uses three letters and two numbers but client codes can be created in any format (we do recommend using a combination of letters and numbers though).
Client Name - enter the client name in this column.
Travel Distance - enter the standard distance which is applicable when travelling to the client. The distance should be based on the same measurement (miles or kilometres) which the odometer readings on the Logbook sheet are in. This standard distance is compared to the trip distances on the Logbook sheet in order to ensure that accurate travel distances are recorded.
The Clients sheet contains the following calculated columns (columns with light blue column headings) which are all based on the filter selections at the top of the sheet:
Total Mileage - this is the total mileage that has been travelled to the client during the user defined period based on the logbook entries which have been recorded on the Logbook sheet.
Total Charges - this is the total charges which have been allocated to the client code and is calculated by multiplying the travel distance in column N on the Logbook sheet by the rate in column L on the Logbook sheet.
Rate Charged - this is the average rate which has been charged for travelling to the client and is calculated by dividing the total charges by the total mileage.
Number of Trips - this is the number of trips which have been allocated to the client. If round trips are recorded as a single trip, the number of trips would reflect round trips.
Average Distance - this is the average distance of the trips to the client as recorded on the Logbook sheet and is calculated by dividing the total mileage by the number of trips.
Distance Variance - this is the variance between the standard client distance in column C and the average distance in the previous column. This calculation indicates whether the trip distance which is recorded on the Logbook sheet is consistent with the standard distance which is specified for the particular client.
Total Mileage by Month - these three columns display the total mileage per month which has been allocated to the client. The monthly reporting periods are determined by the To Date which is specified in cell I4. The calculations in these columns can be used to review the mileage which have been allocated to clients for the appropriate quarter.
Number of Trips by Month - these three columns display the number of trips per month which has been allocated to the client. The monthly reporting periods are determined by the To Date which is specified in cell I4. The calculations in these columns can be used to review the number of trips which have been allocated to clients for the appropriate quarter. This is especially useful when you need to monitor sales representatives with fixed routes.
Note: A customer code of "XXX01" needs to be assigned to all private trips. This customer code has been created specifically for private trips and has been added to the Clients sheet. This default private trip client code therefore needs to be retained in the list of client codes.
Note: The total business & private mileage as well as the appropriate percentages are displayed in column C and D at the top of the sheet.
Note: The template calculations only include the first 200 client codes on the Clients sheet. If this default value is not sufficient, open the Name Manager feature (Formulas tab on the ribbon) and simply change the value of 200 which is specified for the CRecords named range to the required number of client codes which need to be included in the template calculations.
Filter Selection Options
All the calculations in the light blue columns on the Clients sheet are based on the filter selections which have been specified at the top of the sheet. These selections enable users to compile a client charge sheet based on any user defined date range and for any individual vehicle. The filter selections on the Client sheet include the following user input cells:
From Date - the date range for which the calculations on the sheet are performed starts on the user defined date in cell G4. The date should be entered based on the regional date settings which are specified in the System Control Panel. If this cell is left blank, the date range will commence on the first day of the year that is five years before the current year (it can therefore be reasonably expected that all logbook entries for the past 5 years will be included in the calculations on the Clients sheet).
To Date - the date range for which the calculations on the sheet are performed ends on the user defined date in cell I4. The date should be entered based on the regional date settings which are specified in the System Control Panel. If this cell is left blank, the date range will end on the last day of the current calendar month (which is determined based on the current system date).
Vehicle - the calculations on the Clients sheet can be performed based on a single vehicle or all the vehicles which have been added to the Vehicles sheet. If you want the calculations to be based on a specific vehicle, simple select the appropriate vehicle from the list box in cell K4. If you want the calculations to be based on all vehicles, simply delete the contents of cell K4 in order to display a blank cell.
Vehicle Charge Sheet
The Vehicle sheet can be used to analyse the business & private mileage of any vehicle based on any user defined date range. Simply add a new vehicle registration number and enter a description for the vehicle. The columns with light blue column headings contain formulas which need to be copied for all new vehicles that are added to the sheet.
Note: All the vehicle registration numbers that are added to the Vehicles sheet need to be entered in a continuous cell range otherwise some entries may not be included in the template calculations. You should therefore not insert any blank rows between the rows that contain data.
The Vehicles sheet contains the following user input columns:
Vehicle Reg Number - a unique vehicle registration number needs to be entered for each vehicle.
Vehicle Description - enter a description of the vehicle in this column.
The Vehicles sheet contains the following calculated columns (columns with light blue column headings) which are all based on the filter selections at the top of the sheet:
Total Mileage - this is the total mileage that has been recorded for the particular vehicle during the user defined period based on the logbook entries on the Logbook sheet.
Total Charges - this is the total charges which have been allocated to the vehicle and is calculated by multiplying the travel distance in column N on the Logbook sheet by the rate in column L on the Logbook sheet.
Business Mileage - this is the total business trip mileage that has been recorded for the particular vehicle during the user defined period based on the logbook entries on the Logbook sheet.
Business Travel % - this is the percentage of travel for business purposes associated with the particular vehicle. This calculation indicates the level of usage of the vehicle for business purposes and can be compared to the same calculation for other vehicles in order to identify excessive private use of vehicles.
Rate Charged - this is the average rate which has been charged to clients based on the use of the vehicle for business purposes and is calculated by dividing the total charges by the business mileage.
Business Trips - this is the number of business trips which have been allocated to the vehicle. If round trips are recorded as a single trip, the number of business trips would reflect round trips.
Total Mileage by Month - these three columns display the total mileage per month which has been allocated to the vehicle. The monthly reporting periods are determined by the To Date which is specified in cell I4. The calculations in these columns can be used to review the total mileage which have been allocated to each vehicle for the appropriate quarter.
Business % by Month - these three columns display the percentage monthly business use of the vehicle. The monthly reporting periods are determined by the To Date which is specified in cell I4. The calculations in these columns can be used to review the business use of the vehicle for the appropriate quarter.
Number of Business Trips by Month - these three columns display the number of business trips per month which has been allocated to the vehicle. The monthly reporting periods are determined by the To Date which is specified in cell I4. The calculations in these columns can be used to review the number of trips which have been allocated to the vehicle for the appropriate quarter. This is especially useful when you need to monitor sales representatives with fixed routes where each sales representative uses a separate vehicle.
Note: The total business & private mileage as well as the appropriate percentages are displayed in column C and D at the top of the sheet.
Note: The template calculations only include the first 200 vehicle registration numbers on the Vehicles sheet. If this default value is not sufficient, open the Name Manager feature (Formulas tab on the ribbon) and simply change the value of 200 which is specified for the VRecords named range to the required number of vehicle registration numbers which need to be included in the template calculations.
Filter Selection Options
All the calculations in the light blue columns on the Vehicles sheet are based on the filter selections which have been specified at the top of the sheet. These selections enable users to compile a vehicle charge sheet based on any user defined date range and for any individual client. The filter selections on the Vehicles sheet include the following user input cells:
From Date - the date range for which the calculations on the sheet are performed starts on the user defined date in cell G4. The date should be entered based on the regional date settings which are specified in the System Control Panel. If this cell is left blank, the date range will commence on the first day of the year that is five years before the current year (it can therefore be reasonably expected that all logbook entries for the past 5 years will be included in the calculations on the Vehicles sheet).
To Date - the date range for which the calculations on the sheet are performed ends on the user defined date in cell I4. The date should be entered based on the regional date settings which are specified in the System Control Panel. If this cell is left blank, the date range will end on the last day of the current calendar month (which is determined based on the current system date).
Client - the calculations on the Vehicles sheet can be performed based on a single client or all the client codes which have been added to the Clients sheet. If you want the calculations to be based on a specific client code, simple select the appropriate client code from the list box in cell K4. If you want the calculations to be based on all clients, simply delete the contents of cell K4 in order to display a blank cell.
Vehicle Logbook Form
The vehicle logbook form on the Form sheet can be printed and supplied to drivers in order for them to record all business & private trips. The contents of these forms can then be recorded on the Logbook sheet.
Note: An example of the input which is required in each column is supplied at the top of the sheet.
Note: We strongly recommend issuing these forms in a numeric sequence by changing the form number at the top of the sheet before printing the form. This will enable users to check the form sequence in order to ensure that all vehicle logbook entries have been recorded.
Error Codes
The following error codes may result from inaccurate input on the Logbook sheet and will be displayed in the Error Code column. The heading of the affected input column will also be highlighted in orange:
- E1 - this error code means that the vehicle registration number that has been selected in column D is invalid. All the vehicle registration numbers that have been added to the Vehicles sheet will be included in the list boxes in column D and the error can therefore be rectified by simply selecting a valid vehicle registration number from the list box. New vehicle registration numbers must be created on the Vehicles sheet before being available for selection.
- E2 - this error code means that the client code that has been selected in column H is invalid. All the client codes that have been added to the Clients sheet will be included in the list boxes in column H and the error can therefore be rectified by simply selecting a valid client code from the list box. New client codes must be created on the Clients sheet before being available for selection.
- E3 - this error code indicates that an incorrect client code has been specified for a private trip on the Logbook sheet. All private trips must be allocated to the default "XXX01" client code in column H on the Logbook sheet. The error can be corrected by simply selecting the correct client code ("XXX01") in column H or changing the type of travel to business and selecting the correct client code.
- E4 - this error code indicates that an incorrect client code has been specified for a business trip on the Logbook sheet. All private trips must be allocated to the default "XXX01" client code in column H on the Logbook sheet. If you use this default private trip code for a business trip, this error will be displayed. The error can be corrected by simply selecting the correct client code in column H or changing the type of travel to private.
Note: Input errors may result in inaccurate template calculations and it is therefore imperative that all errors are resolved before reviewing the template calculations.
