HOW TO DO WHAT-IF ANALYSIS IN EXCEL?

Table of Contents

INTRODUCTION

Many features in the Excel are very interesting and unique. [ Unique, not any more as most of the required features are found in all the spreadsheet apps ].

One such feature is WHAT-IF analysis.

What-If analysis is a “Condition based Result” kind of solution offered by Excel.

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.

In this article, we’ll learn about the usage of WHAT IF analysis in Excel.

WHERE IS BUTTON LOCATION FOR WHAT-IF ANALYSIS IN EXCEL

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

BUTTON LOCATION FOR WHAT-IF ANALYSIS

DIFFERENT WAYS TO DO WHAT-IF ANALYSIS IN EXCEL

There are three ways to use WHAT – IF ANALYSIS in Excel.

  1. SCENARIO MANAGER
  2. GOAL SEEK
  3. DATA TABLE

We’ll discuss each one of them one by one.

Let us learn the different ways of using What-If analysis.

THREE WAYS TO DO WHAT-IF ANALYSIS

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

1. SCENARIO MANAGER : WORKS WITH A SCENARIO WITH CONDITIONS

2. GOAL SEEK :FINDS OUT THE INPUT VALUE WITH THE HELP OF OUTPUT SOUGHT.

3. DATA TABLE : TO CHECK THE OUTPUT WHEN ONE OR TWO VARIABLES ARE CHANGED.



HOW TO USE SCENARIO MANAGER – WHAT IF ANALYSIS?

SCENARIO MANAGER provides us the option of putting different scenarios’ data on test.

The test will show us the different results on the basis of different situations simultaneously.

WHAT IS A SCENARIO MANAGER IN EXCEL?

SCENARIO MANAGER is a component of the WHAT-IF ANALYSIS.

SCENARIO MANAGER lets us create various scenarios in which we vary the values or a single value . After putting all the scenarios, we can ask EXCEL to provide a summary and with just one click it’ll create a resulting table showing all the possible outcomes [Scenarios].

In this way , we can analyze and decide accordingly.

We can understand it clearly as we take a simple example.



EXAMPLE: USE SCENARIO MANAGER TO SHOW THE DIFFERENT TIME TAKEN BY A CAR AT DIFFERENT SPEEDS

In this example, we’ll find out the simultaneous result for a simple calculation where we’ll calculate the time taken by a car at two different speeds in the given time.

We’ll apply the formula for the DISTANCE , which is

TRAVELED BY A CAR = SPEED OF THE CAR x TIME TAKEN BY THE CAR

Time is constant at 2 Hrs.

STEPS:

  • Go to DATA TAB. 
  • CLICK WHAT IF ANALYSIS>SCENARIO MANAGER.
  • The SCENARIO MANAGER window will open.
SCENARIO MANAGER DIALOG BOX
  • Click ADD.
  • It’ll open the window asking for the scenario details.
ADDING A SCENARIO

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 i.e. G8 where we put the speed value. For this example, we are changing the speed value from 80 to 60. [ Right now we are not putting the value, just choosing the cell where the value will change. ]
  • Click OK.

Following dialog box will appear.

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.

COMPLETE 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.
  • The following settings show the scenario SLOW.
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 formula is

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

FORMULA FOR DISTANCE (DISTANCE=SPEED X TIME)
  • 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 FOR DISTANCE

FOLLOWING SUMMARY WILL BE SHOWN.

The result cell captions [SPEED and DISTANCE ] will be changed manually. Before editing, the result cells will be the address of changing cell and result cells.

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.

GOAL SEEK IN WHAT IF ANALYSIS HELP US TO GET THE VARIABLE VALUE ON THE BASIS OF THE RESULT.

EXAMPLE:

Lets try the formula of simple interest.

Principle amount is = $100000

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

EXAMPLE SET UP
  • Click the WHAT-IF ANALYSIS>GOAL SEEK.
WHAT IF ANALYSIS DIALOG BOX

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

The result is calculated as 0.25 Percent, which is correct.

This is the way we can use GOAL SEEK in WHAT IF analysis.

DATA TABLE-WHAT IF ANALYSIS-WITH SINGLE VARIABLE

DATA TABLE gives the facility to change one variable and get all the outputs.

Suppose we have to calculate the interest for different rates, we can do that by putting all values one by one

or

using this option, we can calculate all the values altogether.

Let us learn DATA TABLE in WHAT IF ANALYSIS using the example.

EXAMPLE:

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

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.
DATATABLE IN 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.

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

  • 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 the following format.
  • Select DATA>DATA TOOLS>WHAT-IF ANALYSIS>DATA TABLE
PUT the two variables, the rate in the column and duration in a 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.

Enter the row input cell and Column input cell

As we click OK, the result will appear which will show a grid showing the different option results with different interest rates for different durations.

THE RESULT SHOWS THE INTEREST PAID AT DIFFERENT DURATIONS

So, this was the detailed steps to use WHAT IF – SCENARIO MANAGER, GOAL SEEK OPTION AND DATA TABLE in EXCEL.