Business

Appendix A Using the Solver Table

Solver Table is an add-in developed by the authors that is useful for doing sensitivity analysis on any spreadsheet model that has been solved using the Solver. In particular, it will allow you to vary one or two data cells in a model and see the impact on the optimal values of the changing cells, the target cell, and/or any other output cells of interest. The Solver Table add-in and installation instructions are contained on the CD-ROM that is packaged with this textbook. This appendix gives instructions for using the Solver Table add-in. We also will use the Wyndor Glass Co. problem from Chapters 2 and 5 to illustrate the use of Solver Table

Using Solver Table to Do One-Way Sensitivity Analysis Solver Table is used to show the results in the changing cells and/or certain output cells for various trial values in a data cell. For each trial value in the data cell, Solver is called on to re-solve the problem. Before using Solver Table, the model must first be developed in the spreadsheet in the usual way, including entering all of the appropriate parameters into the Solver (e.g., the location of the target cell, changing cells, constraints, etc.). Figure A.1 shows the spreadsheet solution for the Wyndor Glass Co. problem. Although this particular example is a linear programming model, Solver Table also can be used on integer and nonlinear programming models, as well as on any other model that can be solved using Solver.

Appendix A

A 1 2 3 4 5 6 7 8 9 10 11 12 B C D

Installing and Using the Solver Table

E F G

Wyndor Glass Co. Product-Mix Problem

Unit Profit Plant 1 Plant 2 Plant 3 Doors $300 Windows $500 Hours Used 2 12 18