## Tuesday, September 2, 2014

### Modeling fuel use in multi-vehicle households.

Question:  The fuel consumed in a household with two vehicles depends upon the fuel efficiency of both vehicles and the amount each vehicle is driven both in the city and on the highway.    Create a spreadsheet that will calculate the amount of fuel consumed by a household with two vehicles under a wide range of assumptions on vehicle use and vehicle fuel efficiency.

Use this spreadsheet to calculate fuel consumption in gallons, fuel expenditure in dollars and household fuel efficiency for a household that owns both the Honda Pilot and the Toyota Prius.   Base your analysis on the following assumptions on vehicle use and vehicle fuel efficiency in both the city and on the highway.

 Input Usage Assumptions: Total Miles Traveled 30000 30000 30000 % of Miles Traveled In Car 1 0.25 0.5 0.75 % Car 1 Miles in City 0.7 0.7 0.7 % Car 2 Miles in City 0.8 0.8 0.8 Fuel Efficiency Assumptions EPA City MPG Car 1 17 17 17 EPA City MPG Car 2 51 51 51 EPA HWAY MPG Car 1 24 24 24 EPA HWAY MPG Car 2 48 48 48

The analysis assumes use Pilot and use of Prius ranges from 25% to 75%.   The analysis assumes slightly higher use of Prius in the city.    There is a greater tendency to take the larger car on longer trips.

Analysis:   First use the estimate input on total miles driven, percent of miles driven for each car and percent of car miles that are city miles to get miles driven in city and highway for each car.

Note the following:

The proportion of miles driven in car 2 is 1 minus the proportion of miles driven by car 1.

The proportion of HWAY miles for car 1 is 1 minus the proportion of city miles for car 1.

The proportion of HWAY miles for car 2 is 1 minus the proportion of city miles for car 2.

Multiply relevant proportions by 30000 miles to get mileage estimates by car (city/HWAY).

Divide the four mileage figures (car 1 city, car 2 city, car 1 HWAY, and car 2 HWAY) by the relevant fuel efficiency figures to get gas consumed in gallons.

Sum to get total gas consumption.

Divide total miles (30,000) by gallons of gas consumed to get household fuel efficiency.

Multiply gallons of gasoline purchased by \$4.00 gas to get total household expenditures on gasoline.

Here are the results for the assumptions that I have placed above.

 Output Mileage Output City Car 1 5250 10500 15750 City Car 2 18000 12000 6000 HWAY Car 1 2250 4500 6750 HWAY Car 2 4500 3000 1500 Sum of Four 30000 30000 30000 Check Sum of Four=Total 1 1 1 Fuel Efficiency Output Gas City Car 1 308.8 617.6 926.5 Gas City Car 2 352.9 235.3 117.6 Gas HWAY Car 1 93.8 187.5 281.3 Gas HWAY Car 2 93.8 62.5 31.3 Total Gas 849.3 1102.9 1356.6 Household Fuel Efficiency 35.3 27.2 22.1 Fuel Expenditures at \$4.00 Gasoline \$3,397 \$4,412 \$5,426

Comments:   Household fuel efficiency over the three scenarios varies from 22.1 to 35.3   Gas consumption varies from \$3,397 to \$5426.    The household with both a Prius and a Pilot can substantially alter fuel consumption and expenditures on fuel by utilizing one car more than another.

A household with two identical cars for instance two Accords will not be able to alter driving patterns to save money.  This analysis suggests that an engaged couple with two mid-size sedans might be better off selling both sedans and getting a larger car and a hybrid.

Of course fuel costs are only part of the puzzle.  A Toyota Corolla is a lot cheaper than the Toyota Prius; hence a person who is concerned about total costs might purchase the Corolla over the Prius.   See my previous posts on this topic.

I am planning to use this model to assess the economic wisdom of a wide variety of household vehicle ownership and utilization combinations.   More will follow over the next week.