The Goal Seek Excel 2007

Author: mety Nagm Labels::

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.

Data to set up the Goal Seek feature to perform a breakeven analysis

Figure 6 We'll use this data to set up the Goal Seek feature to perform a breakeven analysis

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 Goal Seek dialog box with entries for a breakeven analysis

Figure 7 The Goal Seek dialog box filled in with entries for a breakeven analysis

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.



Blog Archive

Powered by Blogger.

I made these pages for me and my friends to help solving the problem we face regarding Computer & internet, if anyone wants me to answer a question or find out about some information please send me email and I will try to reply.*P.S. some of the article I wrote and the other I found on the internet I posted them in sprit of learning and shearing, please forgive me if you found something you don’t want to be in my blog, email me and I will delete them. Thank you for your interest in my pages.امل نجم Amal Nagm

banner 1 banner 2