topic: production & inv. system

please use excel, and provide clear steps

Problem 3: 10 pts Companys pays regular-time wages of $5,000 per employee per period for any time worked up to 500 hours (including undertime). The overtime pay rate is $15 per hour. Overtime cannot exceed 20 percent of the regular-time capacity in any period. Hiring a new employee costs $7,000. Layoff costs are $1,500 per employee. Currently, 40 employees work for Companys in this capacity. No delays in service, or backorders, are allowed. The forecast demand considered for the S&OP is provided below: 1 2 3 4 Total 40 30 45 25 a. Prepare a chase strategy using only hiring and layoffs. What are the total numbers of employees hired and laid off? b. Develop a workforce plan that uses the level strategy, relaying only on overtime and undertime. Maximize the use of overtime during the peak period so as to minimize the workforce level and amount of undertime. C. Propose an effective mixed-strategy plan. d. Compare the total costs of the three plans. Inputs Forecasted demand Workforce level Undertime Overtime Derived Utilized time Hires Layoffs Calculated Utilized time cost Undertime cost Overtime cost Hiring cost Layoff cost Total cost Problem 4: 10 pts The MPS start quantities for product A calls for the assembly department to begin final assembly according to the following schedule: LT = 2 B (2) C (1) 100 units in week 2; 200 units in week 4 120 units in week 6; 180 units in week 7 LT = 1 LT = 2 60 units in week 8. Develop a material requirements plan for the next 8 weeks for items B, C, and D. D (2) INVENTORY RECORD DATA Item LT = 3 B D L4L POQ (P =3) 1 week FOQ = 500 units 3 weeks Data Category Lot-sizing rule Lead time Scheduled receipts Beginning (on-hand) inventory 2 weeks 100 (week 1) None None 20 0 425