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 1 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
Figure 1 A multi-variable model for calculating the profits generated by two products.
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.
NOTE
You may need to install and/or activate the Solver add-in to use it. Choose Office, Excel Options to open the Excel Options dialog box. Click Add-Ins; in the Manage list, click Excel Add-ins, and then click Go. In the Add-Ins dialog box, click to activate the Solve Add-in check box and then click OK.
Popularity: 2% [?]
Pages: 1 2
If you liked this post, would you please buy me a twelve-ounce lattes for only $2
Recent Comments