Group Project Consider a supply chain with the three stages: Retailer, Distributor, and Manufacturer. The Retailer faces period demand that is normally distributed with mean and standard deviation o. Assume that demands in different days are independent and identically distributed. Furthermore, assume that the Retailer uses a continuous review inventory policy and places an order QR with the Distributor whenever its inventory level reaches the reorder point ROPR. The Retailer's ordering lead time is LR periods, and will receive the full ordered quantity if the Distributor has sufficient inventory on hand; otherwise, the Distributor will send whatever they have in stock. Any unmet demand by the Retailer will be lost. The Distributor, in turn, also uses the continuous review inventory policy, and places an order Q with the Manufacturer whenever its inventory level reaches the reorder point ROPD. The Distributor's ordering lead time is LD periods, and it is assumed that the manufacturer will never run out of stock. Given that the supply chain needs to meet a cycle service level of CSL or product fill rate fr, what should the ordering policy be for both the Retailer and the Distributor that will meet the service level, while at the same time minimize their total inventory costs? In other words, for a specific CSL or fr, find QR, QD, ROPR, and ROPD; such that, the total inventory carrying cost and ordering costs are minimized. The inventory carrying costs per unit per period at the Retailer and Distributor are HR and HD respectively. It is assumed that HR > HD. This suggests that pushing inventory upstream the supply chain will be cheaper to carry, however, the lead time will be longer to meet the end consumers' demand. Finally, the ordering costs for the Retailer and Distributor are SR and SD respectively. Given the following: Average demand per period = 500 Standard deviation of demand per period o = 200 Retailer's holding cost per unit per period HR = 5 Distributor's holding cost per unit per period HD = 3.75 Retailer's ordering cost SR = 1000 Distributor's ordering cost S = 1500 Retailer's lead time LR = 2 Distributor's lead time LD = 2 Item fill rate fr = 98% Simulate the supply chain using Microsoft Excel to determine the inventory ordering policy for both the Retailer and Distributor that will minimize total supply chain cost.