Quantitative Reasoning and Technological Literacy I Activity 1:  Trendlines

Please do the following at the beginning of every computer activity.

a. Open a new Word document.

b. Click on the Office Button in the upper left corner, then slide over "Save As".   Choose either "Word Document" to save your document as a Word 2007 document or "Word 97-2003 document" to save your document in an early version of Word.  If you are not sure which to choose, you should use "Word 97-2003 document".  Save the document to the desktop by setting the "Save in" textbox to "Desktop". (Saving to the desktop makes it easy to retrieve your work when you are finished.)  Your file name should be entitled something like "Group Activity 1".  Last, confirm the save as type in the last box.  (*.docx) is the suffix for a Word 2007 document.  (*.doc) is the suffix for early versions of Word.

Learning Goals for this Activity

• You will learn how to add a trendline to a data series in Excel and use it to make predictions.
• You will become aware of the limitations of mathematical models, especially the limitation of extrapolation.

Activity

1. Open the file CarsAndTrucks_By_Year1970-MostRecent.xls contains data from the Bureau of Labor Statistics number of cars and small trucks on the road from 1970 through 2008.

   a. Make an X-Y scatter plot of the data including the trendline and R-squared value. For instructions click here.  Note that Excel will, in most cases, put a legend on your graph by default.  When there is only one data series (as here), you don't need a legend, and it really should be removed.  Paste this chart in your Word document. 

   b. Predict how many cars and trucks there will be in the US in the year 2013 using the regression equation in Excel.   Please note that equation that Excel puts on the graph can be rounded to very few decimal places; this rounding can lead to aberrant results.  Type your prediction (expressed in the appropriate units) into your Word document.

   c. You can double check your prediction in c using by visually extending the trendline you made in part b. Here is how you do this: Right click on the trendline and then choose "Format Trendline."  Then click on the Options tab.  In the center you will find a box entitled "Forecast."  In the "Forward" box, enter 5 to predict the number of passenger cars and trucks there will be in 2012.  Reading the graph, you can get an approximate prediction that should be very close the prediction you got with your equation. Paste the graph with your extended trendline in your Word document. 
   
   Visually extending the trendline is a good check on your algebraic calculations, and it is highly recommended as a check.  Occasionally, the rough, approximate prediction one can make from the extended trendline on the graph is sufficient to the task at hand; however, most often, we use the predictions obtained algebraically from the equations. All the extended trendline indicates is that you have done your math correctly - it does NOT give you confidence in the prediction itself.

   d.  How much confidence do you have in the prediction you made with your model?  Use the language that we discussed in class. 

   e. Estimate when there were no passenger cars and trucks in the US. Use the equation that you included on your graph to calculate an exact year. Type your result in your Word document.  You can also extend the trendline "Backward" (see part c) to double check your math. 

   f. About when were cars actually invented?  (If you don't know, do an Internet search.)   In light in the facts about when cars were really invented, how accurate was your model in predicting when there were no cars?  Explain why the model performed so badly even though it fits the existing data rather well. Why was the model helpful in making your first prediction, but not helpful here?

   g. Predict the number of cars in US in 2050.   How much confidence do you have in your prediction? Explain why.

2.  Open the file Coal_Consumption1970-MostRecent.xls containing data from the U.S. Energy Administration.  The unit of measure here is quads, a standard measure standing for quadrillion BTU's. (A British Thermal Unit is a unit of energy, the amount of energy needed to raise 1 pound of water 1 degree Fahrenheit when the water is about 39.2 degrees Fahrenheit.)

a. Make an X-Y scatterplot of the data. Add a trendline, including the equation of the line and the R-squared value. Paste this chart into your Word document.

b. Predict coal consumption in the year 2010 using the regression equation and double check your prediction by extending the trendline on the graph.  Type your answer and paste your graph in you Word document.

c. Using the equation of the model, predict when coal consumption reach 30 quads?

d. In a short well-written paragraph, discuss social, political, or physical changes which might affect the accuracy of predictions using this model. (It may be a good idea to first determine what coal is used for if you do not already know. This can be done with a quick internet search.)

3.  Linear models occur frequently in real settings not accompanied by data.  For each of the described setting, give a formula for the corresponding linear model

a.  A calling card offers a price of $0.23 cents per minute at any time during the day with a monthly service charge of $1.00.  Express the monthly cost as a function of minutes called.

b. At 0 degrees Celsius, the speed of sound is 350 meters per second and it increases by 0.2 m/s for each increase of 1 degree Celsius.  Express the speed of sound as a function of temperature in Celsius.

c. A helicopter takes off from the roof of a building that is 200 feet about the ground.  The altitude of the helicopter increases by 150 feet each minute.  Express the height of the helicopter as a function of minutes after it starts rising.

d. A car rental agency charges $29.00 plus 6 cents per mile for a one day rental.   Express the cost of renting the car as a function of miles driven.

Please do the following at the completion of every computer activity.

a.  Save your work to the desktop and close all open windows.

b.  Print the Word document, have each group member sign the document, and turn it in.

c.  Make sure at least one member of the group saves the completed activity.

Saving your work...

a.  To save the document to a flash drive, follow these steps.  1. Save the completed activity to the desktop.  2. Insert the drive.  3.  Close all windows.  4.  Double click on "My computer" on the desktop. 5. Double click on Drive E . This should open a window that displays any saved folders on your disk.  If this window is maximized (meaning you can't see any of the desktop), click the icon in the middle at the top right corner of the screen to "restore down".  6.  Click and drag the icon for your saved Activity 1 from the desktop to the window for your drive.  Release.  In a moment it will save your activity to the drive.  7.  When the work has been saved successfully saved, delete it from the desktop either by right clicking on the icon then clicking on delete or by clicking and dragging the icon into the recycle bin.

While this is not the only way to save to a flash drive, I have found that is it the most efficient, effective, and safest way.

b.  Another option for saving work is to send it to yourself in an email message as an attachment.  To do this, follow these steps.  1.  Save the completed activity to the desktop. 2.  Close all windows except for Internet Explorer.  3.  Access your email account.  4. Open a new message and put "Activity 1" in the subject line.  5.  Click on "add attachment"  and search on the desktop.  Choose your saved "Activity 1".  6.  Send the message to yourself and/or other group members.  You should now be able to retrieve the activity via that message.  The process for attaching files varies among internet providers, so if you have questions, just ask.