How to Use What-If Analysis in Excel (3 Suitable Examples)

When planning or analyzing data in Excel, it’s crucial to understand how changes in your inputs can affect the outcome. Excel offers powerful what-if analysis tools like Scenario Manager, Goal Seek, and Data Tables that let you explore different possibilities without altering your original data. In this article, we will teach you each technique step by step so you can confidently model, forecast, and plan in Excel.

What Is a What-If Analysis in Excel?

What-if analysis in Excel lets you explore how changes in inputs affect your final results. Instead of manually changing different values, Excel offers built-in tools that automate the process and help you evaluate various scenarios. There are three main types of what-if analysis:

Scenario Manager: It lets you store and switch between multiple sets of input values to compare different outcomes.
Goal Seek: This helps you find the exact input needed to reach a specific result.
Data Tables: It allows you to test how one or two variables affect your results across a range of values.

Using Scenario Manager for what-if analysis in Excel helps you compare different sets of inputs and their outcomes. Instead of manually changing values one by one, you can store multiple scenarios in one place. This makes it easy to switch between best-case, worst-case, or custom cases. In this section, we’ll show you how to set up and use Scenario Manager for different scenarios.

Suppose we have a sample dataset containing Selling Price and Units sold for 3 items: T-shirts, Mugs, and Caps. In addition, we have 2 types of scenarios: Best case and Worst case. The best case scenario consists of higher units sold with low fixed costs. Similarly, the worst case has lower units sold and higher fixed costs. Now, we will demonstrate both scenarios using the Scenario Manager for what-if analysis in Excel.

First, we will calculate the “Revenue” for each item.

➤ Select cell B4, and put the following formula and hit ENTER.

➤ Drag the fill handle to the right to apply this formula to calculate the revenue for “Mugs” and “Caps” as well.

➤ Now, input your “Total Fixed Cost”.

Here, we have put a Total Fixed cost of 5,000 in cell B6.

➤ To calculate the “Total Revenue,” select cell B7 and input the formula below.

➤ Finally, to determine the “Net Profit,” select the cell and type the formula below.

Thus, we have successfully created a basic financial model, providing a clear overview of our revenue, costs, and profit. Now, we will analyze both our scenarios’ best and worst cases.

➤ First, navigate to the Data tab in the Excel ribbon.
➤ In the Forecast group, click on What-If Analysis.
➤ From the dropdown menu, select Scenario Manager.

This will open the Scenario Manager dialog box.

➤ In the Scenario Manager dialog box, click on the Add button.

➤ In the Scenario name section, name your scenario as Best Case.
➤ For “Changing cells“, select the cells that will vary in your scenario.

In our model, these are the varying cells “Units Sold” for T-Shirts, Mugs, and Caps ($B$3:$D$3) and the “Total Fixed Cost” ($B$6). You can select multiple, non-adjacent cells by holding down the CTRL key while clicking.

➤ Click OK.

➤ In the Scenario Values dialog box, enter the specific values for your “Best Case” scenario:

• For T-Shirt Units ($B$3), enter 700.
• For Mug Units ($C$3), enter 600.
• For Cap Units ($D$3), enter 450.
• For Fixed Cost ($B$6), enter 4000.

➤ Hence, click OK.

➤ Similarly, insert the specific values for your “Worst Case” scenario:

• For T-Shirt Units ($B$3), enter 300.
• For Mug Units ($C$3), enter 200.
• For Cap Units ($D$3), enter 150.
• For Fixed Cost ($B$6), enter 6000.

➤ Finish the process by hitting OK.

➤ Thus, both “Best Case” and “Worst Case” scenarios are added.
➤ Now, click on the Summary button.

➤ Hence, for “Result cells“, specify the cell that contains the final outcome you want to analyze across scenarios.
➤ Click OK.

Finally, Excel will generate a new worksheet titled “Scenario Summary”, which presents a comprehensive report. This summary outlines how your changing cells affect the “Net Profit” under the best and worst scenarios.

Goal Seek in Excel is a powerful tool for testing how changing one input can achieve a specific outcome. Instead of guessing values manually, Goal Seek automates the process. It’s especially useful when you are targeting a specific output, such as a set profit or loan payment. Goal Seek is designed to adjust a single input cell; it cannot work with multiple cells at once. In this section, we’ll show you how to use Goal Seek with a simple example.

Suppose we have a dataset with the following items and their initial values: Selling Price per Unit: $40 (in cell B2), Cost per Unit: $18 (in cell B3), Fixed Costs: $5,000 (in cell B4), Units Sold: 300 (in cell B5). Now, we will set up formulas to calculate our financial metrics and then use Goal Seek to reach a target value.

➤ To calculate Revenue, select cell B7.
➤ In the formula bar, input the formula (Selling Price per Unit * Units Sold) and press ENTER.

➤ Now, calculate Total Variable Cost, select cell B8.
➤ In the formula bar, insert the formula (Cost per Unit * Units Sold) and press ENTER.

➤ Then, compute Total Cost, select cell B9.
➤ In the formula bar, put the formula (Fixed Costs + Total Variable Cost) and press ENTER.

➤ Finally, to calculate Net Profit, select cell B10.
➤ In the formula bar, input the formula (Revenue – Total Cost) and press ENTER.

Now our financial model is set up, let’s use Goal Seek to find out how many units we need to sell to achieve a specific net profit, for example, $5,000.

➤ Navigate to the Data tab in the Excel ribbon.
➤ In the Forecast group, click on What-If Analysis.
➤ From the dropdown menu, select Goal Seek.

This will open the Goal Seek dialog box.

➤ In the Goal Seek dialog box:

For “Set cell“, select the cell that contains your target outcome, which is B10 (Net Profit).
For “To value“, enter your desired net profit, which is 5000.
For “By changing cell“, select the input cell that Goal Seek should adjust to reach your target.

➤ Click OK.

Thus, Excel will automatically adjust the “Units Sold” to the required number to achieve your desired “Net Profit,” providing an instant solution to your what-if scenario.

Data Tables in Excel help you test how changing one or two inputs affects the outcome of a formula. Instead of updating values manually, you can automate the process across a range of possibilities. This makes it easy to compare results side by side. In this section, we’ll show you how to use one-variable, two-variable, and three-variable data tables for quick analysis.

Let’s assume we have a dataset with the following inputs: Loan Amount: $1,00,000 (in cell B2), Annual Interest Rate: 4% (in cell B3), Term (Years): 7 (in cell B4).

One Variable Sensitivity Analysis

One Variable sensitivity analysis shows how changes in a single input affect the result of a formula. In this section, we will show you how to set it up using Excel’s Data Table feature.

First, we will calculate the Monthly EMI.

➤ Select cell B5, and input the formula below.

Here,

(B3/12) converts the annual interest rate to a monthly rate.
(B4*12) converts the term in years to months.
(-B2) represents the loan amount as a negative value, as it’s an outflow.

➤ Press ENTER to get the monthly EMI.

Now, we will create the structure for your data table.

➤ In cell D1, enter “Annual Interest Rate” and in cell E1, enter “Monthly EMI”.
➤ Below “Annual Interest Rate,” list the various interest rates you want to analyze, for example, from 4% to 10% in column D (cells D2:D8).

➤ In the cell above the first interest rate and next to the “Monthly EMI” header (cell E2), link this cell to your original EMI calculation.
➤ Write down the formula in cell E2 and hit ENTER.

➤ Select the entire range of your data table, including the column of varying interest rates and the EMI formula reference (cells D2 to E8).
➤ Navigate to the Data tab in the Excel ribbon.
➤ In the Forecast group, click on What-If Analysis.
➤ From the dropdown menu, select Data Table.

➤ In the Data Table dialog box:

Leave “Row input cell” blank, as our varying input is in a column.
For “Column input cell“, select the cell that holds your original Annual Interest Rate, which is B3.

This tells Excel to substitute the values from column D into cell B3 to calculate the new EMI.

➤ Click OK.

Finally, your data table will populate, showing you the calculated “Monthly EMI” for each “Annual Interest Rate” you specified.

Two Variable Sensitivity Analysis

Sometimes, you need to analyze how two different input variables affect a formula’s outcome. A two-variable Data Table is perfect for this, as it presents a grid showing the results for all combinations of your two changing inputs. Below are the steps for performing a what-if analysis using a two-variable Data Table.

Suppose we have the same loan calculation model set up as before. Here, let’s create the layout for our two-variable data table. Along the top row, starting from cell B7 and moving rightward, list the different “Years” (terms) we want to analyze. Down the first column, starting from cell A8 and moving downward, list the various “Interest Rates” we want to analyze.

➤ In cell A7, enter the formula below.

This cell will be the intersection point for your two variables.

➤ Select cells A7 to J14 containing the EMI formula reference, the row of varying years, and the column of varying interest rates.
➤ Navigate to the Data tab in the Excel ribbon.
➤ In the Forecast group, click on What-If Analysis.
➤ From the dropdown menu, select Data Table.

➤ In the Data Table dialog box:

For “Row input cell“, select the cell that holds your original Term (Years), which is B4.
For “Column input cell“, select the cell that holds your original Annual Interest Rate, which is B3.

➤ Click OK.

Finally, Excel will populate the entire data table, showing you the “Monthly EMI” for every combination of “Term (Years)” and “Annual Interest Rate.” This provides a comprehensive overview of how these two variables collectively influence your loan payments.

Three Variable Sensitivity Analysis

While a two-variable Data Table is excellent for analyzing two changing inputs, sometimes you need to consider a third variable. You can achieve this by combining a two-variable data table with a data validation list, allowing you to dynamically change a third input and observe its impact on the table’s results. Here, we will learn how to analyze EMI while varying the loan amount, interest rate, and term.

➤ First, create a list of different Loan Amounts you want to analyze.

Here, in column D, starting from cell D2, list $1,00,000, $2,00,000, and $3,00,000.

➤ Next, ensure our two-variable data table (with varying “Years” and “Interest Rates” and the “Monthly EMI” formula at the intersection) is already set up as previously described.
➤ In cell F1, enter a header for your third variable, such as “Loan Amount (3rd Variable)”.

➤ Select the cell where you want your dropdown list to appear (cell F2).
➤ Navigate to the Data tab in the Excel ribbon.
➤ In the Data Tools group, click on Data Validation.

➤ In the Data Validation dialog box, under the “Settings” tab:

From the “Allow” dropdown, select List.
In the “Source” field, input the range containing your list of loan amounts, which is =$D$2:$D$4.

➤ Click OK.

Thus, a drop-down arrow will appear in cell F2, allowing you to select different loan amounts from your predefined list.

➤ Choose a loan amount from the drop-down list.

Finally, as you select different loan amounts from the dropdown list in cell F2, your entire two-variable data table will dynamically update. This will show you the monthly EMIs based on the chosen loan amount, combined with varying interest rates and terms.

Frequently Asked Questions

How to fix if Goal Seek is not working in Excel?

You can increase the Maximum Iterations or reduce the Maximum Change from File > Options > Formulas to improve accuracy and find better solutions.

What is the difference between Scenario Manager and Data Table?

Scenario Manager lets you save and switch between multiple input sets manually, while Data Tables automatically calculate and display results based on varying inputs.

Is there a limit to how many scenarios I can create?

Excel allows up to 32 changing cells per scenario and up to 255 scenarios in a worksheet.

Concluding Words

Above, we have explored all the essential methods to perform what-if analysis in Excel, including Scenario Manager, Goal Seek, and Data Tables. These tools help you test different inputs, analyze outcomes, and make better-informed decisions. With these techniques, you can model, plan, and forecast more effectively right within Excel. If you have any questions, feel free to share them in the comment section below.