modify the attached aggregate production plan by adding inventory logic. Solve for best cost with no backlog each month.

Mitigate stockout risk for a 15% forecast error.

Explain your approach:

Note: please use and put your answers in excel spreadsheet.

1 500 2 3 Beginning Inventory 4 Reg. time production cost/unit 5 Average inventory holding cost/unit/month 6 Jan 1 Workforce Size 7 Production Rate/employee/month 8 Max OT/Worker/Month 9 OT Prod Cost per unit 10 Hire 11 Fire 12 $200 $10 20 40 250 10000 10000 13 January 1600 August 1000 1 September October Now Dreier Terar 1000 750 600 12,210 228 2218 2218 818 883 2.813 883 300 February March Forecast April May June sool 750 1110 1000 Workers Hired 1500 Workers Feed Overtime Production Max OT 30 Worders 20 20 20 1 Production 800 NO SOO 300 Beginning inventory z Ending Inventory Average Inventory 2 * Production and Inventory costs Reg. time production cost in s $100,000 $160,000 $160,000 $160,000 $160,000 $ Inventory holding costs TOT Cost s $ $ 5 5 5 Miring cost $ $ $ 5 $ $ Hiring Cost $ $ $ $ $ $ 160.000 $100,000 $160,000 $ 160.000 $100,000 $100,000 $150.000 $1,930.000 $ $ $ 5 $ 5 5 5 s 5 $ 5 5 $ 5 $ $ SL000 B C D E F G H 1 M N 13 14 15 Any February March Agni 500 7501 11101 Total May 1400 je 1500 July 1600 August September October 1400 1300 1000 November December 750 6001 300 12,210 16 17 18 19 80 80 80 20 80 30 80 Forecast Workers Hired Worbers Fired Overtime Production Max OT #Worders Production Beginning inventory Ending Inventory Average Inventory 20 20 20 20 20 20 838 20 20 20 800 20 20 300 80 20 800 800 800 800 800 800 200 800 800 21 22 23 24 25 $180.000 $100.000 $100.000 $10,000 $10,000 $160,000 $10,000 $160,000 $160,000 $ 160,000 160,000 $100,000 1.920,000 27 Production and Inventory Costs Retime production cost in Inventory holding cost in $ OT Cout ong cost Firing Cou $ $ $ $ $ 5 $ $ 5 $ $ 5 28 29 30 31 32 $ $ $ - $ 5 $ $ 5 - 5 $ $ 5 5 $ $ $ $ 5 5 5 $ $ $ 1.920,000 1 500 2 3 Beginning Inventory 4 Reg. time production cost/unit 5 Average inventory holding cost/unit/month 6 Jan 1 Workforce Size 7 Production Rate/employee/month 8 Max OT/Worker/Month 9 OT Prod Cost per unit 10 Hire 11 Fire 12 $200 $10 20 40 250 10000 10000 13 January 1600 August 1000 1 September October Now Dreier Terar 1000 750 600 12,210 228 2218 2218 818 883 2.813 883 300 February March Forecast April May June sool 750 1110 1000 Workers Hired 1500 Workers Feed Overtime Production Max OT 30 Worders 20 20 20 1 Production 800 NO SOO 300 Beginning inventory z Ending Inventory Average Inventory 2 * Production and Inventory costs Reg. time production cost in s $100,000 $160,000 $160,000 $160,000 $160,000 $ Inventory holding costs TOT Cost s $ $ 5 5 5 Miring cost $ $ $ 5 $ $ Hiring Cost $ $ $ $ $ $ 160.000 $100,000 $160,000 $ 160.000 $100,000 $100,000 $150.000 $1,930.000 $ $ $ 5 $ 5 5 5 s 5 $ 5 5 $ 5 $ $ SL000 B C D E F G H 1 M N 13 14 15 Any February March Agni 500 7501 11101 Total May 1400 je 1500 July 1600 August September October 1400 1300 1000 November December 750 6001 300 12,210 16 17 18 19 80 80 80 20 80 30 80 Forecast Workers Hired Worbers Fired Overtime Production Max OT #Worders Production Beginning inventory Ending Inventory Average Inventory 20 20 20 20 20 20 838 20 20 20 800 20 20 300 80 20 800 800 800 800 800 800 200 800 800 21 22 23 24 25 $180.000 $100.000 $100.000 $10,000 $10,000 $160,000 $10,000 $160,000 $160,000 $ 160,000 160,000 $100,000 1.920,000 27 Production and Inventory Costs Retime production cost in Inventory holding cost in $ OT Cout ong cost Firing Cou $ $ $ $ $ 5 $ $ 5 $ $ 5 28 29 30 31 32 $ $ $ - $ 5 $ $ 5 - 5 $ $ 5 5 $ $ $ $ 5 5 5 $ $ $ 1.920,000