Show All

Hide All

This article was adapted from Microsoft Office System Inside Out, 2003 Edition by Michael J. Young and Michael Halvorson. Visit Microsoft Learning to buy this book. Michael J. Young is an award-winning author who’s written more than 15 books on using and programming computers, including Microsoft Office XP Inside Out, XML Step by Step, and several editions of Running Microsoft Office. Michael Halvorson has written more than 20 computer books, including the classic Microsoft Visual Basic 6 Professional Step by Step and the popular Microsoft Office XP Inside Out (with Michael Young). He is a former technical editor, acquisitions editor, and localization manager for Microsoft.

Sample files  You can download the sample files that relate to excerpts from Microsoft Office System Inside Out from Microsoft Office Online. This article uses the file Goal Seek.xls.

The basic forecasting command in Excel is Goal Seek, which is 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 calculate only one unknown value. If you need to determine additional unknowns in your forecasting, such as the effects of advertising or quantity discounts on pricing, use the Solver command .

To use Goal Seek, set up your worksheet to contain the following:

• A formula that calculates your goal (for example, a formula that calculates the total CD sales revenue)
• An empty variable cell for the unknown number that will produce the desired outcome (for example, a cell to hold the number of CDs you must sell to reach $1 million in sales)
• Values in any cells (other than the empty cell) that the formula refers to (for example, a cell that stores the price of a CD)

The empty cell should be referenced in your formula; it serves as the variable that Excel changes.

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 clicking Options on the Tools menu, and then 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 you need to use in your calculation.

   For example, the following figure 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.

   

    The variable cell must be blank
    The formula cell determines the value in the variable cell

2. In your worksheet, select the cell containing the formula.
3. On the Tools menu, click Goal Seek.

   The Goal Seek dialog box opens.

   

   The dialog box asks you to supply three variables, "Set cell1 to value by changing cell2." The cell name you selected before you chose the command will appear in the first box, and a marquee will appear around the cell in your worksheet.

4. Press TAB, and then type the goal that you want to reach in the To value box.

   For example, to reach $30,000 in sales, type 30000 in the To value box.

5. Press TAB to select the By changing cell text box, collapse the Goal Seek dialog box, if necessary, and then click the blank cell that is to contain your answer (the variable cell).

   The Goal Seek command will calculate the value for this blank cell by using your goal in the To value text box and the formula in the cell referenced in the Set cell text box. The variable cell will be indicated by a selection marquee (cell D6 in this example):

   

    The Goal Seek command calculates the value of this cell

6. Click the OK button to find a solution for your sales goal.

   Excel will display the Goal Seek Status dialog box when the iteration is complete, and the result of your forecast will appear in the worksheet, as shown in the following figure. This forecast shows that you need to sell 17,143 coffees at $1.75 per cup to reach your sales goal of $30,000.

7. Click the OK button to close the Goal Seek Status dialog box.

   

Tip