1. List all of the constraints for the aggregate planning model using appropriate cell references. 2. Modify the aggregate planning model in the ff. way. Assume that the timing of demand and production are such that only 70% of production in a given month can be used to satisfy the demand in that month. Change the appropriate formulas for month 3 (column D) to reflect this modification 3. Assume that it takes 5 hours to produce a pair of shoes using overtime labors. Change the appropriate formulas for month 3 (column D) to reflect this modification

F A B SureStep aggregate planning model Input data Initial inventory of shoes Initial number of workers Regular hours/worker/month Maximum overtime hours/worker/month Hiring cost/worker Firing cost/worker 0 Regular wages/worker/month 11 Overtime wage rate/hour 12 Labor hours/pair of shoes 13 Raw material cost/pair of shoes 14 Holding cost/pair of shoes in inventory/month 15 500 100 160 20 $1,600 $2,000 $1,500 Labor hours/pair of shoes for overtime. $13 4 $15 $3 16 Worker plan 17 Workers from previous month 18 Workers hired 19 Workers fired 20 Workers available after hiring and firing Month 1 Month 2 Month. Month 4 100 94 93 50 0 0 0 6 1 43 0 94 93 50 50 21 22 Regular-time hours available 23 Overtime labor hours used 14880 15040 8000 8000 80 0 0 24 25 Maximum overtime labor hours available 1860 1880 1000 1000 26 27 Total hours for production 15040 14960 8000 8000 28 29 Production plan Month 1 Month 2 Month 4 Month 3 30 Shoes produced 3760 3740 2000 1000 1 2 Production capacity 3740 3760 2000 2000 Inventory after production 4260 5000 2000 1000 Forecested demand- Ending inventory 3000 5000 2000 1000 1260 0 Monetary outputs Hiring cost Firing cost Cegular-time wages vertime wages aw material cost Month 1 Month 2 Month 3 Month 4 Totals $0 S0 $0 $86,000 $75,000 $0 $0 $12,000 $141,000 $2,000 $139,500 $1,040 $56,100 $100,000 $430,500 $1,040 $157,500 $0 $0 $75,000 S0 $56,400 $3,780 $213,180 $30,000 $0 $15.000 olding cost $0 $3,780 tals $198,640 $191,000 $90,000 $692,820 F A B SureStep aggregate planning model Input data Initial inventory of shoes Initial number of workers Regular hours/worker/month Maximum overtime hours/worker/month Hiring cost/worker Firing cost/worker 0 Regular wages/worker/month 11 Overtime wage rate/hour 12 Labor hours/pair of shoes 13 Raw material cost/pair of shoes 14 Holding cost/pair of shoes in inventory/month 15 500 100 160 20 $1,600 $2,000 $1,500 Labor hours/pair of shoes for overtime. $13 4 $15 $3 16 Worker plan 17 Workers from previous month 18 Workers hired 19 Workers fired 20 Workers available after hiring and firing Month 1 Month 2 Month. Month 4 100 94 93 50 0 0 0 6 1 43 0 94 93 50 50 21 22 Regular-time hours available 23 Overtime labor hours used 14880 15040 8000 8000 80 0 0 24 25 Maximum overtime labor hours available 1860 1880 1000 1000 26 27 Total hours for production 15040 14960 8000 8000 28 29 Production plan Month 1 Month 2 Month 4 Month 3 30 Shoes produced 3760 3740 2000 1000 1 2 Production capacity 3740 3760 2000 2000 Inventory after production 4260 5000 2000 1000 Forecested demand- Ending inventory 3000 5000 2000 1000 1260 0 Monetary outputs Hiring cost Firing cost Cegular-time wages vertime wages aw material cost Month 1 Month 2 Month 3 Month 4 Totals $0 S0 $0 $86,000 $75,000 $0 $0 $12,000 $141,000 $2,000 $139,500 $1,040 $56,100 $100,000 $430,500 $1,040 $157,500 $0 $0 $75,000 S0 $56,400 $3,780 $213,180 $30,000 $0 $15.000 olding cost $0 $3,780 tals $198,640 $191,000 $90,000 $692,820