Ms Office Tune Up

To see how Solver handles such a problem, follow these steps:

1. Choose Data, Solver. Excel displays the Solver Parameters dialog box.
2. In the Set Target Cell text box, enter a reference to the target cell-that is, the cell with the formula you want to optimize. In the example, you enter C15 (the Total Profit cell).
3. In the Equal To section, select the appropriate option button: Select Max to maximize the target cell, select Min to minimize it, or select Value Of to solve for a particular value (in which case, you also need to enter the value in the text box provided). In the example, you activate Value Of and enter 100000 in the text box.
4. Use the By Changing Cells box to enter the cells you want Solver to change while it looks for a solution. In the example, you enter C5,D5.
5. Click Solve. Solver works on the problem and then displays the Solver Results dialog box, which tells you whether it found a solution.
6. If Solver found a solution that you want to use, click the Keep Solver Solution option and then click OK. If you don’t want to accept the new numbers, click Restore Original Values and click OK or just click Cancel. (To learn how to save a solution as a scenario, see the section later in this chapter titled “Saving a Solution as a Scenario.”)

Figure 2 shows the results for the example. As you can see, Solver has produced a total profit of $100,000 by running the Finley Sprocket with a profit of just under $58,000 and the Langstrom Wrench with a profit of just over $42,000.

In many cases, you want to optimize profit margin, instead. Profit margin is the difference between revenues and costs, divided by revenues. Again, for single-variable models you can use Goal Seek to find the solution, and for multi-variable models, you can use Solver.

Figure 2 When Solver finishes its calculations, it displays the Solver Results dialog box and enters the solu­tion (if it found one) into the worksheet cells.

In the two-product example used in this section, assume that you want to find the number of units you need to sell for each product that produces an overall margin of 22 percent. Moreover, you also want a solution where the margins produced by each product are at least 20 percent. In this case, by setting parameters on the type of results you want, you are constraining the solution, and such parameters are called constraints. Here’s how to run Solver with constraints:

1. Choose Data, Solver to open the Solver Parameters dialog box.
2. In the Set Target Cell text box, enter a reference to the target cell-that is, the cell with the formula you want to optimize. In the example, you enter C17 (the Profit Margin cell).
3. In the Equal To section, select the appropriate option button: Select Max to maximize the target cell, select Min to minimize it, or select Value Of to solve for a particular value (in which case, you also need to enter the value in the text box provided). In the example, you activate Value Of and enter 0.22 in the text box.
4. Use the By Changing Cells box to enter the cells you want Solver to change while it looks for a solution. In the example, you enter C5,D5.
5. To add a constraint, click Add. Excel displays the Add Constraint dialog box.
6. In the Cell Reference box, enter the cell you want to constrain. For the example, you enter cell C14 (the Product Margin formula for the Finley sprocket).
7. Use the drop-down list in the middle of the dialog box to select the operator you want to use. The list contains several comparison operators for the constraint-less than or equal to (<=), equal to (=), and greater than or equal to (>=)-as well as two other data type operators-integer (int) and binary (bin). For the example, select the greater than or equal to operator (>=).

NOTE

Use the int (integer) operator when you need a constraint, such as total employees, to be an integer value instead of a real number.Use the bin (binary) operator when you have a constraint that must be either TRUE or FALSE (or 1 or 0).

8. If you chose a comparison operator in step 4, use the Constraint box to enter the value by which you want to restrict the cell. For the example, enter 0.2.
9. If you want to enter more constraints, click Add and repeat steps 6-8. For the exam­ple, you also need to constrain cell D14 (the Product Margin formula for the Langstrom wrench) so that it, too, is greater than or equal to 0.2.
10. When you’re done, click OK to return to the Solver Parameters dialog box. Excel displays your constraints in the Subject to the Constraints list box, as shown in Figure 3.
11. Click Solve and then either accept or reject the solution, if one was found.

Figure 4 shows the results for the example. As you can see, the Profit Margin is 22 percent, and both Product Margin values are at least 20 percent.

Figure 3 The Solver Parameters dialog box with con­straints added to the model.

Figure 4 Solver found a con­strained solution to the problem.

Technorati Tags: optimizing profit margin, excel 2007

Popularity: 2% [?]

Pages: 1 2