Problem

You know that Solver can generate several types of reports upon finding a solution to a problem and you'd like to learn more about these.

Solution

The version of Solver that ships with Excel can generate three reports: an Answer report, a Sensitivity report, and a Limits report. These reports are described in the following discussion.

Discussion

Solver's three reports are helpful when interpreting the results obtained by Solver. When it finds a solution, you're presented with a dialog box containing a message regarding the nature of the solution found. You can choose which Solver reports , if any, to generate. (See the introduction to Chapter 9 for more information.) Solver will generate the selected reports in separate worksheets that it will create automatically. Each of the three available report types is explained in the following sections.

Answer report

Figure 13-15 shows an example Answer report. This is the Answer report for the problem solved in Recipe 13.1.

Answer reports show the initial and final values of the objective (Target Cell) and all variables (Adjustable Cells). You can quickly see from these reports how close your initial guess was to the optimal solution.

This report also shows the optimum values for all constraints, including a note indicating which constraints were actually binding. For Not Binding constraints, the amount of slack is also reported. The slack on a constraint tells you how far away a constraint is from becoming a binding constraint. All this information is helpful in determining which constraints govern, or limit, the problem being solved, and how much leeway you have on other constraints.

Notice that these reports contain cell references to the objective, variables, and constraints. They also include a name for these cells. These are not cell names as defined using Excel's cell name feature (discussed in Recipe 1.14). Instead, these names are automatically generated by Solver using a specific algorithm. If you set your spreadsheet up with this algorithm in mind, the names are meaningful; otherwise, they could be completely meaningless.

Figure 13-15. Answer report

Solver builds names for target, variable, and constraint cells by searching to the left of the given cell for the first cell containing text. At the same time, it looks above the given cell for the first cell containing text. The text in these two cells is combined to create the name shown in Solver reports. Therefore, if you want a cell to have a meaningful label, whether it's a cell containing the objective function, a variable, or a constraint, you need to make sure that the text cells nearest to the left of and above the given cell contain the desired label. I typically put text labels to the left of my target, variable, or constraints cells, and make sure no cell above these has any text.

Sensitivity report

There are two versions of the sensitivity report. The one shown in Figure 13-16 corresponds to a linear problem. Solver generates a different sensitivity report for nonlinear problems, as shown in Figure 13-17. These reports tell you how sensitive the solution is to small changes in variables and constraints.

Figure 13-16. Linear sensitivity report

Referring to Figure 13-16, you will see that the linear problem sensitivity report contains two tables. The first table provides information on the sensitivity of the problem's variables, while the second contains information on the sensitivity of the problem's constraints.

Both tables start with a cell reference column, along with a label and then a final value column. The final values are simply the resulting values of the variables or constraints corresponding to the solution found by Solver. The other columns in these tables give you the sensitivity information.

The Reduced Cost applies to variables whose optimum value turns out to be 0. In this case, reduced cost provides an estimate of how much the objective function will change if you force the variable to assume some nonzero value. Variables whose values turn out to be nonzero for the optimum solution will show a reduced cost of 0.

Figure 13-17. Nonlinear sensitivity report

The Objective Coefficient shows the coefficient corresponding to each variable as it appears in the objective function. The Allowable Increase and Allowable Decrease values tell you that so long as these coefficients remain with their current value plus or minus the allowable increase and decrease, respectively, the optimum values for the variables will remain unchanged, although the optimum value for the objective function (target cell) may change.

The Shadow Price for each constraint shown in the Constraints table tells you how much the value of the objective function will change if the value of the righthand side of the constraint, shown in the Constraint R.H. Side column, changes within the limits specified by the Allowable Increase and Allowable Decrease values.

For nonlinear problems, the sensitivity report is a little different. Figure 13-17 shows the sensitivity report for a nonlinear problem.

As in the linear case, there are two tables, corresponding to variables (Adjustable Cells) and constraints. In both cases the cell reference, name, and final optimum values for each variable and constraint are shown.

For variables, the Reduced Gradient tells you how much the value of the objective function would change if the value of the variable were increased by 1. For constraints, the Lagrange Multiplier tells you how much the value of the objective function would change if the righthand-side value of the constraint were increased by 1.

Limits report

Limits reports tell you how the value of the objective function changes as each variable is maximized and minimized, while all other values are held constant and while still satisfying the problem's constraints. Figure 13-18 shows a typical Limits report.

Figure 13-18. Limits report

The Target values show you the value of the objective function corresponding to the lower and upper limits of the variables obtained through a maximization and minimization process as described a moment ago.

Using Excel

- Introduction
- Navigating the Interface
- Entering Data
- Setting Cell Data Types
- Selecting More Than a Single Cell
- Entering Formulas
- Exploring the R1C1 Cell Reference Style
- Referring to More Than a Single Cell
- Understanding Operator Precedence
- Using Exponents in Formulas
- Exploring Functions
- Formatting Your Spreadsheets
- Defining Custom Format Styles
- Leveraging Copy, Cut, Paste, and Paste Special
- Using Cell Names (Like Programming Variables)
- Validating Data
- Taking Advantage of Macros
- Adding Comments and Equation Notes
- Getting Help

Getting Acquainted with Visual Basic for Applications

- Introduction
- Navigating the VBA Editor
- Writing Functions and Subroutines
- Working with Data Types
- Defining Variables
- Defining Constants
- Using Arrays
- Commenting Code
- Spanning Long Statements over Multiple Lines
- Using Conditional Statements
- Using Loops
- Debugging VBA Code
- Exploring VBAs Built-in Functions
- Exploring Excel Objects
- Creating Your Own Objects in VBA
- VBA Help

Collecting and Cleaning Up Data

- Introduction
- Importing Data from Text Files
- Importing Data from Delimited Text Files
- Importing Data Using Drag-and-Drop
- Importing Data from Access Databases
- Importing Data from Web Pages
- Parsing Data
- Removing Weird Characters from Imported Text
- Converting Units
- Sorting Data
- Filtering Data
- Looking Up Values in Tables
- Retrieving Data from XML Files

Charting

- Introduction
- Creating Simple Charts
- Exploring Chart Styles
- Formatting Charts
- Customizing Chart Axes
- Setting Log or Semilog Scales
- Using Multiple Axes
- Changing the Type of an Existing Chart
- Combining Chart Types
- Building 3D Surface Plots
- Preparing Contour Plots
- Annotating Charts
- Saving Custom Chart Types
- Copying Charts to Word
- Recipe 4-14. Displaying Error Bars

Statistical Analysis

- Introduction
- Computing Summary Statistics
- Plotting Frequency Distributions
- Calculating Confidence Intervals
- Correlating Data
- Ranking and Percentiles
- Performing Statistical Tests
- Conducting ANOVA
- Generating Random Numbers
- Sampling Data

Time Series Analysis

- Introduction
- Plotting Time Series Data
- Adding Trendlines
- Computing Moving Averages
- Smoothing Data Using Weighted Averages
- Centering Data
- Detrending a Time Series
- Estimating Seasonal Indices
- Deseasonalization of a Time Series
- Forecasting
- Applying Discrete Fourier Transforms

Mathematical Functions

- Introduction
- Using Summation Functions
- Delving into Division
- Mastering Multiplication
- Exploring Exponential and Logarithmic Functions
- Using Trigonometry Functions
- Seeing Signs
- Getting to the Root of Things
- Rounding and Truncating Numbers
- Converting Between Number Systems
- Manipulating Matrices
- Building Support for Vectors
- Using Spreadsheet Functions in VBA Code
- Dealing with Complex Numbers

Curve Fitting and Regression

- Introduction
- Performing Linear Curve Fitting Using Excel Charts
- Constructing Your Own Linear Fit Using Spreadsheet Functions
- Using a Single Spreadsheet Function for Linear Curve Fitting
- Performing Multiple Linear Regression
- Generating Nonlinear Curve Fits Using Excel Charts
- Fitting Nonlinear Curves Using Solver
- Assessing Goodness of Fit
- Computing Confidence Intervals

Solving Equations

- Introduction
- Finding Roots Graphically
- Solving Nonlinear Equations Iteratively
- Automating Tedious Problems with VBA
- Solving Linear Systems
- Tackling Nonlinear Systems of Equations
- Using Classical Methods for Solving Equations

Numerical Integration and Differentiation

- Introduction
- Integrating a Definite Integral
- Implementing the Trapezoidal Rule in VBA
- Computing the Center of an Area Using Numerical Integration
- Calculating the Second Moment of an Area
- Dealing with Double Integrals
- Numerical Differentiation

Solving Ordinary Differential Equations

- Introduction
- Solving First-Order Initial Value Problems
- Applying the Runge-Kutta Method to Second-Order Initial Value Problems
- Tackling Coupled Equations
- Shooting Boundary Value Problems

Solving Partial Differential Equations

- Introduction
- Leveraging Excel to Directly Solve Finite Difference Equations
- Recruiting Solver to Iteratively Solve Finite Difference Equations
- Solving Initial Value Problems
- Using Excel to Help Solve Problems Formulated Using the Finite Element Method

Performing Optimization Analyses in Excel

- Introduction
- Using Excel for Traditional Linear Programming
- Exploring Resource Allocation Optimization Problems
- Getting More Realistic Results with Integer Constraints
- Tackling Troublesome Problems
- Optimizing Engineering Design Problems
- Understanding Solver Reports
- Programming a Genetic Algorithm for Optimization

Introduction to Financial Calculations

- Introduction
- Computing Present Value
- Calculating Future Value
- Figuring Out Required Rate of Return
- Doubling Your Money
- Determining Monthly Payments
- Considering Cash Flow Alternatives
- Achieving a Certain Future Value
- Assessing Net Present Worth
- Estimating Rate of Return
- Solving Inverse Problems
- Figuring a Break-Even Point

Index

Excel Scientific and Engineering Cookbook (Cookbooks (OReilly))

ISBN: 0596008791

EAN: 2147483647

EAN: 2147483647

Year: N/A

Pages: 206

Pages: 206

Authors: David M Bourg

Similar book on Amazon

Flylib.com © 2008-2020.

If you may any questions please contact us: flylib@qtcs.net

If you may any questions please contact us: flylib@qtcs.net