Breaking even is always nice, but profits are even better, so you might want to use Excel to optimize profits. In this case, I’m using the word optimize not in the sense of “maximize,” but in the sense of “reach a predetermined level of.” For example, in your budgeting for the next fiscal year, you might set a goal of $100,000 in profit for a product or division.

If you use a single product model like the one I used in the previous section, then you can optimize profits by using Goal Seek: You set your target cell value (Total Profit) to the profit level you want, and then you set up either the price or the number of units as the changing cell. This method works for a single product, and you can also apply it to multiple products, where you set up a model for each item and then run Goal Seek on each model.

However, in the real world, two (or more) products don’t exist in a vacuum. For example, there is cost savings associated with each product because of joint advertising campaigns, combined shipments to customers (larger shipments usually mean better freight rates), and so on. To allow for this, you need to reduce the cost for each product by a factor related to the number of units sold by the other product. In practice, this is difficult to estimate, but to keep things simple, I use the following assumption: Given two products, the costs for each product are reduced by $1 for every unit sold of the other product.

For instance, if the product A sells 10,000 units, the costs for product B are reduced by $10,000. Figure 7.17 shows a worksheet set up with such a model. Note, in particular, the Variable Costs formula. For example, the formula that calculates variable costs for the Finley sprocket (cell C9) becomes the following:

=C8 * C5 – D5

This formula calculates the regular costs (the unit cost multiplied by the number of units sold) and then subtracts the number of units sold by the other product. Similarly, the formula that calculates variable costs for the Langstrom wrench (cell D9) becomes the following:

=D8 * D5 – C5

By making this change, you move out of Goal Seek’s territory. The Variable Costs formulas now have two variables: the units sold for the Finley sprocket and the units sold for the Langstrom wrench. I’ve changed the problem from one of two single-variable formulas, which Goal Seek can easily handle (individually), to a single formula with two variables, which is the terrain of another Excel data analysis tool: Solver.

Solver is a sophisticated optimization program that enables you to find the solutions to complex problems that would otherwise require high-level mathematical analysis. Solver, like Goal Seek, uses an iterative method to perform its magic. This means that Solver tries a solution, analyzes the results, tries another solution, and so on.

However, this cyclic iteration isn’t just guesswork on Solver’s part. The program looks at how the results change with each new iteration and, through some sophisticated mathematical trickery, can tell (usually) in what direction it should head for the solution. Unlike Goal Seek, however, Solver
enables you to specify multiple changing cells (up to 200); it enables you to set up constraints on the adjustable cells; and Solver seeks not only a desired result (the “goal” in Goal Seek), but also the optimal one, which means you can find a solution that is the maximum or minimum possible.

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 7.18 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.

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 (>=).
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 example, 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 7.19.
11. Click Solve and then either accept or reject the solution, if one was found.

Figure 7.20 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.

• Motorola Droid Tip & Tricks - Multi touch Pinch and Zoom Supported HD www.guysfromqueens.com Motorola Droid - Multi touch Pinch and Zoom . I want to first thanks the Viewer who told me...
• Achieve Your Health Goals This Year I know I've been a little quiet around here lately. I've been working on hard on redefining my life and...
• Newbie Blogger Tips and Tricks - Must Do's and Things to Avoid Although this blog started out as a marketing tool for my upcoming book, it’s become a part of my life. ...
• Super Hit Incense Cones Box of 12 Boxes User Reviews Send this to a friend Super Hit Incense Cones Box of 12 Boxes Manufacturer: Shrinivas Sugandhalaya Customer Rating:...
• Maximize Your Search Results-Part4 This will be the final installment in this series of articles. Maximizing your search results has been fun and very...