Which What if analysis tool should you use if you know the desired result?

What-if analysis is the process of changing the values in cells to see how those changes will affect the outcome of formulas on the worksheet. You can use several different sets of values in one or more formulas to explore all the various results.

What-if analysis is useful in many situations while doing data analysis. For example −

• You can propose different budgets based on revenue.

• You can predict the future values based on the given historical values.

• If you are expecting a certain value as the result from a formula, you can find different sets of input values that produce the desired result.

Excel provides you with the following What-if analysis tools that can be used based on your data analysis needs −

• Data Tables
• Scenario Manager
• Goal Seek

Data Tables and Scenarios take sets of input values and project forward to determine possible results. Goal seek differs from Data Tables and Scenarios in that it takes a result and projects backwards to determine possible input values that produce that result.

In this chapter, you will understand the possible situations where you can use the Whatif Analysis tools. For details on usage of these tools, refer to the later chapters in this tutorial.

Data Tables

A Data Table is a range of cells in which you can change values in some of the cells and come up with different answers to a problem. For example, you might want to know how much loan you can afford for a home by analyzing different loan amounts and interest rates. You can put these different values along with the PMT function in a Data Table and get the desired result.

A Data Table works only with one or two variables, but it can accept many different values for those variables.

For the details on Data Tables, refer to the chapter – What-If Analysis with Data Tables in this tutorial.

Scenario Manager

A scenario is a set of values that Excel saves and can substitute automatically in cells on a worksheet.

The key features are −

• You can create and save different groups of values on a worksheet and then switch to any of these new scenarios to view different results.

• A scenario can have multiple variables, but it can accommodate only up to 32 values.

• You can also create a scenario summary report, which combines all the scenarios on one worksheet. For example, you can create several different budget scenarios that compare various possible income levels and expenses, and then create a report that lets you compare the scenarios side-by-side.

• Scenario Manager is a dialog box that allows you to save the values as a scenario and name the scenario.

For the details on Scenarios, refer to the chapter – What-If Analysis with Scenario Manager in this tutorial.

Goal Seek

Goal Seek is useful if you know the result that you want from a formula, but not sure what input value the formula needs, to get that result. For example, if you want to borrow a loan and you know the loan amount, tenure of loan and the EMI that you can pay, you can use Goal Seek to find the interest rate at which you can avail the loan.

Goal Seek can be used only with one variable input value. If you have more than one variable for input values, you can use the Solver add-in.

For details on the usage of Goal Seek, refer to the chapter – What-If Analysis with Goal Seek in this tutorial.

Solver

Solver comes with Excel as an add-in. You can use Solver to find an optimal value for a formula in a cell called the target cell on a worksheet.

Solver works with a group of cells that are related to the formula in the target cell. Solver adjusts the values in the adjustable cells that you specify, to produce the result that you specify, from the target cell formula.

For the details on the usage of Excel Solver add-in, refer to the chapter - Optimization with Excel Solver in this tutorial.

How to do a What If Analysis in Excel

When we generate an Excel function or formula, we put various components together to return a desired result. The Goal Seek function in Excel operates in an opposite manner: It allows us to begin with our desired result, and then it determines the input value which will give us our desired result. We will now demonstrate how we can use Goal Seek to carry out What if analysis in Excel.

Let’s assume that we have enrolled for a class. We currently have a 65 score, but we require a minimum score of 70 to pass our class. Luckily, we have one last assignment that may be able to increase our average. We can use the Goal Seek feature to discover exactly what score we need on our last assignment to pass our class.

1. In our worksheet example below, we have collected the data available to us from our first four assignments (see column A). Even though we do not know the outcome of our fifth test, we can enter a function or formula that will calculate our final grade. The average formula we will enter into cell B7 of our worksheet example is as follows;
   =AVERAGE(B2:B6)

Our purpose here is to use the Goal Seek feature,  to determine the minimum grade we will require on our final assignment and display it in in cell B6 of our worksheet example.

1. Next we will select the cell which contains the value we desire to change.
   When we use Goal Seek, we will have to choose a cell which already contains a function or formula.
   In our worksheet example, we will select cell B7 since it already contains a formula;
   =AVERAGE(B2:B6)

1. Click on the “Data” tab, and then click on the “What-If Analysis” button, and then select the “Goal Seek” option from the fly-out menu:

A dialog box should appear with 3 fields:

• Set cell = The cell which will contain our desired outcome. In our worksheet example, cell B7 has already been selected.
• To value = This is our desired outcome. In our worksheet example, we will enter 70 since we must earn that minimum score to pass our class.
• By changing cell = The cell where the Goal Seek feature will return our answer. In our worksheet example, we will choose cell B6 since we want to calculate the grade we must score on our final assignment.

1. We can click “OK” when we are done:

1. Our predicted result will appear inside the cell we have selected. In our worksheet example, Goal Seek determined that we must score a minimum of 90 in our final test to earn a pass;

Instant Connection to an Excel Expert

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Is a what if analysis tool that helps to find the input value that results in a target value?

Which of the following tools menu is used to view the results of what if Analyses in Excel?

Which statement best describes the What If analysis Data Table tool?