Excel's most basic forecasting command is Goal Seek, located on the Tools menu. The Goal Seek command determines the unknown value that produces a desired result, such as the number of $14 compact discs a company must sell to reach its goal of $1,000,000 in CD sales. Goal Seek is simple because it's streamlined— it can adjust only one variable to complete its iterative calculation. If you need to consider additional variables in your forecasting, such as the effects of advertising or quantity discounts on pricing, use the Solver command (described in the next section).
To use Goal Seek, set up your worksheet to contain the following:
The empty cell should be referenced in your formula and serves as the variable that Excel changes.
When you run the Goal Seek command on the Tools menu, the cell containing the formula is called the Set Cell, for it sets the terms that produce a result.
When the Goal Seek command starts to run, it repeatedly tries new values in the variable cell to find a solution to the problem you've set. This process is called iteration, and it continues until Excel has run the problem 100 times or has found an answer within .001 of the target value you specified. (You can adjust these iteration settings by choosing Options from the Tools menu and adjusting the Iteration options on the Calculations tab.) Because it calculates so fast, the Goal Seek command can save you significant time and effort over the brute force method of trying one number after another in the formula.
To forecast using the Goal Seek command, follow these steps:
Figure 23-1. The Goal Seek command requires a formula and a blank variable cell.
ON THE WEB
The GoalSeek.xls example is on the Running Office 2000 Reader's Corner page. For information about connecting to this Web site, read the Introduction.
This value is the one that the Goal Seek command will calculate using your goal and the formula in the Set Cell. The cell will be indicated by a selection marquee (cell D6 in this example), as shown below.
The Goal Seek Status dialog box will display a message when the iteration is complete, and the result of your forecast will appear in the worksheet, as shown in Figure 23-2. This forecast shows that you need to sell 17,143 coffees at $1.75 per cup to reach your sales goal of $30,000.
Figure 23-2. The Goal Seek command displays its result in the empty variable cell that you specified in your worksheet.
In a time-consuming calculation, such as a computation that involves several financial functions, you can click the Pause button in the Goal Seek Status dialog box to stop the iteration, or the Step button to view one iteration at a time.