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.      

No comments:

Post a Comment