Question

The goal of this lab is to standardize data, to compute probabilities using the standard normal distribution, and to find values given probabilities. Use the Lab Data Set provided to answer all questions. (all of the data is at the bottom of the post.

1. Use Microsoft Excel to compute the mean and standard deviation of Net Sales for the Patagonia file.

Mean:

Standard deviation:

2. Sort the list of 100 data values from smallest to largest, using Microsoft Excel. Write down the raw scores (x values) on lines #1, #20, and #100. Compute the corresponding z-scores by using the formula for calculating z scores. Round to two decimal places. Finally, look up the z score on the appropriate table and place the value of the cumulative area to the left of your z score on the table below.

Line #	x value	z - score	Value in Z table
2	Low value (L) =$27.07
51	Middle Value (M) =$164.71
101	High Value (H) = $295.56

3. Using the mean and standard deviation from #1 above, and using the formula x = m + zs , find the data value x that goes with the following z-scores.         Use   x = m and    s = s .

z – score	x value
z = -2.50
z = 3.20
z = 0
z = 0.5

For the rest of the lab, you will make the assumption that your data is approximately normally distributed. Use Excel to answer the following questions for the Net Sales data. Copy and paste the output below, don’t include as a separate file, make sure your x axis is labelled properly. You will have to “insert” your graphs in the appropriate places below. Please don’t upload more than one file for me to open and grade, your entire lab should be in ONE uploaded file. If you can’t do that, then print out the separate files and then scan it in as one file, but you should be able to figure out how to copy and paste all your answers into one document.

4. Locate your lowest x value (L) and your highest x value (H) on the axis.

Shade the area between x=L and x=H.

Find the value of the shaded area under the curve. In other words, what is P(L< x<H)?

5. Locate your middle x value (M) and your highest x value (H) on the axis.

Shade the area between x=M and x=H.

Find the value of the shaded area under the curve. In other words, what is P(M< x<H)?

6. Locate your highest x value (H) and your highest x value on the axis.

Shade the area to the right of x=H.

Find the value of the shaded area under the curve. In other words, what is P(x>H)?

7. Shade the area where the lowest 10% of the values would be.

What is the Z-score for this area?     _________________

What is the x-value for this area?    __________________

8. Shade the area where the top 5% of your values would be.

What is the z-score for this area?    _________________

What is the x-value for this area?     _________________

Row	Items	Sales	Card Type	Gender	Country	Age	Martial Status
1	19	$50.61	visa-electron	Male	China	35	2
2	14	$105.37	mastercard	Female	China	22	2
3	11	$90.21	maestro	Female	Russia	52	2
4	20	$280.84	visa	Male	China	38	1
5	18	$265.68	jcb	Male	China	44	1
6	19	$103.63	americanexpress	Female	Indonesia	56	2
7	17	$215.00	jcb	Male	Dominican Republic	51	1
8	20	$168.06	laser	Male	Czech Republic	25	1
9	3	$181.42	maestro	Female	China	41	1
10	11	$240.51	mastercard	Female	China	44	1
11	17	$260.56	jcb	Male	China	58	1
12	17	$170.56	jcb	Female	Belarus	28	2
13	14	$71.42	diners-club-carte-blanche	Male	Sweden	41	1
14	3	$242.23	diners-club-carte-blanche	Female	Indonesia	58	2
15	15	$250.44	visa-electron	Male	Latvia	41	2
16	4	$71.80	jcb	Male	New Zealand	20	2
17	6	$33.62	diners-club-us-ca	Male	United States	48	2
18	17	$81.35	diners-club-enroute	Female	Colombia	44	2
19	13	$67.09	maestro	Male	China	53	1
20	3	$262.41	jcb	Female	Lithuania	22	1
21	16	$204.28	jcb	Female	Indonesia	23	2
22	2	$289.74	jcb	Female	Vietnam	28	2
23	1	$33.45	china-unionpay	Male	China	29	2
24	19	$154.19	jcb	Female	Botswana	46	2
25	20	$43.29	diners-club-enroute	Male	Argentina	58	2
26	5	$96.97	jcb	Male	Russia	42	2
27	1	$46.62	jcb	Male	Ecuador	21	2
28	17	$241.04	jcb	Male	China	41	1
29	6	$251.64	switch	Female	Sudan	58	1
30	1	$115.24	visa-electron	Female	Canada	52	1
31	10	$263.42	jcb	Male	France	44	2
32	10	$274.67	jcb	Female	Italy	32	2
33	1	$69.59	jcb	Female	Switzerland	48	2
34	17	$136.30	china-unionpay	Male	China	44	2
35	7	$201.52	jcb	Male	Macedonia	26	2
36	8	$51.44	switch	Female	Papua New Guinea	51	1
37	11	$52.95	jcb	Male	Czech Republic	48	2
38	19	$162.89	china-unionpay	Female	China	36	2
39	5	$160.09	jcb	Female	China	38	1
40	6	$91.28	jcb	Female	Brazil	39	1
41	4	$140.53	mastercard	Female	Indonesia	26	2
42	15	$190.36	visa	Male	Greece	57	1
43	10	$181.57	americanexpress	Male	Philippines	46	2
44	1	$65.59	jcb	Female	China	31	1
45	3	$49.01	laser	Female	United States	49	2
46	16	$88.05	jcb	Female	France	54	2
47	9	$193.79	jcb	Male	Indonesia	38	1
48	5	$39.55	mastercard	Female	Indonesia	24	2
49	1	$32.56	jcb	Male	Japan	23	1
50	2	$54.52	china-unionpay	Male	Ireland	43	1
51	19	$161.89	jcb	Male	China	57	1
52	2	$59.63	maestro	Male	Cyprus	35	1
53	13	$257.81	bankcard	Male	China	38	1
54	15	$166.53	laser	Male	South Africa	50	1
55	15	$253.02	diners-club-carte-blanche	Female	Canada	39	2
56	16	$193.56	americanexpress	Female	China	30	2
57	18	$80.57	china-unionpay	Male	Brazil	30	1
58	18	$250.29	jcb	Male	Yemen	41	1
59	15	$46.79	jcb	Female	Japan	42	1
60	18	$276.56	laser	Male	Slovenia	32	2
61	14	$135.13	jcb	Male	Tanzania	31	1
62	14	$195.58	jcb	Female	China	42	1
63	15	$182.98	visa	Female	China	52	2
64	8	$221.03	jcb	Male	Zimbabwe	29	1
65	3	$128.11	jcb	Female	China	40	1
66	19	$76.60	diners-club-carte-blanche	Female	Indonesia	38	1
67	13	$27.07	jcb	Female	China	59	2
68	4	$109.20	diners-club-carte-blanche	Male	Russia	48	2
69	4	$276.85	jcb	Male	Uruguay	57	2
70	19	$195.10	jcb	Male	Sao Tome and Principe	25	1
71	5	$112.23	instapayment	Male	Zambia	41	1
72	15	$61.94	jcb	Female	Nigeria	41	1
73	4	$35.08	jcb	Female	China	35	2
74	20	$60.13	switch	Male	China	23	2
75	6	$277.11	visa-electron	Female	Portugal	54	2
76	5	$220.47	jcb	Female	Russia	37	2
77	14	$185.57	laser	Male	Russia	53	2
78	19	$295.96	diners-club-enroute	Male	Greece	51	1
79	12	$238.86	visa	Female	Indonesia	45	2
80	3	$275.81	visa-electron	Female	Indonesia	26	2
81	7	$77.07	visa	Female	Portugal	57	1
82	2	$252.58	mastercard	Female	Russia	45	2
83	4	$134.78	jcb	Male	Japan	29	1
84	3	$43.49	americanexpress	Male	Indonesia	48	2
85	1	$223.78	jcb	Male	Mexico	53	2
86	8	$238.74	jcb	Female	China	28	2
87	9	$291.30	americanexpress	Male	Togo	44	1
88	14	$79.46	jcb	Female	Finland	54	2
89	16	$193.73	jcb	Male	Indonesia	57	1
90	13	$224.23	visa-electron	Female	Pakistan	23	2
91	16	$247.43	mastercard	Female	Honduras	27	1
92	9	$186.11	jcb	Male	China	56	2
93	17	$58.48	jcb	Male	China	53	2
94	1	$281.40	jcb	Female	Philippines	46	2
95	10	$254.37	bankcard	Male	Brazil	42	1
96	8	$145.00	jcb	Female	Indonesia	50	2
97	20	$122.35	jcb	Female	Sweden	25	2
98	1	$210.77	jcb	Male	Portugal	50	1
99	7	$225.37	diners-club-carte-blanche	Female	South Africa	43	2
100	18	$87.98	maestro	Male	China	37	2
Note:
Marital Status 1 = Married
Marital Status 2 = Single

Answer 1

Question 1

From given data and by using excel, we have

Sample mean = Xbar = 158.25

Sample standard deviation = S = 83.75

Question 2

Here, we have to find the z-score and their probabilities values by using z-table. Table and calculations are given as below:

Z = (X – mean) / S

Line #	x value	z - score	Value in Z table
2	Low value (L) =$27.07	Z = (27.07 –158.25)/83.75 = -1.57	0.058635883
51	Middle Value (M) =$164.71	Z = (164.71 – 158.25)/83.75 = 0.077	0.530741658
101	High Value (H) = $295.56	Z = (295.56 – 158.25)/83.75 = 1.64	0.949447744

Question 3

Here, we have to find the values of X = mean + Z*Standard deviation

z – score	x value
z = -2.50	X = 158.25 – 2.50*83.75 = -51.125
z = 3.20	X = 158.25 + 3.20*83.75 = 426.25
z = 0	X = 158.25 + 0*83.75 = 158.25
z = 0.5	X = 158.25 + 0.5*83.75 = 200.125

Question 4

Here, we have to find P(L<X<H)

P(L<X<H) = P(27.07< X< 295.56)

P(L<X<H) = P(X<295.56) – P(X<27.07)

P(L<X<H) = 0.949447744 - 0.058635883

(by using table from question 1)

P(L<X<H) = 0.890812

Required curve is given as below: