Fuel Economy Spreadsheet

The Fuel Economy spreadsheet is designed for use with standard Microsoft Excel (for any version of Windows), and Pocket PC Excel. Pocket PC Excel is a subset of the standard Microsoft Excel, and only the subset features are used in this spreadsheet
A new row needs to be created each time fuel is added to the tank, use copy and paste on the last row (Pocket PC Excel does not support macros). The intention is that you use it every time you refuel, and that you will fill up at least once every few refuels. Keeping track of your fuel consumption is a useful diagnostic for your car's health.
Enter data only in the columns with light blue backgound in the heading. Light yellow headings are optional data entry
Fuel consumption figures are calculated on the main sheet. The Reports worksheet shows some examples of additional queries that you can use and/or adapt to your purposes.
Download ready to use spreadsheet from here: http:\\condellpark.com\4wd\fuelEcon.xls
Download a sample with data already entered: http:\\condellpark.com\4wd\fuelXtrail.xls
Features:
* stores details of all refuelling stops, including ones where tank is partially filled.
* accumulates litres used and km travelled if tank is not filled
* calculates fuel consumption in litres/100 km and miles per gallon from last tank fill, when tank is filled
* calculates cents per km (average fuel cost)
* calculates a running average of recent l/100km and mpg (recent is by default last 5, but you can vary it)
* estimates date next service is due, using average km/day rates and service km target that can vary as time goes by
* estimates date fuel tank will be empty, using average of recent consumption rates and average km/day
* tracks progress against fbt km targets (Australian Fringe Benefits Tax)
Getting Started:
Calculations in each row use the speedo reading from the previous fill to work out km travelled.
The first fill will not have a previous reading, so fuel consumption can not be calculated for it. Enter the date, but you need not bother entering amt spent, rate (cents/litre). Set filled to 1.
For the 2nd and later rows, simply cut and paste the previous row, then overwrite the data entry fields.
Some values are averages of the last 5 entries, and will not be calculated until there are sufficient previous rows (the number of entries averaged over can be changed)
Data Entry Fields:
date each time you refuel, copy the whole of the last row, paste in the next row, edit the date, amt, rate, speedo, filled and remarks data
amt cost in $ of fuel
rate cost per litre of fuel, in cents
speedo speedo (odometer) reading at time of fill
filled set to 1 if tank completely filled (to consistent fill point)
locn code for petrol station, Woolies discount starts with W
all city 1 if all driving done in city conditions (no highway)
% a/c percentage of time air conditioner is on (estimate, round to nearest 20%)
remarks notes as to anything special about fill, eg. if long country drive
$us/barrel cost of oil in $US per barrel (optional: only needed if you want to check if whether your bowser price is related to world oil price)
Calculated Fields:
l litres of fuel in fill (check against docket to help verify data entry !)
km km travelled since last refuel
km since full km travelled since tank filled
l since fill litres of fuel added since tank filled
km/l fuel consumption in kilometres per litre since last full tank (easier when working how far you go per tank)
l/100km fuel consumption in metric units of litres per 100 km (since last full tank)
mpg fuel consumption in imperial miles per gallon (since last full tank)
$ since full dollar cost of fuel purchased since last full tank
cent/km cents per km for travel since last full tank
lt litres (long term) litres of fuel purchased since first record ( I usually hide this column)
lt av. l/100km long term average fuel consumption in litres per 100 km, over period since first record in spreadsheet
last 5 l/100km short term average fuel consumption in litres per 100km, over period of last 5  refuels. Could choose a lower range, but if occassionally don't fill tank, lower refuel range not so good. You can change the number of records averaged over (goto entriesAveraged )
last 5 mpg short term average fuel consumption in imperial miles per gallon (over last 5 refuels)
car name of car, in case you have multiple worksheets for different cars
min fc minimum fuel consumption (mpg) in spreadsheet, for comparing current value with, if you think in l/100 km, edit the formula !
max fc maximum fuel consumption (mpg) in spreadsheet, for comparing current value with
km/d average km per day since last full tank
targ km target km at which next service is due
srvc days days until the next service, assuming average km per day continues
srvc on date expected for next service, assuming average km per day continues
empty km km expected that tank will be empty, assuming fuel consumption average of last 5 refuels
fill km km  expected that tank should be filled, assuming a 10% of tank capacity reserve, and fuel consumption average of last 5 refuels
days petr no of days before petrol should be filled
fill day date on which petrol should be filled
fbt km/wk km/wk need to do to meet the fbt km target
act km/w actual km/wk over last refuel
fbt reqd km odometer reading required to stay on fbt target
fbt shortfall difference fbt reqd minus current odometer reading
FBT Entries (made yearly, or when adjustments required):

see also later explanation of FBT Table
fbt km adjust any extra km to be added to fbt end to account for long trips or long absence of weekly km (-ve value reduces fbt end), and/or reductions due to end of lease or out-of-service periods
Configuration Data (set it once at start of spreadsheet use):
fbt annual km number of km needed per fbt year (see also FBT Table)
fuel tank capacity litre capacity of fuel tank
fill when tank is % empty used tank capacity percentage at which you want to refill (best if less than 90%, 100% is empty)
max possible entries to average over 5 is default, but you might want to average over more or less depending on your style of use. Should be at least 2. If you choose a number larger than available entries, it is automatically limited to available entries.
Configuration Data (you won't need to update):
lower lower threshold for finding average of fuel consumption (filters out out-liers)
upper upper threshold for finding average of fuel consumption (filters out out-liers)
l/gallon constant for converting litres to gallons
km/l to mpg constant for convering km/l to mpg
km/mile constant for converting km to miles
entriesAveraged number of entries to average, including last fill, in recent consumption figures; usually 5 is a good number
Fill Types:
filltype desc  
0 not filled either this or previous fill  
1 caltex or ampol, includes non-city driving  
2 woolies bass hill, includes non-city driving  (actually the one identified in cell PetrolStn)
3 other woolies, inclues non-city driving  
4 not used  
5 caltex or ampol, all city driving  
6 woolies bass hill, all city driving  (actually the one identified in cell PetrolStn)
7 other woolies, all city driving  


 
FBT Table:
Provides data needed to check progress against km targets, for example, as needed for FBT requirements.
       
Each row represents a start and end period. FBT km targets have to be met at the 1st April each year,        
and possibly at the end of lease period. Where a vehicle lease is started on a day other than 1st April,        
the km target is pro-rata adjusted for the days left in the year. Similarly, if it ends, it is pro-rata adjusted.        
Each row calculates the pro-rata target required for the end of FBT year, or end of lease.        

start date
start date of lease, or 1st April of a given year (whichever is later) entry required
end date
end date of lease, or 31st March of a given year. entry required
fbt st km
odometer reading on first day of fbt year (not likely to be exactly same as end km of previous period) entry required
total days
days in FBT period
calculated
non avail days
any non-available days (e.g. panel shop and other repairs > 1 day) entry required (if any)
net days
total days less non-available days (this is use to get pro-rata amount of km) calculated
km reqd
odometer target for last day of fbt year (pro-rata'd to account for net days < 365 and fbt_annual_km)
calculated
km/day
km per day. Check figure - should remain constant, unless you make a data entry mistake or change annual FBT km etc.,.
calculated



Last Revised 4th April 2006, V1.2