Friday, June 19, 2009

I Do Not Have a Spreadsheet Problem

Hi everyone, you can find me over at MIT Mommy today for a guest post on hypermiling.  I originally had more screen shots of my car spreadsheets, but then I realized that people might think that I was a wee bit obsessive.  But on my own, blog, I have no issues parading my obsessive organization.  Here's what made the cutting room floor...

Ever since the days of Lotus 1-2-3 (for those that remember the DOS days), I have kept spreadsheets for every car that I've owned.  I track every tank of gas, and chart gas mileage, odometer miles per year, and most recently price per gallon.  Here a glimpse into my insanity.

When I first show people my spreadsheets, they look at me like I'm crazy.  Then I show all the cool things that I learn from them.  I know that our exceptionally green Toyota Echo gets 40-45 mpg, depending on the season (winter 40, summer 45), even at 157k miles.  When gas prices were around $2 per gallon, it costs 4.5 cents per mile in gas to drive.  However, my all wheel drive BMW 325xiT gets 23-27 mpg, but including the extra cost of premium gas, it costs 8 cents per mile, nearly double!  With this price per mile information, I can accurately predict how much a trip will cost in gas money depending on the car.  Going 200 miles?  $9 for the Echo and $16 for the BMW.  Since these cars do not hugely differ in capacity, that is the difference in cost for performance, comfort and vanity.  I also track service costs in another tab as well, but haven't made the calculations for true cost per mile (mostly because it would be embarrassingly higher for my beloved BMW).  The chart also lets me know if there is a problem with my car.

BMW gas mileage

You can see the seasonal ups and downs for gas mileage. The plus symbols are the actual fill-ups (240 since I've owned this car) and the pink line is the running average of the last four fill-ups.  Right around October of 2005, there was an unusual dip.  This is where my brake calipers were dragging and needed to be replaced.  The spreadsheet clued me in before I could feel the difference in driving.

Another cool chart is the odometer mileage per year, which is an automatically updating column in my spreadsheet.  Here's the chart for the Toyota Echo.

Echo miles per year

You can see that when Doug used this car in 2004 and 2005 for his 94 mile round trip commute, we nearly hit 24k miles per year.  Long trips show up as a slight increase in slope.  Even though Doug only uses this car nowadays to chauffeur the kids around, we still use it on most road trips because of the price per mile advantage stuck in our heads.  This chart is a map of the history of the car (here I am reminiscing over a spreadsheet).

Some people think that I spend way too much time with my spreadsheets (I have another for expenses).  But for the cars, it is really not much work since they are set up and update automatically.  For every fill-up, I jot down the the odometer reading on the receipt.  When I get home, I enter the date, gas brand, odometer reading, gallons, and price per gallon.  The rest is spreadsheet magic.  Even Doug is completely onboard for recording the mileage for his cars and motorcycles (I've done some good brainwashing, eh?).

When gas prices skyrocketed last year, I realized that I could create my own chart of gas prices because I had years of data!  Here is the price per gallon for regular gas in New England over the past 8 years.

Gas prices

From this chart, you can see that in December 2008, gas prices returned to their lowest levels since 2003, but they are creeping back up again. Of course this does nothing to stop the prices, but at least I'm watching them.  Obsessively.

9 comments :

Parlancheq said...

OK, that is COMPLETELY anal. But cool, nevertheless.

Badass Geek said...

See? I love this. I would do the same thing, if I would remember to write down the data each time.

Karmyn R said...

You are funny....

I kept track of all that information with my very first car...but never thought to put it into spreadsheet form.

When I worked, I used Excel on a daily basis - I even used it to make spreadsheets for my wedding (#guests - all the money spent, etc...) but I never thought to put my car data in there.

Asianmommy said...

Wow--amazing info!

Kristen M. said...

Wow, I'm blown away by your spreadsheets. Numbers are not my thing. I guess that's why you are the engineer and I'm not!

karen said...

This is SO COOL!!!

Kathys said...

Am simply amazed by your work! It's actually easier to understand numbers when you show them in graphs. Really appreciate it!
I go on road trips pretty often and I always hire a car. So I don't have to deal with the numbers!

MommyTime said...

This is awesome. If only I weren't so bad at all things diligently meticulous, I would do this too. I instead operate on gut feelings and guilt (oh, oil change overdue...etc) when it comes to my car. Which is precisely why I don't deserve to drive a luxury vehicle. :)

karen said...

So are both drivers meticulous about writing down the appropriate numbers at the gas station each. and. every. time? Back in the day before I got married I kept a little notebook in my car with all the data related to fueling up, but as soon as my husband started taking the car to the gas station (bless his heart for being so nice), I quickly realized that my notebook will never be kept again. At least by him. *sigh*