What-If Analysis in Excel: Manual & Automatic Approaches

The main objective of this article is to explain What-If Analysis in Excel. What-If analysis is a tool that will help you to analyze different scenarios for formulas. In this article, I will explain the different uses of What-If Analysis in Excel.

Download Practice Workbook

What Is “What-If Analysis”?

The name “What-If Analysis”, explains its purpose. What-If Analysis tool helps one to try out different values in formulas and explore the difference in results. You will be able to change the input values to see how the output values change. There are 3 different tools in What-If Analysis.

1. Scenario Manager: Scenario Manager helps to create a report upon changing one or more values in the dataset. This report shows how the result will change upon changing an input value.
2. Goal Seek: The Goal Seek feature helps in a situation where someone knows the final value they want and needs to know how to change the input value to achieve the desired result.
3. Data Table: The Data Table feature table helps to create a table with different scenarios depending on the variable change.

What-If Analysis can also be done manually by simply changing the input values to observe how the result changes.

2 Ways to Perform What-If Analysis in Excel

I have taken the following dataset to explain this article. This dataset contains the calculation for Simple Interest. Here, the Principle Amount, Interest Rate, and Time are the variable. And, Final Amount and Simple Interest are calculated from these 3 variables.

In the following picture, you can see that cell C9 contains the formula for the Final Amount. And the result depends on the values in cells C5, C6, and C7. If you change any one of these values then the result will change accordingly.

Here, you can see that cell C10 contains the formula for Simple Interest and it depends on the values in cells C9 and C5. I will explain how you can use What-If Analysis to explore how the result changes upon changing the variables.

1. Performing Manual What-If Analysis in Excel

A manual What-If Analysis doesn’t require too much explanation. In fact, the example we have used in the previous article (A What-If example in Excel) demonstrates how it’s done. Manual What-If analysis is based on the idea that you have one or more input cells that affect one or more key formula cells. You change the value in the input cells and see what happens to the formula cells. You may want to print the results or save each scenario to a new workbook. The term scenario refers to a specific set of values in one or more input cells.

Here, I have the following dataset and I will show you how you can perform a manual What-If Analysis on it.

Let’s see the steps.

Steps:

• Firstly, Right-click on the sheet name that is at the bottom of the Excel sheet.
• Secondly, select Move or Copy.

Here, a dialog box will appear.

• Firstly, select (move to the end).
• Secondly, select Create a copy.
• Thirdly, select OK.

Now, you will get the exact same sheet as your dataset.

• After that, name the Sheet as Scenario 1.

• Now, change the value to see how the result changes. Here, I changed my Principal Amount to $16,000, and the Final Amount changed to $17,600 automatically.

Manual What-If Analysis is very common. People often use this technique without even realizing that they’re doing a type of What-If Analysis. This method of performing what-if analysis certainly has nothing wrong with it, but you should be aware of some other techniques.

If your input cells are not located near the formula cells, you can consider using a Watch Window to monitor the formula results in a movable window.

2. Uses of What-If Analysis Tool in Excel

In this section, I will explain how you can use the What-If Analysis tool in Excel to perform a What-If Analysis. I will explain the use of all 3 features in the What-If Analysis.

2.1. Using Scenario Manager Feature from What-If Analysis

In this first method, I will explain how you can use the Scenario Manager feature in What-If Analysis in Excel. Here, I will create a report that will show how the Simple Interest will change upon changing the Interest Rate. Let’s see the steps.

Steps:

•  Firstly, go to the Data tab from Ribbon.
• Secondly, select What-If Analysis.

Here, a drop-down menu will appear.

• Thirdly, select Scenario Manager.

Now, a dialog box named Scenario Manager will appear.

• After that, select Add to add Scenarios.

• Next, write the Scenario name as you want. Here, I wrote mine as a 6% Interest Rate.
• Then, select the cell that carries the value you want to change. Here, I selected cell C6 because in my dataset cell C6 contains the Interest Rate.
• After that, select OK.

Here, another dialog box named Scenario Values will appear.

• Firstly, write the value for changing cells. Here, I wrote 0.06 because I want my Interest Rate to be 6%.
• Secondly, select OK.

Now, you will see that the Scenario is added to the Scenario Manager.

• After that, you can create more Scenarios in the same way. Here, I created 2 more Scenarios for a 7% Interest Rate and an 8% Interest Rate.
• Next, select Summary.

Here, a dialog box named Scenario Summary will appear.

• Firstly, select Scenario Summary.
• Secondly, select the Result cells. Here, I selected cell C10 because cell C10 on my dataset contains the Simple Interest.
• Thirdly, select OK.

Finally, you will get the Scenario Summary in a new worksheet. Here, you will be able to see the difference in results upon changing the variable.

2.2. Applying Goal Seek Feature

In this method, I will apply the Goal Seek feature in What-If Analysis in Excel. Here, I will define the final value or result and observe how it changes the input value to achieve the result. For this example, I want the Simple Interest to be $1500 and want to know how much Principal Amount will be needed to get this result. I will use the Goal Seek feature to determine the value.

Let’s see the steps.

Steps:

•  Firstly, go to the Data tab from Ribbon.
• Secondly, select What-If Analysis.

Here, a drop-down menu will appear.

• Thirdly, select Goal Seek.

Here, a dialog box named Goal Seek will appear.

• Firstly, select Set Cell. Here, I selected cell C10 because this cell contains the value of Simple Interest in my dataset.
• Secondly, insert value in the To value field. Here, I wrote 1500 because I want my Simple Interest to be 1500.
• Thirdly, select By changing cell. Here, I selected cell C5 because this cell contains the value of the Principal Amount in my dataset.
• After that, select OK.

Now, another dialog box named Goal Seek Status will appear.

• Finally, select OK.

Here, you can see the needed Principal Amount for the desired Simple Interest.

2.3. Employing Data Table Feature from What-If Analysis in Excel

In this method, I will explain how you can use the Data Table feature in What-If Analysis in Excel. I will use this What-If Analysis to see how the Final Amount changes if I change the Interest Rate.

Let’s see the steps.

Steps:

• Firstly, make a table with your needed columns and rows. Here, I have my table. It contains the Interest Rate and Final Amount columns.

• Secondly, select the first cell of your next column where you want the results. Here, I  selected cell F5.
• Thirdly, in cell F5 write the following formula.

Here, this formula will refer to the formula that is in cell C9.

• After that, press ENTER to get the result.

Now, I will use the Data Table feature.

• Firstly, select the table.
• Secondly, go to the Data tab.
• Thirdly, select What-If Analysis.

Here, a drop-down menu will appear.

• After that, select Data Table.

Now, a dialog box named Data Table will appear.

• Firstly, select the Column input cell as I am giving input in the column named Interest Rate. Here, I selected cell C6 as it carries the Interest Rate in my dataset.
• Secondly, select OK.

Finally, the table will be filled with your desired result. From this table, you will be able to see the Final Amount if you change the Interest Rate.

Read More: Sensitivity Analysis in Excel Using One or Two Variables Data Table

Things to Remember

• It should be noted that manual What-If Analysis can be performed with a small set of data.
• If you want to calculate back calculation then Goal Seek is needed. It can not be done with a manual What-If Analysis.

Practice Section

Here, I have provided a practice sheet for you to practice What-If Analysis in Excel.

Conclusion

In this article, I tried to cover What-If Analysis. I hope this was helpful for you. For more articles like this visit ExcelDemy. If you have any questions let me know in the comment section below.

Related Articles

• How to Perform the What If Analysis with Data Table in Excel
• How to Delete What If Analysis in Excel (2 Simple Methods)
• What If Analysis Data Table Not Working (Issues with Solutions)
• How to Do What-If Analysis Using Goal Seek in Excel
• Create One Variable Data Table Using What If Analysis
• How to Perform What If Analysis in Excel (3 Examples)