Vehicle Template - Google Sheets

[jpfieber]

Overview

The Vehicle is a gathering place for data related to your vehicle:

• A convenient place to store info such as year, manufacturer, model, VIN, License Plate, etc
• Tracking of the fuel you use, with statistics like average mpg, miles per month, cost per month, etc.
• An auto loan payment tracker showing your payment schedule, including interest/principal
• A maintenance tracker, with a schedule of what needs to be done and when
• Track the value of the vehicle over time
• Ability to link budget related information back into the Budget Plan template

It can also be used as an external source to provide fuel, maintenance and loan information about your vehicle for the Budget Plan template. The Vehicle template is designed to hold information for one vehicle, so you’ll want a copy for each of your vehicles (renamed so you can identify which is which).

Most of the features of this template will rely on you inputting more information about your vehicle related spending then you probably have been. Namely, fuel and maintenance information rely on knowing the ongoing mileage of your vehicle. The method I’ve used over time to track this is to write my odometer reading on my fuel receipt, and then adding the odometer and number of gallons purchased to the transaction when it appears in Tiller. Having the number of gallons of fuel, the total cost of the fuel, and the odometer reading for each of your fill-ups allow you to calculate lots of enlightening information.

Installation

Open the link to the “Vehicle Template” template below. On the “Vehicle” sheet name at the bottom of the screen, click the triangle and choose “Copy To”, then “Existing Spreadsheet” and select your Tiller foundation template. Open your Tiller foundation template, find the new sheet (it’s likely the last sheet) and rename it to represent the vehicle you’ll be tracking with it by clicking the triangle on the sheet name and choosing “Rename”. You should then add the same name to cell C3 to represent the ‘Nickname’ of the vehicle, which will be used as a tag to identify which transactions are related to this vehicle.

Vehicle Template

Requirements

This template needs information about your transactions that will need to be stored either in existing columns, or in new columns that you create. Learn more about columns here. Create any new columns first, then go back to the Vehicles template to select the column in the “Settings” section.

• You’ll need a column in your Transactions sheet to define which vehicle the transaction is related to. I use a column called “Tags”. You can choose from your existing columns in “Settings” cell AU24.
• You’ll need a column in your Transactions sheet to note the odometer/mileage at time of the transaction. I use a column called “Note”. You can choose from your existing columns in “Settings” cell AU26.
• You’ll need a column in your Transactions sheet to note the number of gallons of fuel you purchased. I use the existing column “Description”. You can choose from your existing columns in “Settings” cell AU28.
• If you use the Budget Plan template and want to import data from the Vehicle template, enter the name of your Budget Plan template in cell AU30 (eg. “Budget Plan 24”).
• If you use the Budget Plan template and want to import data from the Vehicle template, you’ll need to rename the Vehicle sheet to match the “Nickname” you put in cell C3.

This template is broken into a number of sections that can be opened and closed using the ‘+’ above the section label. Here’s an overview of the different sections in the template.

Vehicle Information

This is a place to get an overview of useful information about your vehicle. Some of it you enter manually (the cells with green backgrounds), the rest are calculated for you based on information available on this and other sheets in your template. Some, like “Nickname”, are used to make things happen in this template, while others, like the VIN or the Make/Model/Style, aren’t referenced elsewhere, and are just there as a handy place to look them up in the future. There are “Notes” on the cells that are required explaining how they are used.

Budget Plan External Ranges

If you use the “Budget Plan” template, you can use the values generated in the Vehicle template to generate budget information that can be input in Budget Plan using the “ExternalSource” frequency, where you then include the ranges defined in C29:31.

Value

If you create and maintain a manual account for your vehicle (a great way to ensure your vehicle’s value is considered in your net worth), you can track the value in the “Value” section, and see the current value in cell C10. Do this by selecting the manual account in the “Settings” section in cell AU22.

Fuel & Mileage

In order to track fuel and mileage info you’ll need to add three pieces of information to your fuel transactions:

• Gallons - Change the “Description” column (or an alternate column of your choice in the Settings) of your fuel transaction to contain the number of gallons purchased (eg. 10.6).
• Odometer - Change the “Note” column (or an alternate column of your choice in the Settings) of your fuel transaction to contain the odometer reading at the time of purchase (eg. 89428).
• Nickname - Change the “Tags” column (or an alternate column of your choice in the Settings) of your fuel transaction to contain the ‘Nickname’ (what you entered in cell C3 of the Vehicle template) of the vehicle being tracked in this template.

In the “Settings” section of the Vehicle template, choose which of your categories is assigned to fuel expenses (AU5). With this information added, the Vehicle template will locate all those transactions and populate the statistical information “Vehicle Information” box on the left side of the template.

Service Items

When I had an older car, I endeavored to keep track of the maintenance that was required on it since I did much of it either on my own, or by piecemealing out to local shops when I determined it was needed. I’ve since purchased a newer vehicle and get most things done at a dealer on the schedule that they set, so I don’t so much use this section anymore, but maybe it will still be useful for some. Typically the oil change is the most common service you’ll have done, and other service items would be done at the same time. Based on this assumption, this template establishes “Service Periods” which are multiples of how often you have your oil changes. I have grouped my service items into multiples of 1, 3, 6, 12 and 24 (eg. 3 means a service is done every 3rd oil change). Since I’ve set my Service Interval to 6 months/6000 miles, and only average 526 miles per month, the time interval wins, so my service items should be done based on time intervals of 6 months, 18 months, 36 months, 72 months and 144 months. If I drove more than 1000 miles per month, then it would be based on mileage, with intervals of 6000, 18000, 36000, 72000 and 144000.

If you hit 36000 miles, you aren’t just getting the service items for that service period, you’re also getting all the items from the first period, and since 36000 is divided evenly by 18000, it’s time for your second period items as well (they should happen every 18000 miles).

You can set the “Service Interval” in the “Settings” section, cells AU15 (time) and AU17 (miles), and cell AU19 will show which will be used based on the existing data it can gather… In the “Service Items” section, you then fill in the green cells for each “Service Item” you require. I’ve included some common ones you can use as a starting point. The information you should include for each is:

• Service Item: The name of the service to be done. You should then split any transactions related to this vehicles service, and change the description to match the service item name you use here.
• Mult: How many oil changes between services for this item? For example, if you replace your air filter every three oil changes, set the Mult to “3”.
• Budget: How much do you anticipate this service item will cost? This will be used to feed your AutoMaintenance budget estimate that can be used in the Budget Plan template.

Select which category applies to your Auto Maintenance in “Settings” using cell AU8 so this template can find the corresponding transactions

When you get a transaction related to auto service, you should do the following:

• Assign the Auto Maintenance category you chose in “Settings”, cell AU8
• Add the vehicle nickname to the column you chose in “Settings”, cell AU24
• Split the transaction so each service item has it’s own line, this can be tricky as you’ll have to go some guestimating when it comes to taxes, fees and labor that may apply to multiple service items.
• Once split, change the descriptions of the service items to match those in the “Service Items” section (eg. “Rotate Wheels & Tires”).

Service Periods

Nothing to change here, this is just a handy table to show which Service Items need to be done at each service period, based on mileage/time.

Loan Payment

Since this template is all about your vehicle, I figured adding a place to track it’s payments might be helpful to some. Enter appropriate values in the green cells in the top section and you’ll get a loan chart, showing your payments over time and how much is principal vs interest. Within the table, if you pay more than the base amount, enter it in “Additional” (column AP) to adjust the rows below to factor this in. You can also select which of your categories is for your auto loan in “Settings”, cell AU11.

External References

If you plan to export data from this template to be used in your Budget Plan template, this is where the data will come from. The range you’ll enter in Budget Plan is shown C29:31 as mentioned above.

Helper Data

This is where some of the magic happens that you generally don’t need to worry about unless there are problems and you’re asking for assistance in the forum.

Let me know if any of this is unclear or if you have any problems and be sure to vote at the top of the post if you find this template useful! If there is interest, I may migrate this to Excel as well.

[twalane]

Such great detail in the documentation… I continue to review but wanted to jump in and say this is a really cool share @jpfieber !..