Goal Seeking In Excel

By Author: Ed Bolton
Total Articles: 17
Comment this article

Microsoft Excel is a phenomenally powerful calculator. You can create spreadsheets with 10,000 lines of data and calculate subtotals instantly. Indeed, if you change your data, any totals will get automatically updated. Arguably that’s not too impressive. If we have quarterly revenues of $1m, and we secure another $20k, we can update our subtotal without summing revenues from scratch.

So it’s more impressive that Excel can do the same thing with statistical functions. If you’ve ever plotted a chart on Excel, you may be aware that you can add a best fit line. These best fit lines are calculated using a method known as regression. Basically, you have to calculate the distance of every single point from the line, and minimise the sum. The maths is a little more sophisticated but the key point is that, every time you change the data, you need to perform the analysis all over again.

In a well-designed spreadsheet, any output can be calculated from the raw data. However, that’s not always enough. Sometimes the output is fixed and the raw data is variable. Let’s say you run an investment ...
... company and want to offer your clients a fixed return. An Excel expert could create a very complex model to calculate the likely return on investments over a fixed period. You could then calculate the internal rate of return being offered to clients.

The problem is that you’re not interested in the return offered to clients; that is, after all, fixed. Instead you’re concerned with how much money you expect to draw from the investment fund, whilst still offering your investors a satisfactory return. If you
have $1 and owe investors a quarter, you can calculate your profits using a simple formula.

Unfortunately an internal rate of return is time dependent so the amount you can withdraw depends on when you take it. Suffice to say, the only way to calculate the amount you can take e.g. halfway through the life of the fund, is by trial and error. If you are evaluating a number of investment opportunities, that can be a very time consuming process.

Therefore Microsoft have built the Goal Seek function to aid your spreadsheet development. Since Excel 2007, it has been available from the Data ribbon. In earlier versions of Excel, it was present from the Tools menu. It gets straight to the point. It asks you which value you would like to fix (in this case the investor’s return), what you would like to fix it to, and asks what you would like to change. All fields can accept cell references. It will then calculate the input through trial and error.

It does have one severe limitation. Goal seek is not a formula. It does not permanently reside in the spreadsheet or the selected cells. Therefore you have to re-run Goal Seek each and every time you change the spreadsheet. Often this is acceptable because you have created a model specifically to calculate that one parameter. Having said that, there will be occasions where this is inadequate.

The good news is that Goal Seek can be accessed and run from Visual Basic. An Excel programmer can write a short script to ensure your variables get re-calculated each and every time you alter your spreadsheet. As Goal Seek takes a noticeable period of time, this could adversely affect your sheet’s performance. The beauty of accessing Goal Seek from Visual Basic is that you can switch off all the processes, such as refreshing the screen, that cause the function to run slow.

About the author: Ed Bolton is the founder of Excel4Business, and expert in spreadsheet development.