Need Completed asap.

Please Help!!!

1

Math 1115

Excel computer Project #2

Working with Polynomial and Rational Functions

Goals: The first goal of this project is to introduce students to modeling data using polynomial

regression. Students will use Excel to fit a polynomial model to data, and use the model to

answer questions about the real world situation. Students will discuss how well and to what extent

the model describes the situation. The second goal of this project is to give students hands-on

experience using a spreadsheet. Students will plot data and fit a trend curve to the data.

Be sure to include your name and identify each problem.

Selecting the cells containing the data, using Chart Wizard to get the scatter plot of the data,

selecting Add Trendline, and picking Polynomial with the order (degree) of the polynomial gives

the equation of the polynomial function that is the best fit for the data, along with the scatter plot

and the graph of the best-fitting curve.

Problem 1: Cell Phones. The following table gives the number of millions of U.S cellular

telephone subscribers.

a. Create a spreadsheet in worksheet with the below data and appropriate column headings.

Title the Worksheet “Cell Phone Subscribers from 1985 to 2002”.

b. Create a scatter plot for the data with x equal to the number of years from 1985 and y equal

to the number subscribers in millions. The table should be titled “U.S. Cell Phone

Subscribers”. The axis labels should be “Subscribers (in millions)” and “Year from 1985”.

ANSWER THE FOLLOWING QUESTIONS ON A SEPARATE SHEET OF PAPER:

1. Does it appear that the data could be modeled with a quadratic function?

2. What is the quadratic function that is the best fit for these data? Include this function line and

equation on the scatter plot.

3. Use the model to estimate the number of subscribers in 2005.

4. What part of the U.S. population does this estimate equal? Note: You will need to look up

the population for 2005.

Year Subscribers

(millions)

Year Subscribers

(millions)

Year Subscribers

(millions)

1985 0.340 1991 7.557 1997 55.312

1986 0.682 1992 11.033 1998 69.209

1987 1.231 1993 16.009 1999 86.047

1988 2.069 1994 24.134 2000 109.478

1989 3.509 1995 33.786 2001 128.375

1990 5.283 1996 44.043 2002 140.767

2

Problem 2: Homicide Rates. The table below gives the U.S. homicide rates per 100,000

people for the years from 1990 through 2001.

a. Create a spreadsheet in worksheet #2 with the below data and appropriate column headings.

Title the “Worksheet Homicide Rates from 1990 to 2001”.

b. Make a scatter plot of the data, with x equal to the number of years from 1990. The table

should be titled “U.S. Homicide Rates”. The axis labels should be “U.S. Homicide Rates (per

100,000)” and “Year from 1990”.

ANSWER THE FOLLOWING QUESTIONS ON A SEPARATE SHEET OF PAPER:

1. Does it appear that a cubic or quartic function would be the better fit for the data?

2. What is the cubic function that is the best fit for the data? Include this function line and

equation on the scatter plot.

3. What is the quartic function that is the best fit for the data? Include this function line and

equation on the scatter plot.

4. Is the cubic or the quartic function better? .

c. The data for 2001 includes the 9/11 terrorism deaths. Would you expect the 2002 rate to be

higher or lower than the 2001 rate?

Year Homicide Rate Year Homicide Rate

1990 10.0 1996 7.9

1991 10.5 1997 7.4

1992 10.0 1998 6.8

1993 10.1 1999 6.2

1994 9.6 2000 6.1

1995 8.7 2001 7.1

3

Problem 3: Cost Plus. The sales per share S (in dollars) for Cost Plus, Inc. for the years

1996 to 2005 are shown in the table below.

a. Create a spreadsheet in worksheet #3 with the below data and appropriate column headings.

Title the Worksheet “Cost Plus Sales from 1996 to 2005”.

b. Make three graphs of the data, with t representing the year and t = 6 corresponding to the

year 1996. The table should be titled “Cost Plus Sales from 1996 to 2005”. The axis labels

should be “Sales per Share (in dollars)” and “Year from 1990”.

c. Find trend curves for the graph for the above scatter plots. The trend curves should be a

linear model, a quadratic model, and a cubic model of the data on separate graphs with the

scatter plot.

ANSWER THE FOLLOWING QUESTIONS ON A SEPARATE SHEET OF PAPER:

1. Which do you think fits the data the best: a linear, quadratic or cubic model?

2. Use each model (linear, quadratic or cubic) to predict the year in which the sales per share

will be about $50.

3. Discuss the appropriateness of each modeling for predicting future values.

4. Try to find out the current selling price of Cost Plus, Inc. and compare to the models.

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: Defense. 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.

a. Create a spreadsheet in worksheet #4 with the below data and appropriate column headings.

Title the Worksheet “National Defense Outlays from 1997 to 2005”.

b. Make a scatter plot of the data, with t = 7 corresponding to the year 1997. The table should

be titled “National Defense Outlays from 1997 to 2005”. The axis labels should be “Defense

Outlays (in billions of dollars)” and “Year from 1997”. Find the best fit function and include on

the graph.

ANSWER THE FOLLOWING QUESTIONS ON A SEPARATE SHEET OF PAPER:

1. Predict the national defense outlays for the years 2010, 2015, and 2020.

2. Use your calculator to predict the defense outlays using the given model. How do they

compare with your prediction from the data using the best fit function? Are the predictions

reasonable?

3. Determine a horizontal asymptote of the graph of the model. What does it represent in the

context of the situation?

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

5

Problem 5: Your Data. Find a data set that you are interested in to do a scatter plot.

a. Create a spreadsheet in Worksheet #5 with the data you have chosen. Title the spreadsheet

appropriately.

b. Create a scatter plot of the data. Make sure you have appropriate titles for the heading and

the axes.

c. Determine the type of function the data resembles and create a trend curve for the data.

Include the trend curve and equation on the graph.

ANSWER THE FOLLOWING QUESTIONS ON A SEPARATE SHEET OF PAPER:

1. Make a prediction of the future based on your data and trend curve.

2. What is the source of your data (website, book, etc.)?