Please use Excel. Q.18

For the data in problem 16, at the end of the first quarter, inventory is 2,000 units.

Mike Blanford, master scheduler at General Avionics, has the following demand forecast for one line in his factory: At the beginning of quarter 1 , there are 1,000 units in inventory. The firm has prepared the following data: Hiring cost per employee =$200 Firing cost per employee =$400 Beginning workforce =60 employees Inventory carrying cost =$2 per unit per quarter of ending inventory Stockout cost =$5 per unit Regular payroll =$1,200 per employee per quarter Overtime cost =$2 per unit Each employee can produce 100 units per quarter. Demand not satisfied in any quarter is lost and incurs a stockout penalty. If Mike produces exactly enough to meet demand each quarter, with no inventories at the end of quarters and no overtime, how much will he produce each quarter, and what is the overall cost? (Use a spreadsheet model for the calculations.) 8. For the data in problem 16 , at the end of the first quarter, inventory is 2,000 units. Marketing has revised the forecasts for quarters 2,3 , and 4 with a 20 percent reduction (i.e., remaining sales =16,000 ). Develop a level and chase plan for the revised forecast that provides for an inventory of at least 1,000 units at the end of each quarter. (Assume 50 people were in the workforce in the first quarter.)