PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES

Contents

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 simulataneoulsy. 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

DIFFERENT WAYS TO DO WHAT-IF ANALYSIS IN EXCEL

THREE WAYS TO DO WHAT-IF ANALYSIS

SCENARIO MANAGER-WHAT IF ANALYSIS

  • STEPS
  •  CLICK WHAT IF ANALYSIS>SCENARIO MANAGER.
WHAT IF ANALYSIS SCENARIO MANAGER
SCENARIO MANAGER DIALOG BOX

CLICK ADD.

SCENARIO MANAGER WHAT IF ANALYSIS
SCENARIO MANAGER DIALOG BOX

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.

 

scenario manager changing cell what if analysis
Enter values of changing cell

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.

what if analysis scenario manager
FULL LIST OF SCENARIOS IS PRESENTED HERE

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

scenario manager what if excel
ENTER SLOW SCENARIO

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
what if analysis formula excel
FORMULA FOR DISTANCE (DISTANCE=SPEEDXTIME)
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.
FORMULA WHAT IF ANALYSIS
FORMULA FOR DISTANCE

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

RESULT WHAT IF ANALYSIS
RESULT OF WHAT IF ANALYSIS-SCENARIO MANAGER

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

goal seek what if analysis

Click the WHAT-IF ANALYSIS>GOAL SEEK

goal seek what if analysis excel

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.

goal seek excel

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.

data table what if excel
EXAMPLE

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.
 
DATA TABLE INPUT BOX EXCEL
DATATABLE EXCEL

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 excel
RESULT-The figure shows that if interest is 0.25 pc, interest is 1000, when it is 4.5 amount of interest is 18000 and so on.

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

data table with two variables
PUT the two variables, rate in column and duration in row and click ok.

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.

data table two variables excel
Enter the row input cell and Column input cell
DATA TABLE WITH DOUBLE VARIABLE EXCEL
THE RESULT SHOWS THE INTEREST PAID AT DIFFERENT RATES FOR DIFFERENT DURATIONS.

Pin It on Pinterest

Share This

KINDLY SPEND FEW SECONDS HERE....

*CLICK ANYWHERE ELSE TO CLOSE THIS WINDOW
%d bloggers like this: