Question

can you solve it with excel??

fx Problem 1 A company makes two types of windows (Product 1 and Product 2). The table below shows the plants where the products are made, the required production times, and the profit per batch of windows. Production Time Required Per Product Plant Product1 Product2 Available Production Time 4 2 12 3 3 18 D Profit 3000 5000 2 How many of each product should the company make to maximize profit? 3 What is the maximum profit? 4

Answer 1

Let x1 be the number of products make of Product 1
Let x2 be the number of products make of Product 2

Max Z = 3000 x1 + 5000 x2

Subject to constraints
x1 <= 4
2x2 < 12
3x1 + 2x2 <=18

x1,x2 >=0

Simplex Method:

The problem converted to canonical form by adding slack, surplus and artificial variables as appropiate 1. As the constraint-1 is of type '<'we should add slack variable s, 2. As the constraint-2 is of type '<'we should add slack variable s, 3. As the constraint-3 is of type '<'we should add slack variable S, After introducing slack variables Max Z = 3000.x, + 5000.x, + 0S, + 0 S2 + 0S3 subject to + S, = 4 X1 + S, = 12 2х, 3x, + 2x, and x1. x3. S1, Sz, S; 2 0 + S3 = 18 C; Iteration-1 3000 5000 MinRatio Хв Св XB S1 S2 S3 X1 X2 х, 12 S2 12 (2) 18 S3 18 2 1 Z; Z= 0 Z, - C; -3000 - 5000 ↑

Negative minimum Z; - C; is - 5000 and its column index is 2. So, the entering variable is x,. Minimum ratio is 6 and its row index is 2. So, the leaving basis variable is S,. :: The pivot element is 2. Entering - x,, Departing = S,, Key Element = 2 + R,(new) = R,(old) - 2 + R,(new) = R,(old) + R;(new) = R;(old) - 2R,(new) C; Iteration-2 3000 5000 MinRatio Xg C3 Хв S1 S2 S3 B X1 х, X1 4 1= 4 5000 0.5 X2 S3 (3) -1 Z = 30000 Z; 5000 2500 Z, - C; - 3000 ↑ 2500

Negative minimum Z; - C; is - 3000 and its column index is 1. So, the entering variable is x1. Minimum ratio is 2 and its row index is 3. So, the leaving basis variable is S,. : The pivot element is 3. Entering = x, Departing = S3, Key Element = 3 + R;(new) = R3(old) = 3 + R,(new) = R,(old) - R;(new) + R2(new) = R,(old) Iteration-3 3000 5000 S2 CB Хв S1 S3 MinRatio X1 X2 0.3333 -0.3333 5000 6. 0.5 X2 3000 2 1 -0.3333 0.3333 X1 Z= 36000 Z; 3000 5000 1500 1000 Z; - C; 1500 1000

Since all Z; - C; 2 0 Hence, optimal solution is arrived with value of variables as : X = 2, x, = 6 Max Z = 36000

Maximum Profit = 36000

Excel:

LPP Using Solver 1 - Microsoft Excel Page Layout Home Insert Formulas Data Review View K Clear E Connections A Properties Reapply Z1 Sort Text to Remove Data From From From From Other Existing Connections Refresh Filter Consolidate What-If Group Ungroup G Edit Links Advanced Columns Duplicates Validation All- Analysis Access Web Text Sources Sort & Filter Get External Data Connections Data Tools fe =SUMPRODUCT(B3:C3,B4:C4) E4 Solver Parameters A Set Target Cell Solve SE$4 Equal To: O Max By Changing Cells: O O Value of: Min Product 1 Product 2 2 Close 3 Profit 3000 5000 $B$4: $C$4 Guess 4 X1,X2 values Min Z = Subject to the Constraints: Options 6 Subject to Constraints SD$10 <= $F$10 Add $D$8 <= $F$8 $D$9 <= $F$9 Change 8 1) Plant 1 9 2) Plant 2 1. 4 <3D Reset All 12 3D> Delete Help 10 3) Plant 3 3 2 18 <= 11 12 13

LPP Using Solver 1 - Microsoft Excel Page Layout Home Insert Formulas Data Review View E Connections K Clear E Properties Reapply Z! Sort Group Ungroup Sub Data From From From From Other Existing Connections Refresh Filter Text to Remove Consolidate What-If Y Advanced G Edit LinkS Columns Duplicates Validation All- Access Web Text Sources Analysis - Sort & Filter Outli Get External Data Connections Data Tools fe G9 A. Solver Results Product 2 Product 1 Solver found a solution. All constraints and optimality conditions are satisfied. 3 Profit 3000 5000 Reports 4 X1,X2 values б Min Z = 36000 Answer Sensitivity Limits O Keep Solver Solution 6 Subject to Constraints O Restore Original Values 8 1) Plant 1 2 4 <= Help OK Cancel Save Scenario... 9 2) Plant 2 12 12 10 3) Plant 3 18 3 18 <=

Click on Ok button , the output be

Max Z = 36000 and x1 = 2 and x2 = 6