Inventory Datamining Worksheet Access V4
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. Use proper column widths
3. Use "wrap text" feature to keep text inside a cell
4. Show numerical results as comma separated values (e.g., 12,045)
5. Show currency values in $ format
6. Display currency and numerical values as whole numbers (zero decimal places)
7. For numerical results with values less than 100 (like %), use two decimal points (e.g., 92.34%)
Student Name:
FORMAT RESULTS ACCORDING TO REQUIREMENTS SET FORTH IN THE INSTRUCTIONS TAB
QUESTION 1
How well does PEC manage inventory velocity and turns?
What opportunity exists for PEC to reduce their inventory investment and their inventory carrying costs?
Table 1A:
Construct an Excel table that shows the following:
Total on-hand units
Total demand
Total value of inventory
Average inventory turns
Total inventory carrying cost
Table 1B:
Total on-hand units
Total demand
Total value of inventory
Average inventory turns
Table 1C:
Total on-hand units
Total demand
Total value of inventory
Average inventory turns
Table 1D:
Total on-hand units
Total demand
Total value of inventory
Average inventory turns
Graph 1E:
Construct an Excel table that shows the following for
each product category
:
Construct an Excel table that shows the following for
each product class
:
Construct an Excel table that shows the following for
inventory turn classification
data:
Create a Tableau or Excel side-by-side vertical bar graph of "Onhand Units" for
Product Class
and
Inventory Turn Classification
Student Name:
FORMAT RESULTS ACCORDING TO REQUIREMENTS SET FORTH IN THE INSTRUCTIONS TAB
QUESTION 2
What is the condition of the inventory in PEC’s distribution system?
Table 2A:
Total on-hand units
Total value of inventory
Total inventory carrying cost
Table 2B:
Table 2C:
Hint:
Select "product condition" as row heading in crosstab querry.
Select "product class" as column heading in crosstab querry.
Graph 2D:
Create a Tableau or Excel vertical bar graph for Table 2C, but only for Product Condition = New Merchandise
Construct an Excel table that shows the following for
each product condition
:
Construct an Excel table that shows
total value of inventrory
for
each product condition
and
each product category
.
Construct an Excel crosstab table that shows total onhand units for
each product condition
and
each product 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
Student Name:
FORMAT RESULTS ACCORDING TO REQUIREMENTS SET FORTH IN THE INSTRUCTIONS TAB
QUESTION 3
How evenly is the inventory spread among the facilities in PEC’s distribution system?
Table 3A:
Total on-hand units
Total demand
Total value of inventory
Average inventory turns
Table 3B:
Hint:
Select "product category" as row heading in crosstab querry.
Select "facility name" as column heading in crosstab querry.
Table 3C:
Hint:
Select "product condition" as row heading in crosstab querry.
Select "facility name" as column heading in crosstab querry.
Graph 3D:
Construct a map of all facilities in Tableau (Hint: Transform/split)
Add Value of Inventory for "Size"
Add OH Units as label
Add Value of Inventory as label
Use "Show Quick Filter" for Product Category "Home Office Equipment"
Add Product Category for "Color"
Construct an Excel table that shows the following for
each distribution center
:
Construct an Excel crosstab table that shows total value of inventory for
each product category
and
each distribution center
:
Construct an Excel crosstab table that shows total value of inventory for
each product condition
and
each distribution center
: