5. Use the data in and description of BUSI1013Case.xls (from Unit 1 Exercises Question 6) to answer this question. a. Perform a statistical test to see whether the average deposits of members after the pilot is the same among the five branches. You will need to reformat the data, i.e., create a column of data for each of the five branches if you use Excel Data Analysis Addin. Source of Variation Sum of Squares Degrees of Freedom Mean Square F Treatment 93.21871 4 23.30468 1.384372 Error 3551.998 211 16.83411 Total 3645.216 215 Using Degrees of Freedom of 4, 211 and Fstatistic of 1.38 provides a Pvalue of 0.24195892 (using an online calculator). Since Reject H o if pvalue a ( 0.05 ) ,then we must fail reject the null hypothesis SUMMARY Groups Count Sum Branch 1 65 1564.8 Average 24.0738 5 Branch 2 40 982.4 24.56 Branch 3 27 707.5 26.2037 Branch 4 20 481.6 Branch 5 138 3393 24.08 24.5869 6 Variance 19.7172 7 16.3229 7 13.3203 7 19.7827 4 15.7113 6 2 24.5924.70 24.0824.70 2+5 26.224.70 2 +5 24.5624.70 2+5 2 24.124.70 +5 MSTR 5 ( .36 ) +5 ( .0196 ) +5 ( 2.25 ) +5 ( 0.3844 )+5 ( 0.0121 ) 4 1.8+ 0.098+11.25+1.922+0.0605 15.3105 = =3.782625(MSTR) 4 4 MSE= 1262.08+636.45+ 346.32+375.82+2152.27 4772.94 = =16.47( MSE ) 285 285 f= b. ( 651 ) 19.72+ ( 401 ) 16.32+ ( 271 ) 13.32+(201)19.78+(1381)15.71 2905 MSTR 3.78 = =0.229508 MSE 16.47 Perform a statistical test to see whether the average increase in loans of members is the same among the five branches. (You need to create a new variable called average increase in loans. Also, if you use the Excel Data Analysis Addin, you will need to reformat the data.) Team Case Study Legend and Coding: Gender : 0 = Male; 1 = Female Age: Age of customers Income: Personal income in thousands Deposit Before: Amount of deposits before the pilot; in thousands Deposit After: Amount of deposits after the pilot; in thousands Loan Before: Amount of loans before the pilot; in thousands Loan After: Amount of loans after the pilot; in thousands Recommend Before: Before the pilot, whether member would recommend the Credit Union to their friends and family; 1 Recommend After: After the pilot, whether member would recommend the Credit Union to their friends and family; 1 = Raw Data can be found under the "Raw Data" Tab!! on to their friends and family; 1 = Yes, 0 = No to their friends and family; 1 = Yes, 0 = No Branch1 Loans Before Loans After 35.2 48.1 54.3 62.4 38.5 51.1 50.6 63.3 39.2 45.5 43.5 60.6 50.7 55.1 50.3 48.8 42.5 55.9 50.2 58.7 46.0 53.0 48.7 54.3 48.8 62.0 40.8 50.1 54.6 51.3 41.9 48.7 39.9 43.2 45.9 47.0 50.2 61.4 49.1 50.1 46.6 61.6 51.5 58.1 47.5 55.7 49.4 56.0 38.0 62.3 46.7 49.9 43.1 53.5 45.6 61.4 40.1 55.7 46.3 49.5 35.8 47.1 49.5 49.6 48.5 54.7 53.2 60.5 46.2 49.2 44.5 57.1 38.1 50.7 50.1 56.0 46.8 50.2 46.8 46.2 46.6 62.7 47.5 61.3 39.5 56.6 35.6 45.1 49.7 55.9 44.0 43.4 40.3 57.9 37.7 47.6 Branch 2 Loans Before Loans After 51.2 63.4 46.1 54.5 43.4 44.7 44.1 57.3 50.2 55.7 39.1 46.5 35.6 49.6 49.3 44.2 37.4 60.6 49.3 56.5 52.9 49.0 50.0 62.8 37.7 61.4 53.0 50.6 45.7 44.7 36.9 44.7 43.4 50.1 41.2 59.2 54.9 54.8 55.0 55.4 36.8 45.7 38.6 59.1 37.1 55.2 49.9 59.5 47.2 45.9 46.0 47.2 39.9 58.4 53.9 58.1 35.5 63.5 54.3 63.0 36.8 61.0 42.0 47.4 50.5 44.4 50.3 63.7 44.7 49.7 37.6 58.0 35.3 48.8 39.8 57.2 48.7 63.5 51.5 43.6 Branch 3 Loans Before Loans After 50.4 54.9 42.9 59.0 52.2 57.2 44.7 52.2 40.6 59.9 38.2 48.2 46.6 59.5 47.0 58.3 53.5 58.6 36.5 56.6 47.7 62.1 45.8 58.9 44.5 57.8 38.3 48.6 54.3 49.2 54.5 52.4 44.5 62.9 42.4 56.3 52.3 62.6 50.7 45.9 43.7 61.0 54.5 52.6 38.7 45.6 44.5 56.8 52.5 53.3 53.0 47.3 48.6 58.7 Loans Before 53.0 51.3 45.2 36.8 35.4 46.7 50.1 40.1 48.2 49.5 39.4 45.5 39.3 44.5 39.9 44.2 39.0 63.4 56.7 47.5 50.5 56.1 57.8 61.2 58.7 45.3 51.5 62.9 46.2 50.1 47.0 48.3 54.3 46.4 Branch 4 Loans Before Loans After 44.3 52.5 54.5 62.7 48.1 49.9 54.9 45.2 41.7 48.2 51.7 53.7 37.1 62.7 39.7 46.2 42.3 58.1 39.6 60.1 53.4 48.3 45.9 56.2 42.8 48.4 46.1 51.1 41.0 57.5 43.8 49.8 36.3 51.7 35.6 56.1 51.6 44.8 49.9 44.3 Branch 5 Loans Before Loans After 48.6 61.4 52.9 45.3 49.5 56.5 49.9 58.7 40.9 54.4 40.2 43.3 45.2 52.0 42.9 55.0 37.6 49.5 42.5 60.7 48.0 46.4 36.4 55.5 38.8 59.2 38.6 44.8 41.4 43.4 54.0 47.5 37.6 59.6 39.7 44.6 45.1 55.0 41.3 47.0 45.6 48.2 53.0 54.3 54.6 59.3 51.8 58.2 41.8 62.1 50.9 47.8 37.6 44.0 54.3 45.2 50.2 58.1 48.1 45.8 46.9 54.5 54.1 50.8 49.0 57.0 37.4 47.7 55.0 63.3 53.0 59.9 42.5 46.9 46.3 54.2 45.5 60.3 39.0 53.1 50.5 61.7 38.0 53.2 46.3 47.4 52.8 44.8 35.0 43.9 39.7 45.3 44.9 45.7 51.8 43.6 49.3 53.3 40.6 44.8 38.2 50.7 43.1 46.4 48.2 49.4 42.9 46.2 39.6 43.5 42.6 39.9 45.4 49.5 38.6 43.8 53.7 47.8 40.0 52.7 50.1 51.2 51.6 48.2 41.5 37.4 45.9 49.5 53.2 45.0 36.2 52.1 51.4 47.3 36.2 44.9 45.0 39.8 37.4 50.9 50.5 46.1 48.1 48.9 37.4 36.2 50.6 52.1 60.7 57.6 53.5 57.2 62.1 50.2 53.1 44.0 45.0 50.8 56.4 57.8 61.7 51.2 61.7 49.3 52.4 50.5 46.7 50.5 46.1 62.9 55.3 48.8 62.5 53.0 51.5 44.5 63.5 62.5 53.7 43.0 44.6 59.5 50.4 51.3 45.3 53.6 57.1 59.1 61.8 62.2 48.0 54.3 57.3 52.0 45.5 62.0 50.2 38.1 42.7 54.6 38.3 41.1 35.5 53.7 40.4 39.9 54.9 52.70 51.20 42.70 39.60 52.30 51.70 47.30 49.80 48.40 45.80 51.70 39.60 42.70 35.90 51.50 47.30 50.00 50.40 54.80 35.80 46.60 36.30 42.10 51.10 40.90 43.70 48.10 54.90 53.80 45.3 48.0 50.3 47.0 43.7 46.7 44.0 57.8 61.9 45.0 49.3 56.80 62.00 45.60 56.20 45.50 62.20 59.60 52.90 54.90 46.60 54.70 44.00 59.70 59.10 48.90 60.40 58.30 44.70 59.80 46.70 43.50 49.80 62.80 53.00 58.50 56.00 56.00 57.50 48.10