Question

Decision Variables: B= # of Basic models to be produced, profits$45/unit P= # of Premium models to be produced, profit-$120/unit Objective: MaxTotal Profit Max $45 B $120 P Constraints: Software Engineer (SE): 20 B5 P QC Manager: Demand for B: Demand for P: Preferred product is: a)B b)P c)QC Manager d) SE e)none of these <= 2400 minutes 15 B+30 P 2400 minutes <=100 units <= 50 units

Answer 1

We have step this problem in excel and solve it using solver.

1. Put the input in excel as follows.

MAX PROFIT 10500 45 120 2 Profit 4 6 Software engineer 7 QC Manager 20 15 30 Constraints No of units of product 10 11 Software engineer 12 QC Manager 13 100 0 100 50 0 50 2250 <- 0 <= 2400 2400 100 50 15 16 17 18

2. The formulas for each cell as shown below.

MAX PROFIT 2 Profit 45 120 -SUMPRODUCT(B2:C2,B13:C13) 4 6 Software engineer 7 QC Manager 20 15 5 30 No of units of product Constraints 10 11 Software engineer 12 QC Manager 13 100 0 SUM(B11:B12) =SUMPRODUCT(B6:C6,B11:C11) SUMPRODUCT (B7:C7,B12:C12) <- 2400 2400 SUM(C11:C12) 100 15 16 17 50

3. Input for solver are given.

Solver Parameters Set Objective: SES2 To: .) Max Min Value Of: 0 By Changing Variable Cells: -$BSI 1:$C$12 Subject to the Constraints: $B$13 <-$B$15 $C$13 < $C$15 $D$11 <= $FS11 $D$12 <= $F$12 Add Change Delete Reset All Load/Save Make Unconstrained Variables Non-Negative Select a Solving Method Simplex LF 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

For the output generated we see that the preferred is SE