Question

Reconsider the Wyndor Glass Co. case study introduced in Section 21/ in class. Suppose that the estimates of the unit profits for the two new products now have been revised to $500 for the doors and $350 for the windows. A curve fitting procedure was used to estimate the weekly marketing costs required to sustain a production rate of doors and W windows • Marketing cost for doors $250 • Marketing costs for windows $66 Hours Used PerUnit Produced: Available Doors Windows lours for each plant Plant ! 1 0 4 Plant 2 0 2 12 Planta 3 2 16 Questions Formulate the model algebraically. (Please clearly define all the decision variables, clearly write down the objective function and each constraints) b. Formulate and solve the model for this problem on a spreadsheet and use solver to solve it. The number of the products are NOT required to be integer.)

Answer 1

a.

Variable Definition:

X = Number of doors

Y = Number of windows

Objective function:

Wyndor Glass Company would like to maximize their profits.

Profit = revenue - cost

Profit = [500X + 350Y] - [ 25D² + 66W²]

Profit = 500X + 350Y - 25D² - 66W²

Therefore we can write that,

Maximize Z = 500X + 350Y - 25D² - 66W²

Constraints:

1X <=4 .............Constraint as limited time available at plant 1

2Y <=12 .............Constraint as limited time available at plant 2

3X + 2Y <=18...........Constraint as limited time available at plant 3

X,Y >=0 ............Non-negativity constraint as number of doors and windows cannot be negative

b.

Now solving using excel.

Framing problem in excel as below:

B с D E F G H 1 X OO Y 0 <-----Variable value appears here --Objective function value appears here 0 <- 19 20 Variables 21 Values 22 Objective functio 23 24 Constraints 25 Plant 1 26 Plant 2 27 Plant 3 28 29 X Y Resources Used Limit 4 1 0 3 0 2 2 0 <= 0 <= 0 <= 12 18

Formulas shown below

C34 C D E F G н х Y 0 0 <-----Variable value appears here =500*C21+350*D21-25*C21^2-66*D21^2 <------Objective function val X Y Limit B 19 20 Variables 21 Values 22 Objective function value 23 24 Constraints 25 Plant 1 26 Plant 2 27 Plant 3 28 29 30 31 4 1 0 3 0 2 2 Resources Used =SUMPRODUCT($C$21:$D$21,C25:D25) <= =SUMPRODUCT($C$21:$D$21,C26:D26) <= =SUMPRODUCT($C$21:$D$21,C27:D27) <= 12 18 P 32

Now solve using solver function from the Data tab in the menu bar:

1. Set objective as shown in the figure below to MAX

2. Add constraints as shown in the figure below from clicking the Add button on right side of solver window

3. Set the solving method to GRG Non-linear

4. Click Solve

nome insert Ldyout romulds Dald Review view Develope X W Detail I Detail ? ДА From Access From Web From From Other Text Sources Get External Data 11 C22 0 O Value of: Q R B Connections Solver Parameters |Z|A| Properties Existing Refresh A Sort Filter Connections All Sa Edit Links Set Objective: $C$22 Connections Sort & Filte fic <-----Variable value appears here To: O Max Min с D E F G H By Changing Variable Cells: X Y $C$21:$D$21 0 ----Variable valu appears here 0 Objective function value appears here Subject to the Constraints: $C$21:$D$21 >=0 X Y Resources Used Limit $E$25:$E$27 <=$G$25:$G$27 0 0 <= 4 0 2 0 <= 12 3 2 0 <= 18 EN Add 1 B 19 20 Variables 21 Values 22 Objective function value 23 24 Constraints 25 Plant 1 26 Plant 2 27 Plant 3 28 29 30 31 32 33 34 Change Delete Reset All Load/Save Make Unconstrained variables Non-Negative Select a Solving Method: GRG Nonlinear Options 35 36 37 38 39 40 41 42 43 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 44 45

Final solution by excel

X 2 5376 <- Y 6 <--Variable value appears here --Objective function value appears here х Y Limit 1 4 19 20 Variables 21 Values 22 Objective function value 23 24 Constraints 25 Plant 1 26 Plant 2 27 Plant 3 28 29 30 31 32 Resources Used 2 <= 12 <= 18 <= 0 2 2 0 3 12 18

Therefore,

X = Number of doors = 2

Y = Number of windows = 6

Profit = $ 5,376