Assignment is done in excel

Studies have shown that the frequency with which shoppers browse Internet retailers is related to the frequency with which they actually purchase products and/or services online.The following data show respondents age and answer to the question "How many minutes do you browse online retailers per week?"

Age (X)Time (Y)

23513

51207

45201

33405

56141

61141

39297

23501

22531

46273

53147

34381

20591

18609

22519

1Use Data > Data Analysis > Correlation to compute the correlation checking the Labels checkbox.

2Use the Excel function =CORREL to compute the correlation. If answers for #1 and 2 do not agree, there is an error.

3The strength of the correlation motivates further examination.

a)Insert Scatter (X, Y) plot linked to the data on this sheet with Age on the horizontal (X) axis.

b) Add to your chart: the chart name, vertical axis label, and horizontal axis label.

c) Complete the chart by adding Trendline and checking boxes

4Read directly from the chart:

a) Intercept =

b) Slope =

c) R2 =

5Perform Data > Data Analysis > Regression.

6Read the standard error in the regression output?

7Based on the regression output, what isthe equation of the regression line?

8Use Excel to predict the number of minutes spent by a 40-year old shopper. Enter = followed by the regression formula.

Enter the intercept and slope into the formula by clicking on the cells in the regression output with the results.

9On this worksheet, make an XY scatter plot linked to the following data:

XY

6.98112.266

3.9828.455

2.0845.951

9.11314.395

2.2807.435

6.56711.332

1.8977.011

7.18612.716

4.0949.214

1.2577.499

7.19910.473

2.1366.124

3.0328.832

3.7359.295

8.61235.000

0.3387.155

5.34810.475

9.20813.650

7.57013.910

8.64611.895

1.9537.387

3.4757.871

3.96210.482

8.08411.727

4.8668.688

10Add trendline and regression equation to the plot.

11The scatterplot reveals a point outside the point pattern. Copy the data to a new location in the worksheet. You now have 2 sets of data.

Data that are more tha 1.5 IQR below Q1 or more than 1.5 IQR above Q3 are considered outliers and must be investigated.

It was determined that the outlying point resulted from data entry error. Remove the outlier in the copy of the data.

12Make a new scatterplot linked to the cleaned data without the outlier, and add trendline and regression equation label.

13Compare the regression equations of the two plots. How did removal of the outlier affect the slope and R2?

7/8/2017 18:19

Highlight the correct answer or answers (#17 & 19) for each of the following questions:

14The correlation R measures the strength of the linear association of variables Y and X, and does not have a unit of measure, e.g. feet, acres, pounds, seconds.

True

False

15Based on the correlation computed in tab "Excel Competencies", does Time tends to increase with Age?

True

False

16The strength of the linear relationship between Age and the Time is

Weak

Moderate

Strong

17Highlight the 4 correct statements. Try not to mix up explanatory and response with dependent and independent.

X denotes the independent or response variable

X denotes the independent or explanatory variable

Y denotes the dependent or explanatory variable

Y denotes the dependent or response variable

x denotes an observed value of the independent variable

x denotes an observed value of the dependent variable

denotes the mean value of observations of the response variable

18The best fitting line minimizes the vertical distances from the points to the line. Hence,

the Y coordinate of a point on the best fitting line provides an estimate or prediction of Y at the value of the corresponding X coordinate.

This process is called regression (to move backward) because

The estimate of Y will be closer to the mean in standard deviations than X is.

The estimate of Y will be farther from the mean in standard deviations than X is.

19Highlight 4 assumptions pertaining to regression:

Scatter plot pattern is reasonably straight (Linearity)

No points lie far enough away to pull the line of best fit away from the main point pattern (Influence).

The plot does not fan out as x increases or decreases (Equal Spread)

Predict Y at a value of X within the range of the X data (Interpolation)

Predict Y at a value of X outside the range of the X data (Extrapolation)

The observations are independent (Independence)

20Based on the data in "Excel Competencies", can Y be predicted for a person who is 80?

No

Yes

21The "intercept" and "slope" completely define the best fitting line.

The intercept is the vertical distance from the origin (where the X and Y axes intersect) up or down to the line. It sets the elevation of the line.

TRUE

FALSE

22As a positively signed slope increases

The whole line moves up without rotating

The whole line moves down without rotating

The line rotates clockwise becoming less steep

The line rotates counterclockwise becoming more steep

23Based on the regression output in "Excel Competencies", when Age increases by 1 year, Time decreases by

0.97

32.10

750.02

11.503

24R2 measures the fit of the line to the points. As R2 increases

The scatter about the line increases and the amount of the variation of Y explained by X decreases

The scatter about the line decreases and the amount of the variation of Y explained by X increases

The scatter about the line increases and the amount of the variation of Y explained by X increases

The scatter about the line decreases and the amount of the variation of Y explained by X decreases

25The Standard Error is a standard deviation measuring the scatter of the points about the regression line.

True

False