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.

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:

  1. 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.
  2. click to view at full size.

    Figure 23-1. The Goal Seek command requires a formula and a blank variable cell.

    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.

  • In your worksheet, select the cell containing the formula. (In the Goal Seek dialog box, this cell is called the Set Cell.)
  • Choose Goal Seek from the Tools menu. The Goal Seek dialog box opens, as shown here. The cell name you selected appears in the Set Cell text box, and a marquee appears around the cell in your worksheet.
  • Press Tab, and then type the goal that you want to reach in the To Value text box. For example, to reach $30,000 in sales, type 30000 in the To Value text box.
  • Press Tab to select the the By Changing Cell text box, move the Goal Seek dialog box out of the way, if necessary, and then click the cell that is to contain your answer (the variable cell).
  • 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.

    click to view at full size.

  • Click OK to find a solution for your sales goal.
  • 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.

  • Click OK to close the Goal Seek Status dialog box.
  • click to view at full size.

    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.

    Running Microsoft Office 2000 Small Business
    Running Microsoft Office 2000
    ISBN: 1572319585
    EAN: 2147483647
    Year: 2005
    Pages: 228
    Authors: Michael Halvorson, Michael J. Young
    BUY ON AMAZON © 2008-2017.
    If you may any questions please contact us: