SOLVE IN EXCEL

There are three Staples stores in Windsor. Assume weekly demand for a Dell 27 monitors at each store is normally distributed with a mean of 500 and a standard deviation of 300. The demands at those stores are independent. Each monitor costs $250, and averages about 5 kg in weight. The Dells distribution center ships each Stapless order within 2days of receiving it (The lead time must consider the process time). The store uses annual holding cost of 15 percent. There are 7 days in a week and 52 weeks in a year. Now Staples wants to evaluate a proposal from a logistics company, who provides trucking service that charges $25 per 100 kg if the quantity shipped is at least 9,000 kg. The average shipping time is 1 day.

1). Staples carries inventory at each store and each store orders from Dell independently. The shipment to each store is also independent. Each store has traditionally purchased lots of 1,000 monitors but can adjust the lot size based on the logistics company to reduce the total cost.

1. Each store of Staples carries a safety inventory equal to 65 percent of the average demand for monitors during the delivery lead time. Calculate the annual total transportation and inventory cost for all three stores (Note: the in-transits inventory also should be considered.).
2. If the shipping time is uncertain and the standard deviation of the lead time is 2 days. Staples carries the safety inventory targeting a cycle service level of 0.95. Calculate the annual total transportation and inventory cost for all stores (Note: the in-transits inventory also should be considered.).
3. Following b), evaluate average inventory at each store and average time spent by the monitor at Staples in Windsor.

2). The new manager who has a masters degree in supply chain engineering and logistics proposes a new centralized inventory policy: Only one store, called Center Store, in Windsor carries the monitors inventory and makes orders based on the total demand in Windsor. All the monitors shipments are going to the center store.

4. The center store carries a safety inventory equal to 65 percent of the average demand during the delivery lead time. Calculate the annual total transportation and inventory cost for the new policy (Note: the in-transits inventory also should be considered.).
5. If the shipping time is uncertain and the standard deviation of the lead time is 2 days. The center store carries the safety inventory targeting a cycle service level of 0.95. Calculate the annual total transportation and inventory cost for the new policy (Note: the in-transits inventory also should be considered.).