Analyzing Customer Complaints In An Electronic Company: Problems, Solutions (essay).

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.

You need to analyze the data with Microsoft Excel software for several sub tasks below:

1. Count the number of Product Family for PF12 (using COUNTIF formula).

2. Display the name of the customer who most frequently complained (using INDEX formula with other possible formulas).

3. List all complaint types raised by the customer identified in the task 1.2 (using VLOOKUP formula with other possible formulas).

4. Establish four pivot tables in a new sheet called “Executive Summary” for the Customer Complaints Database in terms of:

- Total complaints by customer

- Total complaint types

- Total Customer Complaints Raised by Product Family

- Total Complaints Raised by CSR Staffs

- Create a bar chart for each pivot table, providing the count of the data in a descending order.

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.

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.

The research is about an electronic company that mainly deals with supplies from the overseas and domestic locations. The range of the customers of this company is also large, including whole sellers, retailers and end users. The company has a distribution center of 20,000 square meters where the customers are both international as well as domestic. This research is mainly conducted with the view of identifying the reasons for the complaints of the customers of an electronic company and suggest the company some preventive measures on the basis of the results. In order to conduct this research, the required data on the customer complaints has been extracted from the ERP system of the company.

On the basis of the analysis of the extracted data, the observed results have shown that the customers C4, C3 and C31 have registered for complaints for the maximum number of times out of all the recorded complaints. 19 complaints have been recorded on the name of the customer C4. The customer has faced a lot of issues with the product among which, it has been seen that the customer has received the product with wrong quantity in most of the cases. Thus, the problem in this scenario has been unsatisfactory products received by the customers which needs to be amended.

The type of complaints that has been recorded mostly are delivery, wrong quantity and lost in transit. Most of the complaints have been recorded for the delivery issue. Out of the 719 complaint cases recorded, 233 have been found related to the delivery of the products. However, complaints regarding delivering the wrong quantity has been recorded 136 times whereas, complaints regarding the product being lost in transit has been recorded 127 times. The customers would mostly complain about the delivery issues if the product is not delivered within the estimated time. This problem needs to be amended.

The complaints of the customers have been raised mostly for the products belonging to the families PF12, PF10 and PF2. The results have shown that though PF12 have received 60 complaints which is the highest, products from all the other families have received complaints close to 50, which is not much of a difference from each other. Thus, it can be said that complaints have been received in almost an equal proportion for all the product families. Thus, the products from each of the family needs to be tested to find out the reasons for the complaints.

Data Analysis

The CSR staffs have been receiving a lot of phone calls each day and thus too much of angry phone calls irritate them. The analysis showed that P4, P2 and P6 staffs have been receiving the maximum number of angry calls with P4 leading the list of the CSR staffs with 96 angry calls. However, P2 and P6 also have been receiving 81 and 78 calls respectively which not much less than the calls received by P4. The irritation of the CSR staffs has to be reduced by the company to prevent employee attrition.

As discussed earlier from the results earlier that the customer who has been complaining the maximum number of times is complaining about multiple faults with the products delivered to him. Among all the complaints registered, it has been identified that the customer has received the product in the wrong quantity. This problem can arise due to wrongly recording the order placed. This problem can also be due to wrong packaging (Sekaran & Bougie 2016). Both the problems are related to the staffs and hence from here, it can be suggested that the company needs more trained staffs to record the order properly as well as to work with attention while following the order to be packaged (Vezzoli et al. 2015, pp.1-12).

Most of the complaints recorded is related to the product delivery. Problems that can be faced at the time of delivery is losing the product in the process, not delivering the product in the right time as specified. Early arrival of the product ordered is usually not a problem to the customers whereas, customers are angry if the product is delivered later than ETA. The company needs to look into this matter by starting to deliver the products in proper time. The problem can be related to unavailability of delivery van or delivery man or reducing the delivery cost (Psaraftis, Wen & Kontovas 2016, pp.3-31). The problem can also be due to late in the manufacture of the product. All these problems can be solved with recruiting more trained staffs as delivery man or as workers in production (Vidal et al. 2014, pp.658-673).

As observed from the analysis, complaints have been recorded in almost the same number from each of the product families. This can be due to manufacturing problems (Kalpakjian, Vijai Sekar & Schmid 2014). Thus, the equipment producing the products from each of families must be innovated and maintained so that this problem can be minimized (Radziwon et al. 2014, pp.1184-1190).

The work of the CSR staffs is to handle the complaints of the customers and report their concerns to the higher authorities. This work is mostly done over the phone. Thus, if any customer is not satisfied with the product they have received, they call the CSR staffs of the companies. The staffs are bound to entertain their calls. However, if the number of calls are quite high, then the angry calls are a reason for irritation to these staffs (Baranik et al. 2017, pp.1261-1282). Thus, as a remedy to this cause, the products and services of the company has to be developed so that the customers do not have much opportunity to register any angry complaint (Eichorn 2018, p.1).

Conclusion

As a conclusion to the analysis conducted, it can be said that the delivery of the products in the right time and right quantity has to be taken care of. Losing the products in transit needs to be stopped and the products needs to be ready in time. Recruiting more trained staffs can be helpful in reducing these problems. If the company can develop their product and service quality, the customers will not be complaining much about the products and hence the CSR representatives will not face any problem regarding angry phone calls.

References

Baranik, L.E., Wang, M., Gong, Y. & Shi, J. 2017. Customer mistreatment, employee health, and job performance: Cognitive rumination and social sharing as mediating mechanisms. Journal of Management, vol. 43, no. 4, pp.1261-1282.

Eichorn, F.L. 2018. Internal customer relationship management (IntCRM) a framework for achieving customer relationship management from the inside out. Management, vol. 2, p.1.

Kalpakjian, S., Vijai Sekar, K.S. & Schmid, S.R. 2014. Manufacturing engineering and technology. Pearson.

Psaraftis, H.N., Wen, M. & Kontovas, C.A. 2016. Dynamic vehicle routing problems: Three decades and counting. Networks, vol. 67, no. 1, pp.3-31.

Radziwon, A., Bilberg, A., Bogers, M. & Madsen, E.S. 2014. The smart factory: exploring adaptive and flexible manufacturing solutions. Procedia engineering, vol. 69, pp.1184-1190.

Sekaran, U. & Bougie, R. 2016. Research methods for business: A skill building approach. John Wiley & Sons.

Vezzoli, C., Ceschin, F., Diehl, J.C. & Kohtala, C. 2015. New design challenges to widely implement ‘Sustainable Product–Service Systems’. Journal of Cleaner Production, vol. 97, pp.1-12.

Vidal, T., Crainic, T.G., Gendreau, M. & Prins, C. 2014. A unified solution framework for multi-attribute vehicle routing problems. European Journal of Operational Research, vol. 234, no. 3, pp.658-673.