Question

Reconsider the Wyndor Glass Co. case study introduced in Section 2.1/ 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 D doors and W windows: . . Marketing cost for doors = $25D2 Marketing costs for windows = $66W2 Hours Used Per Unit Produced : Available Doors Windows hours for each plant Plant 1 0 4 Plant 2 0 2 12 Plant 3 3 2 18 1 Questions: a. 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.) Upload Choose a File

Answer 1

a)

Algebraic linear programming model is following:

.

Decision variables:  

Let D be the number of Doors to be produced (or production rate of doors), and

W be the number of Windows to be produced.

.

Objective function:

Max 500D + 350W - 25D² - 66W²

.

Constraints:

1D + 0W <= 4

0D + 2W <= 12

3D + 2W <=18

D, W >= 0

-----------------------------------------------------------------

b)

Formulate Spreadsheet model as follows:

1 E F Available Hours <= 4 <= 12 <= 18 0 A B с D D w Hours 2 Used 3 Plant 1 1 0 =SUMPRODUCT(B3:03,$B$7:$C$7) 4 Plant 2 2 ESUMPRODUCT(B4:04,$B$7:$C$7) 5 Plant 3 3 2 =SUMPRODUCT(B5:05,$B$7:$C$7) 6 7 Production rate: 8 9 Total 10 Profit: 500 350 =SUMPRODUCT(B10:010,$B$7:$C$7) 11 Marketing cost: =25*B7^2 =66*C7^2 =SUM(B11:011) 12 13 Total net profit =D10-D11 14 15 16

Note that marketing cost is quadratic function. Therefore, it is a nonlinear model.

In Solver Parameters, Select a Solving Method: GRG Nonlinear

Enter Solver Parameters as shown below:

Solver Parameters Х Set Objective: $D$13 1 To: Max Min Value of: 0 By Changing Variable Cells: $B$7:$C$7 1 Subject to the Constraints: $D$3:$D$5 <= $F$3:$F$5 Add Change Delete Reset All Load/Save Make Unconstrained variables Non-Negative Select a Solving Method: GRG Nonlinear 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

Click Solve to generate the solution.

After that, values appear automatically in variable cells, B7:C7

А I B с D E G J K L - N M 1 D W F Available Hours 4 2 3 0 Hours Used 4 5.30303 17.30303 1 0 <= <= 12 3 2 18 Plant 1 4. Plant 2 5 Plant 3 6 7 Production rate: 8 4 2.6515 Solver Results 9 350 500 400 Total 2928.03 864.0151 Solver found a solution. All Constraints and optimality conditions are satisfied. 464.02 Reports Answer Sensitivity Limits O Keep Solver Solution Total net profit 2064.015 O Restore Original Values 10 Profit: 11 Marketing cost: 12 13 14 15 16 17 18 19 20 21 22 23 Return to Solver Parameters Dialog Outline Reports OK Cancel Save Scenario... Solver found a solution. All constraints and optimality conditions are satisfied. 24 When the GRG engine is used, Solver has found at least a local optimal solution. When Simplex LP is used, this means Solver has found a global optimal solution. 25 26 27

Click OK

--------------------

Number of Doors to be produced, D = 4

Number of Windows to be produced, W = 2.65

Total net profit = $ 2064.015   (this is net of marketing cost)