The Analytics of Optimization
Elliot Bendoly · Cambridge University Press eBooks · 2008
Excel gives us a great tool that might help us determine what specific decisions (i.e., values of our decision variables) should be used to obtain our objectives subject to the issues that constrain us. This tool is Solver. Generally Solver can be accessed under the Data tab in the Analysis section (Figure 6.1). If you do not find Solver in your Excel Data tab, it means that either Solver was not selected for installation at the time your copy of Excel was installed, or it is currently not activated. To activate Solver, click Office>Excel Options>Add-Ins. Select Excel Add-Ins on the Manage drop-down and then click Go. The Add-Ins dialog box opens, enabling you to choose Solver Add-In (Figure 6.2). Optimization with solver The general structure of Solver fits perfectly with the Chapter 4 description of the three key elements of decision representation/structuring – objective, decision variables, and constraint (Figure 6.3). Solver is designed to provide the best solutions it can based on the info we give it. It has its limits (it breaks down with extremely complex or large problems), but for smaller problems that still present challenges to decision makers, it does a nice job. Rather than talk about the theory and math behind simple optimization, we'll take a page from some of the most successful texts on teaching the value and use of this tool by diving right into a few examples in depth.