49680 Value Chain Engineering Systems

Page 1 of 4
49680 Value Chain Engineering Systems
Assignment 1: Supply Chain Analytics
Analysis and Written Research Report (Value: 20%)
Individual Assignment
Due date: 23:00, 17 Apr 2020
Report should be submitted through Canvas before the due
date. Softcopies (MUST BE Word and Excel files) Word file should
be submitted to Turnitin and Excel file should be submitted
electronically through Canvas by due date. A complete
submission requires both files.
NOTE:
• This is an individual assignment. A penalty of 10% of possible marks will be
imposed per day for assignments submitted after the due date.
• The Marking Guide is available with Assignment 1 in Canvas.
Supply Chain Analytics
Overview
You are a supply chain analyst employed by an electronic company to analyze data and find out
problems and suggest solutions. The data in the spreadsheet is pulled out from ERP system of the
company which is active in the electronic industry. They got 20,000 Square meter distribution
center (DC) with local and international customers. The company’s suppliers send products from
overseas and domestic locations. They have different ranges of customers from whole sales,
Page 2 of 4
retailers and end-users. The DC/warehouse have 30,000 stock keeping units (SKU), material and
lifts machines and other equipment. The warehouse staffs are including DC Manager, supply
chain analyst, inbound and outbound supervisors and 50 employees.
Recently they have received numerous customer complaints which make sales and customer
service representative (CSR) department sick of hearing angry customers’ voice. Your manger
has asked you to analyze the customer complaints data to find out problems and come up with
solutions.
Requirements
Task 1- Analysis
You need to analyze the data with Microsoft Excel software for several sub tasks below:
1.1 Count the number of complaints for PF12 (using COUNTIF formula).
1.2 Display the name of the customer who most frequently complained (using INDEX
formula with other possible formulas).
1.3 List all complaint types raised by the customer identified in the task 1.2 (using
VLOOKUP formula with other possible formulas).
1.4 Establish pivot tables in a new sheet called “Executive Summary” for the
Customer Complaints Database in terms of:
1.4.1 Customer complaint type by customer name
1.4.2 Complaints closed by CSR Staffs
1.5 Create a bar chart for each pivot table, providing the count of the data in a
descending order.
1.6 Identify top most product family associated with errors (quantity, and invoice)
Task 2: One of the main reasons for complaint type “Delivery” was found to be with the
insufficient inventory on hand to fulfill customer request on time. To solve this issue, the DC
manager tried to improve warehouse inventory policies for the product with highest demand.
The forecast for this product is 500 units for the first half year and then 1000 units for the
next half year. The holding cost was estimated to be $2.5 per unit per month and the
ordering cost was $65 per order. By taking appropriate assumptions, determine an order
size that will minimize the inventory costs for each of the six-month period. Which
inventory models will be appropriate if demand varies substantially for each of 6-month
period.
Page 3 of 4
– Inbound or receiving (DC/warehouse inbound)
– Put away and operations
– Warehouse Layout
– Outbound or dispatch (DC/warehouse outbound)
– Warehouse Staffs
– Machines (Forklifts, pallet Jack, etc.)
– Equipment, etc.
Deliverables
After completing the assignment, submit the following:
1- Spreadsheets Analysis (Excel file) – Softcopy must be submitted electronically through
Canvas.
It should show your excellent analysis skills with excel including formulas, pivot table,
linking pivot table to graphs, etc.
2- Report (Word file) – Please see what should be in your report (Report writing) in
Assessment Resources in Canvas.
You need to submit your softcopy report through Turnitin (Canvas). Please note that you
should attach UTS Assignment Cover Sheet (you can download from Assessment Resources
in Canvas) to the first page of your report.
Please note: the first three sub tasks (1.1 – 1.3) should be completed in the Answer Sheet in the
Excel and all the fonts shown in the tables and charts should be Times New Roman.
Task 3- Problem definition
After data analysis, find out top 3: complaint types, product family, and customer and CSR
staff respectively. Provide your cause root analysis for top complaint types based on your
understanding of the subject thus far.
Task 4- Suggestion and Solution
After identifying complaints in Task 3, provide your solutions and methodologies to your
manager. Your solutions should be extracted from recent scholarly books/articles/reports
(in the past 3 to 5 years) and root cause analysis and might address the following DC processes:
Page 4 of 4
Assignment Format
Max Length:
Font size:
Line spacing:
Coversheet:
Headers:
Footers:
Referencing:
1500 (1000 minimum) words
12pt Times New Roman
Double line spacing
Attach standard UTS assignment coversheet
Include name, student number and title of your report
Include page numbers and current date
Use Harvard/UTS referencing only
(See Referencing in the UTS library website)
Use of Scholarly Sources
You will need to relate to the Supply Chain Analytics assignment from the contributions of
at least eight (8) recent scholarly books/articles/reports (in the past three to five years), in
addition to any materials that have already been assigned for the course. These scholarly
sources should specify the Supply Chain issues involved with the creation of value to
achieve all the benefits of efficient Supply Chain Management.
Marking
Feedback will be provided by annotating your report with comments and an indication on the
merit of sections of your report using the following:
Z, P, C, D, HD.

Please note: Questions in relation to the assignment should be posted to the
Discussion Board on Canvas. Make sure you have read the Assignment Outline (both
here and in Canvas) before you ask your questions. Questions on the assignment that are
received via email may not be answered.

The post 49680 Value Chain Engineering Systems appeared first on My Assignment Online.