Supply Chain Analytics for Value Chain Engineering Systems
Value Chain Engineering Systems
Please read all the assignment requirements and the materials we provide before starting to write the assignment.
According to the requirements of the assignment, we have completed Excel. You must continue to complete the thesis according to the requirements of the topic and the excel file we provide. We hope you can check for errors in excel. If you find any errors in excel, please let me know first. We will decide whether to modify.
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. （We have completed and provided. If you find any errors in Excel, please let me know first. I will decide whether to modify.）
2- Report (Word file) – It should include: Executive summary, Introduction, Response to Task 1 & 2, Problem Description (Task 3), Solution and Suggestions (Task 4).-（Must use the theoretical knowledge mentioned in the courseware） NOTE: Please include all the Figures created in Excel should be included in the report as well. Please also include the Formulas used in the Appendix section of the report. 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. （You need to continue to complete this report according to the assignment requirements and the Excel file I provided.）
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 complaints for PF13 (using COUNTIF formula). 1.2 Display the name of the customer who most frequently complained (using INDEX formula and/OR other possible formulas). 1.3 List all distinct customers associated with the complaints (using VLOOKUP formula and/OR other possible formulas). 1.4 Establish 2 pivot tables in a new sheet called “Executive Summary” for the Customer Complaints Database in terms of: 1.4.1 Displaying the number of complaints by complaint type and by product family 1.4.2 Displaying the number of complaints raised by CSR staff 1.5 Create a bar chart for complaint types for top 3 product family, providing the count of the data in a descending order.