ISP 120 - Quantitative Reasoning
Group Activity 10: Trendlines

All group activities must include a signed statement from each group member that they participated fully in the assignment.

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

a. Open a new Word document.

b. Click on the "File" on the top menu bar, then go to "Save As".   Give your document a somewhat descriptive name (e.g. "Group Activity 10"). Also 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.)

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.

1. Open the file CarsAndTrucks_By_Year2003.xls containing the following data from the Bureau of Labor Statistics.

a. Make an X-Y scatter plot of the data. Include it in your Word document.

b. Add a trendline to your chart in a. Include the equation for the trendline and the R-squared value. Include this chart too in your Word document.

c. Use the linear equation to predict how many cars there will be in the US in the year 2010.  Make sure you use the equation; while you can visually get an approximation by extending the trendline graph, there are many times when one needs to use the equation to make a more precise prediction.

d. Use the linear equation to predict when there were no cars in the US. How accurate is the model in this prediction?

e. Use the linear equation to predict the number of cars in US in 2050. Do you think it is a good estimate? Why?

2. Open the file Coal_Consumption2003.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. Use your model to predict coal consumption in the year 2010.

c. According to your model, when will the coal consumption be 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.