Consider following spreadsheet for the Aggregate Planning model: What cell entry should be in B32? D E Surestep aggregate planning model 2 3 Input data 4 Initial inventory of shoes 5 Initial number of workers 6 Regular hours/worker/month ? Maximum overtime hours/worker/month 8 Hiring cost/worker 9 Firing cost/worker 10 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 16 Worker plan 12 Workers from previous month 18 Workers hired 19 Workers fired 20 Workers available after hiring and firing SOO 100 160 20 $1,600 $2,000 $1,500 $13 4 $15 SB Month 1 Month 2 Month 3 Month 4 22 Regular-time hours available 23 Overtime labor hours used 25 Marmom overtime tabor hours available 25 27 Total hours for production Month Month 2 Months Month 4 20 Production plan 30 Shoes produced Production capacity inventory after prodition 5000 1000 Forecasted oemand Ending intory $15 $9 Month 1 Month Month Month 12 Labor hour pair of shoes 13 Raw material cost/pan of shoes 14 og cost/paw of shoes in inventory/month 15 16 Worksplan IT Workers from previous month 18 Workers Nired 19 Workers tired 20 workers available after Noring and try 21 22 Regular-me hours available 21 Overtime labor hoursused 2 25 Maximum overtime labor hours available 27 Total hours for procation Month Month 2 Month Month 4 29 Production plan 20 Shoes produced . 32 Production capacity 30 14 Inventory after production 000 5000 2000 1000 Forecasted demand 37 Ending Inventory =B30/$B$12 -B27"$B$12 -B27/$B$12 -B27 The decision variables in transportation problems are: Flows O Profits O Capacities Costs E G Plant net outflow Plant capacity Node balance constraints Plant constraints Node 1 2 3 OR 300 100 Warehouse constraints Node Warehouse not outfiw Required What cell formula would be in B33: D 1 RedBrand shipping model 2 Network structure, flows, and are capacity constraints 4 Origin Destination Unit Cost Flow 5 1 2 5 5 1 3 3 1 4 5 1 5 5 9 1 6 20 10 7 20 11 2 1 2 3 10 2 4 1 5 1 15 2 6 3 7 15 3 0.4 3 3 19 3 3 05 2 3 . 10 2 5 2 6 7 5 Customer net inflow Customer constraints Node 6 7 Customer demand 100 180 WE HEBB 5 3 5 2 22 Objective to mi SUMPRODUCT Fow. Un cot) SUMO 6 20 20 Required 9 9 Warehouse constraints Node Warehouse net outflow 4 5 1 Customer constraints Node 6 Customer net inflow Customer demand 400 > 180 7 9 1 6 10 1 7 11 2 1 12 2 3 13 2 4 14 2 5 15 2 6 16 2 7 17 3 1 18 3 2 19 3 4 20 3 5 21 3 6 22 3 7 23 4 5 24 4 6 25 4 7 26 5 4 27 5 6 20 5 7 29 6 7 30 7 G 31 32 Objective to minimize 33 Total cost 1 8 15 0.4 8 1 0.5 10 12 1.2 2 12 0.8 2 12 1 7 =SUMPRODUCT (Flow. Unit cost) -SUM(Flow) -SUMIF(Destination, F6, Flow) -SUMIF(Origin Fo Flow) Next Previous Use the following screenshot: If I were to put a sign in cells C21:F21 to remind me what to use in Solver, I would put: B D E F H 1 Assignment of jobs to machines 2 3 Times to perform jobs on various machines 4 Job 5 1 2 3 4 6 Machine 1 5 7 7 2 2 12 6 5 8 3 7 8 3 9 9 4 2 6 10 10 5 5 11 12 Assignments, and constraints on machine capacities and job completion requirements Job 14 Jobs on Machine capacity 15 Machine 16 17 15 19 20 Machines on job 23 2 Required U UN 24 Objective to minimize Total time What is in cell B28? E G H Employee scheduling madal 2 3 Decision variables sumber of employee starting the five-day shit on various day Tue 6 Wed 12 Result of decision number of employees working on various day (along tap) who started their shift on various day (along side) M Tee Sa 14 M 15 The 22 Constraint on employee available 23 Emile SCHES DISTUS ON Consider following spreadsheet for the Aggregate Planning model: What cell entry should be in B32? D E Surestep aggregate planning model 2 3 Input data 4 Initial inventory of shoes 5 Initial number of workers 6 Regular hours/worker/month ? Maximum overtime hours/worker/month 8 Hiring cost/worker 9 Firing cost/worker 10 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 16 Worker plan 12 Workers from previous month 18 Workers hired 19 Workers fired 20 Workers available after hiring and firing SOO 100 160 20 $1,600 $2,000 $1,500 $13 4 $15 SB Month 1 Month 2 Month 3 Month 4 22 Regular-time hours available 23 Overtime labor hours used 25 Marmom overtime tabor hours available 25 27 Total hours for production Month Month 2 Months Month 4 20 Production plan 30 Shoes produced Production capacity inventory after prodition 5000 1000 Forecasted oemand Ending intory $15 $9 Month 1 Month Month Month 12 Labor hour pair of shoes 13 Raw material cost/pan of shoes 14 og cost/paw of shoes in inventory/month 15 16 Worksplan IT Workers from previous month 18 Workers Nired 19 Workers tired 20 workers available after Noring and try 21 22 Regular-me hours available 21 Overtime labor hoursused 2 25 Maximum overtime labor hours available 27 Total hours for procation Month Month 2 Month Month 4 29 Production plan 20 Shoes produced . 32 Production capacity 30 14 Inventory after production 000 5000 2000 1000 Forecasted demand 37 Ending Inventory =B30/$B$12 -B27"$B$12 -B27/$B$12 -B27 The decision variables in transportation problems are: Flows O Profits O Capacities Costs E G Plant net outflow Plant capacity Node balance constraints Plant constraints Node 1 2 3 OR 300 100 Warehouse constraints Node Warehouse not outfiw Required What cell formula would be in B33: D 1 RedBrand shipping model 2 Network structure, flows, and are capacity constraints 4 Origin Destination Unit Cost Flow 5 1 2 5 5 1 3 3 1 4 5 1 5 5 9 1 6 20 10 7 20 11 2 1 2 3 10 2 4 1 5 1 15 2 6 3 7 15 3 0.4 3 3 19 3 3 05 2 3 . 10 2 5 2 6 7 5 Customer net inflow Customer constraints Node 6 7 Customer demand 100 180 WE HEBB 5 3 5 2 22 Objective to mi SUMPRODUCT Fow. Un cot) SUMO 6 20 20 Required 9 9 Warehouse constraints Node Warehouse net outflow 4 5 1 Customer constraints Node 6 Customer net inflow Customer demand 400 > 180 7 9 1 6 10 1 7 11 2 1 12 2 3 13 2 4 14 2 5 15 2 6 16 2 7 17 3 1 18 3 2 19 3 4 20 3 5 21 3 6 22 3 7 23 4 5 24 4 6 25 4 7 26 5 4 27 5 6 20 5 7 29 6 7 30 7 G 31 32 Objective to minimize 33 Total cost 1 8 15 0.4 8 1 0.5 10 12 1.2 2 12 0.8 2 12 1 7 =SUMPRODUCT (Flow. Unit cost) -SUM(Flow) -SUMIF(Destination, F6, Flow) -SUMIF(Origin Fo Flow) Next Previous Use the following screenshot: If I were to put a sign in cells C21:F21 to remind me what to use in Solver, I would put: B D E F H 1 Assignment of jobs to machines 2 3 Times to perform jobs on various machines 4 Job 5 1 2 3 4 6 Machine 1 5 7 7 2 2 12 6 5 8 3 7 8 3 9 9 4 2 6 10 10 5 5 11 12 Assignments, and constraints on machine capacities and job completion requirements Job 14 Jobs on Machine capacity 15 Machine 16 17 15 19 20 Machines on job 23 2 Required U UN 24 Objective to minimize Total time What is in cell B28? E G H Employee scheduling madal 2 3 Decision variables sumber of employee starting the five-day shit on various day Tue 6 Wed 12 Result of decision number of employees working on various day (along tap) who started their shift on various day (along side) M Tee Sa 14 M 15 The 22 Constraint on employee available 23 Emile SCHES DISTUS ON