Driving Across Town for Cheaper Gas -- A Cost/Benefit Analysis
In this Spreadsheets Across the Curriculum activity, students are guided step-by-step to build a spreadsheet that estimates the real cost of driving out of the way for less-expensive gasoline. To better illustrate the modeling process, the module begins with the simplest case of factoring in only the extra gas consumed and is then expanded to consider not only the additional wear-and-tear expense, but also the non-monetary cost of travel time. This module should demonstrate both the power of basic mathematics to analyze authentic relevant scenarios as well as the ease at which this can be accomplished using spreadsheets.
- Perform a unit conversion.
- Format cells in an Excel worksheet.
- Use Excel functions to do the same calculations easily.
- Use the spreadsheet to model different price points.
- Gain experience with dimensional analysis.
- Gain experience with using rates and reciprocals of rates.
- Gain experience modeling real-world situations by considering and determining (a) relevant variables, (b) need for precision, and (c) model assumptions and limitations.
- Gain experience with using numbers and mathematical calculations to examine their opinions.
Context for Use
The module is meant to be the first step into real modeling (i.e., thinking beyond the basic textbook story problem). The module is given around the fifth week of classes after the students have completed sessions on applying linear, quadratic, logarithmic and exponential functions as well as systems of linear equations. The reason for the later placement is so students will have time to complete a series of short Excel skill-building assignments. This way, students can start on the module with full confidence in their spreadsheeting abilities. Alternatively, the module could be given to correspond with coverage of linear functions, but then additional time would need to be dedicated to the Excel portions of the module.
I prefer to have a discussion about gas prices and the cheap-gas reports prior to showing the students the module. I open the discussion by showing the students a cheap-gas website or a cheap-gas report from a recent copy of the local newspaper, and then I ask them if they've seen these reports and if anyone actually follows up on them (and how). Typically the conversation stalls with students either believing there is value in making a trip for cheaper gas or that the trip isn't worthwhile; significantly, students in the latter group are unable to provide evidence as to why it isn't worthwhile except for "I don't want to take the time." At this point, I give them the overview of modeling that appears on Slide 2 of the module and immediately follow that up by asking the students to identify everything they think may be relevant to making this decision (Students who already have a strong opinion regarding what they would do in this situation can be coaxed back to "neutral" by asking them to put this in business context where their boss would like actual data before making a choice).
After that introduction, I start to walk the students through the actual module. (They seem to like the fact that they have already brought up most if not all or more of the variables mentioned in the module). As we sort through the variables, the most natural question that arises is what information is actually available to us, which then leads us right into what assumptions are we going to make. For example, the fuel cost to make the trip is the first variable most people consider. Finding this cost depends on the distance we need to travel, the price we pay for the gas, and the fuel efficiency of the vehicle we're driving; hence, we need to establish values for each of these. I chose the numbers I used because (a) they are believable and (b) they work out nicely - which we then proceed to do. The great thing about this is that if students don't like my numbers, they can run their own numbers later.
As is often done in real-world problem solving, we have started our considerations with a simple case. So next we increase the complexity by incorporating the other variables. We discover that gathering the relevant information can become difficult (and possibly impractical) to find, thus requiring additional assumptions if we wish continue. We also find that the scale of the problem may render some variables irrelevant. Lastly, and building on the idea that this may not be worth our time, we take a look at the non-monetary costs (for many scenarios this road can easily lead to a discussion of ethics).
Although we only change gas prices in this module, I have set up the spreadsheet in a manner that allows one to alter distance traveled and wear-and-tear costs.
Description and Teaching Materials
SSAC2006.HE5614.GTF1.2-student (PowerPoint 284kB May18 09)
The module is a PowerPoint presentation with embedded spreadsheets. If the embedded spreadsheets are not visible, save the PowerPoint file to disk and open it from there.
This PowerPoint file is the student version of the module. An instructor version is available by request. The instructor version includes the completed spreadsheet. Send your request to Len Vacher (firstname.lastname@example.org) by filling out and submitting the Instructor Module Request Form (Acrobat (PDF) 91kB Aug20 12).
Teaching Notes and Tips
The instructor version includes a slide of questions that can be used as a pretest.