Transportation Data Mining Worksheet Excel 3a
xlsx
keyboard_arrow_up
School
Auburn University *
*We aren’t endorsed by this school
Course
4700
Subject
Industrial Engineering
Date
Dec 6, 2023
Type
xlsx
Pages
4
Uploaded by jzd0099
Please make sure that your submission follows the formatting guidelines:
1. Tables have gridlines/borders
2. Pivot table show results in "tabular form"
3. Use proper column widths
4. Use "wrap text" feature to keep text inside a cell
5. Show numerical results as comma separated values (e.g., 12,045)
6. Show currency values in $ format
7. Display currency and numerical values as whole numbers (zero decimal places)
8. For numerical results with values less than 100 (like %), use two decimal points (e.g., 92.34%)
9. Do not use sub totals in pivot table results
10. Organize datamining results in the same sequence (top-to-bottom) as listed in the worksheet
Team #:
Student(s) Name:
QUESTION 1
Does PEC strategically manage its transportation buy?
Table 1A:
Total delivery cost
Total delivery cost (% of total)
Count of shipments
Count of shipments (% of total)
Total shipment weight
Additional instructions:
After completing pivot table, add a new colum next to the pivot table to calculate freight rate for each row.
Freight Rate in $/cwt = Total delivery cost / (Total shipment weight/100)
NOTE: You cannot use add a column to pivot table, so instead do the following:
(a) Add a new blank column to the side of the pivot table to compute freigth rates
(b) Enter the freight rate formula with direct cell references, e.g., A16/B10 (do not click on cells)
Table 1B:
Count of shipments
Count of shipment (as % of total)
Total delivery costs
Total delivery costs (as % of total)
Total shipment weight
Additional instructions:
After completing pivot table, add a new colum next to the pivot table to calculate freight rate for each row.
Freight Rate in $/cwt = Total delivery cost / (Total shipment weight/100)
Table 1C:
Count of shipments
Count of shipment (as % of total)
Total delivery costs
Total delivery costs (as % of total)
Total shipment weight
Additional instructions:
Include both, mode and carrier class, as row labels
After completing pivot table, add a new colum next to the pivot table to calculate freight rate for each row.
Freight Rate in $/cwt = Total delivery cost / (Total shipment weight/100)
Construct a pivot table that shows the following for
each shipping mode
:
Construct a pivot table that shows the following for
each carrier class
:
Construct a pivot table that shows the following for
each shipping mode
and
each carrier class
:
Team #:
Student(s) Name:
QUESTION 2
Does PEC receive quality service from its carriers?
Table 2A:
Average
Standard deviation
Minimum
Maximum
Table 2B:
Average actual transit time
Standard deviation of actual transit time
Longest actual transit time
Shortest actual transit time
Graph 2C:
Add "Mode" as a column
Table 2D:
Count of shipments
Count of shipments (% of total)
Table 2E:
Hint:
Select "on-time peofromance" as row labels in the pivot table.
Select "mode" as column labels in the pivot table.
Table 2F:
Hint:
Select "on-time peofromance" as row labels in the pivot table.
Select "carrier class" as column labels in the pivot table.
Table 2G:
Count of shipments
Count of shipments (% of total)
Table 2H:
Hint:
Select "shipment problem code" as row labels in the pivot table.
Select "mode" as column labels in the pivot table.
Construct a pivot table that shows the following for
actual transit time
data:
Construct a pivot table that shows the following for
each shipping mode:
Construct a histogram of
actual transit times
in Tableau
Construct a pivot table that shows the following for
on-time performance
data:
Construct a pivot table that shows total count of shipments for
each shipping mode
and
on-time performance
data:
Construct a pivot table that shows total count of shipments for
each carrier class
and
on-time performance
data:
Construct a pivot table that shows the following for
each shipment problem code
:
Construct a pivot table that shows total count of shipments for
each shipping mode
and
shipment problem code
data:
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Team #:
Student(s) Name:
QUESTION3
How effectively does PEC handle shipping of back orders from out of region DCs? How does the current back order shipping
arrangement work in terms of freight rates and transit times in comparison with in-region shipments?
Table 3A:
Hint:
Use "Origin DC" as filter. Select show quick filter.
Set "Origin DC= Chicago"
Zoom out to show entire country on the map.
Table 3B:
Count of shipments
Count of shipment (as % of total)
Total delivery costs
Total delivery costs (as % of total)
Total shipment weight
Average actual transit time
Additional instructions:
Include both, carrier class and sevice type, as row labels
After completing pivot table, add a new colum next to the pivot table to calculate freight rate for each row.
Freight Rate in $/cwt = Total delivery cost / (Total shipment weight/100)
NOTE: You cannot use add a column to pivot table, so instead do the following:
(a) Add a new blank column to the side of the pivot table to compute freigth rates
(b) Enter the freight rate formula with direct cell references, e.g., A16/B10 (do not click on cells)
Table 3C:
Hint:
Select "service type" as row labels in the pivot table.
Select "on-time performance" as column labels in the pivot table.
Table 3D:
Hint:
Select "home DC" as row labels in the pivot table.
Select "origin DC" as column labels in the pivot table.
Table 3E:
Hint:
Select "home DC" as row labels in the pivot table.
Select "origin DC" as column labels in the pivot table.
Construct a map in Tableau that shows
all destination ZIPs
for shipments
sent from Chicago DC
.
Construct a pivot table that shows the following for
each carrier class
and
each service type:
Construct a pivot table that shows total count of shipments for
each service type
and its
on-time performance
data:
Construct a pivot table that shows total number of shipments for different
origin DC
and
home DCs
:
Construct a pivot table that shows
total cartons shipped
from different
origin DCs
(include home DC information to this table as well):