Supply Chain Analytics: Customer Complaint Database Analysis Report

This report analyzes a customer complaint database for a distributor, utilizing Microsoft Excel to identify complaint trends, frequently complaining customers, and complaint types. The analysis includes bar charts and pivot tables to summarize the data and evaluate problems in the distribution chain and service delivery. The report assesses warehouse policies, comparing regular and economic ordering policies to minimize inventory costs and ensure demand sustenance. The Economic Order Quantity (EOQ) model is applied to determine optimal order sizes. A root cause analysis, using the five whys method, identifies issues related to delivery, wrong quantities, and lost-in-transit items. Solutions are proposed, including improvements to inbound and outbound logistics, warehouse operations, layout, and staff management, to reduce errors and improve efficiency through automation and computerized inventory systems. The report concludes that most errors are avoidable through improved processes and accountability.

Ali Alshehri 12475327 Supply Chain Analytics Assignment 1
Introduction
This is a report based on the analysis of data for a distributor’s customer complaint
database. The report utilizes Microsoft excel to count the number of complaints, to find out the
most frequently complaining customer, and to look up all the complaint types. Furthermore, the
report presents a summary of the customer complaint data and presents the data in bar charts and
pivot tables. The report evaluates, and the main cause of the problems associate with the
distribution chain and service delivery. The report evaluates the possible use of warehouse
policies to improve demand sustenance, taking into consideration the key assumptions.
Task 2: Improvement of Warehouse Policies
The manager has two main options to choose from, regular ordering policy and economic
ordering policy. The inventory management system should be designed in a way that minimizes
inventory but also ensures there will never be a stockout. Excess inventory is a waste, and it
increases operating costs unnecessarily (Inventory Management, n.d.). Inventory management
has several objectives that must be considered in the choice of ordering policy. These objectives
are:
 Minimize ordering costs.
 Minimize carrying/holding costs.
 Minimize the cost of material handling.
 Eliminate the cost of excess inventory.
 Ensure the availability of enough stock to supply the existing demand.
 Avail safety stock.
1 15/04/2020

Ali Alshehri 12475327 Supply Chain Analytics Assignment 2
 Minimize damage and obsolesce of inventory in storage.
The regular ordering policy, also known as periodic ordering system, is a technique in
which a business makes orders on a fixed schedule, for example, monthly or weekly. The
ordering policy and order sizes are determined by deciding on an order cycle that does not let
inventory reduce below the safety stock, and orders are placed according to the maximum
expected demand. This ordering method presents a number of advantages, including the ease of
making operational plans since the ordering and receipt dates are known and the reduction of
costs resulting from delivery timing. The main disadvantage of this method is that the order
quality must be calculated every period and the restrictions and risk of stocks outs due to lack of
flexibility (Muller, 2019).
In this case, if the manager was to adopt the periodic ordering policy for the two-period
considering an ordering cost of $65, and a carrying cost of 2.5 dollars per month. The costs
would be as follows:
Assuming that only half of the order is stored enough to incur the monthly carrying cost
and assuming a regular (monthly orders) of 100 units:
Ordering cost= 500/100*65= $325
Carrying costs= 50*6*2.5=750
Ordering cost= 1000/100*65=650
Carrying costs= 50*12*2.5=1500
A total cost incurred in inventory would be $3225
2 15/04/2020

Ali Alshehri 12475327 Supply Chain Analytics Assignment 3
This cost is too much, according to many researchers, economic ordering policy is the
most economical (Chiang; Shah, 2012; Umamaheswari, Chandrasekeran, & Vijayalakshmi,
2015; Chang, Ho, Ouyang, & Su, 2010). It bears the advantage of minimizing both ordering
costs and carrying costs. The only outstanding disadvantage is that it is based on extensive
assumptions (Harbour, 2014). It is this method that is used to determine the optimal order size
for this case.
The cost of placing an order is 65 dollars and the carrying cost is 2.5 dollars per month
EOQ = 2SD / H ½
For the first 6 months
S=ordering cost = 65
D=500=demand
H=2.5=holding costs
EOQ = 2*65*500/2.5 ½ =162 Units. , this is the optimal order lot size for the first 6
months.
For the second 6-month period, S=65, D=1000, H=2.5
EOQ = 2SD/H ½
EOQ = 2*65*1000/2.5½ =229, this is the optimal order lot size for the second 6-month
period.
The most common methods for ordering policies are the Economic Order Quantity,
Inventory Production Quantity, and ABC analysis. For a demand that is extremely variant for the
3 15/04/2020

Ali Alshehri 12475327 Supply Chain Analytics Assignment 4
six months, the most suitable inventory management model is the Inventory Production Quantity
which applies the formula IPQ = √ 2 KD
H (1−X ) CITATION Lau19 \l 1033 (Bartmann & Bach,
2012)
Where, K = Setup Costs
D=Demand rate
P= yearly production rate
X = D/P
Task 3: Problem Definition
From the data analysis on the excel file, the following top occurrences were identified
Top 3 Complaint Types
1. Delivery
2. Wrong Quantity
3. Lost In Transit
Top 3 Product Families
1. PF12
2. P4
3. PF11
Top 3 Complaining Customers
1. C10
4 15/04/2020

Ali Alshehri 12475327 Supply Chain Analytics Assignment 5
2. C11
3. C150
Top 3 CSR Staff
1. P6
2. P4
3. P8
Root Cause Analysis for Complaint Types
The root cause analysis method used in this analysis is the five whys analysis.
Delivery
Why? - The customer did not receive the products/service ordered.
Why? - The logistics service providers did not deliver.
Why? - The dispatch did not give the logistics service provider the order
Why? - The dispatch had no items to give to logistics.
Why? - There weren't enough items in the store.
The reason why customers complaint of no-delivery was because the seller had not stock
to dispatch to the buyer.
1. Wrong Quantity
Why? - customers receive more or less of what they ordered
Why? - Logistics deliver wrong amounts
5 15/04/2020

Ali Alshehri 12475327 Supply Chain Analytics Assignment 6
Why? - Logistics were given the wrong amount
Why? - Dispatch packed wrong amounts
Why? - Dispatch received the wrong information from the sales department.
The customer is receiving wrong order quantities because the distributor lacks an
effective information transfer and communication system.
2. Lost In transit
Why? – Some items dispatched did not reach the destined recipient.
Why? – Goods in transit are not effectively monitored
Why? – Lack of computerized monitoring equipment and use of complex routes
Why? – Poor choice of route
Why? – Failure to optimize travel paths.
Some goods are getting lost during transit because the distributor uses logistics services
that are not monitored in real time and are not optimized for efficiency.
Task 4: Suggestion and Solutions
Given the complaints that customers are mostly launching to the distributor, it is evident
that the distribution chain has loopholes and are challenged on all fronts. The entire chain is
flawed and needs to be corrected with urgency to reduce the frequency of mistakes and
consequently reduce the number of complaints.
Inbound and Outbound- inbound logistics should be logged in through a computerized
inventory management system. This will see to tit that errors in the approximation of stock on
6 15/04/2020

Ali Alshehri 12475327 Supply Chain Analytics Assignment 7
hand are reduced and will help in eliminating the risk of stocks out. Inbound logistics should be
managed by the distributor and not by a 3PL to improve accountability and ease the process of
monitoring inventory movement.
Put away and Operations- put away should be done in cross-checking with
computerized inventory systems. The inventory management system should be networked with
the entire warehousing operations such that every department gets real time data. The company
should also conduct weekly inventory audits to expose and eliminate any irregularities and
uncertainties (Abbasi, Babaei, Hosseinifard, Smith-Miles, & Dehghani, 2020).
Warehouse layout- Products should be arranged in an open layout with sufficient
gangways and optimized accessibility. Stacking should be done according to product categories,
and each should be labeled with barcodes.
Warehouse staff- warehouse staff, should be reduced. This will solve the accountability
problem and reduce the case of lost inventory. The few remaining staff should operate from their
computers and hand-worn bands to track their movements and to monitor which stacks they
operate on, whether its stacking or otherwise (Patne, Shukla, Kiridena, & Tiwari, 2018).
Machines and equipment- all equipment for indoor operations and for logistics should
be fitted with trackers and computer monitors to monitor their movement and service in real
time.
Conclusion
Most of the errors experienced by customers in this particular supply chain are avoidable
errors. Most of them are human errors and result from a lack of accountability and thorough
7 15/04/2020

Ali Alshehri 12475327 Supply Chain Analytics Assignment 8
attention to details. All these can be eliminated to a level of almost 100% through automation
and the use of computerized inventory management and enterprise resource planning.
8 15/04/2020

Ali Alshehri 12475327 Supply Chain Analytics Assignment 9
References
Abbasi, B., Babaei, T., Hosseinifard, Z., Smith-Miles, K., & Dehghani, M. (2020). Predicting
Solutions of Large-Scale Optimization Problems via Machine Learning: A Case Study in
Blood Supply Chain Management. Computers \& Operations Research.
Bartmann, D., & Bach, M. F. (2012). Inventory control: models and methods. Springer Science
& Business Media.
Chang, H.-C., Ho, C.-H., Ouyang, L.-Y., & Su, C.-H. (2010). The optimal pricing and ordering
policy for an integrated inventory model when trade credit linked to order quantity.
Applied Mathematical Modelling, 33(7), 2978-2991.
Chiang, C. (n.d.). Ordering Policies for Inventory Systems with Expediting. The Journal of the
Operational Research Society, 53(11), 1291-`1295.
Harbour, S. (2014). The Advantages & Disadvantages of Economic Order Quantity (EOQ).
Demand Media.
Inventory Management. (n.d.). Retrieved from https://msu.edu/course/prr/473/oldstuff/Inventory
%20Management.htm
Muller, M. (2019). Essentials of inventory management. HarperCollins Leadership.
Patne, K., Shukla, N., Kiridena, S., & Tiwari, M. K. (2018). Solving closed-loop supply chain
problems using game theoretic particle swarm optimisation. International Journal of
Production Research, 56(17), 5836--5853.
9 15/04/2020

Ali Alshehri 12475327 Supply Chain Analytics Assignment 10
Shah, N. H. (2012). Ordering Cost for Inventory Managemennt When Demadn is Stock
Dependent and a Temporary Price Discount Is Linked toOrder quantity. Revista
INvestigacion Operacional, 33(3), 233-244.
Umamaheswari, S., Chandrasekeran, E., & Vijayalakshmi, C. (2015). Analysis Of an Inventory
Model FOr Optimum ordering Policy and Economic Purchasing Strategy. International
Jounral Of Pure and applied mathematics, 101(5), 739-752.
10
15/04/2020