What IF analysis Function

by on August 3, 2017

 

What IF analysis Function

MS Excel is a data analysis tool used from basic to complex data with the help of functions and formulas. What IF analysis takes the point that what will be the effect if one change the input or the output. How much the impact on the net profit will be in case we change the sales. what our sales need to be in order to earn a certain level of profit. In this article, we will provide answers to such type of questions with examples.

Sub Functions under What IF analysis

  • Scenario Manager

  • Goal Seek

  • Data Tables

Let’s have a look at these powerful functions in turns.  Download the file here.

Scenario Manager

Scenario Manager is a sub-function under What IF analysis Function. It used to create different scenarios and tested for available data and based on the results, the final decision is made. 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

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 output. 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 a 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.

Goal Seek in NPV calculation

Goal Seek in NPV calculation

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

The main benefit of Goal seek is that it gives you exact input at in order to give the desired output. It relieves the user from trial and error hectic routine.

Data Tables

Data table evaluates multiple variables/input at a time and provides its results in a table. In our examples below, we want to determine total sales for T Shirt Product at different quantity and prices. Total sales Formula is = QTY*Price.

Prepare a table like the one provided below. Now in cell E6, enter the formula D2 (Sales Qty)*D3(Sales Price). From F6:N6, enter the different sales quantities. similarly from E7:E21, enter the different sales Prices.

Data Tables

Once your data table is set. Select the data table range (E6:N21). Go to what if analysis under data tab. Select data table. A similar dialog box will appear on your screen. Enter the information as provided below and click OK.

Data Analysis in Excel

At the click of OK button, your data table is ready. Now we have the sales reports at different levels of sales quantity at variable prices. Data Tables can be used to calculate interest payments for loans with different maturity levels at different rates.

share it with your friends so they can also benefit from using these powerful functions.

Please follow and like us:
0

Leave a Reply

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