Safety Stock and Economic Order Quantity

Safety Stock and Economic Order Quantity

How can I use Excel QM to determine Safety Stock problems and economic order quantity problems?

Questions to be solved in Excel QM

6-20 Lila Battle has determined that the annual demand for number 6 screws is 100,000 screws. Lila, who works in her brother’s hardware store, is in charge of purchasing. She estimates that it costs $10 every time an order is placed. This cost includes her wages, the cost of the forms used in placing the order, and so on. Furthermore, she estimates that the cost of carrying one screw in inventory for a year is one-half of 1 cent. Assume that the demand is constant throughout the year.

How many number 6 screws should Lila order at a time if she wishes to minimize total inventory cost?

How many orders per year would be placed? What would the annual ordering cost be?

What would the average inventory be? What would the annual holding cost be?

6-22 Lila’s brother believes that she places too many orders for screws per year. He believes that an order should be placed only twice per year. If Lila follows her brother’s policy, how much more would this cost every year over the ordering policy that she developed in Problem 6-20? If only two orders were placed each year, what effect would this have on the ROP?

6-24 Ken Ramsing has been in the lumber business for most of his life. Ken’s biggest competitor is Pacific Woods. Through many years of experience, Ken knows that the ordering cost for an order of plywood is $25 and that the carrying cost is 25% of the unit cost. Both Ken and Pacific Woods receive plywood in loads that cost $100 per load. Furthermore, Ken and Pacific Woods use the same supplier of plywood, and Ken was able to find out that Pacific Woods orders in quantities of 4,000 loads at a time. Ken also knows that 4,000 loads is the EOQ for Pacific Woods. What is the annual demand in loads of plywood for Pacific Woods?

6-30 After analyzing the costs of various options for obtaining brackets, Ross White (see Problems 6-27 through 6-29) recognizes that although he knows that the lead time is 2 days and the demand per day averages 10 units, the demand during the lead time often varies. Ross has kept very careful records and has determined that lead time demand is normally distributed with a standard deviation of 1.5 units.

What Z value would be appropriate for a 98% service level?

What safety stock should Ross maintain if he wants a 98% service level?

What is the adjusted ROP for the brackets?

What is the annual holding cost for the safety stock if the annual holding cost per unit is $1.50?