QM 2241 Stony Brook University Automobile Insurance Worksheet Questions are in the file. Making the graphs. 2 simple questions, data analysis. Below is a d

QM 2241 Stony Brook University Automobile Insurance Worksheet Questions are in the file. Making the graphs. 2 simple questions, data analysis. Below is a data set containing information on applicants applying for automobile insurance with an insurance
each variable:
App. No.:
Gender:
Marital Status:
Education:
Annual Salary:
Credit Score:
Age:
Deductible:
Claims:
applicant number
‘Female’ or ‘Male’
‘Married’ or ‘Not Married’
highest level completed – ‘High School’, ‘College’, ‘Graduate’
current annual salary
current credit score
age at time of application
desired insurance deductible
number of claims filed during last policy year
App. No.
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
Gender
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Marital Status
Married
Married
Not Married
Married
Not Married
Not Married
Not Married
Married
Married
Married
Married
Married
Married
Married
Not Married
Married
Married
Not Married
Married
Married
Married
Married
Married
Married
Married
Not Married
Married
Not Married
Married
Education
Graduate
Graduate
High School
High School
College
High School
College
College
High School
College
Graduate
High School
Graduate
High School
High School
College
College
Graduate
College
College
Graduate
College
College
Graduate
College
College
Graduate
High School
College
Annual Salary
$51,497
$43,892
$22,975
$32,941
$40,594
$22,231
$42,718
$44,291
$37,752
$50,703
$50,297
$36,605
$53,146
$40,596
$30,593
$42,771
$27,779
$44,069
$49,063
$40,689
$54,281
$40,856
$41,300
$48,299
$40,179
$46,181
$54,540
$38,093
$26,722
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Married
Married
Married
Married
Married
Married
Married
Not Married
Not Married
Married
Not Married
Married
Married
Married
Married
Married
Married
Married
Married
Married
Married
Married
Not Married
Married
Married
Not Married
Married
Married
Not Married
Not Married
Married
Married
Married
Married
Married
Not Married
Not Married
Married
Married
Married
Married
Married
Married
Married
Married
Not Married
Married
High School
Graduate
College
College
College
Graduate
College
High School
High School
College
College
High School
High School
College
High School
Graduate
High School
High School
College
High School
College
College
College
College
Graduate
High School
College
College
College
College
College
College
High School
Graduate
College
College
High School
College
College
College
College
College
Graduate
High School
High School
Graduate
High School
$36,112
$58,340
$53,146
$49,187
$40,541
$41,625
$43,261
$33,913
$38,059
$50,543
$37,286
$29,550
$44,162
$48,755
$43,320
$47,944
$43,320
$33,406
$51,682
$41,700
$40,474
$43,588
$46,102
$42,516
$53,320
$42,371
$50,848
$49,429
$36,800
$43,511
$49,342
$45,131
$38,213
$45,968
$46,389
$49,627
$48,417
$36,426
$52,491
$38,087
$48,072
$44,381
$45,587
$36,914
$39,119
$52,418
$43,448
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Married
Not Married
Not Married
Not Married
Not Married
Not Married
Not Married
Not Married
Married
Married
Married
Not Married
Not Married
Not Married
Not Married
Married
Not Married
Not Married
Not Married
Married
Married
Married
Married
Not Married
Married
Married
Married
Married
Not Married
Married
Married
Married
Married
Married
Married
Married
Not Married
Married
Married
Not Married
Not Married
Married
Married
Married
Married
Not Married
Not Married
Graduate
High School
High School
College
College
College
College
High School
Graduate
College
High School
Graduate
High School
High School
High School
Graduate
College
College
College
High School
College
Graduate
College
High School
High School
College
College
Graduate
College
Graduate
College
High School
College
College
College
Graduate
Graduate
High School
College
College
High School
High School
College
Graduate
Graduate
College
College
$47,336
$37,695
$31,408
$56,070
$45,317
$47,027
$41,266
$41,545
$47,128
$46,125
$41,031
$46,392
$34,002
$34,482
$26,162
$43,846
$49,096
$44,638
$48,357
$36,613
$42,503
$41,432
$50,224
$34,083
$43,367
$50,362
$51,969
$58,161
$48,793
$49,709
$55,964
$43,454
$40,624
$44,637
$49,149
$57,384
$47,181
$43,597
$50,500
$53,657
$31,327
$26,164
$38,471
$55,175
$48,552
$39,517
$39,299
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
Male
Male
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Married
Married
Not Married
Not Married
Not Married
Not Married
Married
Not Married
Not Married
Not Married
Not Married
Not Married
Not Married
Married
Not Married
Not Married
Not Married
Not Married
Not Married
Not Married
Not Married
Not Married
Married
Married
Not Married
Not Married
Not Married
Not Married
Not Married
Not Married
Not Married
Not Married
Married
Married
Not Married
Not Married
Not Married
Married
Not Married
Not Married
Not Married
Not Married
Married
Not Married
Not Married
Married
Not Married
Graduate
College
College
High School
High School
Graduate
College
College
High School
College
College
College
Graduate
Graduate
College
College
College
College
Graduate
High School
Graduate
College
College
College
High School
College
Graduate
Graduate
Graduate
College
Graduate
College
High School
Graduate
College
Graduate
College
Graduate
High School
High School
College
Graduate
Graduate
Graduate
College
College
High School
$53,338
$41,438
$39,736
$34,125
$41,785
$47,798
$35,773
$40,569
$43,595
$23,830
$43,078
$51,427
$40,349
$39,640
$39,843
$36,473
$42,444
$34,165
$43,364
$39,084
$55,748
$48,480
$36,408
$48,703
$21,009
$40,796
$44,823
$42,299
$49,760
$26,034
$44,583
$49,022
$30,732
$47,614
$44,686
$34,894
$35,979
$51,011
$40,837
$33,911
$31,094
$32,482
$57,497
$44,573
$39,689
$36,562
$28,477
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Female
Not Married
Married
Not Married
Not Married
Married
Not Married
Not Married
Not Married
Married
Not Married
Not Married
Not Married
Married
Not Married
Married
Not Married
Not Married
Not Married
Married
Not Married
Not Married
Not Married
Married
Not Married
Not Married
Not Married
Married
Not Married
Not Married
Not Married
Not Married
Not Married
Married
Not Married
Not Married
Not Married
Not Married
Not Married
Married
Not Married
Not Married
Not Married
Not Married
Not Married
Married
Not Married
High School
High School
College
High School
College
Graduate
College
College
Graduate
College
High School
Graduate
High School
College
High School
College
Graduate
Graduate
College
High School
College
College
Graduate
High School
College
College
College
College
College
College
High School
Graduate
College
High School
College
High School
College
College
Graduate
High School
High School
High School
High School
Graduate
High School
High School
$27,879
$24,948
$41,774
$35,171
$43,538
$45,072
$40,004
$34,234
$55,697
$38,768
$34,167
$44,169
$18,956
$45,371
$33,310
$38,010
$46,754
$51,510
$39,330
$28,240
$39,128
$27,366
$57,322
$30,490
$41,662
$34,300
$36,341
$39,523
$35,469
$37,149
$24,768
$40,322
$40,626
$28,822
$50,305
$31,156
$50,678
$40,150
$48,186
$21,541
$33,729
$23,335
$31,212
$47,095
$39,490
$30,223
e insurance with an insurance company. Here is a description of
Credit Score
681
526
349
534
520
333
602
651
636
753
671
607
708
674
503
612
326
545
738
598
734
574
566
596
567
661
723
638
309
Age
42
41
19
23
56
17
46
32
22
47
33
25
46
38
20
36
25
30
38
26
42
33
47
54
38
43
46
24
24
Deductible
$250
$250
$250
$250
$250
$250
$250
$250
$500
$2,500
$500
$500
$500
$500
$250
$1,000
$250
$250
$500
$2,500
$1,000
$250
$500
$1,000
$500
$250
$250
$250
$1,000
Claims
0
1
1
0
1
2
0
2
0
0
1
0
0
0
0
0
0
0
1
1
0
0
0
1
0
2
2
1
0
Claims Paid
$0
$2,805
$4,625
$0
$5,333
$6,486
$0
$12,252
$0
$0
$4,145
$0
$0
$0
$0
$0
$0
$0
$4,116
$4,201
$0
$0
$0
$5,516
$0
$9,882
$10,181
$5,597
$0
598
790
785
723
582
504
643
565
641
757
500
469
740
709
727
577
724
538
763
681
577
613
680
624
712
664
763
744
510
639
702
646
611
602
671
736
797
512
766
551
736
670
590
600
628
701
710
23
50
51
50
34
29
22
20
22
43
36
23
36
44
32
57
40
26
46
40
31
48
37
31
40
56
42
36
26
29
58
43
37
24
46
44
53
24
52
24
32
28
22
38
40
39
41
$250
$250
$250
$1,000
$250
$1,000
$250
$250
$500
$1,000
$250
$250
$1,000
$500
$250
$250
$250
$250
$250
$2,500
$2,500
$2,500
$250
$1,000
$1,000
$500
$250
$1,000
$1,000
$250
$1,000
$250
$250
$250
$250
$250
$250
$500
$2,500
$500
$500
$500
$250
$1,000
$250
$250
$2,500
2
1
0
0
0
0
0
1
2
0
0
0
0
2
0
0
0
2
2
0
0
0
0
1
0
1
0
1
1
1
0
1
2
0
1
1
2
1
1
1
0
0
0
0
1
1
2
$10,951
$4,161
$0
$0
$0
$0
$0
$5,597
$13,839
$0
$0
$0
$0
$9,381
$0
$0
$0
$12,817
$12,055
$0
$0
$0
$0
$3,393
$0
$3,971
$0
$5,199
$4,129
$4,375
$0
$4,918
$9,609
$0
$3,729
$3,974
$12,654
$4,580
$4,365
$3,168
$0
$0
$0
$0
$5,112
$2,862
$3,351
626
570
519
834
640
702
583
645
608
671
678
555
568
570
425
547
716
619
703
610
582
510
739
573
726
750
768
779
681
615
831
717
565
659
738
777
561
746
757
797
524
390
514
733
646
538
526
25
53
21
56
53
36
32
54
30
40
40
53
20
25
19
27
49
49
47
26
46
24
43
23
35
44
46
53
58
55
55
39
36
30
33
48
54
32
45
58
21
31
41
50
27
37
47
$250
$250
$250
$1,000
$500
$250
$250
$250
$1,000
$500
$500
$250
$250
$500
$250
$500
$500
$250
$500
$2,500
$2,500
$1,000
$250
$250
$2,500
$250
$500
$250
$250
$1,000
$250
$2,500
$250
$250
$250
$500
$500
$250
$1,000
$1,000
$250
$500
$500
$250
$250
$250
$250
1
2
1
0
0
0
1
2
0
2
0
2
1
0
0
0
0
1
0
1
0
0
1
1
1
0
2
0
0
0
0
0
2
1
0
1
0
0
1
2
0
0
1
1
0
1
0
$3,970
$8,728
$4,597
$0
$0
$0
$4,950
$17,849
$0
$11,867
$0
$11,056
$5,599
$0
$0
$0
$0
$5,466
$0
$5,933
$0
$0
$5,951
$5,441
$3,792
$0
$11,640
$0
$0
$0
$0
$0
$9,326
$6,433
$0
$7,009
$0
$0
$6,042
$10,123
$0
$0
$4,990
$4,899
$0
$5,593
$0
696
584
636
558
718
694
550
646
752
326
689
819
542
482
605
580
653
531
576
676
788
755
531
779
317
610
608
553
709
343
614
763
519
658
703
397
514
739
711
628
426
368
824
618
603
575
520
46
39
24
53
55
26
26
26
52
23
33
51
23
55
38
24
43
25
42
46
55
48
48
47
43
51
35
37
35
37
30
58
38
47
41
42
43
37
43
20
41
32
58
27
42
25
22
$250
$250
$250
$500
$250
$500
$1,000
$250
$250
$1,000
$250
$250
$250
$500
$1,000
$1,000
$250
$2,500
$250
$250
$500
$250
$250
$250
$250
$250
$250
$250
$2,500
$250
$250
$250
$250
$500
$1,000
$250
$250
$500
$500
$2,500
$500
$1,000
$1,000
$250
$250
$250
$250
0
0
0
1
0
2
0
0
0
0
0
1
0
0
0
0
0
0
0
1
0
1
1
1
1
0
0
0
0
1
1
0
0
0
0
0
0
0
1
1
0
2
1
0
0
0
2
$0
$0
$0
$4,771
$0
$11,624
$0
$0
$0
$0
$0
$4,980
$0
$0
$0
$0
$0
$0
$0
$4,508
$0
$4,859
$4,841
$5,628
$5,295
$0
$0
$0
$0
$5,494
$5,490
$0
$0
$0
$0
$0
$0
$0
$5,180
$4,637
$0
$7,556
$4,247
$0
$0
$0
$9,060
501
426
671
646
662
640
622
534
794
606
641
612
306
719
580
551
655
738
625
521
628
374
834
563
638
534
564
619
555
532
440
544
644
527
790
580
786
639
676
394
607
396
571
661
713
551
20
33
27
20
52
22
39
24
51
26
21
27
27
38
35
49
40
46
27
20
23
31
50
22
41
22
24
27
27
52
21
31
30
24
55
20
56
24
34
18
25
26
24
36
35
22
$500
$250
$250
$500
$1,000
$250
$250
$250
$500
$250
$2,500
$500
$250
$250
$250
$250
$500
$1,000
$250
$250
$250
$250
$500
$500
$2,500
$250
$1,000
$500
$250
$250
$250
$2,500
$250
$250
$1,000
$250
$250
$250
$250
$250
$250
$1,000
$250
$500
$500
$500
1
0
0
0
1
1
2
0
0
0
1
0
0
0
1
0
0
2
1
0
0
0
0
0
0
0
0
2
0
1
2
1
0
2
0
0
0
1
0
0
0
0
1
2
1
1
$4,563
$0
$0
$0
$4,725
$4,951
$9,998
$0
$0
$0
$3,213
$0
$0
$0
$4,998
$0
$0
$10,998
$4,509
$0
$0
$0
$0
$0
$0
$0
$0
$7,294
$0
$5,824
$15,283
$4,856
$0
$8,953
$0
$0
$0
$4,041
$0
$0
$0
$0
$5,274
$11,059
$4,399
$4,539
Identify below 10 questions you might want to
answer regarding this data set.
1
2
3
4
5
6
7
8
9
10
Answer 2 of the questions from the previous sheet. Provide
statistical output and your answer in a text box.
Be sure to identify which questions you are answering.
Copy the data set to this sheet.
Create a random number — =rand().
Sort the data set from smallest RANDOM NUMBER to largest LARGEST RANDOM NUMBER
the random numbers will change when you click on the sort button. This is not a problem.
Copy the CREDIT SCORE for the 80 applicants with the smallest random number to Column P.
For the 80 values in Column P, calculate the percentage of data within 1, 2, and 3 standard
deviations of the mean. Note: use the mean and standard deviation of the data in Column P.
Use a pivot table based on the data in Column P to construct a ‘histogram’.
Are your percentages consistent with what you would expect given your histogram?
DOM NUMBER. Note:
ot a problem.
ber to Column P.
standard
ta in Column P.
Copy the data set to this sheet.
Create a random number — =rand().
Sort the data set from smallest RANDOM NUMBER to largest LARGEST RANDOM NUMBER
the random numbers will change when you click on the sort button.
Delete the last 116 rows. This will leave you with 100 rows.
Create a pivot table on this sheet for MARITAL STATUS and CLAIMS in column R below the heading.
Answer the 5 questions to the right.
PIVOT TABLE
DOM NUMBER. Note:
mn R below the heading.
If an applicant
that
a. the applicant is
b. the applicant is married an
c. the applicant is not marrie
d. the applicant is not
Are the events
applicant is randomly selected from your data set, what is the probability
a. the applicant is married?
b. the applicant is married and has 0 claims?
c. the applicant is not married and has 2 claims?
d. the applicant is not married if applicant has at least 1 claim?
Are the events MARITAL STATUS and CLAIMS independent?
Copy the data set to this sheet.
Create a random number — =rand().
Sort the data set from smallest RANDOM NUMBER to largest LARGEST RANDOM NUMBER
the random numbers will change when you click on the sort button.
Delete the last 150 rows. This will leave you with 66 rows.
Construct a 95% confidence interval to estimate the population mean INCOME. Interpret the
interval you obtain.
Does your interval contain the mean INCOME from the entire data set?
OM NUMBER. Note:
. Interpret the
Copy the data set to this sheet.
Create a random number — =rand().
Sort the data set from smallest RANDOM NUMBER to largest LARGEST RANDOM NUMBER
random numbers will change when you click on the sort button.
Delete the last 150 rows. This will leave you with 66 rows.
Conduct a hypothesis test to determine if the population mean INCOME is different for applicants
with HIGH SCHOOL and COLLEGE EDUCATION. Assume that population variances are equal.
What are the average INCOMES for applicants with HIGH SCHOOL and COLLEGE EDUCATION
entire data set? How does this relate to your findings from they hypothesis test?
OM NUMBER. Note: the
ferent for applicants
nces are equal.
EDUCATION for the

Purchase answer to see full
attachment