Tuesday, March 10, 2015

Simplifying Goal Seek in Excel

Simplifying Goal Seek in

Excel

Goal Seek is one of the tools in the What-If Analysis category, which is located under the Data tab in the ribbon. Goal Seek calculates the value of a single input variable of a computation that will produce a specified value in the outcome.

An input variable that is changed in order to produce a specified outcome is called a Decision Variable. The output variable is called the Objective. Goal Seek calculates the value of a single Decision Variable that will produce a specified value of the Objective.

Goal Seek is a very limited tool for the following reasons:

  1. Goal Seek can only calculate the value of a single Decision Variable. This means that all other inputs to the computation must remain constant while only the single Decision Variable can be changed. No other input can be varied while Goal Seek is performing its calculations.

  2. Goal Seek cannot find a solution if any part of the computation contains a discontinuous formula.

The Excel Solver is the preferred tool when attempting to calculate the value of a Decision Variable that will produce a specified result in the Objective for the following reasons:

  1. Excel Solver can calculate the values of numerous Decision Variables simultaneously for a problem that has multiple inputs.

  2. Excel Solver has the capability to operate within constraints that are imposed on any of the variables in the computation.

  3. Excel Solver can calculate the value of Decision Variables that can maximize or minimize an Objective in addition to merely calculating the value of Decision Variables that produces an exact value in the Objective.

  4. Excel Solver can work with many discontinuous formulas within the computation.

  5. Excel Solver can be set up just as quickly as Goal Seek to solve any problem simple enough that can be solved with Goal Seek.

The brief example problem in this article will therefore be solved with both Goal Seek and the Excel Solver to compare the basic operation of the two. The problem to be solved is the following:

Goal Seek in Excel - Initial Problem Data
(Click On Image To See a Larger Version)

The purpose of this exercise is to calculate the value on the Decision Variable (cell B2, which currently contains the value of 4) that will cause the Objective (cell B4, which currently contains the value 625) to assume the value of 3,125. The solution is reached when the Decision Variable equals 5 because 55 = 3,125. This simple problem will be solved with both Goal Seek and with the Excel Solver.

 

Solving a Single-Decision-Variable Problem With Goal Seek

Goal Seek is one of the tools in the What-If Analysis category under the Data tab in the ribbon. Selecting Goal Seek brings up the Goal Seek Dialogue box shown as follows:

Goal Seek in Excel - Selecting Goal Seek
(Click On Image To See a Larger Version)

The Goal Seek Dialogue is very simply to configure because Goal Seek changes the value of a single Decision Variable that will produce a specific value in the Objective cell without allowing any constraints to be imposed on any variables in the computation. The completed dialogue box for this problem is shown below:

Goal Seek in Excel - Goal Seek Dialogue Box
(Click On Image To See a Larger Version)

Clicking OK produces the following solution. Goal Seek occasionally requires computations to be rounded off.

Goal Seek in Excel - Goal Seek Final Answer
(Click On Image To See a Larger Version)

 

Solving a Single-Decision-Variable Problem With Excel Solver

The Excel Solver require just about the same amount of work to set up and solve this problem. The Excel Solver is an add-in that must be activated on a one-time basis before it appears in the ribbon and is available for use. Most versions of Excel ship with the Solver as an add-in that needs to be activated, but some versions of Excel do not.

Assuming that the Solver has activated, the Solver will appear under the Data tab in the ribbon as follows:

Goal Seek in Excel - Select Solver
(Click On Image To See a Larger Version)

Selecting Solver will produce the following empty dialogue box.

Goal Seek in Excel - Empty Solver Dialogue Box
(Click On Image To See a Larger Version)

Goal Seek in Excel - Initial Problem Data
(Click On Image To See a Larger Version)

For the problem shown here, The Solver dialogue box would be completed as follows:

Goal Seek in Excel - Solver Dialogue Box
(Click On Image To See a Larger Version)

Note that the Excel Solver has the capability to solve for multiple Decision Variables simultaneously and also has the ability to operate within constraints that the user can impose. The Solver method used is the GRG Nonlinear method. This solving method is the default for Solver and can be used to solve linear and nonlinear equations that are continuous. The details of the different Solver methods are discussed in other articles in this blog that focus specifically on that topic.

Selecting Solve produces the following output:

Goal Seek in Excel - Solver Solution
(Click On Image To See a Larger Version)

The Solver creates 3 reports related to different aspects of the computation of its solution.

To sum up, it is better to use the Excel Solver to perform any calculation that can be performed with Goal Seek. The Solver can be set up just about as quickly as Goal Seek but has so much more capability and available output information.

 

Excel Master Series Blog Directory

 

Click Here To See a List Of All

Statistical Topics And Articles In

This Blog

 

You Will Become an Excel Statistical Master!

No comments:

Post a Comment