What IF analysis Function

by on August 3, 2017

What IF analysis Function

Excel has great analytical functionalities with which one can easily test different scenarios and assess its effects on the results. What IF analysis function used to create scenarios. Assume you are planning to launch a new product or project.  The first step of your planning is to assess what amount of sales you needed to be in profits that are acceptable to you. We will help to understand the process with the help of real time example. Download the file here.

Scenario Manager

Scenario Manager is a sub-function under What IF analysis Function and it used for creating a different set of values or scenarios and one can easily choose the different scenarios and assess its effects on the outcome/results. Our data is provided below:

What IF analysis

We will create three (3) Scenarios for Sales as provided in the image above.

In order to create Scenarios, click anywhere in the sheet, go to data tab and then select the scenario Manager option from the drop down list of what-if-analysis option.  A dialog box will open, type the name of the scenario in the name box (“Best” in our case) and select the changing cell, in our case it’s C3.

null

Selec Ok button will bring you to the next screen.

Creating Scenarios in Excel

Here you need to only enter the “Amount” which is 80,000 for scenario 1 (Best). Following the same steps, you need to create two more scenarios (Good and Acceptable). Once done, click in the worksheet and go to the what if analysis section. Click on the Scenario Manager. The below screen will pop up:

Creating Scenarios in Excel

Select the scenario and select the show option to check its results. Keep your eye on the calculation, it will change when you click on the “Show” button. Another way to choose the scenario is to double click on it. I hope that after going through this article, you are now in a position to build your own scenarios to help you.

Goal Seek Formula

Goal seek is another sub-function under What if Analysis. It finds the right input for the required outputIt. For example, we need a certain profit and need to calculate the sales for that profit. Input should be directly or indirectly linked to the output for goal seek formula to be effective.

Assume Profit level of 75000. Go to the to what if analysis function and click on goal seek. “Set cell” is $C$11 , “To value” is 75000 and “by changing cell” is  C3. Click OK and you are done.

Goal Seek Function

Our results showing that we need to sell an amount of 263,158 to reach at a profit of 75000.

Goal Seek Formula

Goal Seek in NPV calculation

Another example where Goal Seek formula will be of great importance is in the calculation of NPV (Net Present Valu). Positive NPV represents good investment opportunity and negative NPV shows that investment is not viable. Look at the below NPV calculation sheet. Since NPV is negative, the project is not viable. Our objective is now to calculate the cash inflows from year 1 to year 6 at which the NPV will be zero.

Open Goal Seek from what if analysis tab and add the following data. The set cell is E13, the value is 0 and change cell is E6. Keep in mind that cash flows as constant from year 1 to year 6. That means that you need to link it cell E6.  Click OK and you are done. Goal Seek

Once you click OK, your NPV calculation will be updated as below:

What IF analysis Function

What IF analysis Function

 

 

 

 

 

 

 

 

 

The main benefit of Goal seek is that it gives you exact input at which the required output will be the same as you needed it to be. It relieves the user from trial and error hectic routine. share it with your friends so they can also benefit from using Goal Seek formula in their work.

Please follow and like us:
0

Leave a Reply

Your email address will not be published. Required fields are marked *