Introduction
Introduction
Gas prices continue to rise while the United States uses about 400 million gallons of gasoline a day. Every year our cars release over 1.7 billion tons of CO2 into the atmosphere contributing to potential global warming. And because of high gas prices it makes sense to find a way to use less gasoline in our automobiles. Hybrids, cars that supplement the standard, gasoline engine with another form of power, usually with an electric, battery powered engine, are an example of new technology that allows cars to get better gas mileage, thus diminishing our dependency on foreign oil. But the initial cost of a hybrid car is more expensive than the initial cost of same car in the conventional gasoline model.
Hybrid Technology
https://www.altenergymag.com/article/2005/06/introducing-hybrid-technol…
Conventional Gas
https://www.nyserda.ny.gov/All-Programs/Programs/Drive-Clean-Rebate/Abo…
Does the savings at the pump when using hybrid cars offset the lower purchase price of conventional gas cars that use more gas?
Task
Task
Someone in your family is ready to purchase a new car and you think it is more cost-effective to buy a hybrid or a gasoline car. Is it though? Based on the data found on the Internet you will also explain whether it is less expensive to drive a car in the winter or summer, and why.
Students will learn about hybrid car technology and determine how many years it will take to break even when purchasing a more expensive hybrid while saving money each year using less gasoline versus saving money when initially purchasing a conventional car that uses more gas. Students will be placed into groups of two and each student will choose one model car from the list. Each student will project the future price of gas by calculating the slope of a line in Excel based on gasoline price data from 2000-2009. From this data you will find the slope using a graph in Excel and project the future price of gas for the next 15 years. Calculate what it will cost to operate each car for those 15 years based on each car traveling an average of 20,000 miles per year. The student will find the difference in the purchase price between the hybrid and conventional gas car. The student will then calculate the dollar amount saved in gas with the hybrid car over the gas car. Next they will use a Excel to calculate how many years it will take for the difference in gas savings to offset the more expensive hybrid car initial cost.
After all the calculations you will graph the results, copy the information to a word document for your family member and make your recommendation whether it is more cost-effective to purchase the hybrid or gas model car.
Process
Process
Step 1.
Watch the following video to help you understand what exactly a hybrid car is and discover the difference between a hybrid car and an all gasoline car.
http://www.youtube.com/watch?v=aW4GjTTHoGc&feature=related
Step 2.
Watch these two videos to learn the process of how gasoline is made from crude oil drilled from the earth.
http://www.youtube.com/watch?v=Hxoyzs9sWtc&feature=related (part 1)
http://www.youtube.com/watch?v=byOq1nCiZt4&feature=related (part 2)
Write a paragraph describing the process required to go from crude oil to gasoline. This will be used in step 10.
Step 3
Divide the class into groups of two students. Each student will graph the price of gas for the last 10 years and determine the slope to project what gas will cost for the next 15 years.
To graph the price of gas from 2001-2010 go to http://data.bls.gov/cgi-bin/surveymost?ap Check ‘Gasoline, Unleaded Regular’ and then click the Retrieve data box at the bottom of the page.
Copy the chart data from 2001 to 2010 including the ‘Year’ row and ‘Annual’ column.
Open Excel and paste this data into cell A3.
Find the average price of gas for each year. The results should be in the ‘Annual’ column and have two decimal points. (Format Cells)
Using the same data find the average cost of gas for each month. These results should be in a row under the 2010 row. The averages should have two decimal points.
Step 4.
Each student will graph the average gas price for each month.
Make sure you label the line graph.
If you need help graphing in Excel please visit this website. http://www.youtube.com/watch?v=J-PaQymHkhg&feature=related
What do you notice about price of gas throughout the year? Why is this? Does your data confirm what this website explains.
http://auto.howstuffworks.com/fuel-efficiency/fuel-consumption/summer-f…
Copy the graph to a Microsoft Word document. And write a paragraph explaining the results of your graph.
Step 5.
Each student will complete this step.
Graph the average annual gas prices from 2001 to 2010 by selecting the year column and the average annual price column while holding down Ctrl. Make a graph using XY (Scatter) graph.
Then right click one of the plotter points and select 'Add Trendline' and use the linear option to determine the slope of the data. The slope of this line is (y2-y1) / (x2-x1). Estimate the gas prices for year 2001 and 2010 and use these numbers in the slope equation. Type Slope in cell A16. Use Excel to calculate your answer in cell A17. (Remember to always start a formula with an '=' sign.) Your answer should have two decimals places.
If you need help with formulas please refer to this website.
http://spreadsheets.about.com/od/excelformulas/ss/formula_begin.htm
Step 6.
Each student will complete this step.
Use the slope, from Step 5, to determine the average gas price for the next 15 years (2011-2025).
In cell A20 type Years. In cell B20 type Gas Price. In cell A24 type 2010 and continue down until you have finished with year 2025. In cell B24 estimate the price of gas for year 2010 from the graph (trendline) in step 5. Then calculate the price of gas for the next 15 years using the slope value. This can easily be calculated using a formula.
Step 7.
Each student will complete this step.
CAR 1: Ford Fusion Data
Ford Fusion Hybrid:
http://www.edmunds.com/new/2010/ford/fusionhybrid/101141447/prices.html
Ford Fusion Gas Powered:
http://www.edmunds.com/new/2010/ford/fusion/101138067/prices.html
CAR 2: Lexus Mini SUV Data
Lexus Mini Hybrid:
http://www.edmunds.com/new/2010/lexus/rx450h/101162468/prices.html
Lexus Mini Gas Powered:
http://www.edmunds.com/new/2010/lexus/rx350/101142322/prices.html
In cell C20 type Hybrid Car and in cell D20 type Gas Car for conventional car.
In cells C21 and D21 input the purchase price (MSRP) of each type of car.
In cells C22 and D22 input the miles per gallon (mpg) for each type of car.
In cell E20 type Cost Diff. for the difference in purchase price between the hybrid and all gas car. In cell E21 calculate the difference using Excel.
Determine how much money it will cost to own the car each year from 2011 to 2025 using the formula money spent on gas=(avg. gas price for each year * 20,000) / the car’s miles per gallon + the original cost of the car. 20,000 is the average miles a car is driven each year.
Put the original price of the each type of car in cells C24 and D24.
Start in cell C25 for year 2011 and calculate how much is spent by the hybrid car on gasoline until year 2025. Because you purchased the car on Dec. 31, 2009 you will use no gas in 2009. The cost to use the Hybrid in 2010 is the original cost of the car plus the dollar amount of gas used for the year. The total for 2011 is the original price of the car plus the amount of gas used in 2010 and 2011. Do the same for the all-gas car starting in cell D25.
Step 8.
Each student will complete this step
You are close to having enough information to make an informed recommendation to your family member.
In cell F20 type Amt Saved on gas. This will be the cumulative amount saved on gas by the hybrid car.
In cell F24, subtract the Cost Diff from the original difference in purchase price to get the amount saved in gas. The results should be in column F.
In cells G24 to G39 copy the amount you calculated in cell E21. This will be the same number.
Step 9.
Each student will complete this step
You will graph columns F and G for 15 years (2010 to 2024). Be sure to have titles for each column in row 23. Use a scatter graph to determine what year the amount saved on gas and the cost difference of the two models cost the same to own. Copy this graph to the Word document. If your family member purchases a new car every few years what kind of car should your family member buy? What type of car should your family member purchase if they plan on keeping a car more than 10 years?
Copy this graph into your Word document.
Step 10.
Make sure your word document has the following:
A picture and the name of the car you would recommend, either the hybrid or the conventional gas car with the purchase prices and gas mileage.
A paragraph explaining the process of how gasoline is made (from the ground to your car).
A graph showing the average price of gas from 2000-2009 and a paragraph explaining your data.
The graph showing what year the hybrid and gas cars cost the same to operate.
A paragraph explaining why you recommended the car you chose.
Evaluation
Evaluation
Using Excel
Beginning
The data was copied from the website but no formulas were used and tables were not complete.
Developing
The data was copied from the website but only some of the formulas were used and tables were not complete.
Accomplished
All the correct data are in the right cells and all the formulas work. The data is presented in a way that is difficult to read.
Exemplary
All the correct data are in the right cells and all the formulas work. The tables are clean and easy to read and labeled correctly.
Score:
Paragraph expaining the process of refining oil
Beginning
The paragraph has less that 5 sentences, incorrect grammar and spelling and describes the process incorrectly.
Developing
paragraph has less that 5 sentences, correct grammar and spelling and describes the process correctly.
Accomplished
The paragraph has 5-7 sentences, desribes the process correctly but has some grammar and spelling errors.
Exemplary
The paragraph has 5-7 sentences, the correct grammar and spelling and desribes the process correctly.
Score:
Graph 1:
Monthly Gas Prices
Beginning
Graph does not have the correct data and is not labeled.
Developing
Graph does not have the correct data but is labeled correctly.
Accomplished
Graph has the correct data and labeled correctly.
Exemplary
Graph has the correct data, colorful and labeled correctly.
Score:
Paragraph explaining the data from Graph 1
Beginning
The paragraph has less that 5 sentences, incorrect grammar and spelling and describes the data from graph 1 incorrectly.
Developing
The paragraph has less that 5 sentences, correct grammar and spelling and describes the data from graph 1 correctly.
Accomplished
The paragraph has 5-7 sentences, desribes the data from graph 1 correctly but has some grammar and spelling errors.
Exemplary
The paragraph has 5-7 sentences, the correct grammar and spelling and desribes the data from graph 1 correctly.
Score:
Graph 2:
Graphing the break-even point owning a hybrid vs an all gas car
Beginning
Graph does not have the correct data and is not labeled.
Developing
Graph does not have the correct data but is labeled correctly.
Accomplished
Graph has the correct data and labeled correctly.
Exemplary
Graph has the correct data, colorful and labeled correctly.
Score:
Flyer in Word recommending the hybrid or all gas car.
Beginning
The flyer has a few of the required elements.
Developing
The flyer does not have all the required elements but is clear and professional.
Accomplished
The flyer has most of the required elements but is difficult to read.
Exemplary
The fly has all the required elements and is clear and professional.
Score:
Conclusion
Conclusion
Congratulations! Hopefully your family member will listen to your recommendation and enjoy his or her new car. Although there are many more factors when purchasing a new car than just purchase price and miles per gallon, this assignment hopefully taught you how to understand certain data that is important in such a big purchase.
Credits
Credits
Thanks to:
The United States Department of Labor for the gas price data
edmunds.com for all the car data. http://www.edmunds.com/
about.com for any reference material needed for Excel and graphing
youtube for the video describing how hybrids and oil refining works
www.fueleconomy.gov for the animation describing how a hybrid car works
Teacher Page
-
Thankyou and Godbless😊