BIT homeworks

profilemark Ma
HW1-start1.xlsm
Home>Business & Finance homework help>Management homework help>BIT homeworks

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