Friday, August 22, 2014

Fuel expenditures for two SUVS -- a simulation.

Daily Fuel Expenditures in the Honda CRV versus Subaru Cross Trek

Question:   Daily city mileage for a taxi driver is normally distributed with mean 120 miles and standard deviation 15 miles.  Daily highway miles for a tax driver is normally distributed with mean 50 miles and standard deviation 15 miles.

The EPA fuel efficiency estimates for the two cars are presented below.

EPA fuel mileage for two SUVs
Subaru XV Crosstrek
Honda CRV

The price of gasoline is uniformly distributed between $3.50 for $4.50

Use Excel simulation tools to estimate the average and the standard deviation of fuel expenditures for this taxi driver if he uses a Subaru CrossTrek and a Honda CRV.

Explain how the simulation is conducted. 

Analysis:   The Excel Function NORM.INV is used to generate a vector of city and highway miles.

City miles = NORM.INV(RAND(),120,15)

Highway miles = NORM.INV(RAND(),50,15)

To get city gallons divide city miles by city mpg.

To get highway gallons divide highway miles by highway mpg.

Do this for the Subaru and for the Honda.

Add city and highway miles together to get total miles.

Generate a random gas price with the rand() function

RGP (Random gas price) = $1 x rand() + 3.50

Multiply RGP by total gas consumed for the two cars.

Make many observations by copying the first line.

Note Rand() resets each time the spreadsheet is open so your actual numbers will differ from mine.  I hope that if large samples are generated differences in summary statistic will be small.

First line of Simulated Database
City Miles
Highway Miles
Subaru City Gallons
Subaru Highway Gallons
Total Subaru Gallons
Honda City Gallons
Honda Highway Gallons
Total Honda Gallons
Gas Price
Gas Expenditure Subaru
Gas Expenditure Honda

Below are the daily average and the daily standard deviation for the two SUVS given the daily variation in amount driven (both city and highway) and the daily variation in gasoline price.

Simulated Results For Average Daily Gas Expenditure for
Subaru and Honda SUV Taxi Cabs
Standard Deviation

Concluding Remark:  The purchase of the more fuel-efficient vehicle reduces average daily expenditures and the variability of expenditures on gasoline.    The results presented here are just one simulation based on one set of assumptions.

In some ways the vehicle miles traveled on highways assumption is unrealistic.  Some people do not often travel on highways but when they do they travel large distances.   STD=15 for highway vehicle miles traveled is very small but it would be wrong to just increase this number because it would result in many people having negative miles.  Negative miles is not realistic, true.

See the appendix to this blog for a discussion of this issue.

An Appendix to the two SUV Simulation

No comments:

Post a Comment