Table of Contents
- INTRODUCTION
- WHERE IS BUTTON LOCATION FOR WHAT-IF ANALYSIS IN EXCEL
- DIFFERENT WAYS TO DO WHAT-IF ANALYSIS IN EXCEL
- THREE WAYS TO DO WHAT-IF ANALYSIS
- HOW TO USE SCENARIO MANAGER – WHAT IF ANALYSIS?
- SETTING UP FORMULA FOR SCENARIO MANAGER-WHAT IF ANALYSIS
- GOAL SEEK- WHAT IF ANALYSIS
- DATA TABLE-WHAT IF ANALYSIS-WITH SINGLE VARIABLE
- DATA TABLE-WHAT IF ANALYSIS-WITH TWO VARIABLES
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.
DIFFERENT WAYS TO DO WHAT-IF ANALYSIS IN EXCEL
There are three ways to use WHAT – IF ANALYSIS in Excel.
- SCENARIO MANAGER
- GOAL SEEK
- 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.
- Click ADD.
- It’ll open the window asking for the scenario details.
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.
- 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.
- The following settings show the scenario SLOW.
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
- 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 [SPEED and DISTANCE ] will be changed manually. Before editing, the result cells will be the address of changing cell and result cells.
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
- 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.
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.
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 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
- 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.
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.
So, this was the detailed steps to use WHAT IF – SCENARIO MANAGER, GOAL SEEK OPTION AND DATA TABLE in EXCEL.