1. When backorder is not allowed, add an appropriate constraint in the spreadsheet and find the optimal solution. Please include a snapshot of the constraint you add, list the optimal solution including the decision variables and corresponding minimized total cost.

2. Assuming that backorder is not allowed, add an appropriate constraint that forces the factory to maintain a minimum buffer inventory of 100 gears each month. Please include a snapshot of the constraint you add, list the optimal solution including the decision variables and corresponding minimized total cost.

3. Assuming that backorder is not allowed, add an appropriate constraint that forces the factory to maintain a minimum buffer inventory of 100 gears each month. If you are using level strategy, what needs to be changed in the given spreadsheet? Please attach a snapshot of the new spreadsheet with the changed number(s) bolded.
    

 

Linear program (Profit Max) Solver Variables Given Parameters Profit Maximization Strategy LP Variables K= 3.8 Forecaste Ending Inventory gears/day Units/W P t Productio Hire W t Worker Holdin d Days kr H t Lt I+ I- Lay Offs Backord per Month Demand per Month n S S er 35 Jan 2700 21 79.8 3032.4 3 38 332.4 Feb 3000 20 76 3192 4 42 524.4 Mar 4000 23 87.4 3670,8 42 195.2 Apr May 4500 21 79.8 3351.6 42 953.2 3200 22 83.6 3511.2 3594.8 42 642 Jun 2900 22 83.6 1 43 52.8 Total 20300 20352.8 8 1104.8 1595.2 Cost of Strategy Profit Maximization Strategy Costs Units Subtotal Production Costs 30.00 20352.8 $610,584.00 Hiring Costs 4,500.00 $36,000.00 Layoff Costs Inv Holding Costs 6,000,00 $0.00 $5,524.00 5.00 1104.8 Backorder Costs 15.00 1595.2 $23,928.00 unit cost subtotal $676,036.00 Cost (per day (8hr) days hourly worker costs 64.00 5356 S342,784.00