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

Table of Contents

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
Plastic	10000
Power	1000
Labour	500
Total cost	11500

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 2^nd example besides these examples we also want to display our previous month’s data.

ABC private limited

October
plastic	9800
power	1050
labour	450
Total cost	11300

go to data tab- what if analysis option-scenario manager- add.
Here we provide the nameof scenario as last month and choose changing cells and select 3 cells and press ok.
In next step wenot change any values because this is actual values of october month and press ok
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 name	runs
A	66
B	33
c
D	5
E	70
F	70
Total	244

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 name	Runs
A	66
B	33
C	56
D	5
E	70
F	70
Total	300

Example 2:

Name	english	maths	science	Social science	geography	average	expectations
Ram	80	88	75	86		82.25	85
Mohan	90	98	94	88		92.5	86
Sohan	94	97	90	92		93.25	95

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.

Name	English	maths	Science	Social science	geography	Average	expectation
Ram	80	88	75	86	96	85	85
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:

Principle

100000

months

ROI

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)

EMI

4522.73

	1^st year	2^nd year	3^rd year	4^th year	5^th year
4522.73	12	24	36	48	60
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:

	1^st year	2^nd year	3^rd year	4^th year	5^th year
4522.73	12	24	36	48	60
100000	8698.8	4522.7	3133.6	2441.3	2027.6
125000	10873.6	5653.4	3917.0	3051.6	2534.5
150000	13048.3	6784.1	4700.5	3661.9	3041.5
175000	15223.0	7914.8	5483.9	4272.3	3548.4
200000	17397.7	9045.5	6267.3	4882.6	4053.3

example 2:

A B

1	R
1	c
	1	2	3	4	5
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