Optimization Modeling with Spreadsheets, 3rd Edition

4SENSITIVITY ANALYSIS IN LINEAR PROGRAMS

As described in Chapter 1, sensitivity analysis involves linking results and conclusions to initial assumptions. In a typical spreadsheet model, we might ask what-if questions regarding the choice of decision variables, looking for effects on the performance measure. Eventually, instead of asking how a particular change in the decision variables would affect the performance measure, we might search for the changes in decision variables that have the best possible effect on performance. That is the essence of optimization. In Microsoft^® Office Excel^®, the Data Table tool allows us to conduct such a search, at least for one or two decision variables at a time. An optimization procedure performs this kind of search in a sophisticated manner and can handle several decision variables at a time. Thus, we can think of optimization as an ambitious form of sensitivity analysis with respect to decision variables.

In this chapter, we consider another kind of sensitivity analysis—with respect to parameters. Here, we ask what-if questions regarding the choice of a specific parameter, looking for the effects on the objective function and the effects on the optimal choice of decision variables. Sensitivity analysis has an elaborate and elegant structure in linear programming problems, and we approach it from three different perspectives. First, to underscore the analogy with sensitivity analyses in simpler spreadsheet models, we explore a Solver-based ...