Description
Complete all of the following problems using Excel. Refer to the Solved Problem Example for more insight.
Use yellow color to identify the final answer for each question! Show adequate work/computations
NOTE: No need for rounding up any of the values. Let Excel do the computations!
4-1. XYZ, Inc., produces a GPS device that is becoming popular in parts of Europe. When XYZ produces one of these, a printed circuit board (PCB) is used, and it is populated with several electronic components. XYZ determines that it needs about 16,000 per year of this type of PCB. Demand is relatively constant throughout the year, and the ordering cost is about $25 per order; the holding cost is 20% of the price of each PCB. Two companies are competing to become the dominant supplier of the PCBs, and both have now offered discounts, as shown in the following table.
Which of the two suppliers should be selected if XYZ wishes to minimize the total annual inventory cost? What would be the annual inventory cost?
SUPPLIER | A | SUPPLIER | B | |
Quantity | Price | Quantity | Price | |
1-199 | $38.40 | 1-299 | $39.50 | |
200-499 | 35.80 | 300-999 | 35.40 | |
500 or more | 34.70 | 1000 or more | 34.60 | |
4-2. Ross White’s machine shop uses 2,500 brackets during the course of a year, and this usage is relatively constant throughout the year. These brackets are purchased from a supplier 100 miles away for $15 each, and the lead time is 2 days. The holding cost per bracket per year is $1.50 (or 10% of the unit cost) and the ordering cost per order is $18.75. There are 250 working days per year.
Required:
(a). What is the EOQ?
( b.) Given the EOQ, what is the average inventory? What is the annual inventory holding costs?
( c). In minimizing cost, how many orders would be made each year? What would be the annual ordering cost?
( d). Given the EOQ, what is the total annual inventory cost, including purchase cost?
( e). What is the time between orders?
( f). What is the reorder point, ROP?
4-3. Refer to Problem 2. Suppose Ross wants to reexamine his decision of buying the bracket and is considering making the brackets in-house. He has determined that setup costs would be $25 in machinist time and lost production time, and 50 brackets could be produced in a day once the machine has been set up. Ross estimates that the cost (including labor time and materials) of producing one bracket would be $14.80. The holding cost would be 10% of this cost.
Required:
( a). What is the daily demand rate?
(b). What is the optimal production quantity?
( c). Given the optimal production run size in part b, what is the total annual inventory cost (including purchase cost, holding, and setup costs)?
4- 4. After analyzing the costs of various options for obtaining brackets, Ross (refer to 2 & 3 above) recognizes that although he knows that lead time is 2 days and demand per day averages 10 units, the demand during the lead time often varies. Ross has kept very good records and has determined lead time demand is normally distributed with a standard deviation of 1.5 units. Assume this company always order 250 units each time an order is placed.
Required:
( a). What Z value would be appropriate for a 98% service level? Use the normal distribution table.
(b). What safety stock should Ross maintain if he wants a 98% service level.
(c). What is the adjusted ROP for the brackets.
(d). What is the annual inventory holding cost including safety stock if the annual holding cost per unit is $1.50?
Hints: When standard deviation of lead time demand is known, safety stock= Z * standard deviation of the lead time demand. However, if you are given lead time, the daily demand and standard deviation of the daily demand, the safety stock = Z * standard deviation of the daily demand * SQRT(lead time).
ROP = lead time * daily demand + safety stock!
Responses must be in an excel spreadsheet using EXCEL QM Add in. I have listed the website to download the add in and book resources can also be found below
https://media.pearsoncmg.com/ph/bp/bridgepages/bp
Please computer responses on different tabs in excel
Needs help with similar assignment?
We are available 24x7 to deliver the best services and assignment ready within 6-12hours? Order a custom-written, plagiarism-free paper
Get Answer Over WhatsApp Order Paper Now