HW 3- Submit work in 1 excel file with answer cells that contain the formulas for your calculations and indicate units as necessary 1) A firm has 77 units of a product on hand. Forecasts of demand are 26 units per week. An MPS quantity of 90 units is planned to arrive in period 3. Customer orders are 29 for period 1,27 for period 2, and 19 for period 3.1 What is the projected on-hand inventory at the end of period 3? (10 pts) 2) A manager has prepared a forecast of expected aggregate demand. Develop an aggregate plan that assumes: A level production rate, back orders are allowed and are charged at the rate of $15 per unit per month, inventory holding costs are $2 per unit per month in average inventory, regular time cost is $7 per unit and beginning inventory is zero. Show the aggregate plan and determine the cost of this plan. (15 pts) Month Forecast 1 270 2 310 3 280 4 350 5 310 2801 nmin 3) Refer to this product tree: A (3) B (4) C(6) M (3) N (4) C (2) D (5) If 24 Ps are needed, and there are 6 Bs on hand, how many Cs will be needed? (15 pts) 4) Pumpkin Patties has just received an order for 900 gift baskets, which must be ready for delivery at the start of week 5. An MRP planner has prepared the following table showing product structure, lead times (orders are lot-for-lot), and quantities on hand: Deluxe Gift Basket Lead Time On-Hand Deluxe Gift Basket 1 week 10 Caramel Pumpkins (6 per) 2 weeks 12 Mini Pies (4 per) 1 week 0 What is the net requirement for Caramel Pumpkins to fill this order? (10 pts)+ 5) Weekly demand for a particular product averages 120 units, with a standard deviation of 8. This item is managed with a fixed-order-interval model. The order interval is 4 weeks, and this item has a certain lead time of one week. The desired service level is 97 percent. Assume that it is now time to place another order, and there are 61 units on hand. How many units should be ordered? (10 pts) 6) The manager of EZ-Mart (open 365 days a year) sells 50 cases of water each day. Order costs are $25 per order, and water costs $0.20 per bottle (each case of water contains 24 bottles). Daily holding costs are equal to 4 percent of the cost of the water. What is the economic order quantity for water? (10 pts) 7) You need to buy raw materials for your production process. You use 10,000 units of the raw material each year. The supplier quotes you a price of $5.50 per unit for an order size of 999 units or less, a price of $5 per unit for orders of 1000 to 1999 units, and a price of $4.50 per unit for an order of 2,000 or more units. You assign an annual holding cost of 20 percent of the price of the unit to this inventory. Assume ordering cost is $50/order. What order quantity would you use if the objective is to minimize total annual costs of holding, ordering, and purchasing (be sure to identity total costs of 3 options)? (20 pts) 8) The manufacturing company uses 1,000 units of raw material at its workstations each 10 hours of production. The cycle time for a standard container, which holds 20 units, averages 30 minutes. If management uses an efficiency factor of 60 percent, how many containers should be used? (10 pts)