Excel for nlinux.org 365 Excel for nlinux.org 365 for Mac Excel 2019 Excel 2016 Excel 2019 for Mac Excel 2013 Excel 2010 Excel 2007 Excel 2016 for Mac Excel for Mac 2011 More...Less

By making use of What-If Analysis tools in Excel, you deserve to usage a number of various sets of worths in one or more formulregarding discover all the assorted outcomes.

You are watching: Which what-if analysis tool is the best option for complex

For instance, you deserve to execute What-If Analysis to build two budgets that each assumes a certain level of revenue. Or, you deserve to specify an outcome that you desire a formula to develop, and also then determine what sets of worths will certainly develop that outcome. Excel offers numerous different devices to aid you perdevelop the form of analysis that fits your demands.

Note that this is simply an overview of those tools. Tright here are web links to aid topics for each one particularly.


What-If Analysis is the procedure of altering the values in cells to view exactly how those changes will certainly impact the outcome of formulas on the worksheet.

Three kinds of What-If Analysis devices come with Excel: Scenarios, Goal Seek, and also File Tables. Scenarios and also Documents tables take sets of input worths and also recognize feasible outcomes. A Documents Table functions via just one or 2 variables, but it have the right to accept many type of various worths for those variables. A Scenario have the right to have actually multiple variables, however it have the right to just accommodate approximately 32 values. Goal Seek works in a different way from Scenarios and also Documents Tables in that it takes an outcome and determines possible input values that produce that outcome.

In addition to these three devices, you deserve to install add-ins that aid you perform What-If Analysis, such as the Solver add-in. The Solver add-in is similar to Goal Seek, however it deserve to accommodate even more variables. You have the right to additionally produce forecasts by using the fill manage and miscellaneous commands that are constructed right into Excel.

For more progressed models, you deserve to use the Analysis ToolPak add-in.


A Scenario is a collection of values that Excel saves and have the right to substitute automatically in cells on a worksheet. You have the right to produce and also conserve various groups of worths on a worksheet and then switch to any type of of these new scenarios to watch various results.

For instance, suppose you have 2 budobtain scenarios: a worst case and a ideal situation. You can use the Scenario Manager to create both scenarios on the exact same worksheet, and also then switch in between them. For each scenario, you specify the cells that readjust and also the values to use for that scenario. When you switch between scenarios, the result cell changes to reflect the different altering cell worths.

*

1. Changing cells

2. Result cell

*

1. Changing cells

2. Result cell

If numerous human being have specific information in separate workbooks that you want to usage in scenarios, you can collect those workbooks and also merge their scenarios.

After you have produced or gathered all the scenarios that you require, you deserve to develop a Scenario Overview Report that incorporates information from those scenarios. A scenario report displays all the scenario information in one table on a brand-new worksheet.

*

Note: Scenario reports are not immediately recalculated. If you readjust the worths of a scenario, those transforms will certainly not display up in an existing summary report. Instead, you must produce a new summary report.


If you recognize the outcome that you desire from a formula, but you"re not certain what input value the formula calls for to acquire that result, you can usage the Goal Seek function. For instance, expect that you need to borrow some money. You recognize how much money you want, just how lengthy a duration you desire in which to pay off the loan, and just how much you can afford to pay each month. You can usage Goal Seek to determine what interest price you should secure in order to fulfill your loan goal.

*

Cells B1, B2, and B3 are the values for the loan amount, term length, and also interest price.

Cell B4 display screens the result of the formula =PMT(B3/12,B2,B1).


Note: Goal Seek functions via just one variable input value. If you want to recognize more than one input worth, for instance, the loan amount and also the monthly payment amount for a loan, you must instead use the Solver add-in. For even more information about the Solver add-in, see the area Prepare forecasts and also progressed organization models, and follow the links in the See Also section.


If you have a formula that offers one or two variables, or multiple formulas that all use one widespread variable, you deserve to use a Documents Table to watch all the outcomes in one area. Using Data Tables makes it simple to examine a selection of possibilities at a glance. Because you focus on only one or two variables, results are straightforward to review and also share in tabular develop. If automatic recalculation is permitted for the workbook, the data in Documents Tables immediately recalculates; as an outcome, you always have actually fresh data.

*

Cell B3 consists of the input value. Cells C3, C4, and also C5 are values Excel substitutes based upon the value gone into in B3.

A Data Table cannot accommodate even more than two variables. If you want to analyze even more than two variables, you can usage Scenarios. Although it is limited to only one or 2 variables, a Data Table can usage as many different variable worths as you desire. A Scenario can have a maximum of 32 different worths, but you have the right to create as many scenarios as you want.


If you want to prepare forecasts, you have the right to usage Excel to automatically geneprice future worths that are based on existing data, or to immediately geneprice extrapolated values that are based on direct trend or growth trfinish calculations.

You have the right to fill in a series of worths that fit a straightforward linear trend or an exponential growth trfinish by using the fill take care of or the Series command also. To extend complicated and nondirect information, you have the right to usage worksheet features or the regression analysis tool in the Analysis ToolPak Add-in.

Although Goal Seek have the right to accommoday just one variable, you have the right to task backward for more variables by using the Solver add-in. By utilizing Solver, you deserve to discover an optimal value for a formula in one cell—dubbed the target cell—on a worksheet.

Solver works with a team of cells that are concerned the formula in the targain cell. Solver adjusts the worths in the transforming cells that you specify—referred to as the adjusecure cells—to create the outcome that you specify from the target cell formula. You have the right to apply constraints to restrict the worths that Solver deserve to use in the version, and the constraints deserve to describe other cells that impact the taracquire cell formula.

See more: What Is The Difference Between Standard English And Nonstandard English Vs


Need more help?

You can constantly ask an experienced in the Excel Tech Community or get assistance in the Answers community.