At this point, the model is complete. We're
ready to use it to determine what product mix will yield the maximum
total profit. By changing the decision variables in the Qty Produced
row, we manipulate the Total Profit figure in cell J6.
The model starts with the production values
originally mandated. That is, Qty Produced levels of 40 for each
chair type. Oak Products' total profit under this scenario is $8,760.
Experiment with changing the Qty Produced
values to try to increase Oak Products' total profit over the $8,760
level. Keep an eye on the values in the Ending Inventory column
to make sure that none of them fall below zero. Negative values in this
column indicate an unworkable model. The conditional formatting I added
(less that zero values display bolded and in red) make them easy to
spot quickly.
Below are the results when we estimate (guess)
what Qty Produced values to enter into the model.
Capt

Mate

AmHi

AmLo

SpanK

SpanQ

Total Profit

40

50

53

40

25

30

$8,970

40

40

40

53

40

40

$9,254

1

72

75

5

28

37

$8,386

Manual manipulation of the Qty Produced decision
variables quickly shows how difficult it is to come up with an optimum
product mix.
Tip: To manage a variety of different scenarios
in a worksheet, consider using Excel's Scenario Manager. Use Excel's
Online Help subject search to see information on this topic.
Instead of spending time manually changing decision
variable values in the model, we can make use of Excel's Solver. The
Solver will quickly determine the most profitable mix for us. We tell
the Solver what to maximize, what can be changed, and then provide it
with information about constraints.
Invoke the Solver with the commands Tools, Solver.
Complete the Solver Parameters dialog.
 The Set Target Cell is Total Profit
(Cell J6), which we ask Solver to maximize.
 The Changing Cells are the decision
variables, or Qty Produced cells (the range C4:H4; Solver adds
absolute referencing).
 Two constraints state that:
 We can't produce a negative quantity of any chair (C4:H4>=0)
 We can't use more inventory than we have on hand (K9:K19<=J9:J19)
Click the Options button on the Solver
Parameters dialog to see the Solver Options screen. For this
problem, specify:
 Assume Linear Model
 Set Tolerance to 0.05
Click OK to return to the Solver Parameters
dialog. Click the Solve button to direct the Solver to start
working to optimize Total Profit in the worksheet.
When Solver finds a solution, it displays a Solver
Results dialog box. You can direct Solver to keep its solution (replacing
Set Cell and Changing Cell values with the ones it's found)
or if you want to reject Solver's suggested solution, you can choose
to retain your model's original values.
[You may submit a problem to Solver for which it
can't find a solution. In that case, Solver displays a message to that
effect and you're returned to the worksheet with the model values left
unchanged.]
You can ask Solver to generate one or more of its
reports on the process: The Answer Report, the Sensitivity Report, or
the Limits Report. If you ask for a Solver report, the report appears
on a separate sheet that Solver adds to your workbook.
In the final worksheet example shown below Solver's
values are displayed in the Qty Produced row and for the Total
Profit figures. Solver suggests that Oak Products drop the Spanish
King and Spanish Queen models in August and concentrate on a product
mix without them. Solver's suggested product mix yields a profit of
$10,294, considerably better than
the $8,760 we started with.