Statistics
NEWS has gathered data over the last 52 weeks. Two of the data items that have been gathered are Profit and the Number of Defective Items.
Question 1: Using the data given below, complete Task 1 and provide a very brief, general description of whether or not a relationship exists between Profit and the Number of Defective Items.
Task 1: Create a Scatterplot
Step 1. Highlight the two columns of data (Profit, Defective Units)
Step 2. Click the Quick Analysis icon on the bottom right
Step 3. Select Charts and Scatter
Place the Chart below this row
Question 2: Using the data given below, complete Task 2 and provide a statistical description of whether or not a relationship appears to exist between Profit and the Number of Defective Items.
Task 2: Correlation and Regression Fitted Line
Step 1. Place your mouse over any point within your scatterplot above and right click. Then select Add Trendline.
Step 2. Select Linear, then scroll down and Display Equation and R squared Value on Chart
Step 3. Place the values in a visible area of the chart so that they are legible and not covered by any of the data
Determine the Correlation Coefficient (R), using the CORREL function and highlighting each column (Profit, Defective).
Check the Correlation Coefficient (R) by taking the square root (SQRT) of the R squared value in the chart above.
Determine the sign (+ or -) of R based on the direction of the regression line.
|
Week
|
Profit (thousands)
|
Number of Defective Items
|
|
1
|
$ 35.00
|
974
|
|
2
|
$ 490.00
|
693
|
|
3
|
$ 777.00
|
248
|
|
4
|
$ 922.00
|
277
|
|
5
|
$ 519.00
|
509
|
|
6
|
$ 520.00
|
635
|
|
7
|
$ 899.00
|
200
|
|
8
|
$ 391.00
|
743
|
|
9
|
$ 577.00
|
563
|
|
10
|
$ 419.00
|
715
|
|
11
|
$ 667.00
|
397
|
|
12
|
$ 399.00
|
720
|
|
13
|
$ 540.00
|
659
|
|
14
|
$ 954.00
|
123
|
|
15
|
$ 1,078.00
|
8
|
|
16
|
$ 563.00
|
444
|
|
17
|
$ 619.00
|
464
|
|
18
|
$ 625.00
|
483
|
|
19
|
$ 351.00
|
715
|
|
20
|
$ 674.00
|
444
|
|
21
|
$ 547.00
|
639
|
|
22
|
$ 578.00
|
503
|
|
23
|
$ 609.00
|
565
|
|
24
|
$ 228.00
|
785
|
|
25
|
$ 871.00
|
286
|
|
26
|
$ 188.00
|
842
|
|
27
|
$ 632.00
|
480
|
|
28
|
$ 442.00
|
721
|
|
29
|
$ 442.00
|
571
|
|
30
|
$ 1,114.00
|
25
|
|
31
|
$ 864.00
|
272
|
|
32
|
$ 825.00
|
241
|
|
33
|
$ 750.00
|
252
|
|
34
|
$ 615.00
|
500
|
|
35
|
$ 445.00
|
674
|
|
36
|
$ 282.00
|
732
|
|
37
|
$ 409.00
|
701
|
|
38
|
$ 637.00
|
401
|
|
39
|
$ 646.00
|
536
|
|
40
|
$ 999.00
|
156
|
|
41
|
$ 232.00
|
824
|
|
42
|
$ 152.00
|
964
|
|
43
|
$ 874.00
|
212
|
|
44
|
$ 981.00
|
218
|
|
45
|
$ 289.00
|
747
|
|
46
|
$ 771.00
|
356
|
|
47
|
$ 806.00
|
303
|
|
48
|
$ 921.00
|
113
|
|
49
|
$ 150.00
|
883
|
|
50
|
$ 113.00
|
910
|
|
51
|
$ 1,084.00
|
85
|
|
52
|
$ 350.00
|
745
|