Essay 49680: Value Chain Engineering Systems – Supply Chain Analytics – Management Assignment Help

Assignment Task:

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 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 pivot tables in a new sheet called “Executive Summary” for the Customer Complaints Database in terms of: 
1.4.1 Customer complaint type by customer name 1.4.2 Complaints closed by CSR Staffs 
1.5 Create a bar chart for each pivot table, providing the count of the data in a descending order. 
1.6 Identify top most product family associated with errors (quantity, and invoice) 

Task 2: One of the main reasons for complaint type “Delivery” was found to be with the insufficient inventory on hand to fulfill customer request on time. To solve this issue, the DC manager tried to improve warehouse inventory policies for the product with highest demand. The forecast for this product is 500 units for the first half year and then 1000 units for the next half year. The holding cost was estimated to be $2.5 per unit per month and the ordering cost was $65 per order. By taking appropriate assumptions, determine an order size that will minimize the inventory costs for each of the six-month period. Which inventory models will be appropriate if demand varies substantially for each of 6-month period.

Task 3- Problem definition 
After data analysis, find out top 3: complaint types, product family, and customer and CSR staff respectively. Provide your cause root analysis for top complaint types based on your understanding of the subject thus far. 

Task 4- Suggestion and Solution 
After identifying complaints in Task 3, provide your solutions and methodologies to your manager. Your solutions should be extracted from recent scholarly books/articles/reports (in the past 3 to 5 years) and root cause analysis and might address the following DC processes: 
– 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.