Mathias Brandewinder on .NET, F#, VSTO and Excel development, and quantitative analysis / machine learning.
by Mathias 29. July 2009 11:24

Today I came across a post which demonstrates how to use Goal Seek to determine how to save for your retirement. Goal Seek is essentially a simplified solver: point Goal Seek at a cell, tell it how much you want it to be and what cells it can tinker with, and Goal Seek will try to find values that reach that goal. The post is an excellent illustration of what’s great about it: it’s super easy to use, and very practical.

However, there is no such thing as a perfect tool, and Goal Seek can fail miserably at finding the optimal answer to very simple problems. After reading this, I thought it would be a good public service to illustrate what its shortcomings are, especially if you are going to trust it for questions as important as your retirement!

For our illustration, we will use the following setup.

Setup

Now let’s say we want to find a value such that B2 = 250. Following Pointy Haired Dilbert, let’s use Goal Seek:

GoalSeek

Put in a value of 1 in cell B1, and run Goal Seek - here is what happens:

GoalSeekFail

Goal Seek fails to find a value in B1 such that B2 = 250. Complete failure.

Is this because no such value exists? Most definitely not, try 8.846. I was actually too lazy to explicitly solve it, but here is the plot of the function, which shows very clearly that there is a solution to the problem.

Curve

So what happened? I deliberately chose the function f(x) = x^3 – 50 * x, because of its shape. The function has what is called a “local maximum”: around –4, the function peaks at around 130. Goal Seek starts at 1 (the value we provided), and looks for what to do. If it increases the input value, the output goes down, and vice-versa, so it keeps reducing the value, and getting closer and closer to 250 - until it hits the local maximum, and can’t do any better.

NaiveSearch

The issue is that Goal Seek follows a greedy algorithm, which is a very naive optimization approach. Wherever it starts, it looks for the best improvement it can find, from that point, which will result in a local optimum, but not necessarily the best solution.

So what is the morale of the story here? The take-away is that you should always take the results of Goal Seek (and the solver) with a grain of salt. Granted, I built this example explicitly to fool it, but it is actually not that rare to have Excel models which can display this kind of behavior. The results depend a lot on the initial values you provide, so before trusting the answers of Goal Seeks to do anything of importance, try to give it different starting values, and see if the answers are consistent.

Comments

9/17/2009 6:27:09 PM #

John Doe

I've run into this very same problem on multiple occasions with the goal seek function...specifically, we use this function to calculate a return on an investment based given a starting balance to calculate a return that will get us to our ending/ goal capital balance.  We've just discovered this and it now makes us lose a bit of confidence in our model.   So much for avoiding human error using this.

John Doe United States | Reply

9/18/2009 4:17:16 AM #

Mathias

Yes, ROI and Internal Rate of Return calculations are 2 typical applications for Goal Seek - and if you are unlucky enough and the value function is not monotonous w.r.t. the rate, you can get into the type of issue I describe. That's one place where a brute-force approach like the Data Table can help you "scan" possible solutions and spot inconsistencies.

Mathias | Reply

1/7/2011 7:01:25 AM #

Mr Jay


Yes, goal seek has its limitations.  However your function was written to expresly exploit those limitations.  Try changing the input cell to 5 before you start your goal seek.  And for those that may lose confidence in the abilities of goal seek.  Note that it did not return a value and go about its business.  It notified the user that it couldn't find a solution.

A more dangerous idea, that as you very your input cell it is possible to find three seperate solutions to a function result of zero, and two seperate solutions to any value between roughly -105 and +105.

Mr Jay United States | Reply

1/8/2011 6:41:29 AM #

mathias

Hi Jay,

Thank you for your comment. You are absolutely right: the function I use was designed with the purpose of exploiting weaknesses in the algorithm. I personally think that Goal Seek is an awesome function, which more people should know about. I believe we agree that in most cases, it will find a good solution, fast - but in some cases, the algorithm will struggle and produce "odd" answers. Understanding its limits, and what type of situation could confuse the algorithm, helps avoid  bad surprises Smile

mathias United States | Reply

6/19/2011 9:07:40 PM #

Claudius51

In my italian office 2007 version it works fine (not in 2003 version)

Claudius51 Italy | Reply

10/28/2011 11:40:20 PM #

Chuck Caldwell

I was just working some problems in OR regarding how to set a timer to fill containers to a certain amount (x) with fluid. The amount of fluid filling the containers is known to have a normal distribution with a known standard deviation. The problem is to set the timer in such a way that 99.9% of containers have the desired amount. The author hints that Goal Seek can be used to solve the problem.
This is the way I set up the equation: since x is fixed (12 oz in one problem), the amount I want to find is the mean, and I can use a formula =1-NORMDIST(x,mean,sdev,1) to calculate the "success" rate of 99.9%. For exxample, with x = 12, mean = 12, sdev = 0.03, the formula yields 0.5. Then I can use Goal Seek to get the mean that makes the formula yield 0.999, but consistently when I do this with a starting mean x, I get a new mean of 101% of x. And Goal Seek says it found a solution (but obviously does not make the formula yield exactly 0.999).
I suspect this is caused by the "step" Goal Seek is allowed to take (in either direction) to find a better solution.

Chuck Caldwell United States | Reply

10/28/2011 11:43:52 PM #

Chuck Caldwell

BTW, I am using Excel 2007 (and sorry for the typos.)

Chuck Caldwell United States | Reply

10/29/2011 12:00:31 AM #

Chuck Caldwell

Using Solver provides a better (correct) solution as long as you don't attempt anything to ambitious. Set up your problem about the same and have the formula cell "equal" a value of 0.999 and Solver returns the correct value.
I also tried setting up the problem to find the minimum mean that would produce a formula value of at least 99.9% but that blew up and gave me the worst answer of all the alternatives.
(Considering the course material, the best solution would probably be just do the math.)

Chuck Caldwell United States | Reply

5/10/2012 4:28:34 AM #

Paul

For our objective  x^3 - 50 x = 250, I agree that Goal Seek cannot start from an initial guess -4 <= x <= +4 (roughly).

However, I start Goal Seek from an initial guess x < -4 (or, let's say x = -8), how can Goal Seek find x = 8.84646177229743 (which is located on the "other side" of the curve)? Note that, if Goal Seek uses the Newton-Raphson method, x = -8 cannot be used as an initial guess, but x > 4 can be. Any idea?!

Paul Canada | Reply

Add comment




  Country flag

biuquote
  • Comment
  • Preview
Loading



Comments

Comment RSS