Question

An electronics manufacturer makes video security systems for parking lots. The "Clear-Shot" is a system that provides 3-D graphic images of the parking lot. Operations manager of the company obtained forecasted demand for "Clear-Shot" and available capacity (in units) they have (shown below) to develope a four-month aggregate plan for manufaturing. Capacity & Demand Month 2 Month 1 Month 3 Month 4 Regular time Overtime 225 300 275 300 15 28 24 26 18 Subcontract 12 17 15 240 316 305 Demand 336 The cost of producing each unit is $875 on regular time, $1,310 on overtime, and $1,500 on a subcontract. Inventory carrying cost is $100 per unit per month. There is to be no beginning or ending inventory and backorders are not permitted. S(enter your response as a Minimizing cost when allocating resources to meet demand using the transportation method, such that the optimal cost of the plan is whole number).

Answer 1

Use the following Excel formulation to obtain the optimal solution:

B D G. C K M N Month 3 Optimal Plan 2Beginning inventory Month Capacity Cost Month. Month 2 Month a Limused Total Month 1 Month 2 Month 3 - L2+SKS19 - L3+SK$19 - L4+SK$19 -SUM(B2 : F2) -SUM(B3 : F3) - SUM(B4 : F4 ) -SUM(B5 : F5 ) -SUM(B6:F6) -SUM(B7 :F7) - SUM(B8 : FS ) -SUM(B9 : F9 ) -SUM(B10 : F10) 26 -SUM(B11 : F11) 18 -SUM(B12 : F12) 300 -SUM(B13:F13) 28 Beginning inventory Month 1 reg time Month 1 overtime Month 1 subcontracting Month 2 reg time 0 0 K2+SK$19 M2+SK$19 - K16 - K17 3 Month 1 reg time -K3+ SK$ 19 M3+SK$19 K4 + SK$ 19 4 Month 1 overtime M4+SK$19 5 5 Month 1 subcontracting 12 -K18 K5+SK$19L5+SK$19 -M5+SK$19 Month 2 reg time L6+SK$19-M6+SKS19 75 9999 K16 6 Month 2 overtime 7 Month 2 overtime -L7+SK$19 -M7+SK$19 =L8+SK$19 -MS+SK$19 24 9999 -K17 Month 2 subcontracting 9999 Month 3 reg time 8 Month 2 subcontracting 9 Month 3 reg time 10 Month 3 overtime 11 Month 3 subcontracting 17 K18 6666 9999 300 9999 K16 M9+SK$ 19 Month 3 overtime Month 3 subcontracting 9999 Month 4 reg time Month 4 overtime M10+ SKS19 9999 -K17 9999 K18 M11+SK$19 12 Month 4 reg time 9999 9999 9999 K16 13 Month 4 overtime 9999 9999 9999 K17 14 Month 4 subcontracting -SUM(B14: F14) 15 Month 4 subcontracting 9999 9999 9999 -K18 - SUM( B2 : B14)-SUM(C2 : C14) -SUM(D2:D14-SUM(E2 :El4) -SUM(F2 : F14) 336 Total 15 58 316 240 Regular time cost 16 Demand 305 875 Overtime Cost 1310 17 -SUMPRODUCT(B2:E14K2:N14) Subcontracting cost 1500 18 Total Cost 100 19 Carrying cost

Set Objective: $B$18 To: OValue Of 0 Min Max By Changing Variable Cells: $B$2:$F$14 Subject to the Constraints: SB$16:$F$16 $B$15:$F$15 Add $G$2:$G$14= SH$2 : SH$ 14 Change Delete Reset All Load/Save Make Unconstrained Variables Non-Negative Simplex LP Select a Solving Method: 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

Solution:

Optimal Plan	Month 1	Month 2	Month 3	Month 4	Unused	Total	Capacity
Beginning inventory	0	0	0	0	0	0	0
Month 1 reg time	225	0	0	0	0	225	225
Month 1 overtime	15	0	0	0	0	15	15
Month 1 subcontracting	0	0	0	0	12	12	12
Month 2 reg time	0	275	0	0	0	275	275
Month 2 overtime	0	24	0	0	0	24	24
Month 2 subcontracting	0	17	0	0	0	17	17
Month 3 reg time	0	0	300	0	0	300	300
Month 3 overtime	0	0	26	0	0	26	26
Month 3 subcontracting	0	0	10	0	8	18	18
Month 4 reg time	0	0	0	300	0	300	300
Month 4 overtime	0	0	0	5	23	28	28
Month 4 subcontracting	0	0	0	0	15	15	15
Total	240	316	336	305	58
Demand	240	316	336	305	58
Total Cost	$1,094,700