Math 1115 Excel Computer Project 2 Working With Polynomial ✓ Solved

1 Math 1115 Excel computer Project #2 Working with Polynomial

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.

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?

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.

Problem 4: Defense. The table shows the national defense outlays D (in billions of dollars) from 1997 to 2005. 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?

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.).

Paper For Above Instructions

The project presented here involves the application of polynomial regression and the use of Excel as a spreadsheet tool for modeling and analyzing data relevant to real-world scenarios. This document outlines the procedures and expected outcomes for each task stipulated in the project.

Problem 1: Cell Phones

The worksheet titled “Cell Phone Subscribers from 1985 to 2002” contains vital data on U.S cellular telephone subscribers. Using the provided data, a scatter plot is created with the x-axis representing the years from 1985, and the y-axis indicating the number of subscribers in millions. After plotting the data, a trendline is added to determine the quadratic function that best fits this model.

To find the quadratic function, the Excel trendline feature is utilized. The function derived from the trendline analysis reveals a strong quadratic relationship in the data, suggesting that quadratic functions can efficiently model subscriber growth over time. The equation from the polynomial regression indicates potential future subscriber counts. For instance, predicting the number of subscribers in 2005 requires evaluating the function at the corresponding x-value derived from 2005. This function indicates an estimated subscriber count significantly correlating to population growth, as this estimate should be compared against the U.S. population in that year, which was approximately 295 million (U.S. Census Bureau, 2005).

Problem 2: Homicide Rates

The analysis of homicide rates from 1990 to 2001 is structured similarly. The worksheet titled “Homicide Rates from 1990 to 2001” includes the years as the x-values and the respective homicide rates per 100,000 people as the y-values. A scatter plot created from this data enables a visual understanding of the trends over these years. The task is to evaluate whether a cubic or quartic function fits the data better by comparing the two polynomial models via their regression analyses.

Upon analysis, the cubic function generally aligns closer with the data trends, although both cubic and quartic functions will be calculated and graphed with corresponding equations included. This assessment aids in determining the implications of violent crime trends over this time frame and informs future forecasting efforts.

Problem 3: Cost Plus Sales

In examining Cost Plus sales from 1996 to 2005, students will create a worksheet named “Cost Plus Sales from 1996 to 2005.” With data plotted using three different polynomial trend models, students evaluate which model—linear, quadratic, or cubic—best describes the sales trends over the observed period. Once the best-fitting model is ascertained, predictions can be made regarding when sales per share will reach approximately $50.

This analysis promotes understanding of corporate financial trends and provides insights into the company’s growth trajectory. As part of the evaluation, students will compare model predictions with actual current share prices to understand the market accuracy of their polynomial forecasting.

Problem 4: National Defense Outlays

The next component involves examining national defense outlays from 1997-2005. This worksheet will be titled “National Defense Outlays from 1997 to 2005.” A scatter plot will be created to visualize trends in defense spending as a function of time. The objective is to develop a function capable of predicting future outlays through polynomial regression and assess its implications based on historical data trends.

Students will explore whether a horizontal asymptote exists within their model, indicating a long-term trend in defense spending, while ensuring that predictions made for future years (2010, 2015, 2020) are reasonable in light of current sociopolitical factors affecting national security funding.

Problem 5: Your Data

For the final problem, students will independently select a dataset of personal interest to model and analyze, thus promoting a deeper engagement with the subject matter. They will develop a worksheet with appropriate titles, construct a scatter plot, and create a trend curve reflective of their data. Through this hands-on experience, students will learn to source data and draw conclusions about trends relevant to their interests.

The final analysis aims to enhance understanding of statistical modeling while encouraging inquiry into personal areas of interest, thus fostering a comprehensive grasp of how polynomial functions apply in various contexts. This reflection will culminate in an assessment of their trend model's predictive capabilities.

References

Related Assignments