how to use what if analysis in excel

How to use what if analysis in excel?

Normally we found this feature in ms excel and this is a very helpful feature for us. And by using this we can use various values and can explore several results.What if analysis in excel gives us three option –

1. Data table

2.Scenario manager

3.Goal seek

What if analysis in excel

As I mentioned earlier what if analysis in excel provides us 3 main features and here we will discuss all these topics one by one.

what if analysis in excel scenario manager

Scenario actually means continuity or part of a picture. Excel gives an opportunity to watch various scenarios.

Actually, this tells us that if we made some changes in our data then what the results look like.

In simple words, scenario manager is the best method to compare one or two versions of data. We can save one or more than one scenario s in one worksheet and can compare according to our wish.

ABC private limited
November
Plastic10000
Power1000
Labour500
Total cost11500

In the uppermost section, we have created a table and we mention the product price. But if anyhow the product price change in next month then the total cost will not change ,to overcome this situation we use scenario manager.

  • To create scenario just go to to data tab – what if analysis option – scenario manager – a box will open – click on the add
  • Another box will open- at the beginning there is scenario name option ,and where we give a name that is minimum
  • Below that another option is given changing cells where we have to select the three cells of plastic,powder, and labour and click on the ok button.
  • In the next step we have to enter values according to your wish that will change according to circumstances. In plastic section we enter 9500 , In power we enter 950 ,in labour we enter 450.Now press the add button.

Now we will prepare our next scenario that is maximum:

In a similar process in scenario name, we enter name maximum.

Changing cells will automatically be selected, now press ok.

Now in the dialogue box enter the maximum value of products. In plastic, we enter 11000, in power we enter 1100, and labor 600 and press ok.

Two scenarios will prepare infront of you and just click on summary and select the result cells that are actually the total cost cell, press ok.

The scenario will be displayed infront of you, where the current value will be 11500, the minimum value will be 10900, and the maximum value will 13300.

Example 2:

In our 2nd example besides these examples we also want to display our previous month’s data.

ABC private limited

October
plastic9800
power1050
labour450
Total cost11300
  1. go to data tab- what if analysis option-scenario manager- add.
  2. Here we provide the nameof scenario as last month and choose changing cells and select 3 cells and press ok.
  3. In next step wenot change any values because this is actual values of october month and press ok
  4. To merge the octobar month data into november month we have to go sheet 1 and go to what if analysis- scenario manager – merge – select sheet -ok summary -ok.

what if analysis in excel goal seek

Goal seek is the most helpful feature in excel.We will discuss goal seek with some examples. Goal seeks actually works in reverse order. In goal seek there are 3 options -set cell, to value, by changing cell.

Example 1:

Player nameruns
A66
B33
c
D5
E70
F70
Total244

In this example we give some players name and their runs, now we want to achieve 300 runs by increasing the runs of player c, we can easily do that with the help of goal seek

1. Go to goal seek option

2. A dialogue box will open where enter data.

Set cell:

The set cell actually means where we enter formulas, where we give output, here we select 244 cells.

To value:

Here in this section, you have to enter, what you actually want, which means what is your target. Here in the c cell, our target is 300. So we enter that.

By changing cell:

This section means to achieve your target which cells data do you want to change. Here we select c cells runs data. And now we press ok.

The result will like this:

Player nameRuns
A66
B33
C56
D5
E70
F70
Total300

Example 2:

NameenglishmathsscienceSocial sciencegeographyaverageexpectations
Ram80887586
82.2585
Mohan90989488
92.586
Sohan94979092
93.2595

In the table we mention some marks of students .And ammount of marks Ram need to get his average in 82.25 . With the help of goal seek we can do that easily.

1. Data tab – what if analysis – goal seek .

2. From the dialogue box

set cell:

Select the average cell of RAM

To value:

Here we will give our expectation value, here we give 85 as our expectation.

By changing cell:

Which cell do you want to change, here we select the geography cell of RAM?

Press the ok button and the result will display.

NameEnglishmathsScienceSocial sciencegeographyAverageexpectation
Ram80887586968585
Mohan






Sohan






In this similar method, we can find other twos data also.

what if analysis in excel data table

We already learn goal seek which normally works with one cell and scenario manager actually works with one cell and scenario manager actually works with multiple cells.

The data table is the mixture of both the things goal seeks as well as scenario manager. It also works in multiple cells and we can use many formulas here.

Example 1:

Principle100000months24ROI8%

Suppose we take a loan of 100000 rupees from a bank and we have to give 8 % interest then what will our EMI per month . We know that we can use the PMT formula and bring our EMI easily.

EMI= PMT(interest/12 month,24 month, – present value)

EMI4522.73

1st year2nd year3rd year4th year5th year
4522.731224364860
100000




125000




150000




175000




200000




Here we create a table and we want to know what is the interest of money and what will the EMI of that money in separate years.

But in the first cell, we enter the previous table EMI. It is mandatory because whenever we create any data table at that moment in the first cell we have to enter some formula of a cell.

1. First select the whole table and data tab – what if analysis – data table.

2. A box will open infront of us; there are 2 sections on that dialogue box. First we have to enter row input cells ,here we enter 24 cell address that we created in previous table. And in column input cells we give cell address 10000 cells no b and press the ok button.

Data table will present infront of us:


1st year2nd year3rd year4th year5th year
4522.731224364860
1000008698.84522.73133.62441.32027.6
12500010873.65653.43917.03051.62534.5
15000013048.36784.14700.53661.93041.5
17500015223.07914.85483.94272.33548.4
20000017397.79045.56267.34882.64053.3

example 2:

A B

1R



1c




12345
1




2




3




4




5




In the upper table we want to print the tables how we will do that with the help of first we enter 1 and 1 in put the formula the result will come, now just select the cells and go to data table option.

Data table dialogue box will open where in the first cell we give a cell address and in the second cell we select column and drag towards the end, data table will displayed.

A B

1R



1c




12345
112345
2246810
33691215
448121620
5510152025

Conclusion:

This article is about the what if analysis in excel, to read this kind of informational article please follow our website.