What-If Analysis in Excel

Updated on January 2, 2024
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

What is a What-If Analysis in Excel?

What-If Analysis in Excel is a tool that helps us create different models, scenarios, and Data Tables. This article will look at the ways of using What-If Analysis.

We have three parts of What-If Analysis in Excel. They are as follows:

  1. Scenario Manager
  2. Goal Seek in Excel
  3. Data Table in Excel
You can download this What-If Analysis Excel Template here – What-If Analysis Excel Template

#1 Scenario Manager in What-If Analysis

As a business head, it is important to know the different scenarios of your future project. Based on the scenarios, the business head will make decisions. For example, you are going to undertake one of the important projects. You have done your homework and listed all the possible expenditures from your end, and below is the list of all your expenses.

What-if analysis Example 1

The expected cash flow from this project is $75 million, which is in cell C2. Total expenses comprise all your fixed and variable expenses, the total cost is $57.45 million in cell C12. Total profit is $17.55 million in cell C14, and profit % is 23.40% of your cash inflow.

It is the basic scenario of your project. Now, you need to know the profit scenario if some of your expenses increase or decrease.

Scenario 1

Scenario 2

  • The “Project Cost” to be at $20 million
  • The “Labor Daily Wages” to be at $5 million
  • The “Operating Cost” is to be at $3.5 million

Now, you have listed out all the scenarios in the form. Based on these scenarios, you need to create a table about how it will impact your profit and profit %.

To create What-If Analysis scenarios, follow the below steps.

  1. Go to DATA > What-If Analysis > Scenario Manager.

    What-if analysis Example 1-1

  2. Once you click “Scenario Manager,” it will show you below the dialog box.

    What-if analysis Example 1-2

  3. Click on “Add.” Then, give “Scenario name.”

    What-if analysis Example 1-3

  4. In changing cells, select the first scenario changes you have listed out. The changes are Project License (cell C10) at $15 million, Raw Material Cost (cell C7) at $11 million, and Other Expenses (cell C11) at $4.5 million. Mention these three cells here.

    What-if analysis Example 1-4

  5. Click on “OK.” It will ask you to mention the new values as listed in scenario 1.

    What-if analysis Example 1-5

  6. Do not click on “OK” but click on “OK Add.” It will save this scenario for you.

  7. Now, it will ask you to create one more scenario. As we listed in scenario 2, make the changes. This time we need to change Project Cost (C10), Labour Cost (C8), and Operating Cost (C9).

    What-if analysis Example 1-6

  8. Now, add new values here.

    What-if analysis Example 1-7

  9. Now click on “OK.” It will show all the scenarios we have created.

    What-if analysis Example 1-8

  10. Click on “Scenario summary.” It will ask you which result cells you want to change. Here, we need to change the Total Expense Cell (C12), Total Profit Cell (C14), and Profit % cell (C16).

    What-if analysis Example 1-9

  11. Click on “OK.” It will create a summary report for you in the new worksheet.

    What-if analysis Example 1-10

Total Excel has created three scenarios even though we have supplied only two scenario changes because Excel will show existing reports as one scenario.

From this table, we can easily see the impact of changes in pour profit %.

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

#2 Goal Seek in What-If Analysis

Now, we know the Scenario Manager’s advantage. What-if-Analysis Goal Seek can tell you what you must do to achieve the target.

Andrew is a class 10th student. His target is to achieve an average score of 85 in the final exam. He has already completed 5 exams and left with only 1 exam. Therefore, in the completed 5 exams.

What-if analysis Example 2

To calculate the current average, apply the average formula in the B7 cell.

What-if analysis Example 2-1

The current average is 82.2.

What-if analysis Example 2-2

Andrew’s GOAL is 85. His current average is 82.2. He is short by 3.8 with one exam.

Now, the question is how much he has to score in the final exam to eventually get an overall average of 85. It can be found by the What-If Analysis GOAL SEEK tool.

  • Step 1: Go to DATA > What-If Analysis > Goal Seek.
What-if analysis Example 2-3
  • Step 2: It will show you below the dialog box.
What-if analysis Example 2-4
  • Step 3: Here, we need to set the cell first. “Set cell” is nothing but which cell we need for the final result, i.e., our overall average cell (B7). Next is “To value.” Again, Andrew’s overall average GOAL is nothing but for what value we need to set the cell (85).

The next and final step is changing which cell you want to see the impact on. So, we need to change cell B6, the cell for the final subject’s score.

What-if analysis Example 2-5
  • Step 4: Click on “OK.” Excel will take a few seconds to complete the process, but it eventually shows the result like the one below.
Example 2-6

Now, we have our results here. To get an overall average of 85, Andrew has to score 99 in the final exam.

#3 Data Table in What-If Analysis

We have already seen two wonderful techniques under What-If Analysis in Excel. First, the Data Table can create different scenario tables based on the variable change. We have two kinds of Data Tables here: one variable Data Table and a “Two-variable data tableTwo-variable Data TableA two-variable data table helps analyze how two different variables impact the overall data table. In simple terms, it helps determine what effect does changing the two variables have on the result.read more.” This article will show you One variable data table in ExcelOne Variable Data Table In ExcelOne variable data table in excel means changing one variable with multiple options and getting the results for multiple scenarios. The data inputs in one variable data table are either in a single column or across a row.read more.

Assume you are selling 1,000 products at ₹15, your total anticipated expense is ₹12,500, and your profit is ₹2,500.

Example 3

You are not happy with the profit you are getting. Your anticipated profit is ₹7,500. You have decided to increase your per-unit price to increase your profit, but you do not know how much you need to increase.

Data tables can help you. Create a table below.

What-if analysis Example 3-1

Now, in the cell, F1 links to the “Total Profit” cell, B6.

What-if analysis Example 3-2
  • Step 1: Select the newly created table.
Example 3-3
  • Step 2: Go to DATA > What-if Analysis > Data Table.
Example 3-4
  • Step 3: Now, you will see below dialog box.
Example 3-5
  • Step 4: Since we are showing the result vertically, leave the ”Row input cell.” In the “Column input cell,” select cell B2, which is the original selling price.
Example 3-6
  • Step 5: Click on “OK” to get the results. It will list out profit numbers in the new table.
Example 3-7

So, we have our Data Table ready. To profit from ₹7,500, you need to sell at ₹20 per unit.

Things to Remember

  • The What-If Analysis data table can be performed with two variable changes. Refer to our article on What-If Analysis two-variable Data Table.
  • What-If Analysis Goal Seek takes a few seconds to perform calculations.
  • What-If Analysis Scenario Manager can give a summary with input numbers and current values together.

This article is a guide to What-If Analysis in Excel. Here, we discuss three types of What-If Analysis in Excel such as 1) Scenario Manager, 2) Goal Seek, 3) Data Tables along with practical examples, and a downloadable Excel template. You may learn more about Excel from the following articles: –