Working backwards using Goal Seek
Usually, you run a formula to calculate a result based upon existing values. By contrast, using Tools > Goal Seek, you can discover what values will produce the result that you want.
To take a simple example, imagine that the Chief Financial Officer of a company is developing sales projections for each quarter of the forthcoming year. She knows what the company’s total income must be for the year to satisfy stockholders. She also has a good idea of the company’s income in the first three quarters, because of the contracts that are already signed. For the fourth quarter, however, no definite income is available. So how much must the company earn in Q4 to reach its goal? The CFO can enter the projected earnings for each of the other three quarters along with a formulaq that totals all four quarters. Then she runs a goal seek on the empty cell for Q4 sales, and receives her answer.
Other uses of goal seek may be more complicated, but the method remains the same. Only one argument can be altered in a single goal seek.
Goal Seek example
To calculate annual interest (I), create a table with the values for the capital (C), number of years (n), and interest rate (i). The formula is I = C*n*i.
Let us assume that the interest rate i of 7.5% and the number of years n (1) will remain constant. However, you want to know how much the investment capital C would have to be modified in order to attain a particular return I. For this example, calculate how much capital C would be required if you want an annual return of $15,000.
Enter each of the values mentioned above into adjacent cells (for Capital C, an arbitrary value like $100,000 or it can be left blank; for number of years n, 1; for interest rate i, 7.5%). Enter the formula to calculate the interest I in another cell. Instead of C, n, and i use the reference to the cell with the corresponding value. In our example, this would be =B1*B2*B3.
- Place the cursor in the formula cell (B4), and choose Tools > Goal Seek.
- On the Goal Seek dialog, the correct cell is already entered in the Formula cell field.
- Place the cursor in the Variable cell field. In the sheet, click in the cell that contains the value to be changed, in this example it is B1.
- Enter the desired result of the formula in the Target value field. In this example, the value is 15000. The figure below shows the cells and fields.
- Click OK. A dialog appears informing you that the Goal Seek was successful. Click Yes to enter the result in the cell with the variable value. The result is shown below.
| Content on this page is licensed under the Creative Common Attribution 3.0 license (CC-BY). |
