What Is What-If Analysis In Excel?
What-If Analysis is a feature used to see the different scenarios when one or more variables change and the overall outcome changes. It helps us build different scenarios, achieve goal seek numbers, and create data tables using variables.
For instance, a printing shop prints approximately 1000 copies per day. Therefore, the estimated profit is calculated as $500. So, now we need to know what the profit will be using what-if scenarios such as what if the company prints 1500 copies, 2000 copies, and 2500 copies. By using What-If analysis, we can create scenarios for profit.
So, let us learn how to use what if analysis in excel in detail.
Table of contents
- What-If Analysis helps in creating and evaluating different scenarios. Thus, it helps users make the best selection from the available alternatives.
- Based on the requirement, we can choose the three methods: Scenario Manager, Goal Seek, and Data Table.
- Goal Seek changes only a single variable, so it is used to set only one cell value.
- Data Table is used to choose the best outcome when changing two variables.
- The scenario manager uses multiple variables simultaneously and returns output with different scenarios.
Types Of What-if Analysis In Excel
There are three types of what-if analysis in excel. They are:
- Scenario Manager
- Goal Seek
- Data Table
We need to choose any of the above three methods to perform a What-If analysis in excel.
So, let us learn about the three types in detail.
#1 – Scenario Manager
What is it?
Using scenario manager, we can make changes to multiple variables. For each change, we can create scenarios and compare one against another. Below are some of the key features of scenario manager.
- We can create different scenarios every time there is a change in a particular variable or multiple variables.
- We can create up to 32 scenarios.
- We can create a summary of all the scenarios by combining all the scenarios in the worksheet.
- We can give names to each scenario as per user requirements.
Step by Step Example
For instance, Mr. Peter is running an advertising company, and his revenue, cost, and profit details are shown below:
Mr. Peter knows that the more he pushes on indirect revenue, the more indirect cost increases. So now, he wants to know what would be his profit numbers when indirect revenue & indirect cost increase.
Mr. Peter estimates the following scenarios:
- When the Indirect Revenue is $10,000, the Indirect Cost increases to $8,000.
- When the Indirect Revenue is $12,000, the Indirect Cost increases to $9,000.
- When the Indirect Revenue is $15,000, the Indirect Cost increases to $14,000.
So, Mr. Peter wants to know which estimate will be best for his business and how his profit will be impacted when these scenarios occur.
Let us make use of scenario managers to create different profit scenarios.
The steps used to obtain results using what if analysis in excel with scenario manager are as follows:
Step 1: Go to the Data and click on the What-If Analysis from the Forecast group.
From the drop-down list, select the Scenario Manager.
Step 2: Now, we can see Scenario Manager window as shown below.
Click on the Add button.
Step 3: Type the scenario name as Option 1.
Step 4: In the Changing Cells box, we need to choose the cells we like to change.
In this example, we want to change the Indirect Revenue (cell B3) and the Indirect Cost (cell B7).
So, choose cells B3 and B7.
Step 5: Click OK.
The Scenario Values window appears. Change the values of cells B3 & B7, respectively.
It is Mr. Peter’s first option or the scenario.
Step 6: Click on Add option since we need to create two more scenarios.
As soon as we click on Add, the Scenario Manager window pops up again. Now, let us type the name as Option 2.
Step 7: Since we want to change the Indirect Revenue (cell B3) and the Indirect Cost (cell B7), the cell references in Changing cells: box remain the same.
Step 8: Click on Add and change the B3 & B7 cell values.
Step 9: Similarly, click on Add and create the third scenario as Option 3.
Step 10: Click OK and change the cell values B3 & B7.
Step 11: Now, do not click on Add because we have created all the scenarios. Instead, select OK, and we will have a list of all the scenarios we have created so far.
Step 12: Click on Summary. The Scenario Summary window pops up. Here, we should choose the Result cells: i.e., the cell where we want to display the results.
Let us choose cell B10 (the Total Profit Cell) in our example.
Step 13: Click OK, and we will have a summary report of each scenario as highlighted in the below image:
We can see that the feature has provided the results with current values and the three scenarios we have created.
Interpretation of Scenarios: From the table, it is clear that Option 2 yields the maximum profit of $23,000. So, Mr. Peter can choose Option 2.
If we look at Option 3, the indirect revenue is more, but due to an increase in the indirect cost, the profit comes down to $21,000.
Likewise, we can create business scenarios and decide on the best-suited scenario using what if analysis in excel.
#2 – Goal Seek
What is it?
Goal seek helps us know the required number to meet the targeted value. For instance, assume a student has completed five exams, and his anticipated total score is 400 out of 500, i.e., an average score of 80 per subject. However, he still has to write one more subject. Therefore, his target is to score more than the average score of 80. In this case, we can use Goal Seek. It helps him know the required score to achieve his targets.
Remember, while using goal seek, we can change only one variable.
Step by Step Example
For instance, Ms. Paula is a cricketer and has a target of getting at least 50 runs in the tournament. Out of 8, she played 7 matches and already scored 326 runs.
The below table shows the runs scored by Ms. Paula.
In cell B10, we have applied the AVERAGE excel function, and the average obtained from matches is 47.
So, now in the tournament’s final match, she wants to know what the targeted score (goal) will be.
Therefore, we can use the Goal Seek feature to find the required score.
The steps used to obtain results using what if analysis in excel with Goal seek method are as follows:
Step 1: Go to the Data tab and click on the What-If Analysis option from the Forecast group.
Then, select the Goal Seek excel option from the drop-down list.
Step 2: The Goal Seek window pops up.
Step 3: In the Set cell: box, we need to choose the cell references for which we need to set the value. Let us select cell B10 (Average Score formula) in this case.
Step 4: In the To value: box, we need to enter the value of the targeted score, i.e., 50.
Step 5: Similarly, in the By changing cell: box, we need to choose the cell where we want to display the result.
Since we want to find the needed runs in Match 8, we should select cell B9.
Click OK, and Excel will run iteration and returns the output in Goal Seek Status window.
So, from the result, it is clear that Ms. Paula needs to score a minimum of 74 runs in the final match.
#3 – Data Table
What is it?
The data table in excel can be used to change two variables. Users need to provide input variables. Excel returns the output based on the formula and variables given by the users.
Step by Step Example
For instance, Mr. Simon is planning to buy a home loan and can afford to pay $3500 monthly as EMI. Hence, he wants to know what the best rate of interest and tenure will be.
Below is the calculation of EMI using the Excel PMT function.
As per the current calculation, the EMI amount is $5,069, which is more than Mr. Simon’s savings.
So, he wants to know the ideal interest rate and tenure year to clear his loan. So, we will create a data table to find the EMI amount at different interest rates and tenure.
The steps used to obtain results using how to use what if analysis in excel with the Data Table method is as follows:
Step 1: Create a table with various interest rates, as shown in the below image.
Step 2: In the formula cell (D2), type the actual EMI formula, i.e., cell B5.
Step 3: Select the newly created table. Go to the Data. Under the Forecast group, click on the What-If Analysis drop-down list and choose the Data Table.
Step 4: The Data Table window appears.
Step 5: In the Row input cell: box, we should choose the cell reference of tenure years, i.e., cell B3, since, in the newly created table, we have categorized the rows that indicate the tenure as years.
Similarly, in the Column input cell: we should choose the cell reference of interest rates, i.e., cell B4. Again, it is because we have interest rates in a column in the newly arranged data table.
Click OK, and we will get different EMI amounts at different interest rates and different tenures, as shown in the image below:
From the table, we can see different EMI amounts. Therefore, Mr. Simon can choose the ideal combination of interest rate and tenure to match his savings to pay the EMI amount.
Also, Mr. Simon can go for the interest rate of 7.5% for a period of eight years, where he has to pay an EMI amount of $3,471, which is well within the budget.
Likewise, we can use of the types of what-if analysis in excel and make useful decisions with changes in different variables.
Important things to note
- What-If Analysis in excel is used to derive results based on various what-if scenarios.
- Excel has three different types of what-if analysis options. They are Scenario Manager, Goal Seek, and Data Table.
- With scenario manager, we can compare the results of various scenarios to multiple variables.
- To use Goal Seek, we should enable iterative operation in Excel workbook.
- Goal seek is limited to changing only one variable at a time, whereas the data table can include a maximum of 2 variables.
Frequently Asked Questions
What-If analysis is a useful feature of excel that help us decide by providing results based on various scenarios.
For instance, if you are running a hotel, you estimate to sell 1000 plates of food per day with an estimated profit of $300. So, now you need to know your profit if you sell 1500, 2000, or 2500 plates of food. Using What-If analysis, we can create scenarios and obtain the results, thus helping you find the estimated profit.
What-If Analysis is one of the inbuilt features of excel. Let us learn how to do What-If Analysis in Excel with an example.
Mr. Rafal runs a pet shop, and he can sell 50 pets per day. The below table shows details of his sales and profit earned in columns A and B, respectively.
Now, Mr. Rafal wants to know the profit scenario if he sells 40, 60, and 70 pets per day.
The steps used to perform What-If Analysis in excel are as follows:
Step 1: Under the Data tab, select Scenario Manager option from the Forecast group.
Now, we can see Scenario Manager window as shown below.
Click on the Add button.
Step 2: Let us type the scenario name as Less Likely in Scenario name: box and choose cell B2 in the Changing cells: section.
Step 3: Click OK. The Scenario values window pops up. Change the cell value to 40.
Step 4: Click Add and create two other scenarios (for selling 60 and 70 pets per day) as Best Case and Less Realistic, respectively.
Step 5: We have created three scenarios. Now, click on Summary to create a summary of all the scenarios. The Scenario Summary window pops up. Choose the Result cells: as B5 (Profit cell).
Click OK, and we will have all the summary scenarios.
With the obtained results, Mr. Rafal can now decide how many pets he should have daily to earn a better profit.
What-If Analysis in Excel is located under the Data tab in the Forecast group.
We can select any of the three types under What-If Analysis drop-down list.
What-If Analysis comes with three different methods of analysis. They are:
1. Scenario Manager
2. Goal Seek
3. Data Table
This article must be helpful to understand What-If Analysis in Excel, with its formula and examples. You can download the template here to use it instantly.
This has been a guide to What-If Analysis in Excel. Here we discuss the types of what-if analysis options with examples and downloadable excel template. You can learn more from the following articles –