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 midsize 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.
Lifetime Carbon Emissions From Prius:
http://dailymathproblem.blogspot.com/2014/08/lifetimecarbonemissionsfromprius.html
http://dailymathproblem.blogspot.com/2014/08/lifetimecarbonemissionsfromprius.html
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