Question
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
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 |
Step by Step Solution
There are 3 Steps involved in it
Step: 1
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-text-image.webp)
Get Instant Access with AI-Powered Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-subtext-image.webp)
Step: 3
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-subtext-image.webp)
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started