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: 15:00 on 19 April 2018 (Week 6)
Report should be submitted through UTSOnline before 15:00 19 April 2018
(Week 6). Softcopies (MUST BE Word and Excel files) Word file should be
submitted to Turnitin and Excel file should be submitted electronically
through UTSOnline 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 UTSOnline.
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 the ERP system of
the company which is active in the electronic industry. The company has a distribution center (DC)
having space of 20,000 square meters. The company’s suppliers send products from overseas and
domestic locations and have different ranges of customers from wholesalers, retailers and end-
Page 2 of 4
users. The DC/warehouse has 30,000 stock keeping units (SKU), material and lifts machines and
other equipment. The warehouse staff includes DC manager, supply chain analyst, inbound and
outbound supervisors and 50 employees.
Recently the company has received numerous customer complaints which make sales and customer
service representative (CSR) department sick of hearing angry customers’ voice. Therefore, your
manger has asked you to analyze the customer complaints in order 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 subtasks below:
1.1 Count the number of complaints initiated in May concerning the product family PF10
(using SUMPRODUCT with other possible formulas).
1.2 Display the name(s) of product family complained by the customer C9 as a wrong product
(using VLOOKUP formula with other possible formulas).
1.3 Give the monthly average of complaints about the product family identified in the task 1.2
(using formulas).
1.4 Create a new sheet named “Executive Summary” and establish four pivot tables in this
sheet for the Customer Complaints Database in terms of:
1.4.1 Total complaints raised by Customers
1.4.2 Total complaints raised by Complaint Types
1.4.3 Total complaints raised by Product Family
1.4.4 Total complaints raised by CSR Staffs
1.5 Insert a bar chart for each pivot table automatically, providing the count of the data in a
descending order.
Please note: The first three subtasks (1.1 – 1.3) should be completed in the Customer Complaints
Database sheet. In addition, all the fonts shown in the tables and charts should be Times New Roman.
Task 2- Problem definition
After data analysis, indicate top 3 problems from the category of Customers, Complaint Types,
Product Family, and CSR Staffs respectively. Provide your root cause analysis for these problems
to find out what are the reasons causing these problems.
Page 3 of 4
Task 3- Solutions and Suggestions
After defining the problems, provide your solutions and methodologies to your manger. Your
solutions should be extracted from recent scholarly books/articles
eports (in the past three to five
years) and root cause analysis, and might address the following DC processes including:
- 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
UTSOnline.
It should show your excellent analysis skills with Microsoft Excel software including using
formulas, creating pivot table, and linking pivot table to graphs, etc.
2- Report (Word file) – Please see what should be included in your report (Report writing)
in Assessment Resources in UTSOnline.
You need to submit your soft copy report through Turnitin (UTSOnline). Please note that you
should attach UTS Assignment Cover Sheet (you can download from Assessment Resources
in UTSOnline) to the first page of your report.
Page 4 of 4
Assignment Format
Max Length: XXXXXXXXXXminimum) words
Font size: 12pt Times New Roman
Line spacing: Double line spacing
Coversheet: Attach standard UTS assignment coversheet
Headers: Include name, student number and title of your report
Footers: Include page numbers and cu
ent date
Referencing: Use Harvard/UTS referencing only
(See Referencing in the UTS li
ary 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
eports (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.
Feedback Weighting
• Executive Summary, Introduction and Conclusion. 10%
• Data Analysis (Spreadsheet Analysis). 40%
• Problem Definition. 20%
• Solutions and Suggestions. 20%
• Grammar, spelling, referencing (Harvard/UTS only),
presentation (including use of sub-headings, etc.).
10%
Please note: Questions in relation to the assignment should be posted to the Discussion
Board on UTS Online. Make sure you have read the Assignment Outline (both here and in
UTSOnline) before you ask your questions. Questions on the assignment that are received via
email may not be answered.
Due date: 15:00 on 19 April 2018 (Week 6)
Report should be submitted through UTSOnline before 15:00 19 April 2018 (Week 6). Softcopies (MUST BE Word and Excel files) Word file should be submitted to Turnitin and Excel file should be submitted electronically through UTSOnline by due date. A co...
NOTE:
Assignment Format
Use of Scholarly Sources
Marking