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
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 What–If Analysis option and Select Scenario manager.
- Then, the scenario manager pop-up window will appear.
- Click on the Add button here.
- 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
- 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
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.
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 double–click on the scenario name to show it in the worksheet.
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
- 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
- After that, a new worksheet will be created aside from the main worksheet. Here, the summary of all scenarios is stored.
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.
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.
- 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.
- 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.
Read More: How to Do What-If Analysis Using Goal Seek in Excel
Similar Readings
- How to Create One Variable Data Table Using What If Analysis
- How to Delete What If Analysis in Excel (2 Simple Methods)
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.
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.
- 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
- A new window named Data Table will appear.
- Enter the input cell reference C5 in the Column input cell
- Finally, press OK.
- As a result, you will get the column filled with the EMI value respective to the interest rate
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
- A new window named Data Table will appear.
- Enter the input cell reference C6 in the Row input cell
- Finally, press OK.
- As a result, you will get the column filled with the EMI value respective to the no of terms.
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.
- 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
- 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.
- As a result, you will get the column filled with the EMI value respective to the NPER terms and Interest Rates.
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.
