Statistics for Managerial Decisions Making

Alimo
HW11-Fall2018.xlsx
Home>Business & Finance homework help>Management homework help>Statistics for Managerial Decisions Making

HW11

MGMT 650 Fall 2018 Week 11 Homework (Last updated 6/24/2018)

Excel Competencies - Regression

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)
19 577
49 401
56 104
20 393
24 555
21 601
30 434
28 540
60 97
58 349
23 578
26 536
60 110
57 221
46 452
30 376
28 343
44 487
49 412
33 449
1 Use Data > Data Analysis > Correlation to compute the correlation checking the Labels checkbox.
2 Use the Excel function =CORREL to compute the correlation. If answers for #1 and 2 do not agree, there is an error.
3 The 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
4 Read directly from the chart:
a) Intercept =
b) Slope =
c) R2 =
5 Perform Data > Data Analysis > Regression. Highlight the Y-intercept with yellow. Highlight the X variable in blue. Highlight the standard error in orange
6 Using your highlighted cells, what is the equation of the regression line?
7 Use Excel to predict the number of minutes spent by a 41-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.

Excel Competencies -Pivot Table

Saeko has a yarn shop and wants to expand the colors in her yarn lines. She sends you the raw data in the "Pivot Table Data" tab.
This data consists of the number of yards and meters of the color names, and the color types that each name belongs to, sold during her busiest weekend last year.
8 Use the "Pivot Table Data" tab to create a pivot table that shows Saeko the number of yards and meters that were sold in the various yarn types during the busiest weekend of her shop last year.
9 Using the pivot table that you just created, determine which of the color types sold the least amount of yards using =MIN()
Using the pivot table that you just created, determine which of the color types sold the largest amount of yards using =MAX()
If Saeko wants to expand the colors in both the best selling and the worst selling color type, which two color types should she buy?

Pivot Table Data

Customer Color Name Color Type Yards Meters
1 Coriander White 471 430.7
2 Lynx Brown 285 260.6
3 Daffodil Yellow 338 309.1
4 Periwinkle Purple 285 260.6
5 Opal Blue 342 312.7
6 Toffee Brown 279 255.1
7 Ruby Red 154 140.8
8 Ash Blue 299 273.4
9 Ash Blue 450 411.5
10 Ash Blue 404 369.4
11 Whirlpool Blue 419 383.1
12 Whirlpool Blue 370 338.3
13 Verde Green 153 139.9
14 Regal Purple 173 158.2
15 Lynx Brown 396 362.1
16 Toffee Brown 446 407.8
17 Chocolate Brown 222 203.0
18 Mist White 393 359.4
19 Whirlpool Blue 185 169.2
20 Alfalfa Green 412 376.7
21 Ruby Red 445 406.9
22 Verde Green 333 304.5
23 Sky White 414 378.6
24 Chocolate Brown 352 321.9
25 Whirlpool Blue 222 203.0
26 Black Black 324 296.3
27 Mist White 274 250.5
28 Alfalfa Green 305 278.9
29 Jade Green 389 355.7
30 Opal Blue 278 254.2
31 Cream White 253 231.3
32 Chocolate Brown 219 200.3
33 Ruby Red 224 204.8
34 Mist White 353 322.8
35 Regal Purple 361 330.1
36 Jade Green 440 402.3
37 Sky White 493 450.8
38 Periwinkle Purple 319 291.7
39 Coriander White 297 271.6
40 Black Black 462 422.5
41 Ash Blue 182 166.4
42 Chocolate Brown 385 352.0
43 Periwinkle Purple 325 297.2
44 Black Black 341 311.8
45 Blush Red 433 395.9
46 Lynx Brown 355 324.6
47 Mist White 158 144.5
48 Coriander White 364 332.8
49 Cream White 314 287.1
50 Verde Green 367 335.6
51 Chocolate Brown 328 299.9
52 Daffodil Yellow 247 225.9
53 Chocolate Brown 479 438.0
54 Daffodil Yellow 243 222.2
55 Regal Purple 256 234.1
56 Sapphire Blue 342 312.7
57 Coriander White 176 160.9
58 Black Black 345 315.5
59 Lynx Brown 393 359.4
60 Lynx Brown 329 300.8
61 Periwinkle Purple 362 331.0
62 Chocolate Brown 495 452.6
63 Coriander White 349 319.1
64 Verde Green 246 224.9
65 Lynx Brown 441 403.3
66 Alfalfa Green 386 353.0
67 Cream White 175 160.0
68 Daffodil Yellow 190 173.7
69 Periwinkle Purple 486 444.4
70 Cream White 336 307.2
71 Toffee Brown 436 398.7
72 Opal Blue 496 453.5
73 Cream White 303 277.1
74 Cream White 389 355.7
75 Coriander White 415 379.5
76 Mist White 352 321.9
77 Verde Green 154 140.8
78 Toffee Brown 160 146.3
79 Blush Red 465 425.2
80 Opal Blue 235 214.9
81 Daffodil Yellow 248 226.8
82 Mist White 339 310.0
83 Alfalfa Green 436 398.7
84 Jade Green 194 177.4
85 Ruby Red 309 282.5
86 Verde Green 244 223.1
87 Cream White 293 267.9
88 Lynx Brown 229 209.4
89 Chocolate Brown 311 284.4
90 Regal Purple 319 291.7
91 Sky White 275 251.5
92 Regal Purple 390 356.6
93 Opal Blue 242 221.3
94 Lynx Brown 163 149.0
95 Regal Purple 364 332.8
96 Ash Blue 292 267.0
97 Periwinkle Purple 204 186.5
98 Black Black 449 410.6
99 Black Black 281 256.9
100 Blush Red 162 148.1
101 Regal Purple 182 166.4
102 Sapphire Blue 491 449.0
103 Whirlpool Blue 212 193.9
104 Chocolate Brown 375 342.9
105 Whirlpool Blue 181 165.5
106 Sapphire Blue 288 263.3
107 Chocolate Brown 368 336.5
108 Toffee Brown 485 443.5
109 Sapphire Blue 475 434.3
110 Whirlpool Blue 187 171.0
111 Black Black 192 175.6
112 Regal Purple 213 194.8
113 Opal Blue 360 329.2
114 Blush Red 336 307.2
115 Coriander White 379 346.6
116 Whirlpool Blue 411 375.8
117 Ash Blue 217 198.4
118 Blush Red 246 224.9
119 Black Black 191 174.7
120 Ruby Red 191 174.7
121 Mist White 497 454.5
122 Chocolate Brown 246 224.9
123 Opal Blue 439 401.4
124 Toffee Brown 334 305.4
125 Ash Brown 427 390.4

Cleaning Data with Outlier

10 On this worksheet, make an XY scatter plot linked to the following data:
X Y
4.27 5.978
7.14 8.568
6.74 8.458
8.58 9.154
9.03 11.06
6.69 33.87
7.76 8.088
5.3 5.3
10.51 12.612
5.52 6.072
8.9 9.35
1.08 1.08
5.65 6.78
9.06 10.308
9.46 11.352
8.07 10.298
10.34 12.578
10.15 11.255
7.61 8.371
3.35 4.365
3.82 5.876
1.05 1.47
4.85 4.85
6.48 7.664
10.5 10.5
11 Add trendline and regression equation to the plot.
12 The 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.
13 Make a new scatterplot linked to the cleaned data without the outlier, and add trendline and regression equation label.
14 Compare the regression equations of the two plots. How did removal of the outlier affect the slope and R2?

Learning

7/8/17 18:19
Highlight the correct answer or answers (#17 & 19) for each of the following questions:
15 The 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
16 Based on the correlation computed in tab "Excel Competencies", does Time tends to increase with Age?
● True
● False
17 The strength of the linear relationship between Age and the Time is
● Weak
● Moderate
● Strong
18 Highlight 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
19 The 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.
20 Based on the data in "Excel Competencies", can Y be predicted for a person who is 80?
● No
● Yes
21 The "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
22 As 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
23 Based on the regression output in "Excel Competencies", when Age increases by 1 year, Time decreases by
● 0.97
● 32.10
● 750.02
● 8.3966
24 R2 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
25 The Standard Error is a standard deviation measuring the scatter of the points about the regression line.
● True
● False