What-if analysis in Excel (2023)

What-if Analysis in Excel is a tool that helps us create different models, scenarios, and data tables. This article explores ways to use what-if analysis.

We have three parts of the what-if analysis in Excel. They are the following:

1. scenario manager
2. Reach the goal in Excel
3. data table in excel

#1Scenario Manager in What-If Analysis

As a business leader, it is important to know the different scenarios of your future project. Based on the scenarios, the manager makes decisions. For example, you will carry out one of the important projects. He has done his homework and listed all possible expenses and below is the list of all his expenses.

The expected cash flow from this project is $75 million, which is located in Cell C2. Total expenses include all your fixed costs andVariable expends, the total cost in cell C12 is $57.45 million. The total profit in cell C14 is $17.55 million and the profit percentage is 23.40% of your cash flow.

It is the basic scenario of your project. Now you need to know the winning scenario if some of your expenses go up or down.

scene 1

• In a general scenario, you estimate the "project license" cost to be $10 million, but you definitely expect $15 million.
• Raw material costs increase by $2.5 million.
• Other expensesOther expensesOther expenses include all non-operating costs incurred to support business operations. These payments, such as rent, insurance, and taxes, are not directly related to the core business.see more informationreduce by 50,000.

scenario 2

• The “cost of the project” will be $20 million
• The "work day" will be $5 million
• “Operating costs” must be $3.5 million

You now have all the scenarios listed on the form. Based on these scenarios, you should create a table of how this will affect your profit and win percentage.

Total Excel created three scenarios, although we only provide two scenario changes because Excel displays existing reports as one scenario.

In this table we can easily see the impact of changes on the win percentage.

#2Reach the goal in what-if analysis

Now we know the benefit of the Scenario Manager. A hypothetical analysis goal can tell you what you need to do to achieve the goal.

Andrew is a tenth grade student. His goal is to achieve an average grade of 85 on the final exam. He already completed 5 exams and left with only 1 exam. He therefore in all 5 exams completed.

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

The current average is 82.2.

Andrew's TARGET is 85. His current GPA is 82.2. He is close with a test of 3.8.

Now the question arises how many points you have to achieve in the final exam to achieve an overall average of 85 at the end. This is determined by the GOAL SEEK what if analysis tool.

• Paso 1:Navigate to DATA > What-If Analysis > Achieve Target.

• Paso 2:It will show you the following dialog.

• Level 3:Here we need to define the cell first. "Define Cell" is nothing more than the cell we need for the final result, which is our overall average cell (B7). Next is "value". Again, Andrew's overall average target value is nothing more than the value we need to define cell (85).

The next and final step is to change the cell in which you want to see the effects. So we need to change cell B6, the cell for the final course grade.

• Stage 4:Click OK." Excel takes a few seconds to complete the process, but eventually displays the output as below.

Now we have our results here. To achieve an overall average of 85, Andrew must achieve a 99 on the final exam.

#3Data Table in What-If Analysis

We've already seen two wonderful techniques in what-if analysis in Excel. First, the data table can create different scenario tables based on the change of the variable. Here we have two types of data tables: a variable data table and a "Table with two variable dataTable with two variable dataA two-variable data table helps you analyze how two different variables affect the overall data table. Simply put, it helps you determine what effect changing the two variables has on the result.see more information.” This article will show youA variable data table in ExcelA variable data table in ExcelA variable data table in Excel means changing a variable with multiple options and getting the results for multiple scenarios. The data entries in a variable data table are in a single column or in a row.see more information.

Suppose you sell 1,000 products for £15, your total expected spend is £12,500, and your profit is £2,500.

You are not satisfied with the benefit you have obtained. Your projected profit is $7,500. You have decided to increase your unit price to increase your profit, but you do not know how much to increase.

Data tables can help you with this. Create a table below.

Cell F1 is now linked to cell Total Profit B6.

• Paso 1:Select the newly created table.

• Paso 2:Go to DATA > What-If Analysis > Data Table.

• Level 3:You will now see the following dialog.

• Stage 4:Since we are displaying the result vertically, exit the "line input cell". In the Input Cell column, select cell B2, which is the original sales price.

• Paso 5:Click OK to get the results. List the winning numbers in the new table.

So we have our data table ready. To make a profit of ₹7500, you need to sell at ₹20 per unit.

things to remember

• The what-if analysis data table can be run with two variable changes. See our article on two-variable data tables for what-if analysis.
• What-If Analysis Goal Seek takes a few seconds to do the calculations.
• What-If Analysis The scenario manager can compile a summary with input figures and current values.

recommended articles

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) Scope Goal, 3) Data Tables, along with practical examples and a downloadable Excel template. You can learn more about Excel in the following articles:-

• Pareto Analysis in ExcelPareto Analysis in ExcelA Pareto chart is a chart that is a combination of a bar chart and a line chart. Shows the frequency of the error and its cumulative impact. It helps to find the bugs to see the best possible measure of overall improvement.see more information
• Reach the goal in VBA
• Sensitivity analysis in Excel