Household1
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
Family Size Location
4
2
4
3
4
4
4
1
3
4
1
3
3
3
2
3
2
2
1
4
1
1
1
3
6
1
4
4
1
1
2
4
3
2
3
2
5
4
3
1
3
3
3
1
3
2
3
2
6
1
7
1
4
1
4
4
1
1
4
4
4
4
5
4
2
2
5
4
2
3
2
3
4
4
1
1
2
3
3
4
6
2
2
3
6
2
1
1
5
3
5
2
Ownership
1
0
1
1
0
1
0
0
1
0
0
0
1
0
0
0
1
1
0
1
0
1
1
1
0
0
1
0
1
1
0
0
1
0
1
1
1
0
0
0
1
0
0
1
0
1
Income
$93,564
$60,640
$39,246
$102,242
$60,715
$46,890
$72,122
$53,739
$76,416
$22,091
$36,384
$34,806
$127,474
$52,889
$43,657
$46,295
$72,649
$106,553
$63,253
$48,369
$61,763
$78,300
$71,653
$79,808
$68,808
$71,800
$80,392
$36,835
$71,211
$59,655
$46,719
$41,942
$154,916
$45,123
$56,750
$67,131
$43,640
$34,150
$97,682
$48,634
$75,512
$47,673
$77,470
$39,991
$51,533
$53,338
Monthly Payment
$1,262
$827
$629
$1,108
$675
$577
$576
$977
$1,589
$613
$601
$681
$1,009
$1,059
$848
$501
$1,573
$1,533
$530
$1,222
$689
$1,242
$1,086
$1,500
$991
$848
$1,223
$603
$1,514
$893
$562
$680
$1,467
$953
$964
$991
$729
$643
$647
$349
$1,040
$1,240
$1,314
$796
$696
$793
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
2
5
2
2
4
2
2
4
4
2
1
3
3
3
4
4
3
1
3
3
3
3
2
2
4
3
1
2
2
2
3
4
1
2
2
5
4
1
9
5
4
2
2
2
4
1
2
2
1
2
1
4
4
2
3
1
2
3
2
4
2
2
1
1
1
3
2
4
4
3
2
3
2
1
3
2
3
1
3
1
1
1
3
1
4
4
1
1
4
2
4
4
2
4
1
1
1
1
0
0
1
1
0
1
0
1
0
1
0
1
1
1
0
1
0
1
0
1
1
0
1
1
1
0
1
0
1
1
1
1
1
0
0
1
0
0
1
0
1
0
0
$76,963
$56,220
$173,706
$96,192
$59,621
$44,437
$55,665
$59,555
$56,441
$110,645
$43,482
$112,446
$48,319
$139,807
$74,460
$51,758
$48,893
$45,869
$55,362
$114,934
$74,636
$28,378
$79,123
$119,247
$24,493
$83,073
$75,865
$84,746
$66,346
$57,073
$159,107
$50,118
$59,579
$68,563
$74,972
$64,085
$108,918
$46,342
$48,762
$76,432
$119,526
$43,765
$74,486
$50,309
$33,646
$45,038
$62,974
$1,603
$1,304
$1,295
$1,751
$617
$520
$1,048
$963
$991
$989
$735
$699
$619
$1,088
$724
$959
$676
$1,377
$770
$1,278
$615
$644
$902
$1,588
$1,136
$1,187
$1,078
$1,280
$1,914
$711
$1,537
$818
$1,161
$1,008
$1,049
$640
$1,112
$560
$648
$1,203
$1,186
$490
$1,051
$997
$606
$984
$765
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
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
3
2
3
2
2
1
3
3
2
1
2
2
4
3
2
2
2
3
3
2
3
3
3
4
1
1
2
4
2
3
1
4
2
4
1
1
4
4
2
4
4
4
2
5
4
4
4
3
2
1
2
1
2
2
2
1
4
1
3
2
3
2
4
4
3
3
3
3
1
2
2
4
2
4
3
2
3
3
2
2
4
1
2
2
2
1
2
2
1
3
4
2
2
2
0
1
1
1
1
1
1
1
0
0
1
1
1
1
0
0
1
1
0
1
0
1
1
1
0
1
0
1
1
1
0
0
1
0
1
0
1
1
1
1
1
1
0
0
0
1
0
$56,437
$68,806
$129,211
$88,859
$41,427
$26,910
$87,636
$147,215
$97,646
$24,859
$49,809
$89,772
$57,734
$61,458
$78,960
$55,575
$35,502
$46,389
$46,022
$39,320
$88,816
$43,179
$126,469
$48,586
$30,557
$45,993
$66,866
$70,304
$55,654
$83,669
$35,105
$86,809
$64,154
$42,032
$48,064
$51,372
$109,665
$118,377
$85,051
$121,986
$118,870
$102,042
$59,159
$53,082
$65,307
$138,791
$63,546
$568
$1,453
$1,136
$810
$1,377
$1,028
$1,707
$1,731
$490
$509
$834
$1,293
$1,142
$933
$1,134
$895
$829
$1,780
$847
$1,259
$653
$909
$1,321
$679
$601
$1,689
$602
$1,435
$1,287
$1,306
$960
$1,740
$1,088
$799
$1,434
$887
$903
$831
$1,307
$1,355
$1,300
$1,420
$996
$334
$1,830
$1,103
$568
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
187
3
5
2
1
6
3
3
3
1
3
1
3
3
4
5
3
8
2
2
3
1
2
2
3
4
10
3
5
1
8
3
5
3
4
6
1
2
1
1
2
4
1
2
3
2
4
1
3
1
3
4
1
1
4
3
4
3
4
2
3
3
3
3
2
1
3
1
2
4
3
3
2
4
2
4
4
1
1
4
1
1
3
3
1
2
3
4
3
2
3
3
2
1
1
1
0
1
0
1
0
1
1
1
1
1
1
0
0
1
0
1
0
1
0
1
0
1
1
1
0
1
1
1
1
1
0
1
1
0
0
1
1
1
0
0
1
0
1
1
1
1
$73,861
$76,802
$72,534
$27,700
$67,154
$80,933
$37,599
$74,320
$27,377
$110,068
$24,992
$116,571
$35,403
$31,878
$58,389
$45,417
$90,947
$68,785
$31,519
$76,825
$60,069
$61,823
$49,744
$40,742
$101,510
$55,723
$93,507
$30,372
$39,578
$95,156
$84,272
$65,498
$93,190
$117,620
$40,953
$26,610
$46,249
$60,240
$31,400
$39,292
$47,109
$67,184
$39,773
$71,134
$130,387
$36,170
$52,448
$773
$892
$956
$728
$1,331
$797
$1,058
$1,217
$815
$670
$579
$1,009
$418
$719
$573
$610
$820
$931
$1,218
$1,184
$1,434
$595
$1,734
$1,403
$1,534
$405
$1,548
$799
$953
$1,042
$1,510
$405
$1,676
$1,123
$954
$999
$1,133
$989
$1,030
$566
$674
$756
$540
$994
$1,137
$1,185
$1,121
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
234
1
3
6
3
2
2
1
5
2
2
1
4
4
1
3
3
3
3
5
4
6
2
3
1
2
2
4
3
3
4
4
2
3
3
2
3
1
5
2
5
4
3
1
2
2
2
1
3
3
1
4
4
1
1
1
4
1
3
4
1
4
2
4
4
1
1
4
1
2
3
2
1
2
4
1
2
4
4
2
1
4
2
3
1
3
2
2
2
1
3
2
4
4
4
1
0
1
1
1
0
1
1
1
0
0
0
1
0
1
0
0
1
0
0
1
1
0
1
1
0
0
1
1
0
0
1
0
0
1
0
1
0
1
1
0
1
0
1
0
1
1
$52,847
$87,039
$72,043
$52,434
$32,806
$127,160
$29,805
$59,501
$55,099
$119,879
$49,358
$41,346
$46,265
$28,837
$96,017
$68,714
$44,840
$80,491
$118,372
$28,255
$62,196
$96,709
$87,205
$48,914
$33,018
$74,330
$64,045
$75,208
$94,868
$77,700
$51,313
$126,882
$46,816
$38,705
$58,666
$32,792
$43,847
$53,918
$85,665
$130,706
$110,956
$73,192
$28,835
$67,005
$69,880
$57,460
$37,924
$1,038
$1,001
$854
$1,093
$771
$931
$1,161
$1,295
$780
$992
$562
$784
$1,626
$569
$1,576
$428
$519
$1,619
$888
$690
$1,039
$1,585
$1,078
$1,153
$871
$1,076
$655
$1,503
$1,050
$671
$497
$1,705
$875
$474
$1,669
$861
$847
$553
$1,802
$1,380
$1,262
$1,984
$793
$914
$534
$724
$653
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
281
3
3
4
5
1
4
3
2
1
2
3
1
1
1
2
2
6
5
4
1
5
4
1
2
1
3
3
3
3
2
2
3
3
1
4
4
1
3
3
4
2
1
2
5
4
1
3
2
3
2
2
2
4
2
2
2
3
3
4
1
4
1
3
1
2
4
1
1
4
3
2
1
1
2
2
4
2
2
1
3
3
3
4
4
1
1
3
4
2
1
4
2
2
2
1
1
1
1
1
1
1
1
0
0
0
0
0
0
1
0
1
1
0
0
1
0
1
1
0
0
1
0
0
1
1
1
0
0
0
0
0
1
1
0
1
1
1
0
1
1
1
$65,800
$79,786
$49,699
$122,193
$76,800
$25,853
$30,225
$84,675
$64,179
$53,779
$76,774
$37,154
$32,125
$18,276
$58,303
$97,420
$88,973
$167,182
$35,176
$34,204
$144,147
$57,582
$41,628
$39,367
$36,838
$133,214
$41,069
$149,441
$63,834
$59,486
$58,305
$102,142
$76,648
$29,407
$43,538
$40,253
$39,987
$88,108
$60,357
$70,373
$35,343
$75,737
$86,424
$37,741
$86,820
$63,825
$105,213
$727
$635
$1,516
$814
$1,237
$948
$1,117
$1,068
$1,432
$707
$719
$657
$597
$478
$1,266
$622
$1,007
$1,253
$355
$838
$1,213
$765
$1,732
$1,233
$621
$1,159
$1,493
$1,166
$776
$933
$1,171
$1,138
$578
$857
$934
$510
$538
$1,436
$1,055
$475
$556
$1,596
$1,032
$572
$980
$1,346
$1,476
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
327
328
3
2
1
4
3
6
10
4
4
4
2
4
1
3
3
2
2
1
1
1
4
4
2
4
2
2
3
3
1
3
3
1
2
1
2
2
1
1
3
3
3
4
3
7
5
1
3
4
1
1
3
3
3
3
3
4
2
4
3
1
2
4
2
2
1
2
3
1
2
3
4
1
3
1
1
2
1
3
2
3
4
3
3
4
3
4
4
1
1
2
1
2
3
2
0
1
0
1
1
1
0
0
1
1
0
0
1
1
0
1
1
1
1
1
1
1
1
0
0
0
0
1
1
1
0
1
0
1
1
0
1
1
0
0
1
0
1
0
1
0
0
$77,195
$110,447
$25,886
$34,884
$36,015
$37,691
$85,465
$90,335
$58,637
$84,886
$51,857
$41,197
$72,515
$104,286
$72,632
$99,352
$130,060
$56,610
$59,321
$35,430
$76,598
$80,712
$84,309
$44,423
$86,208
$46,873
$86,009
$93,026
$59,708
$96,655
$45,068
$66,770
$62,699
$53,283
$81,627
$69,316
$33,940
$37,523
$50,111
$34,195
$49,728
$71,188
$88,204
$52,676
$142,095
$18,119
$123,946
$1,120
$908
$820
$783
$1,150
$925
$616
$766
$584
$1,141
$585
$797
$1,085
$1,292
$484
$1,700
$926
$1,002
$1,664
$1,029
$1,217
$1,062
$753
$389
$887
$627
$854
$1,469
$1,070
$1,601
$500
$1,658
$1,060
$974
$1,235
$729
$1,027
$1,829
$400
$549
$1,378
$523
$1,470
$694
$1,138
$1,452
$1,687
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
1
1
3
5
6
5
4
2
4
4
2
3
4
3
3
3
5
3
2
3
6
3
2
2
3
2
1
1
2
2
4
5
3
1
6
3
8
5
7
1
3
5
5
3
3
6
3
4
2
2
3
4
2
1
4
1
3
4
3
3
3
4
1
2
4
2
3
1
4
4
2
1
4
4
4
1
3
1
3
3
3
2
3
4
4
3
4
4
4
3
3
4
1
4
0
0
1
1
0
1
1
0
1
1
0
1
1
0
1
0
1
1
1
0
1
0
0
1
0
0
0
1
0
0
1
1
1
0
1
0
0
0
1
0
0
0
0
1
0
1
0
$27,721
$72,949
$81,612
$48,304
$47,956
$126,003
$65,717
$27,791
$75,551
$49,156
$48,577
$61,794
$61,764
$63,499
$37,378
$107,937
$121,113
$34,760
$93,156
$47,847
$39,773
$45,995
$71,842
$99,323
$124,842
$44,395
$32,697
$42,058
$87,227
$86,553
$76,001
$82,389
$27,085
$29,289
$126,809
$83,452
$56,334
$35,366
$65,830
$33,087
$59,901
$73,801
$56,897
$37,375
$50,855
$51,537
$65,746
$723
$1,522
$1,787
$752
$586
$1,521
$1,251
$669
$919
$838
$628
$1,614
$1,066
$1,286
$1,252
$748
$990
$493
$1,660
$1,286
$853
$603
$369
$1,044
$574
$402
$623
$884
$831
$941
$1,250
$1,269
$965
$833
$1,800
$732
$644
$788
$722
$633
$577
$398
$778
$985
$680
$821
$383
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
2
1
3
2
2
3
5
1
5
4
4
2
1
3
3
1
3
9
3
5
4
1
2
3
3
3
2
1
5
8
3
3
2
3
1
2
5
3
3
3
3
3
5
4
3
1
5
3
4
1
1
1
1
1
4
2
1
4
1
4
4
1
1
2
1
2
2
3
3
2
4
2
1
2
1
1
4
1
4
1
4
1
4
4
1
2
4
1
4
3
1
1
2
2
1
0
1
0
1
0
1
1
1
1
0
0
0
0
1
0
1
1
1
1
0
1
1
1
1
1
1
1
1
0
1
0
1
0
1
1
0
0
1
0
1
1
1
0
0
1
1
$32,848
$35,289
$50,155
$104,117
$68,714
$53,232
$84,674
$39,187
$101,743
$86,132
$37,382
$133,325
$26,351
$65,847
$94,808
$32,420
$147,155
$57,921
$59,947
$106,495
$59,943
$39,862
$151,964
$65,290
$115,901
$167,600
$112,609
$47,969
$83,607
$49,119
$67,928
$38,448
$64,621
$49,166
$68,299
$39,353
$38,017
$75,549
$60,590
$57,661
$39,080
$34,431
$70,750
$65,859
$59,589
$56,151
$111,665
$720
$457
$1,068
$797
$1,434
$930
$873
$948
$1,424
$614
$558
$1,038
$623
$549
$1,033
$1,075
$1,272
$1,391
$1,498
$1,070
$712
$1,472
$875
$713
$1,065
$1,827
$943
$849
$1,489
$493
$941
$547
$961
$588
$1,271
$495
$762
$1,054
$1,167
$525
$1,103
$805
$866
$921
$1,083
$1,128
$1,248
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
1
2
5
5
6
4
5
1
1
4
1
2
4
3
5
4
2
5
4
2
5
4
1
4
1
2
2
4
2
1
2
1
3
1
3
3
3
4
2
4
5
1
5
3
2
5
2
4
4
4
4
4
1
1
4
1
2
3
2
4
2
2
3
3
3
1
1
2
4
3
3
1
2
4
2
4
3
2
1
1
4
3
4
3
4
1
4
1
1
3
1
1
4
1
0
1
0
0
1
1
0
0
1
1
1
1
0
0
1
0
1
0
1
1
1
0
1
1
0
1
1
1
1
0
1
1
1
0
0
0
0
0
1
1
1
1
1
0
0
0
1
$22,380
$28,534
$35,879
$38,352
$41,335
$77,773
$81,276
$24,478
$57,395
$119,400
$42,313
$85,139
$29,996
$75,211
$83,078
$75,489
$33,105
$42,159
$62,697
$54,362
$85,294
$34,779
$54,374
$63,773
$47,570
$59,151
$58,914
$126,416
$16,971
$47,244
$90,072
$55,487
$56,067
$25,713
$73,114
$65,785
$59,185
$42,384
$126,951
$25,713
$64,023
$53,113
$55,099
$47,628
$57,571
$28,390
$46,834
$746
$760
$658
$557
$959
$1,044
$490
$989
$1,473
$1,460
$883
$2,171
$760
$991
$747
$949
$976
$755
$1,302
$1,318
$1,439
$500
$804
$1,176
$918
$840
$618
$1,091
$807
$638
$1,317
$752
$988
$814
$812
$941
$743
$530
$1,585
$831
$1,067
$1,163
$924
$728
$1,033
$558
$1,081
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
3
5
5
1
5
4
1
1
3
5
3
3
3
5
3
2
2
3
2
4
3
1
3
4
5
4
5
2
2
2
3
3
2
1
2
4
4
3
4
3
2
4
1
3
3
3
2
3
1
3
2
3
2
3
2
3
3
1
3
4
4
4
0
1
1
1
0
0
1
0
1
1
0
1
1
1
0
1
0
1
1
0
0
1
0
1
0
0
0
0
0
0
0
$86,730
$156,293
$92,873
$62,071
$40,824
$42,426
$32,316
$33,699
$56,777
$116,010
$44,289
$56,375
$64,753
$54,966
$52,410
$88,270
$61,203
$77,550
$75,351
$76,828
$88,710
$73,980
$57,723
$97,868
$65,291
$57,391
$72,675
$55,857
$40,084
$47,492
$31,042
$960
$1,799
$1,147
$1,326
$453
$553
$790
$797
$1,705
$1,458
$626
$881
$1,014
$1,432
$888
$1,407
$622
$1,049
$1,084
$760
$778
$823
$745
$1,283
$699
$1,103
$931
$801
$538
$427
$746
Data background and variable definitions/details are found in the question file
Rent
Mortgage
Table 4: Table of Summary Measure for monthly payments ($)
Statistics
Mean
Median
Standard Deviation
Minimum
Maximum
Range
Count
First Quartile
Third Quartile
Interquartile Range (IQR)
Coefficient of Variation (CV)
Monthly rent payment
Monthly mortgage payment
monthly payments ($)
Excel Function Used for Monthly
mortgage payment (including cell
references)
Household Family Size Location
1
4
2
2
4
3
3
4
4
4
4
1
5
3
4
6
1
3
7
3
3
8
2
3
9
2
2
10
1
4
11
1
1
12
1
3
13
6
1
14
4
4
15
1
1
16
2
4
17
3
2
18
3
2
19
5
4
20
3
1
21
3
3
22
3
1
23
3
2
24
3
2
25
6
1
26
7
1
27
4
1
28
4
4
29
1
1
30
4
4
31
4
4
32
5
4
33
2
2
34
5
4
35
2
3
36
2
3
37
4
4
38
1
1
39
2
3
40
3
4
41
6
2
42
2
3
43
6
2
44
1
1
45
5
3
46
5
2
47
2
2
48
5
1
49
2
2
50
2
1
51
4
4
Ownership
1
0
1
1
0
1
0
0
1
0
0
0
1
0
0
0
1
1
0
1
0
1
1
1
0
0
1
0
1
1
0
0
1
0
1
1
1
0
0
0
1
0
0
1
0
1
1
1
1
1
0
Income
$93,564
$60,640
$39,246
$102,242
$60,715
$46,890
$72,122
$53,739
$76,416
$22,091
$36,384
$34,806
$127,474
$52,889
$43,657
$46,295
$72,649
$106,553
$63,253
$48,369
$61,763
$78,300
$71,653
$79,808
$68,808
$71,800
$80,392
$36,835
$71,211
$59,655
$46,719
$41,942
$154,916
$45,123
$56,750
$67,131
$43,640
$34,150
$97,682
$48,634
$75,512
$47,673
$77,470
$39,991
$51,533
$53,338
$76,963
$56,220
$173,706
$96,192
$59,621
Monthly Payment
$1,262
$827
$629
$1,108
$675
$577
$576
$977
$1,589
$613
$601
$681
$1,009
$1,059
$848
$501
$1,573
$1,533
$530
$1,222
$689
$1,242
$1,086
$1,500
$991
$848
$1,223
$603
$1,514
$893
$562
$680
$1,467
$953
$964
$991
$729
$643
$647
$349
$1,040
$1,240
$1,314
$796
$696
$793
$1,603
$1,304
$1,295
$1,751
$617
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
94
95
96
97
98
99
100
101
102
103
2
2
4
4
2
1
3
3
3
4
4
3
1
3
3
3
3
2
2
4
3
1
2
2
2
3
4
1
2
2
5
4
1
9
5
4
2
2
2
4
1
2
3
2
3
2
2
1
3
3
2
1
4
2
3
1
2
3
2
4
2
2
1
1
1
3
2
4
4
3
2
3
2
1
3
2
3
1
3
1
1
1
3
1
4
4
1
1
4
2
4
4
2
4
3
2
1
2
1
2
2
2
1
4
0
1
1
0
1
0
1
0
1
0
1
1
1
0
1
0
1
0
1
1
0
1
1
1
0
1
0
1
1
1
1
1
0
0
1
0
0
1
0
1
0
0
0
1
1
1
1
1
1
1
0
0
$44,437
$55,665
$59,555
$56,441
$110,645
$43,482
$112,446
$48,319
$139,807
$74,460
$51,758
$48,893
$45,869
$55,362
$114,934
$74,636
$28,378
$79,123
$119,247
$24,493
$83,073
$75,865
$84,746
$66,346
$57,073
$159,107
$50,118
$59,579
$68,563
$74,972
$64,085
$108,918
$46,342
$48,762
$76,432
$119,526
$43,765
$74,486
$50,309
$33,646
$45,038
$62,974
$56,437
$68,806
$129,211
$88,859
$41,427
$26,910
$87,636
$147,215
$97,646
$24,859
$520
$1,048
$963
$991
$989
$735
$699
$619
$1,088
$724
$959
$676
$1,377
$770
$1,278
$615
$644
$902
$1,588
$1,136
$1,187
$1,078
$1,280
$1,914
$711
$1,537
$818
$1,161
$1,008
$1,049
$640
$1,112
$560
$648
$1,203
$1,186
$490
$1,051
$997
$606
$984
$765
$568
$1,453
$1,136
$810
$1,377
$1,028
$1,707
$1,731
$490
$509
104
105
106
107
108
109
110
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
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
2
2
4
3
2
2
2
3
3
2
3
3
3
4
1
1
2
4
2
3
1
4
2
4
1
1
4
4
2
4
4
4
2
5
4
4
4
3
5
2
1
6
3
3
3
1
3
1
3
3
4
5
1
3
2
3
2
4
4
3
3
3
3
1
2
2
4
2
4
3
2
3
3
2
2
4
1
2
2
2
1
2
2
1
3
4
2
2
2
3
1
3
4
1
1
4
3
4
3
4
2
3
3
3
1
1
1
1
0
0
1
1
0
1
0
1
1
1
0
1
0
1
1
1
0
0
1
0
1
0
1
1
1
1
1
1
0
0
0
1
0
1
0
1
0
1
0
1
1
1
1
1
1
0
0
1
$49,809
$89,772
$57,734
$61,458
$78,960
$55,575
$35,502
$46,389
$46,022
$39,320
$88,816
$43,179
$126,469
$48,586
$30,557
$45,993
$66,866
$70,304
$55,654
$83,669
$35,105
$86,809
$64,154
$42,032
$48,064
$51,372
$109,665
$118,377
$85,051
$121,986
$118,870
$102,042
$59,159
$53,082
$65,307
$138,791
$63,546
$73,861
$76,802
$72,534
$27,700
$67,154
$80,933
$37,599
$74,320
$27,377
$110,068
$24,992
$116,571
$35,403
$31,878
$58,389
$834
$1,293
$1,142
$933
$1,134
$895
$829
$1,780
$847
$1,259
$653
$909
$1,321
$679
$601
$1,689
$602
$1,435
$1,287
$1,306
$960
$1,740
$1,088
$799
$1,434
$887
$903
$831
$1,307
$1,355
$1,300
$1,420
$996
$334
$1,830
$1,103
$568
$773
$892
$956
$728
$1,331
$797
$1,058
$1,217
$815
$670
$579
$1,009
$418
$719
$573
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
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
3
8
2
2
3
1
2
2
3
4
10
3
5
1
8
3
5
3
4
6
1
2
1
1
2
4
1
2
3
2
4
1
1
3
6
3
2
2
1
5
2
2
1
4
4
1
3
3
3
3
5
4
3
2
1
3
1
2
4
3
3
2
4
2
4
4
1
1
4
1
1
3
3
1
2
3
4
3
2
3
3
2
1
1
3
3
1
4
4
1
1
1
4
1
3
4
1
4
2
4
4
1
1
4
0
1
0
1
0
1
0
1
1
1
0
1
1
1
1
1
0
1
1
0
0
1
1
1
0
0
1
0
1
1
1
1
1
0
1
1
1
0
1
1
1
0
0
0
1
0
1
0
0
1
0
0
$45,417
$90,947
$68,785
$31,519
$76,825
$60,069
$61,823
$49,744
$40,742
$101,510
$55,723
$93,507
$30,372
$39,578
$95,156
$84,272
$65,498
$93,190
$117,620
$40,953
$26,610
$46,249
$60,240
$31,400
$39,292
$47,109
$67,184
$39,773
$71,134
$130,387
$36,170
$52,448
$52,847
$87,039
$72,043
$52,434
$32,806
$127,160
$29,805
$59,501
$55,099
$119,879
$49,358
$41,346
$46,265
$28,837
$96,017
$68,714
$44,840
$80,491
$118,372
$28,255
$610
$820
$931
$1,218
$1,184
$1,434
$595
$1,734
$1,403
$1,534
$405
$1,548
$799
$953
$1,042
$1,510
$405
$1,676
$1,123
$954
$999
$1,133
$989
$1,030
$566
$674
$756
$540
$994
$1,137
$1,185
$1,121
$1,038
$1,001
$854
$1,093
$771
$931
$1,161
$1,295
$780
$992
$562
$784
$1,626
$569
$1,576
$428
$519
$1,619
$888
$690
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
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
6
2
3
1
2
2
4
3
3
4
4
2
3
3
2
3
1
5
2
5
4
3
1
2
2
2
1
3
3
4
5
1
4
3
2
1
2
3
1
1
1
2
2
6
5
4
1
5
4
1
2
1
1
2
3
2
1
2
4
1
2
4
4
2
1
4
2
3
1
3
2
2
2
1
3
2
4
4
4
2
3
2
2
2
4
2
2
2
3
3
4
1
4
1
3
1
2
4
1
1
4
3
2
1
1
1
0
1
1
0
0
1
1
0
0
1
0
0
1
0
1
0
1
1
0
1
0
1
0
1
1
1
1
1
1
1
1
1
1
0
0
0
0
0
0
1
0
1
1
0
0
1
0
1
1
0
$62,196
$96,709
$87,205
$48,914
$33,018
$74,330
$64,045
$75,208
$94,868
$77,700
$51,313
$126,882
$46,816
$38,705
$58,666
$32,792
$43,847
$53,918
$85,665
$130,706
$110,956
$73,192
$28,835
$67,005
$69,880
$57,460
$37,924
$65,800
$79,786
$49,699
$122,193
$76,800
$25,853
$30,225
$84,675
$64,179
$53,779
$76,774
$37,154
$32,125
$18,276
$58,303
$97,420
$88,973
$167,182
$35,176
$34,204
$144,147
$57,582
$41,628
$39,367
$36,838
$1,039
$1,585
$1,078
$1,153
$871
$1,076
$655
$1,503
$1,050
$671
$497
$1,705
$875
$474
$1,669
$861
$847
$553
$1,802
$1,380
$1,262
$1,984
$793
$914
$534
$724
$653
$727
$635
$1,516
$814
$1,237
$948
$1,117
$1,068
$1,432
$707
$719
$657
$597
$478
$1,266
$622
$1,007
$1,253
$355
$838
$1,213
$765
$1,732
$1,233
$621
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
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
3
3
3
3
2
2
3
3
1
4
4
1
3
3
4
2
1
2
5
4
1
3
3
2
1
4
3
6
10
4
4
4
2
4
1
3
3
2
2
1
1
1
4
4
2
4
2
2
3
3
1
3
1
2
2
4
2
2
1
3
3
3
4
4
1
1
3
4
2
1
4
2
2
2
4
1
1
3
3
3
3
3
4
2
4
3
1
2
4
2
2
1
2
3
1
2
3
4
1
3
1
1
2
1
0
1
0
0
1
1
1
0
0
0
0
0
1
1
0
1
1
1
0
1
1
1
0
1
0
1
1
1
0
0
1
1
0
0
1
1
0
1
1
1
1
1
1
1
1
0
0
0
0
1
1
1
$133,214
$41,069
$149,441
$63,834
$59,486
$58,305
$102,142
$76,648
$29,407
$43,538
$40,253
$39,987
$88,108
$60,357
$70,373
$35,343
$75,737
$86,424
$37,741
$86,820
$63,825
$105,213
$77,195
$110,447
$25,886
$34,884
$36,015
$37,691
$85,465
$90,335
$58,637
$84,886
$51,857
$41,197
$72,515
$104,286
$72,632
$99,352
$130,060
$56,610
$59,321
$35,430
$76,598
$80,712
$84,309
$44,423
$86,208
$46,873
$86,009
$93,026
$59,708
$96,655
$1,159
$1,493
$1,166
$776
$933
$1,171
$1,138
$578
$857
$934
$510
$538
$1,436
$1,055
$475
$556
$1,596
$1,032
$572
$980
$1,346
$1,476
$1,120
$908
$820
$783
$1,150
$925
$616
$766
$584
$1,141
$585
$797
$1,085
$1,292
$484
$1,700
$926
$1,002
$1,664
$1,029
$1,217
$1,062
$753
$389
$887
$627
$854
$1,469
$1,070
$1,601
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
3
1
2
1
2
2
1
1
3
3
3
4
3
7
5
1
3
1
1
3
5
6
5
4
2
4
4
2
3
4
3
3
3
5
3
2
3
6
3
2
2
3
2
1
1
2
2
4
5
3
1
6
3
2
3
4
3
3
4
3
4
4
1
1
2
1
2
3
2
4
2
2
3
4
2
1
4
1
3
4
3
3
3
4
1
2
4
2
3
1
4
4
2
1
4
4
4
1
3
1
3
3
3
2
0
1
0
1
1
0
1
1
0
0
1
0
1
0
1
0
0
0
0
1
1
0
1
1
0
1
1
0
1
1
0
1
0
1
1
1
0
1
0
0
1
0
0
0
1
0
0
1
1
1
0
1
$45,068
$66,770
$62,699
$53,283
$81,627
$69,316
$33,940
$37,523
$50,111
$34,195
$49,728
$71,188
$88,204
$52,676
$142,095
$18,119
$123,946
$27,721
$72,949
$81,612
$48,304
$47,956
$126,003
$65,717
$27,791
$75,551
$49,156
$48,577
$61,794
$61,764
$63,499
$37,378
$107,937
$121,113
$34,760
$93,156
$47,847
$39,773
$45,995
$71,842
$99,323
$124,842
$44,395
$32,697
$42,058
$87,227
$86,553
$76,001
$82,389
$27,085
$29,289
$126,809
$500
$1,658
$1,060
$974
$1,235
$729
$1,027
$1,829
$400
$549
$1,378
$523
$1,470
$694
$1,138
$1,452
$1,687
$723
$1,522
$1,787
$752
$586
$1,521
$1,251
$669
$919
$838
$628
$1,614
$1,066
$1,286
$1,252
$748
$990
$493
$1,660
$1,286
$853
$603
$369
$1,044
$574
$402
$623
$884
$831
$941
$1,250
$1,269
$965
$833
$1,800
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
3
8
5
7
1
3
5
5
3
3
6
3
2
1
3
2
2
3
5
1
5
4
4
2
1
3
3
1
3
9
3
5
4
1
2
3
3
3
2
1
5
8
3
3
2
3
1
2
5
3
3
3
3
4
4
3
4
4
4
3
3
4
1
4
3
4
1
1
1
1
1
4
2
1
4
1
4
4
1
1
2
1
2
2
3
3
2
4
2
1
2
1
1
4
1
4
1
4
1
4
4
1
2
4
0
0
0
1
0
0
0
0
1
0
1
0
1
0
1
0
1
0
1
1
1
1
0
0
0
0
1
0
1
1
1
1
0
1
1
1
1
1
1
1
1
0
1
0
1
0
1
1
0
0
1
0
$83,452
$56,334
$35,366
$65,830
$33,087
$59,901
$73,801
$56,897
$37,375
$50,855
$51,537
$65,746
$32,848
$35,289
$50,155
$104,117
$68,714
$53,232
$84,674
$39,187
$101,743
$86,132
$37,382
$133,325
$26,351
$65,847
$94,808
$32,420
$147,155
$57,921
$59,947
$106,495
$59,943
$39,862
$151,964
$65,290
$115,901
$167,600
$112,609
$47,969
$83,607
$49,119
$67,928
$38,448
$64,621
$49,166
$68,299
$39,353
$38,017
$75,549
$60,590
$57,661
$732
$644
$788
$722
$633
$577
$398
$778
$985
$680
$821
$383
$720
$457
$1,068
$797
$1,434
$930
$873
$948
$1,424
$614
$558
$1,038
$623
$549
$1,033
$1,075
$1,272
$1,391
$1,498
$1,070
$712
$1,472
$875
$713
$1,065
$1,827
$943
$849
$1,489
$493
$941
$547
$961
$588
$1,271
$495
$762
$1,054
$1,167
$525
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
3
3
5
4
3
1
5
1
2
5
5
6
4
5
1
1
4
1
2
4
3
5
4
2
5
4
2
5
4
1
4
1
2
2
4
2
1
2
1
3
1
3
3
3
4
2
4
5
1
5
3
2
1
4
3
1
1
2
2
4
4
4
4
4
1
1
4
1
2
3
2
4
2
2
3
3
3
1
1
2
4
3
3
1
2
4
2
4
3
2
1
1
4
3
4
3
4
1
4
1
1
3
1
1
1
1
1
0
0
1
1
0
1
0
0
1
1
0
0
1
1
1
1
0
0
1
0
1
0
1
1
1
0
1
1
0
1
1
1
1
0
1
1
1
0
0
0
0
0
1
1
1
1
1
0
0
$39,080
$34,431
$70,750
$65,859
$59,589
$56,151
$111,665
$22,380
$28,534
$35,879
$38,352
$41,335
$77,773
$81,276
$24,478
$57,395
$119,400
$42,313
$85,139
$29,996
$75,211
$83,078
$75,489
$33,105
$42,159
$62,697
$54,362
$85,294
$34,779
$54,374
$63,773
$47,570
$59,151
$58,914
$126,416
$16,971
$47,244
$90,072
$55,487
$56,067
$25,713
$73,114
$65,785
$59,185
$42,384
$126,951
$25,713
$64,023
$53,113
$55,099
$47,628
$57,571
$1,103
$805
$866
$921
$1,083
$1,128
$1,248
$746
$760
$658
$557
$959
$1,044
$490
$989
$1,473
$1,460
$883
$2,171
$760
$991
$747
$949
$976
$755
$1,302
$1,318
$1,439
$500
$804
$1,176
$918
$840
$618
$1,091
$807
$638
$1,317
$752
$988
$814
$812
$941
$743
$530
$1,585
$831
$1,067
$1,163
$924
$728
$1,033
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
5
2
3
5
5
1
5
4
1
1
3
5
3
3
3
5
3
2
2
3
2
4
3
1
3
4
5
4
5
2
2
2
3
4
1
3
2
1
2
4
4
3
4
3
2
4
1
3
3
3
2
3
1
3
2
3
2
3
2
3
3
1
3
4
4
4
0
1
0
1
1
1
0
0
1
0
1
1
0
1
1
1
0
1
0
1
1
0
0
1
0
1
0
0
0
0
0
0
0
$28,390
$46,834
$86,730
$156,293
$92,873
$62,071
$40,824
$42,426
$32,316
$33,699
$56,777
$116,010
$44,289
$56,375
$64,753
$54,966
$52,410
$88,270
$61,203
$77,550
$75,351
$76,828
$88,710
$73,980
$57,723
$97,868
$65,291
$57,391
$72,675
$55,857
$40,084
$47,492
$31,042
$558
$1,081
$960
$1,799
$1,147
$1,326
$453
$553
$790
$797
$1,705
$1,458
$626
$881
$1,014
$1,432
$888
$1,407
$622
$1,049
$1,084
$760
$778
$823
$745
$1,283
$699
$1,103
$931
$801
$538
$427
$746
Data background and variable definitions/details are found in
the question file
Rent
Mortgage
Table 4: Table of Summary Measure for monthly payments ($)
Statistics
Mean
Median
Standard Deviation
Minimum
Maximum
Range
Count
First Quartile
Third Quartile
Interquartile Range (IQR)
Coefficient of Variation (CV)
Monthly rent payment
Monthly mortgage payment
ly payments ($)
Excel Function Used for Monthly
mortgage payment (including cell
references)
MCD2080 Business Statistics EXCEL Exercise 1
Descriptive Statistics Presentation Using Excel
This assignment is marked out of 84.25 marks.
It is designed to test your understanding of Descriptive Statistics (including Graphical Presentations using Excel
and interpretations) as well as Regression Analysis (including generating regression output using Excel and
interpretations).
Due Date: by 5 pm Sunday of Teaching Week 6 (27th November 2022)
DATA AND IMPORTANCE OF THIS EXERCISE
The file MCD2080_Exer_T3 22.xlsx contains data for the Excel Exercise covering week 1 – 4 Lectures and week
1 – 5 Tutorials.
The aim of this exercise is for you to get familiar with excel tools and functions which you will require in future
studies at the university and even the outside world.
In addition, this gives you an opportunity to be able to interpret the descriptive statistics and regression analysis
you learn during the first few weeks in this unit.
This is a very important task in learning the basic Excel skills.
Data Background: Tongrey Households
You are a statistician in the department of bureau of statistics who is interested in community welfare in the city of
Tongrey. To study the welfare, you take a survey of 500 households that is randomly sampled. Among the information
collected is the number of individuals in the household, the sector where they live, if they reside in their own home
or rented house, their income and either their monthly mortgage or rent payment.
Please answer the following questions and use Microsoft Excel where necessary.
The data for each household includes the following variables:
▪
▪
Family Size:
Location:
▪
▪
▪
Ownership:
Income:
Monthly Payment:
the number of people in the household.
1 = southwest (SW) sector, 2 = northwest (NW) sector,
3 = northeast (NE) sector and 4 = southeast (SE) sector.
0 = rent home, 1=own home.
total annual income of the household’s wage earners ($).
monthly mortgage payment or rent payment ($).
OUTPUT AND RESULTS
•
You are required to use Excel to generate the output.
•
It is recommended to create your charts and tables in the “Results (output)” worksheet provided.
−
To produce the descriptive (summary) statistics, use the excel functions you have learnt during the
tutorials (or Lecture).
−
However, it is also recommended to understand how to use Excel’s Data Analysis Toolpak to produce
summary statistics.
•
Make sure to save and organise your excel outputs (tables and the charts) in order to answer the questions on
Moodle.
•
Keep your work safe until you get final grading and feedback.
• Organise your tables and the charts well, otherwise poor presentation will result in a loss of marks.
HOW TO ANSWER QUESTIONS AND UPLOADING TABLES AND CHARTS TO QUESTIONS
Use the link in the Week 2 Tutorial section labelled “Excel Assignment” and follow the instructions. There are a
set of questions, and you should answer all before submitting your work. Below is just a guide on what you are
expected to do in answering the questions.
For the FULL INSTRUCTIONS please refer to the QUESTIONS section below.
There are 10 questions in this exercise.
o Question 1(a-e): Requires you to provide the correct answer out of the multiple choices provided.
Use the data set provided (MCD2080_Exer_T3 22.xlsx) to answer the rest of the questions
o Question 2: Requires you to rename and upload (pdf) “Table 2a &2b. Upload the table ONLY otherwise, you
lose marks. Hint: copy the Table to a word document and then save as pdf.
o Question 3: Requires you to rename and upload (pdf) “Graph 3a & 3b. Name and upload the Graph ONLY
otherwise, you lose marks. Hint: copy the Graph to a word document and then save as pdf.
o Question 4: Requires you to rename and upload (pdf) “Table 4”. Name and upload the table ONLY
otherwise, you lose marks. Hint: copy this Table to a word document, then organise it carefully and then save
as pdf.
o Question 5: Requires you to interpret the data distribution listed in the question. Be careful not to delete the
box provided to type your answers.
o Question 6: Requires you to construct pivot tables that you will upload on Moodle question 8 (pdf). Name
and upload the Tables 6a), Table 6b) & Table 6c) ONLY otherwise, you lose marks.
Hint: copy the Tables to a word document, then organise them and then save as pdf.
o Question 7: Requires you to rename and upload the table on Moodle. In addition, you are required to provide
interpretations of some proportions/probabilities from the pivot table. These are short answers where you
chose the correct “word(s)”.
o Question 8a): Requires you to rename and upload (pdf) “Graph 8. Name and upload the Graph ONLY
otherwise, you lose marks. Hint: copy the Graph to a word document and then save as pdf.
o Question 8b-d): Requires you to calculate and/or interpret the results as asked
o Question 9a): Requires you to rename and upload (pdf) “Table 9.” Name and upload the Table ONLY
otherwise, you lose marks. Hint: copy the Table to a word document and then save as pdf.
o Question 9b-d): Requires you to use 9a) to calculate state and/or interpret the results as asked in b – d.
o Question 10a): Requires you to rename and upload (pdf) “Table 10.” Name and upload the Table ONLY
otherwise, you lose marks. Hint: copy the Table to a word document and then save as pdf.
o Question 10b – f): Requires you to use the regression output in 10a) to answer questions b – f.
You can start answering the questions on Moodle BUT DO NOT SUBMIT the exercise UNTIL you answer
ALL questions to your best level.
You can go to any question AT ANY TIME using “NEXT” and “PREVIOUS” buttons.
Caution: When you Click SUBMIT the Exercise (QUIZ), you will not be able to change any question that
will be final.
To be able to score high marks,
o
you need to read all the instructions and questions carefully
MCD2080 T3 2022
2|P a g e
Questions
1. State the data type for each of the variables below, i.e., categorical (nominal or ordinal), numerical
(continuous or discrete). Explain your reasoning in each case.
a) Family Size
b) Location
c) Ownership
d) Income
e) Monthly Payment
[5 marks]
Use the data set provided (MCD2080_Assignment_T322.xlsx) to answer the rest of the questions
2. To start your analysis, first, you conduct some descriptive statistics on the monthly home payment for both
households who pay rent and those who own homes (pay mortgage).
To do this analysis you need to sort the data by ‘Ownership’. Once the data is sorted follow the steps below:
o
In the Results (Output) worksheet, cell A1 has the heading ‘Rent’ and cell C1, ‘Mortgage’.
o
Copy/paste the values for monthly payments for the people who rent in column A (i.e. under ‘Rent’) and
for the people who make monthly mortgage payments in column C (i.e. under ‘Mortgage’).
o
Use Excel’s Pivot Table function to create a percentage frequency (count) distribution table for each of
these variables. [Hint: use Count and NOT Sum]
o
For the Rent Payment variable, the class intervals (groups) should start at $300 and have a width of $200
and for the Mortgage Payment variable the class intervals should start at $400 and have a width of $200.
o
Show percentages correct to 2 decimal places.
o
Use the Tabular Format report layout and label your table accurately and informatively.
Name them Table 2a and Table 2b respectively and upload it to Moodle as required.
[6 marks]
3. Use the tables from Question 2 to construct percentage frequency histograms of Monthly Payment for each
ownership type.
Make sure you Hide/remove all field buttons from the graphs and ensure that the histograms are appropriately
labelled. Name them Graph 3a and Graph 3b respectively and upload to Moodle as required.
(Marks will be deducted for the poor presentation of your graph)
[8 marks]
MCD2080 T3 2022
3|P a g e
4. Obtain the indicated summary measures in the following table (Table 4), for the Monthly Rent Payments and
Monthly Mortgage Payments.
Report values to 2 decimal places. Name it Table 4 and upload it to Moodle as required.
Statistics
Table 4: Table of Summary Measure for monthly payments ($)
Excel Function Used for Monthly mortgage
Monthly rent Monthly mortgage
payment
payment
payment
(including cell references)
Mean
Median
Standard Deviation
Minimum
Maximum
Range
Count
First Quartile
Third Quartile
Interquartile Range (IQR)
Coefficient of Variation (CV)
[8.75 marks]
5.
Refer to your Questions 2, 3 & 4 results to describe the Monthly rent payment distribution. Discuss areas of:
a) Central location,
b) Variation, and
c) Shape.
[12.5 marks]
6.
Use Excel’s Pivot Table function to create a table of the frequency distribution of all households in
relation to Location (ROWS) by Ownership (COLUMNS).
Use the “Tabular Form” report layout.
(Hint: For this question use the original data and make sure your table values are “Count and
NOT Sum. Also, label variable categories as defined – see data background)
From this table create and label the following three tables, rounding off percentage values to 2 decimal
places:
o Table 6a: the frequency distribution with values shown as “% of Grand Total”.
o Table 6b: the frequency distribution with values shown as “% of Column Total”.
o Table 6c: the frequency distribution with values shown as “% of Row Total”.
[7.5 marks]
7.
Refer to Tables 6a – c in Question 6 to answer the following:
a) (i) What is the proportion of households that make a monthly rent payment?
(ii) What is the percentage of households that make a monthly mortgage payment and are from the NW
sector?
b) If a household was randomly selected from those who pay a monthly mortgage, what is the probability
that they are located in the NE sector?
c) For the households located in the SE sector, what is the proportion that pays monthly rent?
d) In the process of investigating if a household ownership is independent of the location,
(i) the probability of household paying monthly rent is supposed to be equal to the probability of a
household paying monthly rent given it is located in the NE sector? What are these two probabilities?
(ii) based on these probabilities, state if the ownership and location are independent and why?
[6 marks]
MCD2080 T3 2022
4|P a g e
For the next part of the analysis you will be analysing the relationship between Monthly payment ($)
and Annual income ($).
8.
9.
Use Excel to answer the following questions:
a) Create a scatterplot of the relationship between Monthly payment and Annual income.
Name it Chart 8 and upload it to Moodle as required
b) Describe the relationship between Monthly payment and Annual income from the scatterplot in part a).
c) Calculate the correlation of the relationship between Monthly payment and Annual income and interpret
the value
[7.5 marks]
Use Excel’s Data Analysis toolpak to
a) Run a linear regression to analyse the relationship between Monthly payment and Annual income.
Name it Table 9 and upload it to Moodle as required.
Use the regression output from part a) to:
b) Write down the estimated linear regression equation
c) State and interpret the estimated y-intercept
d) State and interpret the estimated slope
[12 marks]
Hint on writing equation for Question 9b:
10. Use Excel’s Data Analysis toolpak to
a) Run a linear regression to estimate the mean of the Monthly payment. Use a 99% confidence level.
Name it Table 10 and upload it to Moodle as required
Use the regression output produced in part a) to:
b) Write down the mean value (3 decimal places only)
c) Interpret the mean value
d) State and interpret the 99% confidence interval
e) Without calculations compare the precision between the 95% and 99% confidence intervals
f) Apart from the confidence level, state and explain other factors that affect the precision of this
confidence interval.
[11 marks]
MCD2080 T3 2022
5|P a g e
Essay Writing Service Features
Our Experience
No matter how complex your assignment is, we can find the right professional for your specific task. Achiever Papers is an essay writing company that hires only the smartest minds to help you with your projects. Our expertise allows us to provide students with high-quality academic writing, editing & proofreading services.
Free Features
Free revision policy
$10Free bibliography & reference
$8Free title page
$8Free formatting
$8How Our Dissertation Writing Service Works
First, you will need to complete an order form. It's not difficult but, if anything is unclear, you may always chat with us so that we can guide you through it. On the order form, you will need to include some basic information concerning your order: subject, topic, number of pages, etc. We also encourage our clients to upload any relevant information or sources that will help.
Complete the order form
Once we have all the information and instructions that we need, we select the most suitable writer for your assignment. While everything seems to be clear, the writer, who has complete knowledge of the subject, may need clarification from you. It is at that point that you would receive a call or email from us.
Writer’s assignment
As soon as the writer has finished, it will be delivered both to the website and to your email address so that you will not miss it. If your deadline is close at hand, we will place a call to you to make sure that you receive the paper on time.
Completing the order and download