Today’s topic is Goal Seek.
Goal Seek is a very handy tool to use when you know what you want the result to be, but you don’t know what inputs will get you to that result.
To illustrate this, pretend you want to buy some shares of General Electric (GE) today and then sell the shares sometime in the future for a profit of $100. Using goal seek you can determine how high the stock would have to rise in order to meet your investment criteria. First, start by setting up a simple table with the buy and sell events modeled:
In the above table the values in the “Total” column (E) are formulas while everything else is an input. Currently the table shows the buy and sell price as the same. Using goal seek we will change the sell price to the value that will result in a profit of $100.
Start by placing the cursor in E4. Then go to “Data” then to “What if analysis” and select “Goal Seek” The keyboard short cut is Alt→A→W→G. If you’re using a Mac it is the same, except you don’t have a keyboard shortcut. You should see a box that looks like this:
Goal Seek takes three inputs:
Set cell: The cell which contains the formula you want to set to a certain number. In our case this is the total profit.
To value: The value we want the above cell to be. In our case this is $100.
By changing cell: The cell you want Excel to alter to get to your desired result. In our case this is the selling price (B3). So our completed Goal Seek dialogue box looks like this:
After you hit “OK” Excel will try a bunch of different numbers for the selling price until it gets to the right one. Eventually you will see this in your spreadsheet:
And now you know that the price of GE stock would have to rise to $22.76 in order to make $100 if you bought 50 shares.
Keep in mind this only works if the two cells you feed the Goal Seek box meet certain criteria. 1) They have to be connected through formulas 2) “Set Cell” has to be a formula 3) “By changing cell” has to be an input value.
Loading ...