Special Appendix to Chapter 2 Optimization Using Spreadsheets 75in cell addresses is not the only way to enter formulas. The quickest way is to
mouse click on the cell that is part of the formula.)
With the spreadsheet in hand, there are several ways to determine the
microchip firm’s profit-maximizing output. The most primitive way is to try
various numerical values in cell B7, observe the resulting profit results in cell
F7, and, thereby, identify the optimal output. This represents solution by enu-
meration. A second, more expeditious approach uses MR and MC as guides.
Again, values in cell B7 are varied by hand, but this time systematically. Output
should be increased as long as MR exceeds MC; it should be cut if MC exceeds
MR. When MR equals MC, the profit-maximizing level of output has been
attained.
A third approach is to direct the computer to optimize the spreadsheet.
The top menu in Table 2A.2 illustrates Excel’s optimizer, called “Solver,”
which is called by clicking on the “Solver” listing found under the “Tools”
menu. By completing the menu in Table 2A.2, one instructs the computer to
optimize the spreadsheet. In the menu, we have (1) entered target cell F7
(the profit cell), (2) to be maximized, (3) by varying cell B7. Then, after one
clicks on the solve box, the computer finds a new numerical value in cell B7
that maximizes cell F7. (The value one starts with in cell B7 doesn’t matter;
the computer will replace it with the optimal value it finds.) Using an inter-
nal mathematical algorithm, Solver finds the optimal level of output, 3.3 lots,
places this value in cell B7, and the other cells (price, revenue, cost, and so
on) change accordingly.
This simple example illustrates but does not do full justice to the power of
spreadsheet optimization. In fact, optimizers are designed to solve complex
problems involving many decision variables and multiple constraints. For
instance, the firm’s profit might well depend on several decision variables: out-
put, advertising spending, the size of its direct sales force. Here, in order to
maximize profit, the manager would specify multiple variable cells in the solver
menu. In addition, the firm might face various constraints in its quest for max-
imum profit. For instance, suppose the microchip producer was quite sure that
setting a price greater than $91,000 per lot would attract a cutthroat competi-
tor whose sales of “cloned” chips would decimate the firm’s own profit. In this
case, management’s constrainedoptimization problem would include the
requirement that the value in price cell C7 should not exceed 91. The bottom
menu in Table 2A.2 includes this new constraint. The spreadsheet’s new opti-
mal solution (not shown) becomes 3.95 lots, implying exactly a $91,000 price
and a reduced profit of $109,350.
To sum up, the beauty of any spreadsheet-based optimization program is
that, upon execution, it instantly computes all optimal values consistent with
satisfying all constraints.c02OptimalDecisionsUsingMarginalAnalysis.qxd 8/17/11 5:17 PM Page 75