Math 1115 Excel Computer Project 2 Assignment | Buy Assignments Online
Math 1115 Excel Computer Project 2
Working with Polynomial and Rational Functions
Goals: Model data using polynomial regression.
Objectives:
Use Excel to fit a polynomial model to data
Use the model to answer questions about the real world situation.
Plot data and fit a trend curve to the data.
Tasks:
Place each problem on a separate worksheet
Select the cells containing the data
Use Insert Chart to get the scatterplot of the data
Select Add Trendline
Select Polynomial with the order (degree) of the polynomial
Make sure you display the equation on the chart
Type your answers to the questions on the spreadsheet.
Problem 1: The following table gives the number of U.S cellular telephone subscribers in
millions. Create a scatter plot for the data with x equal to the number of years from 1985.
a. Find the quadratic function that is the best fit for these data with x equal to the number of years from 1985 and y equal to the number subscribers in millions.
b. Use the model to estimate the number in 2015.
Year Subscribers
(millions)
Year Subscribers
(millions)
1985 0.340 1999 86.047
1986 0.682 2000 109.478
1987 1.231 2001 128.375
1988 2.069 2002 140.767
1989 3.509 2003 158.722
1990 5.283 2004 182.140
1991 7.557 2005 207.896
1992 11.033 2006 233.000
1993 16.009 2007 245.788
1994 24.134 2008 262.700
1995 33.786 2009 276.611
1996 44.043 2010 300.520
1997 55.312
1998 69.209
2. Problem 2: The table below gives the U.S. homicide rates per 100,000 people for the years
from 1990 through 2011. Make a scatter plot of the data, with x equal to the number of years
after 1990.
a. Find the cubic function that is the best fit for the data, with x equal to the number of years from 1990. Graph the function in blue.
b. Find the quartic function that is the best fit for the data. Graph the function in red.
c. Which function do you think is better? d. Estimate the homicide rate for 2015.
Year Homicide Rate Year Homicide Rate
1990 10.0 2002 6.9
1991 10.5 2003 6.3
1992 10.0 2004 5.9
1993 10.1 2005 5.6
1994 9.6 2006 5.7
1995 8.7 2007 5.6
1996 7.9 2008 5.4
1997 7.4 2009 5.0
1998 6.8 2010 4.8
1999 6.2 2011 4.7
2000 6.1
2001 7.1
3. Problem 3: The sales per share S (in dollars) for Cost Plus, Inc. for the years 1996 to 2005
are shown in the table below. Create a scatter plot of the data. Let t represent the year with
t = 6 corresponding to 1996.
a. Find the trend curve for the graph. Show a linear model of the data in blue, a quadratic model of the data in red, and a cubic model of the data in green with the scatter plot.
b. Which do you think fits the data the best?
c. Use each model to predict the year in which the sales per share will be about $50.
Year Sales per share, S
1996 11.79
1997 13.33
1998 15.81
1999 19.60
2000 23.50
2001 26.38
2002 32.12
2003 36.73
2004 41.52
2005 43.99
4. Problem 4: The table shows the national defense outlays D (in billions of dollars) from 1997 to
2005. The data can be modeled by
2. 21.493 39.06 273.5 , 7 15
0.0051 0.1398 1
where t is the year, with t = 7 corresponding to 1997.
Year Defense Outlays Year Defense Outlays
1997 270.5 2002 348.6
1998 268.5 2003 404.9
1999 274.9 2004 455.9
2000 294.5 2005 465.9
2001 305.5
a. Create a scatter plot of the data
b. Predict the national defense outlays for the years 2010, 2015, and 2020.
c. The national defense outlay for 2015 was $575 billion. How does this compare to your prediction.
d. Use your calculator to predict defense outlays using the given model. How do they
compare with your prediction from the data? Are the predictions reasonable?
e. Determine a horizontal asymptote of the graph of the model. What does it represent in
the context of the situation?
