What Is Excel What if Analysis and It's 3 Types

Excel What-if Analysis Scenario Manager 

The Scenario Manager in What-if Analysis Excel is used to consume the original data and the mathematical formulas implemented on the data to recreate another scenario that inherits similarities from the previous table and generates a new table.

For better learning, let us consider that you are running an IT company and you have employees from three different bands, namely:

• Band A 
• Band B
• Band C

Employees of different bands have different compensations, Basic Pay, and HRAs. The brute force method of excel would recommend creating three different tables with similar data and input the values. This time-consuming process can be eliminated by using Scenario manager. The whole idea of using Scenario manager is to avoid duplication of similar data. You can also generate the summary of all the three tables in one sheet in the form of a detailed visualization.

Take Your Data Scientist Skills to the Next Level

With the Data Scientist Master’s Program from IBMExplore Program

Let us execute an example. The following image depicts the details of Band C Employees. Cells B8, B16, and B18 include a mathematical formula that calculates the summation of the other cells above them and states them as "Compensation from Company", “Maintenance Bill Per Employee", and "Total Expense to Company", respectively.

Now, let us use this table and the Scenario manager from Excel What-if Analysis to recreate the same table for Band B and Band A employees. 

Select the cells consisting of numerical data and ignore the cells with mathematical formulas as shown below. 

Go to the Data option from the toolbar and select the Excel What-if Analysis option from the Forecast Ribbon, as shown below.

A dialog box will appear on the screen with empty scenarios. To add a scenario, select add option as shown below.

Then, a new dialog box will appear on the screen with cell addresses. Type a name for the Scenario and press OK. Here, we have chosen Band C as the name for this Scenario.

The next screen will show a small dialog box with all the values ready for Band C, as shown below.

Similarly, you can add two more scenarios: Band B and Band A in  this example. You can select the add option, and you will have a new dialog box with editable data options. Now, update the values for Band B and select OK as shown below.

And the last step is to create a scenario for Band A. To update values, follow the same procedure as before; click on add and edit the values in the new dialog box and press OK as shown below. 

Now that you have added all the values, you have three different scenarios, namely

Band A 

Band B

Band C

If you click on the summary option, you can create a whole summary as well. 

After clicking on the summary, you will find a new dialog box. Select Scenario Summary and press OK. 

Then, in a new sheet, you will have the complete detailed summary of all three tables. The first column is entirely editable, and the cell addresses can be named with row names in the original data.

In the next stage of this Excel What-if Analysis article, we shall learn about Goal Seek in Excel.

Take Your Data Scientist Skills to the Next Level

With the Data Scientist Master’s Program from IBMExplore Program

Excel What-if Analysis Goal Seek in Excel

In the Excel What-if Analysis tab, we have an option named Goal Seek in Excel. This option helps the user run all possible permutations and combinations to generate or achieve a target in the resultant cell.

To understand it elaborately, let us consider an example as follows. Here we have a set of students, their marks list, and their final aggregate. 

As you can see, a few students do not have the aggregate as 60% or above. To score 60% or above, the candidates need to write an improvement exam and score certain marks to achieve the 60% or above target.

We can use Excel What-if Analysis and employ Goal Seek in the excel method to predict the required marks to achieve 60% or above as the target aggregate. To implement goal seek in excel, you need to select the cell from the aggregate column as shown below.

Now, click on the data option from the toolbar and go to the forecast ribbon and click on the Excel what-if analysis button. A small window will appear, and you must choose the second option that reads Goal Seek, as shown below.

A small window will appear on the screen, and here, you need to specify the location of the target cell, the target value, and the cell you wish to change. Here, your target cell is M4, the target value is 60, and since you would like to write an improvement exam for subject Engg 4, the cell address will be I4.

After entering the values, select OK, and Excel will automatically run all permutations and combinations and provide the result. In this case, the outcome or the result provided is the target score you need to achieve in the "Engg 4" subject to score 60% aggregate.

In the next stage of Excel What-if Analysis, we will learn about Data Table.

Take Your Data Scientist Skills to the Next Level

With the Data Scientist Master’s Program from IBMExplore Program

Excel What-if Analysis Data Table

Data Table in Excel What-if Analysis eases the challenging task of calculating fields and storing results in cells with just a simple drag and drop operation. A simple sentence can be complicated to understand, so let us go through an example.

Let us consider that you are borrowing a car loan from a bank. Your loan amount is two lakh rupees, and the Rate of Interest is 10% with 25 months of tenure.

Now, you wish to calculate and see the different EMI rates for different rates of interest.

So, let us calculate the following.

• EMI
• Total Amount
• Interest Amount

To calculate the above-mentioned fields, we require the following respective formulas as shown below.

EMI

Total Amount

Interest Amount

The final table will appear as follows.

The main problem statement is to find out the EMI for different rates of interest.

Now, create a new column with all the rates of interest you wish to look for. Followed by that, write the cell address of the EMI in your table in the next column. In this case, it is B7. The table should appear something like below.

Now, select the entire table, click on the Excel What-if Analysis, and select the data table option. 

A new window will pop up on the screen. In the dialog box, provide the rate of the interest cell address. In this case, the cell address is B3. The image will appear as follows.

Now, select OK, and the final data will appear as shown below.

This procedure of Excel What-if Analysis is called a single input data table procedure. There is another procedure in Excel What-if Analysis, which is the double input data table. We will learn how to do it.

Take Your Data Scientist Skills to the Next Level

With the Data Scientist Master’s Program from IBMExplore Program

Let us imagine that you are interested in learning EMI values for different Loan Amounts and different Rates of interest. You can do that by filling the different interest rates as the row data and the various loan amounts as the column data, as shown below.

Look at the first cell. The G11, in this case, stores the data rupees 8895.42; this value is borrowed from the cell location B7 l=just like the previous example.

Now, select the entire set of rows and columns, as shown above, then select the excel what-if analysis, and click on the data table. 

The following dialog box will appear. Provide the inputs. You need to give the cell address of the Rate of interest and Loan Amount in  this case. So, your cell addresses are B3 and B7. 

Select OK, and the final data table with auto-filled data will appear as follows.

With that, we come to the end of the article. We hope you found the article informative and helpful.

Next Steps

Data Cleaning happens to be a necessary step before the analytics process as the data might include some falsified information or black spaces that could generate unexpected results in the end report. Such unexpected events can be avoided by employing the excel data cleaning methods.

Are you interested in exploring and gaining more knowledge about Business Analytics with Microsoft Excel and receiving online training and certification?

Then feel free to check out the Business Analytics certification course offered by Simplilearn. The business analytics course from Simplilearn is a career-oriented training and certification program. The training focuses on the fundamental concepts of data analytics and statistics. You will be able to extract insights from data to demonstrate your report using professional-level dashboards, and you can come up with data-driven decision-making.

Got any questions for us? If you have any questions or queries related to this tutorial or our certification course, let us know in the comments section of this tutorial, and our experts will be happy to answer.