Question

Gang Aft Agley, a manufacturing company, faces the aggregate planning problem shown in the table below. Cost of regular production is $5 per unit, the cost of producing the same unit on overtime is $7.50, the cost of subcontracting is S9 per unit, an<d the cost of carrying a unit in inventory from one month to the next is $2 The labor contract at the plant prohibits overtime output to exceed 300 units in any five month window (that is the entire time horizon being considered); likewise, subcontracting output also can't exceed 300 units in any five month window. The plant capacity is 600 units per month (during regular time) produced using two shifts, regardless of the number of days in a month. By policy, management wants to avoid stockouts. Formulate the aggregate plan considering regular time costs, overtime costs, subcontracted production costs, inventory costs and the necessary constraints using linear programming and solve it using Excel Solver for obtaining the optimum minimum cost for the 5-month horizon. Units produced Units during Regular during Subcontracted the time Inventory at the end ot produced Month time Overtime Priduction period Demand 0 100 3 4 5 1,200 D3 650 D Your case study report must contain A. Objective function B. Constraints C. Excel Solver output (ifyou don't attach Excel output with

Answer 1

Units Produced Units Produced SubContracted in Regular Shift in Overtime Inventory at the Regular Shift Month Production end of Production Demand Capcity 0 0 250 50 0 0 0 300 0 0 100 200 50 0 0 0 500 750 1200 650 300 300 Capacity 9 Total Capacity Utilized 10 Unit Cost 11 Cost of Each Option 300 300 300 300 7.5 2250 2700 350 13500 2700 700

The objective function is to reduce Total Cost.

Constraints

1. Inventory at the end of Production(Projected ) should not go below 0.

2. Monthly Regular Production will not be greater than 600.

3. Labour and Subcontracting can't increase more than 300 for 5 months period.

Formula for Inventory

Inventory at the end of Production

E3=SUM(B3:D3)+E2-F3

Closing Inventory of last Bucket+ (Total Production in current Bucket)- Total Demand.

Solver Inputs

Solver Parameters Set Objective SB$12 To: Max O Min Yalue Of: By Changing Variable Cells: Subject to the Constraints: $B$3:$B$7 < $G$3:$G$7 SB$3:SD$7integer Add Change Delete Reset All Load/Save Make Unconstrained Variables Non-Negative Select a Solving Method: Simplex LP Options Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. Help Solve Close