How to Use What If Analysis in Excel (with All 3 Features)

If you are searching for the solution or some special tricks to use What if analysis in Excel then you have landed in the right place. There are 3 different and very useful features in the  What If analysis option in Excel. This article will show you how to use all these 3 features including the Scenario manager, Goal Seek feature, and Data Table feature of what if analysis in Excel with proper illustrations so you can easily apply them for your purpose. Let’s get into the central part of the article.


Download Practice Workbook

You can download the practice workbook from here:


3 Ways to Use What If Analysis in Excel

You will find 3 different and very useful features named Scenario manager, Goal Seek feature, and Data Table feature in the What if analysis option in Excel. In this article, I will show you how to use all three features of What if Analysis in Excel with examples. on Windows operating system. You will find detailed explanations with clear illustrations of each thing in this article. I have used Microsoft 365 version here. But you can use any other versions as of your availability. If anything of this article doesn’t work in your version then leave us a comment.


1. Doing What If Analysis Using Scenario Manager Option

The scenario manager helps to compare the output results for various different inputs. For example, to calculate EMI for a loan, there are 3 parameters that are Loan Amount, Interest Rate, and Number of Monthly Payments. So you can change all of them to match your required EMI per month. And the bank or financial institute may offer you a certain different proposal. To compare the proposals, you can use the Scenario manager tool. Here, you get the EMI result one by one also, you can compare them side by side. Now, I will show you how you can use Scenario manager for comparing EMI calculations.

Step 1: Make a Dataset of the First Scenario

First, you have to make a dataset to enter the amount, Create input cells for the Loan Amount, Interest Rate, and Number of Monthly Payments and type. Then, use the PMT function to calculate the EMI per month. For this Paste the following formula into cell C8:

=PMT(C5/12,C6,C4,0,0)

🔎 Formula Breakdown:

Syntax =PMT(rate, nper, PV, [fv], [type])

  • Rate = C5/12: D5 represents the annual interest rate of 8%, we divide it by 12 to adjust it for one month.
  • NPER = C6 = 60: for 5 years 5*12=60
  • PV= C4 = 2000000: the present value is the total loan amount

How to Use What If Analysis in Excel - EMI Calculation Example


Step 2: Create First Scenario

Now, you can create new scenarios. To create new scenarios, follow the below steps:

  • First, select the cells that affect the output EMI. So, you have to select the cell of range C4:C7.
  • Then, go to the Home
  • Click on the WhatIf Analysis option and Select Scenario manager.

Create New Scenario for EMI Calculations in What if analysis

  • Then, the scenario manager pop-up window will appear.
  • Click on the Add button here.

Scenario Manager Window

  • Then, another new window will appear named “Add Scenario”.
  • Now, give the name of the Scenario.
  • After that, you will find that the Changing cells box is already filled with the selected cells range.
  • Check whether the prevent changes box is marked or not.
  • Then, press

Add New Scenario using What If Analysis in Excel

  • Then, another pop-up window will appear and will ask you to give values of the parameter for the new Scenario.
  • Insert the values in the respective cells and press OK to save.
  • If you want to add another scenario then click Add

Scenario Values Window


Step 3: Create More Scenarios

After clicking the OK button, it will take you to the Scenario manager window. Now, if you want to add another scenario then, click on the Add button here.

Then, you have to follow the same steps to create another scenario as mentioned before.

Create More Scenarios from scenario manager


Step 4: Switch to Another Scenario

After adding several scenarios, you may want to switch from one to another. You can do it very easily.

  • In the Scenario Manager window, select the scenario that you want to show and click on the Show button below.
  • Alternatively, you can simply doubleclick on the scenario name to show it in the worksheet.

Switch to Another Scenario from Scenario manager


Step 5: Create Scenario Summary

Also, you may want to show all the scenarios aside to make comparisons. For this,

  • you have to click on the Summary button in the Scenario Manager

Create Scenario Summary from scenario manager

  • Then, a pop-up window will appear which will ask you to select the result cell. For our case, the result cell will be C8 which is giving the monthly EMI

Create Scenario Summary for EMI Calculation

  • After that, a new worksheet will be created aside from the main worksheet. Here, the summary of all scenarios is stored.

Scenario Summary in Excel

You can use the scenario manager tools for various purposes to compare multiple sets of data. I have shown here a simple example of doing EMI calculations using the Scenario manager tool of What if analysis.

Read More: How to Do What-If Analysis Using Scenario Manager in Excel


2. Performing What If Analysis Using Goal Seek Feature

Goal Seek is another magical tool of the What-If analysis option. Often, we need to do back calculations to get a value. For this, we have to change the formula but while dealing with a complex formula, it becomes tough to do back calculations. And for this, Excel brings you the Goal Seek feature. You can use this feature to get an unknown input value for a known output. Follow the below steps to use the Goal Seek feature in Excel.


Step 1: Create Dataset

First, you have to create a dataset. In this article, I will show you an example dataset where sales of 5 products, profit percentage, and revenue coming from each product are shown. And, data of four products are already inserted which gives the total revenue of $6293.85 but your goal is to earn a revenue of amount $8000. For this, you want to know, how many sales you should have done for the product 5. Here, you use the Goal Seek feature to get the required sale amount for product 5 to achieve the target revenue of $8000.

Performing What If Analysis Using Goal Seek Feature - Dataset


Step 2: Go to Goal Seek Feature

After creating the dataset, you can use the Goal Seek feature to get the unknown value to achieve the target. For this-

  • Go to the Data
  • Then, click on the What if Analysis
  • Select the Goal Seek feature here.

Go to Goal Seek Feature in What if Analysis Option

  • Then, a new window will appear named “Goal Seek”.
  • Here, select cell E10 as the Set Cell box which is the output.
  • Then insert the target amount 8000 in the To Value
  • After that, select cell C9 as the Changing cell.
  • Finally, press OK.

Using Goal Seek Option for Profit Goal Calculation

  • After clicking OK, it will take some time to do an iterative calculation. When it will get a perfect result, it will insert that value automatically into the cell.

Goal Seek Feature - Output

Read More: How to Do What-If Analysis Using Goal Seek in Excel


Similar Readings


3. Creating What If Analysis with Data Table Option

The What If Analysis Data Table feature is used to see the effect of changing one or more input variables on formula output. It is a very useful feature while taking decisions as you can see outputs of all different input variables in a place. Here, I am showing the use of the Data table feature using an example of EMI calculation. So, first, create a dataset to calculate the EMI per month as described in the Scenario Manager example before.

EMI Calculation using PMT Function


3.1  One-Variable in Column Input Cell

  • Here, first, create different interest rates in a column and assign cells aside to get the corresponding EMI value.

One-Variable Data Table for EMI Calculation

  • As our data table is Column-oriented, I have entered the formula in cell F6 to calculate EMI per month in the first row of the EMI column of the data table.

  • Then, select the data table including the cell that contains Present EMI.
  • After that, Go to the Data tab in the top Ribbon.
  • Then, click on the What If Analysis option and select Data Table

Apply Data Table Feature

  • A new window named Data Table will appear.
  • Enter the input cell reference C5 in the Column input cell
  • Finally, press OK.

Select Column Input Cell in One Variable Data Table

  • As a result, you will get the column filled with the EMI value respective to the interest rate

Output of One Variable Data Table


3.2 One-Variable in Row Input Cell

Now, you have to create a data table that is row-oriented. And insert the present EMI value with the formula in cell B13 which is the First column of the Term row. In this data table, you can calculate the EMI value for various Term values with a click. Then follow the below steps to complete the data table.

  • Then, select the data table including the cell that contains Present EMI.
  • After that, Go to the Data tab in the top Ribbon.
  • Then, click on the What If Analysis option and select Data Table

Select Data Table in the What if analysis option

  • A new window named Data Table will appear.
  • Enter the input cell reference C6 in the Row input cell
  • Finally, press OK.

Select Row Input Cell in One Variable Data Table

  • As a result, you will get the column filled with the EMI value respective to the no of terms.

One-Variable in the Row Input Cell for EMI Calculation


3.3 Two-Variable Data Table

Also, you can use the two-variable data table to show how the output is changing by the change of two variables with a formula by a click only. Let’s go through the steps so you can easily learn it.

  • First, make a data table where you have interest rates along the first column and No of payment terms along in the first row.

Two-Variable Data Table for EMI Calculation

  • Now, insert the present EMI value with the formula in cell F5 which is the cell first row and first column cell of the data table.

  • Then, select the data table including the cell that contains Present EMI.
  • After that, Go to the Data tab in the top Ribbon.
  • Then, click on the What If Analysis option and select Data Table

Select Data Table in What if Analysis Option

  • Now, a new window named Data Table will appear.
  • enter the input cell reference C6 in the Row input cell
  • Then, Enter the input cell reference C5 in the Column input cell
  • Finally, press OK.

Select Row and Column input cell for EMI Calculation

  • As a result, you will get the column filled with the EMI value respective to the NPER terms and Interest Rates.

Two-Variable Data Table for EMI Calculation

Read More: How to Perform the What If Analysis with Data Table in Excel


Things to Remember

  • Use the Scenario Manager feature when you have a certain number of datasets of an irregular pattern.
  • Use the Data Table feature when the input variables changes in a regular pattern.
  • Use the Goal Seek Feature to do a back calculation to find an input value using the output formula.

Conclusion

In this article, you have found how to use all these 3 features including the Scenario manager, Goal Seek feature, and Data Table feature of what-if analysis in Excel. I hope you found this article helpful. You can visit our website ExcelDemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

Osman Goni Ridwan

Osman Goni Ridwan

I am Ridwan, graduated from Naval Architecture and Marine Engineering Dept, BUET, currently residing in Dhaka, Bangladesh. And my passion is to grow up my skillsets with industry demands. My prime goal is to be a data analyst as I do love to solve problems and play with data.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo