# What is what if analysis how it is useful?

Digital Marketing CourseData Science CourseWhat is What-If-Analysis in Excel and how is it usedWhat-if-analysis in Excel is a tool in Excel that helps you run reverse calculations,

- Digital Marketing Course
- Data Science Course

## What is What-If-Analysis in Excel and how is it used

What-if-analysis in Excel is a tool in Excel that helps you run reverse calculations, sensitivity analysis and scenarios comparison.

Decision making is a crucial part of any business or job role. When you can take decisions, which are informed based on data, the outcome of the business or project or task is always more in control.

Thus, What if Excel is used by almost every data analyst and especially middle to higher management professionals, to make better, faster and more accurate decisions based on data.

### 3 parts of what-if-analysis in Excel

Table of Contents

- 3 parts of what-if-analysis in Excel
- Goal Seek in What if analysis
- Data Table in What-If analysis
- 1-input Data Table
- 2-input Data Table
- Scenario Manager in what if analysis
- Compare the scenarios
- Conclusion
- Goal Seek Reverse calculations
- Data Table Sensitivity analysis
- Scenario Manager Comparison of scenarios

### Goal Seek in What if analysis

Lets consider a simple dataset, where the invoice amount is Rs. 10,000, on which there is 9% CGST and 9% SGST, which thus amounts to a total of Rs. 11,800.

The customer asks you for a discount of Rs. 800 and thus the final amount should be Rs. 11,000.Want to Know the Path to Become a**Data Science Expert**?Download Detailed Brochure and Get Complimentary access to Live Online Demo Class with Industry ExpertDate: 03rd Sep, 2022 (Saturday) Time: 11:00 AM to 12:00 PM (IST/GMT +5:30)(Do provide your contact details, download link will be emailed to you in few seconds)

- Name *
- Email *
- Phone *
- Producthome
- Product
- Further Communication
- source
- medium
- term
- content
- campaign
- gclid
- csegment
- Country
- Dates
- keyword
- dmoday
- dmo-date
- dmo-time
- City
- clientid
- Register me
- Register me for FREE Orientation Session
- Course *
- Send me course curriculum as well
- Term and condition *
- I agree to Digital Vidya Privacy Policy & Terms of Use.
- PhoneDownload Now {{#message}}{{{message}}}{{/message}} {{^message}} {{/message}}One or more fields have an error. Please enter required fields and try again.{{#errors}}{{error_label}} : {{error_detail}}{{/errors}}

By clicking the above button, you agree to our terms and conditions and our privacy policy.

Now, the equation in simple terms is, X + 18% = 11000, where X is the invoice amount, 18% is the total GST.

To find out, how much + 18% = 11000, we will use Goal Seek in What if analysis.

- Place your cursor on the Total cell
- Under the Data tab, click on What-If-Analysis, then on Goal Seek

- In Set Cell, B4 will automatically be selected as you had kept your cursor on it.
- In To value, enter the desired value, 11000 in this case.
- In By changing cell, choose the value that needs to be changed, invoice amount in this case. Thus cell B1 is selected.
- Press Ok

Excel will reverse calculate and immediately give you the value Rs. 9,322, which + 18% equals exactly to Rs. 11,000

This was a very simple example of using Goal Seek in what-if analysis. You can use Goal Seek even for more complex models, lets take an example of a Car loan model.

The EMI calculated Rs. 19,786 is the outgoing amount per month. The value is negative as money is going out of your pocket.

But, you have a budget of only Rs. 17,000 per month. So, how much can you afford as Price of Car?

Put the Goal Seek values as above and you will know the Price of Car that you can afford.

This was calculations at multiple levels that Goal Seek in What-if analysis did, as it had to consider Available funds, ROI, Number of payments to reverse calculate and give you the answer.

Thats how powerful it is.

### Data Table in What-If analysis

Data Table is used for Sensitivity analysis. What this means is basically, either 1 or 2 of the inputs in your model are changing, you want to know output based on each change.

Lets take the same Car loan example as earlier.

Now, after applying the Goal Seek, you know you can afford to buy a car worth Rs. 7,15,526 instead of Rs. 8,00,000.

#### 1-input Data Table

Then you go to the Car showrooms and research on more cars available. You find out 5 cars that you like, you want to know what would be the EMI amount for each of the car?

Car 1 Rs. 5,54,000

Car 2 Rs. 5,96,000

Car 3 Rs. 6,24,000

Car 4 Rs. 7,36,000

Car 5 Rs. 7,94,000

Use what if analysis data table to find this.

Since only 1 input is changing, that is, Price of Car, we will use 1-input data table.

Make this structure in your Excel sheet next to your model.

In D3, you can write anything you want, doesnt matter.

Next to that, in E4, put =B9. Basically, you are pointing to the formula that is used to calculate the EMI. Thus, here you have informed Excel that you want to calculate the resulting EMI for each value, using the formula in B9.

Now select this structure you have created and go to Data table under what-if analysis in Data tab.

Since our options of Prices of Cars are put vertically in a column, we will use Column input cell. Select cell B1 to inform Excel that the 5 values are Price of Car values.

Press OK

Excel has calculated for you, the EMI for each change in Price of Car.

#### 2-input Data Table

Similarly, you can have 2 inputs varying and still get the respective outputs.

So now you think about what if I change the duration of the loan, and compare for all these 5 cars?

Go to Data Table and select Row input cell as No. of payments in months and Column input cell as Price of car

You will get the EMI amount for each combination in no time, without much effort or any complicated formulas.

### Scenario Manager in what if analysis

Lets say you are working in a Car Showroom in the Sales department. You have been given the task to plan the sales for the next quarter. You must build multiple scenarios and prepare a comparison of all the scenarios.

You make a model as below and then want to create multiple scenarios based on number of cars that you will be able to sell for each of the cars.

Under What-if analysis, go to scenario manager.

Click on Add

Lets start building our 1st scenario

- Scenario Name Best Case
- Changing Cells select cells C2:C6 as these are the No. of cars that you will be able to sell, basically the variable cells
- Press OK
- Enter values for each Car

I have entered values as above, you can enter whatever you like.

Similarly add 1 more Scenario and name it as Worst Case. The changing cells will ofcourse remain the same.

I have put in the below values for Worst case.

You can create many more scenarios like this.

#### Compare the scenarios

Now that your scenarios are created, lets compare them.

In the Scenario Manager window, click on Summary.

You will now be asked for Result cells. Choose the Total Sales Value, cell D8, as thats what you want to compare. If you want to compare more outputs, you can choose multiple cells here too.

A new Sheet will be created automatically on pressing OK which will give you a comparison of the Current values in your Sheet + the 2 scenarios you created.

Thus, in best case, the Total Sales is over Rs. 6 CR. Worst Case is 3.77 CR.

Now you can take your business decisions based on this output.

### Conclusion

Thus, we can conclude that what-if analysis is an integral part of the tools any data analyst or middle to senior management uses. Using the 3 tools in what if, you can analyze data much quickly than if you try to do the same using formulas, thereby allowing you to take faster and accurate decisions.

- Goal seek is for Reverse calculations.
- Data Table is for 1 or 2 inputs changing, resulting changes in output.
- Scenario Manager is to compare multiple business scenarios based on multiple inputs changing.

Many features in different versions of excel work differently, but what if analysis in excel 2010 works same way as what if analysis in excel 2013 and what if analysis in 2007 or 2016.

Take up the Data Analytics using Excel Course to become a proficient Data Analyst.

### Attend Online Demo Class(Do provide your contact details, download link will be emailed to you in few seconds)

- Which Program are you interested in?*
- Name *
- Email *
- Phone *
- Product
- Product
- Further Communication
- source
- medium
- term
- content
- campaign
- gclid
- csegment
- Country
- Dates
- keyword
- dmoday
- dmo-date
- dmo-time
- City
- state
- clientid
- Register me
- Register me for FREE Orientation Session
- Course *
- Send me course curriculum as well
- Term and condition *
- I agree to Digital Vidya Privacy Policy & Terms of Use.
- Receive Updates on Whatsapp
- CTA
- CommentsRegister Now {{#message}}{{{message}}}{{/message}} {{^message}} {{/message}}One or more fields have an error. Please enter required fields and try again.{{#errors}}{{error_label}} : {{error_detail}}{{/errors}}Leave a CommentPublished byRushabh ShahFebruary 23, 2018All Rights ReservedView Non-AMP Version
- Call Us
- WhatsApp Chat
- Join Demo