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,
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 in terms of
- Total complaint by customer
- Total complaint types
- Total Customer Complaints Raised by Product Family
- Total Complaints Raised by CSR staffs
Note: use formulas, pivot table, linking graphs to pivot table, etc.
Task 2- Problem definition
After data analysis, find out top 3 problems in terms of complaint types, product family, and
customer and CSR staff.
Task 3- Suggestion and Solution
After finding the problems, provide your solution and methodology to your manger. Your
solutions should be extracted from recent scholarly books/articles/reports and cause root analysis
and might address following DC processes including;
Page 3 of 5
- 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 through Turnitin
(UTSOnline). For the Excel file no need to submit hardcopy.
It should show your excellent analysis skills with excel including formulas, pivot table,
linking pivot table to graphs, etc.
2- Report – Please see what should be in your report (Report writing) in assessment
resources in UTSOnline.
Your hard copy is to be submitted to your lecturer at the beginning of the week 5 lecture and
your soft copies will be submitted electronically through UTSOnline

Read less