1 Data 2 ld P Gender Job Title 3 4 5 123 Male eutenant, Fire Suppressic Base Pay 128808.87 OvertimePay 220909.48 Male Chief of Police 302578 Male ectronic Maintenance Te 6 4 Male Chief, Fire Department 111921 296943.01 0 146415.32 OtherPay 13126.31 18974.11 7 5 Male MT/Paramedic/Firefighte 126863.19 192424.49 78057.41 17816.59 17917.18 Benefits 44430.12 362844.66 69810.19 321552.11 53102.29 336393.73 72047.88 314759.6 44438.25 337204.86 TotalPay TotalPayBenefits Department 407274.78 Fire 391362.3 Other 389496.02 Not dep 386807.48 Other 381643.11 Other 8 6 Male Dept Head V 271607.74 0 19782.03 84681.82 291389.77 376071.59 Medicine 9 7 Female 10 8 Female 11 9 Female en Mgr, Public Trnsp Dep Dept Head V Captain 3 294000.17 0 287747.89 0 104404 0 12 10 Male 13 11 Male Asst Chf of Dept (Fire Dept attlion Chief, Fire Suppres 204032.52 85503.16 174822.47 118215.58 14 12 Female attlion Chief, Fire Suppres 174822.44 112731.28 0 5274.57 245999.41 26193.09 28845.78 28660.28 15 13 Female 16 14 Male 17 15 Female 18 16 Male 19 17 Male attlion Chief, Fire Suppres attlion Chief, Fire Suppres Assistant Deputy Chief 2 Transit Manager 2 Asst Med Examiner 176771.74 124413.13 174822.41 124573.5 98211.01 260528.26 20 18 Male Dep Chf of Dept (Fire Dept 244102.02 99623.51 22599.6 0 3394.9 24204.66 21 19 Male ecutive Contract Employ 232815.01 0 22 20 Male 23 21 Male 24 22 Female 25 23 Male Asst Med Examiner Assistant Deputy Chief 2 Captain, Fire Suppressior Deputy Chief 3 260528.15 223371 145659.03 263426.5 377.21 7415.52 125868.06 15803.39 36522.39 180516.68 200951.3 20400 20232.29 51564.01 18425.37 63426.03 30474.97 82001.94 294000.17 81824.37 293022.46 24287.23 350403.41 58486.1 315728.77 49648.09 321883.83 53249.56 316214 52320.9 55353.92 32381 327689.78 60285.44 299162.31 73017.12 284323.16 64702.72 288538.97 67667.09 284379.02 72286.35 279330.73 56202.98 45129.03 376002.11 Other 374846.83 Medicine 374690.64 Fire 374214.87 Fire 371531.92 Fire 369463.56 Fire 316988.26 369309.16 Fire 310968.31 366322.23 Fire 360070.78 Other 359447.75 Other 357340.28 Police 353241.69 Other 352046.11 Other 351617.08 Police 294212.55 350415.53 Other 302002.06 347131.09 Fire 0 22857.87 59265.59 286284.37 345549.96 Other 26 24 Female 27 25 Male 28 26 Male 29 27 Female 30 28 Male 31 29 Male 32 30 Female 33 31 Female 34 32 Male 35 33 Female 36 34 Male 37 35 Female Dep Chf of Dept (Fire Dept ecutive Contract Employ Captain, Fire Suppression Dept Head V Captain, Fire Suppressior Dept Head V Adm, SFGH Medical Cente Captain, Fire Suppression Assistant Deputy Chief 2 Asst Chf of Dept (Fire Dept Controller Captain 3 252423.6 8357.25 15742.21 249362.77 145659.03 0 115673.73 253649.8 147069.71 253435.69 113372.94 0 254878.82 0 124573.5 226475.05 208830.92 53895.92 0 54545.03 252210.73 0 108175.03 0 38 36 Male Mayor 260574.05 0 39 37 Female Captain 3 104404 0 40 38 Male Deputy Chief 3 263426.5 0 41 39 Male Deputy Chief 3 253094.51 42 40 Female Captain, Fire Suppression 145658.98 0 114513.29 13810.69 32610 6486 33012.58 6486 9486 130200.4 54967.53 20726.5 3486 203471.13 0 204290.09 12320.8 22206.4 28743.36 68783.23 80999.77 49571.51 293942.76 80660.61 260135.8 45978.15 293455.23 79477.91 259921.69 75003.33 264364.82 28907.03 308669.82 55722.16 281442.58 52943.62 284102.45 80986.55 255696.73 24941.13 311646.16 74698.5 260574.05 24688.54 308694.09 57576.73 275747.3 56561.05 275300.91 42919.16 288915.63 276523.06 345306.29 Other 263173.46 344173.23 Other 343514.27 340796.41 Fire Medicine 339433.38 339399.6 Fire Medicine 339368.15 Other 337576.85 Fire 337164.74 Other 337046.07 Fire 336683.28 Other 336587.29 335272.55 Fire Other 333382.63 Fire 333324.03 Other 331861.96 Other 331834.79 Fire 14 IZ 15 13 16 14 17 15 remare Female Male Female itution Chiei, rire suppres attlion Chief, Fire Suppres attlion Chief, Fire Suppres 1/4022.44 112/51. ZODOU.20 55249.50 310214 .50540 rife 176771.74 124413.13 174822.41 Assistant Deputy Chief 2 124573.5 18 16 Male 19 17 Male Transit Manager 2 Asst Med Examiner 98211.01 260528.26 20 18 Male 21 19 Male Dep Chf of Dept (Fire Dept ecutive Contract Employ 244102.02 232815.01 22 20 Male 23 21 Male 24 22 Female 25 23 Male Asst Med Examiner Assistant Deputy Chief 2 Captain, Fire Suppressior Deputy Chief 3 260528.15 223371 145659.03 263426.5 99623.51 22599.6 0 3394.9 24204.66 0 377.21 7415.52 125868.06 0 15803.39 36522.39 180516.68 200951.3 20400 20232.29 51564.01 52320.9 55353.92 32381 327689.78 60285.44 299162.31 73017.12 284323.16 64702.72 288538.97 67667.09 284379.02 316988.26 310968.31 369309.16 Fire 366322.23 Fire 360070.78 Other 359447.75 Other 357340.28 Police 353241.69 Other 352046.11 Other 18425.37 72286.35 279330.73 351617.08 Police 26 24 Female 27 25 Male 28 26 Male Dep Chf of Dept (Fire Dept ecutive Contract Employ Captain, Fire Suppressior 29 27 Female 30 28 Male 31 29 Male 32 30 Female 33 31 Female 34 32 Male 35 33 Female 36 34 Male 37 35 Female Dept Head V Captain, Fire Suppressior Dept Head V Adm, SFGH Medical Cente Captain, Fire Suppressior Assistant Deputy Chief 2 Asst Chf of Dept (Fire Dept Controller Captain 3 252423.6 249362.77 145659.03 253649.8 147069.71 253435.69 8357.25 0 115673.73 0 113372.94 0 254878.82 124573.5 226475.05 208830.92 0 53895.92 0 54545.03 252210.73 0 108175.03 0 63426.03 30474.97 22857.87 15742.21 13810.69 32610 6486 33012.58 6486 9486 130200.4 54967.53 20726.5 3486 203471.13 38 36 Male Mayor 260574.05 0 39 37 Female Captain 3 104404 0 40 38 Male Deputy Chief 3 263426.5 0 41 39 Male Deputy Chief 3 253094.51 0 42 40 Female Captain, Fire Suppressior 145658.98 43 41 Female Firefighter 109783.86 44 42 Female Anesthetist 227221.89 45 43 Male attlion Chief, Fire Suppres 176515.48 114513.29 160418.47 5693.99 74246.62 0 204290.09 12320.8 22206.4 28743.36 46 44 Female Captain 3 98747.52 1386.38 47 45 Male Asst Med Examiner 243271.98 0 48 46 Male Deputy Chief 3 263390.6 0 49 47 Male Dept Head V 243115.1 0 50 48 Male MT/Paramedic/Firefighte 128091.78 133645.42 51 49 Female Asst Chf of Dept (Fire Dept 204032.52 29768.35 52 50 Male attlion Chief, Fire Suppres 176515.48 71643.89 19873.8 16887.9 23772 203735.92 13708.15 819.01 3486 16130.24 34685.53 23352.06 56202.98 294212.55 45129.03 302002.06 59265.59 286284.37 68783.23 276523.06 80999.77 263173.46 49571.51 293942.76 80660.61 260135.8 45978.15 293455.23 79477.91 259921.69 75003.33 264364.82 28907.03 308669.82 55722.16 281442.58 52943.62 284102.45 80986.55 255696.73 24941.13 74698.5 24688.54 57576.73 56561.05 275300.91 42919.16 288915.63 41230.89 290076.13 78554.11 249803.78 53207.72 274534.1 23607.08 303869.82 68106.81 256980.13 60298.97 264209.61 77382.62 246601.1 43913.33 277867.44 53152.06 268486.4 47455.23 271511.43 350415.53 Other 347131.09 Fire 345549.96 Other 345306.29 Other 344173.23 Other 343514.27 340796.41 Fire Medicine 339433.38 339399.6 Fire Medicine 339368.15 Other 337576.85 Fire 337164.74 Other 337046.07 Fire 336683.28 Other 311646.16 336587.29 Fire 260574.05 335272.55 Other 308694.09 333382.63 Fire 275747.3 333324.03 Other 331861.96 Other 331834.79 Fire 331307.02 Fire 328357.89 Medicine 327741.82 Fire 327476.9 Fire 325086.94 Police 324508.58 Other 323983.72 Medicine 321780.77 Other 321638.46 Fire 318966.66 Fire K L M Charts Analysis 1. Descriptive statistic Instruction Bonus N P Q R S Instruction a. Frequency table b. Histogram c. Pie chart d. Cross-tabulation and double-column chart e. Boxplot (quantile, mean, median) f. Correlation 2. Sampling and estimation a. probabilistic sampling b. Confidence interval a. Hypothesis testing 3. Satistical inference 1. presents title, category name, and value when you make charts 2. use function, not just enter the value 3. put interpretation Feel free to include diverse analysis if you want to know. It will give you bonus 1A Department Frequency 1B Fire 20 MAX MIN Other 20 Medicine Police 6 3 1C male female Gender Frequency 31 19 38% Gender male female 62% 302578 98211.01 Base Pay Frequency Tasks 1-a. make and interpret frequency table about department (pivottable or countif function) 1-b. make and interpret histogram about base pay 1-c. make and interpret frequency table and piechart about gender (pivottable or countif function) 1-d. make and interpret cross-tabulation of department by gender (pivottable or countif function) and double-column chart based on the cross-tabulation 1-e. make and interpret boxplot about benefit by gender 1-f. calculate and interpret correlation between overtimepay and totalpay 2-a. 2-b. 3-a. conduct random sampling about total paybenefit (*size is 30 and the number of samples is 10), and make a histogram about the sampling distribution of mean) build and interpret a confidence interval on total paybenefit (at 95% level): hint - find standard deviation by using =STDEV.S, margin of error by using =CONFIDENCE.NORM, and sample mean by using =AVERAGE conduct hypothesis testing at 0.05 alpha to know whether the population mean of total pay benefit is less than 310000 (your expectation): hint - define hypothesis and test statistics, and write decision with interpretation Paper: it does not matter whether the tasks are in order, but make sure to write the task number when you write the answer 1A Department Frequency 1C Fire 20 Gender male Frequency 31 Other 20 female 19 Medicine 6 Police 3