Question

In: Statistics and Probability

Science MajorsA bank has collected data on the opening balances of new home loans issued in...

Science MajorsA bank has collected data on the opening balances of new home loans issued in the final quarter of 2018. The dataset of 200 observations is contained in the file: Home Loans (THA 1).xlsx. Based on the data, answer the following questions.

Using the Pivot Tablein Excel, construct a frequency distribution tableof Opening Balance and a histogramdisplaying the table. Suggested class intervals: 0 – 200,000; 200,000 – 400,000, etc.

Obs Home Loan, Open. Bal.
1 544,723
2 686,461
3 422,582
4 373,228
5 369,600
6 353,011
7 259,339
8 236,532
9 496,713
10 1,052,824
11 454,077
12 806,701
13 482,691
14 920,000
15 444,720
16 780,000
17 377,858
18 366,611
19 283,918
20 216,536
21 291,182
22 776,145
23 375,723
24 403,552
25 342,989
26 191,632
27 800,350
28 268,200
29 519,466
30 293,192
31 414,739
32 352,222
33 258,587
34 228,633
35 329,476
36 324,013
37 239,375
38 489,659
39 279,174
40 457,456
41 409,731
42 1,332,051
43 367,738
44 331,450
45 364,676
46 472,790
47 332,199
48 525,831
49 393,803
50 291,081
51 469,585
52 363,128
53 360,221
54 290,435
55 452,734
56 493,088
57 362,044
58 232,913
59 367,991
60 451,662
61 495,394
62 473,753
63 245,583
64 212,484
65 495,633
66 348,539
67 380,756
68 395,000
69 554,626
70 346,094
71 482,000
72 279,040
73 431,405
74 712,740
75 479,868
76 632,612
77 334,365
78 290,458
79 583,572
80 311,145
81 406,683
82 234,646
83 236,695
84 239,475
85 877,764
86 559,089
87 278,475
88 1,005,103
89 341,572
90 235,907
91 347,033
92 452,277
93 542,540
94 859,707
95 426,113
96 451,640
97 275,643
98 461,818
99 273,911
100 332,744
101 625,000
102 313,953
103 246,979
104 556,798
105 223,566
106 434,694
107 330,051
108 528,349
109 296,459
110 673,742
111 255,977
112 497,893
113 173,452
114 364,564
115 423,801
116 412,745
117 344,350
118 375,594
119 576,789
120 384,224
121 454,949
122 429,826
123 372,453
124 375,593
125 502,794
126 426,347
127 448,161
128 223,132
129 459,372
130 522,012
131 297,186
132 343,947
133 648,363
134 121,147
135 288,658
136 269,492
137 1,310,311
138 167,636
139 377,545
140 564,538
141 1,113,770
142 442,390
143 258,433
144 433,609
145 256,726
146 249,058
147 258,709
148 244,828
149 581,815
150 344,052
151 331,641
152 303,224
153 328,384
154 187,425
155 312,951
156 476,582
157 951,336
158 284,487
159 400,050
160 536,902
161 439,255
162 266,541
163 263,611
164 442,662
165 557,690
166 375,426
167 337,402
168 240,806
169 367,070
170 621,110
171 317,486
172 266,651
173 466,251
174 304,040
175 297,971
176 199,448
177 340,568
178 236,054
179 500,615
180 187,737
181 330,010
182 1,340,394
183 365,058
184 396,187
185 375,523
186 435,712
187 426,242
188 407,575
189 301,040
190 380,631
191 280,846
192 393,315
193 217,536
194 457,019
195 180,584
196 636,068
197 247,106
198 1,458,183
199 434,295
200 462,356

Solutions

Expert Solution

Select the data and using insert--->pivot table

do this

get this

drag the home loan, Opn bal. field to row label ans sum values

click on the sum of home and pick value filed setting

make sure that the summarize by is count

right click on a number under row labels and choose group

enter these values

get this

this is the frequency distribution table (change the column title to Frequency and re-lable the classes (for example 0-199999 to 0-200000)   if needed)

Opening Balance Frequency
0-199999 8
200000-399999 104
400000-599999 65
600000-799999 10
800000-999999 6
1000000-1199999 3
1200000-1399999 3
1400000-1600000 1

To get the histogram, select the data under Frequency and choose insert--->column

format the graph as needed


Related Solutions

Science MajorsA bank has collected data on the opening balances of new home loans issued in...
Science MajorsA bank has collected data on the opening balances of new home loans issued in the final quarter of 2018. The dataset of 200 observations is contained in the file: Home Loans (THA 1).xlsx. Based on the data, answer the following questions. An observation is a suspected outlier if it is more than 1.5 times the inter-quartile range (1.5 ´IQR)below Q1 or above Q3. Calculate the lower and upper ‘fences’ for this data and report the total number outliers...
Science MajorsA bank has collected data on the opening balances of new home loans issued in...
Science MajorsA bank has collected data on the opening balances of new home loans issued in the final quarter of 2018. The dataset of 200 observations is contained in the file: Home Loans (THA 1).xlsx. Based on the data, answer the following questions. By comparing Q2 and the sample mean comment on the skewness of the data. Explain your answer briefly. Obs Home Loan, Open. Bal. 1 544,723 2 686,461 3 422,582 4 373,228 5 369,600 6 353,011 7 259,339...
Chase Bank account division has collected data on the age of credit accounts. The data collected...
Chase Bank account division has collected data on the age of credit accounts. The data collected indicate that the age of the accounts follows a normal distribution with mean 18 years and standard deviation 6 years. a. What proportion of the accounts are between 20 and 32 years old? b. What is the number of years in which 75% of all accounts are above?
Science Majors The Science Department of a a American university collected data on the grade point...
Science Majors The Science Department of a a American university collected data on the grade point average (GPA) of its science majors at the end of their first year of study to investigate their academic performance. The dataset of 224 observations is contained in the file: GPA (THA 1).xlsx. Based on the data, answer the following questions. Using the Pivot Table in Excel, construct a frequency distribution table of GPA and a histogram displaying the table. Suggested class intervals: 1...
A bank has $850,000 in assets to allocate among investments in bonds, home mortgages, car loans,...
A bank has $850,000 in assets to allocate among investments in bonds, home mortgages, car loans, and personal loans. Bonds are expected to produce a return of 10%, mortgages 8.5%, car loans 9.5%, and personal loans 12.5% To make sure the portfolio is not too risky, the bank wants to restrict personal loans to no more than 30% of the total portfolio. The bank also wants to ensure that more money is invested in mortgages than in personal loans. It...
David's Landscaping has collected data on home values (in thousands of $) and expenditures (in thousands...
David's Landscaping has collected data on home values (in thousands of $) and expenditures (in thousands of $) on landscaping with the hope of developing a predictive model to help marketing to potential new clients. Data for 14 households may be found in the file Landscape. Home Value ($1,000) Landscaping Expenditures ($1,000) 242 8.1 321 10.8 198 12.2 340 16.2 300 15.6 400 18.9 800 23.5 200 9.5 521 17.5 547 22.0 437 12.1 464 13.5 635 17.9 356 13.9...
David's Landscaping has collected data on home values (in thousands of $) and expenditures (in thousands...
David's Landscaping has collected data on home values (in thousands of $) and expenditures (in thousands of $) on landscaping with the hope of developing a predictive model to help marketing to potential new clients. Data for 14 households may be found in the file Landscape. Home Value ($1,000) Landscaping Expenditures ($1,000) 242 8.1 321 10.8 198 12.2 340 16.2 300 15.6 400 18.9 800 23.5 200 9.5 521 17.5 547 22.0 437 12.1 464 13.5 635 17.9 356 13.9...
The manager of the commercial mortgage department of a large bank has collected data during the...
The manager of the commercial mortgage department of a large bank has collected data during the past two years concerning the number of commercial mortgages approved per week. The results from these two years ​(104 weeks) are shown to the right. a. Compute the expected number of mortgages approved per week. b. Compute the standard deviation. c. What is the probability that there will be more than one commercial mortgage approved in a given​ week? Number_Approved   Frequency 0   12 1  ...
The stated interest rate (or APR) that a bank charges on its home loans is 9.00%...
The stated interest rate (or APR) that a bank charges on its home loans is 9.00% and the effective annual interest rate has been computed as 9.38%. Based on this information, interest is most likely being compounded on a:
The following are data on the number of loans approved by a bank per day for...
The following are data on the number of loans approved by a bank per day for 25 working days: 73 19 16 64 28 28 31 90 60 56 31 56 22 18 45 48 17 17 17 91 92 63 50 51 69 a. Calculate the average, median, quartile 1 and quartile 3 b. Calculate range, interquartile range, variance, standard deviation, and coefficient of variation. c. Draw a box plot, give a conclusion
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT