Excel Problems

profilejaimezcla
Assignmentpart2.xlsm
Home>Information Systems homework help>Excel Problems

Questions

1. Use vlookup to calculate the driver pay where the driver pay = PayRate times the rental hours. Place the result in the Driver Pay column on the Use For Data Sheet.
2. Use vlookup to calculate the Driver Bonus where the Driver Bonus is equal to the bonus amount in the Driver Bonus Table. Place the result in the Driver Bonus column on the Use For Data Sheet.
3. Use a nested vlookup to calculate the service charge which can be found in the service charge table. Place the result in the Service Charge column on the Use For Data Sheet.
4. Use a vlookup inside an IF statement to calculate the following: If the Driver Staus in the Driver Master table is Full then the Meal Allowance is 25 dollars otherwise it is nothing.
Place the result in the Meal column on the Use For Data
5. Calculate the New Pay where the new pay is the pay amount found in the Driver Master table increased by 5 percent. Place the result in the New Pay column on the Use For Data

Use for Data

Inv ID Client ID Inv Date Week Driver ID Limo ID Service Level ID Service Type ID Rental Hrs Number Clients Driver Pay Bonus Pay Service Charge Meal New Pay
10100 103 1/1/07 1 FT106 12SL01 S1 B1 3 9
10101 103 1/1/07 1 FT106 12SL01 S1 B1 4 6
10102 146 1/1/07 1 FT102 09SR01 S2 S1 8 5
10103 185 1/1/07 1 FT106 12SL01 S1 T1 2 5
10104 108 1/1/07 1 FT101 09SR02 S2 V1 4 7
10105 101 1/1/07 1 PT101 09SL01 S3 V1 6 5
10106 143 1/1/07 1 FT103 09SL02 S2 W1 7 5
10107 146 1/1/07 1 PT102 09SL01 S3 W1 5 3
10108 103 1/2/07 1 FT101 09SR02 S1 A1 2 3
10109 198 1/2/07 1 PT103 09SL01 S1 B1 4 7
10110 121 1/2/07 1 FT105 12SL02 S1 F1 4 8
10111 121 1/2/07 1 FT105 12SL02 S1 F1 5 9
10112 131 1/2/07 1 FT101 09SR02 S2 G1 6 6
10113 193 1/2/07 1 PT103 09SL02 S2 G1 6 5
10114 101 1/2/07 1 PT101 09SR01 S2 L1 12 5
10115 110 1/2/07 1 FT106 12SL01 S2 D1 3 6
10116 146 1/2/07 1 FT104 09SL01 S2 S1 8 6
10117 110 1/2/07 1 FT103 09SL02 S2 V1 5 3
10118 115 1/3/07 1 FT104 09SL01 S1 A1 2 6
10119 115 1/3/07 1 PT103 09SL01 S1 A1 2 7
10120 188 1/3/07 1 PT101 09SR02 S1 B1 4 2
10121 188 1/3/07 1 FT101 09SR02 S2 B1 6 4
10122 123 1/3/07 1 FT104 09SL01 S2 B1 5 4
10123 155 1/3/07 1 PT102 09SR02 S1 M1 4 6
10124 121 1/3/07 1 FT102 09SR01 S1 G2 6 5
10125 103 1/3/07 1 FT106 12SL01 S1 G2 6 8
10126 114 1/3/07 1 FT106 12SL01 S2 D1 3 3
10127 185 1/3/07 1 FT103 09SL02 S1 S1 4 6
10128 187 1/3/07 1 FT103 09SL02 S1 S1 4 1
10129 111 1/4/07 1 PT101 12SL02 S1 A1 2 6
10130 116 1/4/07 1 PT102 09SR01 S2 B1 8 6
10131 116 1/4/07 1 FT106 12SL01 S1 B1 8 4
10132 107 1/4/07 1 FT101 09SR02 S2 L1 20 4
10133 135 1/4/07 1 FT103 09SL02 S3 L1 13 6
10134 144 1/4/07 1 FT104 09SL01 S3 L1 14 4
10135 141 1/4/07 1 FT105 12SL02 S2 L1 12 10
10136 181 1/5/07 1 FT105 12SL02 S1 B1 4 11
10137 143 1/5/07 1 FT102 09SR01 S2 L1 12 2
10138 146 1/5/07 1 FT106 12SL01 S1 M1 3 5
10139 146 1/5/07 1 FT106 12SL01 S1 D1 3 7
10140 129 1/5/07 1 FT102 09SL02 S1 T1 3 6
10141 114 1/5/07 1 FT103 09SL02 S1 V1 6 6
10142 146 1/5/07 1 FT104 09SL01 S1 W1 8 7
10143 107 1/6/07 1 PT103 09SL01 S1 A1 2 4
10144 183 1/6/07 1 PT102 09SR01 S2 B1 8 4
10145 185 1/6/07 1 PT103 09SL02 S2 S1 5 5
10146 194 1/6/07 1 PT103 09SL02 S2 S1 5 6
10147 148 1/6/07 1 FT105 12SL02 S1 S1 7 6
10148 107 1/6/07 1 FT105 12SL02 S2 V1 4 6
10149 185 1/6/07 1 PT103 09SL01 S2 W1 6 5
10150 127 1/7/07 1 FT101 12SL02 S3 A1 2 3
10151 142 1/7/07 1 FT102 09SR02 S1 F1 6 3
10152 185 1/7/07 1 PT102 09SL01 S2 L1 12 4
10153 176 1/7/07 1 PT103 12SL02 S3 W1 4 10
10154 134 1/8/07 2 FT102 09SR01 S1 A1 7 7
10155 190 1/8/07 2 PT103 09SL02 S2 B1 6 6
10156 187 1/8/07 2 FT104 09SL01 S2 L1 12 8
10157 146 1/8/07 2 FT103 09SL02 S2 L2 28 5
10158 185 1/8/07 2 FT105 12SL02 S1 T1 2 7
10159 133 1/8/07 2 FT105 12SL02 S1 T1 2 5
10160 193 1/9/07 2 FT106 12SL01 S2 B1 4 4
10161 185 1/9/07 2 FT104 09SL01 S2 L1 12 4
10162 146 1/9/07 2 FT105 12SL02 S3 L2 30 7
10163 159 1/9/07 2 FT102 09SR01 S2 S1 6 6
10164 143 1/9/07 2 FT101 09SR02 S1 T1 5 4
10165 155 1/9/07 2 FT101 09SR02 S3 W1 4 4
10166 189 1/10/07 2 PT102 09SR01 S1 A1 2 7
10167 197 1/10/07 2 FT104 09SL01 S2 B1 6 6
10168 167 1/10/07 2 FT103 09SL02 S1 M1 4 4
10169 153 1/10/07 2 FT103 09SL02 S2 S1 5 7
10170 135 1/10/07 2 FT101 09SR02 S2 V1 6 5
10171 144 1/10/07 2 FT104 09SL01 S1 V1 4 5
10172 141 1/10/07 2 FT106 12SL01 S2 V1 6 9
10173 177 1/10/07 2 PT101 09SR02 S2 W1 6 6
10174 177 1/11/07 2 PT101 09SL01 S1 A1 2 6
10175 174 1/11/07 2 PT101 09SL01 S1 B1 6 6
10176 194 1/11/07 2 FT102 09SR01 S2 L1 12 8
10177 159 1/11/07 2 FT101 09SR02 S1 S1 6 7
10178 143 1/11/07 2 PT101 09SR02 S1 V1 3 3
10179 146 1/11/07 2 FT106 12SL01 S2 V1 4 7
10180 146 1/11/07 2 FT106 12SL01 S3 V1 5 6
10181 185 1/11/07 2 PT103 09SL02 S3 W1 8 9
10182 167 1/11/07 2 FT105 12SL02 S1 W1 6 9
10183 152 1/12/07 2 PT103 09SL02 S1 A1 2 7
10184 162 1/12/07 2 PT101 09SR02 S1 F1 6 4
10185 139 1/12/07 2 FT102 09SR01 S1 T1 3 3
10186 139 1/12/07 2 PT102 09SL01 S1 T1 2 5
10187 185 1/12/07 2 FT102 09SR01 S1 V1 6 4
10188 138 1/12/07 2 FT106 12SL01 S3 V1 6 6
10189 106 1/12/07 2 PT101 09SR02 S2 W1 4 4
10190 175 1/13/07 2 PT102 09SL01 S2 B1 4 2
10191 184 1/13/07 2 PT101 09SR02 S1 M1 4 6
10192 148 1/13/07 2 FT101 09SR02 S2 L1 12 3
10193 159 1/13/07 2 PT102 09SR01 S2 L1 12 9
10194 122 1/13/07 2 FT106 12SL01 S1 S1 2 7
10195 102 1/13/07 2 FT103 09SL02 S3 W1 6 6
10196 163 1/14/07 2 FT106 12SL01 S2 B1 5 8
10197 191 1/14/07 2 PT103 09SL01 S1 G1 6 4
10198 109 1/14/07 2 PT102 12SL02 S1 T1 3 4
10199 139 1/14/07 2 FT102 09SR01 S1 V1 2 5
10200 144 1/14/07 2 PT103 09SL02 S1 V1 2 6
10201 146 1/14/07 2 FT106 12SL01 S2 V1 4 9
10202 107 1/14/07 2 FT101 09SR02 S3 W1 5 7
10203 128 1/14/07 2 PT102 12SL02 S3 W1 4 10
10204 107 1/15/07 3 FT103 09SL02 S1 A1 7 2
10205 114 1/15/07 3 FT103 09SL01 S1 A1 2 6
10206 187 1/15/07 3 FT104 09SL01 S1 F1 7 6
10207 187 1/15/07 3 FT105 12SL02 S1 F1 7 7
10208 103 1/15/07 3 PT102 09SR01 S2 G1 6 6
10209 146 1/15/07 3 PT101 09SR02 S2 V1 3 4
10210 107 1/15/07 3 FT102 09SR01 S3 V1 5 7
10211 113 1/16/07 3 PT102 09SL01 S1 A1 3 5
10212 164 1/16/07 3 FT102 09SR01 S2 B1 5 7
10213 168 1/16/07 3 PT101 12SL02 S1 B1 6 9
10214 159 1/16/07 3 FT104 09SL01 S1 M1 7 3
10215 159 1/16/07 3 FT105 12SL02 S1 M1 5 10
10216 102 1/16/07 3 PT101 09SR02 S2 S1 5 5
10217 152 1/16/07 3 FT102 09SR01 S1 T1 4 8
10218 134 1/16/07 3 FT106 09SL02 S1 T1 4 5
10219 185 1/16/07 3 FT103 09SL02 S2 W1 5 5
10220 140 1/17/07 3 PT102 09SL01 S1 A1 3 6
10221 155 1/17/07 3 FT101 09SR02 S1 M1 7 6
10222 131 1/17/07 3 FT104 09SL01 S1 G2 6 6
10223 171 1/17/07 3 PT103 09SL01 S1 G2 6 3
10224 153 1/17/07 3 FT106 12SL01 S2 L1 16 8
10225 111 1/17/07 3 FT102 09SR01 S2 V1 4 9
10226 112 1/17/07 3 FT103 09SL02 S2 V1 5 3
10227 113 1/17/07 3 FT103 09SL02 S2 V1 5 5
10228 115 1/17/07 3 FT105 12SL02 S2 V1 3 5
10229 159 1/18/07 3 FT101 09SR02 S2 L1 12 5
10230 122 1/18/07 3 FT102 09SR01 S2 L1 13 4
10231 102 1/18/07 3 FT103 09SL02 S2 L1 12 6
10232 123 1/18/07 3 FT104 09SL01 S2 L1 12 4
10233 132 1/18/07 3 FT105 12SL02 S2 L1 16 9
10234 141 1/18/07 3 FT106 12SL01 S2 V1 4 8
10235 125 1/18/07 3 FT106 12SL01 S3 W1 4 7
10236 162 1/19/07 3 FT102 09SR01 S2 A1 2 3
10237 168 1/19/07 3 FT104 09SL01 S2 A1 2 6
10238 128 1/19/07 3 PT101 09SR02 S2 L1 12 6
10239 152 1/19/07 3 FT105 12SL02 S1 T1 7 9
10240 133 1/19/07 3 FT106 12SL01 S2 V1 6 8
10241 185 1/19/07 3 PT103 09SL02 S3 W1 5 6
10242 174 1/20/07 3 PT103 09SL02 S1 A1 4 1
10243 175 1/20/07 3 PT103 12SL02 S1 F1 4 8
10244 123 1/20/07 3 FT101 09SR02 S3 S1 8 6
10245 180 1/20/07 3 FT102 09SR01 S2 T1 4 8
10246 144 1/20/07 3 FT101 09SR02 S2 V1 3 4
10247 115 1/20/07 3 FT101 09SR02 S3 V1 3 4
10248 108 1/20/07 3 PT102 09SR01 S3 V1 4 4
10249 101 1/20/07 3 FT106 12SL01 S1 V1 2 7
10250 110 1/20/07 3 FT106 12SL01 S1 V1 4 5
10251 129 1/20/07 3 PT103 09SL01 S2 W1 6 5
10252 124 1/21/07 3 PT103 09SL01 S2 B1 6 6
10253 159 1/21/07 3 PT103 09SL02 S1 M1 6 4
10254 181 1/21/07 3 FT101 09SR02 S2 G1 4 8
10255 163 1/21/07 3 PT102 09SR01 S2 G1 4 4
10256 127 1/21/07 3 FT104 09SL01 S2 G1 4 8
10257 182 1/21/07 3 PT102 12SL02 S2 G1 5 6
10258 139 1/21/07 3 FT106 12SL01 S1 D1 3 8
10259 161 1/21/07 3 FT101 09SR02 S1 T1 2 1
10260 104 1/22/07 4 FT105 12SL02 S2 B1 10 9
10261 162 1/22/07 4 PT101 09SL01 S2 G1 5 6
10262 138 1/22/07 4 FT103 09SL02 S2 L1 12 4
10263 107 1/22/07 4 FT102 09SR01 S3 L2 36 6
10264 132 1/22/07 4 FT106 12SL01 S3 S1 6 6
10265 103 1/23/07 4 PT101 09SR02 S1 A1 2 3
10266 127 1/23/07 4 FT105 12SL02 S2 B1 10 10
10267 139 1/23/07 4 FT106 12SL01 S1 D1 3 8
10268 157 1/23/07 4 PT103 09SL02 S1 T1 3 7
10269 114 1/23/07 4 PT103 09SL02 S3 V1 6 6
10270 107 1/23/07 4 PT101 09SL01 S2 V1 4 9
10271 185 1/23/07 4 FT106 12SL01 S2 W1 6 4
10272 119 1/24/07 4 PT101 09SR02 S1 A1 2 2
10273 120 1/24/07 4 FT101 09SR02 S2 A1 8 3
10274 131 1/24/07 4 FT103 09SL02 S3 B1 6 3
10275 131 1/24/07 4 PT102 09SL01 S2 B1 7 5
10276 131 1/24/07 4 FT106 12SL01 S1 B1 4 9
10277 160 1/24/07 4 FT105 12SL02 S1 T1 4 6
10278 135 1/24/07 4 FT106 12SL01 S2 V1 6 7
10279 133 1/24/07 4 FT103 09SL02 S3 W1 4 7
10280 128 1/25/07 4 FT105 12SL02 S1 A1 2 4
10281 195 1/25/07 4 PT101 09SR01 S2 B1 5 5
10282 134 1/25/07 4 FT106 12SL01 S1 B1 2 5
10283 173 1/25/07 4 PT101 09SR02 S1 G2 4 8
10284 175 1/25/07 4 FT105 12SL02 S1 G2 4 12
10285 172 1/25/07 4 FT106 12SL01 S1 G2 4 9
10286 144 1/25/07 4 FT103 09SL02 S2 L1 12 6
10287 146 1/25/07 4 FT104 09SL01 S2 L1 20 6
10288 128 1/25/07 4 PT102 09SR01 S2 S1 6 6
10289 118 1/26/07 4 PT102 09SL01 S1 A1 2 7
10290 171 1/26/07 4 FT105 12SL02 S1 A1 2 5
10291 191 1/26/07 4 FT101 09SR02 S2 B1 6 3
10292 167 1/26/07 4 PT101 09SR01 S1 M1 6 5
10293 174 1/26/07 4 FT101 09SR02 S1 G2 5 3
10294 161 1/26/07 4 FT106 12SL01 S1 G2 5 6
10295 139 1/26/07 4 PT103 09SL01 S1 S1 4 8
10296 138 1/26/07 4 FT105 12SL02 S2 S1 4 5
10297 144 1/26/07 4 PT103 09SL02 S1 V1 3 4
10298 141 1/26/07 4 PT103 09SL02 S2 V1 6 2
10299 143 1/26/07 4 PT101 09SR01 S3 W1 6 4
10300 146 1/27/07 4 PT102 09SR01 S2 L1 12 8
10301 107 1/27/07 4 FT103 09SL02 S2 L1 12 5
10302 111 1/27/07 4 FT104 09SL01 S2 L1 20 6
10303 143 1/27/07 4 FT101 09SR02 S2 V1 6 5
10304 146 1/27/07 4 FT105 12SL02 S2 V1 4 4
10305 121 1/28/07 4 FT101 09SR02 S1 T1 2 2
10306 165 1/28/07 4 PT103 09SL02 S1 T1 2 5
10307 101 1/28/07 4 PT101 12SL01 S1 T1 3 6
10308 146 1/28/07 4 FT102 09SR01 S2 V1 5 2
10309 139 1/28/07 4 FT101 09SR02 S1 W1 6 6

Tables

DRIVER MASTER
Driver ID Fname Lname Status PayRate
FT101 Leonard Sanchez Full $20.00
FT102 Paul Arnaldi Full $20.00
FT103 Sylvia Rennati Full $18.00
FT104 George Penderson Full $17.00
FT105 Melanie Nesby Full $17.00
FT106 Rick Cabrera Full $16.00
PT101 Selma Hudson Part $15.00
PT102 Chip Skinner Part $14.00
PT103 Stan Prentice Part $14.00
SERVICE TYPE SERVICE CHARGE
Service Type ID Service Type Desc Service Status Service Status Description Service Charge
A1 Airport SX1 SD1 Standard 30.0
B1 Birthday SD1 SX1 Drop of Fee 40.0
D1 Deliveries SD1 LT1 Longterm 12 50.0
F1 Funeral SD1 LT2 Longterm 24 60.0
G1 Graduation - College SD1
G2 Graduation - High School SD1 DRIVER BONUS PAY
L1 Long term full day LT1
L2 Long term minimum 2 day LT2 Hours Bonus Amount
M1 Miscellaneous SD1 0 0
S1 Sight Seeing SD1 3 5
T1 Taxi SX1 6 10
V1 VIP Visitor SD1 8 15
W1 Wedding SD1 12 25