Answer the questions in the various excel worksheets with the formulas in the formula bar and graphs where required.

copy_of_copy_of_hw11_mgmt650_spring2019_questions.xlsx

Unformatted Attachment Preview

MGMT 650

Spring 2019 Week 11 Homework Questions

5/5/2019

Saeko owns a yarn shop and want to expands her color selection.

Before she expands her colors, she wants to find out if her customers prefer one brand

over another brand. Specifically, she is interested in three different types of bison yarn.

As an experiment, she randomly selected 15 different days and recorded the sales of each brand.

At the .05 significance level, can she conclude that there is a difference in preference between the brands?

Misa’s Bison

98

85

91

87

98

Yak-et-ty-Yaks

79

86

72

82

91

1)

2)

What is the null hypothesis?

What is the alternative hypothesis?

3)

What is the level of significance?

Use Tools – Data Analysis – ANOVA:Single Factor

to find the F statistic:

4)

From the ANOVA ooutput: What is the F value?

5)

What is the F critical value?

6)

What is your decision?

Buffalo Yarns

65

83

78

66

193

Anova: Single Factor

Saeko has a yarn shop and wants to expand the colors in her yarn lines. She sends you the raw data in the “Piv

This data consists of the number of yards and meters of the color names, and the color types that each name b

Use the “Pivot Table Data” tab to create a pivot table that shows Saeko the number of yards and meters that w

7)

8)

9)

Using the pivot table that you just created, determine which of the color types sold the least amount of yards u

Using the pivot table that you just created, determine which of the color types sold the largest amount of yard

If Saeko wants to expand the colors in both the best selling and the worst selling color type, which two color ty

Least amount sold:

Largest amount sold:

10)

What colors should Saeko buy?

number of yards and meters that were sold in the various yarn types during the busiest weekend of her shop last year.

Customer

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

Color Name

Coriander

Black

Daffodil

Black

Opal

Toffee

Ruby

Ash

Black

Ash

Black

Whirlpool

Verde

Regal

Lynx

Yellow Rose

Chocolate

Mist

Whirlpool

Alfalfa

Ruby

Verde

Sky

Black

Whirlpool

Black

Mist

Alfalfa

Jade

Yellow Rose

Cream

Black

Ruby

Mist

Yellow Rose

Black

Sky

Periwinkle

Coriander

Black

Yellow Rose

Black

Periwinkle

Black

Blush

Black

Color Type

White

Black

Yellow

Black

Blue

Brown

Red

Blue

Black

Blue

Black

Blue

Green

Purple

Brown

Yellow

Brown

White

Blue

Green

Red

Green

White

Black

Blue

Black

White

Green

Green

Yellow

White

Black

Red

White

Yellow

Black

White

Purple

White

Black

Yellow

Black

Purple

Black

Red

Black

Yards

Meters

651

211

762

735

551

709

792

830

530

292

744

943

947

945

774

801

750

629

113

344

162

964

313

223

200

491

999

598

662

368

529

513

870

342

747

160

628

185

978

607

387

255

742

414

345

892

595.3

192.9

696.8

672.1

503.8

648.3

724.2

759.0

484.6

267.0

680.3

862.3

865.9

864.1

707.7

732.4

685.8

575.2

103.3

314.6

148.1

881.5

286.2

203.9

182.9

449.0

913.5

546.8

605.3

336.5

483.7

469.1

795.5

312.7

683.1

146.3

574.2

169.2

894.3

555.0

353.9

233.2

678.5

378.6

315.5

815.6

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

Mist

Coriander

Cream

Verde

Black

Daffodil

Chocolate

Daffodil

Regal

Daffodil

Coriander

Black

Black

Lynx

Periwinkle

Daffodil

Coriander

Verde

Lynx

Alfalfa

Cream

Daffodil

Periwinkle

Cream

Black

Opal

Cream

Cream

Coriander

Mist

Verde

Black

Blush

Opal

Daffodil

Mist

Alfalfa

Jade

Ruby

Verde

Cream

Black

Chocolate

Regal

Sky

Regal

Yellow Rose

White

White

White

Green

Black

Yellow

Brown

Yellow

Purple

Yellow

White

Black

Black

Brown

Purple

Yellow

White

Green

Brown

Green

White

Yellow

Purple

White

Black

Blue

White

White

White

White

Green

Black

Red

Blue

Yellow

White

Green

Green

Red

Green

White

Black

Brown

Purple

White

Purple

Yellow

727

584

321

478

131

539

767

369

378

376

957

929

959

994

714

912

776

895

706

105

165

505

661

226

472

184

191

238

894

488

400

618

374

359

553

439

448

290

907

402

123

981

247

805

358

461

540

664.8

534.0

293.5

437.1

119.8

492.9

701.3

337.4

345.6

343.8

875.1

849.5

876.9

908.9

652.9

833.9

709.6

818.4

645.6

96.0

150.9

461.8

604.4

206.7

431.6

168.2

174.7

217.6

817.5

446.2

365.8

565.1

342.0

328.3

505.7

401.4

409.7

265.2

829.4

367.6

112.5

897.0

225.9

736.1

327.4

421.5

493.8

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

Lynx

Regal

Ash

Periwinkle

Black

Black

Blush

Regal

Sapphire

Whirlpool

Yellow Rose

Whirlpool

Sapphire

Chocolate

Toffee

Sapphire

Whirlpool

Black

Regal

Opal

Blush

Coriander

Whirlpool

Ash

Blush

Black

Ruby

Mist

Chocolate

Opal

Toffee

Yellow Rose

Brown

Purple

Blue

Purple

Black

Black

Red

Purple

Blue

Blue

Yellow

Blue

Blue

Brown

Brown

Blue

Blue

Black

Purple

Blue

Red

White

Blue

Blue

Red

Black

Red

White

Brown

Blue

Brown

Yellow

964

317

190

423

761

219

258

968

517

362

125

504

427

557

498

216

174

470

644

436

768

456

385

869

416

634

302

892

240

523

710

245

881.5

289.9

173.7

386.8

695.9

200.3

235.9

885.1

472.7

331.0

114.3

460.9

390.4

509.3

455.4

197.5

159.1

429.8

588.9

398.7

702.3

417.0

352.0

794.6

380.4

579.7

276.1

815.6

219.5

478.2

649.2

224.0

Studies have shown that the frequency with which shoppers browse Internet retailers is related to the frequency with

data show respondents age and answer to the question “How many minutes do you browse online retailers per week

Age (X)

11

12

12

14

14

14

15

18

19

20

20

22

26

28

28

30

30

42

43

43

45

47

49

49

50

51

52

53

53

54

55

59

64

Time (Y)

1851

984

841

1063

883

1044

985

902

1040

918

976

728

859

960

893

709

786

463

560

791

591

440

258

250

480

919

262

184

231

246

205

163

300

11)

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

12)

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

13)

14)

15)

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

16)

17)

18)

Read directly from the chart:

a) Intercept =

b) Slope =

c) R2 =

Perform Data > Data Analysis > Regression. Highlight the Y-intercept with yellow. Highlight the X variable in blue. Hi

19)

20)

Using your highlighted cells, what is the equation of the regression line (round to two decimal places)?

21)

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

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

22)

Is it appropriate to use this data to predict the amount of time that a 70-year-old will be on the Internet?

23)

If yes, what is the amount of time, if no, why?

t retailers is related to the frequency with which they actually purchase products and/or services online. The following

s do you browse online retailers per week?”

g the Labels checkbox.

#1 and 2 do not agree, there is an error.

horizontal (X) axis.

24)

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

X

22

48

37

30

24

10

42

30

41

29

16

36

45

11

31

26

31

33

46

22

13

22

32

49

35

Y

3872

9312

5217

4230

4536

1820

8274

121

6314

3828

2448

6156

7515

1309

3534

4576

5797

4983

6670

2464

2197

3278

5408

7497

5705

25)

26)

Add trendline, regression equation and r squared to the plot.

Add this title. (“Scatterplot of X and Y Data”)

27)

The scatterplot reveals a point outside the point pattern. Copy the data to a new location in the worksheet. You now

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

28)

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

29)

Make a new scatterplot linked to the cleaned data without the outlier, and add title (“Scatterplot without Outlier,”) tre

30)

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

…

Purchase answer to see full

attachment