Thursday, December 23, 2004

Goal Seek in Excel

The Goal Seek Tool in Excel is one of the best easy to use and understand functions there is.

Today I was helping a client calculate a breakeven profit and to do this we changed the sale price of his product using the Goal Seek Tool.

Here's how we did it.

From the menu select Tools - Goal Seek.
Set cell: should be the cell that you are wanting to acheive the result in. In this case it was the cell showing his calculated profit.
To value: is the result you want to achieve; in this case zero.
By changing cell: is the cell that you want the Goal Seek tool to change in order to achieve your desired result. In this example it was the sale price of his product.
Clicking OK sends the tool into a number of iterations (maximum 100) to attempt to resolve the solution. Note: the Set cell and the By changing cell must be related dependantly. That is the Set to cell must change if you change the value in the By changing cell.

In our example the clients sale prices was modifed to the price required to breakeven given his quantity of sales, direct costs and over heads to be recovered.


Remember all archived tips are available on the excel tips website for your use.


Posted by: Jethro | 4:30 PM |