Question

1. Listed below is the selling price for a share of PepsiCO Inc. at the close of each year.

Year             Price                            Year                Price

1990             12.9135                       2000                49.5625

1991             16.8250                       2001                48.6803

1992             20.6125                       2002                42.2211

1993             20.3024                       2003                46.6215

1994             18.3160                       2004                52.2019

1995             27.7538                       2005                59.8534

1996             29.0581                       2006                62.0002

1997             36.0155                       2007                77.5108

1998             40.6111                       2008                54.7719

1999             35.0230                       2009                60.8025

a. Plot the data.

b. Use EXCEL’s Data Analysis add-in to determine the least squares trend equation.

c. Discuss the regression equation and include both the coefficient of determination and the          

   correlation coefficient in the discussion. Make sure to test the coefficient to determine if  

   it is statistically significant at the .01 significance level.

d. Calculate the points for the years 1992 and 2004.

e. (i) Estimate the selling price in 2014.                                                                                                                       

(ii) Does this seem like a reasonable estimate based on historical data? Why or why not?

f. By how much has the stock price increased or decreased (per year) on average during the period?

Show ALL of your work and show it in a neat and orderly fashion.

Answer 1

Note : Allowed to solve only 4 sub questions in one post.

a. Plot the data.

b. Use EXCEL’s Data Analysis add-in to determine the least-squares trend equation.

Step 1 : Put the data in excel as shown.

Step 2 : go to DATA -> data analysis -> regression

Step 3 : Input the values as shown.

Step 4 : The output will be generated as follows.

The values highlighted are in yellow are the coefficient of the variables.
The value highlighted in blue are pvalues for each variable used to test their significance.
The value highlighted in green the pvalues for the global hypothesis test used to test if the model is valid.

c. Discuss the regression equation and include both the coefficient of determination and the correlation coefficient in the discussion. Make sure to test the coefficient to determine if it is statistically significant at the .01 significance level.

y = -5702.64 + 2.88(Year)

Coefficient of determination(rsqaure) = 0.8980
It is the measure of the amount of variability in y explained by x. Its value lies between 0 and 1. Greater the value, better is the model. In this case, it 89.80%, hence the model is good

Correlation = sqrt(rsquare) = 0.9476
Both the variable are strongly correlated.

For the beta coefficient we test the following hypothesis.

Next we check the pvalue for the variable in the regression output and check if the pvalue is less than 0.01, if it is less than 0.01, then we reject the null hypothesis and conclude that the variable is significant

In this case pvalue is less than 0.05, hence we conclude that year is a significant predictor of sales.

d. Calculate the points for the years 1992 and 2004.

For 1992 :
y = -5702.64 + 2.88(Year)
y = -5702.64 + 2.88*(1992) = 34.32

For 2004
y = -5702.64 + 2.88*(2004) = 68.88