Using Goal Seek for What-If Analysis

The what-if analysis you've seen so far has concentrated on changing formula variables to see what effect this has on the result. But what if you need to come at the problem from the opposite angle? That is, what if you know what result you're looking for? In that case, what you need to know is the specific set of formula values that will give you the result you seek.

For example, you might know that you need to have $50,000 saved in 15 years for your child's education, or you might know that you have to achieve a 30% gross margin in your next budget. If you need to manipulate only a single variable to achieve these results, you can use Excel's Goal Seek feature. You tell Goal Seek the final value you need and which variable to change, and it finds a solution for you (if one exists). (If you want to manipulate multiple variables, use Solver, instead. See "Solving Complex Problems with Solver," later in this chapter.)

Running Goal Seek

Before you run Goal Seek, you need to set up your worksheet in a particular way. This means doing three things:

• Set up one cell as the changing cell. This is the value that Goal Seek will iteratively manipulate to attempt to reach the goal. Enter an initial value (such as 0) into the cell.

• Set up the other input values for the formula and give them proper initial values.

• Create a formula for Goal Seek to use to try to reach the goal.

Let's suppose that you have future monetary goals that you need to reach by a certain date and that you have an initial amount to invest. Given current interest rates, how much extra do you have to deposit into the investment periodically to achieve your goal? For example, suppose that you want to end up with $50,000 in 15 years to finance your child's college education. If you have no initial deposit and you expect to get 7.5% interest over the term of the investment, how much do you need to deposit each month to reach your target? Figure 2.12 shows a worksheet set up to use Goal Seek:

• Cell B4 is the changing cell: the monthly deposit into the fund (with an initial value of 0).

• The other cells (B2 and B3) are used as constants for the FV() function.

• Cell B5 contains the FV() function that calculates the future value of the college fund. When Goal Seek is done, this cell's value should be $50,000.

Figure 2.12. A worksheet set up to use Goal Seek to find out how much to set aside each year to end up with a $50,000 education fund in five years.

With your worksheet ready to go, follow these steps to use Goal Seek:

1.	Choose Tools, Goal Seek. Excel displays the Goal Seek dialog box.
2.	Use the Set Cell text box to enter a reference to the cell that contains the formula you want Goal Seek to manipulate. (Cell B5 in Figure 2.12.)
3.	Use the To Value text box to enter the final value you want for the goal cell (such as 50000).
4.	Use the By Changing Cell text box to enter a reference to the changing cell. (This is cell B4 in Figure 2.12.) Figure 2.13 shows a completed Goal Seek dialog box. Figure 2.13. The completed Goal Seek dialog box.
5.	Click OK. Excel begins the iteration and displays the Goal Seek Status dialog box. When finished, the dialog box tells you whether Goal Seek found a solution (see Figure 2.14). Figure 2.14. The Goal Seek Status dialog box shows you the solution (if one was found).
6.	If Goal Seek found a solution, you can accept the solution by clicking OK. To ignore the solution, click Cancel.

Goal Seeking with Charts

If you have your data graphed in a 2D bar, column, line, or XY chart, you can run Goal Seek by using the mouse to drag a data marker to a new position. If the data marker represents a formula, Excel uses Goal Seek to work backward and derive the appropriate formula input values.

The following example helps explain this process. Suppose that you want to invest some money every year so that in 10 years, you'll have $150,000. Assuming a constant interest rate, how much do you need to set aside annually to reach your goal? The solution is to adjust the chart data marker at 10 years so that it has the value $150,000. The following procedure shows you the steps to follow:

1.	Activate the chart and select the specific data marker you want to adjust. Excel adds selection handles to the marker. For the example, select the data marker corresponding to 10 years on the category axis.
2.	Drag the black selection handle to the desired value. As you drag the handle, the current value appears in a pop-up, as shown in Figure 2.15. Figure 2.15. Drag the data marker to the desired value.
3.	Release the mouse button. If the marker references a number in a cell, Excel changes the number and redraws the chart. If the marker references a formula, as in the example, Excel displays the Goal Seek dialog box, shown in Figure 2.16. The Set Cell text box shows the cell referenced by the data marker, and the To Value text box shows the new number to which you dragged the marker. Figure 2.16. If the data marker is derived from a formula, Excel runs Goal Seek.
4.	Enter the appropriate reference in the By Changing Cell text box. For the example, you enter B2 to calculate the required annual deposit.
5.	Click OK. The Goal Seek Status dialog box appears while Excel derives the solution for the new number.
6.	When the iteration is complete, click OK. Excel redraws the chart.