Learn how Excel's Goal Seek can efficiently calculate unknown variables, simplifying complex forecasting tasks. Gain practical insights into using this tool for financial planning and academic predictions.
Key Insights
- Goal Seek in Excel allows users to calculate unknown variables by setting target outcomes, such as determining that a 7.2% growth rate is needed to grow a $10,000 IRA investment to $20,000 in 10 years.
- The tool simplifies solving algebraic-like problems by automatically figuring out missing values, demonstrated by finding that a final exam score of 91 is necessary to achieve a targeted weighted average of 88.
- Accessible from Excel's Data tab under "What If Analysis," Goal Seek provides a straightforward, user-friendly process to forecast and strategize across financial and educational scenarios.
Goal Seek. You can use Goal Seek as a powerful calculator in Excel. What it will calculate is a part of an equation.
If we step back for a little bit, let's go back to algebra. In algebra, we have an algebraic expression that sometimes includes the value of x. Now the value of x is one of the precedents to the result that we get in the equation. We get 7 here in this algebraic expression, but we don't know the value of x. So the term we use is we need to solve for x. If you take a look at this long enough, you can probably determine that x is equal to 5 because the relationship between 7 and 2 is that if you minus 2 from 7, you'll figure out and get what the answer is for x. So that is a little bit of algebra there, and that's easy to do if you're just looking at this very simple equation.
But here's another equation. Someone has an IRA balance of $10,000. They have an annual growth rate of 5 percent.
Over the course of 10 years, their total return on their $10,000 investment is $16,289. Now they're not really happy with that. They would prefer to have reached $20,000.
So here is what we would have to solve for. In this case, we would refer to this as solving for x. 5 percent is only going to get us to $16,289. So what does our growth rate need to be in order for us to get to $20,000 by year 10? That's where we'll use Goal Seek.
Goal Seek is available on the Data tab. If you move over to the Forecast group, and then you'll click the dropdown for the What If Analysis command, and you'll see Goal Seek is one of the tools you can use for What If Analysis. I'll click Goal Seek.
Now this is very similar to subtotals. You want to be able to create a sentence that makes sense for the result that you want. So the first thing you need to do is select the cell that you want to set to a certain value by changing another cell.
So I want to set this cell to a value of $20,000 by changing the value of the growth rate. Now Goal Seek is going to do its best to calculate what the growth rate needs to be in order for me to get to $20,000. If it's able to come up with an answer, it will let me know.
And it says in order to get to my target value of $20,000, I need to have a growth rate of 7.2 percent. So in class we would have you tackle this next exercise. Pretend you're tutoring someone.
They want to get a weighted average of 88. But currently their final exam score looks like it's going to be 80. That is not enough to get them to a weighted average of 88.
So using Goal Seek, again you'll go to the forecast group and then click the drop down and choose Goal Seek. You'll fill in the appropriate variables. So what I'm looking to do here is set the weighted average to a value of 88 by changing my final exam score or the final exam score of maybe the person I'm tutoring.
When I click OK, Excel lets me know that this person would need to get a 91 on their final exam score in order for them to get a weighted average of 88. And that's how you can use Goal Seek as a forecasting tool and figure out the value of X, the unknown variable that's going to get you to your goal.