How to use What-If analysis in Excel?

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.

Three kinds of what-if analysis tools come with Excel: scenarios, data tables, and Goal Seek. Scenarios and data tables take sets of input values and determine possible results. A data table works only with one or two variables, but it can accept many different values for those variables. A scenario can have multiple variables, but it can accommodate only up to32 values. Goal Seek works differently from scenarios and data tables in that it takes a result and determines possible input values that produce that result.

In addition to these three tools, you can install add-ins that help you perform what-if analysis, such as the Solver add-in.

The Solver add-in is similar to Goal Seek, but it can accommodate more variables. You can also create forecasts by using the fill handle and various commands that are built into Excel. For more advanced models, you can use the Analysis Pack add-in.

 

By using what-if analysis tools in Microsoft Office Excel, you can use several different sets of values in one or more formulas to explore all the various results.

For example, you can do what-if analysis to build two budgets that each assumes a certain level of revenue. Or, you can specify a result that you want a formula to produce, and then determine what sets of values will produce that result. Excel provides several different tools to help you perform the type of analysis that fits your needs.

You can do what-if analysis to build two budgets that each assumes a certain level of revenue.
Or, you can specify a result that you want a formula to produce, and then determine what sets of values will produce that result.
Excel provides several different tools to help you perform the type of analysis that fits your needs.

By using what-if analysis tools in Microsoft Office Excel, you can use several different sets of values in one or more formulas to explore all the various results.
For example, you can do what-if analysis to build two budgets that each assumes a certain level of revenue.
Or, you can specify a result that you want a formula to produce, and then determine what sets of values will produce that result.
Excel provides several different tools to help you perform the type of analysis that fits your needs.
In this article Overview Use scenarios to consider many different variables Use Goal Seek to find out how to get a desired result Use data tables to see the effects of one or two variables on a formula Prepare forecasts and advanced business models

To learn about the Scenario Manager feature of What-If Analysis, let's using the following vignette.
We operate a small business selling a widget.
We are to project what the Sales volume, COG (Cost of Goods Sold), Gross Profit and Expense values should be for each of the four quarters of the year.
These are our assumptions: Sales volume in Quarter1 is $588,235.29.
Sales increases by a certain percentage quarter by quarter.
(Sales Growth percentage is specified in cell H4) Cost of Goods Sold is a fixed percentage of Sales.
(COGS percentage is specified in cell H5) Expenses are a fixed percentage of Sales.
(Expenses percentage is specified in cell H7) We're going to look at3 possible scenarios: Average, where the Sales Growth percentage is5%, COGS is20%, and Expenses is25% Optimistic, where the Sales Growth percentage is8%, COGS is18%, and Expenses is20% Conservative, where the Sales Growth percentage is2%, COGS is25%, and Expenses is35% Open WhatIf.xlsx, go to the ScenarioManager worksheet.
In cell D4, enter this formula to calculate Sales volume in Quarter2: =C4+C4*$H$4 Copy this formula from C4 to D4:F4.
In cell C5, enter this formula to calculate COGS in Quarter1: =C4*$H$5 Copy this formula from C5 to D5:F5.
In cell C6, enter this formula to calculate Gross Profit in Quarter1: =C4-C5 Copy this formula from C6 to D6:F6.
In cell C7, enter this formula to calculate Expenses in Quarter1: =C4*$H$7 Copy this formula from C7 to D7:F7 In cell C8, enter this formula to calculate Income in Quarter1: =C6-C7 Copy this formula from C8 to D8:F8 In cell G4, Use the SUM() function to get totals of Sales in all4 quarters.
In cells G5:G7, also use the SUM function to get totals of COGS, Gross Profit and Expenses in all4 quarters.
(You may use AutoFill from G4 down to G7.) Click What-If Analysis icon in the Data Tools group under the Data tab.
Next click Scenario Manager...
The Scenario Manager dialog box appears.
Click the Add...
button.
In the Scenario name field: type Average.
In the Changing cells: field, enter H4, H5, H7.
Click OK.
Now the Scenario Values dialog box appears.
In1: enter0.05 (5%); in2: enter0.2 (20%); in3: enter0.25 (25%).
Click OK.
Now the Scenario Values dialog box disappears, and the Scenario Manager dialog box reappears.
Click the Add...
button.
In the Scenario name field: type Optimistic.
(Keep H4, H5, H7 in the Changing cells: field.) Click OK.
Now the Scenario Values dialog box appears.
In1: enter0.08 (8%); in2: enter0.18 (18%); in3: enter0.20 (20%).
Click OK.
Now the Scenario Values dialog box disappears, and the Scenario Manager dialog box reappears.
Click the Add...
button.
In the Scenario name field: type Conservative.
(Keep H4, H5, H7 in the Changing cells: field.) Click OK.
Now the Scenario Values dialog box appears.
In1: enter0.02 (2%); in2: enter0.25 (25%); in3: enter0.35 (35%).
Click OK.
Now the Scenario Values dialog box disappears, and the Scenario Manager dialog box reappears.
Click Close.
Save this file on the desktop with your name as the file name, so you can access the various scenarios later.
Click What-If Analysis icon in the Data Tools group under the Data tab.
Next click Scenario Manager...
You can now go back and choose to show the Average, Optimistic and Conservative scenarios.
Notice the chart below the data is changing as you look at the different scenarios.
To learn about Goal Seek and Data Table features of What-If Analysis, we continue with the same vignette.
We set the Sales Price at $25/unit, and the Unit Cost is $12/unit.
We also have Fixed Costs of $6,500 to operate this business.
So we have a Contribution Margin from each widget unit at $13 (i.e.
= $25 - $12).
Therefore, to break even we need to sell500 units (i.e.
= $6,500 ÷ $13).
We know the Profit (or Loss) of this operation is: = Sales Revenue (i.e.
Units sold × Contribution Margin) - Fixed Costs What-If Analysis using Goal Seek We can use Goal Seek to find the input values needed to achieve a goal or objective.
To use Goal Seek, you select the cell containing the formula that will return the result you’re seeking and then indicate the target value you want the formula to return and the location of the input value that Excel can change to reach the target.
Open WhatIf.xlsx, go to the GoalSeek worksheet.
Go to cell B15, enter this formula for calculating profit/loss: =B4*B10-B7 Click What-If Analysis icon in the Data Tools group under the Data tab.
Next click Goal Seek...
A Goal Seek dialog box appears.
The Set cell: field is pre-filled with B15 because you started by selecting cell B15.
In the To value: field, write $25,000.
This is the profit we want to make.
In the By changing cell: field, write B4 (click the cell B4).
Click OK.
You now see the number in cell B4 is changed.
This is the number of units to be sold in order to make a profit of $25,000.
Now repeat steps #4 through #7 and specify different profit/loss goals: $0 (break-even), $1,000? $5,000? $10,000.
How many widgets do we have to sell to achieve these profit levels? What-If Analysis using Data Table NOTE: Excel supports two types of data tables: (1) a one-variable data table that substitutes a series of possible values for a single input value in a formula, and (2) a two-variable data table that substitutes a series of possible values for two input values in a single formula.
We are only going to demonstrate the one-variable data table here.
You should feel free to use the above referenced resources and try using a two-variable data table.
When performing What-If analysis using a one-variable data table, you specify one cell in the worksheet that serves either as the Row Input Cell (if you’ve entered the series of possible values across columns of a single row) or as the Column Input Cell (if you’ve entered the series of possible values down the rows of a single column), but not both.
Using the same vignette, let’s see what the different Profit/Loss results can be given different number of units sold.
Open WhatIf.xlsx, go to the DataTab;e worksheet.
This sheet is set up in a similar manner to that of GoalSeek.
Go to cell B21, enter this formula for calculating profit/loss: =B10*B4-B7 This is the same as what you did in cell B15 of GoalSeek when you used Goal Seek.
In cells C20:H20, enter the different (projected) numbers of units to be sold.
(These are test values, not actual sales.) Select the table range B20:H21.
Click What-If Analysis icon in the Data Tools group under the Data tab.
Next click Data Table...
A Data Table dialog box appears.
In the Row input cell: field, write $25,000.
This is the profit we want to make.
In the By changing cell: write B4 (click the cell B4).
Ignore the Column input cell: field because we are using a one-variable data table here.
Click OK.
You now see profit/loss numbers in cells C:21:H21, each corresponding to the projected number of units sold above it.
Do you know (exactly or approximately) how many units need to be sold to be the break-even point (ie.
$0 for profit/loss)? Now repeat steps #5 through #12 and find out the profit (or loss) if we sell100,300,500,800,1,200, or1,500.

For Scenario Manager, As per user followup, and Goalseek forecast Report , Data Table Pivot table, as we have back for main bata first we do vlookup for current statusand then Goalseek forecast Report and then Pivot table, for Data table count or etc