Great Deal! Get Instant $10 FREE in Account on First Order + 10% Cashback on Every Order Order Now

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)...

1 answer below »
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
Answered Same Day Apr 11, 2020

Solution

Abr Writing answered on Apr 17 2020
133 Votes
Executive summary
The data analysis of customer complaint data is explained. the major areas which are critical for the customers and identified the problem areas associated with the customer complaints are explained.
In the next step, we have done the root cause analysis of major factors which were responsible for the consumer complaints and were the major source of declining service quality.
Then I have suggested to reduce the consumer complaints and improve the service quality.
Introduction
Data analysis today is one of the major factors which helps in increasing the productivity of the firm. Data analysis helps in identifying patterns relations and associations between various interdependent variables and helps in creating data-driven decisions.
Data Analysis
We can analyze that the most number of consumer complaints are raised by customer C3 and customer C6, both of these customers have raised 10 complaints each. These customers are then followed by customer C4 and customer C8 who have complained 8 and 7 times respectively.
If we analyze the data from complaint type, we can observe that highest number of complaints are being raised regarding the packaging, there are 150 complaints by the customers regarding the packaging, the second highest complaint is regarding the damaged products, there are 115 complaints about the damaged product and 108 complaints about the poor response.
In the third analysis, we have analyzed the consumer complaints buy CSR staff,  we can hear observe that highest number of complaints are raised by P4 CSR steps P4 CSR step has reached 91 complaints which are then followed by P5 CSR staff who has raised 90 complaints.
In the post-analysis we have analyzed the consumer complaints from the product family perspective here we can observe that product family ps10 is associated with the highest number of consumer complaints that is it has...
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here