How to use Goal Seek feature in Excel 2013

Goal Seek feature is an excellent tool in excel to obtain an input value for the desired output value for a given relationship between variables. This eliminates trial and error method to insert different set of input values to get desired output value and above all it's dynamic to use.

The word "Goal Seek" explains the feature that it will seek to find appropriate input value for desired goal (output).

Let's straight away jump into an example of costing to understand it better. The example given here is used to calculate the Total Cost of the company for given variables i.e. Number of units produced and Variable cost per unit. For example, the fixed cost is $1,000 (cell B2), number of units is inserted in cell B3 (say 500) and variable cost per unit in cell B4 (say $2.5 per unit)

Output calculated as: [Fixed_Cost]+[Units_Produced*Variable_Per_Unit] OR = B2+(B3*B4)

The final output derived for this is in cell B6 (Total cost = $2,250).

Now, suppose I have certain budget for Total Cost (say $1800, the desired output) and I have to determine the Number of units that can be produced with this constraint (for given fixed cost and variable cost per unit assumptions).

Following steps will be useful to use Goal Seek the appropriate input value (Number of units) for desired Total Cost (output):

1. Select the cell whose value you wish to change.

This should be the cell that already contains a formula (here cell B6 which calculates Total Cost)

2. Go to Ribbon. Click...Data--->What-if Analysis--->Goal Seek [ Shortcut: Alt+AWG ]

This will show a Goal Seek dialog box on the Excel screen.

4. The dialog box will have three fields as below:

Set Cell: The cell that will contain the desired output (cell B6 with formula that is already selected)

To Value: Insert the desired output that is required (here 1800, the
value of Total Cost)

By Changing Cell: The cell with input value that needs to be changed as per desired output (Units)

5. Click Ok.

On clicking "OK", a new dialog box showing the status of the analysis will be populated.

IMPORTANT NOTE:

• The status dialog box informs that goal seeking process a found the solution. When this is not the case, the Step and Pause buttons inside the dialog box become visible, and further iterations could be carried to narrow and ultimately get the desired value.

Click "OK" to keep the values. Click "Cancel" in case you want to return to original values