Answers to the questions are provided. I need to know how they are obtained. Please show work, and use excel if possible.
Question: The Home and Garden (HG) chain of superstores imports decorative planters from Italy. Weekly demand for planters average 1,500 with a standard deviation of 800. Each planter costs $10. HG incurs a holding cost of 25% per year to carry inventory. HE has an opportunity to set up a superstore in the Phoenix region. Each order shipped from Italy incurs a fixed transportation and delivery cost of $10,000. Consider 52 weeks in the year.
a) Determine the optimal order quantity of planters for HG.
Answer - The optimal order quantity of planters for HG is 24,980.
b) If the delivery lead time from Italy is 4 weeks and HG wants to provide its customers a cycle service level of 90%, how much safety stock should it carry?
Answer - Safety stock = 2048 units (Do this problem using the Standard Normal Table and see if you get the same or close answer)
c) Fastship is a new shipping company that promises to reduce the delivery lead time for planters from 4 weeks to 1 week using a faster ship and expedited customs clearance. Using Fastship will add $0.2 to the cost of each planter. Should HG go with Fastship? Why or why not? Quantify the impact of the change.
Answer - Additional transportation cost per year = $15,600; Savings in holding cost = $2560.00; Thus Fastship should not be used. (Do this problem using the Standard Normal Table and see if you get the same or close answer.)