What-if-Analysis for Excel Part 1- Goal Seek

Introduction

By using three Excel what-if analysis tools, you can experiment with different sets of values in your driver cells and explore how they affect the outcomes in different formulas. Here we are going to discuss the first one goal seek

Goal Seek allows you to find the necessary input value when you already know the desired output value; effectively, you start with the output value and work backwards.

Using Goal Seek

If you know the result you want from a formula, Goal Seek will help you determine the value that the driver input cell needs to be to get that result. Goal Seek can only work with one variable.

Where quantity is quantity sold , Unit Price is the cost to make each item , cost price is selling price . Revenues is Quantity * Cost price , CoGS(cost of goods sold) is -Quantity* Unit Price , fixed cost is some cost to produce items. Assuming we dont have any other expense or profit the

Net Profit=Revenues + CoGS+Fixed Cost

Now what we want to see is how many quantities we should sell in order to breakeven(Net Profit=0), so basically we need to start working backwards from Net Profit to quantity. To do that we use Goal Seek .

Steps to use goal Seek

1. Click the Data tab, click What-if-Analysis, and choose Goal Seek The Goal Seek dialog box is displayed.

2. Here is the Goal Seek Window

3. Enter the Set cell(i.e., the output cell in our case Net Profit)Tip: You can click outside the Goal Seek dialog box and click the appropriate output cell and it will be automatically populated in the Set cell field.

4. Enter the To value (i.e., the desired output in our case it is 0)

5. Enter the By changing cell (i.e., the cell you want to change to get the desired result, in this case Quantity)

6. Click OK 

7. Goal Seek will change the By changing cell input and overwrite the existing value; you can click       Undo to revert to the original value.

Final Result using goal Seek

Goal Seek is a powerful feature in excel that helps us to track the formula backwards and get the desired output.The only limitation it works on One variable at a time . To change two variable we use Data Tables which we will discuss in the next part.


For using Data Tables in Excel use the link below -
http://manishexcel.blogspot.in/2015/09/what-if-analysis-for-excel-part-2-data.html

For using Scenario Manager in Excel use the link-
http://manishexcel.blogspot.in/2015/09/what-if-analysis-for-excel-part-3.html