gavinshearer.com

« Space Mountain 2 Artwork & Video | Main | Seattle Transit: 2023 »

October 24, 2008

Under-Appreciated Excel Feature Of The Week: Goal Seek

This is cross-posted from the Mac Mojo blog.

Last month's post about Data Tables generated a surprising amount of e-mail from readers. Lots of people, it seems, are interested in learning how to get more out of Excel.

In that spirit, I thought I'd take some time to discuss and showcase a companion feature to Data Tables, called Goal Seek.
As I wrote about Data Tables:

Put plainly, Data Tables let you do "what if..." analyses in Excel. They let you see how the results of a formula change as its underlying variables change. Data Tables let you see an entire range of solutions, rather than just one single answer.

In that post, we explored Data Tables through the frame of taking out a large loan. The feature let us see how our monthly payment would vary if we changed the length of the loan or its interest rate. Our end product was a matrix of numbers that lets us see, clearly, how rates and terms affected our monthly payment.

However, there are a number of times when we're not looking for a table of results, but instead are concerned with optimizing around a single variable. For instance, if I go out to buy a car, I have a certain amount of money - say, $350 - that I can afford to put toward a payment each month. In cases like these, I'd much prefer not to look at a matrix - instead, I'd like to just know how much I can get for my money.

This is where you use Goal Seek.

Like Data Tables, Goal Seek lets you do "what if..." analysis, but it will solve for a specific, single answer. Here, I'm really only interested in one question: given that I have $350 a month to spend, how much car can I afford?

I'll walk through an example to show how it's done.

We first need to set up our spreadsheet. Just as we did with Data Tables, we will make a few assumptions about our loan. (If you want to grab the finished spreadsheet from the Data Tables exercise (download here), it will save you a bit of time.)

Just to plug in some numbers, let's assume that we're borrowing $25,000 for 4 years at 7% interest.  If we set up our spreadsheet as follows and use the Excel PMT function (=PMT(B4/12,B3*12,B2)), we will get a monthly payment of ... $598.66.

A payment of $598.66 is quite a bit more than the $350 I have to spend every month. So now it's time to figure out how much car I can actually afford.

Go to the "Tools" menu and select "Goal Seek..." (it's about 2/3 of the way down the list). The Goal seek dialog will appear:

There are three variables to worry about, here: set cell, to value, and by changing cell. The nice thing is that they work as a sentence:

In other words: you're asking Excel to change the value of cell Z until cell X is equal to some value, Y.

With that in mind, filling out Goal Seek is pretty easy. On our spreadsheet, we want to set cell B5 ("Monthly Payment") equal to the value of our monthly payment ($350). And we want Excel to do that by changing cell B2 ("Amount To Be Borrowed").

Setting Goal Seek to these values, we get:

It's worth noting that "to value" is set to minus 350 (-$350). This is because a monthly payment is a cash outflow - it's money you're giving away each month. This is how the Excel PMT function thinks about monthly payments, and, since we're relying on the PMT function to do all our heavy lifting in the math department, we need to make sure we're speaking a language the function understands. (It's a quirk, but an important one.)

Click OK, and Excel will crunch the numbers. Goal Seek will come back and let you know if it found a solution:

Click OK again, and this dialog will vanish. You'll find yourself back out at your workbook, which should now contain the answer to our question:

Looks like I can afford to borrow $14,616 for my new car  - which means I'm looking less at a new Mini Cooper, and more at a shiny Toyota Yaris. Pretty cool, huh?

So that's Goal Seek - yet another under-appreciated Excel feature. As you might imagine, it's a pretty powerful (and profoundly useful) tool. Personally, I use it all the time for situations like this (which seem to crop up in business pretty regularly).

If you'd like to see my spreadsheet, I've attached it to this post - just click here.

Best of luck!

Posted by Gavin Shearer at October 24, 2008 10:24 AM. Posted to Apple | MSFT.

Trackback Pings

TrackBack URL for this entry:
http://www.gavinshearer.com/weblog/mt-tb.cgi/254

Comments

Posted by: Teri at October 24, 2008 2:07 PM

Posted by: Marnie at October 27, 2008 11:33 AM

Post a comment

« Space Mountain 2 Artwork & Video | Main | Seattle Transit: 2023 »