Data from 240 to 280 in the Excel list. In the class, we analyzed bank loan applications. As a reminder, we had 3 data tables that contained loan application data, client locations, and client credit scores. Using this information as an input, complete the following tasks.
Task 1: Statistics
Each student should use only the assigned subset of data to complete this task. The data rows assigned to each student are published in the portal together with the Excel data file from the class. (see file Assignment 1 - task distribution - section 64) Calculate the measures of central tendencies for the credit score values: mean, mode, median, standard deviation, min, max
Task 2: Analysis in Excel
Using pivot tables in Excel, answer the following questions:
What is the average credit score of applicants in different cities?
What is the average distributed loan amount in different cities (per currency)?
Task 3: Hypothesis Testing
Using the same subset of data assigned to you, please answer the following scenario. Choose your preferred confidence level for hypothesis testing (5% is standard). Interpret the results. The manager in the lending department believes that on average the credit score of the bank applicants is greater than 650. You have access only to a limited number of applications (your subset of data) but need to answer if this can be true for the whole population (all applicants). For the same scenario, please calculate the confidence interval of the average credit score using your sample data. Submission format Please prepare in the memo format an MS Word document with answers to the questions above submitted through the portal. Up to 2 pages. For the calculations done in Excel make screenshots and include them directly in the memo. Paste B U v EAv % $ 9 Cell Styles v l Format v Sort & Find & Filter Set Analyze Sensitivity Data Show ToolPak Solver J280 X A 630 33U199872 239 530200040 240[ 530191517 241 530194251 242 530185789 242 530189466 244 530186100 245 530198061 246 530196039 247 530188779 248 530184150" 249 530183770 250 530191673 251 530186893 252 530185250 253 530184971 254 530188331 255 530187281 256 530188196 257 530181529 258 530183359 259 530179953 260 530179955 261 530194109 262 530191472 292 530190535 264 530198036 265 530188490 266 530196235 267 530205000" 268 530203847 269 530188158 270 530192496 the 530194357 272 530197836 530195512 224 530197328 275 530197014 276 530194184 530203234 278 530193352 279/ 530199746 280 530196399 530193881 fx 15 c 7.906107 2019-08-26| 2019-03-27 2019-08-28 2019-08-29 2019-03-30 2019-08-31 2019-09-01 2019-09-02 2019-09-03 2019-09-04 2019-09-05 2019-09-06 2019-09-07 2019-09-08 2019-09-09 2019-09-10 201909-11 2019-09-12 20190913 2019-09-14 201909-15 2019-09-16 201909-17 2019-09-18 20190919 2019-09-20 2019-09-21 2019-09-22 2019-0923 2019-09-24 20190925 2019-09-26 20190927" 2019-09-28 2019-0929 2019-09-30 2019-10-01 2019-10-02' 2019-10-03 2019-10-04 2019-10-05 2019-10-06 2019-10-07| D 0.cA6107 2019-09-10| E F 47000 U3U 20000|EUR 9350 USD 8980 USD 5000 EUR 5000 _EUR 14417 EUR 92000 USD 90000 EUR 22203 EUR 121636 USD 10700 EUR 10000 EUR 46000 EUR 130650 EUR 2800_EUR 4750 USD 5800 USD 70000 EUR 2100 EUR 11310 EUR 6400 USD 6400 EUR 48000 EUR 46000 USD 8024 EUR 3000 USD 40000 USD 155000 EUR 16000 EUR 6500EUR 9428 USD 10000 EUR 5046 EUR 20000 USD 2500 EUR 9000 USD 14000 USD 5000 EUR 15000 EUR 7400 EUR 12000 USD 14000 EUR 57000|EUR 498919 215368 417304 46899 437514 561862 221960 312462 12786 858520 54884 252319 409540 154998 842042 145352 368125 165165 858070 442788 856504 289896 289896 37124 538934 530242 416843 310134 107579 205994 126883 731351 859354 859946 860937 118469 549326 377633 254521 203231 608401 201764 23887* 7798| Prepared data + H san 7h/ 684|Yes 689 No 626 Yes 699 Yes 611 Yes 709 Yes 682 Yes 757 Yes 701Yes 806 No 643 Yes 679 Yes 723 Yes 775 Yes 670 Yes 645 Yes 654_Yes 680 No 669 No 622 Yes 672 Yes 672 Yes 714Yes 699 Yes 676 Yes 625 Yes 89 762 Yes 648Yes 645 Yes 632 Yes 660 Yes 603 Yes 665 Yes 657 No 687 Yes 668 Yes 687 Ye 645 Yes 667 Yes 693 Yes 695_ Yes 755|No K L M N Q R T U v surrey Richmond Port Moody Vancouver Surrey Richmond Port Moody, Port Moody Vancouver Surrey Richmond Port Moody Vancouver Surrey Richmond Port Moody Vancouver haung Richmond Port Moody Vancouver Surrey 2019-08-29 20190831 2019-09-02 2019-09-04 2019-09-08 2019-09-12 2019-09-18 2019-09-06 2019-09-08 2019-09-10 201909-12 2019-09-16 2019-09-20 2019-09-26 1 2019-09-15 201909-17 20190919 2019-09-21 2019-09-25 2019-09-29 2019-10-05 1 Nm 10 15 1 2 Richmond Port Moody Vancouver tauns Richmond Port Moody Vancouver 2019-09-23 2019-09-25 2019-09-27 2019-09-29 2019-10-03 2019-10-07 2019-10-13 Richmond Port Moody Vancouver Surrey Richmond Port Moody Vancouver Surrey Richmond Port Moody Vancouver Surrey Richmond 2019-10-01 2019-10-03 2019-10-05 2019-10-07 20191011 2019-1015 2019-10-21 1 2 10 15 4