# Documentation

 BIT 3424 Dr. Şeref Student Name: Shiyu Ma Unique File ID: First Open: yes Honor Policy: I swear to uphold the Virginia Tech Honor Policy (as specified in the course syllabus). I will not copy another student’s file. I will not share my file with another student. Student Initial: SM

## P1

 Practice 1 (Lecture 1) a Use text functions to extract the store city name from the given store address. b Use date functions to calculate the total number of years (round down) and also total number of months (not rounded) each truck driver has worked for the company (based on today’s date). c Use a math function to calculate the total pay for all truck drivers based on current mileage scheduled and pay per mile for each truck driver. d Use appropriate referencing to calculate the load per truck (round up) based on current store demand and number of trucks available. e Create column labels for number of trucks available: one word “with#trucks”. f Use these column labels and the Store Name labels to name each row and column of the Load per Truck table (not including truck and demand values). g Use a referencing function to calculate the max load per truck number and min shippment per store by referencing the column and row names. Store Name Store Address Store City DoorMart 123 N Main; Vienna, VA Vienna KShop 456 Jones; Baltimore, MD Baltimore Bullseye 789 Turner; Alexandria, VA Alexandria GreenFloor 135 Hemmingway; Springfield, VA Springfield LeftAid 246 Southpaw; Rockville, MD Rockville Truck Driver ID Start Date Total Number Years (round down) Total Number Months (not rounded) Current Mileage Scheduled Pay per Mile ABC1234 1/20/14 4.6388888889 55.67 240 \$6.50 Today’s Date 9/10/18 DEF5678 2/5/16 2.5972222222 31.17 101 \$4.00 GHI9101 2/10/13 5.5833333333 67.00 283 \$8.75 JKL1121 3/20/14 4.4722222222 53.67 197 \$6.50 MNO3141 3/15/14 4.4861111111 53.83 113 \$6.50 PQR5161 2/22/13 5.55 66.60 106 \$8.75 STU7181 1/30/16 2.6111111111 31.33 166 \$4.00 VWX9202 3/1/13 5.525 66.30 139 \$8.75 YZA1222 1/25/13 5.625 67.50 271 \$8.75 Total Payout for All Truck Drivers \$11,634.25 Load per Truck (round up) Based on Current Store Demand and Number of Trucks Available Number of Trucks -> 5 8 10 12 Demand Minimum Shippment Size per Store Store Name DoorMart 580 KShop 700 Bullseye 600 GreenFloor 390 LeftAid 400 Column Names -> Maximum Load per Truck Number

## P2

 Practice 2 (Lecture 2) a Calculate the total shipment load to be dropped off at each store. b Check if the total dropoff per store is less than the requested demand; if so, calculate the demand shortage and display a message “# short!”, otherwise leave blank. c Count the number of times any truck driver is schedule to ship a load greater than its truck capacity. d Highlight table rows for given pickup store and route order. e Calculate the total shipment load to be picked up at each store for each route order. Truck Driver ID Route Order Pickup Store DropOff Store Shipment Load Store Name Total DropOff Demand Demand Warning ABC1234 1 DoorMart K-Shop 141 DoorMart 585 580 ABC1234 2 K-Shop GreenFloor 86 K-Shop 662 700 ABC1234 3 GreenFloor DoorMart 103 Bullseye 609 600 DEF5678 1 GreenFloor Bullseye 66 GreenFloor 319 420 DEF5678 2 Bullseye K-Shop 89 Left-Aid 403 420 DEF5678 3 K-Shop Left-Aid 51 DEF5678 4 Left-Aid Bullseye 84 Truck Driver ID Truck Capacity Capacity Warnings GHI9101 1 Left-Aid Bullseye 147 ABC1234 120 GHI9101 2 Bullseye GreenFloor 73 DEF5678 120 GHI9101 3 GreenFloor DoorMart 72 GHI9101 120 JKL1121 1 Bullseye DoorMart 76 JKL1121 110 JKL1121 2 DoorMart K-Shop 118 MNO3141 130 JKL1121 3 K-Shop Left-Aid 65 PQR5161 130 JKL1121 4 Left-Aid GreenFloor 50 STU7181 110 MNO3141 1 DoorMart Bullseye 111 VWX9202 100 MNO3141 2 Bullseye Left-Aid 53 YZA1222 100 MNO3141 3 Left-Aid DoorMart 105 PQR5161 1 Bullseye GreenFloor 135 Highlight table rows for given Pickup Store and Route Order PQR5161 2 GreenFloor K-Shop 143 Pickup Store Bullseye PQR5161 3 K-Shop DoorMart 125 Route Order 1 STU7181 1 GreenFloor Left-Aid 105 STU7181 2 Left-Aid DoorMart 104 STU7181 3 DoorMart K-Shop 93 Total Shippment to be Picked Up at each store for each route order VWX9202 1 Bullseye GreenFloor 61 Route Order 1 2 3 4 VWX9202 2 GreenFloor Left-Aid 75 DoorMart VWX9202 3 Left-Aid Bullseye 70 K-Shop YZA1222 1 GreenFloor Left-Aid 54 Bullseye YZA1222 2 Left-Aid Bullseye 131 GreenFloor YZA1222 3 Bullseye K-Shop 78 Left-Aid

## P3

 Practice 3 (Lecture 3) a Calculate the miles traveled for each route: use the mileage table to lookup pickup city and dropoff city coordinates. b Create a conditional formatting rule to highlight route table rows with miles traveled greater than required mileage break: your rule will need to lookup the mileage break value for each truck driver. c Determine which driver traveled the maximum number of miles on their 1st route. d Count the number of truck drivers who travel from Vienna to Baltimore or from Baltimore to Vienna. Mileage Vienna Baltimore Alexandria Springfield Rockville Vienna 0 83.2 35.2 22.4 27.2 Baltimore 83.2 0 88 96 70.4 Alexandria 35.2 88 0 16 43.2 Springfield 22.4 96 16 0 41.6 Rockville 27.2 70.4 43.2 41.6 0 Truck Driver ID Route Order Pickup City DropOff City Miles Traveled Truck Driver ID Mileage Break ABC1234 1 Springfield Vienna ABC1234 70 ABC1234 2 Baltimore Springfield DEF5678 50 ABC1234 3 Vienna Baltimore GHI9101 90 DEF5678 1 Baltimore Rockville JKL1121 50 DEF5678 2 Alexandria Baltimore MNO3141 50 DEF5678 3 Springfield Alexandria PQR5161 90 DEF5678 4 Rockville Alexandria STU7181 90 GHI9101 1 Alexandria Springfield VWX9202 70 GHI9101 2 Rockville Alexandria YZA1222 70 GHI9101 3 Springfield Vienna JKL1121 1 Alexandria Vienna JKL1121 2 Rockville Springfield Which Driver drove max miles on 1st route? JKL1121 3 Baltimore Rockville JKL1121 4 Vienna Baltimore MNO3141 1 Rockville Vienna MNO3141 2 Vienna Alexandria Number of Drivers who travel from Vienna to Baltimore OR from Baltimore to Vienna MNO3141 3 Alexandria Rockville PQR5161 1 Alexandria Springfield PQR5161 2 Baltimore Vienna PQR5161 3 Springfield Baltimore STU7181 1 Rockville Vienna STU7181 2 Springfield Rockville STU7181 3 Vienna Baltimore VWX9202 1 Rockville Alexandria VWX9202 2 Springfield Rockville VWX9202 3 Alexandria Springfield YZA1222 1 Alexandria Baltimore YZA1222 2 Springfield Rockville YZA1222 3 Rockville Alexandria

## P4

 Practice 4 (Lecture 4) a Sort the table by Pickup City and then by Route Order. b Make the table and “Excel Table” and filter to show the largest 5 shipment loads. Calculate the average shipment load of these 5 values. c Create a Pivot Table (on this sheet) from the table: show the max shipment loads (values) from each pickup city (rows) to each dropoff store (columns). Show Grand Totals for columns only. d Create Calculated Items for Pickup City: NORTH = Baltimore + Rockville, SOUTH = Alexandria + Springfield + Vienna. Filter pickup cities to show these two items only (NORTH and SOUTH). e Create a Pivot Chart from this pivot table (stacked column). Switch the row/column orientation. Add a slicer for dropoff store and filter for Bullseye, DoorMart, and GreenFloor. Truck Driver ID Route Order Pickup City DropOff Store Shipment Load ABC1234 1 Baltimore K-Shop 141 ABC1234 3 Rockville DoorMart 103 ABC1234 2 Springfield GreenFloor 86 DEF5678 2 Alexandria K-Shop 89 DEF5678 1 Rockville Bullseye 66 DEF5678 3 Springfield Left-Aid 51 DEF5678 4 Vienna Bullseye 84 GHI9101 2 Alexandria GreenFloor 73 GHI9101 3 Rockville DoorMart 72 GHI9101 1 Springfield Bullseye 147 JKL1121 1 Alexandria DoorMart 76 JKL1121 2 Baltimore K-Shop 118 JKL1121 3 Springfield Left-Aid 65 JKL1121 4 Vienna GreenFloor 50 MNO3141 2 Alexandria Left-Aid 53 MNO3141 1 Baltimore Bullseye 111 MNO3141 3 Vienna DoorMart 105 PQR5161 1 Alexandria GreenFloor 135 PQR5161 2 Rockville K-Shop 143 PQR5161 3 Springfield DoorMart 125 STU7181 3 Baltimore K-Shop 93 STU7181 1 Rockville Left-Aid 105 STU7181 2 Springfield DoorMart 104 VWX9202 1 Alexandria GreenFloor 61 VWX9202 2 Rockville Left-Aid 75 VWX9202 3 Vienna Bullseye 70 YZA1222 3 Alexandria K-Shop 78 YZA1222 1 Rockville Left-Aid 54 YZA1222 2 Springfield Bullseye 131 Average of Top 5 Loads