Assignment 1: Supply Chain Analytics Analysis and Written Research Report Individual Assignment 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, 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 Product Family 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 four pivot tables in a new sheet called “Executive Summary” for the Customer Complaints Database in terms of: 1.4.1 Total complaints by customer 1.4.2 Total complaint types 1.4.3 Total Customer Complaints Raised by Product Family 1.4.4 Total Complaints Raised by CSR Staffs 1.5 Create a bar chart for each pivot table, providing the count of the data in a descending order. 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 2- Problem definition After data analysis, find out top 3 problems in terms of complaint types, product family, and customer and CSR staff respectively. Provide your cause root analysis for these problems to find out what are the reasons causing these problems. Task 3- Suggestion and Solution After defining the problems, provide your solutions and methodologies to your manger. Your solutions should be extracted from recent scholarly books/articles/reports (in the past three to five years) and cause root 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) It should show your excellent analysis skills with excel including formulas, pivot table, linking pivot table to graphs, etc. 2- Report (Word file) Assignment Format Max Length: 1000 (750 minimum) 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 current date Referencing: 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. 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%