Goal Seek
What is it?
Goal Seek allows the user to set the value in a cell containing a formula by changing the value in another.
When to use it?
Use Goal Seek to find the result you want by changing a single input variable. Say, for example, you believe the S&P is currently overpriced, knowing that since the 1870s the index has historically traded with a price-to-earnings ratio (P/E) of 15 and it is currently trading at 20.58. Before dipping into the market you would like to see the index’s P/E ratio return to its historical average. Here’s how you can use Goal Seek to identify the price the S&P index would need to drop to in order to return to a P/E of 15: Recent S&P Price = 2,105.33. S&P trailing 12 month (TTM) earnings = 102.31 (for Q4 2014).
How it works
Goal Seek uses the techniques of linear algebra for solving equations.
What’s great about it?
Goal Seek saves you time, eliminating the seemingly endless toggling of values in our spreadsheets to get the formulas to achieve our desired results. Goal Seek is great for tweaking successive inputs to our models until the desired result is achieved. In a later tutorial, we will review Solver/Scenario Manager, an Excel-based tool that allows us to change more than one input variable concurrently, making it possible to achieve optimal results across all variables.
Using Goal Seek
1. Set up your worksheet model.
2. Click the Data tab.
3. Click What-If Analysis
4. Click Goal Seek. The Goal Seek dialog box appears.
5. Click inside the Set cell box.
6. Click the cell that contains the formula you want Goal Seek to work with.
7. Use the To value text box to type the value that you want Goal Seek to find.
8. Click inside the By changing cell box.
9. Click the cell that you want Goal Seek to modify.
10. Click OK.
Goal Seek adjusts the changing cell value until it reaches a solution.
The formula now shows the value you entered in Step 7.
11. Click OK.
Your conclusion? You do not want to start investing in the S&P 500 until the market drops to 1,534.65. Stay on the sidelines for now!