Math 115

1

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

2

1.493 39.06 273.5 , 7 15

0.0051 0.1398 1

t t D t

t t

    

 

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?