Problem 3 A bank must prepare for a discrimination suit filed on behalf of female employees that claim females are paid less than male employees. The bank manager sampled employee files to see if he could build a useful model for predicting salary as a function of gender and other characteristics. For each employee1 the data includes salary {y1 in thousands of dollars}1 years experience [Yrs Exp}1 years prior experience {YrsPl'ior}. and Gender. The data is in the spreadsheet named Brink. a. {ti points} Since Gender is a categorical variable. construct the appropriate dummy variable in column E to indicate gender as female = l and male = fl. 1ilf'ou must use an \"IF" statement in the appropriate cell{s) to indicate the correct dummy value based on gender. b. {4 points} With cell H3 as the upper left corner of the output1 Use Excel Data Analysis tool to generate the full regression model. {Do not generate the residual plot]. c. {ti points) Based on the regression output from part b. shade in yellow the name of any 1' variable that appears signicant and should remain in the model. Also shade the corresponding trim and pvnlue. E F G H B D Question 3b: Regression output goes here: 1 y variable 2 Salary: current annual salary in thousands of dollars. 3 4 x variables 5 YrsExp: years experience with this Bbank 6 YrsPrior: number of years prior work experience at another bank 7 Gender: (categorical), 1 = Female, 0 = Male 8 Bank salary data Question 3a 9 YrsPrior Gender 10 Employee Salary YrsExp Male 11 32 6 1 Female 12 39.1 17 SW N H 15 Female 13 33.2 Female 14 30.6 11 0 Male 15 29 Female 30.5 0 16 Female 17 30 Male 18 co 27 11 Female 34 12 Female 10 29.5 JOMO O NOONO 11 12 Female Female 31.2 12 Female 34.7 13 Female Female 30 Female 16 31 Female 17 27 Female 18 29.6 Female 19 32.6 TOWN WOD OC Female 20 29.6 Female 31 21 29.5 Male 32 22 31 Female 33 23 28.5 Male 34 24 26.7 Male 35 25 30.75 Male 36 26 29.5 19 Female 37 27 42.2 Female 38 28 37.6 16 Female 39 29 34 15 40 30 33 7 Female Female 41 31 28.76 10 Female 42 32 35.4 14 Male 43 33 31 Female 44 34 38.8 21 45 35 34.3 17 Female 46 35 22 Female 36 47 2 Female 37 34.6 Female 48 38 28.5 39 14 Female 49 29.5 Male 50 40 30.5 CO On Female 51 41 34.2 Female 52 42 43.6 18 Female 53 43 33.5 10 OOO W O HOOK NO. Female 54 44 33 15 Female 55 45 45.3 21 Male 56 46 38.8 20 57 47 29.9 13 Female 58 48 31.2 Male CO Female 59 49 34 18A B C D F F G 60 50 30.45 5 0 Female 61 51 35.5 6 Male 62 52 34 7 10 Female 63 53 29.1 10 Female 64 54 29.65 11 Female 65 55 29.2 18 Female 66 56 29.8 12 Female 67 57 33.5 19 Female 68 58 34 11 Female 69 59 29.6 12 Female 70 60 34 21 Female 71 61 37.25 6 Female 72 62 33 9 Male 73 63 28.6 7 Female 74 64 36 8 Female OOO WOD HUI POO A SHOW OO OO OOO HOC 75 65 37.3 Female 76 66 29.9 10 Male 77 67 31.5 14 Female 78 68 41.4 8 Female 79 69 32.74 20 Female 80 70 33.5 6 Male 81 71 32 8 Female 82 72 30.8 12 Female 83 73 42 6 Female 84 74 34 7 Male 85 75 32.5 19 Female 86 76 31.7 12 Female 87 77 36.5 6 Male 88 78 33 7 Male 89 79 31.2 11 Female 90 80 34 11 Female 91 81 33 9 Female 92 82 33.9 7 Female 93 83 39 6 Female 94 84 34.92 15 Male 95 85 39 6 Male 96 86 34 11 Female 97 87 31.9 9 Female 98 88 37 6 Male 99 89 34 Male 100 90 36.4 6 Female 101 91 38.2 18 Female 102 92 35.3 18 O NOWUGOOWOOOONONOKNOKWOGOUPOOOOOO Female 103 93 34.5 6 Male 104 94 30.5 15 Female 105 95 30 5 Male 106 96 37.3 11 Female 107 97 40.2 8 Female 108 98 35.5 8 Male 109 99 35 14 Female 110 100 38 7 Female 111 101 35.3 12 Female 112 102 34.1 17 Female 113 103 43.2 7 Female 114 104 36.1 18 Female 115 105 34.6 10 Female 116 106 36 8 Male 117 107 36.2 10 Female 118 108 37.5A C D E F G 119 109 41 7 12 Female 120 110 35.6 13 Female 121 111 39.8 8 Female 122 112 41.3 14 Female 123 113 42.5 12 Female 124 114 45.8 7 Female 125 115 34.9 8 Female 126 116 41.5 7 Male 127 117 38 8 Female 128 118 35 9 Female 129 119 40 9 Female 130 120 36 8 Male 131 121 33.7 12 Female 132 122 36.3 8 Male 133 123 38 6 Female 134 124 39.5 7 Female 135 125 36.3 10 Female 136 126 32.5 11 Female 137 127 37 15 Female 138 128 32.6 6 Female 139 129 36 Female 140 130 35 6 Female 141 131 43.6 6 Female 142 132 33.8 11 Female 143 133 35.3 24 Female 144 134 42.4 19 Female 145 135 39.5 8 Male 146 136 43.5 28 NOOOA NOOD HWAOW WWWOUH NUDHOOGOOGOOHONGONAOOOOOOOOO WAUO Female 147 137 42 9 Male 148 138 40.3 24 Female 149 139 44 9 Male 150 140 40.66 28 Female 151 141 39.7 16 Female 152 142 45 9 Female 153 143 43.9 10 Female 154 144 38 11 Female 155 145 39.02 8 Female 156 146 44.5 8 Male 157 147 41 7 Male 158 148 44 9 Male 159 149 44 9 Male 160 150 42.5 8 Female 161 151 40.26 10 Female 162 152 44.5 8 Male 163 153 35.5 16 Female 164 154 42.5 9 Male 165 155 44 10 Female 166 156 45 9 Male 167 157 44.4 18 Female 168 158 38 20 Female 169 159 41.8 7 Male 170 160 45.5 26 Male 171 161 42.5 8 Male 172 162 44 6 Female 173 163 54.3 22 Female 174 164 44.8 29 Female 175 165 47 9 Male 176 166 43.8 18 Female 177 167 48 15 Female178 168 42.7 12 Female 179 169 48.5 17 Female 180 170 42 19 Female 181 171 45.5 19 Female 182 172 44.5 14 Female 183 173 51.2 16 Female 184 174 47.5 10 Male 185 175 44.5 11 Female 186 176 47 11 Male 187 177 47 9 Male 188 178 43.1 20 Female 189 179 49 11 Male 190 180 48.5 11 Male 191 181 45 11 Female 192 182 52.5 19 Female 193 183 47.5 9 Male 194 184 48 10 Male 195 185 46.5 11 Male 196 186 61.5 15 Female 197 187 50 12 Female 198 188 61.8 15 0 0 0 0 O O O O HO O A O O B W J O BON N NA O NUG O NA O O O O NO PO PO Female 199 189 43 19 Female 200 190 47 14 Male 201 191 58.5 12 Female 202 192 55 19 Male 203 193 57 27 Male 204 194 57 20 Male 205 195 60 17 Male 206 196 60 16 Male 207 197 59 22 Male 208 198 60 15 Male 209 199 65 23 Male 210 200 52 23 Male 211 201 58 25 Male 212 202 60 24 Male 213 203 74 42 Male 214 204 95 37 Male 215 205 97 39 Male 216 206 88 35 Male 217 207 94 38 Male 218 208 30 36 Female 219 220 221 222 223