Exploring what-if scenarios using Goal Seek

- Excel's "Goal Seek" feature located on the Data tab under "What If Analysis" is a powerful tool for letting us come up with a different answer. Now, you could describe this in a fanciful way. I've got a formula here that's adding up data from columns F and G, I'd like to have a different answer. Let's connect this with a real life situation. This is an organization that's going to provide bonuses for people based on their job ratings, and there's a formula in column G that's going to be using this table here to the right. And in effect, the table will give us answers saying if your job rating is a 2 you'll get a bonus of $375.

If it's a 3, it's $1500. If it's 4 or more, $3000. And in this table, these numbers are related to one another. The 3000 here is a pure number, the 1500 here is half of that, and the 375 here is a quarter of that. Now, you're the chief financial officer and you've decided that the company can afford to spend $28.5 million on the combination of these two. This is the formula right here in this cell here.

It's a merged cell, and it's adding up the data. So, we want that total there to be 28.5 million. "Goal Seek" in effect says you can change this cell. You can come up with a different value based on one other cell changing, and only one. If you want a more sophisticated variation on this, by the way, the Excel add in feature called "Solver" allows you to work with formulas and come up with up to 31 different cells that could change, a much more sophisticated mathematical modeling tool.

"Goal Seek" only one cell but, nevertheless, powerful. And this example here, we are going to in fact change all the entries in column G so that our total changes. "Goal Seek" will allow us to point to that one cell, it's going to be the cell with the 3000. Remember the two cells above it are formulas based on the 3000, and the formula in column G, let's take a look at it here, in G2 and downward, is a VLOOKUP formulas that says let's look at the job rating, compare it with the left column of the table over here and when we come to the appropriate amount, go into the second column to get our answer.

And these must be in ascending order in this example here. So this is an example of using VLOOKUP and so called approximate match. We don't need a fourth argument here for an exact match, although we could possibly have used that function. So, let's change this value by way of "Goal Seek". On the Data tab in the Data Tools group, "What If Analysis", "Goal Seek". Set the cell J9 to be equal to the value 28, and we can use commas here if we wish, 28,500,000.

By changing which cell, the cell K7, click "OK", we've got our new total, if we want to keep it, click "OK", if we don't click "Cancel". It looks okay from here, we've got a new total here. We'll click "OK". Notice this number's changed, this is half of it, this is a fourth of the previous number. This number, however, is not exact. If we double-click cell K7, we see here's the real answer here. Now if we simply want our total to be a little bit more exact, in other wards, let's round off the pennies here, let's change this actual amount so that it's $28 here rather than $27.70.

So, we actually are raising it slightly, this means that our grand total will go up a little bit as I press Enter. Not a whole lot, it went up by $60. But we now have whole numbers here as our answers. So we can see how easy it was to set this up. And you can imagine other scenarios using "Goal Seek", in this case, involving quite a few other cells. Not only these here, which trigger the action, but the VLOOKUPs that we're seeing over here. So now our total, we can also see this by the way, by dragging across columns F and G you'll see that in the status bar too, that adjusted total.

Another example of "Goal Seek" over in columns M, N and O you're involved in sales and you'd like to reach 100,000 in sales for the year. Here are the totals so far, it's early July, here's what's been happening. Out in column O we're tracking the percent of change month by month, a simple formula out here. We're seeing what's happening, and you can see the numbers out here. And our total so far for the first half is 45,000 so we need to make close to 55,000 here for the remaining 6 months. Now, we don't always grow at a steady pace but we're guessing that if starting in July, if we grow at 2% a month, in other wards if this number is 2% bigger than this, we will reach our total.

So, let's put in a formula here, I'm going to write a formula that's going to use that 2% there, equal the cell above it, that's N7 times the 2% up here. We'll be using this over and over so let's make that an absolute address. I'll press the function key, F4, plus N7, one of a number of different ways we could write this formula. I'll press Control Enter instead of Enter so the active cell does not move downward. That's going to be our July total if we grow by 2%. And we can drag this down the column to see our other numbers, but at this rate we're not going to reach 100,000.

And let's be realistic too, we don't always want to grow at the same percent, but let's say in this ideal world if we grow at 2% here's our total. As I drag this formula downward, we'd expect to see 2% for all these, at least for now. But, we want this to be 100,000. Let's us "Goal Seek" again. We want this total here in cell N14 to be equal to 100,000. That's our total for the year. By changing which cell, the 2% was just a guess but that's what we're basing our formulas on.

Let's change that to the appropriate percent. And there we see a total, once again, if we want to keep this we'll click "OK", if not we click "Cancel". So it looks like 3.31%, that too is not an exact number. Let's double-click cell N1, take a look at that. Let's make it truly 3.31%. That's our goal, Enter, it'll go over 100,000 slightly, but close enough. And again, in this ideal world where things grow at a steady pace, this is what we've got to maintain.

Now as you start to record the actual numbers here, you might do this in a separate column, you'll be checking the percent of growth month to month maybe it won't quite work out this way but at least you know what you have to growing at on average month by month here to even out close to 100,000 or slightly over. Another example over here, in columns R through S. You live in Houston, you've got a friendly rivalry with your friend in Dallas-Fort Worth and you expect Houston, the metro area, is going to exceed Dallas-Fort Worth.

Here's the numbers for the year 2010, here are the latest estimates for 2013 and you've heard already that Dallas is projected to grow at the rate of 1.5% per month over the next few years. You've a friendly bet with your friend from Houston, you've got a friendly bet with your friend from Dallas. By the year 2025, Houston will overtake Dallas, that's what you're projecting. And you've heard that Houston's going to grow by 1.75% and you're hoping that's going to be enough, if it's going to overtake Dallas by then.

So let's write formulas here that use those numbers down in row 16. We could actually highlight both cells together here, equal the Dallas-Fort Worth entry, we'll start with this one times and we'll use the number down below. Now since we are going to be using this in separate columns, we'll press the F4 key a few times so the absolute reference is in row 16, plus the cell above and we can press Control Enter here. Get our answers. And then just copy that down the column.

Now off to the right, we've got formulas that are calculating the difference here. That's Houston minus Dallas, and you see what's happening over time if Houston grows at this rate it's not going to surpass Dallas, in fact it's not even going to be close, as we see the number down here at the bottom. So Houston has to grow at a faster percent. Let's figure out what that percent is. We want the difference for the year 2025 to be positive. We could put in zero or let's say let's put in the number one, positive one. That'll work.

Let's go to "What If Analysis" again, "Goal Seek". We want that cell, U15, remember that's the difference between our two 2025 numbers here, we want that to be one, we don't care if it's greater but at least one, by changing which cell, the percent that Houston is projected to grow at. That's the 1.75%. Remember, that's going to change the other numbers in here, click "OK". And there it is. If we click "OK", Houston will need to grow at 2.144%.

That too is not an exact number as we double-click. So let's make it to simply be 2.15, more than enough. Is that a realistic number, well, we'll have to talk to the demographers about that. Anyway, at that rate Houston will have exceeded Dallas-Fort Worth and you will have won your bet if these figures hold up. Of course, they vary over time and they probably will not be this exact or they could be averaging this over time. So, we've seen three different examples of how to use "Goal Seek", a powerful tool.

If you'd like to use a more sophisticated variation of "Goal Seek" check out the Excel add in called "Solver". It allows you to work with formulas that can depend on up to 31 different cells, not just one cell.