Oak Products Project - Page 7   

  Forward  Back (Page 6)   Excel Review Home

 

G. Find Maximum Total Profit

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.

Maximizing Total Profit by Hand

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.

Return to Top

 

Maximizing Total Profit Using the Solver

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.

Return to Top   Next Page (worksheets)