Excel Training by Netresult Training
There are many occasions when you need to know the steps to take to reach a goal you already know. The goal could be an amount, of profit for example, or a percentage. A typical example might be “I need to make 45,00 profit this year. What do I need to change my price to in order to reach that figure assuming my sales volume remains the same?”
In this Excel training tutorial I will show you how a simple tool within Excel’s standard functionality allows you to calculate the value you will have to change to achieve the result you want.
What-If Analysis and Goal Seek
Goal Seek is part of the “What-If” analysis series in Excel and is really useful if you know the answer that you want, but need to work backwards to find the input value that gives you that answer. A kind of reverse engineering, if you like.
Goal Seek allows you to specify an answer and to tell Excel which cell to change in order to achieve this answer. For example, Goal Seek is useful if you need to know how many products you need to sell before you will break even.
First click the Data tab
To use Goal Seek, go to the Data tab, click on the What‐if Analysis button and choose Goal Seek:
The Goal Seek dialog box will be displayed
In the Set cell box, enter the reference or name of the cell containing the formula for which you want to find a specific solution, or click on the selector, and then click on the cell in your worksheet…
In the To value box, enter the value that you want the cell to be…
In the By changing cell box, enter the reference of the cell containing the value to be changed in solving for the result that you want, or click on the selector, and then click on the cell in your worksheet…
Click on the OK button.
A dialog box that shows the status of the goal seeking will be displayed
To pause while goal seeking, click on the Pause button
When Goal Seek has finished, Microsoft Excel displays the results on the worksheet. If the specific solution that you want cannot be reached, Microsoft Excel displays a message.
If Microsoft Excel reaches the solution that you want for your formula, click on the OK button to keep the solution values on the worksheet. To restore the original values, click on the Cancel button.