HOW TO DO WHAT-IF ANALYSIS IN EXCEL?

INTRODUCTION

The What-If analysis is done for a scenario where we have two conditions and we want a report of the outcome simultaneously. e.g. how much distance our vehicle would cover if the speed is 80 kmph and 60 kmph in 2 hr time. It can be done using this analysis. So lets try to understand how to do this.

BUTTON LOCATION FOR INSERTION OF TEXT BOX IN EXCEL

The button or option for the WHAT IF ANALYSIS is present under the DATA TAB as shown in the picture below.

DIFFERENT WAYS TO DO WHAT-IF ANALYSIS IN EXCEL

THREE WAYS TO DO WHAT-IF ANALYSIS

We can perform WHAT IF analysis in the following three ways.

SCENARIO MANAGER-WHAT IF ANALYSIS

STEPS

•  CLICK WHAT IF ANALYSIS>SCENARIO MANAGER.

CLICK ADD.

In the above picture,

we have put the name of the scenario as FAST ( The higher speed is taken).

The CHANGING CELL is the cell where the value changes. For this example, we are changing the speed value from 80 to 60.

Click OK.

Following dialog box will appear.

After setting up the name of the scenario , in the next window, the Excel would ask for the value of the changing cell.

For the FAST scenario , we’ll set the value as 80 (kmph) or anything what you want.

CLICK OK.

AGAIN THE SCENARIO MANAGER WILL APPEAR.

 Again click ADD and add another scenario named as SLOW in the same way as FAST scenario was added.

CHANGING CELL will be same as the previous scenario i.e. G8 in our case.

The value will be set as 60 (kmph) when asked for the changing value.

SETTING UP FORMULA FOR SCENARIO MANAGER-WHAT IF ANALYSIS

After the scenarios are ready, we need to specify the formula. The formula specification can be done prior to scenario managing or after this. We will be finding out the distance. The distance=speed x time.The speed is varying from 80 to 60. Time is 2 hrs. Lets find out the distance in the two cases using what-if analysis

OPEN SCENARIO MANAGER.

CLICK SUMMARY.

FOLLOWING DIALOG BOX WILL OPEN.SELECT THE CELL WITH THE FINAL RESULT FORMULA.

IN OUR CASE IT IS THE CELL WITH THE DISTANCE FORMULA AND CLICK OK.

FOLLOWING SUMMARY WILL BE SHOWN.  The result cell captions will be changed manually.

GOAL SEEK- WHAT IF ANALYSIS

Goal Seek is used for trying a back formula. Suppose , we have some output bound in a formula and we need to know what value should be used to get a particular result, in that case we use GOAL SEEK.

EXAMPLE:

Lets try the formula of simple interest.

Principle amount is = $10000

Duration = 4 years

We’ll find out the rate of interest in which the interest comes out to be $1000.

STEPS:

• Enter the details as shown. The interest rate is to be calculated.
• APPLY THE formula in Interest as principle*rate*duration/100

Click the WHAT-IF ANALYSIS>GOAL SEEK

SET VALUE: ENTER THE CELL , WHERE YOU WANT TO CHANGE THE VALUE (THE VALUE WHICH YOU WANT EXCEL TO FIND).

TO VALUE= TARGET VALUE

BY CHANGING CELL=THE VARIABLE CELL, THE VALUE WHICH NEEDS TO BE CHANGED. THE VALUE TO BE FOUND.

CLICK OK.

DATA TABLE-WHAT IF ANALYSIS-WITH SINGLE VARIABLE

DATA TABLE gives the facility to change one variable and get all the outputs. ssuppose we have to calculate the interest for different rates, we can do that by putting all values one by one or by this option, we can calculate all the values altogether.

EXAMPLE:

DATA: SUPPOSE we have to calculate the interest for different interest rates.

The formula is entered in the diagonally located cell right side of the column of interest rates for which the interest is to be calculated.

If the interest rates are to be put in the row, the formula is put in the diagonally left cell upper left corner.

STEPS:

WHEN THE INTEREST RATES TO BE CHECKED ARE IN THE COLUMN

• SELECT THE COLUMN OF THE INTEREST RATES+THE FORMULA CELL+THE ADJACENT COLUMN OF THE INTEREST RATES, WHERE THE OUTPUT WILL BE CALCULATED.
• GO TO WHAT IF ANALYSIS>DATA TABLE.
• A DIALOG BOX WILL OPEN.

In the dialog box, enter the variable cell, here we want to change the interest rate, so we will choose RATE CELL and we’ll put it in column input because our interest rates are put in the column. Click OK. The result is shown in the picture.

DATA TABLE-WHAT IF ANALYSIS-WITH TWO VARIABLES

DATA TABLE gives us the facility to change one variable and get all the outputs when other variables are fixed. In the previous section of the same post, the DATA TABLE with the single variable has been discussed. If we take the same example as mentioned earlier, suppose we have to calculate the interest for different rates,and this time the duration also changes, we can do that by this option, we can calculate all the values altogether.

EXAMPLE:

DATA: SUPPOSE we have to calculate the interest for different interest rates.

SUPPOSE in the same case we vary the duration also, in that case we’ll take interest in the column and duration in the row as following format.

Select DATA>DATA TOOLS>WHAT-IF ANALYSIS>DATA TABLE

Select DATA>DATA TOOLS>WHAT-IF ANALYSIS>DATA TABLE

Following DIALOG BOX will open. Enter the range of variables in the respective fields and click OK.