Using Goal Seek
Goal Seek allows you to determine the values that are required for a particular function when you set a specific value as the target return for the function. Goal Seek is very useful in cases where you want to see what your sales figures would have to be to reach a certain annual sales figure or to compute the interest rate that you would have to receive on a particular investment to reach a goal value.
Let's say you want to make a monthly payment into an investment account so that over a 10-year period at a 6% annual interest rate, you end up with 20,000 dollars in the account. All you have to do is set up Calc's PV formula and then use Goal Seek to figure out how much the monthly investment payment into the account would have to be.
Figure 9.11 shows a sheet where the PV formula (the syntax is PV (interest rate;term;monthly payment)) has been set up and Goal Seek is being used to determine what the monthly investment payment would have to be to reach an investment goal of $20,000 over a ten-year period.
Figure 9.11. Goal Seek can be used to determine the value for a particular variable in a function given a particular result for the function itself.
To Set Up the Goal Seek Function
To use Goal Seek, you need to set up the function or formula that you will use Goal Seek on. You don't have to enter a value for the cell; that will be a variable value determined by Goal Seek.
1. |
Enter the appropriate labels onto your sheet. |
2. |
Create your formula or function on the sheet (use the Function Autopilot to create functions). |
3. |
Input the values in the sheet that will remain static when you use Goal Seek. |
To Use Goal Seek
Once you have the formula or function on the sheet, you can use Goal Seek to determine what a particular value needs to be to reach a particular outcome (meaning, What does a value have to be so that the formula or function will return a value that you pick?).
1. |
Select the Tools menu, then select Goal Seek. The Goal Seek dialog box will appear (see Figure 9.12). Figure 9.12. The Goal Seek dialog box is used to specify the location of the formula, the value Goal Seek should compute, and the outcome that you want the formula to return.
|
2. |
Click the Shrink button next to the Formula cell box. On the sheet, click on the cell that holds the formula or function. |
3. |
Click the Shrink button on the Goal Seek rollup and you will be returned to the Goal Seek dialog box. |
4. |
Enter the value that you want the formula to return in the Target Value box. |
5. |
Click the Shrink button next to the Variable cell box. Click on the cell that holds the variable value for the formula or function. |
6. |
Click the Shrink button on the Goal Seek rollup and you will be returned to the dialog box. |
7. |
Once all the cell addresses and values have been placed in the Goal Seek dialog box, click OK. |
8. |
A dialog box will appear, letting you know that Goal Seek was successful. The box will provide a value that can be entered into the sheet to return the value you designated (see Figure 9.13). Click Yes to have the value placed in the appropriate cell. Figure 9.13. Goal Seek will let you know what the value needs to be to return your expected result.
|
As soon as you use Goal Seek to place the found value in the cell, your formula or function will return the value that you set.