Assignment:
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 Squaremeter 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- 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 thedata in a descending order.