Using the Goal Seek Command to Forecast
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:
- A formula that calculates your goal
- An empty cell for the missing number that will get you there
- Any other values required in the formula
The empty cell should be referenced in your formula and serves as the variable that Excel changes.
NOTE
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:
- Create a worksheet that contains a formula, an empty variable cell that will hold your solution, and any data that you need to use in your calculation. For example, Figure 23-1 shows how you might set up a worksheet to determine the number of cups of coffee priced at $1.75 that you would have to sell to gross $30,000.
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.
TIP
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.
