Demand 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
7
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
How is PEC managing its distribution network and workload balance across its facilities?
Note that PEC’s stated policy is to hold all products at all locations. Is PEC successful in implementing this policy?
Table 1A:
total gross margin
total revenue
total quantity shipped
total weight shipped
total number of customer orders
Hint:
Select "facility name" as row labels in the pivot table.
Map 1A:
Color code the map for each region
Hint:
Use "customer zip" for customer locations
Table 1B:
Hint:
Select "facility name" as row labels in the pivot table.
Select "product category" as column labels in the pivot table.
Construct a pivot table that shows the following for
each distribution center
:
Construct a Tableau map that shows the location of
ALL customers
in
EACH region
Construct a pivot table that shows total quantity shipped for
each distribution center
AND
each product category
:
Team #:
Student(s) Name:
QUESTION 2
How is demand for PEC's products distributed among its customers, models, and product classes?
How profitable are these customers and
products?
Table 2A:
total gross margin
total revenue
total quantity shipped
total weight shipped
total number of customer orders
Hint:
Select "product category" as row labels in the pivot table.
Color code the map for each product category.
Size dots on the map according to total quantity shipped.
Show the map only for "Personal Computer" product category. Copy map on the right side of the tables.
Hint:
1. Drag-and-drop "Product Category" to "Color" box located in the MARKS section (right below FILTERS section).
2. Drag-and-drop "Quantity Shipped (as SUM)" to "Size" box located
in the MARKS section (right below FILTERS section).
Table 2B:
total gross margin
total revenue
total quantity shipped
total weight shipped
Construct a pivot table that shows the following for
each product category
:
Map 2A:
Construct a Tableau map to show customer zip codes.
Construct a pivot table that shows the following for
each customer class
:
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
total number of customer orders
Hint:
Select "customer class" as row labels in the pivot table.
Table 2C:
total gross margin
total revenue
total quantity shipped
total weight shipped
total number of customer orders
Hint:
Select "product class" as row labels in the pivot table.
Table 2D:
Hint:
Select "customer class" as row labels in the pivot table.
Select "product class" as column labels in the pivot table.
Table 2E:
Hint:
Select "product category" as row labels in the pivot table.
Select "product class" as column labels in the pivot table.
Table 2F:
total number of customer orders
Construct a pivot table that shows the following for
each product class
:
Construct a pivot table that shows total gross margin for
each customer class
AND
each product class
:
Construct a pivot table that shows total gross margin for
each product category
AND
each product class
:
Construct a pivot table that shows the following for
each profitability rating
:
total gross margin
Team #:
Student(s) Name:
QUESTION 3
How well is PEC serving its customer base in terms of order cycle time, fill rate and shipment performance?
Table 3A:
Average
Standard deviation
Minimum
Maximum
Graph 3A:
Format the axis to larger font size.
Hint:
Select "Histogram" option under "Show Me" tool.
Under ROWS, use "Quick Table Calculation" and "% of Total" option.
Table 3B:
Average
Standard deviation
Minimum
Maximum
Table 3C:
total number of orders
Construct a pivot table that shows the following for
order cycle time
data:
Construct a "Histogram" of
order cycle time
in Tableau.
Construct a pivot table that shows the following for
fill rate percentage
data:
Construct a pivot table that shows the following for
each order fill status
:
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
% of total number of orders
Table 3D:
total number of orders
total number of orders (% of total)
total quantity shipped
total gross margin
Table 3E:
average fill rate percentage
average order cycle time
average commitment date variance
Table 3F:
average fill rate percentage
average order cycle time
average commitment date variance
Table 3G:
total number of orders
total revenue
average fill rate percentage
average order cycle time
Construct a pivot table that shows the following for
perfect order
data:
Construct a pivot table that shows the following for
each customer class
:
Construct a pivot table that shows the following for
each product class
:
For
each order fill date
, show the following: