In Appendix 7.1 we showed how Excel Solver can be used to solve a linear program by using it to solve the RMC problem. We used reports similar to Excels Answer Report in Chapter 7, but these reports do not contain the sensitivity analysis information discussed in this chapter. Let us now see how Excel Solver can be used to provide sensitivity analysis information.

When Excel Solver has found the optimal solution to a linear program, the Solver Results dialog box (see Figure 8.23) will appear on the screen. If only the solution is desired, simply click OK. To obtain the optimal solution and the sensitivity analysis output, you must select Sensitivity in the Reports box before clicking OK; the Sensitivity Report is created on another worksheet in the same Excel workbook. Following this procedure for the RMC problem, we obtained the optimal solution shown in Figure 8.24. Figure 8.25 shows the Sensitivity Report as generated by Excel Solver. Note that there are no columns for Model Variables or Constraint Numbers as shown in the reports in Chapter 8. The Cell columns in Figure 8.25 correspond to the location of the decision variables and constraints in the Excel model (Figure 8.24).

Figure 8.23Excel Solver Results Dialog Box to Produce Sensitivity Report

Figure 8.24Excel Solution for the RMC Problem

Figure 8.25

Excel Solver Sensitivity Report for the RMC Problem

Adjustable Cells
Cell	Name	Final Value	Reduced Cost	Objective Coefficient	Allowable Increase	Allowable Decrease
$B$15	Tons Produced Fuel Additive	25.000	0.000	40.000	20.000	16.000
$C$15	Tons Produced Solvent Base	20.000	0.000	30.000	20.000	10.000
Constraints
Cell	Name	Final Value	Shadow Price	Constraint R.H. Side	Allowable Increase	Allowable Decrease
$B$20	Material 1 Amount Used	20.000	33.333	20.000	1.500	6.000
$B$21	Material 2 Amount Used	4.000	0.000	5.000	1E+30	1.000
$B$22	Material 3 Amount Used	21.000	44.444	21.000	9.000	2.250