Aggregate Production Planning: Please see the attached Excel file for the question in the first 2 spreadsheets (last spreadsheet for MPS question). You will basically fill out with the answers you...

Aggregate Production Planning: Please see the attached Excel file for the question in the first 2 spreadsheets (last spreadsheet for MPS question). You will basically fill out with the answers you have. I expect you to see using Excel formulas to make your production planning strategy fully automatic, which means that when I change the numbers within the input area, the answer should change automatically.
Master Production Scheduling: Please fill out the yellow highlighted rows in the last spreadsheet in the attached Excel file. Again, try to make fully automated Excel file.


Constant Production Exercise Fill the necessary cells according to the class exercises and examplesLimit of maximum overtime (%)20 Initial # of workers (People)65 MONTHDECJANFEBMARAPRMAYJUNTOTALInitial Inventory (pounds)8500 Monthly Production/WorkerRegular Production Time (Hours/Month)160 Beginning InventoryRegular Production Rate (Pound/Month/Labor)90 Work Force Level65Overtime Production Rate (of regular time)0.2 Straight ProductionCost of regular time production ($/hour)5 Overtime ProductionCost of overtime production ($/hour)7.51.5 Subcontract ProductionCost of carry inventory ($/lb/month)0.25 Monthly ProductionCost of Stock Out ($/lb/month)0 Sales Forecast10000110001000080006000650051500Cost of hiring new people ($/person)150 Ending Inventory8500Cost of laying off people ($/person)300 Cost ComponentsEnding Inventory6000 Hired Workers Fired Workers Hiring/Firing Cost Regular Production Cost Overtime Cost Subcontract Cost Stock Out Cost Inv. Holding Cost Total Cost JIT Production Exercise Fill the necessary cells according to the class exercises and examplesLimit of maximum overtime (%)20 Initial # of workers (People)65 Initial Inventory (pounds)8500 MONTHDECJANFEBMARAPRMAYJUNTOTALRegular Production Time (Hours/Month)160 Beginning InventoryRegular Production Rate (Pound/Month/Labor)90 Work Force Level65Overtime Production Rate (of regular time)0.2 Straight ProductionCost of regular time production ($/hour)5 Overtime ProductionCost of overtime production ($/hour)7.51.5 Subcontract ProductionCost of carry inventory ($/lb/month)0.25 Monthly ProductionCost of Stock Out ($/lb/month)0 Sales Forecast10000110001000080006000650051500Cost of hiring new people ($/person)150 Ending Inventory8500Cost of laying off people ($/person)300 Cost ComponentsEnding Inventory6000 Hired Workers Fired Workers Hiring/Firing Cost Regular Production Cost Overtime Cost Subcontract Cost Stock Out Cost Inv. Holding Cost Total Cost MPS Exercise Fill the yellow highlighted rows Period123456789101112 Forecast251540504040304040504040 Customer Orders2020292117145011521012 This is an extra row for the illustrationInventory Before Production Projected Available Balance - I(t) Available to Promise (ATP) MPS On Hand Inventory50 Planning Time Fence10 Lot Size (Batch Size) - Qt100 Safety Stock (SS)0