49680 Value Chain Engineering Systems

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 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 Customer complaint type by product family
1.4.2 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.

1.6 Identify top most product family associated with errors (product, quantity, and invoice)

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.