Amal Nagm

The Goal Seek command

Question: For a given price, how many glasses of lemonade does a lemonade store need to sell per year to break even?

The Goal Seek feature in Office Excel 2007 enables you to compute a value for a worksheet input that makes the value of a given formula match the goal you specify. For example, in our lemonade store example, suppose we have fixed overhead costs, fixed per-unit costs, and a fixed sales price. Given this information, we can use Goal Seek to calculate the number of glasses of lemonade we need to sell to break even. Essentially, Goal Seek embeds a powerful equation solver in your worksheet. To use Goal Seek, you need to provide Excel with three pieces of information:

• Set Cell Specifies that the cell contains the formula that calculates the information you're seeking. In the lemonade example, the Set Cell would contain the formula for profit.
• To Value Specifies the numerical value for the goal that's calculated in the Set Cell. In the lemonade example, because we want to determine the sales volume that represents the breakeven point, the To Value would be 0.
• By Changing Cell Specifies the input cell that Excel changes until the Set Cell calculates the goal defined in the To Value cell. In the lemonade example, the By Changing Cell would contain annual lemonade sales.

Our work for this section is shown in Figure 6. Once again I have assumed an annual fixed cost of $45,000.00 and variable unit cost of $0.45. Let's assume a price of $3.00. The question is how many glasses of lemonade we need to sell each year to break even.

To start, insert any number for demand in cell D2. In the What-If Analysis group on the Data tab, click Goal Seek. Now fill in the Goal Seek dialog box as shown in Figure 7.

The dialog box indicates that we want to change cell D2 (annual demand, or sales) until cell D7 (profit) hits a value of 0. After clicking OK, we get the result that's shown in Figure 6. If we sell approximately 17,647 glasses of lemonade per year (or 48 glasses per day), we'll break even. To find the value we're seeking, Excel varies the demand in cell D2 (alternating between high and low values) until it finds a value that makes profit equal $0. If a problem has more than one solution, Goal Seek will still display only one answer.