1. Your company manufactures bowling balls in Akron, Ohio. The primary raw material is polyurethane that you order from suppliers. The cost to place each order is $250. The price of the material is $4000 per ton delivered to your plant regardless of lot size. Annual demand for polyurethane at your plant is 76,000 tons. Your company uses 4.5% to calculate its annual cost of carrying materials in inventory. What is the optimal lot size in order to minimize the sum of annual ordering costs and inventory carrying costs of polyurethane? What is the total annual cost based on your optimal lot size?
If you supplier offered you a 10% price reduction per order for minimum orders over 500 tons, would you continue to use your optimal lot size or order at least the minimum? Support your decision with calculations.
Please show and explain calculations in excel
2. Given the following historical data on two alternative suppliers of a critical material your company requires, which of the two suppliers (supplier 1 or supplier 2) will minimize your required safety stock? Show and describe your analysis!
Base your analysis on the supplied data and assume "all else is equal" between the two suppliers. In other words, cost per unit, quality, TCO, etc. do not need to be considered. Do not concern yourself with any other issues or data other than that which is given below.
For any given period, your company's goal is to have required materials available in inventory 99.9% of the time.
Your mean requirement (demand) per day: 700 units.
Standard deviation of your daily requirement: 65 units.
Supplier 1
Mean lead-time: 7 days
Standard deviation of lead-time: 1 day
Supplier 2
Mean lead-time: 5 days
Standard deviation of lead-time: 2 days