Advanced Data Analysis - What-If Analysis

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.