SOLVER basics 2

Download: SolverBasics-p2_init.xlsx

Introduction

In this screencast we will continue our exploration of SOLVER by using it to achieve other objectives (maximization, finding roots). We will also explore troubleshooting steps to take when SOLVER fails to find a solution.

Note that if SOLVER exits abnormally (which happens) it often leaves Excel in an undesirable state. Specifically: it will sometimes fail to turn on automatic recalculations (without telling you) and if you try to open SOLVER again it will fail to find solutions even for simple problems where it should work. The CLS-Stats add-in includes a utility to reset SOLVER to its initial state (and to turn automatic recalculation back on) which should fix the issues. Or you can just restart Excel.

Learning Goals

• Learn to use SOLVER to find other objectives: maximization, finding roots
• Learn to troubleshoot SOLVER when it fails to find a solution

Tasks

• Set $X = 2$ and (using the GRG “single start” option) attempt to find the maximum of the function. Explain what happened: why didn’t SOLVER find the global maximum?
• Constrain $X$ so that $1 \le X \le 5.25$; this will involve adding two constraints to SOLVER. Then change the initial value to $X = 5$ and make a prediction of what will happen when you ask SOLVER to find the maximum. Invoke the Solver and generate a report.
• Switch the GRG option “single start” to “constrained Multistart” and reset the initial value of $X = 5$ again. What do you expect will happen? Invoke SOLVER to check your prediction, generating a report.
• Set the SOLVER goal to set the objective cell to be equal to one; in other words we are finding $X$ so that the polynomial $Y = 1$. This is equivalent to finding the root of a polynomial. Set $X = 1$ and invoke SOLVER. Did it behave as expected? Generate a report.
• Now set $X = 2.5$. Before invoking SOLVER make a prediction of what will happen. Then run the Solver. Why did it fail?
• Review the troubleshooting slides (attached below).

Download: Solver-Troubleshooting-slides.pdf